Aktienkurse automatisiert abfragen mit Excel und YAHOO

24.08.2014

Dies ist der erste Artikel einer Serie, welche in Zukunft fortgesetzt wird.

Seit Jahren pflege ich die Daten meines Portfolios in einer Excel-Arbeitsmappe, und ich kann mit diesem Werkzeug recht genau nachvollziehen, wie es um Gewinn und Verlust steht.

Auch die Modell-Portfolios, welche Sie hier herunterladen können, werden mit Excel verwaltet. In dieser Arbeitsmappe muss ich, wie in meinem eigenen Portfolio, die aktuellen Kurs der Aktien jeweils von Hand nachtragen.

Dabei liesse sich die Sache weitgehend automatisieren. Yahoo stellt seit Jahren Werkzeuge zur Verfügung, mit welchen sich Aktienkurse aus aller Welt automatisiert abfragen lassen. Wenn man diese Werkzeuge mit der Programmierpower von Excel kombiniert, lassen sich recht einfach massgeschneiderte Lösung bauen. Einige Beispiele:

  • Der aktuelle Kurs einer Aktie wird abgefragt und in Excel in einer bestimmten Zelle angezeigt.
  • Die historischen Kurse einer Aktie werden abgefragt und in ein Arbeitsblatt übertragen. Die Kursentwicklung wird dann gleich in einem Diagramm angezeigt.
  • Die Dividenden der letzten zehn oder zwanzig Jahre einer Aktie werden ermittelt und die Dividendenrendite sowie das Wachstum der Dividende werden berechnet und in Excel angezeigt.
  • Die historischen Kurse einer Aktie werden abgefragt und in einer Access-Datenbank gespeichert. (Dazu ist nicht nötig, dass Sie Access installiert haben.)

Sie sehen, dass man mit diesem Ansatz einige ganz praktische Sachen anstellen kann.

So arbeitet das YAHOO-API zum Abfragen von aktuellen Aktienkursen

Das Yahoo-API ist einfach zu verwenden, und die Abfragen lassen sich einfach über den Browser testen, bevor sie im Programmcode verwendet werden.

Im ersten Schritt wird ein URL gebildet, der Yahoo mitteilt, an welchen Aktienkursen und an welchen Informationen wir interessiert sind. Sie können Kurse aus aller Welt abfragen, und Sie können, mindestens bei amerikanischen Aktien, eine grosse Anzahl an verschiedenen Informationen über eine Aktie abfragen. Für nicht-amerikanische Aktien gibt es nicht so viele Informationen, aber immerhin können Sie im Minimum aktuelle und historische Kurse abrufen. Der URL teilt Yahoo auch mit, wie die Datei heissen soll, welche der Dienst uns zurücksenden soll.

Im zweiten Schritt wird der URL an Yahoo gesendet. Das kann auf zwei Arten erfolgen: Man ruft den URL im Browser auf, und öffnet dann die heruntergeladene Datei mit der Antwort von Yahoo. Oder man sendet den URL per Programm an Yahoo und erhält dann die Antwortdatei von Yahoo.

Im dritten Schritt wird die Antwortdatei verarbeitet. Das kann manuell oder via Programm erfolgen.

Damit Sie Aktienkurse bei Yahoo abfragen können, müssen Sie die Symbole der gesuchten Aktien kennen. Symbole amerikanischer Aktien bestehen aus 1 bis 5 Buchstaben. Die Symbole aller anderen Aktien bestehen aus zwei Teilen: Dem Symbol der Aktie, gefolgt von einem Punkt und der Abkürzung der Börse, an welcher die Aktie gehandelt wird. Für Nestlé also NESN.VX, für SAP SAP.DE. Wenn Sie ein Symbol nicht kennen, können Sie es einfach herausfinden: Gehen Sie auf de.finance.yahoo.com und geben Sie im Textfeld Kurs abfragen einfach den Namen der Firma ein. Eine Auswahlliste unterhalb des Feldes zeigt Ihnen sofort alle passenden Symbole an.

Möchten Sie zum Beispiel das Symbol von Carlo Gavazzi an der Schweizer Börse finden, geben Sie einfach gava in das Feld ein, und schon haben Sie alle Treffer.

 

Aktuelle Aktienkurse abfragen

Schauen wir uns zuerst an, wie Sie aktuelle Aktienkurse abfragen. Nehmen wir an, dass Sie die aktuellen Kurse von Yahoo, Apple, Google, Microsoft, Nestlé und SAP abfragen wollen.

Sie beginnen mit diesem URL, den Sie - theoretisch - direkt im Browser ausprobieren können:

http://finance.yahoo.com/d/quotes.csv?s=YHOO+AAPL+GOOG+MSFT+NESN.VX+SAP.DE     

Damit wurde früher eine Datei mit dem Namen quotes.csv heruntergeladen, welche den Hinweis enthielt, dass die Formatangabe fehlt. Heute passiert gar nichts mehr, wenn Sie den URL absenden. Yahoo besteht jetzt darauf, dass wir via URL mitteilen, an welchen Informationen wir interessiert sind.

Mit der Formatangabe legen Sie also fest, welche Daten Yahoo liefern soll. Eine vollständige Übersicht aller Parameter finden Sie hier:

http://www.jarloo.com/yahoo_finance/

Wir beschränken uns auf ein paar wichtige Daten wie Symbol, Letzter Preis, Prozentuale Änderung, Datum und Uhrzeit. Das ergibt diesen URL

http://finance.yahoo.com/d/quotes.csv?s=YHOO+AAPL+GOOG+MSFT+NESN.VX+SAP.DE&F=sl1p2d1t1

Wenn Sie dies im Browser ausprobieren, wird wieder eine Datei namens quotes.csv heruntergeladen. Wenn Sie diese in Excel öffnen, sieht es ungefähr so aus:

 

Das Abfragen von historischen Kursen funktioniert grundsätzlich ähnlich, und wir werden das später ebenfalls anschauen.

Projekt 1: Wir automatisieren die Abfrage eines aktuellen Aktienkurses

Wenn wir die grundsätzliche Arbeitsweise zum Abfragen von aktuellen Kursen kennen, können wir die Abfrage im nächsten Schritt automatisieren. Zuerst machen wir das mit einer einzigen Aktie.

Ich stelle mir etwa diese Lösung vor:

In Zelle A1 trägt man ein Symbol ein, klickt dann auf die Schaltfläche „Kurs ermitteln“, und das Programm trägt die ermittelten Daten im Bereich B1:E1 ein.

Das sieht für das Programm so aus:

1.       Herausfinden, welcher Kurs abgefragt werden soll.

2.       Einen URL bilden, der als Antwort die angeforderten Daten liefert.

3.       Den URL aufrufen und die Antwort als Datei speichern.

4.       Die Ergebnisdatei in Excel öffnen.

5.       Die ermittelten Werte in Variablen übertragen.

6.       Die Ergebnisdatei wieder schliessen.

7.       Die Werte der Variablen zurück in den Bereich B1:E1 übertragen.

 

Halt ein typisches kleines Programm. Untypisch für ein Programm: Es gibt keine Bedingungen, und keine Wiederholungen.

Sie können Projekt 1 hier herunterladen.

Die Arbeit wird im Projekt von zwei Prozeduren und zwei Hilfsprozeduren ausgeführt, zusammen sind es weniger als 100 Zeilen einfachen Programmcodes. Die beiden wichtigsten Prozeduren heissen KursErmitteln und TransferCSVFromYahoo.

Der kommentierte Code sieht so aus:

Option Explicit

Sub KursErmitteln()

Dim url As String

Dim TempFilename As String

Dim Ticker As String 's

Dim AktuellerKurs   'l1

Dim AktuellesDatum 'd1

Dim AktuelleZeit    't1

Dim ProzentÄnderung 'p2

 

    'Doku zu finden auf

    'http://www.jarloo.com/yahoo_finance/

 

    'Bilde den Dateinamen der Datei, in welcher die heruntergeladenen Daten gespeichert werden.

    TempFilename = Environ("Temp") & "\yahoo_transfer.csv"

 

    'URL zum Aufruf des Yahoo-API

    url = "http://download.finance.yahoo.com/d/quotes.csv?s={0}&f=sl1d1t1p2c1ohgvm3m4&e=.csv"

   

    'Das Symbol steht in der Zelle A1

    Ticker = Range("A1")

   

    'Das Symbol wird im URL an er richtigen Stelle eingesetzt

    url = StrFormat(url, Ticker)

   

    'TransferCSVFromYahoo erledigt das Herunterladen und speichern der Daten

    TransferCSVFromYahoo url, TempFilename

   

    'die gespeicherte Datei kann in Excel geöffnet werden, da sie im CSV-Format vorliegt

    Workbooks.Open TempFilename

   

    'Wir interessieren uns für einige Werte aus der geöffneten Datei

    'und übertragen sie in die oben deklarierten Variablen

    AktuellerKurs = ActiveSheet.Range("B1")

    AktuellesDatum = ActiveSheet.Range("C1")

    AktuelleZeit = ActiveSheet.Range("D1")

    ProzentÄnderung = ActiveSheet.Range("E1")

 

    'Wir brauchen die Datei mit den heruntergeladenen Werten nicht weiter,

    'drum wird sie geschlossen

    ActiveWorkbook.Close False

    'und wenn alles gut geht auch gelöscht

    On Error Resume Next

    Kill TempFilename

    On Error GoTo 0

   

    'zuletzt übertragen wir die Werte, welche wir in den Variablen zwischengespeichert haben,

    'in unser Arbeitsblatt

    Range("B1") = AktuellerKurs

    Range("C1") = ProzentÄnderung

    Range("D1") = AktuellesDatum

    Range("E1") = AktuelleZeit

    Range("C1").NumberFormat = "0.00%"

   

End Sub

 

Sub TransferCSVFromYahoo(url As String, Filename As String)

'fragt die Kurse von Yahoo ab und schreibt das Ergebnis in eine Datei

'url:       der URL mit der Angabe der abzufragenden Aktien und der erwünschten Felder

'Filename: der Name der Datei, in welche das Resultat geschrieben werden soll.

'           Ist die Datei bereits vorhanden, wird sie überschrieben.

Dim WinHttpReq As Object

Dim RetVal As String

Dim oStream As Object

 

    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

    WinHttpReq.Open "GET", url, False

    WinHttpReq.send

    Do While WinHttpReq.ReadyState <> 4

        DoEvents

    Loop

    RetVal = WinHttpReq.responseBody

    On Error Resume Next

        Kill Filename

    On Error GoTo 0

    If WinHttpReq.Status = 200 Then

        Set oStream = CreateObject("ADODB.Stream")

        oStream.Open

        oStream.Type = 1

        oStream.Write WinHttpReq.responseBody

        oStream.SaveToFile Filename

        oStream.Close

    End If

    Set WinHttpReq = Nothing

End Sub

 

Wenn Sie nie programmiert haben, können Sie damit wenig anfangen. Andernfalls sehen Sie schnell, dass da keine Hexerei im Spiel ist.

Projekt 2: Wir fragen mehrere Aktienkurse ab

Mit dem Wissen aus Projekt 1 können wir nun eine etwas nützlichere Lösung erstellen: Wir möchten, dass Excel für uns gleich mehrere Aktienkurse abfragt und das Resultat in die passenden Zellen schreibt. Das soll etwa so aussehen:


 

Die Vorgehensweise ist gleich wie bei Projekt 1.

Hier enthält der URL mehrere Symbole, nämlich die Symbole aus Spalte A. Dazu werden vom Programm in einer Schlaufe die Symbole aus Spalte A aneinandergehängt, bis eine leere Zelle kommt. Dann wird der URL wieder an Yahoo übergeben, und schliesslich die Antwortdatei verarbeitet. Da die Antwortdatei nun Informationen zu mehreren Aktienkursen enthält, müssen die Werte mit einer Schlaufe daraus ausgelesen und in Array-Variablen zwischengespeichert werden. Zuletzt werden die Werte von den Array-Variablen mit einer weiteren Schlaufe in das Arbeitsblatt übertragen.

Sie können Projekt 2 hier herunterladen.

Jetzt können Sie die aktuellen Kurse aller Aktien, die Sie interessieren, mit einem Klick abfragen. Viel Spass!

Im nächsten Projekt werden wir ein einfaches Portfolio bauen, das die aktuelle Performance von Aktien automatisch berechnet. 

Kommentar(e)

Die Kommentarfunktion ist zurzeit wegen "Spammer-Besuchs" aus Russland ausgeschaltet