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.

Regresia–príklad z praxe

 

Tento článok by som kategorizoval do triedy – “čo ma prekvapilo v práci”. Pre mnohých z vás je pojem Regresná analýza veľká neznáma, iní sa s týmto pojmom stretli na kurzoch Štatistiky avšak kto z Vás túto “srandičku” použil alebo videl aplikovanú v reporte a akým spôsobom (výnimku tvoria samozrejme kvantitatívne náročnejšie povolania v rôznych poisťovniach, bankách apod.)?

Aby sme ostali v rozmedzí Excel-u a nie matematických výpočtov, zatiaľ prezradím len to, že za istých podmienok môžete pomocou historických dát odvodiť model – funkciu, pomocou ktorej môžete predpovedať hodnoty v budúcnosti.

Aby som bol konkrétnejší, jedná sa o vzťah medzi dvoma premennými

1) Vysvetľovaná – jej budúcu hodnotu chceme odhadnúť

2) Vysvetľujúca – jej hodnoty sú exogennými vstupmi do modelu, ktorým predpovedáme vysvetľovanú premennú

Ako príklad uvediem vysvetľovanú premennú  Y – množstvo vypestovaných zemiakov a vysvetľujúcu premennú X – množstvo použitých postrekov proti škodcom.

Na to aby sme mohli odvodiť istú formu modelu, musíme si definovať typ. Dnes sa budem zaoberať priamkou s konštantou a bez konštanty, takže model by mohol vyzerať takto:

Takže vidíme, že modely sú veľmi podobné, líšia sa iba konštantou C, tí ktorých trápi zmysel

ε dodám, že táto hodnota sa v konečnom dôsledku vynuluje. V praxi si môžete predstaviť, že viete dopredu koľko chemikálií použijete na svoje pole, túto hodnotu vložíte do funkcie a výstupom bude predpokladaná úroda zemiakov na Vašom poli.

Je samozrejmé, že pri Regresnej analýze závisí hlavne na kvalite modelu, no túto problematiku budem rozoberať neskôr. Nás bude zaujímať len budúca hodnota! Použijeme preto nasledujúcu funkciu:

=TREND(“oblasť vysvetľovaných dát”;”oblasť vysvetľujúcich dát”;”Budúca hodnota vysvetľujúcej premennej”;0 - bez konštanty / 1- s konštantou)

Celý príklad si môžete stiahnuť tu. Ja okomentujem fakt, že v sample file-e je k dispozícii aj náhľad na Regresnú analýzu vrátane relevantných testov, spojených s kvalitou modelu. Táto časť má skôr informačný charakter a dúfam, pre mnohých z Vás aj motivačný.

Ak sa pýtate či som naozaj predpovedal budúcoročnú úrodu konkrétnej farmy, mýlite sa . Túto vskutku užitočnú funkciu som objavil v istom finančnom výkaze, kde sa snažili odhadnúť budúce náklady na povezme základné fixné výdavky firmy.