Uppdaterad växelkurs i Excel

Jag har upprepade gånger analyserat sätt att importera data till Excel från Internet med efterföljande automatisk uppdatering. Särskilt:

• I äldre versioner av Excel 2007-2013 kunde detta göras med en direkt webbförfrågan.
• Från och med 2010 kan detta göras mycket bekvämt med Power Query-tillägget.
  

Till dessa metoder i de senaste versionerna av Microsoft Excel kan du nu lägga till ytterligare en – importera data från Internet i XML-format med hjälp av inbyggda funktioner.

XML (eXtensible Markup Language = Extensible Markup Language) är ett universellt språk utformat för att beskriva alla typer av data. I själva verket är det vanlig text, men med speciella taggar som läggs till för att markera datastrukturen. Många webbplatser tillhandahåller gratis strömmar av sina data i XML-format för alla att ladda ner. På webbplatsen för vårt lands centralbank (www.cbr.ru), i synnerhet, med hjälp av en liknande teknik, ges data om växelkurserna för olika valutor. Från Moscow Exchange-webbplatsen (www.moex.com) kan du ladda ner kurser för aktier, obligationer och mycket annan användbar information på samma sätt.

Sedan version 2013 har Excel två funktioner för att direkt ladda XML-data från Internet till kalkylbladsceller: WEBB-SERVICE (WEBB-SERVICE) и FILTER.XML (FILTERXML). De fungerar i par – först funktionen WEBB-SERVICE exekverar en begäran till den önskade platsen och returnerar dess svar i XML-format och använder sedan funktionen FILTER.XML vi "parar" det här svaret i komponenter och extraherar data vi behöver från det.

Låt oss titta på hur dessa funktioner fungerar med ett klassiskt exempel - import av växelkursen för valfri valuta vi behöver för ett visst datumintervall från webbplatsen för vårt lands centralbank. Vi kommer att använda följande konstruktion som blank:

Här:

• De gula cellerna innehåller start- och slutdatum för perioden som är intressant för oss.
• Den blå har en rullgardinslista med valutor med kommandot Data – Validering – Lista (Data — Validering — Lista).
• I de gröna cellerna kommer vi att använda våra funktioner för att skapa en frågesträng och få serverns svar.
• Tabellen till höger är en referens till valutakoder (vi kommer att behöva den lite senare).
  

Nu går vi!

Steg 1. Skapa en frågesträng

För att få den information som krävs från webbplatsen måste du fråga den korrekt. Vi går till www.cbr.ru och öppnar länken i sidfoten på huvudsidan' Tekniska resurser'- Hämta data med XML (http://cbr.ru/development/SXML/). Vi rullar lite lägre och i det andra exemplet (exempel 2) kommer det att finnas det vi behöver – att få växelkurserna för ett givet datumintervall:

Som du kan se i exemplet måste frågesträngen innehålla startdatum (date_req1) och avslutningar (date_req2) av perioden av intresse för oss och valutakoden (VAL_NM_RQ), den kurs som vi vill få. Du hittar de viktigaste valutakoderna i tabellen nedan:

En komplett guide till valutakoder finns också på centralbankens webbplats – se http://cbr.ru/scripts/XML_val.asp?d=0

Nu kommer vi att bilda en frågesträng i en cell på ett ark med:

• textsammansättningsoperatorn (&) för att sätta ihop det;
  
• Funktioner VPR (VISNING)för att hitta koden för den valuta vi behöver i katalogen;
  
• Funktioner TEXT (TEXT), som konverterar datumet enligt det givna mönstret dag-månad-år genom ett snedstreck.

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

Steg 2. Utför begäran

Nu använder vi funktionen WEBB-SERVICE (WEBB-SERVICE) med den genererade frågesträngen som enda argument. Svaret kommer att vara en lång rad XML-kod (det är bättre att slå på radbrytning och öka cellstorleken om du vill se den i sin helhet):

Steg 3. Analysera svaret

För att göra det lättare att förstå strukturen för svarsdata är det bättre att använda en av online-XML-parsarna (till exempel http://xpather.com/ eller https://jsonformatter.org/xml-parser), som visuellt kan formatera XML-kod, lägga till indrag i den och framhäva syntaxen med färg. Då blir allt mycket tydligare:

Nu kan du tydligt se att kursvärdena ramas in av våra taggar ., och datum är attribut Datum i taggar .

För att extrahera dem, välj en kolumn med tio (eller fler – om det görs med en marginal) tomma celler på arket (eftersom ett 10-dagars datumintervall har ställts in) och skriv in funktionen i formelfältet FILTER.XML (FILTRERAXML):

Här är det första argumentet en länk till en cell med ett serversvar (B8), och det andra är en frågesträng i XPath, ett speciellt språk som kan användas för att komma åt de nödvändiga XML-kodfragmenten och extrahera dem. Du kan till exempel läsa mer om språket XPath här.

Det är viktigt att inte trycka på efter att ha angett formeln ange, och kortkommandot ctrl+shift+ange, dvs ange den som en arrayformel (de lockiga hängslen runt den läggs till automatiskt). Om du har den senaste versionen av Office 365 med stöd för dynamiska arrayer i Excel, då en enkel ange, och du behöver inte markera tomma celler i förväg – funktionen i sig tar så många celler som den behöver.

För att extrahera datum kommer vi att göra detsamma - vi kommer att välja flera tomma celler i den intilliggande kolumnen och använda samma funktion, men med en annan XPath-fråga, för att få alla värden för Date-attributen från Record-taggarna:

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

Nu i framtiden, när du ändrar datumen i de ursprungliga cellerna B2 och B3 eller väljer en annan valuta i rullgardinsmenyn för cell B3, kommer vår fråga att uppdateras automatiskt, med hänvisning till centralbankens server för ny data. För att tvinga fram en uppdatering manuellt kan du dessutom använda kortkommandot ctrl+andra+F9.

• Importera bitcoinkurs till Excel via Power Query
• Importera växelkurser från Internet i äldre versioner av Excel