Uuendatud vahetuskurss Excelis

Olen korduvalt analüüsinud võimalusi, kuidas andmeid Internetist Excelisse importida koos hilisema automaatse värskendamisega. Eriti:

• Exceli vanemates versioonides 2007–2013 sai seda teha otsese veebipäringu abil.
• Alates 2010. aastast saab seda väga mugavalt teha Power Query lisandmooduliga.
  

Nendele meetoditele Microsoft Exceli uusimates versioonides saate nüüd lisada veel ühe – andmete importimise Internetist XML-vormingus, kasutades sisseehitatud funktsioone.

XML (eXtensible Markup Language = Extensible Markup Language) on universaalne keel, mis on loodud igasuguste andmete kirjeldamiseks. Tegelikult on see lihttekst, kuid sellele on lisatud andmestruktuuri märgistamiseks spetsiaalsed sildid. Paljud saidid pakuvad oma andmete tasuta vooge XML-vormingus, et igaüks saaks alla laadida. Meie Riigi keskpanga veebisaidil (www.cbr.ru) antakse sarnase tehnoloogia abil andmeid erinevate valuutade vahetuskursside kohta. Moskva börsi veebisaidilt (www.moex.com) saate samamoodi alla laadida aktsiate, võlakirjade ja palju muud kasulikku teavet.

Alates versioonist 2013 on Excelil kaks funktsiooni XML-andmete otse Internetist töölehe lahtritesse laadimiseks: VEEBITEENUS (VEEBITEENUS) и FILTER.XML (FILTERXML). Nad töötavad paaris – kõigepealt funktsioon VEEBITEENUS täidab päringu soovitud saidile ja tagastab oma vastuse XML-vormingus ning seejärel funktsiooni kasutades FILTER.XML parsisime selle vastuse komponentideks, eraldades sellest vajalikud andmed.

Vaatame nende funktsioonide toimimist klassikalise näite varal – mis tahes meile vajaliku valuuta vahetuskursi importimine meie Riigi Keskpanga kodulehelt antud kuupäevaintervalli jaoks. Kasutame toorikuna järgmist konstruktsiooni:

siin:

• Kollased lahtrid sisaldavad meid huvitava perioodi algus- ja lõppkuupäeva.
• Sinisel on valuutade rippmenüü, mis kasutab käsku Andmed – valideerimine – loend (Andmed – valideerimine – loend).
• Rohelistes lahtrites kasutame oma funktsioone päringustringi loomiseks ja serveri vastuse saamiseks.
• Parempoolne tabel on viide valuutakoodidele (meil läheb seda veidi hiljem vaja).
  

Lähme!

Samm 1. Päringustringi moodustamine

Saidilt vajaliku teabe saamiseks peate seda õigesti küsima. Me läheme aadressile www.cbr.ru ja avame lingi avalehe jaluses. Tehnilised ressursid - Andmete hankimine XML-i abil (http://cbr.ru/development/SXML/). Kerime veidi madalamale ja teises näites (näide 2) on see, mida vajame – antud kuupäevavahemiku vahetuskursside saamine:

Nagu näitest näha, peab päringu string sisaldama alguskuupäevi (date_req1) ja lõpud (date_req2) meid huvitava perioodi ja valuuta koodi (VAL_NM_RQ), mille määra soovime saada. Peamised valuutakoodid leiate allolevast tabelist:

Täielik valuutakoodide juhend on saadaval ka keskpanga veebisaidil – vt http://cbr.ru/scripts/XML_val.asp?d=0

Nüüd moodustame lehe lahtris päringustringi, milles on:

• teksti ühendamise operaator (&) selle kokku panemiseks;
  
• FUNKTSIOONID VPR (OTSING)kataloogist meile vajaliku valuuta koodi leidmiseks;
  
• FUNKTSIOONID TEKST (TEKST), mis teisendab kuupäeva etteantud mustri järgi päev-kuu-aasta läbi kaldkriipsu.

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

2. samm. Täitke taotlus

Nüüd kasutame funktsiooni VEEBITEENUS (VEEBITEENUS) genereeritud päringustringiga ainsa argumendina. Vastuseks on pikk rida XML-koodi (parem on reamurdmine sisse lülitada ja lahtri suurust suurendada, kui soovite seda tervikuna näha):

3. samm. Vastuse sõelumine

Vastuste andmete struktuuri mõistmise hõlbustamiseks on parem kasutada üht veebipõhist XML-i parserit (näiteks http://xpather.com/ või https://jsonformatter.org/xml-parser), mis suudab XML-koodi visuaalselt vormindada, lisades sellele taande ja tuues süntaksi värviga esile. Siis saab kõik palju selgemaks:

Nüüd näete selgelt, et kursuse väärtused on raamitud meie siltidega ...ja kuupäevad on atribuudid kuupäev siltides .

Nende ekstraheerimiseks valige lehel kümnest (või enamast veerisega) tühjast lahtrist koosnev veerg (kuna määrati 10-päevane kuupäevavahemik) ja sisestage funktsioon valemiribale. FILTER.XML (FILTERXML):

Siin on esimene argument link serveri vastusega lahtrile (B8) ja teine ​​on XPathi päringutring, mis on spetsiaalne keel, mille abil saab ligi pääseda vajalikele XML-koodi fragmentidele ja neid ekstraktida. XPathi keele kohta saad täpsemalt lugeda näiteks siit.

On oluline, et pärast valemi sisestamist ärge vajutage siseneja klaviatuuri otsetee Ctrl+nihe+sisene, st sisestage see massiivivalemina (selle ümber olevad lokkis sulud lisatakse automaatselt). Kui teil on Office 365 uusim versioon, mis toetab Excelis dünaamilisi massiive, on lihtne sisene, ja te ei pea eelnevalt tühje lahtreid valima – funktsioon ise võtab nii palju lahtreid kui vaja.

Kuupäevade ekstraheerimiseks teeme sama – valime külgnevast veerust mitu tühja lahtrit ja kasutame sama funktsiooni, kuid erineva XPathi päringuga, et saada kõik kuupäeva atribuutide väärtused kirje siltidest:

=FILTER.XML(B8;”//Salvesta/@Kuupäev”)

Nüüd edaspidi, kui muudate kuupäevi algsetes lahtrites B2 ja B3 või valite lahtri B3 rippmenüüst erinevat valuutat, uuendatakse meie päringut automaatselt, viidates uute andmete saamiseks keskpanga serverile. Värskenduse käsitsi sundimiseks saate lisaks kasutada kiirklahvi Ctrl+muu+F9.

• Importige bitcoini määr Excelisse Power Query kaudu
• Importige vahetuskursid Internetist Exceli vanematesse versioonidesse