Wechselkurse per Webservice

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

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.

Daten in den Tabellen speichern

Das Speichern der Daten in den Tabellen erledigt die Prozedur WechselkursSpeichern, die für jede Kombination aus Datum, Währung und Wechselkurs einmal von der Prozedur WechselkurseAuslesen aus aufgerufen wird (siehe Listing 4).

Public Sub WechselkursSpeichern(datDatum As Date, strWaehrung As String, curWechselkurs As Currency)
     Dim db As DAO.Database
     Dim lngWaehrungID As Long
     Dim strSQL As String
     Set db = CurrentDb
     On Error Resume Next
     strSQL = "INSERT INTO tblWaehrungen(Waehrung) VALUES('" & strWaehrung & "')"
     db.Execute strSQL, dbFailOnError
     Select Case Err.Number
         Case 3022
             lngWaehrungID = DLookup("WaehrungID", "tblWaehrungen", "Waehrung = '" & strWaehrung & "'")
         Case 0
             lngWaehrungID = db.OpenRecordset("SELECT @@IDENTITY", dbOpenDynaset).Fields(0)
         Case Else
             MsgBox "Fehler " & Err.Number & "'" & Err.Description & "'"
     End Select
     On Error GoTo 0
     On Error Resume Next
     strSQL = "INSERT INTO tblWechselkurse(WaehrungID, Wechselkursdatum, Wechselkurs) VALUES(" & lngWaehrungID & ", " _
         & SQLDatum(datDatum) & ", " & Replace(curWechselkurs, ",", ".") & ")"
     db.Execute strSQL, dbFailOnError
     Select Case Err.Number
         Case 3022
             Debug.Print "Wechselkurs für EUR/" & strWaehrung; " am " & datDatum & " bereits vorhanden."
         Case 0
         Case Else
             MsgBox "Fehler " & Err.Number & "'" & Err.Description & "'"
     End Select
End Sub

Listing 4: Prozedur zum Speichern der Wechselkurse mit Parametern

Diese Prozedur deklariert ein Database-Objekt namens db, mit dessen Execute-Methode gleich die Währung aus strWaehrung in die Tabelle tblWaehrungen eingetragen wird:

db.Execute "INSERT INTO tblWaehrungen(Waehrung)  VALUES('" & strWaehrung & "')", dbFailOnError

Zuvor deaktivieren wir die Fehlerbehandlung, damit im Falle eines Fehlers nicht die eingebaute Fehlermeldung erscheint. Stattdessen werten wir die Fehlernummer aus und prüfen, ob diese den Wert 3022 enthält. Das würde darauf hindeuten, dass bereits ein Datensatz mit der zu speichernden Währungsbezeichnung in der Tabelle tblWaehrung vorhanden ist.

Der Fehler wird ausgelöst, weil wir für das Feld Waehrung einen eindeutigen Index definiert haben.

Egal, ob wir den Datensatz in der Tabelle tblWaehrungen neu anlegen oder nicht, wir benötigen den Wert des Primärschlüsselfeldes der Tabelle tblWaehrungen für den Datensatz mit der übergebenen Währung. Falls die Währung bereits vorhanden war, ermitteln wir den Primärschlüsselwert über eine DLookup-Funktion, der wir den Namen der Währung als Vergleichswert für das Feld Waehrung übergeben und tragen den zurückgelieferten Wert in die Variable lngWaehrungID ein. Falls die Währung noch nicht in der Tabelle tblWaehrungen vorhanden war, können wir diese mit der Abfrage SELECT @@IDENTITY ermitteln, welche den zuletzt hinzugefügten Autowert liefert:

lngWaehrungID = db.OpenRecordset("SELECT @@IDENTITY",  dbOpenDynaset).Fields(0)

Die Tabelle tblWaehrungen sieht danach wie in Bild 5 aus.

Bild 5: Daten in der Tabelle tblWaehrungen

Wenn wir Währung ermittelt haben, können wir direkt den Datensatz für die Kombination aus Währung, Datum und Wechselkurs in die Tabelle tblWechselkurse eintragen:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo