Výroba makra pomocí skriptu a základní poznatky o jeho zapsání pomocí VBA (kde a jak), operace s buňkami a jejími oblastmi, práce s listy excelovského sešitu, cyklická funkce a první analýza historických dat (prozatím intradenní držení akcií) pomocí velmi jednoduché automatizace práce vytvořeným VBA skriptem. To by mohlo být shrnutí dosavadních článků o snaze ulehčit si práci s testováním historických dat nad rámec klasických nástrojů Excelu. Je velice nepravděpodobné, že by mi stačilo ke spokojenosti vyhodnocení intradenního obchodu na akciovém titulu, tak jak jsem svou první mechanizovanou strategii popsal v minulém článku, budu chtít samozřejmě daleko více, abych nakonec využité poznatky využil k testování složitějších obchodních úvah, zejména vymyslet, jak analyzovat například zamýšlené opční obchody. Musím však své poznání možností jazyka VBA ještě rozšířit a zdokonalit svůj skript tak, abych mu nejen lépe porozuměl, ale hlavně jej efektivněji využil. Jenom tak si budu schopen svá napsaná makra přizpůsobit nejrůznějším vlastním nápadům, jejichž „ruční testování“ by mi zabralo třeba desítek hodin času.    

   Chci se stále držet proklamace, že nebudu teoretizovat a budu pouze konkrétní. Budu tedy pokračovat ve zdokonalení mého již vytvořeného skriptu, kterým jsem provedl analýzu jednodenního obchodu na akciích Goldman Sachs za období posledních pěti let na datech stažených z Yahoo.finance. Vstup na Open obchodního dne a výstup na Close stejného dne není nic, co bych pravděpodobně v praxi prováděl a samotný jednoduchý test přinesl výsledek – ztrátu ve výši -8.073 USD. Zkusím tedy nyní do svého testu zakomponovat jiná časová období držení akciového obchodu a pokusit se již vyrobit také nějaké srovnání jednotlivých přístupů. Co mohu prozatím z popsaných znalostí o psaní VBA shrnout? Umím již vytvořit jednoduchý skript, který umí základní práci s listy a buňkami excelovského sešitu, vím, co jsou proměnné a jak funguje funkce jednoduchého cyklu. Požadované operace a jednotlivé výpočty jsou potom po každém spuštění prováděny tak, jak jsou chronologicky za sebou napsány jednotlivé řádky skriptu, pokud například nenarazí na příkazy k cyklické funkci.

   Provedením požadovaného počtu opakování nebo například splněním nějaké podmínky se cyklická funkce ukončí a výpočetní operace pokračují až do vyčerpání celého napsaného skriptu. Do skriptu si pak mohu psát nejrůznější poznámky a texty, uvozené znakem apostrofu, které jsou VBA ignorovány. Takto funguje prozatím mé aktuálně napsané makro.

   V dnešním článku provedu další vylepšení napsaného skriptu o procedury, které mi ulehčí práci s analyzovanými daty a povedou ke zpřehlednění jejich výsledků. V tomto článku nyní budu postupovat naopak oproti předchozím článkům, a to tak, že ukážu celý rozšířený skript dopředu a popíšu, co jsem do skriptu z minulého článku doplnil, proč jsem tak udělal a co to vlastně znamená. Protože je skript již rozsáhlejší, rozdělil jsem jej do několika částí tak, jak jdou jednotlivé řádky za sebou, tyto části jsem pak oddělil zeleným textem vytvořený z čísla popisované části.

   Mám nyní na analýzu tyto požadavky. Na stažených datech z Yahoo.finance chci provádět zjištění, jak by se mi vedlo, pokud bych nakupoval a prodával akcie v různých časových intervalech. Každou analýzu si chci pojmenovat (abych zhruba věděl, co jsem vlastně testoval) a také chci mít možnost měnit počet držených akcií nebo výši poplatků za mé obchody, takto vytvořenou analýzu chci přenášet na zvláštní list mého sešitu, kde uvidím její základní parametry (datum vstupu, výstupu, vstupní cena….celková equity analýzy). Pouze celkový výsledek této analýzy pak chci přenést na další list sešitu, abych ji mohl srovnávat z dalšími předchozími analýzami zpracovanými podle jiných parametrů a vyhodnotit, jak si aktuální zpracovávaný úkol vede. Na dalším listu si pak chci tyto srovnání vyhodnotit graficky ve formě přednastaveného grafu. Takto zpracovaný excelovský sešit s napsaným skriptem je možné stáhnout z tohoto umístění, při rozbalování souboru je nutné potvrdit „Povolit Makra“

1/ Nastavuji listy

   Popis skriptu začínám napsáním názvu makra TestujiAkcie a nastavením listů.  

   Mohu pak vypozorovat, že jsem si přidal do svého sešitu dva nové aktivní listy, na kterých mohu provádět nějaké výpočetní operace, a to list Start a list Grafy. Tyto nové listy jsou samostatné objekty, které nadeklaruji jako proměnné Dim, abych je v dalším odstavci nastavil příkazem Set, nic nového, toto jsem již popisoval v minulém článku. Na novém listu Grafy pak budu chtít pozorovat srovnání jednotlivých analýz, list Start jsem si pak vytvořil jako úvodní list analytického sešitu, ze kterého bych chtěl zadávat základní charakteristiky testování mých stažených dat.

   Mohu tak vypozorovat, že mám dva nové listy (šipky) a přehlednější zadávací tabulku pro měnění parametrů zpracovávaných dat. Nyní již vím, že jednotlivé položky tabulky budou také proměnnými, které si později nadeklaruji, abych pomocí jejich změny mohl měnit parametry analýz. Mohu tak zadávat a měnit pro každé spuštění makra Název analýzy, Ticker, Počet akcií, Poplatky a Počet dnů trvání obchodu. Na plochu listu jsem si také vyrobil tlačítko na spouštění aktuální analýzy (modré) a tlačítko ke smazání listu Celkem, kde budu shromažďovat pouze sumarizace jednotlivých provedených testů, samotné jednotlivé analýzy na listu Backtest jsou mazány po každém spuštění makra, takto nastaveno to mám již z minula a nic jsem na tom neměnil.  

2/ Deklaruji proměnné a mažu předchozí analýzu

   Spuštěním makra, které přečte a nastaví listy mého sešitu, postoupí čtení a zpracování skriptu do stavu, kdy chci definovat názvy a tvar proměnných, se kterými budu následně pracovat.

   K původním proměnným z minulého článku přibyly další nově zadávané, jako je například proměnná Analyza definovaná jako String, tedy textový údaj, do které budu načítat hodnotu z buňky z listu Start, která bude krátce popisovat, co je předmětem právě zadávaného testování. Další proměnná PocetDnuObchodu deklarovaná jako Integer pak například bude číslo vyjadřující počet dnů, kdy chci držet nakoupené akcie, které se bude opět načítat z buňky z listu Start. Takto mám pak nadeklarovány ještě nově další proměnné VolnyRadek, VolnySloupec a Vysledek, se kterými budu pracovat později v textu…Druhou červenou šipkou je pak zapsán skript pokynu k mazání předchozího backtestu na listu Backtest, každé spuštění makra pak má zajištěno, že se jeho výsledku zapisují na prázdný list Backtest, toto zůstalo zachováno z minulého sešitu.

3/ Načítám první proměnné a tisknu hlavičky přehledů

   Mám stažená data, nastavené listy sešitu a řekl jsem svému počítači, jaké proměnné budu při psaní skriptu dále používat, aby se podle toho zařídil, také jsem zajisttil smazání poslední prováděné analýzy na listu Backtest. Pokud čtení skriptu dorazilo do této fáze, další napsané řádky budou znamenat již postupné načítání hodnot proměnných z buněk mých excelovských listů nebo zápisy do některých buněk „natvrdo“ požadovanými texty a daty.

   Hodnotu proměnné AktualniRadek nastavuji „napevno“ na hodnotu čtyři (1). číslo čtyři bude dále znamenat, že má stažená data na listu Data začínají na čtvrtém řádku.

   Toto je nastaveno z posledního skriptu a není to žádná změna. Je také jasné, že pokud bych chtěl, aby má analýza nezačínala datem 6.7.2015, ale například až 13.7.2015, stačí změnit hodnotu AktualniRadek na hodnotu řádku +9 a testování dat tak začne až od tohoto nového časového okamžiku v minulosti…Další část napsaných řádků skriptu z tohoto oddílu již znamená načtení hodnot dalších proměnných

    Proměnné s již deklarovanými názvy a tvary Analyza, Ticker, PocetAkcii, Poplatky a PocetDnuObchodu jsou vybaveny hodnotami z buněk z listu Start (StartSheet.Cells.), tedy z jakéhosi nově vytvořeného ovládacího panelu celého sešitu, který jsem umístil na nově vytvořený list Start. Proč má PocetDnuObchodu hodnotu sníženou o jedna (2)? Pokud budu chtít analyzovat držení obchodu například tři dny a toto zapsat na listu Start do buňky „Počet dnů trvání obchodu“ zápisem čísla tři, znamená to, že můj obchod začne na řádku s označením například 15.9.2017 a z něj vyberu data pro vstup do obchodu – to je první den obchodu, druhý den obchodu 16.9.2017 nevybírám nic (obchod trvá) a obchod ukončuji třetí den 17.9.2017 a z tohoto řádku vybírám data pro ukončení obchodu, data tohoto obchodu se ale nacházejí na druhém řádku níže než je řádek pro vstup do obchodu a nikoliv na třetím, který reprezentuje mé číslo trvání obchodu vyplněné do zadání analýzy, proto odečítám jedničku. Intradenní požadavek na testování pak znamená zápis do buňky „Počet dnů trvání obchodu“ vložením číslice jedna, proměnná PocetDnuObchodu pak bude mít hodnotu o jedna nižší, tedy hodnotu nula. Znamená to, že se budu pohybovat s výběrem dat na stejném řádku svých stažených dat. Poslední část skriptu z obrázku popisuje tvorbu hlavičky přehledů na požadované listy, které analýza poté naplní daty a testovanými výsledky.

   Vyplnění hlaviček přehledů začíná v části (3). Prvních šest řádků se týká listu Backtest, na který budou přenášeny výsledky aktuální analýzy a znamenají „natvrdo“ napsaný text do konkrétních buněk prvního řádku listu a znamenají pojmenování jednotlivých sloupců. Zajímavý je sedmý řádek (4), který definuje název sloupce na listu Backtest, kde se bude načítat celkový výsledek aktuální analýzy. Tento název sloupce je tvořen složeninou dvou proměnných Ticker a Analyza spojených pomocí znaků ampersand (&) a má tvar Ticker & “ “ & Analyza. Vložené uvozovky mezi ampersandy pak vytváří mezeru mezi těmito výrazy. Protože aktuální hodnota proměnné Ticker je „GS“ a hodnota proměnné Analyza je „tři dny“, bude v této buňce výraz „GS tři dny“. Osmý řádek skriptu (5) pak umisťuje do druhého řádku (buňky B2) slovo „Celkem Backtest“ a bude to v budoucnu znamenat, že na tomto řádku budu moci umisťovat celkový výsledek testu vyjádřený jedním číslem. Pokud tedy provedu nějaký test, vytvoří se mi na listu Backtest tato formace dat.

   Vyplněná hlavička formuláře společně se sumarizací „Celkem“ na druhém řádku po naplnění daty pak vypadá podle vyobrazení výše. Výsledek v červeném obdélníku je vynesen právě na druhý řádek a znamená výsledek aktuálního testu. Umístění do tohoto místa může vypadat podivně, ale je to hodnota poslední buňky v tomto sloupci, která se při testování většího objemu dat může nacházet na x-tisícátém řádku tohoto sloupce, bylo by pak nepohodlné rolovat listem dolů až k takto zjištěné hodnotě poslední zaplněné buňky tohoto sloupce (její zjištění samozřejmě popíšu dále v textu). Mohu tak zjistit, že opakování třídenního držení 100 akcií GS by vyneslo za posledních pět let 2.468,01 USD i se zaplacenými poplatky.

   Poslední dva řádky skriptu v tomto oddíle vytvářejí hlavičku formuláře na listu Celkem, kde budu chtít shromažďovat pouze konečné výsledky z listu Backtest.

   Poslední dva řádky skriptu umisťující texty na list Celkem (CelkemSheet.Cells.) jsou označeny (6). Vyplněná hlavička listu Celkem i s načtenými daty šesti posledních pokusných analýz pak bude například vypadat následovně.

   V červeném obdélníku jsou zadané texty podle posledních dvou řádků skriptu. Vpravo jsou již načtena data výsledku jednotlivých analýz i s jejich sumarizacemi tak, jak je uvidím po postupném testování jednotlivých přístupů.

4/ Cyklus Do While…Loop s úpravou

   Pokud čtení napsaného skriptu proběhne bezchybně až do okamžiku z předcházejícího posledního obrázku z načtením textů a hodnot do hlaviček přehledů, narazí na cyklickou funkci VBA, kterou jsem již popisoval v minulém článku a která je „motorem“ celého skriptu, protože zpracovává načtená data ze stránky Yahoo.finance v mém sešitu pomocí operací s proměnnými, které na sebe berou podobu požadovaných buněk podle zadaných parametrů každého jednotlivého cyklu. Co cyklus, to výpočet pro zadanou sérii dat vtělených do proměnných a zápis výsledků na požadované místo v mém sešitu. Po vyčerpání všech dat se cyklus zastaví. Tento skript cyklu pak může vypadat jako na obrázku níže a jeho jednotlivé části rozeberu postupně.

   Zadaný příkaz Do While jsem již popisoval v minulém článku s tím, že cyklus se zastaví, pokud bude splněna nějaké podmínka. Tato podmínka byla definována nalezením první prázdné buňky na listu Data ve sloupci B a jednoduše znamenala, že jsem vyčerpal ke zpracování všechna data, která jsem si do sešitu stáhl. Zápis skriptu z minulého článku jsem si ale musel nyní upravit (1), protože se týkal pouze analýzy, kdy jsem zkoušel zjistit, jak by se chovalo nakupování a prodávání akcií GS v jeden den (nákup na Open a prodej na Close). Pohyboval jsem se tak pouze na jednom řádku mých stažených dat na listu Data.

   Testování podmínky na prázdnou buňku tak skončilo opravdu vyčerpáním všech řádků ze sešitu Data ihned poté, kdy jsem zpracoval poslední řádek mých stažených dat.      

   Poslední zpracovaná data při intradenním držení akcií GS z minulého článku jsou z řádku 1262, poté se cyklus posunul o jeden řádek na řádek 1263 (modrá buňka), aby procedura Do (While)…Loop reagovala splněním podmínky na existenci prázdné buňky v tomto nové řádku níže ukončením cyklu a pokračováním makra po vyčerpání poslední várky zpracovávaných dat. Nyní však netestuji intradenní držení, ale například držení akcií v čtyřdenním intervalu, a tento požadavek pak pro můj skript znamená, aby dotaz na prázdnou buňku přišel dříve, protože čtyřdenní obchodní perioda znamená pro cyklické načítání čtyřdenních dat například tuto situaci.

   Má stažená data jsou nyní skriptem zpracovávána pro požadavek testování na čtyřdenní držení. Poslední obchod, který mohu otestovat je ten, který začíná 26.6.2020 (pátek) na Open (1), protože mám k dispozici data také pro vystoupení z obchodu dne 1.7.2020 (středa) ve čtvrtý den jeho držení do Close (červené obdélníky). Další obchod bych tak začínal 2.7.2020 (2), k němu ale již data k dispozici pro výstup z obchodu nemám a analýza by považovala za výsledek obchodu nákup za pořizovací cenu, ale jako prodej by byla započítána nula což by vedlo k obrovské ztrátě způsobené nákupem za peníze, ale prodejem „za nic“. Takový obchod již nesmím svému backtestu povolit, proto musí přijít dotaz na prázdnou buňku (3) k ukončení analýzy v okamžiku vstupu do posledního možného testuschopného obchodu, který má také k dispozici uzavírací cenu, tedy v okamžiku (1). Musím se tedy zaměřit na tyto části skriptu.

   Zápis řádku skriptu (1) mi tedy nyní říká, že pro první cyklus je hodnota proměnné AktualniRadek na úrovni +4, tedy data začínají na čtvrtém řádku a k nim přičítám pro první cyklus čtyřdenního backtestu hodnotu proměnné PocetDnuObchodu s hodnotou čtyři sníženou o jedničku, tedy s hodnotou +3. Můj první dotaz na prázdnou buňku a ukončení běhu cyklu je tedy pro buňku na sedmém řádku ve druhém sloupci, která prázdná není, proto cyklus běží dál až do řádku předcházející instrukci Loop (2). Tam je pro další cyklus zvýšena hodnota proměnné AktualniRadek o hodnotu proměnné PocetDnuObchodu a ještě zvýšená o jedničku, tedy nyní +7 + 3 +1 na hodnotu +11. Instrukcí Loop se cyklus vrátí na řádek s příkazem Do While. Znamená to, že druhý cyklus již bude testovat ukončení cyklu dotazem na prázdnou buňku nalézající se ve druhém sloupci na řádku podle zadané podmínky AktualniRadek + PocetDnuObchodu, tedy v řádku označeném +11, ten také prázdný není a cyklus se opakuje až do konce testovaných dat. Tento konec čtyřdenního testování mých stažených dat podle takto zadaných podmínek byl ukončen tak, jak je patrné na obrázku níže z listu backtest.          

  Posledním obdobím, pro které mám k dispozici kompletní data pro vstup i výstup z obchodu, a která testuji v čtyřdenních intervalech, je období 26.6.2020 – 30.6.2020, které by znamenalo ztrátu s poplatky -140 USD a způsobilo, že by pětileté opakování třídenních obchodů vyneslo profit -2468 USD. Proč byl ukončen backtest tímto posledním obchodem právě tady je vyobrazeno na obrázku níže.

   Podle napsaného skriptu byl tento řádek datem 26.6.2020 a ukončením 30.6.2020 vybrán z řádku dat s pořadovým číslem 1258 (zelená šipka). Po provedeném úkonu – výpočtu hodnot, byla před procedurou Loop zvýšena hodnota tohoto řádku o +3 (PocetDnuObchodu) a ještě plus jedničku na hodnotu 1262, v obrázku výše se skriptem označeno (2), aby se cyklus vrátil na řádek Do While, kde byla testována podmínka prázdnosti buněk pro buňku ve sloupci dva (B), který má aktuální hodnotu ještě dále zvýšenou o +3 (PocetDnuObchodu), v obrázku výše se skriptem označeno (1), nyní je tedy dotaz směrován do buňky na řádku 1265 (modrý obdélník). Tato buňka je prázdná a s ní celý řádek, proto se cyklus ukončil a makro tak mohlo běžet dál po provedení všech úkonů vepsaných do cyklu. Objasněním podmínek ukončování cyklu formou dotazu na prázdné buňky, které je zobrazeno na posledním obrázku s napsaným skriptem, bych mohl navázat dalším popisem skriptu s předpokladem, že prozatím neexistuje prázdná buňka, která by ukončila cyklus, ale začínají se provádět samotné procedury výpočtů a načítání proměnných uvnitř cyklu.

   Pokud je povoleno, aby cyklus stále probíhal, je v řádcích skriptu označených (3) vyznačeno, jakou mají hodnotu proměnné VstupniDatum, VystupniuDatum, VstupniCena a VystupniCena za situace, kdy je hodnota proměnné AktualniRadek na úrovni +4 (zde začíná první řádek s daty). Je patrné, že jsou tyto hodnoty načteny ze sešitu Data (DataSheet.Cells.). Pokud bych předpokládal, že nyní testuji například třídenní držení akcií a jsem v prvním cyklu, kdy AktualniRadek je čtyřka, potom by čtyři první řádky znamenaly tyto hodnoty.

   VstupniDatum má hodnotu DataSheet.Cells(AktualniRadek, 2), konkrétně hodnotu ze čtvrtého řádku a druhého sloupce (1). VystupniDatum má hodnotu DataSheet.Cells(AktualniRadek + PocetDnuObchodu, 2), tedy hodnotu z šestého řádku, protože AktualniRadek má nyní hodnotu +4 a PocetDnuObchodu pro třídenní držení je +3 sníženo o jedničku (přesně +2), tedy konkrétně hodnotu ze šestého řádku a druhého sloupce (2). Obdobně pak pro VstupniCenu (3) a VystupniCenu (4). Načtením těchto čtyřech proměnných při prvním cyklu z těchto buněk bude v každém cyklu nahrazeno načtením dalších dat reprezentující třídenní posun v datové řadě na nižších řádcích. Tyto proměnné (a výpočty s nimi) pak budu chtít přenést do listu Backtest, kde si budu chtít zobrazit jednotlivé obchody v přehledu, ke kterému jsem si již vytvořil hlavičku podle textu výše. K takovému přenášení jsem si nejdříve vybral místo v listu Backtest tímto skriptem.

   Řádek skriptu (4) se snaží najít hodnotu pro nadefinovanou proměnnou VolnyRadek nadeklarovanou v úvodu článku. K čemu ji potřebuji? Pokud jsem testoval intradenní obchod v minulém článku, tak jsem přenášel jednotlivé vypočtené hodnoty z analýzy přímo na list Backtest a tam jsem mohl vidět jeho přehled. Vše se ale odehrávalo na jenom řádku, takže výsledky jednotlivých obchodních případů opravdu byly seřazeny pod sebou a mezi jednotlivými řádky nebyly mezery. Pokud bych to takto udělal například u testování třídenního držení akcií, přenesly by se mi tyto výsledky na list Backtest s nevyplněnými řádky mezi výpočty, a to by bylo velmi nepraktické. Budu tedy výpočty z cyklických operací mít na listu Backtest přeneseny tak, aby každý nový zápis byl proveden do prvního prázdného řádku a mezi řádky tak nevznikaly mezery. K takovému zápisu mi poslouží zjištění hodnoty čísla prvního volného řádku v zadaném sloupci právě tímto jednořádkovým skriptem. Metod, jak zjistit první prázdnou buňku je více (například pomocí cyklické funkce For…Next s vnořenou podmínkou), ale tento dynamický zápis je opravdu skvělým řešením. Tento jednořádkový skript pak sděluje číslo takového řádku takto

= BacktestSheet.Cells(Rows.Count, 2).End(xlUp).Row + 1

   Mohu si jej vyložit jako „…na listu Backtest hledej ve sloupci 2 první zaplněnou buňku tak, že začneš od nejspodnější buňky, kterou máš v tomto sloupci a postupuješ v hledání ve sloupci směrem vzhůru, až na ni nenarazíš…“, poté k hodnotě první zaplněné buňky přičtu jedna a mám první volnou buňku ve sloupci dvě (B). V mém konkrétním případě pak první zápis výsledku prvního cyklu Do While…Loop bude v tomto umístění

     První plnou buňkou odspodu ve sloupci dva (B) na listu Backtest je buňka na druhém řádku, protože je v ní napsáno „Celkem Backtest“ z předpřipravené hlavičky přehledu o právě prováděné analýze, první volná buňka v tuto chvíli je tak ve třetím řádku. Hodnota proměnné VolnyRadek je tak nyní v prvním průběhu cyklu na hodnotě +3. Můj první zápis obchodu z prvního cyklu tak bude nasměrován do třetího řádku, který je momentálně prvním volným řádkem, v dalším cyklu pak tento jednořádkový skritpt zjistí, že VolnyRadek má hodnotu +4, protože již bude existovat na řádku 3 zápis výsledku obchodu z prvního cyklu. Tyto jednotlivé zápisy výsledků podle jednotlivých cyklů pak budou vypadat následovně.

   Zápisy jsou obdobné jako u skriptu z minulého článku, pouze je směrování do buněk na list Backtest řízeno hodnotou VolnyRadek, aby byl zajištěn zápis vždy do momentálně prvního volného řádku. VstupniDatum tak bude vyplněn do buňky BacktestSheet.Cells(VolnyRadek, 2), pro první cyklus to bude do buňky BacktestSheet.Cells(3, 2) tedy B3, jako první označené buňky v prvním volném řádku, ve stejném smyslu pak budou vyplněny i další buňky volného řádku dalšími požadovanými zápisy výpočtů podle skriptu v části (5). Spuštění makra a provedením všech cyklů se provede načtení všech dílčích obchodů do sešitu Backtest tak, že pak nebude mezi žádným řádkem mezera.

   Zápisy do prvních volných řádků zejména ošetřují snadnou sumarizaci celého Backtestu v posledním sloupci, kam se načítá celková equity obchodů.

5/ Zjišťuji celkový výsledek Backtestu

   Po vyplnění všech řádků na listu Backtest, které představují jednotlivé dílčí obchody podle provedených cyklů, budu chtít mít jednoznačnou představu, jak takový test dopadl vyjádřením v konečné sumě vyjádřené jednočíselným údajem. Jedná se o zjištění tohoto čísla.

   Toto konečné číslo jsem si v úvodu skriptu již nadeklaroval jako proměnnou s názvem Vysledek. Protože v posledním sloupci na listu backtest postupně sčítám hodnoty jednotlivých výsledků, je tímto číslem poslední vyplněná buňka v tomto osmém sloupci (H) po ukončení konkrétní analýzy. Hodnotu takové buňky, tedy hodnotu proměnné Vysledek, mohu opět zjistit jednořádkovým skriptem

   Jednořádkový skript (1)

=BacktestSheet.Cells(65536, 8).End(xlUp).Value

   pak mohu přečíst jako „…hledej hodnotu první zaplněné buňky odspodu od řádku 65536 ve sloupci číslo 8 směrem vzhůru…“ Hodnota 65536 je maximální počet řádků v Excelu 2003, předpokládám, že nyní nebude nikdo dělat takové testy, které by měly třeba milion řádků (pak by se jenom číslo upravilo na milion). Podle předcházejícího obrázku se hledaná hodnota nachází na řádku 421 a její výše je 2.468,01. Touto hodnotou vybavím hodnotu proměnné Vysledek a bude výsledkem skriptu v řádku (1). Hodnotu výsledku analýzy vtělenou do proměnné Vysledek pak nechám zapsat do buňky H2, což jsem provedl zápisem skriptu na řádku označeném (2)

6/ Vytvářím list Celkem

   Pokud skript úspěšně projde všemi cykly a provede zjištění konkrétního výsledku ve formě jednočíselného údaje, budu mít na listu Backtest načteny výsledky konkrétně zadané analýzy včetně uvedení konečného čísla výsledku analýzy. Tato analýza má poslední sloupec s postupně načítanými výsledky tak, jak by se vyvíjel celkový výsledek takového postupného investování se zjištěním celkové konečné hodnoty. Právě tento sloupec bych si chtěl přenést do listu Celkem, abych jej mohl porovnávat s dalšími následnými analýzami.

   Na listu Celkem již mám také připravenou hlavičku přehledu pro takovou sumarizaci, kterou jsem si vložil skriptem před začátkem cyklu (v textu výše).

   Mým úkolem nyní bude si první takový souhrnný sloupec z listu Backtest přenést právě na list Celkem, a to do prvního prázdného sloupce zleva na tomto listu, tedy podle obrázku výše do sloupce B. Mým úkolem nyní bude nalezení prvního prázdného sloupce napravo od připravené hlavičky formuláře, který ještě není zaplněn daty.

  S vyhledáním prvního volného sloupce mi opět vypomůže jednořádkový skript, který bude mít za úkol zjistit číslo prvního takového sloupce. Toto číslo pak bude načteno do proměnné s názvem VolnySloupec, kterou jsem si nadeklaroval již v úvodu článku. Opět tak mohu jednořádkový skript

=CelkemSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 1

   přečíst jako „…hledej na listu Celkem v prvním řádku první zaplněný sloupec, a toto hledání proveď od konce listu směrem doleva…“  K takto nalezenému číslu prvního plného sloupce hledaného zleva přičtu hodnotu jedna, abych obdržel hodnotu prvního prázdného sloupce. Této hodnotě se pak bude rovnat hodnota proměnné VolnySloupec. Pro zápis prvního backtestu pak bude prvním zaplněným sloupcem v prvním řádku sloupec č.1 (A), protože je v něm napsán text hlavičky přehledu „Název testu“, proto bude hodnota proměnné VolnySloupec nyní pro první zápis po přičtení jedničky na úrovni +2.

   Kopírování sloupce H do listu Celkem do míst prvního prázdného sloupce pak provedu jednořádkovým kopírovacím skriptem (2).

   Oblast, do které směruji kopírované buňky má pak tvar Destination:=CelkemSheet.Cells(1, VolnySloupec), tedy napoprvé do sloupce č. 2, protože taková je nyní hodnota proměnné VolnySloupec. Mohu pak po prvním spuštění makra vypozorovat, jak se do listu Celkem zapíše pouze sumarizace aktuálně prováděného backtestu.

   Každá jednotlivá analýza prováděná spuštěním celého makra je pro kontrolu, že vše proběhlo v pořádku, zakončena vyskakujícím oknem, sdělujícím výsledek konkrétní analýzy

   Je patrné, že jsem k textu oznámení „Výsledek Backtestu je“ pomocí znaku ampersand přidal hodnotu proměnné Vysledek. Konec každé analýzy tak bude ohlašovat okno například takového tvaru.

    Kliknutím na OK pak okno se zprávou uzavřu a makro se ukončí.  

   List Celkem pak nepodléhá žádnému automatickému mazání při každém novém spuštění tohoto makra tak, jak je tomu u načtených dat do listu Backtest (každý konkrétní test), takže další analýza s nějakými jinými parametry se pak zapisuje vždy do dalšího volného sloupce. Mohu pak po pěti bleskově provedených analýzách s různou dálkou trvání a pojmenováním například vidět tento výsledek zápisu jejich sumarizace.

   Pětiletý datový soubor pro akciový titul GS by pak podle doby držení přinesl tyto výsledky. Pro mazání tohoto listu Celkem jsem si vytvořil samostatné makro.  

   Nadeklarováním a nastavením sešitu Celkem mohu jednořádkovým skriptem vymazat oblast buněk na listu Celkem. Tomuto mazání jsem přiřadil samostatné tlačítko na listu Start.

    Výsledek testování si mohu vizualizovat na listu Grafy, kde mohu v předpřipravené šabloně pozorovat sumarizovaná výsledná data načtená do listu Celkem.

   Protože jsem se pohyboval na jednom pětiletém datovém vzorku cen akcií GS a testoval různě dlouhé držení obchodů, tak je zřejmé, že nejvíce obchodů by bylo intradenních a nejméně například desetidenních, takže křivky grafu mají nestejnou délku a při větším počtu jednotlivých testů by se pohled na takový spojnicový graf stal nepřehledný. Mnohem lepší vizualizaci pak dává sloupcový graf, kde jsou tyto nestejné počty obchodů eliminovány a jsou porovnávány pouze celkové výsledky.

   Není jednoduché nějakou srozumitelnou formou popsat tvorbu takového skriptu, aby se čtenář v textu neztratil a nevytratil se smysl a logika jednotlivých programátorských kroků. Přestože to tak nevypadá, tak celý skript popisovaného makra je poměrně krátký a zabírá celkově šedesát řádků, pokud neberu v úvahu řádky jednotlivých popisků označených zeleně. Z těchto šedesáti řádků tvoří 24 řádků (tedy téměř polovina) pouze deklaraci proměnných a jejich typů a nastavení listů sešitu. Zbylých 36 řádků pak představují samotné výpočetní operace a další úkony, což opravdu není mnoho a mohlo by to demonstrovat sílu a značné možnosti skryté za pochopením základních znalostí jazyka VBA. Tyto možnosti se pak budu snažit rozšířit v dalším článku…:c)    

   Komentáře a příspěvky k tomuto článku prosím směrujte do Diskuzního fóra do tohoto vlákna k tomuto článku nebo do obecnější diskuze o Excelu :c)