Historische Aktienkurse direkt aus dem Web nach Excel importieren

In einem vorherigen Artikel bin ich bereits darauf eingegangen, wie die Yahoo Finance API aufgebaut ist und welche Daten von Yahoo Finance verfügbar sind. In diesem Artikel möchte ich einmal konkret darauf eingehen, wie wir historische Kursdaten für einen festgelegten Zeitraum direkt nach Excel importieren und mit einem Vergleichswert (z.B. einem Benchmark-Index) vergleichen können.

---

Was du in diesem Artikel lernst

• Wie du ein einfaches Makro aufzeichnest
• Wie du die Yahoo Finance Daten mithilfe einer Webabfrage nach Excel importierst
• Wie du die Webabfrage automatisierst
• Wie du einen “Aktualisieren”-Button einfügst und mit dem Makro verknüpft

Vorab schonmal ein kleiner Hinweis: Ich bin ein kompletter VBA-Neuling. Es gibt also ggf. noch alternative oder auch bessere Lösungen, die Kursdaten in Excel zu importieren. Mir kam allerdings nach ein paar Recherchen dieser Weg am einfachsten und besten vor.

---

Historische Aktienkurse nach Excel importieren

Bevor ich in die Details einsteige, hier ein kleiner Screenshot des Ergebnisses, welches wir am Ende dieser Anleitung erreichen möchten:

Wir möchten im Wesentlichen die Kursverläufe einer beliebigen Aktie (hier Apple – AAPL) für einen von uns festgelegten Zeitraum (hier vom 14.5.2006 bis zum 14.5.2016, also für 10 Jahre) mit einem relevanten Index (hier der Nasdaq – ^IXIC) vergleichen.

Um das umzusetzen, müssen wir nacheinander folgende Schritte durchführen:

1. In Excel die Entwicklertools im Menüband sichtbar machen
2. Die erste Webabfrage als Makro aufzeichnen
3. Die Webabfrage automatisieren, d.h. so ergänzen, dass wir Ticker, Vergleichswert, Anfangs- und Enddatum direkt in Excel eingeben können
4. Die Abfrage über einen Button aktualisieren lassen
5. Für Aktie und Vergleichswert jeweils den Index ausrechnen und grafisch darstellen

[stextbox id=”diy_2_blau”]Update März 2018

Yahoo Finance hat kürzlich die API für den Download historischer Daten angepasst. Ein automatisierter Download nach Excel ist nun nicht mehr ohne Weiteres möglich.

Ihr könnt stattdessen aber die Daten von Google Finance (nur noch eingeschränkt nutzbar) bzw. Alpha Vantage in euer Excel ziehen. Da die Methodik aber mehr oder weniger identisch zum hier beschriebenen Ansatz ist, solltet ihr diesen Post auf jeden Fall zunächst zu Ende lesen.

Der Download von Echtzeitdaten über Yahoo Finance funktioniert übrigens leider auch nicht mehr.[/su_note]

---

1. Den Reiter “Entwicklertools” sichtbar machen

In der Standardkonfiguation von Excel ist die Menüleiste mit den Entwicklertools im Menüband nicht sichtbar. Wir benötigen allerdings diese Menüleiste, um unser Makro aufzuzeichnen und das Makro in Visual Basic zu bearbeiten. Um nun das Menüband “Entwicklertools” (oder in der englischen Version “Developer”) in Excel oben im Menüband sichtbar zu machen, müssen wir dieses zunächst einmal aktivieren.

Dazu gehen wir zunächst auf die Registrierkarte Datei und klicken dann auf Optionen. Über die Auswahl Menüband anpassen (Customize Ribbon) erhalten wir dann folgende Ansicht:

Wir müssen nun in der entsprechenden Hauptregisterkarte das Kontrollkästchen für die Entwicklertools (Developer) aktivieren und mit OK bestätigen.

Wir sollten nun oben in der Menüleiste den Reiter für die Entwicklertools / Developertools sehen:

---

2. Webabfrage per Makro aufzeichnen

Um die Webabfrage aufzuzeichnen, gehen wir nun direkt in das Menüband Entwicklertools und klicken dort auf Makro aufzeichnen:

Anschließend müssen wir dem Makro noch einen Namen geben (in diesem Fall Kurs_1). Sobald wir mit OK bestätigen, läuft die Aufzeichnung des Makros.

Um nun die Webabfrage aufzuzeichnen, gehen wir in die Registrierkarte Daten und wählen dort Aus Text aus.

Es öffnet sich dann folgendes Fenster:

Es sieht nun zunächst so aus, als müssten wir ein Dokument von unserem Computer öffnen. Dem ist aber nicht so. Wir können hier auch direkt eine URL eintragen, die ein Textdokument (also zB. eine .csv-Datei) auf unseren Computer lädt.

Wie im Artikel zur Yahoo Finance API bereits beschrieben, lautet die URL für die historischen Kursdaten folgendermaßen:

http://real-chart.finance.yahoo.com/table.csv?s=AAPL&d=4&e=10&f=2016&g=d&a=11&b=12&c=1980&ignore=.csv

wobei

• s=AAPL – Das Tickersymbol des Unternehmens, für das wir die Daten benötigen
• d=4, e=10, f=2016 – Enddatum (Monat, Tag und Jahr) der Zeitreihe, die wir herunterladen möchten. In diesem Fall endet die Zeitreihe am 10. Mai 2016
• a=11, b=12, c=1980 – Anfangsdatum (Monat, Tag, Jahr) der Zeitreihe. In diesem Fall beginnt die Zeitreihe am 12. Dezember 1980

Wir kümmern uns erstmal nicht um das Unternehmen bzw. das Anfangs- und Enddatum, sondern importieren zunächst mal die Daten so, wie sie auf der Yahoo Finance Seite für Apple zur Verfügung gestellt werden:

Als nächstes öffnet sich folgendes Fenster:

Wir legen nun fest, wie genau wir die Textdaten (also die .csv Daten) importieren möchten. Wir sehen schon an der kleinen Vorschau des .csv Dokuments, dass wir nun eine Textdatei mit den Spalten Datum, Eröffnungskurs, Tageshoch, Tagestief, Schlusskurs, Handelsvolumen und angepasster Schlusskurs erhalten. Die einzelnen Kurse sind wie wir sehen können durch ein Komma getrennt und in der englischen bzw. US-amerikanischen Schreibweise dargestellt (d.h. das Komma zwischen Euro- (oder Dollar-) und Cent-Beträgen wird hier als Punkt dargestellt).

Wichtig ist noch, dass oben Delimited ausgewählt ist. Das bedeutet im Wesentlichen, dass die Daten der verschiedenen Felder (in Excel dann später Spalten) durch Kommas o.Ä. getrennt sind. Wir klicken anschließend auf Weiter.

Im nächsten Schritt müssen wir dann auswählen, durch welches Trennzeichen die Daten getrennt werden. Wie wir bereits gesehen haben, werden die Daten von Yahoo Finance hier durch ein Komma getrennt. Wir wählen also entsprechend Komma aus und klicken auf Weiter.

Wir sehen nun bereits, wie die Daten in formatierter Form in unserem Excel-Tabellenblatt später aussehen werden. Nun können wir noch bestimmen, in welchem Format wir die Daten importieren möchten bzw. ob wir bestimmte Spalten überhaupt importieren möchten:

In diesem Fall möchten wir gerne das Datum sowie den angepassten Schlusskurs importieren.

Für das Datum wählen wir als Datenformat Datum aus. Für die Spalten Open, High, Low, Close und Volume wählen wir Spalten nicht importieren aus. Die Spalte Adj. Close belassen wir, wie sie ist. Die in schwarz hervorgehobene Spalte ist jeweils die aktive Spalte.

Nach dem Klick auf Fertigstellen müssen wir nur noch festlegen, an welcher Stelle in unserem Excel-Tabellenblatt wir die Daten einfügen möchten. Wir wählen hier die Zelle A9 aus, damit wir weiter später oben noch Platz für die Eingabefelder für Ticker und Anfangs- und Enddatum haben. Wichtig ist auch, dass wir die Zelle recht genau festlegen, also auch angeben, in welchem Tabellenblatt die Daten eingefügt werden sollen (in diesem Fall in Tabellenblatt 1 bzw. Sheet 1). Ansonsten kann es uns später passieren, dass Daten in anderen Tabellenblättern überschrieben werden.

Mit dem Klick auf OK werden die Daten in Excel eingefügt (siehe Screenshot unten). Wir haben nun die Webabfrage abgeschlossen und können auch die Aufzeichnung des Makros beenden. Dazu gehen wir zurück auf den Reiter Entwicklertools und klicken auf Aufzeichnung beenden:

Wie wir sehen können, wurden die Daten inzwischen in unser Tabellenblatt eingefügt. Als nächstes schauen wir uns mal das Makro an, dass Excel nun aufgezeichnet hat.

---

3. Die Webabfrage automatisieren

Dafür gehen wir wieder in die Registrierkarte Entwicklertools und wählen den Menüpunkt Makros aus. Unser aufgezeichnetes Makro heißt Kurs_1. Wir wählen dieses aus klicken auf Bearbeiten bzw. Edit. Daraufhin öffnet sich der so genannte VBA-Editor, in dem wir den Visual Basic Code unseres aufgezeichneten Makros ansehen können (der Code passt gerade noch auf einen Screen):

Wie wir sehen, befindet sich recht weit oben im Code die URL zu unserem .csv Dokument. Wir sehen auch, dass die Abfrage den Kurs für den Ticker AAPL (also die Apple Aktie) für die vorher in der URL angegebenen Anfangs- und Enddaten importiert.

Wir müssen also im nächsten Schritt den Ticker AAPL durch eine Variable ersetzen und diese mir unserem Excel-Tabellenblatt verknüpfen (damit immer die Daten heruntergeladen werden, die wir für unsere Analyse gerne haben möchten).

Wir definieren also zunächst mal in unserem Excel-Tabellenblatt die Zellen für Ticker, Anfangsdatum und Enddatum:

Der Ticker befindet sich also nun in Zelle B4, das Enddatum in Zelle B5 und das Anfangsdatum in Zelle B7. Als Enddatum habe ich in diesem Fall über die Formel heute() bzw. today() das heutige Datum gewählt, als Anfangsdatum habe ich den gleichen Tag vor 10 Jahren gewählt.

Nun müssen wir eigentlich nur noch den Code entsprechend anpassen, damit das Makro direkt die von uns in Excel festgelegten Ticker etc. verwendet. Dazu gehen wir wieder zurück in den Code und ergänzen ihn an einigen Stellen:

Zunächst mal sollen natürlich alle alten Daten erstmal gelöscht werden, bevor wir neue Daten aus dem Netz ziehen. Dafür fügen wir ganz oben den Befehl Range(“A9:C5000”).ClearContents hinzu. Dieser löscht zu Beginn der Abfrage alle Daten im Feld A9:C5000. Bei längeren Zeitreihen als 10 bis 15 Jahren müssten wir hier dann anstelle der 5000 einen höheren Wert ansetzen.

Anschließend müssen wir analog zu den Daten in der URL entsprechende Variablen festlegen. Wir definieren die Variable ticker als String (also als Text) und die Variablen sday, smonth, syear, eday, emonth, eyear als Long (also als rationale Zahl). Wir benötigen jeweils eine separate Variable für den Tag, den Monat und das Jahr, weil dies in der URL getrennt abgefragt wird.

Als nächstes sagen wir dem Programm noch, aus welchen Zellen in unserem Tabellenblatt sich das Makro die Werte für die Variablen ziehen soll. Z.B. finden wir den Ticker in der Zelle B4, schreiben also ticker = Range (“B4”). Für das Anfangs- und Enddatum müssen wir jeweils Tag, Monat und Jahr getrennt aus der Zelle B5 bzw. B7 auslesen. Deshalb schreiben wir z.B. für das Jahr des Anfangsdatums syear = Year(Range (“B7”)). Beim Monat gibt es bei Yahoo eine kleine Besonderheit: Die Monate werden von 0 bis 11  und nicht von 1 bis 12 durchnummeriert. Dem entsprechend müssen wir die Monatsangaben, die wir den Zellen B5 und B7 entnehmen noch um 1 reduzieren.

Abschließend müssen wir noch dafür sorgen, dass die URL nicht mehr immer auf den Ticker von Apple (AAPL) zurückgreift, sondern stattdessen unsere definierte Variable names ticker nutzt. Und für die Datumsangaben analog. Um dies zu bewerkstelligen, ersetzen wir in der URL einfach AAPL durch folgendes Konstrukt:
“& ticker &”.

Beim Importieren unseres Vergleichswerts können wir analog vorgehen (also ein neues Makro aufzeichnen und später bei Klick auf den Button ausführen lassen) oder auch einfach nur unseren Code kopieren und leicht anpassen. Ich habe mich hier dafür entschieden, einfach das gleiche Makro zu nutzen und den Code zu kopieren. Im Wesentlichen müssen wir folgende Schritte abarbeiten:

• Unseren derzeitigen Code (ohne die Definition der Variablen etc.) kopieren und unten einfügen (allerdings innerhalb des Makros, also vor dem End Sub Befehl
• Eine zusätzliche Variable für den Ticker des Vergleichswerts definieren (z.B. tickerbm)
• Eine weitere Zelle in Excel festlegen und übergeben, die den Ticker für den Vergleichswert enthält (z.B. Zelle C4)
• Die Zelle anpassen, in der die Zeitreihe eingefügt werden soll (nämlich neben der bereits existierenden) und dafür sorgen, dass das Datum nicht nochmal übertragen wird

Um sicherzustellen, dass das Datum nicht nochmal übertragen wird, können wir einfach den Befehl
.TextFileColumnDataTypes = Array (4, 9, 9, 9, 9, 9, 1) modifizieren. Die 9 steht hier für Spalten, die nicht übertragen werden. Insofern ersetzen wir einfach die erste Zahl (kann 3 oder 4 oder auch eine andere Zahl sein, je nachdem welches Datumsformat wir ursprünglich gewählt hatten) einfach durch eine 9.

---

4. Einen “Aktualisieren”-Button einfügen und mit dem Makro verknüpfen

Wenn wir das Dokument nun als .xlsm Dokument abspeichern, sind wir eigentlich im Wesentlichen fertig. Jetzt ist einzig und allein das Aktualisieren unserer Abfrage etwas schwierig bzw. umständlich. Deshalb fügen wir zum Abschluss noch einen Button ein, mit dessen Hilfe wir die Abfrage auf Knopfdruck aktualisieren können.

Zum Einfügen eines Buttons gehen wir wieder auf die Entwicklertools und dann auf Einfügen. Wir wählen das obere linke Element unter ActiveX Controls aus, genannt CommandButton.

Wir können dann den Button in unserem Tabellenblatt flexibel platzieren.

Mit einem Rechtsklick auf den Button öffnet sich dann folgendes Dialogfeld:

Hier können wir nun das Design des Buttons anpassen (über die Auswahl der Hintergrundfarbe und der Textfarbe) sowie auch den Namen des Buttons (Name) sowie die Beschriftung (Caption) ändern. Wir nennen den Button einfach btn_akt und wählen als Beschriftung “Aktualisieren”.

Nun müssen wir dem Programm nur noch sagen, dass bei einem Klick auf den Button unser Makro Kurs_1 ausgeführt werden soll. Dafür machen wir einfach einen Doppelklick auf den Button, wodurch wir in den VBA-Code des Buttons gelangen:

Hier tragen wir einfach den Namen unseres Makros Kurs_1 ein und speichern das Makro ab. Somit sollte auch der Button funktionieren und bei Eingabe eines anderen Tickers (z.B. Daimler – DAI.DE) den entsprechenden Kurs ausgeben.

Es könnte sein, dass der Code noch eine Zeile .CommandType = 0 enthält und diese zu einem Runtime-Error führt. Diese Zeile kann dann einfach gelöscht werden.

---

5. Den Index ausrechnen und grafisch darstellen

Die Berechnung des Index für unseren Wert sowie den Vergleichswert sowie die grafische Darstellung erläutere ich hier nicht im Detail, weil ich glaube, dass sowieso jeder seine eigenen Auswertungen mit den Kursen machen möchte.

Solltest du trotzdem die Auswertung genauso nutzen wollen, wie oben dargestellt, dann kannst du das Excel-File inklusive Makro hier herunterladen:

Hier herunterladen

Lass mich außerdem sehr gerne wissen, falls du Anregungen bzw. Verbesserungsvorschläge für die DIY Tools hast.

Wenn du Excel 2013 installiert hast, dann solltest du dir außerdem diesen Artikel einmal ansehen (es geht nämlich noch einfacher): Finanzdaten von Yahoo Finance in Excel 2013