Wechselkurse per Webservice

Für viele finanzwirtschaftliche Anwendungen spielt die Umwandlung von Beträgen in verschiedene Währungen eine Rolle. Da wäre es doch praktisch, wenn man immer die aktuellen Umrechnungskurse parat hätte. Am besten jedoch nicht so, dass man diese immer aus dem Internet oder der Tageszeitung entnimmt und in die Anwendung eintippt – sondern eher als Information, die eine Anwendung automatisch aus dem Internet bezieht. Wir schauen uns an, wie wir den Webservice der europäischen Zentralbank für unsere Zwecke nutzen können.

Fertige XML-Dateien der europäischen Zentralbank

Für den schnellen Gebrauch bietet die europäische Zentralbank XML-Dokumente an. Die täglichen Wechselkurse von EUR in viele andere Währung bietet beispielsweise diese Webseite an:

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

Das Ergebnis sieht im Browser wie in Bild 1 aus. Nun müssen wir es nur noch schaffen, den Inhalt dieser Datei per VBA abzurufen und auf die Daten im XML-Format zuzugreifen.

Bild 1: XML-Dokument mit Wechselkursen

XML-Verweis

Dazu fügen wir dem VBA-Projekt unserer Beispieldatenbank erst einmal einen Verweis auf die Bibliothek Microsoft XML 6.0 Object Library hinzu (siehe Bild 2).

Bild 2: Verweis auf die XML-Bibliothek

Außerdem benötigen wir noch einen Verweis auf die Bibliothek Microsoft WinHTTP Services, version 5.1.

Damit können wir nun eine erste kleine Testprozedur bauen, die uns den Inhalt der obigen URL herunterlädt und diesen in ein XML-Dokument überträgt, bevor wir dessen Inhalt im Direktfenster ausgeben lassen.

Diese Prozedur finden Sie in Listing 1. Sie deklariert zwei Objekte – eines mit dem Typ WinHttp.WinHttpRequest und eines des Typs MSXML2.DOMDocument60. Das erste dient dazu, die Anfrage an die URL zu schicken und die zweite zum Verarbeiten des zurückgelieferten XML-Dokuments.

Public Sub WechselkurseInsDirektfenster()
     Dim objWinHTTP As WinHttp.WinHttpRequest
     Dim objXML As MSXML2.DOMDocument60
     Dim strURL As String
     strURL = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
     Set objWinHTTP = New WinHttp.WinHttpRequest
     objWinHTTP.Open "GET", strURL, False
     objWinHTTP.send
     If objWinHTTP.status = 200 Then
         Set objXML = New MSXML2.DOMDocument60
         If objXML.loadXML(objWinHTTP.responseText) Then
             Debug.Print objXML.XML
         End If
     End If
End Sub

Listing 1: Prozedur zum Herunterladen eines XML-Dokuments

Die Prozedur speichert die URL in der Variablen strURL. Dann erstellt sie ein neues Objekt des Typs WinHttp.WinHttpRequest und referenziert dieses mit der Variablen objWinHTTP. Die Open-Anweisung erwartet die Art des Zugriffs, die zu öffnende URL und einen Boolean-Wert, der angibt, ob die Anfrage synchron oder asynchron ausgeführt werden soll.

Wir benötigen die synchrone Variante, damit der Code solange angehalten wird, bis der Inhalt abgerufen wurde. Die send-Methode schließlich schickt die Abfrage ab.

Danach prüfen wir in einer If…Then-Bedingung, ob die Eigenschaft Status den Wert 200 enthält, was bedeutet, dass die Abfrage erfolgreich war. In diesem Fall erstellt die Prozedur ein neues Objekt des Typs MSXML2.DOMDocument60.

Mit der Methode LoadXML füllen wir das XML-Dokument mit der durch den HTTP-Request gelieferten Antwort, ermittelt mit der Eigenschaft responseText. Wenn der Aufruf den Wert True zurückgibt, wurde der Text erfolgreich als XML-Dokument eingelesen und wir geben diesen mit der XML-Eigenschaft des MSXML2.DOMDocument-Objekts im Direktbereich des VBA-Editors aus.

Das Ergebnis sieht wie in Bild 3 aus.

Bild 3: Ausgabe des soeben noch im Webbrowser betrachteten XML-Dokuments im Direktbereich des VBA-Editors

Tabellen zum Speichern der Wechselkurse

Nun können wir uns an die Aufgabe begeben, die gewünschten Daten aus dem XML-Dokument zu extrahieren.

Wir wollen das Datum ermitteln und die Wechselkurse von Euro in die verschiedenen anderen Währungen in eine Tabelle eintragen. Die Tabelle sieht in der Entwurfsansicht wie in Bild 4 aus.

Bild 4: Entwurf der Tabelle tblWechselkurse

Neben dem Primärschlüsselfeld WechselkursID verwenden wir ein Fremdschlüsselfeld namens WaehrungID, mit dem wir einen Eintrag einer weiteren Tabelle namens tblWaehrungen auswählen können. Diese Tabelle enthält nur die beiden Felder WaehrungID und Waehrung, wobei wir das letztere mit einem eindeutigen Index ausgestattet haben, damit jede Währung nur einmal in die Tabelle eingetragen werden kann.

Das Feld Wechselkursdatum wollen wir mit dem Datum füllen, für das der Wechselkurs abgefragt wurde. Schließlich fehlt noch das Feld Wechselkurs, das den eigentlichen Wechselkurs aufnehmen soll. Dieses haben wir als Währungsfeld mit dem Format Allgemeine Zahl definiert.

Für die beiden Felder WaehrungID und Wechselkursdatum haben wir außerdem noch einen zusammengesetzten, eindeutigen Index definiert, damit für jede Kombination aus Währung und Datum nur ein Wechselkurs eingetragen werden kann.

XML-Dokument auslesen

Nun wollen wir das ermittelte XML-Dokument auslesen und die ermittelten Informationen in die beiden Tabellen tblWaehrungen und tblWechselkurse speichern. Die erste Prozedur, die wir hierzu einsetzen, heißt WechselkurseSpeichern (siehe Listing 2). Diese ist prinzipiell wie die weiter oben vorgestellte Prozedur WechselkurseInsDirektfenster aufgebaut, ruft aber nach dem Einlesen des XML-Dokuments die Prozedur WechselkurseAuslesen auf und übergibt dieser als Parameter das MSXML2.DOMDocument60-Element.

Public Sub WechselkurseSpeichern()
     Dim objWinHTTP As WinHttp.WinHttpRequest
     Dim objXML As MSXML2.DOMDocument60
     Dim strURL As String
     strURL = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
     Set objWinHTTP = New WinHttp.WinHttpRequest
     objWinHTTP.Open "GET", strURL, False
     objWinHTTP.send
     If objWinHTTP.status = 200 Then
         Set objXML = New MSXML2.DOMDocument60
         If objXML.loadXML(objWinHTTP.responseText) Then
             WechselkurseAuslesen objXML
         End If
     End If
End Sub

Listing 2: Prozedur zum Speichern der Wechselkurse

Prozedur zum Auslesen der Wechselkurse

Diese Prozedur nimmt den Parameter namens objXML mit dem Typ MSXML2.DOMDocument60 entgegen und deklariert zunächst einige Variablen (siehe Listing 3).

Public Sub WaehrungenAuslesen(objXML As MSXML2.DOMDocument60)
     Dim objDatum As MSXML2.IXMLDOMNode
     Dim datDatum As Date
     Dim objWechselkurs As MSXML2.IXMLDOMNode
     Dim strWaehrung As String
     Dim curWechselkurs As Currency
     Dim strXMLNamespaces As String
     strXMLNamespaces = "xmlns:d=""http://www.ecb.int/vocabulary/2002-08-01/eurofxref"""
     objXML.SetProperty "SelectionNamespaces", strXMLNamespaces
     
     Set objDatum = objXML.selectSingleNode("//d:Cube[@time]")
     datDatum = objDatum.Attributes.getNamedItem("time").nodeTypedValue
     For Each objWechselkurs In objXML.selectNodes("//d:Cube[@currency]")
         strWaehrung = objWechselkurs.Attributes.getNamedItem("currency").nodeTypedValue
         curWechselkurs = Replace(objWechselkurs.Attributes.getNamedItem("rate").nodeTypedValue, ".", ",")
         WechselkursSpeichern datDatum, strWaehrung, curWechselkurs
     Next objWechselkurs
End Sub

Listing 3: Prozedur zum Auslesen der Daten aus der XML-Datei

In obj-Datum wollen wir das XML-Element speichern, welches das Datum der Wechselkurse enthält. In objWechselkurs wollen wir die XML-Elemente speichern, das die Währung und den Wechselkurs liefern. datDatum, strWaehrung und curWechselkurs nehmen die dabei ermittelten Informationen auf. strXMLNamespace benötigen wir für eine Spezialaufgabe, die wir gleich beschreiben.

Für das XML-Dokument sind nämlich verschiedene Namespaces festgelegt – dies können wir dem Kopf des Dokuments entnehmen, das wie folgt aussieht:

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">

Wir haben also den Namespace http://www.gesmes.org/xml/2002-08-01, dessen Element mit dem Präfix gesmes markiert werden. Das gilt beispielsweise gleich für das erste Element, das die Namespace-Angaben enthält.

Außerdem haben wir den Namespace http://www.ecb.int/vocabulary/2002-08-01/eurofxref, der ohne Präfix kommt. Dementsprechend bezieht er sich auf alle Elemente, die ohne Präfix angegeben werden. Dabei handelt es sich um die Cube-Elemente – und diese enthalten genau die Informationen, die wir einlesen und verarbeiten wollen:

<Cube>
     <Cube time="2020-04-09">
         <Cube currency="USD" rate="1.0867"/>
         ...
     </Cube>
</Cube>

Damit gleich beim Aufruf der Methoden selectNodes und selectSingleNodes klar ist, in welchem Namespace sich die Elemente beziehen, die wir suchen wollen, legen wir diesen zuvor mit einer Property namens SelectionNamespaces fest:

strXMLNamespaces = "xmlns:d=""http://www.ecb.int/vocabulary/2002-08-01/eurofxref"""
objXML.SetProperty "SelectionNamespaces", strXMLNamespaces

Damit können wir nun das Element einlesen, welches uns das Datum liefert und so aussieht:

<Cube time="2020-04-09">

In der Hierarchie des XML-Dokuments befindet es sich unterhalb eines weiteren Cube-Elements.

Wir könnten mit folgendem Suchausdruck darauf zugreifen:

Set objDatum = objXML.selectSingleNode("//d:Cube/d:Cube")

Dies liefert das erste Cube-Element, das ein übergeordnetes Cube-Element enthält. Wir verwenden aber den folgenden Ausdruck:

Set objDatum = objXML.selectSingleNode("//d:Cube[@time]")

Damit suchen wir etwas gezielter nach dem ersten Cube-Element, welches das time-Attribut enthält. Dieses lesen wir mit der nächsten Anweisung in die Varible datDatum ein:

datDatum = objDatum.Attributes.getNamedItem("time").nodeTypedValue

Anschließend durchlaufen wir in einer For Each-Schleife alle Elemente, die dem Schema //d:Cube[@currency] entsprechen und somit ein Cube-Element enthalten, welches das Attribut currency enthält:

For Each objWechselkurs In  objXML.selectNodes("//d:Cube[@currency]")

Innerhalb dieser Schleife ermitteln wir zunächst den Wert des Attributs currency und schreiben diesen in die Variable strWaehrung:

strWaehrung = objWechselkurs.Attributes. getNamedItem("currency").nodeTypedValue

Danach lesen wir den Wechselkurs aus dem Attribut rate ein und ersetzen hier direkt den Punkt durch das Komma als Dezimaltrennzeichen:

curWechselkurs = Replace(objWechselkurs.Attributes. getNamedItem("rate").nodeTypedValue, ".", ",")

Schließlich rufen wir die nächste Prozedur namens WechselkursSpeichern auf, der wir das Datum, die Währung und den Wechselkurs mit den Variablen datDatum, strWaehrung und curWechselkurs übergeben:

WechselkursSpeichern datDatum, strWaehrung, curWechselkurs

Diese Anweisungen erledigen wir für alle Cube-Elemente mit dem Attribut rate, welche die SelectNodes-Methode liefert.

Username or Email

Passwort

Angemeldet bleiben