Posodobljeni menjalni tečaj v Excelu

Večkrat sem analiziral načine za uvoz podatkov v Excel iz interneta z naknadnim samodejnim posodabljanjem. Še posebej:

• V starejših različicah Excela 2007–2013 je bilo to mogoče storiti z neposredno spletno zahtevo.
• Od leta 2010 je to mogoče narediti zelo priročno z dodatkom Power Query.
  

Tem metodam v najnovejših različicah Microsoft Excela lahko sedaj dodate še eno – uvoz podatkov iz interneta v formatu XML z uporabo vgrajenih funkcij.

XML (eXtensible Markup Language = Razširljiv označevalni jezik) je univerzalni jezik, zasnovan za opis vseh vrst podatkov. Pravzaprav je navadno besedilo, vendar s posebnimi oznakami, dodanimi za označevanje strukture podatkov. Številna spletna mesta ponujajo brezplačne tokove svojih podatkov v formatu XML, ki jih lahko vsak prenese. Zlasti na spletni strani Centralne banke naše države (www.cbr.ru) so s pomočjo podobne tehnologije podani podatki o menjalnih tečajih različnih valut. Na spletnem mestu Moskovske borze (www.moex.com) lahko na enak način prenesete kotacije za delnice, obveznice in veliko drugih koristnih informacij.

Od različice 2013 ima Excel dve funkciji za neposredno nalaganje podatkov XML iz interneta v celice delovnega lista: SPLETNA STORITEV (SPLETNA STORITEV) и FILTER.XML (FILTERXML). Delajo v parih – najprej funkcija SPLETNA STORITEV izvede zahtevo do želenega mesta in vrne njen odgovor v formatu XML, nato pa uporabi funkcijo FILTER.XML ta odgovor "razčlenimo" na komponente in iz njega izvlečemo podatke, ki jih potrebujemo.

Oglejmo si delovanje teh funkcij na klasičnem primeru – uvoz menjalnega tečaja katerekoli valute, ki jo potrebujemo za določen datumski interval, s spletne strani Centralne banke naše države. Kot prazno bomo uporabili naslednjo konstrukcijo:

Tukaj:

• Rumene celice vsebujejo začetni in končni datum obdobja, ki nas zanima.
• Modra ima spustni seznam valut z ukazom Podatki – Validacija – Seznam (Podatki — Validacija — Seznam).
• V zelenih celicah bomo uporabili naše funkcije za ustvarjanje poizvedbenega niza in pridobitev odgovora strežnika.
• Tabela na desni je sklic na kode valut (potrebovali jo bomo malo kasneje).
  

Pojdimo!

Korak 1. Oblikovanje poizvedbenega niza

Če želite s spletnega mesta pridobiti zahtevane informacije, jih morate pravilno vprašati. Gremo na www.cbr.ru in odpremo povezavo v nogi glavne strani' Tehnični viri'- Pridobivanje podatkov s pomočjo XML (http://cbr.ru/development/SXML/). Pomaknemo se malo nižje in v drugem primeru (primer 2) bo tisto, kar potrebujemo – pridobivanje menjalnih tečajev za določen datumski interval:

Kot lahko vidite iz primera, mora poizvedbeni niz vsebovati začetne datume (datum_req1) in končnice (datum_req2) obdobja, ki nas zanima, in kodo valute (VAL_NM_RQ), stopnjo, ki jo želimo dobiti. Glavne kode valut najdete v spodnji tabeli:

Popoln vodnik po kodah valut je na voljo tudi na spletni strani centralne banke – glejte http://cbr.ru/scripts/XML_val.asp?d=0

Sedaj bomo oblikovali poizvedbeni niz v celici na listu z:

• operator veriženja besedila (&), da ga sestavite;
  
• Lastnosti VPR (VLOOKUP)najti kodo valute, ki jo potrebujemo v imeniku;
  
• Lastnosti BESEDILO (BESEDILO), ki pretvori datum v skladu z danim vzorcem dan-mesec-leto skozi poševnico.

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

Korak 2. Izvedite zahtevo

Zdaj uporabljamo funkcijo SPLETNA STORITEV (SPLETNA STORITEV) z ustvarjenim poizvedbenim nizom kot edinim argumentom. Odgovor bo dolga vrstica kode XML (bolje je vklopiti prelom besed in povečati velikost celice, če jo želite videti v celoti):

Korak 3. Razčlenitev odgovora

Za lažje razumevanje strukture odgovornih podatkov je bolje uporabiti enega od spletnih razčlenjevalnikov XML (na primer http://xpather.com/ ali https://jsonformatter.org/xml-parser), ki lahko vizualno oblikuje kodo XML, ji doda zamike in poudari sintakso z barvo. Potem bo vse veliko bolj jasno:

Zdaj lahko jasno vidite, da so vrednosti tečaja uokvirjene z našimi oznakami ..., datumi pa so atributi Datum v oznakah .

Če jih želite izvleči, izberite stolpec z desetimi (ali več – če naredite z robom) praznih celic na listu (ker je bil nastavljen 10-dnevni datumski interval) in vnesite funkcijo v vrstico s formulo FILTER.XML (FILTERXML):

Tu je prvi argument povezava do celice z odgovorom strežnika (B8), drugi pa je poizvedbeni niz v XPathu, posebnem jeziku, ki ga je mogoče uporabiti za dostop do potrebnih fragmentov kode XML in njihovo ekstrahiranje. Več o jeziku XPath lahko na primer preberete tukaj.

Pomembno je, da po vnosu formule ne pritisnete Vnesitein bližnjico na tipkovnici Ctrl+Shift+Vnesite, tj. vnesite jo kot matrično formulo (zavit oklepaj okoli nje bo samodejno dodan). Če imate najnovejšo različico Office 365 s podporo za dinamična polja v Excelu, potem preprosto Vnesite, in vam ni treba vnaprej izbrati praznih celic – funkcija sama zavzame toliko celic, kot jih potrebuje.

Za ekstrahiranje datumov bomo storili enako – izbrali bomo več praznih celic v sosednjem stolpcu in uporabili isto funkcijo, vendar z drugo poizvedbo XPath, da pridobimo vse vrednosti atributov datuma iz oznak zapisa:

=FILTER.XML(B8;”//Zapis/@Datum”)

Zdaj v prihodnosti, ko spremenite datume v prvotnih celicah B2 in B3 ali izberete drugo valuto na spustnem seznamu celice B3, se bo naša poizvedba samodejno posodobila in se nanašala na strežnik centralne banke za nove podatke. Če želite posodobitev vsiliti ročno, lahko dodatno uporabite bližnjico na tipkovnici Ctrl+druga+F9.

• Uvozi tečaj bitcoinov v Excel prek Power Queryja
• Uvozite menjalne tečaje iz interneta v starejših različicah Excela