Opdateret valutakurs i Excel

Jeg har gentagne gange analyseret måder at importere data til Excel fra internettet med efterfølgende automatisk opdatering. I særdeleshed:

• I ældre versioner af Excel 2007-2013 kunne dette gøres med en direkte webanmodning.
• Fra 2010 kan dette gøres meget bekvemt med Power Query-tilføjelsen.
  

Til disse metoder i de seneste versioner af Microsoft Excel kan du nu tilføje endnu en - import af data fra internettet i XML-format ved hjælp af indbyggede funktioner.

XML (eXtensible Markup Language = Extensible Markup Language) er et universelt sprog designet til at beskrive enhver form for data. Faktisk er det almindelig tekst, men med specielle tags tilføjet for at markere datastrukturen. Mange websteder tilbyder gratis streams af deres data i XML-format, som alle kan downloade. På webstedet for vores lands centralbank (www.cbr.ru), især ved hjælp af en lignende teknologi, gives data om valutakurserne for forskellige valutaer. Fra Moscow Exchange-webstedet (www.moex.com) kan du downloade kurser for aktier, obligationer og en masse anden nyttig information på samme måde.

Siden version 2013 har Excel to funktioner til direkte indlæsning af XML-data fra internettet i regnearksceller: WEBSERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). De arbejder i par – først funktionen WEBSERVICE udfører en anmodning til det ønskede websted og returnerer dets svar i XML-format, og bruger derefter funktionen FILTER.XML vi "parser" dette svar i komponenter og udtrækker de data, vi har brug for, fra det.

Lad os se på driften af ​​disse funktioner ved hjælp af et klassisk eksempel - import af vekselkursen for enhver valuta, vi har brug for for et givet datointerval, fra webstedet for vores lands centralbank. Vi vil bruge følgende konstruktion som blank:

Her:

• De gule celler indeholder start- og slutdatoerne for den periode, der er af interesse for os.
• Den blå har en rulleliste over valutaer ved hjælp af kommandoen Data – Validering – Liste (Data — Validering — Liste).
• I de grønne celler vil vi bruge vores funktioner til at oprette en forespørgselsstreng og få serverens svar.
• Tabellen til højre er en reference til valutakoder (vi skal bruge den lidt senere).
  

Lad os gå!

Trin 1. Dannelse af en forespørgselsstreng

For at få de nødvendige oplysninger fra webstedet, skal du spørge det korrekt. Vi går til www.cbr.ru og åbner linket i sidefoden på hovedsiden' Tekniske ressourcer'- Hentning af data ved hjælp af XML (http://cbr.ru/development/SXML/). Vi ruller lidt længere ned, og i det andet eksempel (eksempel 2) vil der være det, vi har brug for – at få valutakurserne for et givet datointerval:

Som du kan se fra eksemplet, skal forespørgselsstrengen indeholde startdatoer (dato_req1) og slutninger (dato_req2) af perioden af ​​interesse for os og valutakoden (VAL_NM_RQ), den sats, som vi ønsker at få. Du kan finde de vigtigste valutakoder i tabellen nedenfor:

En komplet vejledning til valutakoder er også tilgængelig på centralbankens websted – se http://cbr.ru/scripts/XML_val.asp?d=0

Nu vil vi danne en forespørgselsstreng i en celle på et ark med:

• tekstsammenkædningsoperatoren (&) for at sætte det sammen;
  
• Funktionalitet VPR (OPLYSNING)for at finde koden for den valuta, vi har brug for, i biblioteket;
  
• Funktionalitet TEKST (TEKST), som konverterer datoen efter det givne mønster dag-måned-år gennem en skråstreg.

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

Trin 2. Udfør anmodningen

Nu bruger vi funktionen WEBSERVICE (WEBSERVICE) med den genererede forespørgselsstreng som eneste argument. Svaret vil være en lang række XML-kode (det er bedre at slå ordombrydning til og øge cellestørrelsen, hvis du vil se den i sin helhed):

Trin 3. Parsing af svaret

For at gøre det nemmere at forstå strukturen af ​​svardataene, er det bedre at bruge en af ​​de online XML-parsere (f.eks. http://xpather.com/ eller https://jsonformatter.org/xml-parser), som visuelt kan formatere XML-kode, tilføje indrykninger til den og fremhæve syntaksen med farve. Så bliver alt meget klarere:

Nu kan du tydeligt se, at kursets værdier er indrammet af vores tags ..., og datoer er attributter Dato i tags .

For at udtrække dem skal du vælge en kolonne med ti (eller flere – hvis det gøres med en margen) tomme celler på arket (fordi der er angivet et 10-dages datointerval) og indtaste funktionen i formellinjen FILTER.XML (FILTERXML):

Her er det første argument et link til en celle med et serversvar (B8), og det andet er en forespørgselsstreng i XPath, et særligt sprog, der kan bruges til at få adgang til de nødvendige XML-kodefragmenter og udtrække dem. Du kan for eksempel læse mere om XPath-sproget her.

Det er vigtigt, at du ikke trykker på efter indtastning af formlen Indtast, og tastaturgenvejen Ctrl+Flytte+Indtast, dvs. indtast det som en matrixformel (de krøllede klammeparenteser omkring det tilføjes automatisk). Hvis du har den nyeste version af Office 365 med understøttelse af dynamiske arrays i Excel, så en simpel Indtast, og du behøver ikke at vælge tomme celler på forhånd – selve funktionen tager lige så mange celler, som den har brug for.

For at udtrække datoer, vil vi gøre det samme - vi vil vælge flere tomme celler i den tilstødende kolonne og bruge den samme funktion, men med en anden XPath-forespørgsel, for at få alle værdierne af Dato-attributterne fra Record-tags:

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

Nu i fremtiden, når du ændrer datoerne i de originale celler B2 og B3 eller vælger en anden valuta i rullelisten i celle B3, vil vores forespørgsel automatisk blive opdateret, med henvisning til centralbankens server for nye data. For at gennemtvinge en opdatering manuelt kan du desuden bruge tastaturgenvejen Ctrl+andre+F9.

• Importer bitcoin rate til Excel via Power Query
• Importer valutakurser fra internettet i ældre versioner af Excel