Aktualisierter Wechselkurs in Excel

Ich habe immer wieder Möglichkeiten analysiert, Daten aus dem Internet in Excel mit anschließender automatischer Aktualisierung zu importieren. Im Speziellen:

• In älteren Versionen von Excel 2007-2013 konnte dies mit einer direkten Webanfrage erfolgen.
• Ab 2010 geht das ganz bequem mit dem Power Query Add-In.
  

Zu diesen Methoden in den neuesten Versionen von Microsoft Excel können Sie jetzt eine weitere hinzufügen – den Import von Daten aus dem Internet im XML-Format mithilfe integrierter Funktionen.

XML (eXtensible Markup Language = Extensible Markup Language) ist eine universelle Sprache zur Beschreibung beliebiger Daten. Tatsächlich handelt es sich um einfachen Text, dem jedoch spezielle Tags hinzugefügt wurden, um die Datenstruktur zu kennzeichnen. Viele Websites bieten kostenlose Streams ihrer Daten im XML-Format zum Herunterladen an. Auf der Website der Zentralbank unseres Landes (www.cbr.ru) werden insbesondere mit Hilfe einer ähnlichen Technologie Daten zu den Wechselkursen verschiedener Währungen angegeben. Von der Website der Moskauer Börse (www.moex.com) können Sie Kurse für Aktien, Anleihen und viele andere nützliche Informationen auf die gleiche Weise herunterladen.

Seit Version 2013 verfügt Excel über zwei Funktionen, um XML-Daten direkt aus dem Internet in Arbeitsblattzellen zu laden: INTERNETSERVICE (INTERNETSERVICE) и FILTER.XML (XML-FILTER). Sie arbeiten paarweise – zuerst die Funktion INTERNETSERVICE führt eine Anfrage an die gewünschte Site aus und gibt ihre Antwort im XML-Format zurück und verwendet dann die Funktion FILTER.XML Wir „parsen“ diese Antwort in Komponenten und extrahieren daraus die Daten, die wir benötigen.

Schauen wir uns die Funktionsweise dieser Funktionen anhand eines klassischen Beispiels an – den Import des Wechselkurses einer beliebigen Währung, die wir für ein bestimmtes Datumsintervall benötigen, von der Website der Zentralbank unseres Landes. Als Leerzeichen verwenden wir folgende Konstruktion:

Hier:

• Die gelben Zellen enthalten die Start- und Enddaten des für uns interessanten Zeitraums.
• Der blaue hat eine Dropdown-Liste mit Währungen, die den Befehl verwenden Daten – Validierung – Liste (Daten – Validierung – Liste).
• In den grünen Zellen verwenden wir unsere Funktionen, um eine Abfragezeichenfolge zu erstellen und die Antwort des Servers zu erhalten.
• Die Tabelle auf der rechten Seite ist ein Verweis auf Währungscodes (wir werden sie etwas später benötigen).
  

Los geht's!

Schritt 1. Erstellen einer Abfragezeichenfolge

Um die erforderlichen Informationen von der Website zu erhalten, müssen Sie sie richtig fragen. Wir gehen zu www.cbr.ru und öffnen den Link in der Fußzeile der Hauptseite. Technische Ressourcen'- Abrufen von Daten mit XML (http://cbr.ru/development/SXML/). Wir scrollen etwas nach unten und im zweiten Beispiel (Beispiel 2) finden Sie das, was wir brauchen – die Wechselkurse für ein bestimmtes Datumsintervall abrufen:

Wie Sie dem Beispiel entnehmen können, muss die Abfragezeichenfolge Startdaten enthalten (date_req1) und Endungen (date_req2) des für uns interessanten Zeitraums und des Währungscodes (VAL_NM_RQ), deren Rate wir erhalten möchten. Die wichtigsten Währungscodes finden Sie in der folgenden Tabelle:

Eine vollständige Anleitung zu den Währungscodes ist auch auf der Website der Zentralbank verfügbar – siehe http://cbr.ru/scripts/XML_val.asp?d=0

Jetzt bilden wir eine Abfragezeichenfolge in einer Zelle auf einem Blatt mit:

• der Textverkettungsoperator (&), um ihn zusammenzusetzen;
  
• Eigenschaften VPR (SVERWEIS)um den Code der Währung zu finden, die wir im Verzeichnis benötigen;
  
• Eigenschaften TEXT (TEXT), der das Datum nach dem vorgegebenen Muster Tag-Monat-Jahr durch einen Schrägstrich umwandelt.

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

Schritt 2. Führen Sie die Anfrage aus

Jetzt verwenden wir die Funktion INTERNETSERVICE (INTERNETSERVICE) mit der generierten Abfragezeichenfolge als einzigem Argument. Die Antwort wird eine lange Zeile XML-Code sein (es ist besser, den Zeilenumbruch zu aktivieren und die Zellengröße zu erhöhen, wenn Sie ihn vollständig sehen möchten):

Schritt 3. Analysieren der Antwort

Um die Struktur der Antwortdaten besser nachvollziehen zu können, verwenden Sie besser einen der Online-XML-Parser (z. B. http://xpather.com/ oder https://jsonformatter.org/xml-parser). die XML-Code visuell formatieren, Einzüge hinzufügen und die Syntax farblich hervorheben kann. Dann wird alles viel klarer:

Nun sieht man deutlich, dass die Kurswerte von unseren Tags umrahmt werden ..., und Daten sind Attribute Datum bei Tags .

Um sie zu extrahieren, wählen Sie eine Spalte mit zehn (oder mehr – wenn Sie mit einem Rand fertig sind) leeren Zellen auf dem Blatt aus (da ein Datumsintervall von 10 Tagen festgelegt wurde) und geben Sie die Funktion in die Bearbeitungsleiste ein FILTER.XML (FILTERXML):

Hier ist das erste Argument ein Link zu einer Zelle mit einer Serverantwort (B8) und das zweite ein Abfragestring in XPath, einer speziellen Sprache, mit der auf die erforderlichen XML-Codefragmente zugegriffen und diese extrahiert werden können. Hier können Sie beispielsweise mehr über die Sprache XPath lesen.

Es ist wichtig, dass Sie nach Eingabe der Formel nicht drücken Enter, und die Tastenkombination Ctrl+Shift+Enter, dh als Matrixformel eingeben (die geschweiften Klammern darum werden automatisch hinzugefügt). Wenn Sie die neueste Version von Office 365 mit Unterstützung für dynamische Arrays in Excel haben, dann ist eine einfache Enter, und Sie müssen keine leeren Zellen im Voraus auswählen – die Funktion selbst nimmt so viele Zellen, wie sie benötigt.

Um Daten zu extrahieren, gehen wir genauso vor – wir wählen mehrere leere Zellen in der angrenzenden Spalte aus und verwenden dieselbe Funktion, aber mit einer anderen XPath-Abfrage, um alle Werte der Datumsattribute aus den Datensatz-Tags zu erhalten:

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

Wenn Sie in Zukunft die Daten in den ursprünglichen Zellen B2 und B3 ändern oder eine andere Währung in der Dropdown-Liste der Zelle B3 auswählen, wird unsere Abfrage automatisch aktualisiert und auf den Server der Zentralbank für neue Daten verwiesen. Um ein Update manuell zu erzwingen, können Sie zusätzlich die Tastenkombination verwenden Ctrl+Andere+F9.

• Importieren Sie den Bitcoin-Kurs über Power Query in Excel
• Importieren Sie Wechselkurse aus dem Internet in ältere Versionen von Excel