Aktuelle Wechselkurse aus dem Web abrufen
Wechselkurse sind in vielen Branchen wichtig, um beispielsweise eine stabile Basis für die Kalkulation zu haben. Es gibt diverse Seiten im Netz, die aktuelle Wechselkurse unterschiedlichster Währungen anbieten. Eine aus unserer Sicht geeignete und zuverlässige Seite ist das Angebot der EZB: http://wechselkurse-euro.de/ weil dort auch die eine oder andere Besonderheit enthalten ist, die ein Anlass zur Wissensvermittlung sein kann. In jedem Fall kommen diese „Besonderheiten” in vielen Variationen bei diversen Aufgaben vor. 😉
Wenn Sie die Website öffnen, wird sich das folgende Bild (Stand 5.8.2016) bieten:
Die Daten im Web als Tabelle
Diese Tabelle soll an jedem Arbeitstag importiert und anschließend in einem Arbeitsblatt in etwas „verschlankter” Form dargestellt werden. Es werden dann nur die wichtigen Werte angezeigt, um das Ganze einerseits übersichtlich zu gestalten und auch keine überflüssigen Daten in der Tabelle zu speichern, weil beispielsweise mit der SVERWEIS()-Funktion darauf zugegriffen werden soll. Gerade dieses Argument zeigt Ihnen aber auch auf, dass es extrem wichtig ist, dass der Aufbau immer gleich ist und nicht irgendwann einmal mehr oder weniger Spalten in der Tabelle sind.
Grundsätzlicher Hinweis
Power Query wird auch heute noch ständig und in relativ kurzen Abständen aktualisiert. Das führt dazu, dass es durchaus denkbar ist, dass sie das eine oder andere nicht genauso nachvollziehen können, wie es hier geschildert ist. Das beste Beispiel: Als ich Mitte Dezember 2016 diesem Beitrag abschließend bearbeitet habe, musste ich verschiedene Stellen streichen oder stark aktualisieren.
Im Prinzip sind all diese Änderungen eine sehr willkommene Verbesserung. Teilweise sind auch neue Funktionalitäten dabei. Das bedeutet für Sie aber auch, dass sie seitens Microsoft angebotene Aktualisierungen dieses Add-Ins auch durchführen sollten. Vieles gelingt dadurch einfacher, eleganter, bequemer. – Falls sie also bei der Abarbeitung dieser Beispiele den einen oder anderen Punkt nicht so wie beschrieben nachvollziehen können, liegt es gewiss an einer solchen Aktualisierung. In diesem Rahmen bitte ich Sie, mir eine kurze Nachricht mit dem Hinweis auf die nunmehr nicht mehr aktuelle Situation zukommen zu lassen. Ich werde dann den Beitrag im Blog entsprechend anpassen. Danke.
Import mit Power Query in Excel 2010⁄13
Hinweis: Wenn Sie mit Excel >=2016 arbeiten, geht es hier weiter.
Grundsätzlich wird der Import per PQ in anderen Beiträgen genauer beschrieben. Darum beschränken wir uns hier auf Stichworte und kurze Statements, Screenshots nur dort, wo sie nicht redundant wären.
- Öffnen Sie in einem beliebigen Internet-Browser die Seite mit den Quell-Daten. Das dient der Kontrolle, dass es auch aktuelle Werte sind.
- Kopieren Sie die URL (die Adresse der Seite) in die Zwischenablage oder notieren Sie sich die Adresse.
- Aktivieren Sie den Menüpunkt Power Query.
- In der Gruppe Externe Daten abrufen wählen Sie das erste Symbol Aus dem Web.
- Im Dialogfenster geben Sie die notierte URL ein oder fügen sie diese aus der Zwischenablage ein. Anschließend mit OK bestätigen.
- Klicken Sie links im Navigator die einzelnen Tabellen an (sofern mehrere Tabellen angezeigt werden) und überprüfen Sie im rechten Vorschau-Bereich, welche Tabelle die gewünschten Daten enthält.
- Nach der Auswahl entweder auf Laden Klicken, um die Daten in ein neues Arbeitsblatt zu importieren; oder Sie Klicken auf die ▼-Schaltfläche direkt rechts daneben und nutzen die Möglichkeit, über Laden in… einen definierten Bereich in einem ausgewählten Sheet mit den zu importierten Daten zu füllen. – Weiter geht es hier.
Import mit Power Query in Excel ≥2016
Grundsätzlich wird der Import per PQ in anderen Beiträgen genauer beschrieben. Darum beschränken wir uns hier auf Stichworte und kurze Statements.
- Öffnen Sie in einem beliebigen Browser die Ziel-Seite. Das dient der Kontrolle, dass es auch aktuelle Daten sind.
- Kopieren Sie die URL (die Adresse der Seite) in die Zwischenablage oder notieren Sie sich die Adresse.
- Aktivieren Sie den Menüpunkt Daten.
- In der Gruppe Abrufen und Transformieren wählen Sie zuerst das Symbol Neue Abfrage und dort die Möglichkeit Aus anderen Quellen und im Untermenü dann Aus dem Web. Im Dialogfenster geben Sie die notierte URL ein oder fügen sie diese aus der Zwischenablage ein. Anschließend mit OK bestätigen.
- Klicken Sie links im Navigator die einzelnen Tabellen an (sofern mehrere Tabellen angezeigt werden) und überprüfen Sie im rechten Vorschau-Bereich, welche Tabelle die gewünschten Daten enthält.
- Nach der Auswahl entweder auf Laden Klicken, um die Daten in ein neues Arbeitsblatt zu importieren; oder Sie Klicken auf die ▼-Schaltfläche direkt rechts daneben und nutzen die Möglichkeit, über Laden in… einen definierten Bereich in einem ausgewählten Sheet mit den zu importierten Daten zu füllen.
Analyse des Imports
Grundsätzlich sollten Sie nach einem Import kontrollieren, was importiert wurde. Dazu gehört auch, dass Sie stichprobenartig vergleichen, ob das überhaupt die richtigen Daten sind und auch, dass die Werte mit Ihrer Zielvorstellung übereinstimmen. Und da ist schon die eine oder andere Überraschung drin …
Ganz offensichtlich ist, dass es leere Spalten gibt. Zumindest im Daten-Bereich sind die Spalten leer, eine Überschrift wurde (bei Bedarf) durch Power Query eingefügt, denn in einer Tabelle/Liste muss jede Spalte eine Überschrift haben. Welche Spalten das sind und warum das so ist, das werden Sie spätestens auf den zweiten Blick erkennen.
Die Zahlen sollten stimmen. Oder doch nicht? Nun ja, die Ziffernfolge ist OK, aber mit etwas Logik werden Sie erkennen, dass der Wert der Zahlen entschieden zu hoch ist. Wenn Sie die Zahlen mit denen in der Website vergleichen, dann erkennen sie recht rasch, dass in diesem Import zwar die Ziffernfolge stimmt aber der in der Web-Site vorhandene trennende Punkt nicht vorhanden ist. Er wurde einfach gelöscht.
Schauen Sie sich doch einmal diesen Screenshot an:
Die automatische Umwandlung ist nicht immer optimal …
Im rechten Seitenfenster erkennen sie, dass der letzte Schritt (der automatisch durch Power Query durchgeführt wurde) eine Typ-Änderung ist. Und wenn sie dann auf beliebigem Wege erkunden, welchen Datentyp die Spalte mit den Umrechnungswerten derzeit hat wird Ihre wahrscheinlich Vermutung, dass es sich um eine Ganzzahl handelt, gewiss bestätigt.
Machen Sie diesen Schritt einfach einmal rückgängig. Dazu Klicken Sie im rechten Seitenfenster in der Gruppe Angewendete Schritte auf das 
links des Eintrags und umgehend wird die Spalte in genau dem Format dargestellt, wie es auch in der Website sichtbar ist. Der Datentyp wird automatisch auf Text geändert und an der entsprechenden Stelle wird ein Punkt als brennendes Element eingesetzt.
Dieser Punkt ist kein Tausenderpunkt ist sondern der Dezimaltrenner. Mit anderen Worten: Statt des sonst in Deutschland gebräuchlichen Kommas ist ein Punkt verwendet worden; man gibt sich international, auch wenn die EZB ihren Sitz in Frankfurt hat.
Bereinigung der Daten
Der Klassiker
Zugegeben, eigentlich gehört diese Vorgehensweise nicht in diesem Beitrag. Schließlich geht es hier in erster Linie um die Anwendung von Power Query. Aber wenn Sie zu den Usern gehören, die am liebsten eingefahrenen Wege beschreiten, dann kann das durchaus für Sie von Interesse sein. 😉 Hinweis: Wenn Sie zielgerichtet (und ausschließlich) die PQ-Methode kennen lernen wollen, dann machen Sie gleich hier weiter. Ansonsten …
Die Vorgehensweise: Nach dem Import und der Rück-Umwandlung zum Datentyp Text schließen Sie die Anfrage per Schließen & laden und dann löschen Sie in der neu erstellten Tabelle alle nicht benötigten Spalten. Sorgfalt ist natürlich angebracht, denn falls Sie mit SVERWEIS() arbeiten, muss die numerische Position der Ergebnisspalte immer die gleiche sein. Ich selbst ziehe aus diesem Grund vor, die „überflüssigen” Spalten bereits im Query-Editor zu löschen. Das bringt mehr Sicherheit.
Die Zahlen, die Kurse sind bzw. scheinen ja alle um den Multiplikator 1000 zu hoch, weil beim Import der eigentliche Dezimalpunkt als 1000er Punkt gewertet wurde. Aber das scheint nur so zu sein. Einzelne Währungen würden bei einer Division durch 1000 durchaus fehlerhafte Werte zurückgeben. Probieren Sie es gerne einmal beim Schweizer Franken aus; Ihnen wird gewiss klar sein, dass dieser auch bei einer Schwäche des Euro keineswegs ca. 10 € wert ist. Darum ist die einfachere Denkweise auch die bessere:
- Markieren Sie die Spalte mit dem Kurs für 1 €
- StrgH und es öffnet sich das Menü zum Ersetzen von Werten
- Ersetzen Sie den Punkt durch ein Komma
- Achten Sie darauf, dass die Ziffern Folgen jetzt rechtsbündig sind.
Bei allen Vorgehensweisen identisch: Zum Schluss werden Sie das Tabellenblatt umbenennen. Vielleicht in „Heute” bzw. das aktuelle Datum oder einen anderen beliebigen Namen der darauf hinweist, dass es die aktuellen Daten sind. Das ist besonders dann wichtig, wenn mehrere Tage in der Mappe erfasst werden um eine Kursentwicklung darstellen zu können oder zu Prüfzwecken „archiviert” werden sollen.
Zusammenfassung: Das Maß an Automatisierung ist hier sehr gering, von der gesamten Aufgabe wird nur der Import ohne Ihr Zutun gesteuert, der Rest ist vielfach Handarbeit und somit fehleranfällig. Außerdem ist nicht immer gewährleistet, dass nach einer Aktualisierung wiederum korrekte Werte vorliegen.
Überwiegend genutzt
Wohl als künftigen Standard zu bezeichnen ist die Vorgehensweise, die Sie vielleicht zumindest teilweise schon kennen, wenn Sie diesen Beitrag gelesen haben oder auch den zweiten Teil des Einstiegs. Da dieses aber ein eigenständiger Blog-Beitrag ist und sich dieses oder jenes doch anders darstellt, zeigen wir Ihnen auch hier den Weg ausführlich auf.
Voraussetzung für das weitere Vorgehen ist, dass der Query Editor geöffnet ist und die importierten Daten sichtbar sind; das könnte sich so darstellen wie dem hierunter gezeigten Bild. Um sie nicht zu irritieren, habe ich die Zahlen etwas unkenntlich gemacht. Denn: Ich habe diesen Screenshot natürlich zu einem anderen Zeitpunkt gefertigt und die Zahlen sind zum Zeitpunkt Ihrer Arbeit mit diesem Beitrag gewiss anders.
Die Daten direkt nach dem Import in den Query Editor
Da es ja nur 31 Zeilen mit Daten sind, ist das ganze ja recht übersichtlich. Es fällt sofort auf, dass einige Spalten vom Inhalt her komplett leer sind. Dieses sind die zweite Spalte sowie die beiden letzten (Graph und Rech.). Diese Spalten brauchen sie in gar keinen Fall. Darum markieren Sie die zweite Spalte, drücken Strg und markieren dann nacheinander die beiden letzten Spalten. Jetzt ein Rechtsklick in eine der Überschriften der markierten Spalten und im Kontextmenü wählen Sie den zweiten Eintrag Spalten entfernen.
Jetzt sieht das ganze ja schon etwas gestraffter aus. Dennoch sollten Sie verbleibenden Spalten darauf prüfen, ob sie diesen Inhalt für eine künftige Auswertung überhaupt brauchen. Die erste Spalte wo nur der Text Wechselkurse drin steht gehört gewiss nicht dazu. Bei den anderen Spalten ist es Ihnen anheim gestellt, ob sie die Information benötigen oder nicht. Wenn sie öfter mit den Währungen umgehen, werden sie mit Sicherheit die Spalte Land für die Identifikation nicht brauchen. Sie kennen die typischen Abkürzungen. Auch die Spalte Währung ist in den meisten Fällen nicht vonnöten. Die Veränderung als auch den Gegenkurs werden sie in den meisten Fällen auch nicht benötigen. Löschen Sie also wie eben schon geschehen die nicht benötigten Spalten aus der Tabelle. Alternativ könnten sie auch die (zwei) zu verbleibenden Spalten markieren, in eine der beiden Überschriften einen Rechtsklick durchführen und im Kontextmenü dann die dritte Auswahl Andere Spalten entfernen wählen. – Ach ja, wenn Sie jetzt denken: „Das mit den 2 zu verbleibenden Spalten hätte ich ja auch gleich machen können …” dann ist das 100% richtig. 😎
Damit haben sie die aktuellen Umrechnungskurse auf dem Bildschirm. Aber es bleibt der „Makel“, dass durch den unerwünschten Dezimaltrenner (Punkt) die Werte im Kurs für einen Euro in jedem Fall hoch sind. Um das zu bereinigen nutzen Sie hier in Power Query praktisch die gleiche Vorgehensweise wie sonst in Excel auch, nämlich den Punkt durch ein Komma ersetzen. Markieren Sie erst einmal die zweite Spalte mit den Kurswerten. Klicken Sie mit der rechten Maustaste in die Überschrift und wählen im Kontextmenü dann den Punkt Werte ersetzen. Bei Zu suchender Wert tragen sie einen . in das Textfeld ein, bei Ersetzen durch ein ‚. Anschließend ein Klick auf OK. Es wird sie vielleicht irritieren, dass die Kurse immer noch linksbündig dargestellt werden. Das ist auch korrekt so, denn der Datentyp ist immer noch Text. Also werden sie den Datentyp jetzt zu Dezimalzahl ändern und sofort werden die Kurse auch rechtsbündig in der Spalte dargestellt. Ein Kontrollblick beim Schweizer Franken zeigt Ihnen auch, dass offensichtlich alles wie gewollt ist.
Ein Klick auf Speichern & laden und die Daten werden in einer ganz normalen Excel Tabelle in einem neuen Arbeitsblatt angezeigt. Der ausgesprochen angenehme Nebeneffekt dabei ist, dass die Excel Tabelle auch einen sprechenden Namen hat. Hier ist es der Name Aktuelle Wechselkurse EZB gefolgt vom Wochentag und dem Datum des Tages, der die Daten widerspiegelt. Das sorgt für Transparenz.
Was ist aber, wenn sie eigentlich nur ein zwei oder drei Währungen für ihre Umrechnungen benötigen? Müssen Sie dann jedes Mal in der Ergebnistabelle entweder alle anderen Werte mitschleppen oder per Hand löschen bzw. filtern? Natürlich nicht. Auch das lässt sich in PQ elegant lösen. Dazu öffnen Sie wiederum den Abfrage-Editor, beispielsweise indem sie im rechten Seitenfenster auf den Namen der Abfrage zeigen und im Bereich der Statuszeile des erschienenen Dialogs auf den Punkt Bearbeiten Klicken.
Angenommen sie brauchen den US-Dollar, das Britische Pfund und die Schweizer Franken als Umrechnungskurs, dann Klicken Sie auf den Dropdown-Pfeil 
der Spalte Abkürzung. Sie könnten nun das Häkchen beim Eintrag (Alles auswählen) entfernen und dann bei den drei Währungsbezeichnungen das Häkchen setzen. Ich möchte Ihnen aber noch einen weiteren Weg aufzeigen: Wählen Sie im Menü die Auswahl Textfilter. Hier wiederum wählen Sie den Eintrag Ist gleich… und es öffnet sich ein Dialogfenster, wo sie die Auswahl der weiterhin anzuzeigenden Zeilen (Währungen) treffen können. Da Sie im Modus Standard nur zwei Einträge wählen können, Klicken Sie bitte auf die Options-Schaltfläche Erweitert. Umgehend stellt sich das Fenster etwas anders dar.
Die erste Zeile ist ja teilweise schon korrekt ausgefüllt. Tragen Sie bei Wert USD ein. In der Zeile darunter ist an erster Stelle ein Feld wo sie die Art der Verknüpfung festlegen. Hier ist natürlich das Oder die richtige Wahl, denn es soll ja entweder der Dollar oder das Pfund angezeigt werden. Beides (entspräche einem Und) kann nicht in der einen Zelle stehen. In der Spalte Operator wählen Sie auch hier Ist gleich, bei Wert tragen sie GBP ein. Um die dritte Möglichkeit für den Schweizer Franken hinzuzufügen, Klicken Sie auf die Schaltfläche Klausel hinzufügen. Auch hier ist es natürlich die Oder-Bedingung, die Feldbezeichnung Abkürzung ist selbstverständlich auch korrekt, als Operator wiederum Ist gleich und beim Wert kommt die Zeichenfolge CHF hinein. Bestätigen Sie mit OK und die Anzahl der Zeilen im Editor ist auf drei geschrumpft. – Schließen Sie den Editor und auch die Tabelle im Arbeitsblatt hat sich auf dieses Minimalvolumen angepasst.
Nutzen der Daten
Prinzipiell könnten Sie diese Mappe mit der Umrechnungstabelle schließen und als Referenz für ein anderes Arbeitsblatt in einer getrennten Mappe nutzen. Da würde sich dann beispielsweise ein SVERWEIS() anbieten. Eventuell wäre auch das Speichern dieser Abfrage in der Mappe mit den umzurechnenden Werten hilfreich.Übrigens: Das Speichern kann einen Moment länger dauern als gewohnt, weil die Abfrage dabei noch einmal aktualisiert wird. Das können Sie aber in diesem Fall gerne unterbrechen.
Natürlich wollen Sie die Daten nicht nur am Tag der Erstellung nutzen sondern auch später. Da ist der Hinweis wichtig, dass sich die Abfrage aus gutem Grund nicht automatisch mit dem derzeitigen Werten im Netz synchronisiert. Es reicht aber ein Klick auf Aktualisieren und die Tabelle der Abfrage wird die neuesten Daten darstellen.
Noch eine kleine Korrektur
Ist Ihnen im meiner Wortwahl etwas aufgefallen? „Könnte”, „würde”, … sind ja nicht unbedingt klare und eindeutige Aussagen. Und die Tücke liegt wie so oft im Leben im Detail. Erinnern Sie sich, dass ich vorhin noch lobend erwähnt habe, dass der Name der erstellten Liste automatisch ein „sprechender” ist? Prinzipiell stehe ich auch zu der Aussage, dass so etwas positiv ist.
Im Normalfall würde ein SVERWEIS() ja auf diese Liste, speziell den Namen der Liste Bezug nehmen. Das hat er den großen Vorteil, dass der Bereich dynamisch ist. Bei weniger oder auch mehr Einträgen wird immer auf den korrekten Bereich zugegriffen, ohne dass Sie den Bereich anpassen müssen. So weit, so gut. Aber diese Liste enthält ja den Namen des Tages, an dem die Abfrage erstmalig erstellt worden ist. Ich kann mir nun gut vorstellen, dass eine Formel mit dem Verweis auf eine Liste mit diesem Namen zu Verwirrung führen kann, wenn das aktuelle Datum ein ganz anderes ist.
Darum mein Vorschlag: Öffnen Sie noch einmal die Abfrage und geben im rechten Seitenfenster oben bei Eigenschaften den Namen Aktuell oder einen vergleichbar neutralen Begriff ein. Im Normalfall wird der neue Name dann auch automatisch in die aktualisierte Liste übernommen.
Beispiel mit SVERWEIS()
Der Einfachheit halber gehe ich davon aus, dass die erzeugte Liste mit den Umrechnungswerten in der gleichen Mappe ist. Der Name dieses Blattes (Register) ist Valuta. In einem weiteren Blatt erstellen Sie eine Rechnung. Beispielsweise für ein Maschinen-Teil. Um es Ihnen etwas einfacher zu machen, habe ich Ihnen hier eine Muster-Mappe hinterlegt. Hinweis: Mit der Syntax und der Anwendung der Funktion SVERWEIS() sollten Sie vertraut sein. Erforderlichenfalls kann Ihnen dieser Beitrag in unserem Blog eine kleine Hilfe sein.
Sie erkennen in der Muster-Mappe im Blatt Valuta die 3 Währungen und im Arbeitsblatt Tabelle1 eine kleine Rechnung mit einer Position. Um mich auf den Kern der Sache zu beschränken, sind die Spalten A:E von Hand eingegeben worden. In Spalte F ist es wichtig, dass sie die Fremdwährung mit der korrekten Abkürzung eingeben: Hier also die 3 Zeichen USD. In Spalte G wird nun für jeden Tag der Preis für den Kunden neu berechnet. Dabei wird automatisch auf den Wert des Tabellenblatts Valuta Bezug genommen.
Prinzipiell werden sie solch eine Rechnung natürlich noch formatieren, eventuell auch noch eine Sicherheit-Marge in den Preis für den Kunden integrieren. Hier geht es aber nur um das Prinzip, wie sie per SVERWEIS() auf die tagesaktuellen Werte der von ihnen verwendeten Währungen zugreifen können. So bietet sich für die verschiedenen Währungen auch eine Datenüberprüfung an …
Wenn sie sehr vertraut sind mit Power Query werden Sie vielleicht auch statt der Funktion SVERWEIS() eine Funktionalität von Power Query nutzen; dann kann die Tabelle mit den Währungen auch gerne in einer ganz anderen, sogar geschlossenen Mappe stehen und sie haben dennoch die gleiche sichere Umrechnung. Auch Kollegen könnten dann im Firmen-Netz darauf zugreifen. Diese Diskussion würde an dieser Stelle aber doch zu weit führen. Das Prinzip können Sie hier im Blog nachlesen …
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (z.B. 1,00€) Ihrerseits freuen …