{"id":4525,"date":"2020-06-28T13:16:11","date_gmt":"2020-06-28T11:16:11","guid":{"rendered":"http:\/\/dobretrejdy.com\/?p=4525"},"modified":"2024-08-18T09:09:59","modified_gmt":"2024-08-18T07:09:59","slug":"backtestovani-v-excelu-vba-iii","status":"publish","type":"post","link":"https:\/\/dobretrejdy.com\/?p=4525","title":{"rendered":"Backtestov\u00e1n\u00ed v Excelu (VBA) \u2013 III."},"content":{"rendered":"

\u00a0 \u00a0V minul\u00e9m \u010dl\u00e1nku<\/a><\/strong><\/span> jsem nazna\u010dil, jak si uleh\u010dit pomoc\u00ed z\u00e1znamu makra n\u011bjakou po\u010detn\u00ed \u00falohu a zjistil, \u017ee tento z\u00e1znam mi v excelovsk\u00e9m se\u0161itu na kart\u011b \u201eV\u00fdvoj\u00e1\u0159\u201c a po kliknut\u00ed na n\u00e1stroj \u201eVisual Basic\u201c otev\u0159el tu \u010d\u00e1st aplikace Excelu, kde jsem mohl objevit zaznamenan\u00e9 makro ve form\u011b VBA skriptu<\/strong>. Tak\u00e9 jsem nazna\u010dil, \u017ee nebudu zaznamen\u00e1vat makra, abych z\u00edsk\u00e1val VBA skript, ale budu postupovat zcela obr\u00e1cen\u011b, tedy \u017ee pomoc\u00ed napsan\u00e9ho VBA skriptu budu vytv\u00e1\u0159et jednotliv\u00e1 makra podle sv\u00e9 vlastn\u00ed pot\u0159eby. Na obr\u00e1zku n\u00ed\u017ee by mohl b\u00fdt z\u00e1kladn\u00ed \u201etradersk\u00fd \u00fakon\u201c formulovan\u00fd do excelovsk\u00e9 tabulky, tedy zji\u0161t\u011bn\u00ed, jak si vedou m\u00e9 nakoupen\u00e9 akcie.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Mohu vypozorovat, \u017ee dnes je 27.6.2020 (bu\u0148ka \u201eA3\u201c) a m\u00e1m nakoupeny akcie Goldman Sachs (\u201eB3\u201c) v\u00a0celkov\u00e9m po\u010dtu 120 kus\u016f (\u201eC3\u201c). Akcie jsem nakoupil za 179.56 USD (\u201eD3\u201c) a jejich Last cena je dnes na \u00farovni 184.32 USD (\u201eE3\u201c). M\u00fdm zad\u00e1n\u00edm<\/strong> je vypo\u010d\u00edtat, kolik na t\u011bchto akci\u00edch vyd\u011bl\u00e1v\u00e1m\/prod\u011bl\u00e1v\u00e1m<\/strong> a tuto informaci chci vid\u011bt v\u00a0bu\u0148ce \u201eF3\u201c<\/strong>. Toto nen\u00ed nijak p\u0159evratn\u00fd matematick\u00fd \u00fakon, se kter\u00fdm by si m\u016fj Excel neporadil, ode\u010dtu od dne\u0161n\u00ed ceny akcie hodnotu po\u0159\u00edzen\u00ed akcie a tento v\u00fdnos na jednu akcii vyn\u00e1sob\u00edm po\u010dtem akci\u00ed v\u00a0m\u00e9m portfoliu. Tento vzorec je pak v\u00a0obr\u00e1zku \u010derven\u011b ozna\u010den. Toto ale nebude m\u00e9 \u0159e\u0161en\u00ed, proto\u017ee si chci tento jednoduch\u00fd \u00fakon vyj\u00e1d\u0159it pomoc\u00ed VBA a p\u0159ipravit se tak na automatizaci v\u011bt\u0161\u00edho po\u010dtu budouc\u00edch v\u00fdpo\u010dt\u016f, nap\u00ed\u0161u si tedy pomoc\u00ed VBA sv\u00e9 prvn\u00ed makro\u2026<\/span><\/p>\n

\n

\u00a0\u00a0 Jak napsat prvn\u00ed makro?<\/strong><\/span><\/h1>\n

\u00a0\u00a0 V minul\u00e9m \u010dl\u00e1nku jsem ozna\u010dil na plo\u0161e n\u00e1stroje Visual Basic okno, kde se objevil VBA skript zaznamenan\u00e9ho makra. V tomto okn\u011b si tak\u00e9 nap\u00ed\u0161u sv\u016fj prvn\u00ed VBA skript, ani\u017e bych musel cokoliv zaznamen\u00e1vat. Mus\u00edm je\u0161t\u011b podotknout, \u017ee plocha okna pro psan\u00ed VBA skriptu je interaktivn\u00ed, tak\u017ee um\u00ed nap\u0159\u00edklad dopl\u0148ovat n\u011bkter\u00e9 z\u00e1kladn\u00ed program\u00e1torsk\u00e9 \u00falohy za m\u011b nebo mi napov\u011bd\u011bt, jakou dal\u0161\u00ed volbu pokra\u010dov\u00e1n\u00ed skriptu bych m\u011bl zvolit. Ka\u017ed\u00e9 makro, kter\u00e9 budu cht\u00edt vyj\u00e1d\u0159it pomoc\u00ed VBA skriptu za\u010d\u00edn\u00e1 ozna\u010den\u00edm Sub<\/em><\/span><\/strong> se zadan\u00fdm n\u00e1zvem makra a kon\u010d\u00ed ozna\u010den\u00edm End Sub.<\/strong><\/span><\/em> Mezi t\u011bmito pokyny (ozna\u010den\u00edmi) pak na jednotliv\u00fdch \u0159\u00e1dc\u00edch budu zapisovat skript, kter\u00fd bude ozna\u010dovat \u00fakony, kter\u00e9 chci ve sv\u00e9m excelu prov\u00e1d\u011bt. Prvn\u00ed zn\u00e1mku interaktivity okna skriptu mohu vyzkou\u0161et naps\u00e1n\u00edm ozna\u010den\u00ed Sub<\/strong><\/span><\/em> Akcie<\/strong>, kde \u201eAkcie\u201c je n\u00e1zev makra, kter\u00e9 budu vytv\u00e1\u0159et.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Pokud po naps\u00e1n\u00ed ozna\u010den\u00ed, \u017ee za\u010d\u00edn\u00e1m tvo\u0159it makro Sub<\/strong><\/span><\/em> s n\u00e1zvem makra Akcie<\/strong>, potvrd\u00edm tuto zam\u00fd\u0161lenou tvorbu kl\u00e1vesou Enter, okno pro tvorbu skriptu interaktivn\u011b dopln\u00ed ozna\u010den\u00ed ukon\u010den\u00ed procedur vyvol\u00e1van\u00e9 t\u00edmto makrem End Sub<\/strong><\/span><\/em> na nov\u00fd \u0159\u00e1dek.<\/span><\/p>\n

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

\u00a0\u00a0\u00a0Mezi t\u011bmito dv\u011bma \u0159\u00e1dky, z\u00e1rodku budouc\u00edho makra, pak mohu vepisovat jednotliv\u00e9 \u0159\u00e1dky VBA skriptu, kter\u00e9 budou m\u00fdmi zam\u00fd\u0161len\u00fdmi procedurami. Proto\u017ee budu cht\u00edt, abych se ve sv\u00fdch v\u00fdtvorech vyznal a mohl si sv\u00e9 my\u0161lenky p\u0159i tvorb\u011b makra n\u011bjak zaznamen\u00e1vat mohu do skriptu vkl\u00e1dat pozn\u00e1mky<\/strong>, kter\u00e9 bude proces b\u011b\u017e\u00edc\u00edho makra ignorovat,<\/strong> takov\u00e9 pozn\u00e1mky, popisky nebo i rozpracovan\u00e9 \u0159\u00e1dky skriptu jednodu\u0161e obda\u0159\u00edm prvn\u00edm znakem<\/strong> ve form\u011b apostrofu<\/strong>, VBA pak pozn\u00e1, \u017ee takov\u00fd \u0159\u00e1dek nen\u00ed ur\u010den ke zpracov\u00e1n\u00ed a p\u0159esko\u010d\u00ed jej. P\u0159i tvorb\u011b m\u00e9ho prvn\u00edho makra pak mohu takov\u00fd \u0159\u00e1dek k\u00a0ignorov\u00e1n\u00ed pou\u017e\u00edt k\u00a0z\u00e1kladn\u00edmu popisku, co vlastn\u011b tvorbou makra zam\u00fd\u0161l\u00edm.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 \u0158\u00e1dek za\u010d\u00ednaj\u00edc\u00ed apostrofem se podbarvil do zelena<\/strong> a sd\u011bluje mi, \u017ee tvo\u0159en\u00fd skript bude m\u00edt za \u00fakol vypo\u010d\u00edtat v\u00fdnos z dr\u017een\u00ed akcie Goldman Sachs. Mohu tak ji\u017e nyn\u00ed vypozorovat, \u017ee text skriptu m\u00e1 ji\u017e t\u0159i barevn\u00e1 proveden\u00ed \u2013 modrou pro povinn\u011b formulovan\u00e9 p\u0159ednastaven\u00e9 pokyny (Sub, End Sub\u2026), \u010dernou pro vlastn\u00ed z\u00e1pis skriptu (Akcie) a zelenou pro \u0159\u00e1dky skriptu, kter\u00fd bude v procedu\u0159e ignorov\u00e1n. Vytvo\u0159en\u00ed n\u00e1zvu makra pomoc\u00ed Sub a End Sub s popiskem, co budu m\u00edt s makrem v \u00famyslu, zp\u016fsob\u00ed jeho vytvo\u0159en\u00ed v Seznamu Maker<\/strong>, kter\u00e9 mohu zkontrolovat podle postupu z\u00a0p\u0159edch\u00e1zej\u00edc\u00edho \u010dl\u00e1nku.<\/span><\/p>\n

\"\"<\/p>\n

\n

Bu\u0148ky a jejich z\u00e1pis do skriptu<\/strong><\/span><\/h1>\n

\u00a0\u00a0 Postup v\u00fdpo\u010dtu, jak zjistit v\u00fdnos m\u00fdch akci\u00ed Goldman Sachs, bych mohl podle vzorce v Excelu vyj\u00e1d\u0159it slovn\u011b takto \u201e…zjisti hodnotu dne\u0161n\u00ed ceny, zjisti hodnotu po\u0159izovac\u00ed ceny a tyto ceny od sebe ode\u010dti, pot\u00e9 v\u00fdsledek vyn\u00e1sob zji\u0161t\u011bn\u00fdm po\u010dtem dr\u017een\u00fdch akci\u00ed a tuto vypo\u010d\u00edtanou hodnotu vlo\u017e do bu\u0148ky vybran\u00e9 pro vyzna\u010den\u00ed v\u00fdnosu…“.<\/span><\/p>\n

\u00a0\u00a0 Bu\u0148ky<\/strong> mohu zpracov\u00e1vat pomoc\u00ed VBA ve dvou z\u00e1kladn\u00edch mo\u017enostech<\/strong>, kter\u00e9 budu vyu\u017e\u00edvat tak\u00e9 ve sv\u00fdch skriptech, a to pomoc\u00ed Range<\/strong> nebo Cells<\/strong>.<\/span><\/p>\n

Range<\/strong><\/span><\/h2>\n

\u00a0 \u00a0Zvolen\u00edm metody Range mohu vyb\u00edrat ze sv\u00e9ho se\u0161itu bu\u0148ky ke sv\u00e9 pr\u00e1ci s n\u00e1sledn\u00fdm ozna\u010den\u00edm sloupce a \u0159\u00e1dku tak, jak jsem zvykl\u00fd z klasick\u00e9ho excelovsk\u00e9ho listu, zad\u00e1n\u00ed Range pak zjednodu\u0161en\u011b znamen\u00e1 oblast, se kterou chci pracovat. Range<\/strong> m\u016f\u017ee b\u00fdt nejm\u00e9n\u011b v\u00fdb\u011br jedn\u00e9 jedin\u00e9 bu\u0148ky<\/strong> nebo m\u016f\u017ee nab\u00fdvat hodnoty oblasti n\u011bkolika bun\u011bk<\/strong> najednou, mohu si tak tuto zcela z\u00e1kladn\u00ed a nezbytnou z\u00e1le\u017eitost\u00a0 p\u0159irovnat k funkcionalit\u011b klasick\u00e9ho listu, kdy bu\u010f kliknu na jedinou bu\u0148ku nebo ta\u017een\u00edm my\u0161\u00ed ozna\u010d\u00edm v listu oblast bun\u011bk, se kterou chci pracovat. Range (\u201eC3\u201c)<\/strong> tak znamen\u00e1, \u017ee chci pracovat s bu\u0148kou C3<\/strong>, Z\u00e1pis Range (\u201eC3:G11\u201c)<\/strong> pak znamen\u00e1, \u017ee chci pracovat s bu\u0148kami v oblasti C3:G11<\/strong>. Pokud chci zjistit a pracovat s hodnotou nal\u00e9zaj\u00edc\u00ed se v dan\u00e9 bu\u0148ce, opat\u0159\u00edm instrukci Range s vybranou oblast\u00ed (\u201eC3\u201c) p\u0159\u00edznakem .Value.<\/strong> Potom bych mohl sv\u016fj jednoduch\u00fd \u00fakol vid\u011bt v tomto n\u00e1hledu. \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Pomoc\u00ed Range mohu zji\u0161\u0165ovat hodnotu bu\u0148ky nebo tak\u00e9 n\u011bjakou hodnotu do bu\u0148ky vkl\u00e1dat. Mohu tak pomoc\u00ed t\u00e9to funkcionality nap\u0159\u00edklad cht\u00edt, abych p\u0159\u00edkazem vt\u011blen\u00fdm do \u0159\u00e1dku skriptu VBA vlo\u017eil do bu\u0148ky B6 hodnotu 8000 a do oblasti bun\u011bk B7:D15 vlo\u017eil n\u00e1zev spole\u010dnosti \u201eGoldman Sachs\u201c. \u00a0Vytvo\u0159en\u00fd dvou\u0159\u00e1dkov\u00fd skript by pak vypadal takto.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 \u00a0V makru nazvan\u00e9m Akcie chci instrukc\u00ed Range (\u201eB6\u201c).Value<\/strong> vlo\u017eit hodnotu 8000<\/strong>\u00a0do bu\u0148ky B6 (1)<\/span><\/strong> a n\u00e1sledn\u011b chci obdobnou instrukc\u00ed Range (\u201eB7:D15\u201c).Value <\/strong>do oblasti B7:D15<\/strong> vepsat slova \u201eGoldman Sachs\u201c (2)<\/span><\/strong>. Po naps\u00e1n\u00ed jednoduch\u00e9ho a douf\u00e1m pochopiteln\u00e9ho skriptu spust\u00edm mou po\u017eadovanou proceduru dvou \u00fakol\u016f za sebou kliknut\u00edm na spou\u0161t\u011bc\u00ed tla\u010d\u00edtko (3)<\/strong><\/span> z\u00a0p\u00e1su n\u00e1stroj\u016f. Podot\u00fdk\u00e1m s\u00a0p\u0159edstihem, \u017ee u po\u017eadavku na vlo\u017een\u00ed po\u017eadovan\u00e9ho textu mus\u00ed b\u00fdt vkl\u00e1dan\u00fd text opat\u0159en uvozovkami.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 V\u00fdsledek nepot\u0159ebuje koment\u00e1\u0159. Mohu ale tak\u00e9 po\u017eadovat, \u017ee do pr\u00e1zdn\u00fdch bun\u011bk nebudu vkl\u00e1dat \u201enatvrdo\u201c n\u011bjakou hodnotu nebo text, ale \u017ee ji vypln\u00edm n\u011bjak\u00fdm v\u00fdpo\u010dtem. Zp\u011bt tedy k\u00a0m\u00e9mu \u00fakolu vypo\u010d\u00edtat v\u00fdkonnost akcie Goldman Sachs z\u00a0m\u00e9ho portfolia. N\u011bkter\u00e9 hodnoty v\u00a0bu\u0148k\u00e1ch ji\u017e m\u00e1m pro sv\u016fj v\u00fdpo\u010det vlo\u017eeny a budu se tak sna\u017eit do n\u011bjak\u00e9 pr\u00e1zdn\u00e9 bu\u0148ky nebo oblasti vlo\u017eit vypo\u010d\u00edtan\u00e9 hodnoty a nikoliv pevn\u00e9 nadefinovan\u00e9 hodnoty. Mohu si nyn\u00ed vymyslet, \u017ee budu cht\u00edt do oblasti<\/strong> bun\u011bk F6:F15<\/strong> uv\u00e9st akciov\u00fd v\u00fdnos<\/strong> p\u0159ipadaj\u00edc\u00ed na jednu akcii.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 V\u00fdnos na jednu akci zjist\u00edm jednoduch\u00fdm ode\u010dten\u00edm Last ceny<\/strong> Range (\u201eE3\u201c).Value<\/em> od Po\u0159izovac\u00ed ceny<\/strong> Range (\u201eD3\u201c).Value<\/em> a v\u00fdsledek pak budu cht\u00edt rozprost\u0159\u00edt do v\u0161ech bun\u011bk oblasti F6:F15, tedy ve VBA se tento v\u00fdpo\u010det objev\u00ed v tomto \u201erend\u017ei\u201c Range (\u201eF6:F15\u201c).Value<\/em><\/strong>.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Jedno\u0159\u00e1dkov\u00fd skript \u0159\u00edk\u00e1 m\u00e9mu excelovsk\u00e9mu listu, aby do oblasti<\/strong> bun\u011bk F6:F15 (1)<\/span><\/strong> vypo\u010d\u00edtal a vlo\u017eil rozd\u00edl mezi hodnotou bu\u0148ky E3 a D3. Po spu\u0161t\u011bn\u00ed makra to vypad\u00e1 n\u00e1sledovn\u011b.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Je to jednoduch\u00e1 ode\u010d\u00edtac\u00ed operace s bu\u0148kami, jenom p\u0159eps\u00e1na do VBA skriptu, nic jin\u00e9ho a tak\u00e9 nic mimo\u0159\u00e1dn\u00e9ho k pochopen\u00ed. Pokud ji\u017e um\u00edm do bu\u0148ky vlo\u017eit n\u011bjakou hodnotu „natvrdo“ nebo pomoc\u00ed n\u011bjak\u00e9ho v\u00fdpo\u010dtu, nebude patrn\u011b nic t\u011b\u017ek\u00e9ho vy\u0159e\u0161it mou z\u00e1kladn\u00ed \u00falohu, vypo\u010d\u00edtat v\u00fdkonnost akcie Goldman Sachs do ur\u010dit\u00e9 bu\u0148ky pomoc\u00ed operac\u00ed s bu\u0148kami, ve kter\u00fdch m\u00e1m pot\u0159ebn\u00e9 hodnoty k v\u00fdpo\u010dtu. V m\u00e9m p\u0159\u00edpad\u011b budu cht\u00edt do \u017elut\u011b podbarven\u00e9 bu\u0148ky F3<\/strong> Range (\u201eF3\u201c).Value<\/em> vyplnit hodnotou vzniklou v\u00fdpo\u010dtem =(E3-D3)*C3, tedy (Last Cena \u2013 Po\u0159izovac\u00ed cena)*Po\u010det akci\u00ed. Cel\u00fd tento skript v\u00fdpo\u010dtu mohu vid\u011bt na obr\u00e1zku<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Do bu\u0148ky F3 (1)<\/span><\/strong> jsem um\u00edstil hodnotu v\u00fdpo\u010dtu, kter\u00fd je tvo\u0159en rozd\u00edlem<\/strong> aktu\u00e1ln\u00ed a po\u0159izovac\u00ed ceny (2), <\/strong>to v\u0161e <\/span><\/span>vyn\u00e1soben\u00e9 po\u010dtem<\/strong> dr\u017een\u00fdch akci\u00ed (3).<\/span><\/strong> Spu\u0161t\u011bn\u00edm procedury spou\u0161t\u011bc\u00ed \u0161ipkou pak mohu vid\u011bt ve sv\u00e9m se\u0161itu v\u00fdsledek.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Tato velmi jednoduch\u00e1 procedura pr\u00e1ce s bu\u0148kami na b\u00e1zi Range je samoz\u0159ejm\u011b pln\u011b funk\u010dn\u00ed v p\u0159\u00edpad\u011b, \u017ee m\u00e1m otev\u0159en jeden excelovsk\u00fd se\u0161it s jedn\u00edm aktivn\u00edm listem, na kter\u00e9m nyn\u00ed pracuji. P\u0159i otev\u0159en\u00ed dal\u0161\u00edho excelovsk\u00e9ho souboru (se\u0161itu) v m\u00e9m po\u010d\u00edta\u010di nebo p\u0159i otev\u0159en\u00e9m se\u0161itu, kter\u00fd bude m\u00edt v\u00edce list\u016f, bude doch\u00e1zet ke zmatk\u016fm, kdy VBA nebude v\u011bd\u011bt, kter\u00e9 bu\u0148ky (nap\u0159\u00edklad C3) m\u00e1m na mysli vyu\u017e\u00edvat, ze kter\u00e9ho listu nebo dokonce ze kter\u00e9ho dal\u0161\u00edho otev\u0159en\u00e9ho se\u0161itu. P\u0159esto\u017ee se takov\u00e9mu nastaven\u00ed dostanu pozd\u011bji, je ji\u017e nyn\u00ed vhodn\u011bj\u0161\u00ed adresovat pr\u00e1ci s bu\u0148kami minim\u00e1ln\u011b na aktu\u00e1ln\u011b otev\u0159en\u00fd list, tedy ten, pro kter\u00fd spou\u0161t\u00edm vytv\u00e1\u0159en\u00e9 makro. Nen\u00ed to nic nemo\u017en\u00e9ho ke proveden\u00ed, instrukci Range<\/strong> p\u0159i\u0159ad\u00edm p\u0159edponu ActiveSheet.<\/strong>, budu tak m\u00edt jistotu, \u017ee se spou\u0161t\u011bn\u00ed bude prov\u00e1d\u011bt na bu\u0148k\u00e1ch na aktu\u00e1ln\u011b aktivn\u00edm se\u0161itu. Mohu pak sv\u016fj v\u00fdpo\u010det vt\u011blen\u00fd do skriptu VBA zdokonalit na tento z\u00e1pis.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 Je tak z obr\u00e1zku patrn\u00e9, \u017ee jsem pouze obohatil p\u016fvodn\u00ed z\u00e1pis Range na ActiveSheet.Range a v\u0161e z\u016fstalo stejn\u00e9. P\u016fvodn\u00ed \u201echud\u0161\u00ed\u201c \u0159\u00e1dek jsem opat\u0159il apostrofem, aby se podbarvil zelen\u011b a do v\u00fdpo\u010dtu nezasahoval. V\u00fdsledek se po spu\u0161t\u011bn\u00ed nov\u011b upraven\u00e9ho makra nezm\u011bnil.\u00a0\u00a0<\/span><\/p>\n

\"\"<\/p>\n

Cells<\/strong><\/span><\/h2>\n

\u00a0\u00a0 Jenom jsem uk\u00e1zal mo\u017enost pr\u00e1ce s\u00a0bu\u0148kami pomoc\u00ed Range, u\u017e budu ukazovat n\u011bco podobn\u00e9ho, ale pomoc\u00ed Cells<\/strong>. Nen\u00ed to \u00fa\u010del vytvo\u0159it po\u010d\u00e1te\u010dn\u00ed zmatek, ale uk\u00e1zat na jednom m\u00edst\u011b oba zp\u016fsoby, proto\u017ee oba zp\u016fsoby budu ve sv\u00fdch budouc\u00edch skriptech vyu\u017e\u00edvat. Lze je toti\u017e pou\u017e\u00edvat sou\u010dasn\u011b a ka\u017ed\u00fd se hod\u00ed pro jin\u00e9 situace. Pr\u00e1ce s\u00a0bu\u0148kami prost\u0159ednictv\u00edm Cells je velmi podobn\u00e1 pr\u00e1ci na b\u00e1zi Range, z\u00e1kladn\u00ed rozd\u00edl je v\u0161ak v\u00a0pojmenov\u00e1n\u00ed bu\u0148ky, se kterou pracuji. ActiveSheet.Range (\u201eB3\u201c)<\/strong> ozna\u010duje bu\u0148ku B3<\/strong> aktu\u00e1ln\u00edho listu tak, jak vid\u00edm jej\u00ed \u201ek\u00f3tov\u00e1n\u00ed\u201c pomoc\u00ed excelovsk\u00e9 tabulky s\u00a0n\u00e1zvy sloupc\u016f pomoc\u00ed p\u00edsmen (sloupec B) a n\u00e1zvy \u0159\u00e1dk\u016f pomoc\u00ed \u010d\u00edsel (\u0159\u00e1dek 3). AktiveSheet.Cells<\/span> (3,2)<\/strong> je pak stejn\u00e1 bu\u0148ka B3<\/strong> z\u00a0aktivn\u00edho excelovsk\u00e9ho listu, jenom je jej\u00ed um\u00edst\u011bn\u00ed pops\u00e1no syntax\u00ed ActiveSheet.Cells (\u010d\u00edslo \u0159\u00e1dku, \u010d\u00edslo sloupce), tedy zcela polopatisticky ActiveSheet.Range (\u201eB3\u201c)<\/strong> = AktiveSheet.Cells (\u0159\u00e1dek \u010d\u00edslo 3, sloupec \u010d\u00edslo 2) = AktiveSheet.Cells (3,2)<\/strong>. Nahrazen\u00ed jednoduch\u00e9ho ozna\u010den\u00ed bu\u0148ky \u010d\u00edslem \u0159\u00e1dku a \u010d\u00edslem sloupce pak bude hojn\u011b vyu\u017e\u00edv\u00e1no v\u00a0pr\u00e1ci s\u00a0cykly, kter\u00fdch si u\u017eiji v\u00a0dal\u0161\u00edm psan\u00ed skript\u016f v\u00edce ne\u017e dost. Vybr\u00e1n\u00ed n\u011bjak\u00e9 oblasti bun\u011bk nam\u00edsto jedin\u00e9 bu\u0148ky pak m\u00e1 podobn\u011b odvozenou syntaxi, mohu si pak zkusit jednoduch\u00fd skript pomoc\u00ed Cells podle obr\u00e1zku n\u00ed\u017ee.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Napsan\u00e9 dva \u0159\u00e1dky skriptu maj\u00ed za \u00fakol<\/strong> do bu\u0148ky nach\u00e1zej\u00edc\u00ed se na p\u00e1t\u00e9m \u0159\u00e1dku a ve t\u0159et\u00edm sloupci (bu\u0148ka C5<\/strong>) zapsat<\/strong> hodnotu 100 (1)<\/span><\/strong> a do oblasti<\/strong> vyty\u010den\u00e9 bu\u0148kou nach\u00e1zej\u00edc\u00ed se na sedm\u00e9m \u0159\u00e1dku a v t\u0159et\u00edm sloupci (C7<\/strong>) a bu\u0148kou nach\u00e1zej\u00edc\u00ed se na t\u0159in\u00e1ct\u00e9m \u0159\u00e1dku a v\u00a0sedm\u00e9m sloupci (F13<\/strong>) zapsat slovo \u201eAaple\u201c (2)<\/span><\/strong>. Spu\u0161t\u011bn\u00edm procedury se tak tak\u00e9 stane.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Mohu si pak ve stejn\u00e9 logice napsat skript pomoc\u00ed Cells na v\u00fdpo\u010det v\u00fdkonnosti akci\u00ed Goldman Sachs, kdy\u017e pro jednotliv\u00e9 bu\u0148ky budu m\u00edt tento popis um\u00edst\u011bn\u00ed.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0\u00a0 Vlastn\u00ed skript pak nebude nic jin\u00e9ho, ne\u017e procedura v\u00fdpo\u010dtu stejn\u00e1 jako pomoc\u00ed Range, pouze bude na b\u00e1zi Cells.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Do bu\u0148ky F3<\/strong> chci zapsat v\u00fdsledek<\/strong> a tato bu\u0148ka se nach\u00e1z\u00ed na 3. \u0159\u00e1dku<\/strong> a v\u00a06. sloupci<\/strong>, proto chci v\u00fdsledkem vyplnit bu\u0148ku ActiveSheet.Cells (3,6)<\/strong>, kterou za\u010d\u00edn\u00e1 skript, postup v\u00fdpo\u010dtu je pak d\u00e1le na \u0159\u00e1dku stejn\u00fd jako u Range, jenom p\u0159epsan\u00fd pomoc\u00ed Cells (1)<\/span>,<\/strong> Za pov\u0161imnut\u00ed stoj\u00ed, \u017ee oproti skriptu pomoc\u00ed Cells v\u00fd\u0161e (2)<\/strong><\/span> tento aktivn\u00ed skript nem\u00e1 p\u0159\u00edpony .Value a stejn\u011b vypo\u010d\u00edtal stejnou hodnotu. V\u00fdsledek je na obr\u00e1zku.<\/span><\/p>\n

\"\"<\/p>\n

Maz\u00e1n\u00ed bun\u011bk<\/strong><\/span><\/h2>\n

\u00a0 Maz\u00e1n\u00ed obsahu bun\u011bk m\u00e1 pak stejnou logiku, jako zji\u0161t\u011bn\u00ed hodnoty bun\u011bk. Stejn\u011b jako v klasick\u00e9m excelovsk\u00e9m listu si mazanou bu\u0148ku nejd\u0159\u00edve vyberu kliknut\u00edm my\u0161\u00ed a n\u00e1sledn\u00fdm stisknut\u00edm kl\u00e1vesy Delete ji sma\u017eu (vypr\u00e1zdn\u00edm jej\u00ed obsah), smaz\u00e1n\u00ed oblasti p\u0159edch\u00e1z\u00ed ozna\u010den\u00ed bun\u011bk ta\u017een\u00edm my\u0161i a n\u00e1sledn\u00e9 op\u011btovn\u00e9 potvrzen\u00ed stisknut\u00edm kl\u00e1vesy Delete. Stejn\u011b postupuje tak\u00e9 VBA skript. Smaz\u00e1n\u00ed bu\u0148ky A3 bude p\u0159edch\u00e1zet jej\u00ed v\u00fdb\u011br pomoc\u00ed Range nebo Cells, s p\u0159ipojen\u00edm p\u0159\u00edpony .Select<\/strong> (Vyber) tedy ActiveSheet.Range (\u201eA3\u201c).Select<\/strong> nebo ActiveSheet.Cells (3,1).Select<\/strong>. T\u00edmto pokynem ozna\u010d\u00edm bu\u0148ku A3. N\u00e1slednou instrukc\u00ed Selection.ClearContents<\/strong> ozna\u010denou bu\u0148ku vyma\u017eu. Stejn\u011b tak pro oblast bun\u011bk<\/strong> nejd\u0159\u00edve vyberu jej\u00ed rozsah pomoc\u00ed Range nebo Cells a p\u0159ipoj\u00edm p\u0159\u00edponu .Select<\/strong> (Vyber). Oblast bun\u011bk B5:C8<\/strong> ozna\u010d\u00edm instrukc\u00ed ActiveSheet.Range (\u201eB5:C8\u201c).Select<\/strong> nebo ActiveSheet.Range (Cells (5,2), Cells(8,3)).Select<\/strong>. N\u00e1sledn\u011b pokynem Selection.ClearContents<\/strong> vyma\u017eu ozna\u010den\u00e9 bu\u0148ky. Na obr\u00e1zku n\u00ed\u017ee je nazna\u010den po\u017eadavek na maz\u00e1n\u00ed bun\u011bk z excelovsk\u00e9ho se\u0161itu.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Nejd\u0159\u00edve budu cht\u00edt vymazat pouze datum (1)<\/span><\/strong> v\u00a0bu\u0148ce A3<\/strong> a n\u00e1sledn\u011b vymazat oblast<\/strong> bun\u011bk C2:F3<\/strong>, zbytek chci ponechat. Napsan\u00fd skript by mohl m\u00edt t\u0159eba tento tvar.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Vyu\u017eil jsem ob\u011b mo\u017enosti z\u00e1pisu pomoc\u00ed Range a Cells v\u00a0jednom skriptu. Nejd\u0159\u00edve jsem vybral pomoc\u00ed Range<\/strong> bu\u0148ku A3 (1)<\/span><\/strong> a pot\u00e9 vybral pomoc\u00ed Cells<\/strong> oblast bun\u011bk C2:F3 (2)<\/span><\/strong>. Vybran\u00e9 jsem pak nechal vymazat pokynem Selection.ClearContents (3)<\/span><\/strong>. V\u00fdsledek po spu\u0161t\u011bn\u00ed makra je na obr\u00e1zku n\u00ed\u017ee.<\/span><\/p>\n

\"\"<\/p>\n

\n

\u00a0\u00a0 V\u00fdb\u011br bu\u0148ky a pr\u00e1ce s\u00a0n\u00ed je z\u00e1kladem budouc\u00edho \u00fasp\u011bchu a stane se pil\u00ed\u0159em budouc\u00ed pr\u00e1ce. P\u0159esto\u017ee existuje n\u011bkolik zp\u016fsob\u016f, jak na to j\u00edt, je v\u00fdb\u011br a pr\u00e1ce s\u00a0bu\u0148kou pomoc\u00ed Range a Cells velmi jednoduch\u00e1 a pr\u00e1ce s\u00a0t\u011bmito prvky p\u0159ejde velmi rychle \u201edo krve\u201c. Po prozkou\u0161en\u00ed z\u00e1kladn\u00edch po\u010detn\u00edch \u00faloh s\u00a0bu\u0148kami a osah\u00e1n\u00ed \u00fapln\u011b z\u00e1kladn\u00edch funkcionalit okna Visual Basic je nyn\u00ed dobr\u00e9 nast\u00ednit, jak vlastn\u011b programov\u00e1n\u00ed slo\u017eit\u011bj\u0161\u00edch \u00faloh m\u016f\u017ee prob\u00edhat. \u00a0<\/span><\/p>\n

„…Rozhod\u010d\u00ed je Hugo a Hugo je v\u016fl\u2026“<\/strong><\/span><\/h3>\n

\u00a0\u00a0 Star\u00e1 a dobr\u00e1 hl\u00e1\u0161ka z fotbalov\u00e9ho hledi\u0161t\u011b podle m\u011b velmi dob\u0159e vystihuje z\u00e1kladn\u00ed princip pr\u00e1ce s VBA. Pokud bych si ji cht\u011bl n\u011bjak jednodu\u0161e logicky rozebrat, tak \u201eemocion\u00e1ln\u011b nestabiln\u00ed\u201c div\u00e1k chce sd\u011blit rozhod\u010d\u00edmu, \u017ee nen\u00ed s jeho prac\u00ed spokojen, nicm\u00e9n\u011b z obavy z na\u0159\u010den\u00ed, \u017ee doty\u010dn\u00e9ho fotbalov\u00e9ho arbitra naz\u00fdv\u00e1 volem, p\u0159enese tento nejb\u011b\u017en\u011bj\u0161\u00ed \u010desk\u00fd vulgarismus na jak\u00e9hosi \u201eHuga\u201c. Pokud se rozhod\u010d\u00ed zrovna nejmenuje Hugo (to by byl fat\u00e1ln\u00ed z\u00e1kladn\u00ed nedostatek hl\u00e1\u0161ky :c), je pro sud\u00edho h\u016f\u0159e rozpoznateln\u00e9, \u017ee si z n\u011bj div\u00e1k utahuje. Ba co v\u00edc, p\u0159esto\u017ee Rozhod\u010d\u00ed z\u016fst\u00e1v\u00e1 st\u00e1le rozhod\u010d\u00edm a V\u016fl je po\u0159\u00e1d volem, m\u016f\u017ee nespokojen\u00fd div\u00e1k vym\u011b\u0148ovat slovo Hugo za libovoln\u00e9 jin\u00e9 jm\u00e9no a obsah sd\u011blen\u00ed z\u016fst\u00e1v\u00e1 nezm\u011bn\u011bn. \u201eRozhod\u010d\u00ed je Pepa a Pepa je v\u016fl\u201c je tak stejn\u00e9 sd\u011blen\u00ed se stejn\u00fdm n\u00e1bojem a stejn\u00fdm obsahov\u00fdm v\u00fdznamem. Slovo Hugo nebo Pepa tak na sebe berou b\u0159emeno sd\u011blen\u00ed, \u017ee jsou volov\u00e9 a toto stigma vola pak p\u0159ed\u00e1vaj\u00ed na jakoukoliv jinou osobu, t\u0159eba zrovna fotbalov\u00e9ho rozhod\u010d\u00edho. V obecn\u011bj\u0161\u00edm pohledu jsou pak Hugo nebo Pepa jak\u00e9si pomysln\u00e9 prom\u011bnn\u00e9, kter\u00e9 jsou schopny nabrat n\u011bjakou hodnotu, kterou jsou schopny p\u0159en\u00e9st na n\u011bkoho nebo na n\u011bjak\u00e9 jin\u00e9 m\u00edsto.<\/span><\/p>\n

Deklarace prom\u011bnn\u00fdch<\/strong><\/span><\/h1>\n

\u00a0\u00a0 Pr\u00e1ce s\u00a0prom\u011bnn\u00fdmi je d\u016fle\u017eitou sou\u010d\u00e1st\u00ed tvorby VBA skript\u016f, proto mus\u00ed b\u00fdt jasn\u00e9, co si pod t\u00edm vlastn\u011b p\u0159edstavit a jak takov\u00e9 prom\u011bnn\u00e9 vlastn\u011b nastavit. Ve smyslu VBA je prom\u011bnn\u00e1<\/strong> m\u00edsto v\u00a0pam\u011bti m\u00e9ho po\u010d\u00edta\u010de<\/strong>, kter\u00e9 si n\u011bjak zapamatovateln\u011b pojmenuji a sd\u011bl\u00edm tomuto m\u00edstu, jak\u00fd bude m\u00edt tvar (form\u00e1t). Druh\u016f prom\u011bnn\u00fdch je mnoho, ve sv\u00e9m programov\u00e1n\u00ed se ale omez\u00edm pouze na nejb\u011b\u017en\u011bj\u0161\u00ed z nich. Budu p\u0159edpokl\u00e1dat, \u017ee ve sv\u00e9m skriptu budu pracovat s datumem<\/strong>, takov\u00fd \u00fadaj o datumu pak bude m\u00edt form\u00e1t Date<\/strong><\/span><\/em>. Pokud budu v\u011bd\u011bt, \u017ee m\u00e1 prom\u011bnn\u00e1 bude cel\u00e9 \u010d\u00edslo<\/strong>, budu ozna\u010deno jako<\/span>\u00a0Integer<\/strong><\/em>. Pokud budu ve v\u00fdpo\u010dtech nakl\u00e1dat s desetinn\u00fdmi \u010d\u00edsly<\/strong>, budou m\u00edt tyto prom\u011bnn\u00e9 ozna\u010den\u00ed Double<\/strong><\/span><\/em>. V p\u0159\u00edpad\u011b, \u017ee prom\u011bnnou bude text<\/strong>, bude m\u00edt prom\u011bnn\u00e1 definovan\u00fd tvar ozna\u010den\u00fd jako String<\/strong><\/span><\/em>, prom\u011bnn\u00e1 Hugo tedy ur\u010dit\u011b bude String.\u00a0 \u00a0<\/span><\/p>\n

\u00a0\u00a0 Zad\u00e1n\u00ed prom\u011bnn\u00e9 pomoc\u00ed skriptu VBA prov\u00e1d\u00edm instrukc\u00ed Dim<\/strong><\/em><\/span>, kter\u00e1 m\u00e1 tvar Dim<\/strong><\/em><\/span> \u201eN\u00e1zev Prom\u011bnn\u00e9\u201c As<\/strong><\/em><\/span> \u201eP\u0159\u00edslu\u0161n\u00fd form\u00e1t\u201c, v\u00a0m\u00e9m konkr\u00e9tn\u00edm fotbalov\u00e9m p\u0159\u00edpad\u011b pak bude ve skriptu naps\u00e1no Dim<\/strong><\/span><\/em> Hugo<\/strong> As String<\/strong><\/span><\/em>. Op\u011bt si mohu vyzkou\u0161et interaktivitu pole pro psan\u00ed VBA skriptu, kter\u00fd po zad\u00e1n\u00ed Dim a n\u00e1zvu prom\u011bnn\u00e9 nab\u00edz\u00ed mo\u017en\u00e9 tvary z\u00a0rozbalovac\u00edho menu, cel\u00fd z\u00e1pis prom\u011bnn\u00e9 Hugo pak vypad\u00e1 takto.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 K \u010demu to je? Prom\u011bnn\u00e1 Hugo bude v\u017edy znamenat n\u011bjak\u00fd textov\u00fd \u00fadaj, kter\u00fd si do ni na\u010dtu z bu\u0148ky excelovsk\u00e9ho listu. D\u00e1le pak ji\u017e ve skriptu budu pracovat s touto prom\u011bnnou, kter\u00e9 budu schopen nap\u0159\u00edklad m\u011bnit jej\u00ed hodnoty. Na obr\u00e1zku n\u00ed\u017ee je n\u011bkolik mo\u017enost\u00ed, kter\u00fdch m\u016f\u017ee hodnota Hugo nab\u00fdvat spolu s po\u017eadavkem na \u0159e\u0161en\u00ed jednoduch\u00e9ho excelovsk\u00e9ho v\u00fdpo\u010detn\u00edho \u00fakolu \u2013 zji\u0161t\u011bn\u00ed \u201eCo je vlastn\u011b rozhod\u010d\u00ed za\u010d?\u201c<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 V\u00a0bu\u0148k\u00e1ch B2, B3<\/strong> a B4<\/strong> jsou jednotliv\u00e9 mo\u017enosti, kter\u00fdch m\u016f\u017ee prom\u011bnn\u00e1<\/strong> Hugo nab\u00fdvat<\/strong> i s\u00a0popiskem um\u00edst\u011bn\u00ed bu\u0148ky v\u00a0listu se\u0161itu pomoc\u00ed Cells. Do bu\u0148ky C6<\/strong> chci po spu\u0161t\u011bn\u00ed makra um\u00edstit v\u00fdsledek<\/strong>, tedy odpov\u011b\u010f na ot\u00e1zku, kterou reprezentuje hodnota<\/strong> prom\u011bnn\u00e9 Hugo<\/strong>. Jednoduch\u00e9 makro pak m\u016f\u017ee vypadat n\u00e1sledovn\u011b.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Sv\u00e9mu makru jsem dal n\u00e1zev RozhodciJeHugo a p\u0159idal stru\u010dn\u00fd popisek, co vlastn\u011b makro \u0159e\u0161\u00ed (1).<\/strong><\/span> Definoval jsem prom\u011bnnou Hugo<\/strong> jako textov\u00fd v\u00fdraz (String<\/strong>), nebude to tedy \u017e\u00e1dn\u00e9 \u010d\u00edslo nebo n\u011bco jin\u00e9ho, ale jen napsan\u00fd text (2)<\/strong><\/span>. Vybavil jsem prom\u011bnnou Hugo hodnotou<\/strong>, kterou si sebou nyn\u00ed ponese, moje volba padla na mo\u017enost z\u00a0\u0159\u00e1dku B4<\/strong>, rozhod\u010d\u00ed toti\u017e je pro tuto chv\u00edli p\u0159edev\u0161\u00edm \u201esuper chlap\u201c (3)<\/span><\/strong>. Do bu\u0148ky<\/strong>, kde chci v\u011bd\u011bt odpov\u011b\u010f (C6), sm\u011bruji<\/strong> nikoliv natvrdo napsan\u00fd text, ale prom\u011bnnou Hugo<\/strong>, kter\u00e1 moment\u00e1ln\u011b nese hodnotu \u201esuper chlap\u201c. (4)<\/strong><\/span>. Makro spust\u00edm spou\u0161t\u011bc\u00edm tla\u010d\u00edtkem (5)<\/strong><\/span>. V\u00fdsledek je tento:<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Pokud bych cht\u011bl b\u00fdt opravdu vulg\u00e1rn\u00ed a nechat \u0159e\u0161en\u00ed ot\u00e1zky t\u00edm nejhor\u0161\u00edm zp\u016fsobem, uprav\u00edm text skriptu pouze o hodnotu prom\u011bnn\u00e9 Hugo.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 P\u0159i\u0159adit prom\u011bnn\u00e9 Hugo hodnotu \u201ev\u016fl\u201c je v\u00a0excelovskom listu ve druh\u00e9m \u0159\u00e1dku<\/strong> a druh\u00e9m sloupci<\/strong> (B2). Proto pozm\u011bn\u00edm pouze um\u00edst\u011bn\u00ed bu\u0148ky, ze kter\u00e9 budu hodnotu bu\u0148ky na\u010d\u00edtat (\u010derven\u00fd obd\u00e9ln\u00edk) a provedu spu\u0161t\u011bn\u00ed makra znovu.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 V\u00fdsledek je ot\u0159epan\u00e1 vulgarita, kterou vygenerovalo velmi jemn\u011b upraven\u00e9 makro. V\u00a0praxi tak na\u010d\u00edt\u00e1n\u00ed hodnot prom\u011bnn\u00fdch bude hr\u00e1t z\u00e1sadn\u00ed roli v\u00a0postupn\u00fdch v\u00fdpo\u010dtech, ji\u017e nyn\u00ed je alespo\u0148 jasn\u00e9, \u017ee se mi bude l\u00e9pe pracovat s\u00a0hodnotou Hugo ne\u017e s\u00a0hodnotou ActiveSheet.Cells (2,2).Value\u2026 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n

\n

Prvn\u00ed testovac\u00ed makro<\/strong><\/span><\/h1>\n

\u00a0\u00a0 Um\u00edm ji\u017e vysledovat hodnotu bu\u0148ky a prov\u00e1d\u011bt nejjednodu\u0161\u0161\u00ed matematick\u00e9 operace s\u00a0obsahem bun\u011bk a tak\u00e9 um\u00edm bu\u0148ky smazat. V\u00edm, co je to prom\u011bnn\u00e1 a jak mohu m\u011bnit jej\u00ed hodnotu a tak\u00e9 s\u00a0touto prom\u011bnnou tak\u00e9 pracovat. Je tak \u010das vytvo\u0159it n\u011bjakou slo\u017eit\u011bj\u0161\u00ed \u00falohu, kde vyu\u017eiji v\u0161echny prozat\u00edm na\u010derpan\u00e9 znalosti. Mohu si p\u0159edstavit, \u017ee m\u00e1m k\u00a0dispozici tyto jednoduch\u00e1 data m\u00e9ho akciov\u00e9ho miniportfolia tvo\u0159en\u00e9ho 120 kusy akci\u00ed Goldman Sachs.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Budu cht\u00edt vyhodnotit n\u011bkolik ukazatel\u016f tohoto obchodu a tyto sestavit do n\u011bjak\u00e9ho p\u0159ehledu, kter\u00fd by se mi vytvo\u0159il na listu excelovsk\u00e9ho se\u0161itu. Mohu nap\u0159\u00edklad cht\u00edt zjistit, kolik dn\u016f<\/strong> jsem aktu\u00e1ln\u011b v\u00a0obchodu, jak\u00fd je zisk\/ztr\u00e1ta na jednu akcii,<\/strong> jak\u00fd je zisk\/ztr\u00e1ta na jeden den<\/strong> dr\u017een\u00e9 investice a kolik celkov\u011b<\/strong> na obchodu vyd\u011bl\u00e1v\u00e1m\/prod\u011bl\u00e1v\u00e1m<\/strong>. Tento p\u0159ehled pak budu cht\u00edt podrobit n\u011bjak\u00e9mu jednoduch\u00e9mu testu, kdy budu m\u011bnit nap\u0159\u00edklad aktu\u00e1ln\u00ed cenu akcie a zji\u0161\u0165ovat, jak se mi zm\u011bn\u00ed pohled na tuto investici.<\/span><\/p>\n

\u00a0 Za\u010dnu p\u0159\u00edpravou makra zad\u00e1n\u00edm Sub<\/strong><\/em><\/span> a stru\u010dn\u00fdm popiskem p\u0159edm\u011btu \u010dinnosti<\/span><\/p>\n

\"\"<\/p>\n

Deklarace prom\u011bnn\u00fdch<\/strong><\/span><\/h2>\n

\u00a0\u00a0 Prvn\u00ed \u010d\u00e1st VBA skriptu bude tvo\u0159it deklarace prom\u011bnn\u00fdch, tedy definov\u00e1n\u00ed, jak\u00fdch tvar\u016f mohou nab\u00fdvat. K\u00a0t\u00e9to \u010dinnosti pou\u017eiji instrukci Dim<\/strong><\/em><\/span> a p\u0159\u00edslu\u0161n\u00fd n\u00e1zev typu prom\u011bnn\u00e9.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Zvolil jsem n\u00e1zvy prom\u011bnn\u00fdch tak, aby co nejl\u00e9pe vystihovaly obsah, kter\u00fd si ponesou. Prom\u011bnn\u00fdmi jsem si ur\u010dil v\u0161e, co by mohlo nab\u00fdvat n\u011bjak\u00e9 budouc\u00ed zm\u011bny, kterou chci pot\u00e9 otestovat. Nejd\u0159\u00edve jsem cht\u011bl definovat den n\u00e1kupu akci\u00ed a dne\u0161n\u00ed datum, abych mohl splnit zadan\u00fd \u00fakol \u2013 nap\u0159\u00edklad zji\u0161\u0165ovat po\u010det dn\u00ed dr\u017een\u00ed akciov\u00e9ho titulu. DnesniDatum<\/strong> a DatumPorizeni<\/strong> jsou budouc\u00ed prom\u011bnn\u00e9, kter\u00e9 maj\u00ed form\u00e1t datum, tak\u017ee jejich deklarace je ..As Date<\/span><\/em> (1)<\/span><\/strong>. Prom\u011bnnou Ticker<\/strong> budu m\u00edt na mysli n\u00e1zev akciov\u00e9 spole\u010dnosti, bude to tedy text,<\/strong> proto Dim<\/span><\/em> Ticker As String <\/span><\/em>(2)<\/span><\/strong>. Prom\u011bnn\u00e1 PocetAkcii<\/strong> deklarovan\u00e1 \u2026As Integer<\/strong><\/em><\/span> znamen\u00e1 dr\u017een\u00fd po\u010det kus\u016f akci\u00ed, tento m\u016f\u017ee nab\u00fdvat hodnot cel\u00fdch \u010d\u00edsel<\/strong>, proto Integer. Prom\u011bnn\u00e9 PorizovaciCena<\/strong> a AktualniCena<\/strong> jsou desetinn\u00e1 \u010d\u00edsla<\/strong> a p\u0159edstavuj\u00ed cenu, za kterou jsem akcii nakoupil (PorizovaciCena) a aktu\u00e1ln\u00ed cenu akci\u00ed (AktualniCena), pro charakter \u010d\u00edsel s desetinn\u00fdmi m\u00edsty jsou tyto prom\u011bnn\u00e9 deklarov\u00e1ny jako \u2026As Double<\/strong><\/span><\/em>.<\/span><\/p>\n

Na\u010dten\u00ed hodnoty prom\u011bnn\u00fdch<\/strong><\/span><\/h2>\n

\u00a0\u00a0 Nyn\u00ed na\u010dtu obsah prom\u011bnn\u00fdch tak, jak m\u00e1m k dispozici data v m\u00e9m excelovsk\u00e9m se\u0161itu. Pro prvn\u00ed dv\u011b prom\u011bnn\u00e9 DnesniDatum<\/strong> a DatumPorizeni<\/strong> m\u00e1m hodnoty tyto.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 DnesniDatum<\/strong>, prom\u011bnn\u00e1 kter\u00e1 m\u00e1 hodnotu 28.6.2020<\/strong> je v\u00a0bu\u0148ce A4,<\/strong> a to znamen\u00e1 um\u00edst\u011bn\u00ed v\u00a0m\u00edst\u011b popsan\u00e9m VBA jako ActiveSheet.Cells (4,1)<\/em>. DatumPorizeni<\/strong>, prom\u011bnn\u00e1, kter\u00e9 m\u00e1 hodnotu 25.6.2020<\/strong> je v bu\u0148ce D4<\/strong>, a to znamen\u00e1 um\u00edst\u011bn\u00ed v m\u00edst\u011b popsan\u00e9m VBA jako ActiveSheet.Cells (4,4)<\/em>. Mohu tedy jednoduch\u00fdm z\u00e1pisem tyto prom\u011bnn\u00e9 naplnit hodnotou konkr\u00e9tn\u00edch datum\u016f v t\u011bchto bu\u0148k\u00e1ch m\u00e9ho excelovsk\u00e9ho listu.<\/span><\/p>\n

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

\u00a0\u00a0 Stejn\u011b tak postupuji u zbyl\u00fdch prom\u011bnn\u00fdch, pro kter\u00e9 jsem si deklaroval jejich typ. Cel\u00e9 na\u010dten\u00ed hodnot do prom\u011bnn\u00fdch pak bude vypadat takto.<\/span><\/p>\n

\"\"<\/p>\n

Skript jednotliv\u00fdch \u00faloh<\/strong><\/span><\/h2>\n

\u00a0\u00a0 Takto p\u0159ipraven\u00fd VBA skript mohu za\u010d\u00edt zat\u011b\u017eovat \u010dinnostmi, kter\u00e9 mi bude popisovat d\u00e1le vytv\u00e1\u0159en\u00fd skript, a kter\u00fd bude vyu\u017e\u00edvat hodnot prom\u011bnn\u00fdch k \u0159e\u0161en\u00ed nejjednodu\u0161\u0161\u00edch \u00fakol\u016f. P\u0159edsevzal jsem si, \u017ee si vytvo\u0159\u00edm p\u0159ehled<\/strong>, ve kter\u00e9m budu cht\u00edt pozorovat, kolik dn\u016f jsem aktu\u00e1ln\u011b v obchodu, jak\u00fd je zisk\/ztr\u00e1ta na jednu akcii, jak\u00fd je zisk\/ztr\u00e1ta na jeden den dr\u017een\u00e9 investice a kolik celkov\u011b na obchodu vyd\u011bl\u00e1v\u00e1m\/prod\u011bl\u00e1v\u00e1m. Tento p\u0159ehled, jak\u00fdsi jednoduch\u00fd formul\u00e1\u0159, um\u00edst\u00edm pod \u00fadaje o obchodu na stejn\u00fd list excelovsk\u00e9ho se\u0161itu, proto si nejd\u0159\u00edve nech\u00e1m do tohoto listu vlo\u017eit hlavi\u010dku takov\u00e9ho p\u0159ehledu, tedy popisy bun\u011bk s n\u00e1zvy sledovan\u00fdch akc\u00ed. Nebude to nic jin\u00e9ho, ne\u017e ji\u017e zn\u00e1m\u00e9 vkl\u00e1d\u00e1n\u00ed slov do konkr\u00e9tn\u00edch bun\u011bk \u201enatvrdo\u201c popsan\u00e9 v \u00favodu \u010dl\u00e1nku. \u201eDen vstupu\u201c bude prvn\u00edm v\u00fdrazem p\u0159ehledu a tento budu cht\u00edt um\u00edstit do bu\u0148ky A6, \u201eDen v\u00fdstupu\u201c budu cht\u00edt um\u00edstit do bu\u0148ky A7, \u201eTitul\u201c budu cht\u00edt um\u00edstit do bu\u0148ky A8. \u201eTrv\u00e1n\u00ed obchodu\u201c bude dal\u0161\u00edm v\u00fdrazem p\u0159ehledu a tento budu cht\u00edt um\u00edstit do bu\u0148ky A9, \u201eZisk\/ztr\u00e1ta na akcii\u201c pak bude druh\u00fdm v\u00fdrazem, kter\u00fd budu cht\u00edt um\u00edstit do bu\u0148ky A10, \u201eZisk\/ztr\u00e1ta na den\u201c bude v bu\u0148ce A11 a nakonec v\u00fdraz \u201eZisk\/ztr\u00e1ta celkem\u201c budu cht\u00edt \u201eodsadit\u201c o jeden pr\u00e1zdn\u00fd \u0159\u00e1dek, proto jej budu cht\u00edt m\u00edt a\u017e v bu\u0148ce A13. Tyto natvrdo napsan\u00e9 texty do bu\u0148ky vlo\u017e\u00edm t\u00edmto skriptem.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Vlo\u017een\u00ed textu do vybran\u00fdch bun\u011bk provedu zad\u00e1n\u00edm um\u00edst\u011bn\u00edm bu\u0148ky op\u011bt pomoc\u00ed Cells a vkl\u00e1dan\u00fd text opat\u0159\u00edm uvozovkami. Spu\u0161t\u011bn\u00edm makra pak mohu vypozorovat v\u00fdsledek na m\u00e9m aktivn\u00edm excelovsk\u00e9m listu<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Na ur\u010den\u00fdch m\u00edstech listu se mi objevily n\u00e1zvy bun\u011bk, kter\u00e9 jsem si jednoduch\u00fdm skriptem vlo\u017eil, aby vytvo\u0159ily hlavi\u010dku jednoduch\u00e9ho p\u0159ehledu (lev\u00e1 \u010derven\u00e1 \u0161ipka), kter\u00fd budu nyn\u00ed zapl\u0148ovat \u00fadaji a v\u00fdpo\u010dty. Tyto v\u00fdpo\u010dty pak budou v bu\u0148k\u00e1ch ozna\u010den\u00fdch \u010derven\u00fdmi obd\u00e9ln\u00edky a budou odpov\u00eddat popisku, kter\u00fd jsem si nyn\u00ed do bun\u011bk vlevo do prvn\u00edho sloupce vlo\u017eil.<\/span><\/p>\n

\u00a0\u00a0 Nebudu nyn\u00ed do bun\u011bk pro \u00fadaje a v\u00fdpo\u010dty uv\u00e1d\u011bt hodnoty jin\u00fdch bun\u011bk, ale budu ji\u017e pracovat s\u00a0prom\u011bnn\u00fdmi,<\/strong> kter\u00e9 jsem si nadefinoval a kter\u00fdm jsem n\u00e1sledn\u011b na\u010detl jejich hodnoty. Proto do prvn\u00ed bu\u0148ky B6<\/strong>, ve form\u00e1tu VBA jako ActiveSheet.Cells (6,2)<\/em><\/strong>, kde chci um\u00edstit datum vstupu do obchodu, a kter\u00e9mu odpov\u00edd\u00e1 vedlej\u0161\u00ed bu\u0148ka p\u0159ehledu s\u00a0vlo\u017een\u00fdm v\u00fdrazem \u201eDen vstupu\u201c, um\u00edst\u00edm prom\u011bnnou DatumPorizeni<\/strong> \u00a0\u00a0<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 Spu\u0161t\u011bn\u00edm makra se mi do tvo\u0159en\u00e9ho p\u0159ehledu zap\u00ed\u0161e datum vstupu do obchodu, kter\u00e9 obsahuje prom\u011bnn\u00e1 DatumPorizeni,<\/strong> tedy hodnota 25.6.2020<\/strong>. Stejnou proceduru provedu tak\u00e9 s hodnotou v dal\u0161\u00ed bu\u0148ce p\u0159ehledu B7,<\/strong> ve form\u00e1tu VBA jako ActiveSheet.Cells (7,2)<\/em><\/strong>, kam chci vlo\u017eit Den v\u00fdstupu z\u00a0obchodu, kter\u00e9mu odpov\u00edd\u00e1 prom\u011bnn\u00e1 DnesniDatum<\/strong>. Stejn\u011b postupuji pro zji\u0161t\u011bn\u00ed \u00fadaje o n\u00e1zvu spole\u010dnosti do bu\u0148ky B8<\/strong>, ve form\u00e1tu VBA jako ActiveSheet.Cells (8,2)<\/em><\/strong>, kam um\u00edst\u00edm obsah prom\u011bnn\u00e9 Ticker<\/strong>.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Spu\u0161t\u011bn\u00ed makra zp\u016fsob\u00ed dal\u0161\u00ed vypln\u011bn\u00ed p\u0159ehledu o Den v\u00fdstupu a Titul obchodovan\u00e9 spole\u010dnosti<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Dal\u0161\u00ed bu\u0148ka B9, <\/strong>ve form\u00e1tu VBA jako ActiveSheet.Cells (9,2)<\/em><\/strong>, pak bude po\u017eadovat vypln\u011bn\u00ed doby trv\u00e1n\u00ed obchodu. Zde ji\u017e nebudu pouze<\/strong> vkl\u00e1dat hodnotu prom\u011bnn\u00e9<\/strong>, ale mus\u00edm prov\u00e9st prvn\u00ed po\u010detn\u00ed operaci<\/strong> s\u00a0prom\u011bnn\u00fdmi.<\/strong> Proto\u017ee po\u010det dn\u016f v obchodu zjist\u00edm ode\u010dten\u00edm data, kdy jsem do obchodu vstoupil od dne\u0161n\u00edho data, zjist\u00edm tuto hodnotu ode\u010dten\u00edm<\/strong> prom\u011bnn\u00fdch DnesniDatum \u2013 DatumPorizeni,<\/strong> tyto toti\u017e v sob\u011b maj\u00ed na\u010dtenou hodnotu 28.6.2020 a 25.6.2020, v\u00fdsledkem operace by pak m\u011blo b\u00fdt \u010dasov\u00e9 obdob\u00ed t\u0159i dny.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Spu\u0161t\u011bn\u00ed cel\u00e9ho makra pak vypln\u00ed p\u0159ehled tak\u00e9 po\u010dtem dn\u016f, po kter\u00fd obchod trv\u00e1.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Nyn\u00ed se ji\u017e tak\u00e9 za\u010d\u00edn\u00e1 r\u00fdsovat prvn\u00ed z\u00e1rodek testovac\u00edho p\u0159\u00edstupu. Pokud toti\u017e ve sv\u00fdch \u00fadaj\u00edch o portfoliu nakoupen\u00fdch akci\u00ed Goldman Sachs ve \u010dtvrt\u00e9m \u0159\u00e1dku zm\u011bn\u00edm Datum po\u0159\u00edzen\u00ed z 25.6.2020 nap\u0159\u00edklad na 20.6.2020 a spust\u00edm makro, ode\u010dten\u00edm prom\u011bnn\u00fdch DnesniDatum \u2013 DatumPorizeni se provede nov\u00fd v\u00fdpo\u010det s jin\u00fdm v\u00fdsledkem, prom\u011bnn\u00e9 toti\u017e v sob\u011b maj\u00ed na\u010dtenou hodnotu 28.6.2020 (z\u016fst\u00e1v\u00e1 nezm\u011bn\u011bno) a novou hodnotu 20.6.2020, v\u00fdsledkem je pak 8, obchod by tak trval osm dn\u016f.<\/span><\/p>\n

\u00a0\u00a0 Obdobn\u011b postupuji v dal\u0161\u00ed bu\u0148ce B10<\/strong>, ve form\u00e1tu VBA jako ActiveSheet.Cells (10,2)<\/em>,<\/strong> kam chci um\u00edstit v\u00fdsledek v\u00fdpo\u010dtu \u201eZisk\/ztr\u00e1ta na akcii<\/strong>\u201c. Tento v\u00fdsledek je op\u011bt ode\u010d\u00edtac\u00ed operac\u00ed mezi prom\u011bnn\u00fdmi AktualniCena \u2013 PorizovaciCena<\/strong>. V bu\u0148ce B11<\/strong>, ve form\u00e1tu VBA jako ActiveSheet.Cells (10,2)<\/em><\/strong> kam chci um\u00edstit v\u00fdsledek v\u00fdpo\u010dtu \u201eZisk\/ztr\u00e1ta na den<\/strong>\u201c ji\u017e provedu slo\u017eit\u011bj\u0161\u00ed operaci, kde zisk\/ztr\u00e1tu na jedn\u00e9 akcii (AktualniCena \u2013 PorizovaciCena) vyn\u00e1sob\u00edm po\u010dtem akci\u00ed na\u010dten\u00fdch do prom\u011bnn\u00e9 PocetAkcii, tedy (AktualniCena \u2013 PorizovaciCena)*PocetAkcii<\/strong>, a to cel\u00e9 pak vyd\u011bl\u00edm<\/strong> po\u010dtem dn\u00ed<\/strong> trv\u00e1n\u00ed obchodu, cel\u00e9 tak ((AktualniCena \u2013 PorizovaciCena)*PocetAkcii)\/( DnesniDatum \u2013 DatumPorizeni)<\/strong>. \u00a0<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Spu\u0161t\u011bn\u00edm makra pak obdr\u017e\u00edm vypln\u011bn\u00ed p\u0159ehledu o tyto dal\u0161\u00ed \u00fadaje<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 V posledn\u00ed bu\u0148ce p\u0159ehledu B13<\/strong>, ve form\u00e1tu VBA jako ActiveSheet.Cells (13,2<\/strong>)<\/em>, kam chci um\u00edstit v\u00fdsledek v\u00fdpo\u010dtu \u201eZisk\/ztr\u00e1ta celkem<\/strong>\u201c um\u00edst\u00edm v\u00fdpo\u010det pomoci prom\u011bnn\u00fdch (AktualniCena \u2013 PorizovaciCena)*PocetAkcii<\/strong>. T\u00edm budu m\u00edt cel\u00fd p\u0159ehled vypln\u011bn pomoc\u00ed VBA skriptu.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Cel\u00fd p\u0159ehled pak spu\u0161t\u011bn\u00edm makra mohu vid\u011bt vypln\u011bn\u00fd takto:<\/span><\/p>\n

\"\"<\/p>\n

Testuji mo\u017enosti<\/strong><\/span><\/h2>\n

\u00a0\u00a0 Cel\u00e9 vytvo\u0159en\u00e9 makro pak provede vyhodnocen\u00ed m\u00e9ho miniportfolia p\u0159esn\u011b podle krok\u016f, kter\u00e9 vyjad\u0159uj\u00ed jednotliv\u00e9 \u0159\u00e1dky skriptu tak, jak jdou jednotliv\u011b za sebou, co \u0159\u00e1dek \u2013 to jeden \u00fakon, kter\u00fd chci prov\u00e9st na sv\u00e9m excelovsk\u00e9m listu do ozna\u010den\u00fdch bun\u011bk. Ka\u017ed\u00e9 spu\u0161t\u011bn\u00ed makra tedy nejd\u0159\u00edve nadeklaruje prom\u011bnn\u00e9 podle jejich n\u00e1zvu a p\u0159i\u0159ad\u00ed jim po\u017eadovan\u00fd typ, pot\u00e9 se hodnoty prom\u011bnn\u00fdch napln\u00ed hodnotami z mnou ozna\u010den\u00fdch bun\u011bk, abych n\u00e1sledn\u011b hodnoty t\u011bchto prom\u011bnn\u00fdch vyu\u017e\u00edval k d\u00edl\u010d\u00edm v\u00fdpo\u010dt\u016fm. Je pak tak\u00e9 jednoduch\u00e9 si p\u0159edstavit, \u017ee pokud zm\u011bn\u00edm n\u011bkterou z hodnot v informac\u00edch o m\u00e9m portfoliu Goldman Sachs, nap\u0159\u00edklad zm\u011bn\u00edm aktu\u00e1ln\u00ed cenu na hodnotu 150 USD, tato se po znovuspu\u0161t\u011bn\u00ed makra na\u010dte do prom\u011bnn\u00e9 AktualniCena a v\u0161echny n\u00e1sledn\u00e9 v\u00fdpo\u010dty, kter\u00e9 jsem si zaznamenal VBA skriptem a kter\u00e9 obsahuj\u00ed prom\u011bnnou AktualniCena, se provedou s touto jej\u00ed novou hodnotou. \u00a0\u00a0\u00a0\u00a0<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 \u00a0Je pak vid\u011bt, \u017ee sn\u00ed\u017een\u00ed aktu\u00e1ln\u00ed ceny na 150 USD na\u010dten\u00e9 do prom\u011bnn\u00e9 AktualniCena zp\u016fsobila ztr\u00e1tu na m\u00e9m testovan\u00e9m akciov\u00e9m miniportfoliu Golman Sachs. Mohu si tak ze sv\u00e9ho excelovsk\u00e9ho listu vyrobit jednoduch\u00fd testovac\u00ed n\u00e1stroj, kter\u00fd mohu obsluhovat stisknut\u00edm tla\u010d\u00edtka po ka\u017ed\u00e9 zm\u011bn\u011b n\u011bjak\u00e9ho \u00fadaje v bu\u0148ce. P\u0159ed ka\u017edou operac\u00ed testu pro jistotu vyma\u017eu star\u00fd vypln\u011bn\u00fd p\u0159ehled, aby se mi data na\u010d\u00edtala do pr\u00e1zdn\u00fdch bun\u011bk listu. Maz\u00e1n\u00ed bun\u011bk provedu podle textu o maz\u00e1n\u00ed bun\u011bk v\u00fd\u0161e v\u00fdb\u011brem oblasti, kterou chci smazat a tuto proceduru um\u00edst\u00edm do skriptu p\u0159ed proceduru vypl\u0148ov\u00e1n\u00ed hlavi\u010dky p\u0159ehledu a samotn\u00e9 v\u00fdpo\u010dty.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0 Vyrobit si tla\u010d\u00edtko<\/strong> na spu\u0161t\u011bn\u00ed makra<\/strong> mohu podle instrukc\u00ed v minul\u00e9m \u010dl\u00e1nku<\/a><\/strong><\/span> z p\u00e1su n\u00e1stroj\u016f na kart\u011b \u201eV\u00fdvoj\u00e1\u0159\u201c. Celou anal\u00fdzu pak budu prov\u00e1d\u011bt ma\u010dk\u00e1n\u00edm na tla\u010d\u00edtko, kter\u00e9 bude spou\u0161t\u011bt vypl\u0148ov\u00e1n\u00ed p\u0159ehledu podle \u00fadaj\u016f v bu\u0148k\u00e1ch o m\u00e9m miniportfoliu. Mohu tak nap\u0159\u00edklad zadat zm\u011bnu, \u017ee jsem neobchodoval akcie Goldman Sachs, ale McDonald\u2018s<\/strong>, do obchodu jsem vstoupil p\u0159ed m\u011bs\u00edcem 27.5.2020<\/strong> a tomu samoz\u0159ejm\u011b odpov\u00edd\u00e1 tak\u00e9 jin\u00e1 vstupn\u00ed cena<\/strong> a jej\u00ed dne\u0161n\u00ed \u00farove\u0148<\/strong>, nav\u00edc jsem po\u0159\u00eddil 283 akci\u00ed<\/strong>. Nov\u00e9 zad\u00e1n\u00ed je pak mo\u017en\u00e9 nastavit nap\u0159\u00edklad jako na obr\u00e1zku.<\/span><\/p>\n

\"\"<\/p>\n

\u00a0\u00a0 Kliknut\u00edm na tla\u010d\u00edtko ke spu\u0161t\u011bn\u00ed makra dojde k na\u010dten\u00ed nov\u00fdch hodnot do prom\u011bnn\u00fdch (zelen\u00fd obd\u00e9ln\u00edk), sma\u017ee se star\u00fd p\u0159ehled t\u00fdkaj\u00edc\u00ed se akci\u00ed Goldman Sachs (\u010derven\u00fd obd\u00e9ln\u00edk) a vypln\u00ed se nov\u00fdmi \u00fadaji o m\u00e9m obchodu s akciemi McDonald\u2019s. Zbrusu nov\u00fd formul\u00e1\u0159 p\u0159ehledu s nov\u011b vypln\u011bn\u00fdmi \u00fadaji a v\u00fdpo\u010dty je pak vygenerov\u00e1n ve zlomku sekundy.<\/span><\/p>\n

\"\"<\/p>\n

\n

\u00a0\u00a0 Pr\u00e1ce s bu\u0148kami a prom\u011bnn\u00fdmi je z\u00e1kladem ka\u017ed\u00e9ho VBA skriptu. Nen\u00ed to v\u016fbec nic slo\u017eit\u00e9ho, nakonec se z takov\u00e9ho psan\u00ed skriptu m\u016f\u017ee st\u00e1t b\u011b\u017en\u00e1 rutina. Jak napsat makro pomoc\u00ed VBA skriptu a nemuset prov\u00e1d\u011bt jeho z\u00e1znam jako v minul\u00e9m \u010dl\u00e1nku nakonec m\u016f\u017ee b\u00fdt pro ka\u017ed\u00e9ho velmi jednoduchou program\u00e1torskou z\u00e1le\u017eitost\u00ed. Jak je patrn\u00e9, nen\u00ed se \u010deho ob\u00e1vat a jedin\u00e9, na co je si d\u00e1t pozor, je spr\u00e1vn\u00e1 syntaxe jednotliv\u00fdch \u0159\u00e1dk\u016f skriptu. Nebudu se ani v budoucnosti pou\u0161t\u011bt do n\u011bjak\u00fdch slo\u017eit\u011bj\u0161\u00edch konstrukc\u00ed, abych se nakonec s\u00e1m nezamotal do nep\u0159ehledn\u00e9ho k\u00f3du a unikal mi smysl vytv\u00e1\u0159en\u00e9ho skriptu. Pokud jsem si ji\u017e nyn\u00ed osvojil z\u00e1kladn\u00ed pr\u00e1ci s bu\u0148kami a tvorbou jednoduch\u00e9ho makra, bude sta\u010dit tyto znalosti obohatit o pozn\u00e1n\u00ed programov\u00e1n\u00ed jednoduch\u00fdch cykl\u016f a rozhodovac\u00edch \u00faloh a m\u00e1m vyhr\u00e1no, toto pak bude p\u0159edm\u011btem dal\u0161\u00edho \u010dl\u00e1nku.<\/span><\/p>\n

\u00a0\u00a0 Se\u0161it s makrem<\/strong> je pak mo\u017en\u00e9 k prozkoum\u00e1n\u00ed k\u00f3du st\u00e1hnou zde<\/a><\/strong><\/span>. P\u0159i otev\u0159en\u00ed mus\u00edte potvrdit volbu \u201ePovolit makra\u201c.<\/span><\/p>\n

\u00a0 Koment\u00e1\u0159e a p\u0159\u00edsp\u011bvky k tomuto \u010dl\u00e1nku pros\u00edm sm\u011brujte do Diskuzn\u00edho f\u00f3ra do tohoto vl\u00e1kna<\/a><\/strong><\/span> k tomuto \u010dl\u00e1nku nebo do obecn\u011bj\u0161\u00ed diskuze o Excelu<\/a><\/strong><\/span> :c)<\/span>\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\u00a0 \u00a0V minul\u00e9m \u010dl\u00e1nku jsem nazna\u010dil, jak si uleh\u010dit pomoc\u00ed z\u00e1znamu makra n\u011bjakou po\u010detn\u00ed \u00falohu a zjistil, \u017ee tento z\u00e1znam mi v excelovsk\u00e9m se\u0161itu na kart\u011b \u201eV\u00fdvoj\u00e1\u0159\u201c a po kliknut\u00ed na n\u00e1stroj \u201eVisual Basic\u201c otev\u0159el tu \u010d\u00e1st aplikace Excelu, kde jsem mohl objevit zaznamenan\u00e9 makro ve form\u011b VBA skriptu. Tak\u00e9 Continue Reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":4582,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[57],"tags":[46],"class_list":["post-4525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backtestovani-a-prace-s-daty","tag-excel"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/dobretrejdy.com\/wp-content\/uploads\/2020\/06\/main-2.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8zd8A-1aZ","jetpack-related-posts":[{"id":4483,"url":"https:\/\/dobretrejdy.com\/?p=4483","url_meta":{"origin":4525,"position":0},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 II.","author":"dobretrejdy :c)","date":"25.6.2020","format":false,"excerpt":"\u00a0\u00a0 Pot\u0159ebuji zjistit, jak se chov\u00e1 n\u00e1kup Put Bear Spreadu s Long Put na ATM strike a vypsanou opc\u00ed o t\u0159i strike n\u00ed\u017ee, po\u0159izovan\u00fd ka\u017ed\u00e9 pond\u011bl\u00ed na weeklys opc\u00edch s expirac\u00ed v p\u00e1tek stejn\u00e9ho t\u00fddne za posledn\u00edch p\u011bt let na titulu JNJ, k tomu chci zjistit to sam\u00e9, ale pro\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/06\/main-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/06\/main-1.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/06\/main-1.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/06\/main-1.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4598,"url":"https:\/\/dobretrejdy.com\/?p=4598","url_meta":{"origin":4525,"position":1},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 IV.","author":"dobretrejdy :c)","date":"4.7.2020","format":false,"excerpt":"\u00a0\u00a0 V\u00a0\u010dl\u00e1nku Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 II. jsem popsal, jak v Excelu otev\u0159\u00edt kartu V\u00fdvoj\u00e1\u0159 a jak vytvo\u0159it prvn\u00ed makro. Toto makro vygenerovalo VBA skript, abych pot\u00e9 napsal, \u017ee nebudu makra zaznamen\u00e1vat, aby mi tvo\u0159ila VBA skript, ale budu postupovat p\u0159esn\u011b naopak, tedy ps\u00e1t VBA skript pro vytvo\u0159en\u00ed makra. Takovou\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/Main.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/Main.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/Main.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/Main.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4720,"url":"https:\/\/dobretrejdy.com\/?p=4720","url_meta":{"origin":4525,"position":2},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 VI.","author":"dobretrejdy :c)","date":"19.7.2020","format":false,"excerpt":"\u00a0\u00a0 Vyrobit anal\u00fdzu t\u0159\u00eddenn\u00edho dr\u017een\u00ed akciov\u00e9ho obchodu pomoc\u00ed Excelu je nyn\u00ed ji\u017e t\u00e9m\u011b\u0159 hra\u010dka. M\u011bnit d\u00e9lky dr\u017een\u00ed takov\u00fdch fiktivn\u00edch obchod\u016f je ji\u017e hra\u010dkou tak\u00e9, toto v\u0161echno bylo pops\u00e1no v minul\u00fdch \u010dl\u00e1nc\u00edch p\u0159i postupn\u00e9m pozn\u00e1v\u00e1n\u00ed, jak funguje z\u00e1kladn\u00ed logika a psan\u00ed VBA skript\u016f. Na p\u011btilet\u00fdch sta\u017een\u00fdch datech z Yahoo.finance jsem toto\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-2.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-2.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-2.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-2.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4656,"url":"https:\/\/dobretrejdy.com\/?p=4656","url_meta":{"origin":4525,"position":3},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 V.","author":"dobretrejdy :c)","date":"12.7.2020","format":false,"excerpt":"\u00a0\u00a0 V\u00fdroba makra pomoc\u00ed skriptu a z\u00e1kladn\u00ed poznatky o jeho zaps\u00e1n\u00ed pomoc\u00ed VBA (kde a jak), operace s bu\u0148kami a jej\u00edmi oblastmi, pr\u00e1ce s listy excelovsk\u00e9ho se\u0161itu, cyklick\u00e1 funkce a prvn\u00ed anal\u00fdza historick\u00fdch dat (prozat\u00edm intradenn\u00ed dr\u017een\u00ed akci\u00ed) pomoc\u00ed velmi jednoduch\u00e9 automatizace pr\u00e1ce vytvo\u0159en\u00fdm VBA skriptem. To by mohlo b\u00fdt\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-1.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-1.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/07\/main-1.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4884,"url":"https:\/\/dobretrejdy.com\/?p=4884","url_meta":{"origin":4525,"position":4},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 VIII.","author":"dobretrejdy :c)","date":"2.9.2020","format":false,"excerpt":"\u00a0\u201e\u2026Pot\u0159ebuji zjistit, jak se chov\u00e1 n\u00e1kup Put Bear Spreadu s\u00a0Long Put na ATM strike a vypsanou opc\u00ed o t\u0159i strike n\u00ed\u017ee, po\u0159izovan\u00fd ka\u017ed\u00e9 pond\u011bl\u00ed na weeklys opc\u00edch s\u00a0expirac\u00ed v\u00a0p\u00e1tek stejn\u00e9ho t\u00fddne za posledn\u00edch p\u011bt let na titulu JNJ, k\u00a0tomu chci zjistit to sam\u00e9, ale pro t\u0159\u00edstrikov\u00fd Call Bull Spread na\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/09\/main.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/09\/main.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/09\/main.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/09\/main.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4803,"url":"https:\/\/dobretrejdy.com\/?p=4803","url_meta":{"origin":4525,"position":5},"title":"Backtestov\u00e1n\u00ed v\u00a0Excelu (VBA) \u2013 VII.","author":"dobretrejdy :c)","date":"10.8.2020","format":false,"excerpt":"\u00a0\u00a0 Napsat jednoduch\u00e9 makro pomoc\u00ed jazyka VBA, vyplnit jej skripty, kter\u00e9 um\u00ed \u010d\u00edst hodnoty z bun\u011bk nebo je form\u00e1tovat, mazat \u010di p\u0159en\u00e1\u0161et na jin\u00e9 m\u00edsto v excelovsk\u00e9m listu, tvo\u0159it prom\u011bnn\u00e9 a vybavovat je hodnotami, zajistit cyklick\u00e9 na\u010d\u00edt\u00e1n\u00ed dat, um\u011bt formulovat z\u00e1kladn\u00ed logick\u00e9 a rozhodovac\u00ed funkce a vytvo\u0159it n\u00e1stroj na z\u00e1m\u011bnu\u2026","rel":"","context":"V \u201eBacktestov\u00e1n\u00ed a pr\u00e1ce s daty\u201c","block_context":{"text":"Backtestov\u00e1n\u00ed a pr\u00e1ce s daty","link":"https:\/\/dobretrejdy.com\/?cat=57"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/08\/main.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/08\/main.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/08\/main.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dobretrejdy.com\/wp-content\/uploads\/2020\/08\/main.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/posts\/4525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4525"}],"version-history":[{"count":16,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/posts\/4525\/revisions"}],"predecessor-version":[{"id":7290,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/posts\/4525\/revisions\/7290"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=\/wp\/v2\/media\/4582"}],"wp:attachment":[{"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dobretrejdy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}