{"id":55001240,"date":"2020-06-01T00:00:00","date_gmt":"2020-07-10T09:41:33","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=1240"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"Wechselkurse_per_Webservice","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/Wechselkurse_per_Webservice\/","title":{"rendered":"Wechselkurse per Webservice"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/b><\/p>\n

Fertige XML-Dateien der europäischen Zentralbank<\/b><\/p>\n

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:<\/p>\n

http:\/\/www.ecb.europa.eu\/stats\/eurofxref\/eurofxref-daily.xml<\/pre>\n
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.<\/p>\n
\"XML-Dokument<\/p>\n
Bild 1: XML-Dokument mit Wechselkursen<\/span><\/b><\/p>\n
XML-Verweis<\/b><\/p>\n
Dazu fügen wir dem VBA-Projekt unserer Beispieldatenbank erst einmal einen Verweis auf die Bibliothek Microsoft XML 6.0 Object Library <\/b>hinzu (siehe Bild 2).<\/p>\n
\"Verweis<\/p>\n
Bild 2: Verweis auf die XML-Bibliothek<\/span><\/b><\/p>\n
Außerdem benötigen wir noch einen Verweis auf die Bibliothek Microsoft WinHTTP Services, version 5.1<\/b>.<\/p>\n
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.<\/p>\n
Diese Prozedur finden Sie in Listing 1. Sie deklariert zwei Objekte – eines mit dem Typ WinHttp.WinHttpRequest <\/b>und eines des Typs MSXML2.DOMDocument60<\/b>. Das erste dient dazu, die Anfrage an die URL zu schicken und die zweite zum Verarbeiten des zurückgelieferten XML-Dokuments.<\/p>\n
Public Sub <\/span>WechselkurseInsDirektfenster()\r\n     Dim <\/span>objWinHTTP As <\/span>WinHttp.WinHttpRequest\r\n     Dim <\/span>objXML As <\/span>MSXML2.DOMDocument60\r\n     Dim <\/span>strURL As String<\/span>\r\n     strURL = \"http:\/\/www.ecb.europa.eu\/stats\/eurofxref\/eurofxref-daily.xml\"\r\n     Set<\/span> objWinHTTP = New<\/span> WinHttp.WinHttpRequest\r\n     objWinHTTP.Open \"GET\", strURL, False<\/span>\r\n     objWinHTTP.send\r\n     If <\/span>objWinHTTP.status = 200 Then<\/span>\r\n         Set<\/span> objXML = New<\/span> MSXML2.DOMDocument60\r\n         If <\/span>objXML.loadXML(objWinHTTP.responseText) Then<\/span>\r\n             Debug.Print<\/span> objXML.XML\r\n         End If<\/span>\r\n     End If<\/span>\r\nEnd Sub<\/span><\/pre>\n
Listing 1: Prozedur zum Herunterladen eines XML-Dokuments<\/span><\/b><\/p>\n
Die Prozedur speichert die URL in der Variablen strURL<\/b>. Dann erstellt sie ein neues Objekt des Typs WinHttp.WinHttpRequest <\/b>und referenziert dieses mit der Variablen objWinHTTP<\/b>. Die Open<\/b>-Anweisung erwartet die Art des Zugriffs, die zu öffnende URL und einen Boolean<\/b>-Wert, der angibt, ob die Anfrage synchron oder asynchron ausgeführt werden soll.<\/p>\n
Wir benötigen die synchrone Variante, damit der Code solange angehalten wird, bis der Inhalt abgerufen wurde. Die send<\/b>-Methode schließlich schickt die Abfrage ab.<\/p>\n
Danach prüfen wir in einer If…Then<\/b>-Bedingung, ob die Eigenschaft Status <\/b>den Wert 200 <\/b>enthält, was bedeutet, dass die Abfrage erfolgreich war. In diesem Fall erstellt die Prozedur ein neues Objekt des Typs MSXML2.DOMDocument60<\/b>.<\/p>\n
Mit der Methode LoadXML <\/b>füllen wir das XML-Dokument mit der durch den HTTP-Request gelieferten Antwort, ermittelt mit der Eigenschaft responseText<\/b>. Wenn der Aufruf den Wert True <\/b>zurückgibt, wurde der Text erfolgreich als XML-Dokument eingelesen und wir geben diesen mit der XML-Eigenschaft des MSXML2.DOMDocument<\/b>-Objekts im Direktbereich des VBA-Editors aus.<\/p>\n
Das Ergebnis sieht wie in Bild 3 aus.<\/p>\n
\"Ausgabe<\/p>\n
Bild 3: Ausgabe des soeben noch im Webbrowser betrachteten XML-Dokuments im Direktbereich des VBA-Editors<\/span><\/b><\/p>\n
Tabellen zum Speichern der Wechselkurse<\/b><\/p>\n
Nun können wir uns an die Aufgabe begeben, die gewünschten Daten aus dem XML-Dokument zu extrahieren.<\/p>\n
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.<\/p>\n
\"Entwurf<\/p>\n
Bild 4: Entwurf der Tabelle tblWechselkurse<\/span><\/b><\/p>\n
Neben dem Primärschlüsselfeld WechselkursID <\/b>verwenden wir ein Fremdschlüsselfeld namens WaehrungID<\/b>, mit dem wir einen Eintrag einer weiteren Tabelle namens tblWaehrungen <\/b>auswählen können. Diese Tabelle enthält nur die beiden Felder WaehrungID <\/b>und Waehrung<\/b>, wobei wir das letztere mit einem eindeutigen Index ausgestattet haben, damit jede Währung nur einmal in die Tabelle eingetragen werden kann.<\/p>\n
Das Feld Wechselkursdatum <\/b>wollen wir mit dem Datum füllen, für das der Wechselkurs abgefragt wurde. Schließlich fehlt noch das Feld Wechselkurs<\/b>, das den eigentlichen Wechselkurs aufnehmen soll. Dieses haben wir als Währungsfeld mit dem Format Allgemeine Zahl <\/b>definiert.<\/p>\n
Für die beiden Felder WaehrungID <\/b>und Wechselkursdatum <\/b>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.<\/p>\n
XML-Dokument auslesen<\/b><\/p>\n
Nun wollen wir das ermittelte XML-Dokument auslesen und die ermittelten Informationen in die beiden Tabellen tblWaehrungen <\/b>und tblWechselkurse <\/b>speichern. Die erste Prozedur, die wir hierzu einsetzen, heißt WechselkurseSpeichern<\/b> (siehe Listing 2). Diese ist prinzipiell wie die weiter oben vorgestellte Prozedur WechselkurseInsDirektfenster <\/b>aufgebaut, ruft aber nach dem Einlesen des XML-Dokuments die Prozedur WechselkurseAuslesen<\/b> auf und übergibt dieser als Parameter das MSXML2.DOMDocument60<\/b>-Element.<\/p>\n
Public Sub <\/span>WechselkurseSpeichern()\r\n     Dim <\/span>objWinHTTP As <\/span>WinHttp.WinHttpRequest\r\n     Dim <\/span>objXML As <\/span>MSXML2.DOMDocument60\r\n     Dim <\/span>strURL As String<\/span>\r\n     strURL = \"http:\/\/www.ecb.europa.eu\/stats\/eurofxref\/eurofxref-daily.xml\"\r\n     Set<\/span> objWinHTTP = New<\/span> WinHttp.WinHttpRequest\r\n     objWinHTTP.Open \"GET\", strURL, False<\/span>\r\n     objWinHTTP.send\r\n     If <\/span>objWinHTTP.status = 200 Then<\/span>\r\n         Set<\/span> objXML = New<\/span> MSXML2.DOMDocument60\r\n         If <\/span>objXML.loadXML(objWinHTTP.responseText) Then<\/span>\r\n             WechselkurseAuslesen objXML\r\n         End If<\/span>\r\n     End If<\/span>\r\nEnd Sub<\/span><\/pre>\n
Listing 2: Prozedur zum Speichern der Wechselkurse<\/span><\/b><\/p>\n
Prozedur zum Auslesen der Wechselkurse<\/b><\/p>\n
Diese Prozedur nimmt den Parameter namens objXML <\/b>mit dem Typ MSXML2.DOMDocument60<\/b> entgegen und deklariert zunächst einige Variablen (siehe Listing 3).<\/p>\n
Public Sub <\/span>WaehrungenAuslesen(objXML As <\/span>MSXML2.DOMDocument60)\r\n     Dim <\/span>objDatum As <\/span>MSXML2.IXMLDOMNode\r\n     Dim <\/span>datDatum As Date<\/span>\r\n     Dim <\/span>objWechselkurs As <\/span>MSXML2.IXMLDOMNode\r\n     Dim <\/span>strWaehrung As String<\/span>\r\n     Dim <\/span>curWechselkurs As Currency<\/span>\r\n     Dim <\/span>strXMLNamespaces As String<\/span>\r\n     strXMLNamespaces = \"xmlns:d=\"\"http:\/\/www.ecb.int\/vocabulary\/2002-08-01\/eurofxref\"\"\"\r\n     objXML.SetProperty \"SelectionNamespaces\", strXMLNamespaces\r\n     \r\n     Set<\/span> objDatum = objXML.selectSingleNode(\"\/\/d:Cube[@time]\")\r\n     datDatum = objDatum.Attributes.getNamedItem(\"time\").nodeTypedValue\r\n     For Each objWechselkurs In objXML.selectNodes(\"\/\/d:Cube[@currency]\")\r\n         strWaehrung = objWechselkurs.Attributes.getNamedItem(\"currency\").nodeTypedValue\r\n         curWechselkurs = Replace<\/span>(objWechselkurs.Attributes.getNamedItem(\"rate\").nodeTypedValue, \".\", \",\")\r\n         WechselkursSpeichern datDatum, strWaehrung, curWechselkurs\r\n     Next<\/span> objWechselkurs\r\nEnd Sub<\/span><\/pre>\n
Listing 3: Prozedur zum Auslesen der Daten aus der XML-Datei<\/span><\/b><\/p>\n
In obj-Datum <\/b>wollen wir das XML-Element speichern, welches das Datum der Wechselkurse enthält. In objWechselkurs <\/b>wollen wir die XML-Elemente speichern, das die Währung und den Wechselkurs liefern. datDatum<\/b>, strWaehrung <\/b>und curWechselkurs <\/b>nehmen die dabei ermittelten Informationen auf. strXMLNamespace <\/b>benötigen wir für eine Spezialaufgabe, die wir gleich beschreiben.<\/p>\n
Für das XML-Dokument sind nämlich verschiedene Namespaces festgelegt – dies können wir dem Kopf des Dokuments entnehmen, das wie folgt aussieht:<\/p>\n
<gesmes:Envelope xmlns:gesmes=\"http:\/\/www.gesmes.org\/xml\/2002-08-01\" xmlns=\"http:\/\/www.ecb.int\/vocabulary\/2002-08-01\/eurofxref\"><\/pre>\n
Wir haben also den Namespace http:\/\/www.gesmes.org\/xml\/2002-08-01<\/b>, dessen Element mit dem Präfix gesmes <\/b>markiert werden. Das gilt beispielsweise gleich für das erste Element, das die Namespace<\/b>-Angaben enthält.<\/p>\n
Außerdem haben wir den Namespace http:\/\/www.ecb.int\/vocabulary\/2002-08-01\/eurofxref<\/b>, 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<\/b>-Elemente – und diese enthalten genau die Informationen, die wir einlesen und verarbeiten wollen:<\/p>\n
<Cube>\r\n     <Cube time=\"2020-04-09\">\r\n         <Cube currency=\"USD\" rate=\"1.0867\"\/>\r\n         ...\r\n     <\/Cube>\r\n<\/Cube><\/pre>\n
Damit gleich beim Aufruf der Methoden selectNodes <\/b>und selectSingleNodes <\/b>klar ist, in welchem Namespace sich die Elemente beziehen, die wir suchen wollen, legen wir diesen zuvor mit einer Property namens SelectionNamespaces <\/b>fest:<\/p>\n
strXMLNamespaces = \"xmlns:d=\"\"http:\/\/www.ecb.int\/vocabulary\/2002-08-01\/eurofxref\"\"\"\r\nobjXML.SetProperty \"SelectionNamespaces\", strXMLNamespaces<\/pre>\n
Damit können wir nun das Element einlesen, welches uns das Datum liefert und so aussieht:<\/p>\n
<Cube time=\"2020-04-09\"><\/pre>\n
In der Hierarchie des XML-Dokuments befindet es sich unterhalb eines weiteren Cube<\/b>-Elements.<\/p>\n
Wir könnten mit folgendem Suchausdruck darauf zugreifen:<\/p>\n
Set<\/span> objDatum = objXML.selectSingleNode(\"\/\/d:Cube\/d:Cube\")<\/pre>\n
Dies liefert das erste Cube<\/b>-Element, das ein übergeordnetes Cube<\/b>-Element enthält. Wir verwenden aber den folgenden Ausdruck:<\/p>\n
Set<\/span> objDatum = objXML.selectSingleNode(\"\/\/d:Cube[@time]\")<\/pre>\n
Damit suchen wir etwas gezielter nach dem ersten Cube-Element, welches das time<\/b>-Attribut enthält. Dieses lesen wir mit der nächsten Anweisung in die Varible datDatum <\/b>ein:<\/p>\n
datDatum = objDatum.Attributes.getNamedItem(\"time\").nodeTypedValue<\/pre>\n
Anschließend durchlaufen wir in einer For Each<\/b>-Schleife alle Elemente, die dem Schema \/\/d:Cube[@currency] <\/b>entsprechen und somit ein Cube<\/b>-Element enthalten, welches das Attribut currency <\/b>enthält:<\/p>\n
For Each objWechselkurs In  objXML.selectNodes(\"\/\/d:Cube[@currency]\")<\/pre>\n
Innerhalb dieser Schleife ermitteln wir zunächst den Wert des Attributs currency <\/b>und schreiben diesen in die Variable strWaehrung<\/b>:<\/p>\n
strWaehrung = objWechselkurs.Attributes. getNamedItem(\"currency\").nodeTypedValue<\/pre>\n
Danach lesen wir den Wechselkurs aus dem Attribut rate <\/b>ein und ersetzen hier direkt den Punkt durch das Komma als Dezimaltrennzeichen:<\/p>\n
curWechselkurs = Replace<\/span>(objWechselkurs.Attributes. getNamedItem(\"rate\").nodeTypedValue, \".\", \",\")<\/pre>\n
Schließlich rufen wir die nächste Prozedur namens WechselkursSpeichern <\/b>auf, der wir das Datum, die Währung und den Wechselkurs mit den Variablen datDatum<\/b>, strWaehrung <\/b>und curWechselkurs <\/b>übergeben:<\/p>\n
WechselkursSpeichern datDatum, strWaehrung, curWechselkurs<\/pre>\n
Diese Anweisungen erledigen wir für alle Cube<\/b>-Elemente mit dem Attribut rate<\/b>, welche die SelectNodes<\/b>-Methode liefert.<\/p>\n
<\/p>\n
Daten in den Tabellen speichern<\/b><\/p>\n
Das Speichern der Daten in den Tabellen erledigt die Prozedur WechselkursSpeichern<\/b>, die für jede Kombination aus Datum, Währung und Wechselkurs einmal von der Prozedur WechselkurseAuslesen <\/b>aus aufgerufen wird (siehe Listing 4).<\/p>\n
Public Sub <\/span>WechselkursSpeichern(datDatum As Date<\/span>, strWaehrung As String<\/span>, curWechselkurs As Currency<\/span>)\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>lngWaehrungID As Long<\/span>\r\n     Dim <\/span>strSQL As String<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     On Error Resume Next<\/span>\r\n     strSQL = \"INSERT INTO tblWaehrungen(Waehrung) VALUES('\" & strWaehrung & \"')\"\r\n     db.Execute strSQL, dbFailOnError\r\n     Select Case Err.Number\r\n         Case <\/span>3022\r\n             lngWaehrungID = DLookup(\"WaehrungID\", \"tblWaehrungen\", \"Waehrung = '\" & strWaehrung & \"'\")\r\n         Case <\/span>0\r\n             lngWaehrungID = db.OpenRecordset(\"SELECT @@IDENTITY\", dbOpenDynaset).Fields(0)\r\n         Case Else<\/span>\r\n             MsgBox<\/span> \"Fehler \" & Err.Number & \"'\" & Err.Description & \"'\"\r\n     End Select<\/span>\r\n     On Error GoTo<\/span> 0\r\n     On Error Resume Next<\/span>\r\n     strSQL = \"INSERT INTO tblWechselkurse(WaehrungID, Wechselkursdatum, Wechselkurs) VALUES(\" & lngWaehrungID & \", \" _\r\n         & SQLDatum(datDatum) & \", \" & Replace<\/span>(curWechselkurs, \",\", \".\") & \")\"\r\n     db.Execute strSQL, dbFailOnError\r\n     Select Case Err.Number\r\n         Case <\/span>3022\r\n             Debug.Print<\/span> \"Wechselkurs für EUR\/\" & strWaehrung; \" am \" & datDatum & \" bereits vorhanden.\"\r\n         Case <\/span>0\r\n         Case Else<\/span>\r\n             MsgBox<\/span> \"Fehler \" & Err.Number & \"'\" & Err.Description & \"'\"\r\n     End Select<\/span>\r\nEnd Sub<\/span><\/pre>\n
Listing 4: Prozedur zum Speichern der Wechselkurse mit Parametern<\/span><\/b><\/p>\n
Diese Prozedur deklariert ein Database<\/b>-Objekt namens db<\/b>, mit dessen Execute<\/b>-Methode gleich die Währung aus strWaehrung <\/b>in die Tabelle tblWaehrungen<\/b> eingetragen wird:<\/p>\n
db.Execute \"INSERT INTO tblWaehrungen(Waehrung)  VALUES('\" & strWaehrung & \"')\", dbFailOnError<\/pre>\n
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 <\/b>enthält. Das würde darauf hindeuten, dass bereits ein Datensatz mit der zu speichernden Währungsbezeichnung in der Tabelle tblWaehrung <\/b>vorhanden ist.<\/p>\n
Der Fehler wird ausgelöst, weil wir für das Feld Waehrung <\/b>einen eindeutigen Index definiert haben.<\/p>\n
Egal, ob wir den Datensatz in der Tabelle tblWaehrungen <\/b>neu anlegen oder nicht, wir benötigen den Wert des Primärschlüsselfeldes der Tabelle tblWaehrungen <\/b>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<\/b>-Funktion, der wir den Namen der Währung als Vergleichswert für das Feld Waehrung <\/b>übergeben und tragen den zurückgelieferten Wert in die Variable lngWaehrungID <\/b>ein. Falls die Währung noch nicht in der Tabelle tblWaehrungen <\/b>vorhanden war, können wir diese mit der Abfrage SELECT @@IDENTITY <\/b>ermitteln, welche den zuletzt hinzugefügten Autowert liefert:<\/p>\n
lngWaehrungID = db.OpenRecordset(\"SELECT @@IDENTITY\",  dbOpenDynaset).Fields(0)<\/pre>\n
Die Tabelle tblWaehrungen <\/b>sieht danach wie in Bild 5 aus.<\/p>\n
\"Daten<\/p>\n
Bild 5: Daten in der Tabelle tblWaehrungen<\/span><\/b><\/p>\n
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 <\/b>eintragen:<\/p>\n
db.Execute \"INSERT INTO tblWechselkurse(WaehrungID,  Wechselkursdatum, Wechselkurs) VALUES(\"  & lngWaehrungID & \", \" & SQLDatum(datDatum)  & \", \" & Replace<\/span>(curWechselkurs, \",\", \".\") & \")\"<\/pre>\n
Auch dies erledigen wir bei deaktivierter Fehlerbehandlung, damit keine Fehlermeldung erscheint, wenn wir versuchen, einen Datensatz für eine bereits vorhandene Kombination einzutragen. Das Ergebnis in der Tabelle tblWechselkurse <\/b>sehen Sie in Bild 6.<\/p>\n
\"Daten<\/p>\n
Bild 6: Daten in der Tabelle tblWechselkurse<\/span><\/b><\/p>\n
Auf diese Weise können wir die tagesaktuellen Daten für die Wechselkurse ermitteln. Aber vielleicht benötigen Sie diese nicht nur für den aktuellen Tag, sondern wollen auch die Wechselkurse der Vergangenheit einlesen, um die Entwicklung zu verfolgen. Dafür gibt es spezifischere Webservices, die wir nachfolgend beschreiben.<\/p>\n
Webservice für spezifische Daten zu Wechselkursen<\/b><\/p>\n
Auf der folgenden Webseite finden Sie die Startseite der Dokumentation des Webservice der europäischen Zentralbank:<\/p>\n
https:\/\/sdw-wsrest.ecb.europa.eu\/help\/<\/pre>\n
Bei dem Webservice handelt es sich um einen RESTful Webservice, was bedeutet, dass Sie die Parameter alle über die URL übergeben und das Ergebnis als XML-Dokument zurückbekommen. <\/p>\n
Den Webservice erreichen Sie über den folgenden Link:<\/p>\n
https:\/\/sdw-wsrest.ecb.europa.eu\/service<\/pre>\n
Dem können Sie einige Unterelemente beziehungsweise Parameter anhängen:<\/p>\n
https:\/\/sdw-wsrest.ecb.europa.eu\/service\/<resource>\/<flow-Ref>\/<key>\/<providerRef><queryStringParameters><\/pre>\n
Die in spitzen Klammern stehenden Elemente haben die folgende Bedeutung:<\/p>\n