V minulém článku jsem naznačil, jak si ulehčit pomocí záznamu makra nějakou početní úlohu a zjistil, že tento záznam mi v excelovském sešitu na kartě „Vývojář“ a po kliknutí na nástroj „Visual Basic“ otevřel tu část aplikace Excelu, kde jsem mohl objevit zaznamenané makro ve formě VBA skriptu. Také jsem naznačil, že nebudu zaznamenávat makra, abych získával VBA skript, ale budu postupovat zcela obráceně, tedy že pomocí napsaného VBA skriptu budu vytvářet jednotlivá makra podle své vlastní potřeby. Na obrázku níže by mohl být základní „traderský úkon“ formulovaný do excelovské tabulky, tedy zjištění, jak si vedou mé nakoupené akcie.
Mohu vypozorovat, že dnes je 27.6.2020 (buňka „A3“) a mám nakoupeny akcie Goldman Sachs („B3“) v celkovém počtu 120 kusů („C3“). Akcie jsem nakoupil za 179.56 USD („D3“) a jejich Last cena je dnes na úrovni 184.32 USD („E3“). Mým zadáním je vypočítat, kolik na těchto akciích vydělávám/prodělávám a tuto informaci chci vidět v buňce „F3“. Toto není nijak převratný matematický úkon, se kterým by si můj Excel neporadil, odečtu od dnešní ceny akcie hodnotu pořízení akcie a tento výnos na jednu akcii vynásobím počtem akcií v mém portfoliu. Tento vzorec je pak v obrázku červeně označen. Toto ale nebude mé řešení, protože si chci tento jednoduchý úkon vyjádřit pomocí VBA a připravit se tak na automatizaci většího počtu budoucích výpočtů, napíšu si tedy pomocí VBA své první makro…
Jak napsat první makro?
V minulém článku jsem označil na ploše nástroje Visual Basic okno, kde se objevil VBA skript zaznamenaného makra. V tomto okně si také napíšu svůj první VBA skript, aniž bych musel cokoliv zaznamenávat. Musím ještě podotknout, že plocha okna pro psaní VBA skriptu je interaktivní, takže umí například doplňovat některé základní programátorské úlohy za mě nebo mi napovědět, jakou další volbu pokračování skriptu bych měl zvolit. Každé makro, které budu chtít vyjádřit pomocí VBA skriptu začíná označením Sub se zadaným názvem makra a končí označením End Sub. Mezi těmito pokyny (označeními) pak na jednotlivých řádcích budu zapisovat skript, který bude označovat úkony, které chci ve svém excelu provádět. První známku interaktivity okna skriptu mohu vyzkoušet napsáním označení Sub Akcie, kde „Akcie“ je název makra, které budu vytvářet.
Pokud po napsání označení, že začínám tvořit makro Sub s názvem makra Akcie, potvrdím tuto zamýšlenou tvorbu klávesou Enter, okno pro tvorbu skriptu interaktivně doplní označení ukončení procedur vyvolávané tímto makrem End Sub na nový řádek.
Mezi těmito dvěma řádky, zárodku budoucího makra, pak mohu vepisovat jednotlivé řádky VBA skriptu, které budou mými zamýšlenými procedurami. Protože budu chtít, abych se ve svých výtvorech vyznal a mohl si své myšlenky při tvorbě makra nějak zaznamenávat mohu do skriptu vkládat poznámky, které bude proces běžícího makra ignorovat, takové poznámky, popisky nebo i rozpracované řádky skriptu jednoduše obdařím prvním znakem ve formě apostrofu, VBA pak pozná, že takový řádek není určen ke zpracování a přeskočí jej. Při tvorbě mého prvního makra pak mohu takový řádek k ignorování použít k základnímu popisku, co vlastně tvorbou makra zamýšlím.
Řádek začínající apostrofem se podbarvil do zelena a sděluje mi, že tvořený skript bude mít za úkol vypočítat výnos z držení akcie Goldman Sachs. Mohu tak již nyní vypozorovat, že text skriptu má již tři barevná provedení – modrou pro povinně formulované přednastavené pokyny (Sub, End Sub…), černou pro vlastní zápis skriptu (Akcie) a zelenou pro řádky skriptu, který bude v proceduře ignorován. Vytvoření názvu makra pomocí Sub a End Sub s popiskem, co budu mít s makrem v úmyslu, způsobí jeho vytvoření v Seznamu Maker, které mohu zkontrolovat podle postupu z předcházejícího článku.
Buňky a jejich zápis do skriptu
Postup výpočtu, jak zjistit výnos mých akcií Goldman Sachs, bych mohl podle vzorce v Excelu vyjádřit slovně takto „…zjisti hodnotu dnešní ceny, zjisti hodnotu pořizovací ceny a tyto ceny od sebe odečti, poté výsledek vynásob zjištěným počtem držených akcií a tuto vypočítanou hodnotu vlož do buňky vybrané pro vyznačení výnosu…“.
Buňky mohu zpracovávat pomocí VBA ve dvou základních možnostech, které budu využívat také ve svých skriptech, a to pomocí Range nebo Cells.
Range
Zvolením metody Range mohu vybírat ze svého sešitu buňky ke své práci s následným označením sloupce a řádku tak, jak jsem zvyklý z klasického excelovského listu, zadání Range pak zjednodušeně znamená oblast, se kterou chci pracovat. Range může být nejméně výběr jedné jediné buňky nebo může nabývat hodnoty oblasti několika buněk najednou, mohu si tak tuto zcela základní a nezbytnou záležitost přirovnat k funkcionalitě klasického listu, kdy buď kliknu na jedinou buňku nebo tažením myší označím v listu oblast buněk, se kterou chci pracovat. Range („C3“) tak znamená, že chci pracovat s buňkou C3, Zápis Range („C3:G11“) pak znamená, že chci pracovat s buňkami v oblasti C3:G11. Pokud chci zjistit a pracovat s hodnotou nalézající se v dané buňce, opatřím instrukci Range s vybranou oblastí („C3“) příznakem .Value. Potom bych mohl svůj jednoduchý úkol vidět v tomto náhledu.
Pomocí Range mohu zjišťovat hodnotu buňky nebo také nějakou hodnotu do buňky vkládat. Mohu tak pomocí této funkcionality například chtít, abych příkazem vtěleným do řádku skriptu VBA vložil do buňky B6 hodnotu 8000 a do oblasti buněk B7:D15 vložil název společnosti „Goldman Sachs“. Vytvořený dvouřádkový skript by pak vypadal takto.
V makru nazvaném Akcie chci instrukcí Range („B6“).Value vložit hodnotu 8000 do buňky B6 (1) a následně chci obdobnou instrukcí Range („B7:D15“).Value do oblasti B7:D15 vepsat slova „Goldman Sachs“ (2). Po napsání jednoduchého a doufám pochopitelného skriptu spustím mou požadovanou proceduru dvou úkolů za sebou kliknutím na spouštěcí tlačítko (3) z pásu nástrojů. Podotýkám s předstihem, že u požadavku na vložení požadovaného textu musí být vkládaný text opatřen uvozovkami.
Výsledek nepotřebuje komentář. Mohu ale také požadovat, že do prázdných buněk nebudu vkládat „natvrdo“ nějakou hodnotu nebo text, ale že ji vyplním nějakým výpočtem. Zpět tedy k mému úkolu vypočítat výkonnost akcie Goldman Sachs z mého portfolia. Některé hodnoty v buňkách již mám pro svůj výpočet vloženy a budu se tak snažit do nějaké prázdné buňky nebo oblasti vložit vypočítané hodnoty a nikoliv pevné nadefinované hodnoty. Mohu si nyní vymyslet, že budu chtít do oblasti buněk F6:F15 uvést akciový výnos připadající na jednu akcii.
Výnos na jednu akci zjistím jednoduchým odečtením Last ceny Range („E3“).Value od Pořizovací ceny Range („D3“).Value a výsledek pak budu chtít rozprostřít do všech buněk oblasti F6:F15, tedy ve VBA se tento výpočet objeví v tomto „rendži“ Range („F6:F15“).Value.
Jednořádkový skript říká mému excelovskému listu, aby do oblasti buněk F6:F15 (1) vypočítal a vložil rozdíl mezi hodnotou buňky E3 a D3. Po spuštění makra to vypadá následovně.
Je to jednoduchá odečítací operace s buňkami, jenom přepsána do VBA skriptu, nic jiného a také nic mimořádného k pochopení. Pokud již umím do buňky vložit nějakou hodnotu „natvrdo“ nebo pomocí nějakého výpočtu, nebude patrně nic těžkého vyřešit mou základní úlohu, vypočítat výkonnost akcie Goldman Sachs do určité buňky pomocí operací s buňkami, ve kterých mám potřebné hodnoty k výpočtu. V mém případě budu chtít do žlutě podbarvené buňky F3 Range („F3“).Value vyplnit hodnotou vzniklou výpočtem =(E3-D3)*C3, tedy (Last Cena – Pořizovací cena)*Počet akcií. Celý tento skript výpočtu mohu vidět na obrázku
Do buňky F3 (1) jsem umístil hodnotu výpočtu, který je tvořen rozdílem aktuální a pořizovací ceny (2), to vše vynásobené počtem držených akcií (3). Spuštěním procedury spouštěcí šipkou pak mohu vidět ve svém sešitu výsledek.
Tato velmi jednoduchá procedura práce s buňkami na bázi Range je samozřejmě plně funkční v případě, že mám otevřen jeden excelovský sešit s jedním aktivním listem, na kterém nyní pracuji. Při otevření dalšího excelovského souboru (sešitu) v mém počítači nebo při otevřeném sešitu, který bude mít více listů, bude docházet ke zmatkům, kdy VBA nebude vědět, které buňky (například C3) mám na mysli využívat, ze kterého listu nebo dokonce ze kterého dalšího otevřeného sešitu. Přestože se takovému nastavení dostanu později, je již nyní vhodnější adresovat práci s buňkami minimálně na aktuálně otevřený list, tedy ten, pro který spouštím vytvářené makro. Není to nic nemožného ke provedení, instrukci Range přiřadím předponu ActiveSheet., budu tak mít jistotu, že se spouštění bude provádět na buňkách na aktuálně aktivním sešitu. Mohu pak svůj výpočet vtělený do skriptu VBA zdokonalit na tento zápis.
Je tak z obrázku patrné, že jsem pouze obohatil původní zápis Range na ActiveSheet.Range a vše zůstalo stejné. Původní „chudší“ řádek jsem opatřil apostrofem, aby se podbarvil zeleně a do výpočtu nezasahoval. Výsledek se po spuštění nově upraveného makra nezměnil.
Cells
Jenom jsem ukázal možnost práce s buňkami pomocí Range, už budu ukazovat něco podobného, ale pomocí Cells. Není to účel vytvořit počáteční zmatek, ale ukázat na jednom místě oba způsoby, protože oba způsoby budu ve svých budoucích skriptech využívat. Lze je totiž používat současně a každý se hodí pro jiné situace. Práce s buňkami prostřednictvím Cells je velmi podobná práci na bázi Range, základní rozdíl je však v pojmenování buňky, se kterou pracuji. ActiveSheet.Range („B3“) označuje buňku B3 aktuálního listu tak, jak vidím její „kótování“ pomocí excelovské tabulky s názvy sloupců pomocí písmen (sloupec B) a názvy řádků pomocí čísel (řádek 3). AktiveSheet.Cells (3,2) je pak stejná buňka B3 z aktivního excelovského listu, jenom je její umístění popsáno syntaxí ActiveSheet.Cells (číslo řádku, číslo sloupce), tedy zcela polopatisticky ActiveSheet.Range („B3“) = AktiveSheet.Cells (řádek číslo 3, sloupec číslo 2) = AktiveSheet.Cells (3,2). Nahrazení jednoduchého označení buňky číslem řádku a číslem sloupce pak bude hojně využíváno v práci s cykly, kterých si užiji v dalším psaní skriptů více než dost. Vybrání nějaké oblasti buněk namísto jediné buňky pak má podobně odvozenou syntaxi, mohu si pak zkusit jednoduchý skript pomocí Cells podle obrázku níže.
Napsané dva řádky skriptu mají za úkol do buňky nacházející se na pátém řádku a ve třetím sloupci (buňka C5) zapsat hodnotu 100 (1) a do oblasti vytyčené buňkou nacházející se na sedmém řádku a v třetím sloupci (C7) a buňkou nacházející se na třináctém řádku a v sedmém sloupci (F13) zapsat slovo „Aaple“ (2). Spuštěním procedury se tak také stane.
Mohu si pak ve stejné logice napsat skript pomocí Cells na výpočet výkonnosti akcií Goldman Sachs, když pro jednotlivé buňky budu mít tento popis umístění.
Vlastní skript pak nebude nic jiného, než procedura výpočtu stejná jako pomocí Range, pouze bude na bázi Cells.
Do buňky F3 chci zapsat výsledek a tato buňka se nachází na 3. řádku a v 6. sloupci, proto chci výsledkem vyplnit buňku ActiveSheet.Cells (3,6), kterou začíná skript, postup výpočtu je pak dále na řádku stejný jako u Range, jenom přepsaný pomocí Cells (1), Za povšimnutí stojí, že oproti skriptu pomocí Cells výše (2) tento aktivní skript nemá přípony .Value a stejně vypočítal stejnou hodnotu. Výsledek je na obrázku.
Mazání buněk
Mazání obsahu buněk má pak stejnou logiku, jako zjištění hodnoty buněk. Stejně jako v klasickém excelovském listu si mazanou buňku nejdříve vyberu kliknutím myší a následným stisknutím klávesy Delete ji smažu (vyprázdním její obsah), smazání oblasti předchází označení buněk tažením myši a následné opětovné potvrzení stisknutím klávesy Delete. Stejně postupuje také VBA skript. Smazání buňky A3 bude předcházet její výběr pomocí Range nebo Cells, s připojením přípony .Select (Vyber) tedy ActiveSheet.Range („A3“).Select nebo ActiveSheet.Cells (3,1).Select. Tímto pokynem označím buňku A3. Následnou instrukcí Selection.ClearContents označenou buňku vymažu. Stejně tak pro oblast buněk nejdříve vyberu její rozsah pomocí Range nebo Cells a připojím příponu .Select (Vyber). Oblast buněk B5:C8 označím instrukcí ActiveSheet.Range („B5:C8“).Select nebo ActiveSheet.Range (Cells (5,2), Cells(8,3)).Select. Následně pokynem Selection.ClearContents vymažu označené buňky. Na obrázku níže je naznačen požadavek na mazání buněk z excelovského sešitu.
Nejdříve budu chtít vymazat pouze datum (1) v buňce A3 a následně vymazat oblast buněk C2:F3, zbytek chci ponechat. Napsaný skript by mohl mít třeba tento tvar.
Využil jsem obě možnosti zápisu pomocí Range a Cells v jednom skriptu. Nejdříve jsem vybral pomocí Range buňku A3 (1) a poté vybral pomocí Cells oblast buněk C2:F3 (2). Vybrané jsem pak nechal vymazat pokynem Selection.ClearContents (3). Výsledek po spuštění makra je na obrázku níže.
Výběr buňky a práce s ní je základem budoucího úspěchu a stane se pilířem budoucí práce. Přestože existuje několik způsobů, jak na to jít, je výběr a práce s buňkou pomocí Range a Cells velmi jednoduchá a práce s těmito prvky přejde velmi rychle „do krve“. Po prozkoušení základních početních úloh s buňkami a osahání úplně základních funkcionalit okna Visual Basic je nyní dobré nastínit, jak vlastně programování složitějších úloh může probíhat.
„…Rozhodčí je Hugo a Hugo je vůl…“
Stará a dobrá hláška z fotbalového hlediště podle mě velmi dobře vystihuje základní princip práce s VBA. Pokud bych si ji chtěl nějak jednoduše logicky rozebrat, tak „emocionálně nestabilní“ divák chce sdělit rozhodčímu, že není s jeho prací spokojen, nicméně z obavy z nařčení, že dotyčného fotbalového arbitra nazývá volem, přenese tento nejběžnější český vulgarismus na jakéhosi „Huga“. Pokud se rozhodčí zrovna nejmenuje Hugo (to by byl fatální základní nedostatek hlášky :c), je pro sudího hůře rozpoznatelné, že si z něj divák utahuje. Ba co víc, přestože Rozhodčí zůstává stále rozhodčím a Vůl je pořád volem, může nespokojený divák vyměňovat slovo Hugo za libovolné jiné jméno a obsah sdělení zůstává nezměněn. „Rozhodčí je Pepa a Pepa je vůl“ je tak stejné sdělení se stejným nábojem a stejným obsahovým významem. Slovo Hugo nebo Pepa tak na sebe berou břemeno sdělení, že jsou volové a toto stigma vola pak předávají na jakoukoliv jinou osobu, třeba zrovna fotbalového rozhodčího. V obecnějším pohledu jsou pak Hugo nebo Pepa jakési pomyslné proměnné, které jsou schopny nabrat nějakou hodnotu, kterou jsou schopny přenést na někoho nebo na nějaké jiné místo.
Deklarace proměnných
Práce s proměnnými je důležitou součástí tvorby VBA skriptů, proto musí být jasné, co si pod tím vlastně představit a jak takové proměnné vlastně nastavit. Ve smyslu VBA je proměnná místo v paměti mého počítače, které si nějak zapamatovatelně pojmenuji a sdělím tomuto místu, jaký bude mít tvar (formát). Druhů proměnných je mnoho, ve svém programování se ale omezím pouze na nejběžnější z nich. Budu předpokládat, že ve svém skriptu budu pracovat s datumem, takový údaj o datumu pak bude mít formát Date. Pokud budu vědět, že má proměnná bude celé číslo, budu označeno jako Integer. Pokud budu ve výpočtech nakládat s desetinnými čísly, budou mít tyto proměnné označení Double. V případě, že proměnnou bude text, bude mít proměnná definovaný tvar označený jako String, proměnná Hugo tedy určitě bude String.
Zadání proměnné pomocí skriptu VBA provádím instrukcí Dim, která má tvar Dim „Název Proměnné“ As „Příslušný formát“, v mém konkrétním fotbalovém případě pak bude ve skriptu napsáno Dim Hugo As String. Opět si mohu vyzkoušet interaktivitu pole pro psaní VBA skriptu, který po zadání Dim a názvu proměnné nabízí možné tvary z rozbalovacího menu, celý zápis proměnné Hugo pak vypadá takto.
K čemu to je? Proměnná Hugo bude vždy znamenat nějaký textový údaj, který si do ni načtu z buňky excelovského listu. Dále pak již ve skriptu budu pracovat s touto proměnnou, které budu schopen například měnit její hodnoty. Na obrázku níže je několik možností, kterých může hodnota Hugo nabývat spolu s požadavkem na řešení jednoduchého excelovského výpočetního úkolu – zjištění „Co je vlastně rozhodčí zač?“
V buňkách B2, B3 a B4 jsou jednotlivé možnosti, kterých může proměnná Hugo nabývat i s popiskem umístění buňky v listu sešitu pomocí Cells. Do buňky C6 chci po spuštění makra umístit výsledek, tedy odpověď na otázku, kterou reprezentuje hodnota proměnné Hugo. Jednoduché makro pak může vypadat následovně.
Svému makru jsem dal název RozhodciJeHugo a přidal stručný popisek, co vlastně makro řeší (1). Definoval jsem proměnnou Hugo jako textový výraz (String), nebude to tedy žádné číslo nebo něco jiného, ale jen napsaný text (2). Vybavil jsem proměnnou Hugo hodnotou, kterou si sebou nyní ponese, moje volba padla na možnost z řádku B4, rozhodčí totiž je pro tuto chvíli především „super chlap“ (3). Do buňky, kde chci vědět odpověď (C6), směruji nikoliv natvrdo napsaný text, ale proměnnou Hugo, která momentálně nese hodnotu „super chlap“. (4). Makro spustím spouštěcím tlačítkem (5). Výsledek je tento:
Pokud bych chtěl být opravdu vulgární a nechat řešení otázky tím nejhorším způsobem, upravím text skriptu pouze o hodnotu proměnné Hugo.
Přiřadit proměnné Hugo hodnotu „vůl“ je v excelovskom listu ve druhém řádku a druhém sloupci (B2). Proto pozměním pouze umístění buňky, ze které budu hodnotu buňky načítat (červený obdélník) a provedu spuštění makra znovu.
Výsledek je otřepaná vulgarita, kterou vygenerovalo velmi jemně upravené makro. V praxi tak načítání hodnot proměnných bude hrát zásadní roli v postupných výpočtech, již nyní je alespoň jasné, že se mi bude lépe pracovat s hodnotou Hugo než s hodnotou ActiveSheet.Cells (2,2).Value…
První testovací makro
Umím již vysledovat hodnotu buňky a provádět nejjednodušší matematické operace s obsahem buněk a také umím buňky smazat. Vím, co je to proměnná a jak mohu měnit její hodnotu a také s touto proměnnou také pracovat. Je tak čas vytvořit nějakou složitější úlohu, kde využiji všechny prozatím načerpané znalosti. Mohu si představit, že mám k dispozici tyto jednoduchá data mého akciového miniportfolia tvořeného 120 kusy akcií Goldman Sachs.
Budu chtít vyhodnotit několik ukazatelů tohoto obchodu a tyto sestavit do nějakého přehledu, který by se mi vytvořil na listu excelovského sešitu. Mohu například chtít zjistit, kolik dnů jsem aktuálně v obchodu, jaký je zisk/ztráta na jednu akcii, jaký je zisk/ztráta na jeden den držené investice a kolik celkově na obchodu vydělávám/prodělávám. Tento přehled pak budu chtít podrobit nějakému jednoduchému testu, kdy budu měnit například aktuální cenu akcie a zjišťovat, jak se mi změní pohled na tuto investici.
Začnu přípravou makra zadáním Sub a stručným popiskem předmětu činnosti
Deklarace proměnných
První část VBA skriptu bude tvořit deklarace proměnných, tedy definování, jakých tvarů mohou nabývat. K této činnosti použiji instrukci Dim a příslušný název typu proměnné.
Zvolil jsem názvy proměnných tak, aby co nejlépe vystihovaly obsah, který si ponesou. Proměnnými jsem si určil vše, co by mohlo nabývat nějaké budoucí změny, kterou chci poté otestovat. Nejdříve jsem chtěl definovat den nákupu akcií a dnešní datum, abych mohl splnit zadaný úkol – například zjišťovat počet dní držení akciového titulu. DnesniDatum a DatumPorizeni jsou budoucí proměnné, které mají formát datum, takže jejich deklarace je ..As Date (1). Proměnnou Ticker budu mít na mysli název akciové společnosti, bude to tedy text, proto Dim Ticker As String (2). Proměnná PocetAkcii deklarovaná …As Integer znamená držený počet kusů akcií, tento může nabývat hodnot celých čísel, proto Integer. Proměnné PorizovaciCena a AktualniCena jsou desetinná čísla a představují cenu, za kterou jsem akcii nakoupil (PorizovaciCena) a aktuální cenu akcií (AktualniCena), pro charakter čísel s desetinnými místy jsou tyto proměnné deklarovány jako …As Double.
Načtení hodnoty proměnných
Nyní načtu obsah proměnných tak, jak mám k dispozici data v mém excelovském sešitu. Pro první dvě proměnné DnesniDatum a DatumPorizeni mám hodnoty tyto.
DnesniDatum, proměnná která má hodnotu 28.6.2020 je v buňce A4, a to znamená umístění v místě popsaném VBA jako ActiveSheet.Cells (4,1). DatumPorizeni, proměnná, které má hodnotu 25.6.2020 je v buňce D4, a to znamená umístění v místě popsaném VBA jako ActiveSheet.Cells (4,4). Mohu tedy jednoduchým zápisem tyto proměnné naplnit hodnotou konkrétních datumů v těchto buňkách mého excelovského listu.
Stejně tak postupuji u zbylých proměnných, pro které jsem si deklaroval jejich typ. Celé načtení hodnot do proměnných pak bude vypadat takto.
Skript jednotlivých úloh
Takto připravený VBA skript mohu začít zatěžovat činnostmi, které mi bude popisovat dále vytvářený skript, a který bude využívat hodnot proměnných k řešení nejjednodušších úkolů. Předsevzal jsem si, že si vytvořím přehled, ve kterém budu chtít pozorovat, kolik dnů jsem aktuálně v obchodu, jaký je zisk/ztráta na jednu akcii, jaký je zisk/ztráta na jeden den držené investice a kolik celkově na obchodu vydělávám/prodělávám. Tento přehled, jakýsi jednoduchý formulář, umístím pod údaje o obchodu na stejný list excelovského sešitu, proto si nejdříve nechám do tohoto listu vložit hlavičku takového přehledu, tedy popisy buněk s názvy sledovaných akcí. Nebude to nic jiného, než již známé vkládání slov do konkrétních buněk „natvrdo“ popsané v úvodu článku. „Den vstupu“ bude prvním výrazem přehledu a tento budu chtít umístit do buňky A6, „Den výstupu“ budu chtít umístit do buňky A7, „Titul“ budu chtít umístit do buňky A8. „Trvání obchodu“ bude dalším výrazem přehledu a tento budu chtít umístit do buňky A9, „Zisk/ztráta na akcii“ pak bude druhým výrazem, který budu chtít umístit do buňky A10, „Zisk/ztráta na den“ bude v buňce A11 a nakonec výraz „Zisk/ztráta celkem“ budu chtít „odsadit“ o jeden prázdný řádek, proto jej budu chtít mít až v buňce A13. Tyto natvrdo napsané texty do buňky vložím tímto skriptem.
Vložení textu do vybraných buněk provedu zadáním umístěním buňky opět pomocí Cells a vkládaný text opatřím uvozovkami. Spuštěním makra pak mohu vypozorovat výsledek na mém aktivním excelovském listu
Na určených místech listu se mi objevily názvy buněk, které jsem si jednoduchým skriptem vložil, aby vytvořily hlavičku jednoduchého přehledu (levá červená šipka), který budu nyní zaplňovat údaji a výpočty. Tyto výpočty pak budou v buňkách označených červenými obdélníky a budou odpovídat popisku, který jsem si nyní do buněk vlevo do prvního sloupce vložil.
Nebudu nyní do buněk pro údaje a výpočty uvádět hodnoty jiných buněk, ale budu již pracovat s proměnnými, které jsem si nadefinoval a kterým jsem následně načetl jejich hodnoty. Proto do první buňky B6, ve formátu VBA jako ActiveSheet.Cells (6,2), kde chci umístit datum vstupu do obchodu, a kterému odpovídá vedlejší buňka přehledu s vloženým výrazem „Den vstupu“, umístím proměnnou DatumPorizeni
Spuštěním makra se mi do tvořeného přehledu zapíše datum vstupu do obchodu, které obsahuje proměnná DatumPorizeni, tedy hodnota 25.6.2020. Stejnou proceduru provedu také s hodnotou v další buňce přehledu B7, ve formátu VBA jako ActiveSheet.Cells (7,2), kam chci vložit Den výstupu z obchodu, kterému odpovídá proměnná DnesniDatum. Stejně postupuji pro zjištění údaje o názvu společnosti do buňky B8, ve formátu VBA jako ActiveSheet.Cells (8,2), kam umístím obsah proměnné Ticker.
Spuštění makra způsobí další vyplnění přehledu o Den výstupu a Titul obchodované společnosti
Další buňka B9, ve formátu VBA jako ActiveSheet.Cells (9,2), pak bude požadovat vyplnění doby trvání obchodu. Zde již nebudu pouze vkládat hodnotu proměnné, ale musím provést první početní operaci s proměnnými. Protože počet dnů v obchodu zjistím odečtením data, kdy jsem do obchodu vstoupil od dnešního data, zjistím tuto hodnotu odečtením proměnných DnesniDatum – DatumPorizeni, tyto totiž v sobě mají načtenou hodnotu 28.6.2020 a 25.6.2020, výsledkem operace by pak mělo být časové období tři dny.
Spuštění celého makra pak vyplní přehled také počtem dnů, po který obchod trvá.
Nyní se již také začíná rýsovat první zárodek testovacího přístupu. Pokud totiž ve svých údajích o portfoliu nakoupených akcií Goldman Sachs ve čtvrtém řádku změním Datum pořízení z 25.6.2020 například na 20.6.2020 a spustím makro, odečtením proměnných DnesniDatum – DatumPorizeni se provede nový výpočet s jiným výsledkem, proměnné totiž v sobě mají načtenou hodnotu 28.6.2020 (zůstává nezměněno) a novou hodnotu 20.6.2020, výsledkem je pak 8, obchod by tak trval osm dnů.
Obdobně postupuji v další buňce B10, ve formátu VBA jako ActiveSheet.Cells (10,2), kam chci umístit výsledek výpočtu „Zisk/ztráta na akcii“. Tento výsledek je opět odečítací operací mezi proměnnými AktualniCena – PorizovaciCena. V buňce B11, ve formátu VBA jako ActiveSheet.Cells (10,2) kam chci umístit výsledek výpočtu „Zisk/ztráta na den“ již provedu složitější operaci, kde zisk/ztrátu na jedné akcii (AktualniCena – PorizovaciCena) vynásobím počtem akcií načtených do proměnné PocetAkcii, tedy (AktualniCena – PorizovaciCena)*PocetAkcii, a to celé pak vydělím počtem dní trvání obchodu, celé tak ((AktualniCena – PorizovaciCena)*PocetAkcii)/( DnesniDatum – DatumPorizeni).
Spuštěním makra pak obdržím vyplnění přehledu o tyto další údaje
V poslední buňce přehledu B13, ve formátu VBA jako ActiveSheet.Cells (13,2), kam chci umístit výsledek výpočtu „Zisk/ztráta celkem“ umístím výpočet pomoci proměnných (AktualniCena – PorizovaciCena)*PocetAkcii. Tím budu mít celý přehled vyplněn pomocí VBA skriptu.
Celý přehled pak spuštěním makra mohu vidět vyplněný takto:
Testuji možnosti
Celé vytvořené makro pak provede vyhodnocení mého miniportfolia přesně podle kroků, které vyjadřují jednotlivé řádky skriptu tak, jak jdou jednotlivě za sebou, co řádek – to jeden úkon, který chci provést na svém excelovském listu do označených buněk. Každé spuštění makra tedy nejdříve nadeklaruje proměnné podle jejich názvu a přiřadí jim požadovaný typ, poté se hodnoty proměnných naplní hodnotami z mnou označených buněk, abych následně hodnoty těchto proměnných využíval k dílčím výpočtům. Je pak také jednoduché si představit, že pokud změním některou z hodnot v informacích o mém portfoliu Goldman Sachs, například změním aktuální cenu na hodnotu 150 USD, tato se po znovuspuštění makra načte do proměnné AktualniCena a všechny následné výpočty, které jsem si zaznamenal VBA skriptem a které obsahují proměnnou AktualniCena, se provedou s touto její novou hodnotou.
Je pak vidět, že snížení aktuální ceny na 150 USD načtené do proměnné AktualniCena způsobila ztrátu na mém testovaném akciovém miniportfoliu Golman Sachs. Mohu si tak ze svého excelovského listu vyrobit jednoduchý testovací nástroj, který mohu obsluhovat stisknutím tlačítka po každé změně nějakého údaje v buňce. Před každou operací testu pro jistotu vymažu starý vyplněný přehled, aby se mi data načítala do prázdných buněk listu. Mazání buněk provedu podle textu o mazání buněk výše výběrem oblasti, kterou chci smazat a tuto proceduru umístím do skriptu před proceduru vyplňování hlavičky přehledu a samotné výpočty.
Vyrobit si tlačítko na spuštění makra mohu podle instrukcí v minulém článku z pásu nástrojů na kartě „Vývojář“. Celou analýzu pak budu provádět mačkáním na tlačítko, které bude spouštět vyplňování přehledu podle údajů v buňkách o mém miniportfoliu. Mohu tak například zadat změnu, že jsem neobchodoval akcie Goldman Sachs, ale McDonald‘s, do obchodu jsem vstoupil před měsícem 27.5.2020 a tomu samozřejmě odpovídá také jiná vstupní cena a její dnešní úroveň, navíc jsem pořídil 283 akcií. Nové zadání je pak možné nastavit například jako na obrázku.
Kliknutím na tlačítko ke spuštění makra dojde k načtení nových hodnot do proměnných (zelený obdélník), smaže se starý přehled týkající se akcií Goldman Sachs (červený obdélník) a vyplní se novými údaji o mém obchodu s akciemi McDonald’s. Zbrusu nový formulář přehledu s nově vyplněnými údaji a výpočty je pak vygenerován ve zlomku sekundy.
Práce s buňkami a proměnnými je základem každého VBA skriptu. Není to vůbec nic složitého, nakonec se z takového psaní skriptu může stát běžná rutina. Jak napsat makro pomocí VBA skriptu a nemuset provádět jeho záznam jako v minulém článku nakonec může být pro každého velmi jednoduchou programátorskou záležitostí. Jak je patrné, není se čeho obávat a jediné, na co je si dát pozor, je správná syntaxe jednotlivých řádků skriptu. Nebudu se ani v budoucnosti pouštět do nějakých složitějších konstrukcí, abych se nakonec sám nezamotal do nepřehledného kódu a unikal mi smysl vytvářeného skriptu. Pokud jsem si již nyní osvojil základní práci s buňkami a tvorbou jednoduchého makra, bude stačit tyto znalosti obohatit o poznání programování jednoduchých cyklů a rozhodovacích úloh a mám vyhráno, toto pak bude předmětem dalšího článku.
Sešit s makrem je pak možné k prozkoumání kódu stáhnou zde. Při otevření musíte potvrdit volbu „Povolit makra“.
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)
Sleduj facebook, napiš e-mail nebo tweet
