Excel, Mentors Fridays

Automatické nahrání kurzového lístku

10 února, 2023
Štěpán Ficík

Kurzový lístek

Pracujete často s přepočtem kurzů a přemýšlíte nad tím, jak si tuto práci ulehčit? V Excelu existuje hned několik způsobů, jak si proces stahování aktuálních kurzových lístků automatizovat. My si zde konkrétně ukážeme tři způsoby, jak toho dosáhnout.

U všech zde navrhovaných způsobů je zapotřebí, abyste měli nainstalovanou verzi MS Excel 2016 a novější.

Pokud chcete detailnější popis problematiky s vysvětlením, mrkněte na naše video kde vše vyjma datového typu Měny vysvětlujeme 🙂

Použití datového typu Měny

Tento přístup doporučujeme, pokud potřebujete pokaždé znát poslední známou hodnotu směnného kurzu.

Pokud nevidíte ve své verzi Excelu “Datové typy” pod záložkou “Data”, zkontrolujte zda máte verzi Microsoft 365 a pokud tuto možnost přesto nevidíte, je třeba se zaregistrovat do programu Windows Insider, díky kterému získáte přístup k novým funkcím Excelu dříve než ostatní. Více informací na odkazu.

Zadejte do buňky měnový pár, např. pro směnný kurz z jednoho amerického dolaru na české koruny, zadejte “USD/CZK”
Klikněte na buňku a zároveň na záložce Data v sekci Datové typy klikněte na položky Měny.
Pokud Excel najde shodu mezi dvojící měn a poskytovatelem dat pro tento účel, text se převede na datový typ a před “USD/CZK” se objeví ikona měny.
Pokud se Vám ikona nezobrazila, resp. se Vám místo ní ukazuje otazník, máte s největší pravděpodobností špatně napsaný text – ten tedy zkontrolujte a zkuste to znovu.
Jakmile budete mít veškerá data co potřebujete připravena, můžete se směnným kurzem pracovat. Pro aktualizování dat na nejnovější hodnoty stačí přejít na záložku Data → Aktualizovat vše a získat tak aktualizovaná data.

Použití PowerQuery a napojení se na kurzovní lístek České národní banky (ČNB)

Tento přístup doporučujeme, pokud potřebujete sledovat směnné kurzy i historicky, např. pro přepočet obchodních dat po jednotlivých dnech. Pro více informací mrkněte na video na vrchu této stránky.

Jako zdroj nám poslouží data ČNB, konkrétně pro roční historii po dnech použijeme následující odkaz: https://www.cnb.cz/cs/financni-trhy/devizovy-trh/kurzy-devizoveho-trhu/kurzy-devizoveho-trhu/rok_form.html
Vybereme rok 2022 a klikneme na “Spustit sestavu”, čímž se nám otevře nové okno s historickými údaji pro rok 2022. Adresu stránky si zkopírujeme, jelikož bude sloužit jako zdroj na který se v Excelu pomocí PowerQuery budeme odkazovat.
V Excelu si přes záložku Data → Načíst Data → Z jiných zdrojů → Z Webu načteme data
Vložíme odkaz na stránku ČNB s historickými daty
Ještě než v dialogovém okně zvolíme Transformovat data, nastavíme si vlastní oddělovač dat tak, aby se nám pěkně rozdělila do jednotlivých sloupců dle jednotlivých měn. Použijeme stejný oddělovač jako je na stránkách ČNB, tedy | a teprve poté přistoupíme k Transformaci dat.PS: Pokud se Vám data načtou a záhlaví máte v prvním řádku, použijte v PowerQuery pouze příkazu “Použít první řádek jako záhlaví” na záložce “Domů”.
Když si však data procházíme, vidíme, že od data 02.03.2022 dochází ke změně a jednotlivá data nám nesedí k záhlaví. Je to dáno tím, že se ČNB po vypuknutí války na Ukrajině rozhodla přestat nabízet ve svém směnném kurzovním lístku a to nám ovlivňuje všechna data od Švédské koruny dále. Z tohoto důvodu si ukážeme trochu složitější, ale za to zaručený postup jak si udržet i historické směnné kurzy správné napojené. Bude zapotřebí si vytvořit tabulku se směnnými kurzy od 01.01.2022 do 01.03.2022 zvlášť, poté vytvořit tabulku od 02.03.2022 dále a tyto dvě tabulky v jiném dotazu společně spojit. Tím si zajistíme, že se nám všechny kurzy načítají správně.
Pro vytvoření první tabulky budeme vycházet z našeho existujícího dotazu:
- Najdeme si, od jakého řádku se nám data změnila, v tomto konkrétním případě je to řádek 43
- Na záložce Domů → Odebrat řádky → Odebrat střídavé řádky zvolíme pro možnost “První řádek, který má být odebrán” číslo 43, pro možnost “Počet řádků, který má být odebrán” zvolíme např. 400 (rok má 365 dní, tj. zde máme jistotu, že více jak 365 řádků v dotazu nebude, ale pro jistotu jich dáváme 400) a pro možnost “Počet řádků, který má být zachován” zvolíme číslo 0, tj. chceme zachovat pouze data za období 01.01.2022 – 01.03.2022.
- Tímto nám zůstane zachován kurzovní lístek pouze pro toto období
Pro vytvoření druhé tabulky budeme vycházet opět z existujícího dotazu, resp. z první vytvořené tabulky.
- Tu si pro začátek duplikujeme kliknutím pravým tlačítkem na dotaz a zvolením možnost “Duplikovat”
- Jelikož potřebujeme pracovat s daty od 02.03.2022 a také máme jiné záhlaví, bude třeba po pravé straně zrušit jednotlivé krok vyjma položky “Zdroj” tak, abychom neměli ani první řádek přetvořený na záhlaví.Tedy zdrojový soubor bude mít následující podobu:
- Stejně jako jsme u předchozí tabulky mazali řádky následující po 01.03.2022, nyní potřebujeme naopak odmazat řádky před datem 02.03.2022. Najdeme si tedy opět na kterém řádku dochází ke změně a zjistíme, že se jedná o řádek 43. Přes možnost Domů → Odebrat řádky → Odebrat horní řádky zvolíme počet 43 tak, aby se nám po transformaci na prvním řádku objevilo záhlaví. To poté opět přes Domů → Použít první řádek jako záhlaví změníme.
Když nyní máme obě tabulky připravené, můžeme je přes spojit v jednu. Přes Domů → Připojit dotazy klikneme na šipku vedle a zvolíme Připojit dotazy jako nové. Jako první tabulku zvolíme tabulku s daty od 01.01.2022 do 01.03.2022 a jako druhou tabulku zvolíme data od 02.03.2022 dále.
- Tento dotaz / tabulku si můžeme přejmenovat tak, abychom vždy poznali, že se jedná o vstupní tabulku pro naše výpočty, např. jako FX (Foreign exchange).
- Můžeme vybrat pouze ty kurzy, s kterými chceme opravdu pracovat, nejčastěji EUR, GBP, USD. Toho dosáhneme tak, že si pomocí klávesy Ctrl označíme všechny sloupce, s kterými pracovat nechceme a buďto přes Domů → Odebrat sloupce případně pravým kliknutím na kterýkoli z označených sloupců a zvolením možnosti Odebrat sloupce.
  Druhou, v tomto případě rychlejší variantou může být označení sloupců Datum, 1 EUR, 1 GBP a 1 USD a zvolením možnosti Odebrat ostatní sloupce. Výsledný efekt je stejný.
- Výsledkem je dotaz/tabulka obsahující pouze měny, s kterými chceme pracovat. Může se však stát, že se nám oba dotazy spojily, ale v jiných formátech. Tedy pro zajištění toho, že pracujeme vždy se stejnými daty klikneme na symbol formátu vedle názvu záhlaví a zvolíme patřičný formát. Například v případě datumu klikneme na symbol a zvolíme formát Datum. U jednotlivých měn pak aplikujeme možnost Desetinné číslo. Druhou variantou jak dosáhnout stejného, a v případě měn rychlejšího výsledku je označení všech sloupců a přes Transformace → Datový typ: Desetinné místo změníme hromadně datový typ u všech sloupců.
Jakmile máme všechny tyto kroky hotové, můžeme již pouze přes záložku Domů přejít na možnost Zavřít a načíst. Načtou se nám všechny dotazy do tabulek do jednotlivých lístů. Kromě listu obsahující naší zkombinovanou tabulku můžeme všechny ostatní listy smazat.
Výsledkem je tedy finální tabulka, s kterou můžeme nadále pracovat. Jak s ní můžete pracovat můžete vidět v přiloženém videu.

Použití funkce Stockhistory

Tento postup navrhujeme použít v případě, kdy chcete mít jistotu historických dat kurzovního lístku bez nutnosti spoléhat se na zachování struktury dat u ČNB. Anebo prostě pokud je pro Vás PowerQuery až moc matoucí 🙂 Postup opět více popsán v přiloženém videu.

Jedná se o dynamickou funkci, která se bude automaticky sama

Do prázdných buněk si připravíme vše, co budeme potřebovat, tj. “Burzovní symbol” směny, tj. opět např. USD/CZK, dále pak počáteční a konečně datumy období, u něhož chceme vidět historický vývoj.
Do prázdné buňky začneme psát funkci STOCKHISTORY, která má následující syntaxi:
- Burzovní_symbol: Symbol finančního nástroje, který má být zohledněn, nebo datový typ Akcie – např. USD/CZK
- Počáteční_Datum: První datum, ze kterého se mají vrátit data.
- Koncové_datum: Poslední datum, ze kterého se mají vrátit data.
- Interval: Číslo udávající podrobnost zobrazených dat:
  - 0 – denně
  - 1 – týdně
  - 2 – měsíčně
- Záhlaví: Logická hodnota pro přidání dat záhlaví sloupce:
  - 0 – Žádné záhlaví sloupce
  - 1 – zobrazit záhlaví sloupce
  - 2 – zobrazit identifikátor nástroje a záhlaví sloupce
- Vlastnosti: Další možnost zobrazení, a to sice:
  - 0 – Datum – Datum obchodu (Pokud není zvolena žádná vlastnost, je vybráno automaticky)
  - 1 – Uzavření – Cena, za kterou se v dané časové období instrument naposledy obchodoval (Pokud není zvolena žádná vlastnost, je vybráno automaticky)
  - 2 – Otevření – Cena, s kterou bylo obchodování v daném časovém období otevřeno
  - 3 – Maximum – Nejvyšší cena instrumentudv dané časové období
  - 4 – Minimum – Nejnižší cena instrumentu v dané časové období
  - 5 – Objem – Počet jednotlivých kusů instrumentu zobchodovaných v daném časovém období (Pro měny bude vracet chybovou hodnotu, funguje spíše pro akciové tituly, např. AVST = Avast.
Do jednotlivých částí funkce dosadíme odkazy na buňky obsahující burzovní symbol, počáteční a konečně datum. V případě, že chceme vidět např. směnný kurz USD/CZK za období 01.01.2022 až 31.12.2022 (tj, příprava pro celý rok, kurzy se nám budou každý den s otevřením sešitu automaticky načítat), cenu s jakou burza v daný den otevírala, zavírala, jaké bylo maximum a jaké bylo minimum, vypadal by vzorec a výsledek následovně: