Ve svých příspěvcích odkazuji na možné datové zdroje pro různé analýzy a vypadá to, že mým favoritem v poskytování historických dat je server Quandl.com, kde mohu získat zdarma například denní Close data nejznámějších akciových titulů. Bohužel musím konstatovat, že po ukončení aktualizace databáze WIKI, která obsahovala velké množství akciových titulů, už na serverech Quandl.com nezbývá mnoho databází, které by zadarmo poskytovaly nejzákladnější data alespoň pro akciové tituly, z databáze EOD mohu například stahovat zdarma data pouze pro nejběžnější a největší akciové tituly, více již mi není zdarma nabízeno. Práci s Quandl.com jsem ukazoval také proto, že z ní lze pohodlně stahovat data do Excelu, což je skvělé pro další práci v tomto pravděpodobně nejběžnějším analytickém nástroji. Protože jsem klientem brokera Interactive Brokers a vycházím z předpokladu, že valná většina čtenářů používá tohoto brokera, bude předmětem tohoto článku jednoduchá ukázka, jak pořídit základní akciová historická data do Excelu právě od tohoto poskytovatele. Nebudu již potřebovat navštěvovat stránky Quandl.com nebo dokonce stahovat jeho doplněk do Excelu, ale k získání historických dat mi postačí pouze spuštěná platforma TWS a mírně upravený excelovský sešit.

Omezení rozsahu dat

   Protože nic není úplně dokonalé, je třeba si dopředu ukázat, jak budou má data limitována, jinými slovy, jaká historická data jsem schopen pořídit a jaká historická data již pořídit nelze. Na obrázku níže jsou popsána omezení přímo ze stránek poskytovatele – Interactive Brokers

   Z textu je patrné, že nebude možné získat historická data pro opční kontrakty, které již expirovaly a také alespoň EOD (End-Of-Day) ceny opčních kontraktů, které se obchodují a doposud neexpirovaly. Myšlenku, že budu moci zapomenout na skvělý thinkorswim s historickými cenami opcí, musím tedy odložit. Naopak ale u instrumentů, u kterých jsou historická data dostupná, mohu získat data na nižších časových rámcích, než jsou pouze EOD data poskytována například z Quandl.com. Vše ostatně vyplyne ze samotného nastavení připojení a struktury dotazu na požadovaná historická data. K hladkému fungování stahování historických dat do mého excelovského sešitu prostřednictvím Interactive Brokers potřebuji tři základní věci: 1/ Nastavit platformu TWS, 2/ Nastavit excelovský sešit a 3/ Pochopit nastavení požadavku na data

Nastavení TWS

   Přípravu k načítání dat musím začít u nastavení platformy TWS. Data do svého excelovského sešitu budu od mého brokera získávat prostřednictvím API a technologií DDE (Dynamic Data Exchange). K tomu, abych mohl toto API využívat, musím jej mít nainstalováno na svém počítači. Není asi zapotřebí dodávat, že vše je vhodné si vyzkoušet a nastavit na spuštěném Demo účtu, sice nebudu posílat prostřednictvím mého Excelu žádné obchodní příkazy, ale je jistější vše vyzkoušet na tomto simulovaném účtu. Pokud ještě takový „cvičný“ účet nemám zřízen, lze postupovat podle tohoto návodu. Také musím podotknout, že žádné stahování dat nebude fungovat, pokud nemám reálný účet u Interactive Brokers, který je nafundován penězi a připravený k obchodování. 

   API software nainstaluji do svého počítače ze stránek Interactive Brokers, kliknutím na tlačítko GET API SOFTWARE.

 Po úspěšné instalaci API na mém počítači musím povolit komunikaci mého Excelu s datovým rozhraním mého brokera. V hlavním okně platformy TWS postupuji podle níže uvedeného obrázku

   (1) Na pásu nástrojů kliknu na „Configure“

   (2) Z „Configuration“ vyberu „API“ a následně potvrdím „Settings“

   (3) V okně možných voleb vpravo vyberu a zatrhnu „Enable DDE clients“

   (4) Nastavení potvrdím kliknutím na „OK“

   Doporučuji nastavit aplikaci TWS jako na obrázku, v dalším článku se chci zmínit o streamování reálných dat z IB do Excelu, takže se nastavení bude hodit i pro tyto možné potřeby. Tímto je má obchodní platforma, v mém případě demo účet, připraven k práci z připravených excelovským sešitem.

Nastavení Excelu

   Získání historických dat pomocí TWS brokera Interactive Brokers prostřednictvím Excelu je koncipováno na základě vytvoření správně formulovaného požadavku v buňce Excelu, který, odeslán jako DDE spustí proceduru stažení dat do mého sešitu. Než se podívám na konstrukci takového požadavku, tak je jisté, že budu chtít a potřebovat svůj požadavek měnit a požadovat různé typy dat, proto musím v Excelu vytvořit a nastavit „tlačítko“, které bude takové nové požadavky odesílat. Protože nechci ve své ukázce zabíhat do podrobností s prací s VBA a pokoušet se předvádět, jak si toto vše mohu samostatně naprogramovat a vylepšit, bude krok k vytvoření takového spouštěcího tlačítka pouhým sledem kroků, které jen jednoduše napodobím.

   Na pásu nástrojů připraveného nového excelovského sešitu kliknu na „Vývojář“ (1) a ze zvolené nabídky vyberu „Visual Basic“ (2).

   Tímto krokem je iniciováno okno pro práci s Visual Basic, což mě nemusí nijak zneklidňovat, protože mě žádné programování nebude čekat. Výchozí okno této nabídky by mělo vypadat jako na níže uvedeném obrázku.

  Nastavení zkontroluji podle jednotlivých šipek. Nyní provedu nejtěžší programátorskou práci, když do takto připraveného okna zkopíruji níže uvedený script, ve kterém provedu pouze jednu jedinou úpravu, a to v místě, které jsem červeně označil. Toto místo označuje název uživatelského účtu u Interactive Brokers, které požaduje stažení historických dat, je to tedy to přihlašovací jméno, které vyplňuji do přihlašovacího boxu při spouštění aplikace TWS na mém počítači, pozor – písmeno „S“ před označením přihlašovacího jména musí zůstat zachováno, pokud se tedy přihlašuji jako novak156, potom musím níže uvedenou červenou část scriptu označenou jako „mujucet123“ mít ve tvaru „Snovak156“    

Sub fetchHistoricalData()

    Dim TheArray() As Variant

    TheArray = getData(„Smujucet123„, „hist“, „id4?result“)

    Call populate(TheArray)

End Sub

Function getData(serverName, topic, request)

    Dim chan As Integer

    chan = Application.DDEInitiate(serverName, topic)

    getData = Application.DDERequest(chan, request)

    Application.DDETerminate chan

End Function

Sub populate(ByRef TheArray() As Variant)

On Error GoTo ErrHandler

    For i = 1 To UBound(TheArray)

        Range(„F“ & i + 1).Value = TheArray(i, 1)

        Range(„G“ & i + 1).Value = TheArray(i, 2)

        Range(„H“ & i + 1).Value = TheArray(i, 3)

        Range(„I“ & i + 1).Value = TheArray(i, 4)

        Range(„J“ & i + 1).Value = TheArray(i, 5)

        Range(„K“ & i + 1).Value = TheArray(i, 6)

        Range(„L“ & i + 1).Value = TheArray(i, 7)

        Range(„M“ & i + 1).Value = TheArray(i, 8)

        Range(„N“ & i + 1).Value = TheArray(i, 9)

    Next

ErrHandler:

    Exit Sub

End Sub

   Programovací dovednosti tedy potvrdím využitím tlačítek Ctrl+C a Ctrl+V, abych tento script vložil do okna pro VBA. Celý proces by měl nakonec konečnou podobu podle níže uvedeného obrázku

   Celý script vložím do místa označeného (1), celou transakci potvrdím uložením (2) a okno zavřu (3). Touto operací jsem do sešitu vložil instrukce – jednotlivá makra a funkce, které pomohou nastavit DDE požadavek a kanál, kterým budou stažena data do mého sešitu a tam také rozmístěna a zobrazena. Abych mohl vložené požadované makro pro opakované načítání historických dat pohodlně spouštět přímo s „plochy sešitu“, využiji funkce Excelu a takové „spouštěcí tlačítko“ si vyrobím.

   Z nabídky „Vývojář“ (1) vyberu „Vložit“ (2) a z nabídky prvků si vyberu první nabízenou možnost z „Ovládacích prvků formuláře“, a to ikonu „Tlačítko“ (3). Pokud budu postupovat tímto způsobem, po volbě „Tlačítko“ se mi změní šipka mého ukazatele myši na malý křížek a tímto křížkem si mohu tažením myší při stisknutém levém tlačítku vytvořit v sešitu požadované tlačítko. Celý výše uvedený grafický proces s vytvořením tlačítka by měl skončit tak, jak je patrné na obrázku

   Takto vytvořené tlačítko je možné upravit a hlavně připravit ke spouštění očekávané funkce pro načítání historických dat. Toto mohu zařídit kliknutím pravým tlačítkem myši na oblast tlačítka a mohu z nabídky vybrat další úkony, vše jako na níže uvedeném obrázku

   Možností „Upravit text“ (1) mohu tlačítko přejmenovat například popisem, co od něj budu očekávat po jeho stisknutí. Důležitější ovšem je upravit nabídku tlačítka „Přiřadit makro…“ (2). Po potvrzení této nabídky se mi objeví okno z nabídkami maker k přiřazení k vytvářenému tlačítku

   Na obrázku je vidět výsledek přejmenování tlačítka podle předcházející volby „Upravit text“ (1). V okně, které mi nabízí makra vytvořené v sešitu bych měl mít pouze jednu možnou nabídku (pokud mám otevřen v počítači pouze jeden excelovský sešit), a to „fetchHistoricaldata“, což je makro vytvořené v sešitu pomocí vloženého scriptu ze stránky výše, kliknu tedy na toto makro (2) a okno zavřu potvrzením „OK“ (3). Tímto úkonem jsem si připravil excelovský sešit na správné odesílání požadavku mému brokerovi na stahování historických dat pomocí vytvořeného tlačítka

Nastavení požadavku na data

   Zásadním problémem získání historických dat je správná konstrukce požadavku v buňce mého excelovského sešitu, která, pomocí již nainstalovaného scriptu, získá do mého Excelu požadovaná historická data. Budu již zcela konkrétní a budu se snažit získat do mého excelovského sešitu hodinová historická data akciového titulu JPM, a to za poslední měsíc ode dnešního dne, kdy píšu tento článek. Můj požadavek, který napíšu do libovolné buňky Excelu by měl mít tento tvar:

=Snovak156|hist!’id4?req?JPM_STK_SMART_USD_~/20180709singleSpace23singleColon59singleColon59_1singleSpaceM_10_MIDPOINT_1_1′

   Tento požadavek má pevně danou syntaxi, kterou je zapotřebí rozklíčovat pro další práci s jinými druhy získávaných dat. Níže proto popis červeně vyznačených částí excelovského požadavku, který mohu poté libovolně měnit v závislosti na mé potřebě získávaného druhu historických dat“:

• novak156 – mé uživatelské jméno, kterým se připojuji k TWS, například při otevření demo účtu. Musí být stejné, jak jsem jej přepsal ve scriptu, který jsem kopíroval do mého sešitu
• JPM – ticker požadované akcie
• STK – typ cenného papíru (STK = akcie)
• SMART – název burzy, jejíž data chci pro daný titul získat
• USD – měna cenného papíru
• 20180709 – datum, od kterého chci získat data zpět, ve tvaru RRRRMMDD, chci tedy získat data od 9.7.2018 zpětně
• M – označení období, za které chci požadovaná data zpět od startovacího časového okamžiku podle předcházejícího bodu, chci konkrétně data měsíc zpět od 9.7.2018
• 10 – označení „velikosti úsečky“, na které chci mít data získávána data rozdrobena (10 = hodina), v mém případě chci hodinová data, měsíc zpět od 9.7.2018

   Pokud zkopíruji výše uvedený požadavek do libovolné buňky mého Excelu, kde mám nainstalován script podle textu výše a vyrobeno spouštěcí tlačítko, potom, samozřejmě za současně spuštěné aplikaci TWS, se mi objeví v této buňce nejdříve slovo „PROCESSING“, které se po vyřízení požadavku změní na „FINISHED“, viz obrázek

   Na obrázku výše je vidět zkopírovaný požadavek v označené buňce (2) a následně zpracovaný požadavek s potvrzením (1) „FINISHED“. Kliknutím na tlačítko „Stáhnout data“ (3) se mi požadovaná data objeví v mém excelovském sešitu.

  Získaná data si mohu například zobrazit graficky pomocí klasického excelovského burzovního grafu a nechat si tato hodinová data vizualizovat.

  Syntaxe požadavku v buňce Excelu je klíčem k získávání historických dat podle nejrůznějších kritérií. Zásadním kritériem, které ve formuli mohu měnit je typ investičního nástroje (akcie, futures…), požadovaná perioda, za kterou chci data zpět a délka úsečky, tedy požadovaný timeframe. Na níže uvedeném obrázku je znázorněn přehled označení časové periody, kterou mohu do požadavku dosazovat (namísto mého „M“ pro měsíční dobu zpět)

  Dále je na níže uvedeném obrázku uvedeno označení „délky úsečky“, kterou bych mohl chtít zobrazit (namísto mé „10“ pro hodinové svíčky)

   Syntaxe požadavku na historická data pro jiné druhy investičních nástrojů než jsou akcie jsou k prostudování zde . Musím ale konstatovat, že přestože stahování historických dat pro akciové tituly funguje téměř bezchybně, získat historická data futures kontraktů je opravdu problém.  

  Je jasné, že celý proces získávání dat lze v Excelu uživatelsky přizpůsobit naprogamováním požadavku na historická data do excelovské buňky pomocí jednoduchého VBA scriptu, není ale smyslem článku zacházet do takových programátorských detailů. Změnu požadavku tickeru akciového titulu mohu změnit ručně přímo v požadavku v buňce, stejně tak mohu měnit požadavek na stahovanou periodu zpět a velikost úsečky stejným přepsáním v buňce, důležité je pochopit, jak je požadavek na data strukturován a jak vůbec dostanu požadovaná data do mého excelovského sešitu. Protože nemohu zadávat požadavek na období delší než jeden rok zpět od určitého data, mohu pak období delší než jeden rok zpět sestavit z více požadavků najednou s jinými daty začátků těchto period.

   Pokud jsem klientem Interactive Brokers a chci získávat historická data do Excelu a databázové servery typu Quand.com mi nevyhovují nebo nenabízí co potřebuji, mohu využít popsaného postupu. Mohu pak pohodlně stahovat historická data zejména na kraších časových rámcích než jsou End-of-Day ceny, které mohu pořídit z mnoha jiných volných datových zdrojů. Efektivní získání například minutových dat (zadáním „5“ namísto „10“) akcie Harley-Davidson Inc. (zadáním „HOG“ namísto „JPM“) za poslední rok (zadáním „Y“ namísto „M“) je opravdu otázka několika vteřin pouhým přepsáním těchto tří údajů v buňce s požadavkem na historická data. Ukázkový Excel s popisovanými funkcionalitami (scriptem a tlačítkem) je možné stáhnout zde  :c)