Aktienkurse per VBA abfragen: Power-Query-Editor

Aktienkurse in Excel importieren: Aktienkurse per VBA abfragen

Hier haben wir bereits gezeigt, wie sich historische Aktienkurse in Excel importieren lassen. Die im verlinkten Beitrag gezeigte Vorgehensweise lässt sich ganz ohne Programmierkenntnisse umsetzen. In diesem Artikel wollen wir hingegen darauf eingehen, wie sich historische Aktienkurse per VBA abfragen und per Knopfdruck aktualisieren lassen. Dafür ist ein bisschen mehr Excel-Wissen nötig.

Konkret zeigen wir Dir, wie Du historische Aktienkurse über die Yahoo Finance beziehen kannst. Mit ein bisschen Excel-Magie beziehungsweise VBA lassen sich so die Kurse von Yahoo in Excel importieren.
Der Vorteil dieser Vorgehensweise ist, dass Du die Abfrage dynamisch gestalten kannst. Damit ist gemeint, dass zum Beispiel der gewünschte Zeitraum der historischen Daten in Excel direkt eingestellt werden kann. Außerdem musst Du so nicht pro Aktie jedes Mal eine neue Abfrage erzeugen.

Bevor wir jedoch loslegen, hier ein paar Quick-Facts zum Artikel Aktienkurse per VBA abfragen.

Aktienkurse per VBA abfragen: Quick Facts

  • Wir suchen uns eine Aktie auf Yahoo Finance
  • Anschließend zeigen wir, wie man in Excel ein Makro aufzeichnet
  • Danach importieren wir die Daten von Yahoo Finance in Excel
  • Zum Schluss parametrisieren wir den Programmcode so, dass die Abfrage dynamisch ist

Aktie auf Yahoo Finance finden

Um die historischen Aktienkurse per VBA abzufragen und die Kurse in Excel zu importieren, besuchen wir zunächst Yahoo Finance. In der Suchmaske geben wir die Aktie ein, deren Kurse wir in Excel haben wollen.

Also Beispiel wollen wir den norwegischen Lachszüchter Mowi ASA nehmen, den wir hier vorgestellt haben. In die Suchmaske geben wir also zunächst Mowi ein.

Aktienkurse in Excel importieren: Aktie auswählen

Im Drop-Down-Menü sehen wir die Treffer, die Yahoo uns zurückgibt. Auf der rechten Seite ist der Wertpapiertyp und die zugehörige Börse gelistet, von der wir die Kurse beziehen wollen. In unserem Fall wählen wir die Frankfurter Börse aus. Damit bekommen wir Kurse in Euro.

Nachdem wir auf die Aktie geklickt haben, gehen wir auf den Bereich Historische Daten. Auf der rechten Seite befindet sich ein Download-Link. Diesen kopieren wir uns in die Zwischenablage indem wir mit der rechten Maustaste auf den Link klicken und Adresse des Links kopieren auswählen.

Aktienkurse in Excel importieren: Link kopieren

Entwicklertools in Excel aktivieren

Als Nächstes solltest Du die Entwicklertools in Excel aktivieren. Dieses Werkzeug wird benötigt, um das Makro aufzuzeichnen und den Programmcode zu schreiben. Deswegen musst Du unter Datei auf Optionen gehen. In den dortigen Excel-Optionen findest Du den Punkt Menüband anpassen. Auf der rechten Seite setzen wir den Haken bei Entwicklertools. Danach kannst Du das Menü mit Ok verlassen.

Aktienkurse in Excel importieren: Entwicklertools aktivieren

Wie Du siehst, ist unser Excel auf Deutsch eingestellt. Ist Deine Systemsprache auf Englisch beziehungsweise ist Dein Excel auf Englisch eingestellt, heißen die Menüpunkte ein bisschen anders. Du solltest die Entwicklertools unter File>Options>Customize Ribbon>Developer Tools finden.

Sollte alles funktioniert haben, hast Du jetzt ein Punkt in Deinem Menüband mit dem Namen Entwicklertools. Außerdem hast Du noch den Link von der ausgesuchten Aktie in der Zwischenablage.

Aktienkurse per VBA abfragen: Yahoo-Finance Abfrage

Du hast den Link von Yahoo ja noch auf der Zwischenablage. Jetzt schauen wir ihn mal genauer an. Er sollte in etwa so aussehen:

https://query1.finance.yahoo.com/v7/finance/download/PND.F?period1=1560695785&period2=1592318185&interval=1d&events=history

Die einzelnen Elemente innerhalb der URL bedeuten Folgendes:

  • PND.F: die Aktie, die wir ausgewählt haben, mit dem Börsenplatz Frankfurt
  • period1: das Startdatum des Zeitraums in Sekunden seit dem 01.01.1970 (Unixzeit)
  • period2: das Enddatum des Zeitraums in Sekunden seit dem 01.01.1970
  • interval: Abstand zwischen den Zeiträumen, möglich ist 1 Tag (1d), 1 Woche (1wk) oder 1 Monat (1mo)
  • events: für Aktienkurse wähle history für Dividenden verwende div

Als Nächstes wollen wir diese Abfrage dynamisch gestalten, sodass wir die Daten jeder beliebigen Aktie auf einen Klick abfragen können. Um das Grundgerüst zu erhalten, verwenden wir zunächst den Makrorecorder in Excel.

Ein Excel Makro aufnehmen

Den Makrorecorder findet ihr unter dem Reiter Entwicklertools in Excel. Auf der linken Seite befindet sich dort eine Schaltfläche mit dem Namen Makro aufzeichnen.

Aktienkurse in Excel importieren: Makro aufzeichnen
Aktienkurse in Excel importieren: Entwicklertools aktivieren

Wir klicken als zunächst auf Makro aufzeichnen, bevor wir fortfahren. Im darauffolgenden Fenster kannst Du als Makronamen zum Beispiel Kursabfrage eintragen. Anschließend bestätigst Du die Eingabe mit Ok. Alles, was Du jetzt in Excel durchführst, wir im Hintergrund quasi aufgezeichnet, bis Du den Recorder wieder beendest. Das wollen wir uns jetzt zunutze machen. Ähnlich wie in der Anleitung zur Abfrage von historische Aktienkursen in Excel ohne VBA, die hier abgelegt ist, fragen wir jetzt die Daten von Yahoo Finance über das Menü Daten>Aus dem Web ab.

Datenabfrage aus dem Web

Im Reiter Daten befindet sich auf der linken Seite, die Schaltfläche zur Datenabfrage aus dem Web. Nachdem Du darauf geklickt hast, wirst Du nach einer URL gefragt, von der Du die Daten abfragen willst.

Aktienkurse in Excel importieren: Daten auf dem Web abfragen

In das Fenster kannst Du die Yahoo-Finance URL hineinkopieren, die Du noch auf der Zwischenablage hast. Anschließend musst Du Excel noch mitteilen, wie Du die Daten abfragen willst. In diesem Fenster kannst Du alles auf der Voreinstellung Anonym belassen und mit Verbinden quittieren. So oder so ähnlich sollte Dein Fenster anschließend aussehen:

Aktienkurse in Excel importieren: Datenabfrage aus dem Web

Power-Query-Editor

Ein klick auf Laden würde Dir die Daten in Excel einladen, so wie sie abgebildet sind. Wie Du aber sehen kannst, wurden die Kurse von Excel nicht richtig erkannt. Deswegen gehen wir zunächst auf Daten transformieren. Das bringt den Power-Query-Editor zum Vorschein.

Der Editor hat schon selbstständig versucht, die Daten zu interpretieren und umzuwandeln. Auf der rechten Seite findest Du die vom Editor angewandten Schritte. Hier löschen wir den Schritt Geänderter Typ mit einem Klick auf das vorangestellte X, sodass dort nur noch Quelle und Höher gestufte Header steht. Deine Abfrage im Power-Query-Editor sollte zunächst so aussehen.

Aktienkurse in Excel importieren: Power-Query-Editor

Wie Du sehen kannst, müssen wir jetzt noch den Datentyp für die einzelnen Spalten anpassen. Dazu markierst Du eine Spalte, indem Du auf die oberste Zelle klickst und gehst unter Transformieren>Datentyp auf den für die Spalte entsprechenden Datentyp. Für die erste Spalte wählen wir den Datentyp Datum aus. Excel hat jetzt den Datentyp entsprechend angepasst.

Um Dezimalzahlen in den Spalten Open, High, Low, Close und Adj. Close zu erhalten, markierst Du die Spalten und gehst unter Tranformieren>Analysieren auf JSON. Danach sollte in Deinem Power-Query-Editor in den entsprechenden Spalten die Dezimalwerte auftauchen.

Aktienkurse in Excel importieren: Power-Query-Editor JSON

Die letzte Spalte ist das Volumen. Da es sich dabei um eine ganze Zahl handelt, markierst Du die Spalte und wählst für die Spalte Transformieren>Datentyp>Ganze Zahl aus.

Anschließend kannst Du den Power-Query-Editor mit einem schließen. Darauf erscheint ein Fenster, mit der Frage, ob Du die Änderungen beibehalten willst. Dieses quittieren wir mit Beibehalten.

Die Aufzeichnung des Makros können wir nun auch über Entwicklertools>Aufzeichnung beenden stoppen.

Aktienkurse per VBA abfragen: der Programmcode

Excel beziehungsweise der Makrorecorder von Excel hat im Hintergrund den folgenden Code für uns erzeugt. Den Code des Markos kannst Du über Entwicklertools>Makro>Abfragen einsehen und editieren. Er sollte in etwas so aussehen:

Sub Kursabfrage()
'
' Kursabfrage Makro
'
    ActiveWorkbook.Queries.Add Name:="PND", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(Web.Contents(""https://query1.finance.yahoo.com/v7/finance/download/PND.F?period1=1560695785&period2=1592318185&interval=1d&events=history""),[Delimiter="","", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Tabl" & _
        "e.TransformColumnTypes(#""Höher gestufte Header"",{{""Date"", type date}})," & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"" = Table.TransformColumns(#""Geänderter Typ"",{{""Open"", Json.Document}, {""High"", Json.Document}, {""Low"", Json.Document}, {""Close"", Json.Document}, {""Adj Close"", Json.Document}})," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ1"" = Table.TransformColumnTypes(#""Analysierte JSON""," & _
        "{{""Volume"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ1"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=PND;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [PND]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "PND"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Deutlich ersichtlich ist die Quelle, von der die Daten abgerufen werden. In der Klammer von Web.Contents(...) steht die URL, die wir von Yahoo-Finance kopiert und in Excel eingefügt haben.

Dieses Makro beziehungsweise diese URL musst Du jetzt so modifizieren, dass die Abfrage parametrisierbar wird. Schließlich willst Du nicht bei jedem Makroaufruf, die Daten von Mowi abrufen, sondern vielleicht auch von Royal Dutch Shell oder einer Aktie Deiner Wahl.

Wir definieren uns dazu zunächst fünf Variablen. Zum einen benötigen wir die, um die Daten aus der Excel-Tabelle aus VBA heraus anzusprechen. Zum anderen speichern wir darin aber auch ein paar Zwischenergebnisse. In folgendem Listing siehst Du die fünf Variablen und dahinter die entsprechende Erklärung. Die Definition der Variablen beginnt jeweils mit einem Dim.

Dim StDatum As Long ' das Startdatum
Dim EndDatum As Long ' das Enddatum
Dim Aktie As String ' Name der Aktie aus der Excel-Tabelle
Dim Name As String ' Name der Aktie ohne Punkt PND.F -> PND
Dim Url As String ' die zusammengesetzte URL

Am einfachsten geht das Ansprechen von Tabelle in Excel über die Funktion Range. Damit kannst Du entweder einzelne Zellen oder ganze Bereiche angeben. Irgendwohin müssen wir die Abfrage aber auch gespeichert werden. Als Ziel wählen wir die definierten Variablen.

Aktie = Range("B1")
StDatum = DateDiff("s", DateSerial(1970, 1, 1), Range("B2"))
EndDatum = DateDiff("s", DateSerial(1970, 1, 1), Range("B3"))
Name = Split(Aktie, ".")(0)

Url = "https://query1.finance.yahoo.com/v7/finance/download/" & Aktie & "?period1=" & StDatum & "&period2=" & EndDatum & "&interval=1d&events=history"

Wie Du im Listing sehen kannst, rechnen wir mit DateDiff gleich die Unixzeit aus. Das Start- und Enddatum lesen wir aus der Zelle B2 bzw. B3. In die Variable Url speichern wir die parametrisierte URL. Anschließend müssen wir in den vom Makrorecorder erstellten Programmcode noch die Variablen an die entsprechenden Stellen einfügen. Am Ende sieht unser kleines Kursabfrage-Makro so aus:

Sub Kursabfrage()
'
' Kursabfrage
'

Dim StDatum As Long
Dim EndDatum As Long
Dim Aktie As String
Dim Name As String
Dim Url As String

StDatum = DateDiff("s", DateSerial(1970, 1, 1), Range("B2"))
EndDatum = DateDiff("s", DateSerial(1970, 1, 1), Range("B3"))
Aktie = Range("B1")
Name = Split(Aktie, ".")(0)

Url = "https://query1.finance.yahoo.com/v7/finance/download/" & Aktie & "?period1=" & StDatum & "&period2=" & EndDatum & "&interval=1d&events=history"

    ThisWorkbook.Queries.Add Name:=Name, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(Web.Contents(""" & Url & """),[Delimiter="","", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"" = Ta" & _
        "ble.TransformColumns(#""Höher gestufte Header"",{{""Open"", Json.Document}, {""High"", Json.Document}, {""Low"", Json.Document}, {""Close"", Json.Document}, {""Adj Close"", Json.Document}, {""Volume"", Json.Document}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"""
    ThisWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Name & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.ListObject.DisplayName = "PND"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Aktienkurse per VBA abfragen: Kurse abfragen

Um die Kurse abzufragen, musst Du zunächst die Zeilen B1 bis B3 mit Werten befüllen. Anschließend wählst Du das Makro über Entwicklertools>Makros>Kursabfrage aus.

Aktienkurse in Excel importieren: Kurse abfragen

Wenn alles geklappt hat, solltest Du jetzt für den von Dir gewählten Zeitraum und für die von Dir gewählte Aktie die historischen Aktienkurse in Excel zur Verfügung stehen haben.

Wie Du siehst, ist es mit dem Excel und dem Makrorecorder, relativ einfach historische Aktienkurse in Excel zu importieren beziehungsweise per VBA abzufragen. Ein paar kleine Excel-Kniffe genügen schon.

Das Marko soll als Startpunkt für Deine Projekte dienen. Denkbar wäre es, sich zum Beispiel noch einen Button einzubauen, auf dem das Makro hinterlegt ist. Weiterhin könnte man auch gleich einen Chart für die Aktie erstellen lassen.