Oppdatert valutakurs i Excel

Jeg har gjentatte ganger analysert måter å importere data til Excel fra Internett med påfølgende automatisk oppdatering. Spesielt:

• I eldre versjoner av Excel 2007-2013 kan dette gjøres med en direkte nettforespørsel.
• Fra og med 2010 kan dette gjøres veldig praktisk med Power Query-tillegget.
  

Til disse metodene i de nyeste versjonene av Microsoft Excel, kan du nå legge til en annen – importere data fra Internett i XML-format ved hjelp av innebygde funksjoner.

XML (eXtensible Markup Language = Extensible Markup Language) er et universelt språk designet for å beskrive alle typer data. Faktisk er det ren tekst, men med spesielle tagger lagt til for å markere datastrukturen. Mange nettsteder tilbyr gratis strømmer av dataene deres i XML-format som alle kan laste ned. På nettstedet til Central Bank of Our Country (www.cbr.ru), spesielt ved hjelp av en lignende teknologi, er data om valutakursene til forskjellige valutaer gitt. Fra Moscow Exchange-nettstedet (www.moex.com) kan du laste ned kurs for aksjer, obligasjoner og mye annen nyttig informasjon på samme måte.

Siden versjon 2013 har Excel to funksjoner for direkte lasting av XML-data fra Internett til regnearkceller: NETTJENESTE (NETTJENESTE) и FILTER.XML (FILTERXML). De fungerer i par – først funksjonen NETTJENESTE utfører en forespørsel til ønsket sted og returnerer svaret i XML-format, og bruker deretter funksjonen FILTER.XML vi "parser" dette svaret til komponenter og trekker ut dataene vi trenger fra det.

La oss se på driften av disse funksjonene ved å bruke et klassisk eksempel - importere valutakursen til enhver valuta vi trenger for et gitt datointervall fra nettstedet til sentralbanken i vårt land. Vi vil bruke følgende konstruksjon som blank:

Her:

• De gule cellene inneholder start- og sluttdatoene for perioden av interesse for oss.
• Den blå har en rullegardinliste over valutaer som bruker kommandoen Data – Validering – Liste (Data — Validering — Liste).
• I de grønne cellene vil vi bruke funksjonene våre til å lage en spørringsstreng og få serverens svar.
• Tabellen til høyre er en referanse til valutakoder (vi trenger den litt senere).
  

La oss gå!

Trinn 1. Lage en spørringsstreng

For å få den nødvendige informasjonen fra nettstedet, må du spørre den riktig. Vi går til www.cbr.ru og åpner lenken i bunnteksten på hovedsiden' Tekniske ressurser'- Hente data ved hjelp av XML (http://cbr.ru/development/SXML/). Vi ruller litt lavere og i det andre eksemplet (eksempel 2) vil det være det vi trenger – å få valutakursene for et gitt datointervall:

Som du kan se fra eksempelet, må spørringsstrengen inneholde startdatoer (dato_req1) og avslutninger (dato_req2) av perioden av interesse for oss og valutakoden (VAL_NM_RQ), raten vi ønsker å få. Du finner de viktigste valutakodene i tabellen nedenfor:

En komplett veiledning til valutakoder er også tilgjengelig på sentralbankens nettsted – se http://cbr.ru/scripts/XML_val.asp?d=0

Nå skal vi danne en spørringsstreng i en celle på et ark med:

• tekstsammenkoblingsoperatoren (&) for å sette den sammen;
  
• Egenskaper VPR (VISNING)for å finne koden til valutaen vi trenger i katalogen;
  
• Egenskaper TEKST (TEKST), som konverterer datoen i henhold til det gitte mønsteret dag-måned-år gjennom en skråstrek.

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

Trinn 2. Utfør forespørselen

Nå bruker vi funksjonen NETTJENESTE (NETTJENESTE) med den genererte spørringsstrengen som eneste argument. Svaret vil være en lang linje med XML-kode (det er bedre å slå på tekstbryting og øke cellestørrelsen hvis du vil se den i sin helhet):

Trinn 3. Parsing svaret

For å gjøre det lettere å forstå strukturen til responsdataene, er det bedre å bruke en av de online XML-parserne (for eksempel http://xpather.com/ eller https://jsonformatter.org/xml-parser), som visuelt kan formatere XML-kode, legge til innrykk i den og fremheve syntaksen med farge. Da blir alt mye klarere:

Nå kan du tydelig se at kursverdiene er rammet inn av våre tagger ..., og datoer er attributter Dato i tagger .

For å trekke dem ut, velg en kolonne med ti (eller flere – hvis det gjøres med en margin) tomme celler på arket (fordi et 10-dagers datointervall ble satt) og skriv inn funksjonen i formellinjen FILTER.XML (FILTERXML):

Her er det første argumentet en kobling til en celle med et serversvar (B8), og det andre er en spørringsstreng i XPath, et spesialspråk som kan brukes til å få tilgang til de nødvendige XML-kodefragmentene og trekke dem ut. Du kan for eksempel lese mer om XPath-språket her.

Det er viktig at du ikke trykker etter å ha skrevet inn formelen Enter, og hurtigtasten Ctrl+Skift+Enter, dvs. skriv den inn som en matriseformel (krøllete klammeparenteser rundt den legges til automatisk). Hvis du har den nyeste versjonen av Office 365 med støtte for dynamiske arrays i Excel, så en enkel Enter, og du trenger ikke å velge tomme celler på forhånd – selve funksjonen vil ta så mange celler som den trenger.

For å trekke ut datoer, vil vi gjøre det samme - vi vil velge flere tomme celler i den tilstøtende kolonnen og bruke samme funksjon, men med en annen XPath-spørring, for å hente alle verdiene til Date-attributtene fra Record-taggene:

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

Nå i fremtiden, når du endrer datoene i de opprinnelige cellene B2 og B3 eller velger en annen valuta i rullegardinlisten til celle B3, vil spørringen vår automatisk oppdateres, med henvisning til sentralbankens server for nye data. For å tvinge en oppdatering manuelt, kan du i tillegg bruke hurtigtasten Ctrl+andre+F9.

• Importer bitcoin rate til Excel via Power Query
• Importer valutakurser fra Internett i eldre versjoner av Excel