Aktualizovaný výmenný kurz v Exceli

Opakovane som analyzoval spôsoby importu údajov do Excelu z internetu s následnou automatickou aktualizáciou. Konkrétne:

• V starších verziách Excelu 2007-2013 to bolo možné vykonať priamou webovou požiadavkou.
• Od roku 2010 to možno urobiť veľmi pohodlne pomocou doplnku Power Query.
  

K týmto metódam v najnovších verziách programu Microsoft Excel teraz môžete pridať ďalšiu – import údajov z internetu vo formáte XML pomocou vstavaných funkcií.

XML (eXtensible Markup Language = Extensible Markup Language) je univerzálny jazyk určený na popis akéhokoľvek druhu údajov. V skutočnosti je to obyčajný text, ale sú k nemu pridané špeciálne značky na označenie štruktúry údajov. Mnoho stránok poskytuje bezplatné toky svojich údajov vo formáte XML, aby si ich mohol stiahnuť ktokoľvek. Najmä na webovej stránke Centrálnej banky našej krajiny (www.cbr.ru) sú pomocou podobnej technológie uvedené údaje o výmenných kurzoch rôznych mien. Z webovej stránky Moskovskej burzy (www.moex.com) si rovnakým spôsobom môžete stiahnuť kotácie akcií, dlhopisov a mnoho ďalších užitočných informácií.

Od verzie 2013 má Excel dve funkcie na priame načítanie údajov XML z internetu do buniek hárka: WEBOVÁ SLUŽBA (WEBOVÁ SLUŽBA) и FILTER.XML (FILTERXML). Pracujú vo dvojiciach – najprv funkcia WEBOVÁ SLUŽBA vykoná požiadavku na požadovanú stránku a vráti svoju odpoveď vo formáte XML a potom pomocou funkcie FILTER.XML túto odpoveď „analyzujeme“ do komponentov a extrahujeme z nej údaje, ktoré potrebujeme.

Pozrime sa na fungovanie týchto funkcií na klasickom príklade – importovanie kurzu ľubovoľnej meny, ktorú potrebujeme pre daný dátumový interval z webovej stránky centrálnej banky našej krajiny. Ako polotovar použijeme nasledujúcu konštrukciu:

Tu:

• Žlté bunky obsahujú dátumy začiatku a konca obdobia, ktoré nás zaujíma.
• Modrý má rozbaľovací zoznam mien pomocou príkazu Údaje – Validácia – Zoznam (Údaje – Validácia – Zoznam).
• V zelených bunkách použijeme naše funkcie na vytvorenie reťazca dotazu a získanie odpovede servera.
• Tabuľka vpravo je odkaz na kódy mien (budeme to potrebovať o niečo neskôr).
  

Poďme!

Krok 1. Vytvorenie reťazca dotazu

Ak chcete získať požadované informácie zo stránky, musíte sa ich správne opýtať. Ideme na www.cbr.ru a otvoríme odkaz v päte hlavnej stránky“ Technické zdroje - Získavanie údajov pomocou XML (http://cbr.ru/development/SXML/). Posunieme sa trochu nižšie a v druhom príklade (Príklad 2) bude to, čo potrebujeme – získanie výmenných kurzov pre daný interval dátumov:

Ako vidíte z príkladu, reťazec dopytu musí obsahovať dátumy začiatku (date_req1) a koncovky (date_req2) obdobia, ktoré nás zaujíma, a kód meny (VAL_NM_RQ), mieru, ktorú chceme získať. Hlavné kódy mien nájdete v tabuľke nižšie:

Kompletný sprievodca menovými kódmi je dostupný aj na webovej stránke centrálnej banky – pozri http://cbr.ru/scripts/XML_val.asp?d=0

Teraz vytvoríme reťazec dotazu v bunke na hárku s:

• operátor zreťazenia textu (&), ktorý ho spojí;
  
• Vlastnosti VPR (VLOOKUP)aby sme v adresári našli kód meny, ktorú potrebujeme;
  
• Vlastnosti TEXT (TEXT), ktorý prevedie dátum podľa daného vzoru deň-mesiac-rok cez lomku.

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

Krok 2. Vykonajte požiadavku

Teraz použijeme funkciu WEBOVÁ SLUŽBA (WEBOVÁ SLUŽBA) s vygenerovaným reťazcom dopytu ako jediným argumentom. Odpoveďou bude dlhý riadok kódu XML (je lepšie zapnúť zalamovanie slov a zväčšiť veľkosť bunky, ak ju chcete vidieť celú):

Krok 3. Analýza odpovede

Na uľahčenie pochopenia štruktúry údajov odpovedí je lepšie použiť niektorý z online analyzátorov XML (napríklad http://xpather.com/ alebo https://jsonformatter.org/xml-parser), ktorý dokáže vizuálne formátovať kód XML, pridávať doň zarážky a zvýrazniť syntax farbou. Potom bude všetko oveľa jasnejšie:

Teraz môžete jasne vidieť, že hodnoty kurzu sú orámované našimi značkami ..., a dátumy sú atribúty dátum v značkách .

Ak ich chcete extrahovať, vyberte stĺpec desiatich (alebo viacerých – ak sa to robí s okrajom) prázdnych buniek na hárku (pretože bol nastavený 10-dňový interval dátumov) a zadajte funkciu do riadka vzorcov FILTER.XML (FILTERXML):

Tu je prvým argumentom odkaz na bunku s odpoveďou servera (B8) a druhým je reťazec dotazu v XPath, špeciálnom jazyku, ktorý možno použiť na prístup k potrebným fragmentom kódu XML a na ich extrahovanie. Viac o jazyku XPath si môžete prečítať napríklad tu.

Je dôležité, aby ste po zadaní vzorca nestlačili vstúpiťa klávesovú skratku ctrl+smena+vstúpiť, tj zadajte ho ako vzorec poľa (zložené zátvorky okolo neho sa pridajú automaticky). Ak máte najnovšiu verziu Office 365 s podporou dynamických polí v Exceli, potom je to jednoduché vstúpiť, a nemusíte vopred vyberať prázdne bunky – samotná funkcia zaberie toľko buniek, koľko potrebuje.

Ak chcete extrahovať dátumy, urobíme to isté – vyberieme niekoľko prázdnych buniek v susednom stĺpci a použijeme rovnakú funkciu, ale s iným dotazom XPath, aby sme získali všetky hodnoty atribútov Dátum zo značiek Record:

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

Teraz v budúcnosti, keď zmeníte dátumy v pôvodných bunkách B2 a B3 alebo vyberiete inú menu v rozbaľovacom zozname bunky B3, náš dotaz sa automaticky aktualizuje s odkazom na server centrálnej banky pre nové údaje. Ak chcete vynútiť aktualizáciu manuálne, môžete použiť aj klávesovú skratku ctrl+ostatné+F9.

• Importujte bitcoinovú sadzbu do Excelu cez Power Query
• Importujte výmenné kurzy z internetu v starších verziách Excelu