HTML-Formular einer Webseite durch VBA-Makro nutzen

Die hier vorgestellte Lösung resultiert aus folgender Aufgabenstellung: Ich habe eine Aktie bzw. deren ISIN und möchte dazu die URL bei einem bestimmten Finanzportal herausfinden. Das ganze soll automatisiert geschehen, denn es handelt sich nicht nur um eine, sondern um eine lange Liste von Aktien.

Diese URLs sind zum Beispiel hilfreich, um Tools zu bauen, mit denen man automatisch eine Menge Kennzahlen abrufen kann, die sich dann wiederum für alle nur denkbaren Investment-Strategien auswerten lassen.

Wie so etwas geht, habe ich in diesem Artikel beschrieben: Daten aus dem Internet automatisch in ein Excel-Sheet übertragen.

Was ich hier beschreibe, funktioniert zwar nicht für alle Finanzseiten, aber für einige. Ich zeige das am Beispiel von Ariva (http://www.ariva.de). So in etwa erscheint die Seite mit der Suchmöglichkeit nach Name, WKN oder ISIN oben rechts.

ariva.de

Wenn wir in das Feld nun eine ISIN eingeben und suchen, landen wir direkt auf der Seite der zugehörigen Aktie, denn die ISIN ist eine eindeutige Kennzahl. Nehmen wir zum Beispiel die Adidas-Aktie. Die hat die ISIN DE000A1EWWW0. Wenn wir danach suchen, erscheint die passende Seite. Dort gibt es z.B. einen Link „Kennzahlen“. Klickt man den an, steht oben in der URL-Zeile genau das, was wir suchen, in unserem Beispiel: http://www.ariva.de/adidas-aktie/bilanz-guv.

Vorarbeit: Analysieren der Web-Seite

Wie können wir das nun automatisch ablaufen lassen, so dass es dann auch auf eine ganze Liste von ISINs, die in einem Excel-Sheet stehen, anwendbar ist?

Dazu müssen wir in Erfahrung bringen, wie das Suchfeld funktioniert und ob es für unsere Zwecke geeignet ist. So ein Suchfeld befindet sich innerhalb eines HTML-Formulars. Wir suchen dieses Formular nun im HTML-Quelltext der Internetseite heraus, um festzustellen, ob und wie wir es benutzen (ansprechen) können.

Dazu rufen wir im Browser mit der Seite die Entwicklerwerkzeuge auf. Das geht meistens mit F12. Es erscheint etwas derartiges mit einem „Inspektor“ oder „DOM-Explorer“, worin der HTML-Quelltext zu sehen ist.

HTML-Inspektor

Wir durchsuchen den HTML-Code nun nach „ISIN“, um herauszufinden, wo im Code sich das Eingabefeld für die Suche und damit auch das Such-Formular befindet, und springen dadurch an die passende Stelle. Das sieht in etwa so aus:

HTML-Formular

Nun können wir ablesen, ob und wie wir mit unserer Automatisierung vorgehen können. Ich gebe hier den Code-Ausschnitt wieder, der für uns interessant ist: Ich lasse dabei alles weg, was für uns irrelevant ist, bzw. ersetze es durch Punkte:

<form ... action="/search/search.m" method="get" ... >
   <input ... name="searchname" placeholder="Name, WKN, ISIN" ... type="text">
   <input ... type="submit">
   <input name="go" value="1" type="hidden">
</form>

Zunächst einmal sehen wir, dass dieser <form>-Code unserem gesuchten Formular entspricht, denn das Feld, in dem „Name, WKN, ISIN“ steht, befindet sich darin, also innerhalb der Tags <form> und </form>.

Das Formular hat den Parameter method="get". Das ist die Methode, wie die Daten aus den Eingabefeldern des Formulars zur weiteren Verarbeitung übergeben werden. method="get" besagt, dass das über die URL geschieht, durch Anhängen von Parametern. Das ist genau das, was wir brauchen, weil wir so etwas sehr gut in einer Excel-Web-Abfrage durchführen können. Im Parameter action="/search/search.m" steht die URL. Sie ist hier relativ zur Domain angegeben, lautet also vollständig: http://www.ariva.de/search/search.m

Die Daten aus den Formularfeldern werden als Parameter an die URL angehängt. Das passiert mit vorangestelltem ?. Dann folgen die Angaben in der Gestalt feldname=feldwert. Mehrere werden durch ein & getrennt. Felder sind alle Elemente mit dem Tag <input>, die den Parameter type="text" oder type="hidden" haben. Von diesen interessieren uns jeweils die Parameter name und value.

Das Suchfeld hat name="searchname". Als value wird das übergeben, was wir ins Feld eintragen, in unserem Fall also eine ISIN. Weiterhin gibt es noch ein verstecktes Feld mit name="go" und value="1". Keine Ahnung, ob das wichtig ist bzw. wofür das ist. Das weiß derjenige, der das programmiert hat. Die URL zum Suchen der Seite mit der ISIN DE000A1EWWW0, also der Adidas-Aktie würde wie folgt aussehen:

http://www.ariva.de/search/search.m?searchname=DE000A1EWWW0&go=1

Wir können das ausprobieren, indem wir es einfach oben in die Adresszeile des Browsers eingeben bzw. hier auf den Link klicken. Wir landen tatsächlich direkt auf der Seite mit der Adidas-Aktie. Wir können auch ausprobieren, dass der letzte Parameter &go=1 dabei keine Rolle spielt und einfach weggelassen werden kann.

Bau der Excel-Lösung

Nun können wir eine Excel-Lösung bauen, die zu einer Liste von ISINs jeweils eine Web-Abfrage mit entsprechender URL durchführt und dann ausliest, was wir wissen wollen.

Beginnen wir mit einem Tabellenblatt, auf dem die Aktien mit ihren ISINs in Spalte A bzw. Spalte B stehen. In Spalte C soll dann die gesuchte Ariva-URL landen. Wir nennen das Blatt „Aktien“. So könnte es aussehen:

Tabellenblatt Aktien

Weiterhin brauchen wir ein Tabellenblatt, das wir „Web“ nennen. Das soll als Hilfsblatt für die Web-Abfragen dienen.

So können wir nun ein Makro schreiben:

Option Explicit
Sub ArivaURLs()

   Dim shW As Worksheet, qt As QueryTable, urlAnfang As String
   Dim shA As Worksheet, zeile As Integer, isin As String
   Dim rng As Range, k As Integer

   'Web-Abfrage vorbereiten:
   Set shW = Sheets("Web")
   shW.Cells.Clear
   Set qt = shW.QueryTables.Add("URL;", shW.Cells(1, 1))
   With qt
      .BackgroundQuery = False
      .RefreshStyle = xlInsertDeleteCells
      .WebSelectionType = xlEntirePage
      .WebFormatting = xlWebFormattingAll
      .WebDisableDateRecognition = True
   End With

   'an diese URL wird die ISIN angehängt:
   urlAnfang = "http://www.ariva.de/search/search.m?searchname="

   'die Liste der Aktien wird durchgegangen:
   Set shA = Sheets("Aktien")
   zeile = 2
   Do Until shA.Cells(zeile, 1).Value = ""

      'Fortschrittsanzeige:
      Application.StatusBar = "Verarbeite Zeile " & zeile
      DoEvents

      'Web-Abfrage ausführen:
      isin = shA.Cells(zeile, 2).Value
      With qt
         .Connection = "URL;" & urlAnfang & isin
         .Refresh (False)
      End With

      'Auswerten: Wohin führt der Link "Kennzahlen"?
      Set rng = shW.Cells.Find("Kennzahlen", shW.Cells(1, 1))
      If Not rng Is Nothing Then
         If rng.Hyperlinks.Count > 0 Then
            'Das ist die gesuchte URL:
            shA.Cells(zeile, 3).Value = rng.Hyperlinks(1).Address
            DoEvents
         End If
      End If

      zeile = zeile + 1
   Loop

   'Aufräumen und fertig:
   shW.Cells.Clear
   For k = shW.QueryTables.Count To 1 Step -1
      shW.QueryTables(k).Delete
   Next
   Application.StatusBar = "OK"

End Sub

Was hier besonders zu beachten ist, ist die Eigenschaft qt.WebFormatting = xlWebFormattingAll, so dass dann auch Hyperlinks verfügbar sind. Um zu sehen, was auf dem Tabellenblatt „Web“ beim Ausführen der Web-Abfrage ankommt, kann man im Debugger gleich nach dem Ausführen der Abfrage anhalten. So erhält man einen Eindruck, warum der Code so gebaut wurde.

Nachdem das Makro gelaufen ist, sieht es in der Tabelle etwa so aus:

Tabellenblatt Aktien

Statt der gesamten URL könnte man natürlich auch nur jeweils den spezifischen Teil in die Tabelle schreiben, ja nachdem was man benötigt.

Download: Beispiel-Datei
Beispiel-Datei: ariva-urls-holen.xslm 20 KB

Weitere Erläuterungen zum Thema Web-Abfragen in Excel findet man hier: Daten aus dem Internet automatisch in ein Excel-Sheet übertragen. Dort gibt es zusätzlich ein PDF-E-Book, dass noch detaillierter auf das Thema eingeht.

Wichtiger Nachtrag Mai 2017

Wenn durch die Do-Until-Schleife aus dem obigen Code-Beispiel (siehe auch Beispiel-Datei) eine sehr lange Liste abgearbeitet wird, lässt die Performance immer mehr nach, je länger das Ganze läuft. Dafür habe ich eine Lösung gefunden, die darin besteht, die QueryTable vor jedem neuen URL-Abruf zu entsorgen und neu zu initialisieren. Genauer erklärt ist das in diesem Artikel: Bessere Performance bei vielen Web-Abfragen hintereinander

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.