Opgedateerde wisselkoers in Excel

Ek het herhaaldelik maniere ontleed om data vanaf die internet in Excel in te voer met daaropvolgende outomatiese opdatering. In die besonder:

• In ouer weergawes van Excel 2007-2013 kan dit met 'n direkte webversoek gedoen word.
• Vanaf 2010 kan dit baie gerieflik gedoen word met die Power Query-byvoeging.
  

By hierdie metodes in die jongste weergawes van Microsoft Excel, kan jy nou nog een byvoeg – die invoer van data vanaf die internet in XML-formaat deur gebruik te maak van ingeboude funksies.

XML (eXtensible Markup Language = Extensible Markup Language) is 'n universele taal wat ontwerp is om enige soort data te beskryf. Trouens, dit is gewone teks, maar met spesiale etikette wat daarby gevoeg is om die datastruktuur te merk. Baie werwe bied gratis strome van hul data in XML-formaat vir enigiemand om af te laai. Op die webwerf van die Sentrale Bank van Ons Land (www.cbr.ru), veral, met behulp van 'n soortgelyke tegnologie, word data oor die wisselkoerse van verskillende geldeenhede gegee. Van die Moscow Exchange-webwerf (www.moex.com) kan jy op dieselfde manier kwotasies vir aandele, effekte en baie ander nuttige inligting aflaai.

Sedert weergawe 2013 het Excel twee funksies om XML-data direk vanaf die internet in werkbladselle te laai: WEBDIENS (WEBSERVICE) и FILTER.XML (FILTERXML). Hulle werk in pare – eers die funksie WEBDIENS voer 'n versoek na die verlangde webwerf uit en gee sy antwoord in XML-formaat terug, en gebruik dan die funksie FILTER.XML ons "ontleed" hierdie antwoord in komponente, en onttrek die data wat ons benodig daaruit.

Kom ons kyk na die werking van hierdie funksies deur 'n klassieke voorbeeld te gebruik – die invoer van die wisselkoers van enige geldeenheid wat ons benodig vir 'n gegewe datuminterval vanaf die webwerf van die Sentrale Bank van Ons Land. Ons sal die volgende konstruksie as 'n leegte gebruik:

hier:

• Die geel selle bevat die begin- en einddatums van die tydperk wat vir ons van belang is.
• Die blou een het 'n aftreklys van geldeenhede wat die opdrag gebruik Data – Bekragtiging – Lys (Data — Bekragtiging — Lys).
• In die groen selle sal ons ons funksies gebruik om 'n navraagstring te skep en die bediener se reaksie te kry.
• Die tabel aan die regterkant is 'n verwysing na geldeenheidkodes (ons sal dit 'n bietjie later nodig hê).
  

Kom ons gaan!

Stap 1. Vorm 'n navraagstring

Om die vereiste inligting van die webwerf te kry, moet jy dit korrek vra. Ons gaan na www.cbr.ru en maak die skakel in die voetskrif van die hoofblad oop' Tegniese Hulpbronne'- Kry data met behulp van XML (http://cbr.ru/development/SXML/). Ons blaai 'n bietjie laer en in die tweede voorbeeld (Voorbeeld 2) sal daar wees wat ons nodig het - om die wisselkoerse vir 'n gegewe datuminterval te kry:

Soos u uit die voorbeeld kan sien, moet die navraagstring begindatums bevat (datum_req1) en eindes (datum_req2) van die tydperk van belang vir ons en die geldeenheid kode (VAL_NM_RQ), die koers waarvan ons wil kry. U kan die belangrikste geldeenheidkodes in die tabel hieronder vind:

'n Volledige gids tot geldeenheidkodes is ook beskikbaar op die Sentrale Bank-webwerf – sien http://cbr.ru/scripts/XML_val.asp?d=0

Nou sal ons 'n navraagstring in 'n sel op 'n blad vorm met:

• die teksaaneenskakelingsoperateur (&) om dit saam te stel;
  
• Kenmerke VPR (VERSOEKING)om die kode van die geldeenheid wat ons benodig in die gids te vind;
  
• Kenmerke TEKS (TEKS), wat die datum omskakel volgens die gegewe patroon dag-maand-jaar deur 'n skuinsstreep.

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

Stap 2. Voer die versoek uit

Nou gebruik ons ​​die funksie WEBDIENS (WEBSERVICE) met die gegenereerde navraagstring as die enigste argument. Die antwoord sal 'n lang lyn XML-kode wees (dit is beter om woordomhulsel aan te skakel en die selgrootte te vergroot as jy dit in sy geheel wil sien):

Stap 3. Ontleding van die antwoord

Om dit makliker te maak om die struktuur van die responsdata te verstaan, is dit beter om een ​​van die aanlyn XML-ontleders te gebruik (byvoorbeeld, http://xpather.com/ of https://jsonformatter.org/xml-parser), wat XML-kode visueel kan formateer, inkepings daarby kan voeg en die sintaksis met kleur uitlig. Dan sal alles baie duideliker word:

Nou kan jy duidelik sien dat die kursuswaardes deur ons etikette omraam word ..., en datums is eienskappe datum in etikette .

Om hulle te onttrek, kies 'n kolom van tien (of meer - indien gedoen met 'n kantlyn) leë selle op die blad (omdat 'n 10-dae datuminterval gestel is) en voer die funksie in die formulebalk in FILTER.XML (FILTERXML):

Hier is die eerste argument 'n skakel na 'n sel met 'n bedienerrespons (B8), en die tweede is 'n navraagstring in XPath, 'n spesiale taal wat gebruik kan word om toegang tot die nodige XML-kodefragmente te verkry en dit te onttrek. Jy kan meer oor die XPath-taal lees, byvoorbeeld hier.

Dit is belangrik dat jy nie druk nadat jy die formule ingevoer het nie Tik, en die sleutelbordkortpad Ctrl+Skuif+Tik, dws voer dit as 'n skikkingsformule in (die krulhakies daaromheen sal outomaties bygevoeg word). As jy die nuutste weergawe van Office 365 het met ondersteuning vir dinamiese skikkings in Excel, dan is 'n eenvoudige Tik, en jy hoef nie leë selle vooraf te kies nie – die funksie self sal soveel selle neem as wat dit nodig het.

Om datums te onttrek, sal ons dieselfde doen - ons sal verskeie leë selle in die aangrensende kolom kies en dieselfde funksie gebruik, maar met 'n ander XPath-navraag, om al die waardes van die Datum-kenmerke van die Rekord-etikette te kry:

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

Nou in die toekoms, wanneer die datums in die oorspronklike selle B2 en B3 verander word of 'n ander geldeenheid in die aftreklys van sel B3 gekies word, sal ons navraag outomaties opgedateer word, met verwysing na die Sentrale Bank-bediener vir nuwe data. Om 'n opdatering handmatig af te dwing, kan jy ook die sleutelbordkortpad gebruik Ctrl+alt+F9.

• Voer bitcoin-koers na Excel in via Power Query
• Voer wisselkoerse van die internet af in ouer weergawes van Excel