Atjaunināts valūtas maiņas kurss programmā Excel

Esmu vairākkārt analizējis veidus, kā importēt datus programmā Excel no interneta ar sekojošu automātisku atjaunināšanu. It īpaši:

• Vecākajās Excel versijās 2007–2013 to var izdarīt, izmantojot tiešu tīmekļa pieprasījumu.
• Sākot ar 2010. gadu, to ļoti ērti var izdarīt ar Power Query pievienojumprogrammu.
  

Šīm metodēm jaunākajās Microsoft Excel versijās tagad var pievienot vēl vienu – datu importēšanu no interneta XML formātā, izmantojot iebūvētās funkcijas.

XML (eXtensible Markup Language = Extensible Markup Language) ir universāla valoda, kas izstrādāta, lai aprakstītu jebkāda veida datus. Faktiski tas ir vienkāršs teksts, taču tam ir pievienoti īpaši tagi, lai atzīmētu datu struktūru. Daudzas vietnes nodrošina bezmaksas datu straumes XML formātā, lai ikviens to varētu lejupielādēt. Mūsu valsts centrālās bankas vietnē (www.cbr.ru) jo īpaši ar līdzīgas tehnoloģijas palīdzību tiek sniegti dati par dažādu valūtu kursiem. No Maskavas biržas tīmekļa vietnes (www.moex.com) varat lejupielādēt akciju, obligāciju un daudz citas noderīgas informācijas cenas tādā pašā veidā.

Kopš 2013. gada versijas programmai Excel ir divas funkcijas tiešai XML datu ielādei no interneta darblapas šūnās: TĪMEKĻA PAKALPOJUMI (WEBPAKALPOJUMI) и FILTER.XML (FILTERXML). Viņi strādā pa pāriem – vispirms funkcija TĪMEKĻA PAKALPOJUMI izpilda pieprasījumu vajadzīgajai vietnei un atgriež atbildi XML formātā un pēc tam izmantojot funkciju FILTER.XML mēs “parsējam” šo atbildi komponentos, iegūstot no tās nepieciešamos datus.

Apskatīsim šo funkciju darbību, izmantojot klasisku piemēru – jebkuras mums nepieciešamās valūtas kursa importēšanu uz noteiktu datumu intervālu no Mūsu Valsts Centrālās bankas mājas lapas. Kā sagatavi izmantosim šādu konstrukciju:

Šeit:

• Dzeltenajās šūnās ir norādīts mūs interesējošā perioda sākuma un beigu datums.
• Zilajā ir nolaižamais valūtu saraksts, izmantojot komandu Dati – Validācija – Saraksts (Dati — Validācija — saraksts).
• Zaļajās šūnās mēs izmantosim savas funkcijas, lai izveidotu vaicājuma virkni un saņemtu servera atbildi.
• Tabulā labajā pusē ir atsauce uz valūtu kodiem (mums tas būs nepieciešams nedaudz vēlāk).
  

Ejam!

1. darbība. Vaicājuma virknes veidošana

Lai saņemtu nepieciešamo informāciju no vietnes, jums tā ir jājautā pareizi. Mēs ejam uz www.cbr.ru un atveram saiti galvenās lapas kājenē. Tehniskie resursi - Datu iegūšana, izmantojot XML (http://cbr.ru/development/SXML/). Ritinām nedaudz zemāk, un otrajā piemērā (2. piemērs) būs tas, kas mums nepieciešams – iegūt valūtas maiņas kursus konkrētam datuma intervālam:

Kā redzams no piemēra, vaicājuma virknē ir jābūt sākuma datumiem (date_req1) un galotnes (date_req2) mūs interesējošo periodu un valūtas kodu (VAL_NM_RQ), kuras likmi vēlamies iegūt. Galvenos valūtu kodus varat atrast tabulā:

Pilnīga valūtu kodu rokasgrāmata ir pieejama arī Centrālās bankas vietnē – skatiet http://cbr.ru/scripts/XML_val.asp?d=0

Tagad mēs izveidosim vaicājuma virkni lapas šūnā ar:

• teksta savienošanas operators (&), lai to saliktu kopā;
  
• Apkalpošana VPR (MEKLĒT)lai direktorijā atrastu mums nepieciešamās valūtas kodu;
  
• Apkalpošana TEKSTS (TEKSTS), kas pārvērš datumu atbilstoši norādītajam modelim diena-mēnesis-gads, izmantojot slīpsvītru.

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

2. darbība. Izpildiet pieprasījumu

Tagad mēs izmantojam funkciju TĪMEKĻA PAKALPOJUMI (WEBPAKALPOJUMI) ar ģenerēto vaicājuma virkni kā vienīgo argumentu. Atbilde būs gara XML koda rinda (labāk ir ieslēgt vārdu aplaušanu un palielināt šūnas izmēru, ja vēlaties to redzēt pilnībā):

3. darbība. Atbildes parsēšana

Lai būtu vieglāk izprast atbilžu datu struktūru, labāk ir izmantot kādu no tiešsaistes XML parsētājiem (piemēram, http://xpather.com/ vai https://jsonformatter.org/xml-parser), kas var vizuāli formatēt XML kodu, pievienojot tam atkāpes un izceļot sintaksi ar krāsu. Tad viss kļūs daudz skaidrāks:

Tagad jūs varat skaidri redzēt, ka kursu vērtības ir ierāmētas ar mūsu tagiem ..., un datumi ir atribūti datums tagos .

Lai tos izvilktu, lapā atlasiet kolonnu ar desmit (vai vairāk — ja tas izdarīts ar piemali) tukšām šūnām (jo tika iestatīts 10 dienu datuma intervāls) un ievadiet funkciju formulas joslā. FILTER.XML (FILTRSXML):

Šeit pirmais arguments ir saite uz šūnu ar servera atbildi (B8), bet otrais ir vaicājuma virkne XPath, īpašā valodā, ko var izmantot, lai piekļūtu nepieciešamajiem XML koda fragmentiem un tos izvilktu. Vairāk par XPath valodu varat lasīt, piemēram, šeit.

Ir svarīgi, lai pēc formulas ievadīšanas nespiediet ienāktun tastatūras īsceļu Ctrl+maiņa+ienākt, ti, ievadiet to kā masīva formulu (cirtainās iekavas ap to tiks pievienotas automātiski). Ja jums ir jaunākā Office 365 versija ar dinamisko masīvu atbalstu programmā Excel, tad vienkārši ienākt, un nav nepieciešams iepriekš atlasīt tukšas šūnas – funkcija pati aizņems tik šūnu, cik tai nepieciešams.

Lai izvilktu datumus, mēs darīsim to pašu – blakus kolonnā atlasīsim vairākas tukšas šūnas un izmantosim to pašu funkciju, bet ar citu XPath vaicājumu, lai iegūtu visas datuma atribūtu vērtības no ieraksta tagiem:

=FILTER.XML(B8;”//Ierakstīt/@Datums”)

Turpmāk, mainot datumus sākotnējās šūnās B2 un B3 vai izvēloties citu valūtu šūnas B3 nolaižamajā sarakstā, mūsu vaicājums tiks automātiski atjaunināts, atsaucoties uz Centrālās bankas serveri, lai iegūtu jaunus datus. Lai piespiestu atjaunināt manuāli, varat papildus izmantot īsinājumtaustiņus Ctrl+cits+F9.

• Importējiet bitcoin likmi programmā Excel, izmantojot Power Query
• Importējiet valūtas maiņas kursus no interneta vecākās Excel versijās