{"id":2248,"date":"2018-07-18T08:14:22","date_gmt":"2018-07-18T06:14:22","guid":{"rendered":"http:\/\/dobretrejdy.com\/?p=2248"},"modified":"2025-11-05T14:26:36","modified_gmt":"2025-11-05T12:26:36","slug":"ziva-data-z-ib-do-excelu","status":"publish","type":"post","link":"https:\/\/dobretrejdy.com\/?p=2248","title":{"rendered":"\u017div\u00e1 data z IB do Excelu"},"content":{"rendered":"

\u00a0 \u00a0Z\u00edsk\u00e1n\u00ed historick\u00fdch dat pro pot\u0159eby nejr\u016fzn\u011bj\u0161\u00edch anal\u00fdz v\u00a0aplikaci Excel bylo p\u0159edm\u011btem minul\u00e9ho \u010dl\u00e1nku<\/a><\/span><\/strong>. Vy\u017eadovalo to m\u00edrnou \u00fapravu platformy TWS a n\u00e1sledn\u011b p\u0159\u00edpravu excelovsk\u00e9ho se\u0161itu, abych po jednoduch\u00e9m porozum\u011bn\u00ed sestaven\u00ed p\u0159\u00edkazu na po\u017eadavek pro historick\u00e1 data do bu\u0148ky v\u00a0takto p\u0159ipraven\u00e9m Excelu mohl za\u010d\u00edt stahovat historick\u00e1 data v\u00a0po\u017eadovan\u00e9m tvaru. Pokud jsem tedy schopen z\u00edskat historick\u00e1 data (nejb\u011b\u017en\u011bji nap\u0159\u00edklad pro akciov\u00e9 tituly) pro r\u016fzn\u00e9 \u010dasov\u00e9 r\u00e1mce, nemuselo by b\u00fdt tak\u00e9 \u0161patn\u00e9 zjistit, jak\u00fdm zp\u016fsobem by bylo mo\u017en\u00e9 po\u0159\u00eddit do excelovsk\u00e9ho se\u0161itu \u017eiv\u00e1 aktu\u00e1ln\u00ed data<\/strong> od m\u00e9ho brokera Interactive Brokers<\/strong>, mohl bych pak nap\u0159\u00edklad vid\u011bt ve sv\u00e9m Excelu historick\u00e1 data sou\u010dasn\u011b s\u00a0aktu\u00e1ln\u00ed cenou pro sledovan\u00fd titul a vytv\u00e1\u0159et si tak aktu\u00e1ln\u00ed obr\u00e1zek o aktu\u00e1ln\u00edm chov\u00e1n\u00ed na trhu sou\u010dasn\u011b se zobrazenou histori\u00ed podle typu sta\u017een\u00fdch dat.<\/span><\/p>\n

\u00a0\u00a0 K\u00a0propojen\u00ed a z\u00edsk\u00e1n\u00ed dat do Excelu nebude zapot\u0159eb\u00ed<\/strong> \u017e\u00e1dn\u00e9 \u201en\u00e1ro\u010dn\u00e9 program\u00e1torsk\u00e9 pr\u00e1ce\u201c se skripty a jejich kop\u00edrov\u00e1n\u00ed do okna aplikace Visual Basic v\u00a0m\u00e9m Excelu, jako v\u00a0p\u0159\u00edpad\u011b historick\u00fdmi daty, bude sta\u010dit pouze pochopit logiku z\u00edsk\u00e1v\u00e1n\u00ed dat a konstrukce po\u017eadavku na tato \u017eiv\u00e1 data, potom ji\u017e stahov\u00e1n\u00ed bude ot\u00e1zkou jednoduch\u00e9 rutiny. Stejn\u011b jako v\u00a0p\u0159\u00edpad\u011b pr\u00e1ce s\u00a0historick\u00fdmi daty, bude zapot\u0159eb\u00ed p\u0159ipravit aplikaci TWS<\/strong>, tedy obchodn\u00ed platformu a tak\u00e9 po\u0159\u00eddit nezbytn\u00e9 programov\u00e9 vybaven\u00ed pro spr\u00e1vnou komunikaci mezi TWS a m\u00fdm excelovsk\u00fdm se\u0161item. \u00a0<\/span><\/p>\n

\n

P\u0159\u00edprava TWS<\/strong><\/span><\/h1>\n

\u00a0\u00a0 Nejprve je zapot\u0159eb\u00ed p\u0159ipomenout, \u017ee nemohu z\u00edskat \u017eiv\u00e1 data<\/strong>, kter\u00e1 nem\u00e1m opr\u00e1vn\u011bn\u00ed<\/strong> z\u00edsk\u00e1vat do sv\u00e9 platformy TWS. Znamen\u00e1 to tedy, \u017ee uvid\u00edm takov\u00e1 data, na kter\u00e1 m\u00e1m subscription, a kter\u00e1 se mi tak\u00e9 \u017eiv\u011b zobrazuj\u00ed v\u00a0dob\u011b obchodov\u00e1n\u00ed v\u00a0TWS. Nastaven\u00ed opr\u00e1vn\u011bn\u00ed pro po\u017eadovan\u00e1 data mohu prov\u00e1d\u011bt v\u00a0Account Management, co\u017e je z\u00e1kladn\u00ed klientsk\u00e1 operace, kterou zde nebudu rozeb\u00edrat. V\u00a0d\u0159evn\u00edch dob\u00e1ch m\u00fdch po\u010d\u00e1tk\u016f s\u00a0experimentov\u00e1n\u00edm s\u00a0daty s\u00a0TWS do Excelu jsem zakou\u0161el mnoho kru\u0161n\u00fdch chvil s\u00a0\u017eiv\u00fdm streamov\u00e1n\u00ed dat, proto\u017ee prost\u0159ed\u00ed, vytvo\u0159en\u00e9 Interactive Brokers, bylo zna\u010dn\u011b nestabiln\u00ed a vykazovalo zna\u010dn\u00e9 nedostatky ve spolehlivosti. Stahov\u00e1n\u00ed dat toti\u017e fungovalo prost\u0159ednictv\u00edm DDE, tedy obdobn\u011b jako v\u00a0p\u0159\u00edpad\u011b zpracov\u00e1v\u00e1n\u00ed po\u017eadavku na historick\u00e1 data, a toto bylo zna\u010dn\u011b nekomfortn\u00ed. Vy\u017eadovalo ji\u017e m\u00edrn\u011b pokro\u010dilej\u0161\u00ed schopnosti s\u00a0prac\u00ed ve VBA, a toto nav\u00edc nebylo \u017e\u00e1dnou z\u00e1rukou pro spr\u00e1vnou funkcionalitu, stahov\u00e1n\u00ed postihovaly \u010dast\u00e9 v\u00fdpadky dat, tak\u017ee to bylo t\u00e9m\u011b\u0159 k\u00a0ni\u010demu. Zd\u00e1 se, \u017ee se v Interactive Brokers pou\u010dili a pro pot\u0159eby \u017eiv\u00e9ho stahov\u00e1n\u00ed dat vymysleli lep\u0161\u00ed technologii. Stahov\u00e1n\u00ed dat prob\u00edh\u00e1 nov\u011b prost\u0159ednictv\u00edm RTD serveru API<\/strong>, kter\u00e1 umo\u017e\u0148uje na\u010d\u00edtat pot\u0159ebn\u00e1 data do bun\u011bk Excelu na z\u00e1klad\u011b jednodu\u0161e formulovan\u00fdch po\u017eadavk\u016f v\u00a0t\u011bchto bu\u0148k\u00e1ch, tyto po\u017eadavky pak p\u0159edstavuj\u00ed formulovan\u00e9 vzorce s\u00a0po\u017eadovanou syntax\u00ed, jejich\u017e odezvou jsou \u017eiv\u00e1 data. Z\u00a0technologie a softwarov\u00e9ho \u0159e\u0161en\u00ed RTD serveru pak vypl\u00fdv\u00e1, \u017ee tato mo\u017enost b\u011b\u017e\u00ed pouze v\u00a0prost\u0159ed\u00ed Windows<\/strong>, majitel\u00e9 Mac\u016f mohou na toto \u017eiv\u00e9 stahov\u00e1n\u00ed toto cestou prozat\u00edm zapomenout.<\/span><\/p>\n

\u00a0\u00a0 Ke spr\u00e1vn\u00e9mu nastaven\u00ed platformy TWS budu tedy pot\u0159ebovat t\u00e9m\u011b\u0159 stejn\u00e9 nastaven\u00ed, jako v\u00a0p\u0159\u00edpad\u011b po\u017eadavku na historick\u00e1 data z\u00a0minul\u00e9ho \u010dl\u00e1nku. Pro po\u0159\u00e1dek znovu p\u0159ipomenu.<\/span><\/p>\n

\u00a0\u00a0 API software nainstaluji do sv\u00e9ho po\u010d\u00edta\u010de ze str\u00e1nek Interactive Brokers<\/a><\/strong><\/span>, kliknut\u00edm na tla\u010d\u00edtko GET API SOFTWARE.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Zde mus\u00edm po\u017eadovat instalaci verze API 9.73 a vy\u0161\u0161\u00ed<\/strong>. Pokud ji\u017e m\u00e1m instalaci ve star\u0161\u00ed verzi, mus\u00edm ji p\u0159einstalovat<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Tato vy\u0161\u0161\u00ed verze m\u00e1 toti\u017e implementovanou komunikaci s\u00a0RTD serverem<\/strong>, kter\u00e1 zajist\u00ed hladk\u00fd pr\u016fb\u011bh vy\u0159izov\u00e1n\u00ed po\u017eadavk\u016f na \u017eiv\u00e1 data do m\u00e9ho Excelu<\/span><\/p>\n

\u00a0 \"\"<\/span><\/p>\n

\u00a0\u00a0 Po \u00fasp\u011b\u0161n\u00e9 instalaci spr\u00e1vn\u00e9 verze API na m\u00e9m po\u010d\u00edta\u010di mus\u00edm povolit komunikaci m\u00e9ho Excelu<\/strong> s datov\u00fdm rozhran\u00edm m\u00e9ho brokera. V\u00a0hlavn\u00edm okn\u011b platformy TWS<\/strong> postupuji podle n\u00ed\u017ee uveden\u00e9ho obr\u00e1zku<\/span><\/p>\n

\"\"<\/p>\n

(1) Na p\u00e1su n\u00e1stroj\u016f kliknu na \u201eConfigure<\/strong>\u201c<\/span><\/p>\n

(2) Z \u201eConfiguration<\/strong>\u201c vyberu \u201eAPI\u201c a n\u00e1sledn\u011b potvrd\u00edm \u201eSettings<\/strong>\u201c<\/span><\/p>\n

(3) V\u00a0okn\u011b mo\u017en\u00fdch voleb vpravo vyberu a zatrhnu \u201eEnable DDE clients<\/strong>\u201c<\/span><\/p>\n

(4) Nastaven\u00ed \u201eSocket Port<\/strong>\u201c ponech\u00e1m v\u00fdchoz\u00ed 7496<\/strong><\/span><\/p>\n

(5) Nastaven\u00ed potvrd\u00edm kliknut\u00edm na \u201eOK<\/strong>\u201c<\/span><\/p>\n

\u00a0\u00a0 Instalac\u00ed API a spr\u00e1vnou \u00fapravou TWS jsem nyn\u00ed schopen ve sv\u00e9m Excelu nastavovat po\u017eadavky na \u017eiv\u00e1 data m\u00e9ho brokera Interactive Brokers.<\/span><\/p>\n

\n

\u00a0\u00a0\u00a0 Syntaxe po\u017eadavku v\u00a0bu\u0148ce Excelu<\/strong><\/span><\/h1>\n

\u00a0\u00a0 Vytvo\u0159en\u00ed po\u017eadavku na \u017eiv\u00e1 dat do bu\u0148ky m\u00e9ho Excelu p\u0159ipom\u00edn\u00e1 zad\u00e1n\u00ed jak\u00e9hokoliv jin\u00e9ho matematick\u00e9ho nebo logick\u00e9ho vzorce. Syntaxi si mohu p\u0159izp\u016fsobovat podle pot\u0159eby komplexnosti m\u00e9ho po\u017eadavku, nebudu zde ale popisovat jednotliv\u00e9 nuance, proto\u017ee budou z\u00e1visl\u00e9 na ka\u017ed\u00e9m odli\u0161n\u011b, podle odli\u0161nosti po\u017eadavk\u016f na parametry z\u00edsk\u00e1van\u00fdch dat v\u00a0z\u00e1vislosti na individu\u00e1ln\u00edch pot\u0159eb\u00e1ch jednotliv\u00fdch trader\u016f.<\/span><\/p>\n

\u00a0\u00a0 Ne\u017e odk\u00e1\u017eu na celou komplexn\u00ed mo\u017enost syntaxe po\u017eadavk\u016f pro nejr\u016fzn\u011bj\u0161\u00ed investi\u010dn\u00ed instrumenty a nejrozmanit\u011bj\u0161\u00ed podoby z\u00edsk\u00e1van\u00fdch dat, uvedu na jednoduch\u00e9m p\u0159\u00edkladu nejd\u0159\u00edve obecn\u00fd form\u00e1t\u00a0nejjednodu\u0161\u0161\u00ed formy po\u017eadavku<\/strong>. Podstatou syntaxe po\u017eadavku je n\u00ed\u017ee uveden\u00fd vzor, ve sv\u00e9 pat\u0159i\u010dn\u00e9 modifikaci pro r\u016fzn\u00e9 investi\u010dn\u00ed n\u00e1stroje vepsan\u00fd do bu\u0148ky Excelu za sou\u010dasn\u011b spu\u0161t\u011bn\u00e9 platform\u011b TWS.<\/span><\/p>\n

=RTD<\/span>(tws.twsrtdserverctrl<\/span>;;T\u011bloDotazu<\/span>; Co chci zobrazit<\/span>)<\/strong><\/span><\/h3>\n

\u00a0\u00a0 K\u00a0popisu jednotliv\u00fdch \u010d\u00e1st\u00ed vzorce<\/span><\/p>\n