Bijgewerkte wisselkoers in Excel

Ik heb herhaaldelijk manieren geanalyseerd om gegevens van internet in Excel te importeren met daaropvolgende automatische updates. Vooral:

• In oudere versies van Excel 2007-2013 kon dit worden gedaan met een direct webverzoek.
• Vanaf 2010 kan dit heel gemakkelijk met de Power Query add-in.
  

Aan deze methoden in de nieuwste versies van Microsoft Excel kunt u er nu nog een toevoegen: gegevens importeren van internet in XML-indeling met behulp van ingebouwde functies.

XML (eXtensible Markup Language = Extensible Markup Language) is een universele taal die is ontworpen om alle soorten gegevens te beschrijven. In feite is het platte tekst, maar met speciale tags die eraan zijn toegevoegd om de gegevensstructuur te markeren. Veel sites bieden gratis streams van hun gegevens in XML-indeling die iedereen kan downloaden. Met name op de website van de Centrale Bank van Ons Land (www.cbr.ru) worden met behulp van een vergelijkbare technologie gegevens over de wisselkoersen van verschillende valuta's gegeven. Van de Moscow Exchange website (www.moex.com) kunt u op dezelfde manier koersen voor aandelen, obligaties en een heleboel andere nuttige informatie downloaden.

Sinds versie 2013 heeft Excel twee functies om XML-gegevens rechtstreeks van internet in werkbladcellen te laden: WEBSERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). Ze werken in paren – eerst de functie WEBSERVICE voert een verzoek uit naar de gewenste site en retourneert het antwoord in XML-indeling, en gebruikt vervolgens de functie FILTER.XML we "parsen" dit antwoord in componenten en halen er de gegevens uit die we nodig hebben.

Laten we eens kijken naar de werking van deze functies aan de hand van een klassiek voorbeeld – het importeren van de wisselkoers van elke valuta die we nodig hebben voor een bepaald datuminterval van de website van de Centrale Bank van Ons Land. We gebruiken de volgende constructie als blanco:

Hier:

• De gele cellen bevatten de begin- en einddatum van de voor ons interessante periode.
• De blauwe heeft een vervolgkeuzelijst met valuta's met het commando Gegevens – Validatie – Lijst (Gegevens — Validatie — Lijst).
• In de groene cellen zullen we onze functies gebruiken om een ​​queryreeks te maken en het antwoord van de server te krijgen.
• De tabel aan de rechterkant is een verwijzing naar valutacodes (we hebben deze later nodig).
  

Laten we gaan!

Stap 1. Een queryreeks vormen

Om de vereiste informatie van de site te krijgen, moet u deze correct vragen. We gaan naar www.cbr.ru en openen de link in de voettekst van de hoofdpagina' Technische bronnen'- Gegevens ophalen met XML (http://cbr.ru/development/SXML/). We scrollen iets lager en in het tweede voorbeeld (voorbeeld 2) is er wat we nodig hebben - de wisselkoersen voor een bepaald datuminterval ophalen:

Zoals u in het voorbeeld kunt zien, moet de queryreeks startdatums bevatten (datum_req1) en eindes (datum_req2) van de voor ons interessante periode en de valutacode (VAL_NM_RQ), waarvan we de koers willen halen. U vindt de belangrijkste valutacodes in de onderstaande tabel:

Een complete gids voor valutacodes is ook beschikbaar op de website van de Centrale Bank – zie http://cbr.ru/scripts/XML_val.asp?d=0

Nu zullen we een queryreeks vormen in een cel op een blad met:

• de tekstaaneenschakelingsoperator (&) om het samen te stellen;
  
• Voordelen VPR (VERT.ZOEKEN)om de code van de valuta te vinden die we nodig hebben in de directory;
  
• Voordelen TEKST (TEKST), die de datum omzet volgens het gegeven patroon dag-maand-jaar door middel van een schuine streep.

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

Stap 2. Voer het verzoek uit

Nu gebruiken we de functie WEBSERVICE (WEBSERVICE) met de gegenereerde queryreeks als het enige argument. Het antwoord is een lange regel XML-code (het is beter om woordterugloop in te schakelen en de celgrootte te vergroten als je het in zijn geheel wilt zien):

Stap 3. Het antwoord ontleden

Om de structuur van de responsgegevens beter te begrijpen, is het beter om een ​​van de online XML-parsers te gebruiken (bijvoorbeeld http://xpather.com/ of https://jsonformatter.org/xml-parser), die XML-code visueel kan opmaken, er inspringingen aan kan toevoegen en de syntaxis met kleur kan markeren. Dan wordt alles veel duidelijker:

Nu kun je duidelijk zien dat de cursuswaarden worden omlijst door onze tags ..., en datums zijn attributen Datum in labels .

Om ze te extraheren, selecteert u een kolom van tien (of meer - indien gedaan met een marge) lege cellen op het blad (omdat er een datuminterval van 10 dagen is ingesteld) en voert u de functie in de formulebalk in FILTER.XML (FILTERxml):

Hier is het eerste argument een link naar een cel met een serverrespons (B8), en het tweede is een querystring in XPath, een speciale taal die kan worden gebruikt om toegang te krijgen tot de benodigde XML-codefragmenten en deze te extraheren. U kunt hier bijvoorbeeld meer lezen over de XPath-taal.

Het is belangrijk dat u na het invoeren van de formule niet op . drukt Enteren de sneltoets Ctrl+Shift+Enter, dwz voer het in als een matrixformule (de accolades eromheen worden automatisch toegevoegd). Als je de nieuwste versie van Office 365 hebt met ondersteuning voor dynamische arrays in Excel, dan is een eenvoudige Enter, en u hoeft geen lege cellen van tevoren te selecteren - de functie zelf neemt zoveel cellen in beslag als nodig is.

Om datums te extraheren, zullen we hetzelfde doen - we zullen verschillende lege cellen in de aangrenzende kolom selecteren en dezelfde functie gebruiken, maar met een andere XPath-query, om alle waarden van de Date-attributen uit de Record-tags te halen:

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

In de toekomst, bij het wijzigen van de datums in de oorspronkelijke cellen B2 en B3 of het kiezen van een andere valuta in de vervolgkeuzelijst van cel B3, zal onze zoekopdracht automatisch worden bijgewerkt, waarbij wordt verwezen naar de server van de Centrale Bank voor nieuwe gegevens. Om handmatig een update te forceren, kunt u bovendien de sneltoets gebruiken Ctrl+anders+F9.

• Importeer bitcoin-snelheid naar Excel via Power Query
• Wisselkoersen importeren van internet in oudere versies van Excel