Propojení Excelu a IB - základní tutoriál na stavbu VBA kódu - Praxe

Propojení Excelu a IB - základní tutoriál na stavbu VBA kódu

Jmenuji se Petr a zde na finančníku vystupuji pod nickem gizmo. Tomáš zde nedávno publikoval článek o automatickém obchodování z Excelu, a protože několik lidí vyjádřilo zájem mít zde technicky zaměřený tutorial, rozhodl jsem se jeden napsat. Pátým rokem pracuji jako software inženýr pro jednu velkou softwarovou firmu a programování je i mým velkým koníčkem. Sám ve Visual Basicu neprogramuji, nicméně Excel jeho podobu používá jako makro jazyk (abych byl přesný, používá VBA – Visual Basic for Applications), a proto ho budeme muset používat i v tomto článku.

Pro samotné propojení s trhem budeme používat platformu Trader Workstation (dále TWS) od Interactive Brokers. Tato platforma má docela propracované API a disponuje také rozhraním DDE (Dynamic Data Exchange), což je sice rozhraní poměrně zastaralé a bylo postupem času do značné míry nahrazeno rozhraním COM (Component Object Model), ale pro naše účely je naprosto dostačující. A co víc, je relativně jednoduché.
Cílem článku není poskytnout kompletní návod programování ve VBA. Cílem je okomentovat základní princip propojení Excelu s Interactive Brokers. Nebudu zde tedy rozebírat samotné principy programování. O VBA toho na Internetu najdete víc než dost.

Rozhraní DDE

Někteří z vás už mají nejspíš s DDE rozhraním nějaké zkušenosti, protože ho lze využít také například k propojení s platformou ThinkorSwim (dále TOS), o kterém už tu Petr psal v minulosti (zde). Propojení s touto platformou TOS je velice jednoduché. Stačí spustit TOS, spustit Excel a do nějaké buňky vložit =TOS|LAST!MSFT a Excel automaticky začne streamovat Last cenu titulu MSFT. Propojení s TWS je o něco komplikovanější, ale v podstatě to funguje na stejném principu jako propojení s TOS. Nejdříve si samozřejmě musíte nainstalovat TWS. To musí vždy běžet, jinak nebude mít Excel odkud tahat data. Také musíte povolit DDE rozhraní, Tomáš to popisuje ve svém článku (zde). Pokud nemáte účet u IB, můžete použít uživatelské jméno edemo a heslo demouser. Poběží to v demo módu a místo reálných dat to generuje náhodná data, ale pro účely testování je to naprosto dostačující.

Nachvilku se ještě vrátím ke vzorovému XLS, které IB poskytuje na stránkách a Tomáš o něm psal ve svém článku. V dnešním článku si ukážeme, jak replikovat funkce, které jsou ve vzorovém XLS obsaženy v prvním listu. Proč dělat něco, co už je hotové? Abychom si ukázali, jak vše funguje pod pokličkou. IB má v Excelu hodně kódu a pro neprogramátora je to nejspíš naprosto nepochopitelné. Já jsem sice programátor, ale ve VBA jsem dělal za svůj život minimálně, a proto mi taky chvilku trvalo, než jsem to všechno rozluštil. V následující ukázce si tak vytvoříme výrazně zjednodušený Excel, který nám také načte data z TWS, ale bude k tomu potřeba minimum VBA kódu.

Načítání dat do Excelu

Jak už jsem zmínil, propojení Excelu a TOS je záležitost jednoho příkazu. S TWS je to trošku složitější, protože potřebujeme příkazy minimálně dva. První příkaz vytvoří „ticker“, IB to nazývá vytvoření „control linku“ a druhým přistupuje k určitým datům z tickeru.

Na obrázku vidíte screenshot ze vzorového XLS od IB. Na první řádek jsem umístil titul MSFT, vyplnil těch pár dalších políček, které jsou potřeba a stiskl tlačítko “Request Market Data”. Nás nyní zajímá sloupeček s názvem “Ctrl” - ten obsahuje již zmíněný control link. Vidíte, že se v něm zobrazuje nula, ale když danou buňku označíte, v řádku formule se zobrazí DDE příkaz. V našem případě je tam: =edemo|tik!id1?req?MSFT_STK_SMART_USD_ISLAND. Co to všechno znamená? Znak "rovná se" na začátku Excelu říká, aby obsah buňky interpretoval jako formuli, ne jako obyčejný text. Dále tam máme „edemo“, což je jméno účtu, ke kterému se chceme v TWS připojit, „pipe“ znak („|”) Excelu říká, že předcházející řetězec je název DDE severu, ke kterému se má připojit a „tik“ značí „ticker“ DDE doménu, která se stará o všechno, co se týče dat. Pak tam máme identifikátor „id1“, „req“ znamenající požadavek (anglicky request) a dále je tam už jen specifikace titulu, pro který daný ticker vytváříme. My jsme chtěli symbol „MSFT“ typu „STK“ (stock – akcie), „SMART“ říká TWS platformě, že má použít funkci smart routing a sama určit burzu, kde se symbol obchoduje, „USD“ je měna, ve které se finanční nástroj obchoduje. Dále je v kódu v našem případě ještě primary exchange „ISLAND“. Jde o identifikaci primární burzy a kód je nezbytný v případě, že má více symbolů stejné jméno. V našem příkladu to používat nebudeme, protože jsem ještě nenarazil na případ, kdy by to bylo nezbytné.

Control link bychom měli, podívejme se dále, jak tedy konečně získat nějaká užitečná data:

Ve stejném řádku jako předtím jsem si označil buňku ve sloupci „Last Price“. Tato buňka streamuje cenu posledního spárovaného obchodu. Pokud se podíváme do řádku formule, je tam tentokrát: =edemo|tik!id1?last. První část příkazu je shodná. A co je důležité, musí být shodný i identifikátor, v našem případě “id1”. To je totiž identifikátor, pod kterým jsme vytvořili ticker a přesně k tomu tickeru se teď chceme připojovat. A pak už je tam místo “req” pouze “last”, čímž TWS říkáme, že chceme poslední cenu. Tento příkaz už je velice podobný tomu z propojení Excelu a TOS. Pokud bychom tam například místo “last” uvedli “bid”, tak nám to bude streamovat cenu poptávky. “ask” nám zase analogicky zprostředkuje cenu nabídky atd.

Praktické využití

Nyní už víme, jak vytvářet příkazy pro DDE. Pojďme si proto zkusit vytvořit takový náš jednoduchý Excel sheet. Uděláme si ho pro akcie, použijeme vždy SMART exchange a budeme automaticky předpokládat, že se titul obchoduje v dolarech. To vše proto, abychom do našeho XLS mohli zadat pouze akciový symbol a nic jiného. U každého symbolu si pak budeme zobrazovat velikost poptávky, cenu poptávky, cenu nabídky, velikost nabídky a poslední cenu (přesně jako ve vzorovém XLS).

Nejprve si vytvoříme v Excelu šablonu s rozvržením stránky, a pak si k tomu napíšeme VBA kód. Pro práci s kódem je třeba si zobrazit „Developer“ záložku v menu (v české verzi to bude asi něco jako „Vývojář“). Klepněte na tlačítko Office úplně nahoře vlevo, pak na tlačítko „Možnosti“. Měl by to být třetí checkbox. Vyberte záložku, která se vám tam nyní zobrazila a klepněte na „Vložit“ (nebo v anglicke verzi je to aspoň Insert) a vyberte tlačítko. Až ho vložíte do Excelu, tak zatím zrušte dialog přiřazení makra. To uděláme později. Vytvořte si podobné rozvržení Excelu jako je na následujícím screenshotu:

Tak a teď trošku programování. Našim úkolem bude naprogramovat funkci, která po stisknutí tlačítka projde řádek po řádku, načte jméno symbolu z prvního sloupečku, vytvoří control link do druhého a načte realtime data do sloupečků C až G. Budeme tedy provádět v podstatě tu samou věc dokola pro každý řádek, a na to se v programování používají cykly. My použijeme „While“ cyklus, který na začátku zkontroluje podmínku, a pokud podmínka platí, provede tělo cyklu. Podmínkou bude, že první sloupeček obsahující symbol nesmí být prázdný.
V záložce „Developer“ je v menu první tlačítko „Visual Basic“. Klepněte na něj a otevře se vám vývojové prostředí Visual Basicu. Z menu vyberte „Vložit“->“Modul“ (Insert->Module) a napište tam následující kód:

klikněte pro obrázek v plném rozlišení

Kód jsem zde vložil jako screenshot, aby to nezabíralo tolik místa. Vzorový XLS je k dispozici ke stažení na adrese www.financnik.cz/download/DDE_tutorial.zip a komu se nechce psát, může si kód odtud zkopírovat. Pak už stačí jenom kliknout pravým tlačítkem na naše tlačítko „Načíst data“ a přiřadit k němu makro „NacteniDat“ (to je funkce, kterou jsme si právě vytvořili). Potom už jenom nezapomeňte vyplnit správné uživatelské jméno a stiskněte ono tlačítko. Mělo by vám to načíst data řádek po řádku. Až se načtou všechna data, měla by se začít data streamovat. Někdy to chce trošku trpělivosti, než se všechna data zobrazí. Všechny ty zelené řádky v kódu jsou komentáře a kód je jednoduchý, takže by mělo být jasné, co se v tom VBA kódu děje.

Závěrem

Doufám, že aspoň někomu byl tento článek přínosný a objasnil trochu taje propojení Excelu s TWS přes DDE rozhraní. Byl bych rád, kdybyste do diskuzního fóra napsali, co vám v článku nebylo jasné, případně na jaký detail by bylo lepší se zaměřit podrobněji. Tento konkrétní příklad pracuje s akciovými tituly, ale měl by v principu stejně fungovat třeba i pro komodity. Jenom tam bude třeba v control linku uvést více informací. Nicméně pokud budete třeba kód přizpůsobit dalším trhům, stačí trochu prozkoumat vzorový XLS od IB (pro opce je například potřeba specifikovat dobu expirace, směr – PUT nebo CALL – atd.).

Přeji hodně štěstí s experimentováním, a pokud budou nějaké dotazy, pokusím se na ně odpovědět. Chtěl bych ale všechny poprosit, aby se neptali na otázky typu „Na co mi to je?“. Pokud to nevíte, je vám to nanic. Tento článek je pro lidi, kteří vědí, k čemu podobný kód použít, jenom potřebovali vysvětlení, jak přesně na to.

Autor: Petr alias gizmo
excel
7.4.2010

Finančník.cz
Přehled pokračovat Praxe
Sdílíme, co nám samotným funguje.
7 výukových lekcí.

Jak reálně uspět v tradingu?

Naučte se vydělávat na své sny (naše metody na Finančník.cz)

Praktický návod, jak v trzích získat šanci vydělávat stovky tisíc až miliony dolarů ročně bez vlastního kapitálu a nutností trávit denně hodiny před počítači (bez práce to ale nepůjde).
>> Získat kurz zdarma <<

Naposledy publikováno

Trading Room intradenní breakout

Časování návratu k průměru pomocí implikované volatility

Vyplnění formuláře 8-3-6 u Interactive Brokers Irsko

Lekce ze získání první alokace 30 000 euro

Shrnutí vývoje obchodování a výuky na Finančníkovi – update 2024/8

Jízda na dlouhém chvostu

Intradenní obchodování Bitcoinu

Síla diskrečního obchodování v systematických strategiích
Další články na toto téma

Základy Excelu - podmíněné formátování

Těžko asi najdeme tradera, který ke svému obchodování nepoužívá program Microsoft Excel. Rozhodli jsme se proto, že budeme na stránkách Finančníka občas publikovat samostatné články se zajímavými tipy pro práci s tímto programem.
V dnešním článku se podíváme na možnosti při používání logických příkazů při práci v Excelu. Je totiž rozdíl použití podmínek při formátu a podmínek při výpočtech, což někdy může uživatelům motat hlavu.
Veškeré logické příkazy v Excelu mají za úkol nám ulehčit práci v tom smyslu, že Excel za nás bude dle určitých parametrů hlídat hodnoty a podle zadaných kritérií je vyhodnocovat. Po jejich vyhodnocení provede následně příkazy, které mu nastavíme. Je to jakési pravidlo „AKCE -> REAKCE“. Důležité však je, o jaké příkazy jde a co pomocí nich chceme dokázat.
V případě formátu používáme logické podmínky hlavně, jak už název napovídá, u podmíněného formátování. V případě výpočtů je používáme u logických funkcí, bez kterých můžeme říci, že se trader ve svých tabulkách pravděpodobně nikdy neobejde.
Logika používaná pro formátování
Oproti „ručním“ úpravám grafiky buněk, kterému říkáme formátování, si v Excelu můžeme nastavit tzv. podmínky, při kterých Excel zformátuje data obsažená v jednotlivých buňkách přesně podle toho, co nadefinujeme. V Podmíněném formátování, které najdeme na kartě Domů, část Podmíněné formátování, najdeme spoustu možností jaké podmínky využít. Můžeme nastavit téměř jakoukoli podmínku. Mohou to být varianty od základních porovnání, kdy pokud bude splněna podmínka, např. hodnota v buňce bude „větší než“ zadané číslo Excel, zpracuje buňku tak, jak mu zadáme (jako parametr v připravené podmínce můžeme použít buď čistě číslo- „5“ - nebo můžeme použít i odkazy na jiné buňky - „A5“), až po složité podmínky, které musíme vyjádřit zadáním klasických funkcí. Ty používáme k nastavení všeho, čeho nelze dosáhnout pomocí předdefinovaných nástrojů podmíněného formátování.

Výběr možných podmínek je opravdu široký, Excel má už předpřipravené nejčastěji používané podmínky a to i pro různé formáty. Najdeme tu tedy podmínky pro práci jak s čísly, tak i textem, datem apod.
Nemusí však vždy nutně jít o porovnávání nebo jiné podmínky stanovené pomocí výpočtů. Předchozí varianta pravidel jsou jediná, která fungují jen v těch konkrétních buňkách, což znamená, že každá buňka je samostatně porovnána s kritérii a podle toho je následně zformátována. Všechna ostatní podmíněná formátování jsou závislá na zbývajících hodnotách buněk formátovaných podle stejných podmínek. Excel totiž umí a dokonce to i nabízí v nabídce mezi pro nejčastěji používané příkazy, vybrat z určité oblasti např. prvních 10 % hodnot. Prohlíží tedy určitou oblast tabulky a z té vybere a zformátuje dle připravených příkazů 10 % nejvyšších hodnot.
Při zadávání podmínek také není potřeba se jakkoli omezovat, pro buňky lze nastavit i více pravidel než jen jedno. Bohužel zde nepotěším uživatele starších verzí Excelu, protože do verze 2000 (a starší) lze pro buňky zadat pouze 3 podmínky. Proto jestli potřebujete pracovat s více podmínkami, doporučím upgrade na vyšší verzi. Osobně jsem však zatím nepoužil více jak dvě pravidla najednou…
Příprava logických podmínek u podmíněného formátování je však jen první část, druhou částí je nastavení toho, co má Excel provést po splnění takto připravených podmínek. Různé verze formátů nebo příprava vlastních formátů je ale spíše téma pro jiný článek.
Logika při práci s výpočty
Druhá oblast, ve které se setkáváme se zadáváním podmínek, jsou logické funkce. Jde však o dvě naprosto odlišné kategorie. Zatímco v předchozí části jsme sice zadávali podmínky, podle kterých má Excel cosi zpracovat, týkaly se tyto podmínky jen a pouze formátování těch konkrétních buněk. Při práci s logickými funkcemi ať už zadáme jakoukoli podmínku, nikdy tato podmínka nebude mít vliv na formátování buňky a samozřejmě naopak.
Při přípravě podmínek ve vzorcích nechceme po Excelu, aby něco formátoval, ale aby určité podmínky vyhodnotil a na základě toho nám vrátil buď PRAVDA či NEPRAVDA. Výsledky logických funkcí totiž nejsou běžné hodnoty, jako jsme zvyklí, ale pouze tyto dvě varianty. S těmi samozřejmě můžeme dále pracovat ať už za použití dalších funkcí nebo už přímo za použití funkce KDYŽ.

Je tedy nutné rozlišovat, pro co stanovujeme podmínky, zda nám jde o zformátování buňky (tedy barva, text, ohraničení apod.) nebo připravujeme výpočet (funkci) pro zjištění nějakého stavu.
Kombinace povolena
Je však potřeba dodat, že samozřejmě může nastat situace, kdy budete chtít připravit takovou podmínku pro formátování, kdy se nevyhnete nutnosti použít logickou funkci. Na druhou stranu je dobré, že to lze jen jedním směrem a to pouze vložením funkce do podmíněného formátování, opačně se to dá udělat těžko.
Závěrem trochu praxe
Zkuste si tedy ve svých backtesterech nejprve nastavit podmínky pro zformátování sloupečku s vašimi zisky a ztrátami tak, že ztráty budou červeně a zisky zeleně a ještě tučně. Například u této úlohy je nutné použít právě dvě podmínky. Podmínky tedy budou vypadat následovně:
1) Zvýraznit pravidla buněk -> Je větší než -> zde zadáme 0 a ve formátu zelenný text
2) Zvýraznit pravidla buněk -> Je menší než -> zde zadáme opět 0 a ve formátu vlastní, kde barvu textu zaměníme na červenou a tučné písmo
Tímto jsme přidali dvě pravidla, jež můžete následně vidět ve Správci pravidel a zde je i měnit. Zkuste sami vymyslet a upravit jedno z pravidel tak, aby byla zahrnuta i nula, kterou jsme teď vynechali…

A nyní vyzkoušíme logiku ve funkcích. Porovnejte si vstupy a výstupy a zjistěte, zda je PRAVDA, že máte výsledek větší než 0 nebo ne.
1) Do buňky vložte funkci = “adresa buňky s výsledkem“>0
Na těchto příkladech vidíte, že sice porovnáváme stejné věci, ale výsledek je vždy jiný…
Závěrem vám tedy přeji hodně zdaru při tvorbě podmínek pro formáty nebo komponování logických funkcí ve vašich tabulkách či backtesterech.

Kalkulace cost basis při částečném prodeji akcií

Řekněme, že nakoupím 100 akcíí firmy XY za 10 USD. Stojí mě to celkem 1000 USD, cost basis (průměrná cena za akcii) je 10 USD.
Za rok se cena vyšplhá na 50 USD za akcii, já prodám pouze část, třeba 30 akcií. Prodám tedy za 1500 USD.
Jak teď správně počítat cost basis zbývajících 70 akcií? Vychází mi to v negativních hodnotách a já si s tím nevím rady jak to správně trackovat v excelu :-(
Poradí někdo prosím jak evidovat průměrnou cenu za akcii v tomto případě?
Díky!

Základy Excelu – ověření správnosti zadávaných dat

Většině obchodníků se již patrně mnohokrát stalo, že si do svého obchodního deníku zanesli chybu vzniklou jednoduchým překlepem při zadávání vstupních dat. Řadě chyb se přitom dá předejít prostřednictvím jednoduché automatické kontroly vkládaných dat přímo v programu Microsoft Excel. Pojďme si dnes ukázat, jak konkrétně tuto kontrolu v Excelu využívat.
Ověření dat se používá k nastavení pravidel pro uživatelem zadávané hodnoty. Každému se někdy stane, že se přepíše. Při prohledávání a následném zpracovávání dat může pak takový překlep způsobit řadu nepřesností a následně i starostí či práce s jeho dohledáním.
Abychom těmto situacím předešli, můžeme si v našem backtestovacím deníku nastavit u buněk funkci, která nám ověří správnost vkládaných dat. Neumožní nám tak vepsat nic jiného, než co jsme si předem určili. Ať už to bude typ dat nebo konkrétní hodnoty.
Kde funkci najdeme a co chceme ověřovat?
Funkce Ověření dat se nachází na kartě Data, skupina Datové nástroje. Nabízí více možností, a proto si musíme nejdříve ujasnit, co konkrétně chceme ověřovat nebo omezovat.

Můžeme využít přednastavené možnosti jako je Čas, Datum, Celé číslo, Desetinné číslo, Seznam nebo Délka textu. Pomocí těchto kritérií omezíme například časové nebo kalendářní údaje mimo určené časové rozmezí, zobrazování jen kladných nebo záporných čísel atd. Pokud bychom chtěli ověřit něco jiného, můžeme využít poslední položku ve výběru, tj. Vlastní. U jednotlivých možností pak můžeme dále vybírat z Rozsahu a ten pak ještě dále upřesnit. Tato funkce nám tak poskytuje velké množství variant.
Při zadávání pravidel pro ověřování dat je dobré si nejdřív označit oblast, pro kterou chceme pravidlo nastavit (například si označíme buňky A1:C10). V případě, že máme ověření nastavené jen pro buňku A1 (zapomněli jsme oblast označit nebo chceme nastavení změnit až v budoucnu), stačí buňku A1 zkopírovat do požadované oblasti a ta se zkopíruje i s pravidlem pro ověření dat.
Jak ale poznám, na jaké buňky jsem ověření dat aplikoval?
Chcete-li vyhledat buňky, u kterých jste si ověření dat nastavili, stačí jít na kartu Domů. Tam ve skupině Úpravy najedete na položku Najít a vybrat (ikona dalekohledu) a pomocí šipky se zobrazí výběr, ve kterém klikněte na položku Ověření dat. Buňky, kde je ověření nastavené se zbarví světle modrou barvou. Abyste zjistili, jaké pravidlo jste si pro ně zvolili, je dobré si při nastavování ověření dat vepsat nápovědu, která se nám zobrazí při kliknutí na buňku. Tu také využijeme, když funkci ověření dat máme u více buněk v různých variantách a nepamatujeme si, jaká je u dané konkrétní buňky podmínka. Popis pravidla by proto měl být jednoznačný.
V nastavení je k dipozici i možnost zadat chybovou hlášku. Ta nám vypíše zprávu, která se zobrazí při vepsání chybné hodnoty. Opět máme na výběr z několika variant – stop, varování a informace. Každá varianta má jiné uplatnění. Stop nám zakáže zadat do buňky nesprávná data. Naopak Varování nám nesprávná data dovolí zadat, ale upozorní nás na to. Podobně jako Varování funguje i poslední varianta Informace.

Jak ověření dat odeberu?
Chcete-li ověření dat odebrat, můžete si zvolit, zda tak učiníte jen pro určitou konkrétní buňku, nebo pro všechny buňky se stejným pravidlem.
Pro odstranění ověření dat z jedné buňky ji stačí vybrat a poté kliknout na Ověření dat (karta Data, skupina Datové nástroje). V možnosti Nastavení, kde se zadává pravidlo pro ověření je možnost Vymazat vše. Tak se rychle zbavíte všech nastavených ověření pro jednu konkrétní buňku, na které jste se zrovna nacházeli.
Pokud chcete vymazat ověření dat ze všech buněk se stejným pravidlem, stačí zaškrtnout „Použít tuto možnost u všech ostatních buněk se stejným nastavením“. Pak opět klinout na Vymazat vše.
Na závěr trochu praxe
Pro lepší představu, jak tuto funkci MS Excelu použít ve svém backtestovacím deníku, si nyní zkusíme nastavit ověření dat pomocí Seznamu. Jedná se o možnost zadávat do buněk jen předem nadefinovaná data a nic jiného. Tak se můžeme vyhnout nejen překlepům, ale ušetříme si tím i čas s vepisováním delšího textu.
Začnete tím, že si na libovolném z listů Excelu vytvoříte seznam patternů, které používáte. Oblast, na kterou chcete ověření dat aplikovat, označíte myší (v našem příkladu zapisujeme patterny od buňky B8 dolů). Poté kliknete na Ověření dat (karta Data, skupina Datové nástroje). Zde zvolte možnost Seznam a vyberte data z vytvořeného seznamu patternů (v našem příkladu A2:A5).
Chcete-li, můžete si nastavit nápovědu nebo i chybovou hlášku. Poté nastavení potvrďte. Nyní, když kliknete na buňku, kde jste pravidlo nastavili, zobrazí se šipka dolů a po jejím rozkliknutí seznam patternů. Stačí vybrat ten správný.
Pomocí této funkce můžete ušetřit nejen čas při zápisu, ale i při opravování chybně zadaných názvů.

Chcete-li si více osvojit práci v programu MS Excel, rádi vás uvidíme na našem celodenním workshopu Kurz práce s Microsoft Excelem pro tradery. Workshop je určen pro malou skupinu účastníků, kteří pracují na svých počítačích za průběžné asistence a výkladu lektora. S konkrétními dotazy ohledně kurzu se můžete obracet i na e-mail kurzy@financnik.cz