Buď o krok vpred!

Ako si urobiť Reuters / Bloomberg z Yahoo-Finance.

 

Nebolo to tak dávno, čo som Vám ukázal, ako rozbehnúť add-in tool, dnes budem používať základné funkcie, ktoré sa môžu hodiť.

Priznám sa, tento add-in objavujem spolu s vami, takže ak by som niečo nevysvetlil adekvátne, môžete ma doplniť alebo poukázať na užitočnosť, ktorá Vás zaujala.

Čo budeme vytvárať? Túto otázku sa budem snažiť zodpovedať nasledujúcim obrázkom:

Ako sami vidíte, jedná sa o časovú radu vo forme Candlestick a základné finančné ukazatele konkrétneho inštrumentu.

Niektorí možno nechápete, aký to má zmysel pri dnešných platformách typu MetaTrader apod. no ja osobne vidím výhodu v tom, že mám okrem časovej rady cien a objemov k dispozícii prakticky skoro všetky parametre pre FCF model ocenenia akcie (niektoré sa predsa musia individuálne doplniť).

Taktiež veľmi dôležitá je možnosť používania vlastných nápadov (vzorcov) bez nutnej znalosti programovacieho jazyka, čo je motivujúce pre ľudí ktorí bezpodmienečne neštudujú Algorithmic trading na škole.

Hlavnou myšlienkou je vás dokopať k tomu, aby ste si skúsili špekulácie a získavali cit pre trhy. V podstate celý tento blog sa snažím postaviť na myšlienke, aby bežný človek bol pozbavený pocitu informačnej ilúzie a zbytočnej obavy. A práve jednoduchosť Excel-u je priam stvorená pre prvé kroky s analýzou dat.

Mnohí z vás máte talent na prácu s číslami, avšak pre obavy a strach ho nedokážete spoznať a využiť!

Vrátim sa však k podstate článku, teda dvom kľúčovým funkciám:

1) RCHGetYahooHistory("označenie akciového titulu")

2) RCHGetYahooQuotes("označenie akciového titulu",”oblasť symbolov”)

Označenie akciového titulu: je štandardizovaný názov inštrumentu, ktorý ak neovládate – zistíte si ho na Google alebo Yahoo Finance.

Oblasť symbolov: je oblasť buniek, v ktorých máte nadefinované kódy pre jednotlivé ukazovatele (Klikni a zobrazí sa ti tabuľka).

Postup:

Stiahnite si hotovú šablónu niekde na plochu alebo iné miesto na disku.

Označte si bunky I1 až O101. Po označení oblasti napíšte =RCHGetYahooHistory(“YELP”) a potom použite klávesovú kombináciu Ctrl+Shift+Enter.

Označte oblasti dát pod názvami: Open, High, Low a Close a na karte Insert – Charts – Other Charts – All chart types a vyberiete folder s názvom Templates.

   V tomto folder-i sa musí nachádzať stiahnutá šablóna, takže v dialógovom okne zvoľte možnosť Manage Templates…. Objaví sa vám folder, do ktorého presuniete stiahnutú šablónu (súbor vo formáte *.CRX).

 

 

 

Takto vloženú šablónu potom použijeme na označené dáta: Open, High, Low a Close. Výsledok nebude vyzerať 100%-ne, musíme doladiť napr. názov a taktiež rozsah vertikálnej osi (z auto na konkrétnu hodnotu tak aby bolo vidieť dynamiku v plnej kráse).

Ostáva nám už len vytvoriť tabuľku s doplňujúcimi dátami o firme Yelp. Pripravte si jednoduchú predlohu:

Do označenej bunky napíšeme vzorec: =RCHGetYahooQuotes($C$9,D10) kde prvá bunka označuje štandardizovaný názov spoločnosti, YELP (ukotvený), druhá bunka obsahuje symboly napravo od tabuľky (s, n, a2 atď.). Tento vzorec potom skopírujeme do zvyšných prázdnych buniek.

Ak to chcete mať estetické, môžete dodatočné symboly zafarbiť na bielo a taktiež uzamknúť pred nedobrovoľným vymazaním a manipuláciou.

Finálne by Yelp výstup mal vyzerať takto:

Vianočná príloha & PF 2013

 

Vážení čitatelia, vzhľadom k tomu že Vianoce máme predo dvermi a taktiež sa blíži koniec roka, chcel by som sa Vám poďakovať za priazeň a taktiež popriať príjemnú a pokojnú atmosféru.

Dúfam, že budúci rok bude v znamení ďalších nových nápadov, spojených s Excel-om a taktiež, že sa mi podarí rozbehnúť výukový kurz, ktorý by posunul moje poslanie na vyššiu úroveň.

No aby sme neostali len pri postrehoch a prianiach, ukážem Vám, ako urobiť v Excel-i jednoduché PF 2013. Postup je jednoduchý:

1) Príprava dát: ja som pre hodnoty Y a V použil funkciu =randbetween(a,b) kde a je dolná a b je horná hranica.

2) Vloženie grafu: označte si oblasť hodnôt a vložte Bubble chart.

3)  Pripravte si obrázok podľa vkusu a po kliknutí na Format Data series… použite obrázok ako výplň. 

4) Odstráňte mriežky a taktiež vyplňte oblasť grafu požadovanou farbou. Môžete pridať rôzne doplnky a na záver Word Art text typu PF 2013 (pohrajte sa s kozmetickými úpravami).

Finálne PF 2013 by mohlo vyzerať napr. takto:

Tento článok by mal byť pre rok 2012 posledný, ešte raz ďakujem za priazeň a teším sa na Vás aj v novom roku 2013.

Yahoo Finance on-line v Excel-i

Pamätáte sa na článok, v ktorom som sa cez MSN Money Central Investor Stock Quotes pokúšal uploadovať dáta o finančných trhoch? Tak dnes Vám ukážem niečo GENIÁLNE! Týmto nástrojom si môžu pomôcť všetci tí, ktorí sa radi prehrabávajú v časových radách a iných ukazateľoch! Taktiež, kto píše bakalárku alebo diplomovú prácu na technickú analýzu, tento Add-in mu značne uľahčí život!

Podotýkam: je tam obrovské množstvo obchodovaných titulov!

Návod na použitie:

1) kliknite na obrázok a stiahnite požadovaný RAR súbor.

2) Extrahujte obsah do: C:\Program Files\SMF Add-In

3) Prejdite na Štart – Ovládací panel – Hodiny, jazyk a oblasť – Miestne a jazykové nastavenie – Formáty – Formát: Angličtina (USA)

4) Otvorte si aplikáciu Excel – File – Options – Add-Ins – Manage: Excel Add-Ins Go… V možnosti Browse vyberte adresu s SMF Add-In folderom a dajte OK

5) Zaškrtnite možnosť Stock Market Functions Add-In a kliknite na OK

6) Označte si 7 stĺpcov a 10 riadkov, vypíšte funkciu =RCHgetYahooHistory(“kód inštrumentu”)

Výsledok by mal pre firmu Google vyzerať nasledujúco: =RCHgetYahooHistory(“goog”)

Ako ste si už všimli, tento Add-In poskytuje oveľa viac funkcií. S kamarátom sme si overovali rozmanitosť tohto doplnku: našli sme takto aj akcie firiem z Číny a iné exotické inštrumenty. Myslím, že tento nástroj je jeden z viac užitočných, takže vzdelávajte sa a bádajte.

Ako premiestňovať dáta medzi listami (formulár - databáza)

 

Dnešok venujem jednoduchému makru, ktoré mi veľmi zjednodušilo život, keď som robil v controlling-u.

Aj keď sa Vám sample file bude zdať jednoduchý a možno neopodstatnený, verte mi pri veľkých tabuľkách a niekoľkonásobných copy-paste procedúrach vás po čase prestane baviť to nepretržité klikanie na myš.

Osobne Vám odporúčam zvyknúť si pri práci v Excel-i prestať používať myšku. Je to jednak znak toho, že ste pokročilejší užívateľ ale HLAVNE: šetrí to čas a eliminuje monotónnu prácu.

Celkovú koncepciu makra a použitia si pozrite na videu, ja sa budem venovať len jednému riadku z kódu:

Selection.End(xlUp).Offset(r, s).Select

Červenou farbou som zvýraznil pojem Offset, ktorý sa po nahraní makra v kóde neobjaví. Tento argument posúva kurzor z pôvodnej bunky o r riadkov a s stĺpcov.

Takže ak chcete posunúť kurzor z poslednej počiatočnej zaplnenej bunky v databáze na prvú prázdnu (chcete vložiť nové záznamy), použijete nasledujúci kód:

Selection.End(xlUp).Offset(1, 0).Select

Lepší popis procesu tvorby je k dispozícii na nasledujúcom videu:

 

Databáza-formulár

Dúfam, že sa počas tvorby nestratíte, ak áno – kedykoľvek sa ozvite, resp. komentujte.

Makra začiatok: ako si zrýchliť prácu v Excel-i

 

Ak začínate s makrami – nahrávate si niektoré úlohy, aby ste eliminovali “manuálnu prácu”, určite ste si všimli, že napríklad také formátovanie veľkej tabuľky trvá celkom dlho.

Makra globálne slúžia predovšetkým na to, aby zrýchlili prácu s dátami a taktiež šetrili pamäť. V praxi ako analytik budete musieť zobrať pôvodný napr. 80 MB súbor, porozumieť mu a pokúsiť sa čo najviac výpočtových operácii ( komplikované vzorčeky v tabuľkách ) prepísať do VBA kódu, ktorý je v porovnaní so vzorcami v tabuľke:

1) rýchlejší

2) pamäťovo šetrnejší

Čo ak nastane prípad, že máte v súbore niekoľko stotisíc vzorčekov a po aktualizácii dát ich budete chcieť nechať všetky prepočítať?

Alebo je bezvýhradne nutné pri veľkých súboroch nechávať automatické prepočítavanie vzorčekov v súbore?

Každopádne toto sú otázky tak trochu mimo náš článok a vrátim sa k podstate: ako urýchliť realizáciu marka?

Postačí ak použijete dva príkazy:

1) Application.ScreenUpdating = False  -  Application.ScreenUpdating = True

2) Application.Calculation = False  -  Application.Calculate

 

Prvý príkaz ako keby zmrazí – False a potom aktualizuje - True  zobrazenie listu. Druhý príkaz zruší automatické prepočítavanie a Application.Calculate jednorázovo prepočíta celý list. V konečnom dôsledku si sami môžete overiť efektivitu týchto príkazov na videu:

Makra “pomaly” a “rychly”

Zabudol by som - taktiež máte k dispozícii sample file. Ak by sa ho náhodou nepodarilo stiahnuť, napíšte mi na Facebook-u a ja Vám ho pošlem

 

 

Ako Excel sťahuje živé dáta z finančných trhov

 

Pevne verím, že sa nájde niekto z vás koho nadchol svet investovania, burzy, akcie, komodity apod.. Avšak stať sa traderom znamená mať komfortný prístup k užitočným informáciám. 

Povedzte, čo môže byť viac komfortné pre začínajúcich analytikov, ako mať dáta priamo v Exceli bez toho aby ich “manuálne” hľadali na webe?

Pýtate sa, koľko práce za tým bude. No prezradím Vám, že v tomto článku žiaden sample file k dispozícii nebude.

Otvorte si prázdny workbook a začneme.

Do stĺpcov si napíšte vybrané štandardné názvy akciových titulov. Ja som si vybral tieto:

Po takto nadefinovaných tituloch prejdite na záložku Data a vyberte možnosť Existing connections. Keďže sa v mojom prípade jedná o akcie tak zvolím posledný zdroj dát:

Po kliknutí na Open sa nám objaví okno s voľbou bunky, od ktorej chceme aby boli dáta vložené (zvoľte si ľubovoľnú bunku). V Properties… stačí ak vyberiete Refresh every 1 minute a kliknete na OK. Potom stačí ak do posledného okna  vložíte oblasť buniek s nadefinovanými akciovými titulmi a kliknete na OK.

 

POZOR: nezabudnite zaškrtnúť možnosť Use this value/reference for future refreshes

Výsledok sa Vám objaví prakticky okamžite. Tí, ktorým sa to nepodarilo alebo sa nezorientovali– máte k dispozícii video:

Ako v McKinsey & Company používajú Excel

Dnešok bude patriť práci s grafmi, teda jedným špecifickým. Priznám sa, že tento typ grafu som zatiaľ nemal dôvod používať. Avšak ak má človek zopár známych, pre ktorých je práca s Excelom taktiež zdroj obživy – dostávajú sa mu postrehy a ďalšie zaujímavé námety. A práve jeden z nich je tzv. waterfall graf.

Ak zapátrate do histórie, dozviete sa že ho spopularizovala známa konzultantská firma McKinsey & Company. A aby som toto tvrdenie adekvátne podložil, nasledujúci graf skutočne pochádza zo štúdie McKinsey & Company.

Presuňme sa k podstate – kedy sa takýto graf dá využiť a ako ho zostrojiť?

Predstavte si situáciu, keď máte prehľad príjmov a nákladov a chcete efektívne predviesť vývoj P&L. V nasledujúcom sample file môžete naraziť na nasledujúcu tabuľku:

Pri tvorbe grafu musíme vytvoriť tri pomocné stĺpce:

Dolná hranica ( ňou vytvoríme medzeru medzi osou a reálnymi hodnotami )

Telo ( sú hodnoty príjmov/výdajov v rovnakom smere od osi )

Prienik ( hodnota príjmov/výdajov v opačnom smere od osi – potrebujeme pri prechode medzi kladnými a zápornými hodnotami )

Pre naše hodnoty by zdroj dat pre graf mal vyzerať takto:

Z týchto hodnôt urobíte graf nasledujúcim spôsobom:

Cez lištu Insert vložíte graf typu Column a typ Stacked Column.

V zobrazenom grafe vidíte tri typy stĺpčekov. Dva z nich vyjadrujú štruktúru príjmov/výdajov a to Telo a Prienik. Tým pádom im musíte  nadefinovať rovnaký formát HLAVNE ROVNAKÚ FARBU. Dolná hranica ostane bez výplne, teda Format Data Series – Fill – No Fill.

Taktiež v rámci skrášlenia by som zvolil “tučnejšie” stĺpce:Format Data Series –Series Options –  Gap Width – 20%. Finálnu podobu si môžete prispôsobiť podľa vkusu.

Nakoľko viem, že by som nemohol spokojne spávať bez toho, aby som Vám neposkytol pripravený template so vzorcami tak, aby ste si nemuseli manuálne počítať pomocné stĺpce, dovolil som si použiť už otestovanú metodiku vzorcov od tretej osoby.  Výsledný graf z template-u nájdete na liste P&L, ktorý vyzerá takto:

Uvedené vzorce si myslím, že nemá zmysel rozoberať podrobne – článok by bol príliš dlhý a vzhľadom k problematike zbytočne komplexný.

Používame Řešitel alebo Solver (Aby domáce úlohy boli záživnejšie :))

 

Určite ste sa aspoň v škole stretli s problémom (matematickou úlohou), ktorý sa veľmi ťažko počítal ručne! Pre mňa osobne je najhoršie hľadať adekvátny počet hodnôt tak, aby som niečo splnil – isté obmedzujúce podmienky.

Prevažne sa jedná o počty, na ktoré musíte ísť formou pokus, omyl, resp. robíte niekoľko iterácií, až kým nesplníte dané podmienky!

Našťastie nám Excel umožňuje použiť tzv. Solver alebo  Řešitel. Nakoľko som si viac-menej istý, že väčšina z vás ho nebude mať k dispozícii na svojom Excel-i, kliknutím na článok Prvé kroky (Options –> Add-in –> Manage: Excel Add-in –> Řešitel) si môžete zistiť ako ho pridať do aplikácie. Vráťme sa k podstate – riešime slovnú úlohu.

Príklad:

Máme tri typy akcií, do ktorých zvažujeme investovať. V každej z nich máme nadefinované: Výnos, Riziko a Poplatky (viď nasledujúca tabuľka).

Našou úlohou je vytvoriť také portfólio (kombinácia akcií), aby sme splnili minimálny výnos, maximálne prípustné riziko a neprešvihli rozpočet na poplatky (viď nasledujúca tabuľka). Taktiež chceme mať čo najmenej akcií, aby sme sa nestrácali pri sledovaní ich vývoja na burze.K dispozícii je opäť Sample file, ktorý si stiahnite a môžeme začať!

Prejdime na záložku Data a v sekcii Analýza vyberieme voľbu Řešitel (Solver). Objaví sa nám okno v ktorom potrebujeme nadefinovať prakticky celú slovnú úlohu.

Nastavit Cíl: je bunka, pomocou ktorej sa má program orientovať. V našom prípade sa jedná ú súčet množstva kúpených akcií: $G$13

Min Max Hodnota – nastaviť ako má program optimalizovať cieľovú bunku (vyberieme Min, lebo chceme čo najmenej akcií)

Na základe změny proměnných buněk: určíme oblasť buniek, ktoré môže program meniť - $G$8:$G$10 (množstvo nakúpených jednotlivých akcií).

Omezujíci podmínky: nadefinujeme požadovaný výnos,riziko a poplatky (cez možnosť Pridať).

Dôležité je taktiež zaškrtnúť možnosť: Nastavit proměnné bez omezujícich podmínek jako nezáporné (nakupujeme, nie predávame akcie). Kompletná štruktúra nadefinovaného Řešitela bude vyzerať takto:

Potvrdíme možnosťou Řešit a Ok (použiť výsledky riešenia na meniace sa bunky) čakáme na výsledok:

Celkovo sme nakúpili necelé tri akcie – skoro 2,3 kusov Akcie typu III. a cca 0,6 kusov akcie typu II.  Pričom sa nám podarilo splniť obmedzujúce podmienky.

V prípade, že by ste chceli mať celočíselné hodnoty (nie 0,605… kusov akcie), môžete si pridať tieto možnosti ako obmedzujúce podmienky. Pripravený Solver sa zmení o obmedzujúce podmienky takto:

Výsledok bude vyzerať nasledovne:

V porovnaní s predchádzajúcim riešením diametrálne odlišné výsledky, avšak predsa splnené obmedzujúce podmienky.

Takto si môžete nadefinovať v podstate akúkoľvek úlohu a prakticky dospieť k elegantnému riešeniu. Nabudúce budeme pokračovať makrami. Pravdepodobne si na túto srandičku urobíme makro, aby sme nemuseli nepretržite klikať na Řešitel-a .

Makra Message-Box

 

Dnes v tak neskorej nočnej hodine som sa rozhodol opäť obohatiť (osviežiť) vaše znalosti a preto si ukážeme ako vlastne v Excel-i vzniká dialógové okno.

Pre začiatok by bolo vhodné stiahnuť si sample file. Princíp, resp. syntax dialógového okna je veľmi jednoduchý:

Sub Názov makra()
MsgBox "ĽUBOVOLNÝ TEXT"
End Sub

Výstup tohto makra vyzerá nasledovne:

Druhý spôsob –  pomocou ActiveX Controls. Vyrobíme Button a potom dvojklikom mu napíšeme makro. Pozor! v tomto štádiu sa nachádzame v Design mode! Ak pravým tlačidlom kliknete na takto vytvorený button, v Properties si môžete zmeniť napríklad názov alebo jeho farbu.

Ja som v sample file pozmenil:

1) farbu na červenú – BackColor

2) Písmo na biele – Forecolor

3) Text v Button-e – Caption

Čo ak chcem aby sa mi makro automaticky spustilo po otvorení súboru?

Znie to trošku lišiacky, no nie je to žiadna veda, proste zmeníme názov makra takto:

Sub Názov makra()   na   Sub Auto_Open()

Príklad si môžete pozrieť v sample file. To by bolo pre dnešok všetko, no čaká nás veľa článkov. Týmto dúfam, že sa mi podarilo zaujať aspoň aké-také publikum a že už niektorí sa učíte programovať jednotlivé typy makier .

Odblokujeme zaheslovaný list v Excel-i

 

Ako už iste viete v živote sa mnohokrát stretnete s prekážkami, ktoré ste nikdy predtým neriešili. Aj v práci to tak mnohokrát býva. Pred nedávnom som sa stretol s úlohou, kde som mal rozlúštiť jeden vzorec avšak som ho nemohol rozkliknúť, lebo list bol zaheslovaný a nepoznal som heslo.

Čo robiť v takejto situácii?

Po bádaní na internetovom prehliadači som našiel takéto pekné makro:

Sub PasswordBreaker()
   Dim i As Integer, j As Integer, k As Integer
  Dim l As Integer, m As Integer, n As Integer
  Dim i1 As Integer, i2 As Integer, i3 As Integer
  Dim i4 As Integer, i5 As Integer, i6 As Integer
  On Error Resume Next
  For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
  For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
  For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
  For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
      Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
      Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
  If ActiveSheet.ProtectContents = False Then
      MsgBox "One usable password is " & Chr(i) & Chr(j) & _
          Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
          Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
   ActiveWorkbook.Sheets(1).Select
   Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
          Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
          Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
       Exit Sub
  End If
  Next: Next: Next: Next: Next: Next
  Next: Next: Next: Next: Next: Next

End Sub

Aby som bol korektný, nie je z mojej dielne, ale otestoval som ho a funguje! Skúste si stiahnuť nasledujúci sample file.

 

 

Takéto dialógové okno by sa Vám malo zobraziť po tom, čo sa pokúsite rozkliknúť spojené bunky.

1) Otvorte si nový súbor – aktivujte VBA prostredie (Alt + F11)

2) Skopírujte tam spomenuté makro (predtým vložte modul: Insert – module)

3) Zavrite VBA prostredie a kliknite na list, ktorý chcete odblokovať, kliknite na Macros a spuste makro, obsahujúce v názve: PasswordBreaker.

Následne, ak sa Vám objaví podobné dialógové okno, súbor by mal byť zbavený hesla .

Na záver by som Vás chcel povzbudiť k tomu, aby ste otvorili ten zoskupený stĺpec a klikli na tlačidlo.