Aktualizovaný směnný kurz v Excelu

Opakovaně jsem rozebíral způsoby importu dat do Excelu z internetu s následnou automatickou aktualizací. Zejména:

• Ve starších verzích Excelu 2007-2013 to bylo možné provést pomocí přímého webového požadavku.
• Od roku 2010 to lze velmi pohodlně provést pomocí doplňku Power Query.
  

K těmto metodám v nejnovějších verzích Microsoft Excelu nyní můžete přidat další – import dat z internetu ve formátu XML pomocí vestavěných funkcí.

XML (eXtensible Markup Language = Extensible Markup Language) je univerzální jazyk určený k popisu jakéhokoli druhu dat. Ve skutečnosti je to prostý text, ale jsou k němu přidány speciální značky pro označení datové struktury. Mnoho webů poskytuje zdarma streamy svých dat ve formátu XML, které si může kdokoli stáhnout. Zejména na webových stránkách Centrální banky naší země (www.cbr.ru) jsou pomocí podobné technologie uvedeny údaje o směnných kurzech různých měn. Z webových stránek Moskevské burzy (www.moex.com) si stejným způsobem můžete stáhnout kotace akcií, dluhopisů a mnoho dalších užitečných informací.

Od verze 2013 má Excel dvě funkce pro přímé načítání dat XML z internetu do buněk listu: WEBOVÁ SLUŽBA (WEBOVÁ SLUŽBA) и FILTER.XML (FILTERXML). Pracují ve dvojicích – nejprve funkce WEBOVÁ SLUŽBA provede požadavek na požadovaný web a vrátí svou odpověď ve formátu XML a poté pomocí funkce FILTER.XML tuto odpověď „analyzujeme“ do komponent a extrahujeme z ní data, která potřebujeme.

Podívejme se na fungování těchto funkcí na klasickém příkladu – importování kurzu libovolné měny, kterou potřebujeme pro daný časový interval z webu Centrální banky naší země. Jako polotovar použijeme následující konstrukci:

Zde:

• Žluté buňky obsahují datum začátku a konce období, které nás zajímá.
• Modrý má rozevírací seznam měn pomocí příkazu Data – Validace – Seznam (Data — Ověření — Seznam).
• V zelených buňkách použijeme naše funkce k vytvoření řetězce dotazu a získání odpovědi serveru.
• Tabulka vpravo je odkaz na kódy měn (budeme to potřebovat o něco později).
  

Pojďme!

Krok 1. Vytvoření řetězce dotazu

Abyste z webu získali požadované informace, musíte se jich správně zeptat. Přejdeme na www.cbr.ru a otevřeme odkaz v patičce hlavní stránky' Technické zdroje - Získávání dat pomocí XML (http://cbr.ru/development/SXML/). Posuneme se o něco níže a ve druhém příkladu (Příklad 2) bude to, co potřebujeme – získání směnných kurzů pro daný interval dat:

Jak můžete vidět z příkladu, řetězec dotazu musí obsahovat počáteční data (date_req1) a koncovky (date_req2) období, které nás zajímá, a kód měny (VAL_NM_RQ), jehož rychlost chceme získat. Hlavní kódy měn naleznete v tabulce níže:

Kompletní průvodce kódy měn je také k dispozici na webu centrální banky – viz http://cbr.ru/scripts/XML_val.asp?d=0

Nyní vytvoříme řetězec dotazu v buňce na listu s:

• operátor zřetězení textu (&) pro jeho sestavení;
  
• Funkce VPR (VYHLEDAT)abychom v adresáři našli kód měny, kterou potřebujeme;
  
• Funkce TEXT (TEXT), který převede datum podle daného vzoru den-měsíc-rok přes lomítko.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Krok 2. Proveďte požadavek

Nyní použijeme funkci WEBOVÁ SLUŽBA (WEBOVÁ SLUŽBA) s vygenerovaným řetězcem dotazu jako jediným argumentem. Odpovědí bude dlouhá řada kódu XML (pokud ji chcete vidět celou, je lepší zapnout zalamování slov a zvětšit velikost buňky):

Krok 3. Analýza odpovědi

Pro snazší pochopení struktury dat odpovědí je lepší použít některý z online analyzátorů XML (například http://xpather.com/ nebo https://jsonformatter.org/xml-parser), který umí vizuálně formátovat kód XML, přidávat do něj odrážky a zvýraznit syntaxi barvou. Pak bude vše mnohem jasnější:

Nyní můžete jasně vidět, že hodnoty kurzu jsou orámovány našimi značkami ..., a data jsou atributy Datum ve značkách .

Chcete-li je extrahovat, vyberte na listu sloupec deseti (nebo více – pokud se to dělá s okrajem) prázdných buněk (protože byl nastaven 10denní interval data) a zadejte funkci do řádku vzorců FILTER.XML (FILTRXML):

Zde je prvním argumentem odkaz na buňku s odpovědí serveru (B8) a druhým je řetězec dotazu v XPath, speciálním jazyce, který lze použít pro přístup k nezbytným fragmentům kódu XML a jejich extrakci. Více o jazyce XPath si můžete přečíst například zde.

Je důležité, abyste po zadání vzorce netiskli vstoupita klávesovou zkratkou Ctrl+směna+vstoupit, tj. zadejte jej jako maticový vzorec (složené závorky kolem něj budou přidány automaticky). Pokud máte nejnovější verzi Office 365 s podporou dynamických polí v Excelu, pak je to jednoduché vstoupit, a nemusíte předem vybírat prázdné buňky – samotná funkce si vezme tolik buněk, kolik potřebuje.

Pro extrakci dat uděláme totéž – vybereme několik prázdných buněk v sousedním sloupci a použijeme stejnou funkci, ale s jiným dotazem XPath, abychom získali všechny hodnoty atributů Date ze značek Record:

=FILTER.XML(B8;”//Záznam/@Datum”)

Nyní v budoucnu, při změně dat v původních buňkách B2 a B3 nebo výběru jiné měny v rozevíracím seznamu buňky B3, bude náš dotaz automaticky aktualizován s odkazem na server centrální banky pro nová data. Chcete-li aktualizaci vynutit ručně, můžete navíc použít klávesovou zkratku Ctrl+Další+F9.

• Import bitcoinů do Excelu přes Power Query
• Import směnných kurzů z internetu ve starších verzích Excelu