Frage von hayla44, 21

Excel Web Query Crawler - Makro um aus Website Daten zu extrahieren?

Excel Web Query Crawler - Makro um aus Website Daten zu extrahieren?

Hallo liebe Community,

Kann mir netterweise jemand weiterhelfen bei folgendem Vorhaben in Microsoft Excel: Es geht um folgende Tabelle auf Website: http://de.investing.com/commodities/rohstoffe-futures

Ich habe derzeit in Excel in Spalte C diverse Rohstoffe stehen (die Namen sind identisch mit denen in der Tabelle von obiger Website) und in Spalte H habe ich die zugehörigen Kurse eingetragen.

Nun sollte das Makro idealerweise in einem Feld z.B. in A1 ein Button zeigen (Beschriftung "update"), das nachdem man raufdrückt, alle Kurse in Spalte H für die Rohstoffe in Spalte C mithilfe der obigen Website aktualisiert. Es müsste im Prinzip also die obige Website nach den Namen in Spalte C durchsuchen und zutreffende Kurse in Spalte H extrahieren.

Hat jemand eine effektive Lösung dafür? Vielen Dank schon mal für eure Ideen.

Expertenantwort
von Ninombre, Community-Experte für Excel, 4

Die Seite eignet sich vom Aufbau her nicht für eine Datenverbindung (Daten > Daten aus Web). Über den Quelltext die Daten auslesen ist möglich, ich finde so was aber immer etwas wackelig. Soll die Exceltabelle professionell verwendet werden? Dann wäre eine direkt abfragbare Quelle ratsam.

Nach dem Wort Gold suchen ist nicht eindeutig. In der Tabelle solltest Du noch eine Spalte aufnehmen, mit dem tatsächlichen Suchbegriff, mit dem sich der Höchstwert im Quelltext finden lässt. Die Seite verwendet immer eine ID je Rohstoff, der Tag für den Höchstpreis ist z.B. pid-8830-high für Gold.

Ich habe einen vorhandenen Code für Quelltextauslesen leicht angepasst. Das eigentlich "spannende" ist das Ausschneiden der richtigen Passage - die beiden Zeilen mit cells(i,3).value...
erst mal die Stelle finden mit pid-8830-high
dann das Ende der Zahl ermitteln.  Das geht vermutlich auch in einem Schritt, war mir aber jetzt zu aufwändig.
Da neben "normalem" Format auf der Seite auch 19,220 o.ä. als Zahl verwendet wird, noch runden auf 2 Nachkommastellen.

Was Du anpassen musst die die Zahlen jeweils bei cells(i,2) oder cells(i,3)
2 = Spalte 2 sprich B bzw. 3 -> C

cells(i,2) ist die Spalte mit den Suchbegriffen wie pid-8830-high
Cells(i,3) ist die Spalte, wohin der Preis geschrieben werden soll.

Sub lesen()
Set appIE = CreateObject("InternetExplorer.Application")
i = 2 'das ist die Zeile mit dem ersten Eintrag (ohne Überschriften also)
surl = "http://de.investing.com/commodities/rohstoffe-futures"
appIE.navigate surl
Do: Loop Until appIE.Busy = False
Do: Loop Until appIE.Busy = False
stxt = appIE.document.DocumentElement.outerHTML
While Cells(i, 2).Value <> ""
Cells(i, 3).NumberFormat = "#,##0.00"
Cells(i, 3).Value = Mid(stxt, InStr(stxt, Cells(i, 2).Value) + Len(Cells(i, 2).Value) + 2, 10)
Cells(i, 3).Value = Round(Left(Cells(i, 3).Value, InStr(1, Cells(i, 3).Value, "<") - 1), 2)
i = i + 1
Wend
Set appIE = Nothing
Close
End Sub

Die Suchbegriff hab ich noch extrahiert. Es ist die Standardreihenfolge beim Aufrufen der Seite.

pid-8830-high
pid-68-high
pid-8836-high
pid-8831-high
pid-8910-high
pid-8883-high
pid-8849-high
pid-8833-high
pid-8862-high
pid-8988-high
pid-954867-high
pid-8861-high
pid-49768-high
pid-956470-high
pid-959207-high
pid-959208-high
pid-959211-high
pid-959209-high
pid-8917-high
pid-13916-high
pid-8918-high
pid-8916-high
pid-8915-high
pid-8919-high
pid-8851-high
pid-8894-high
pid-8832-high
pid-8869-high
pid-8891-high
pid-8914-high
pid-8913-high
pid-961618-high
pid-959198-high
pid-959199-high

Kommentar von hayla44 ,

Hi Ninombre! Wow, hat super funktioniert mit deinem Beispiel, danke sehr!

Aber trotzdem bleibt leider noch meine Originalfrage: Wie kann ich die Kurse erhalten, wenn ich nicht "pid-959199-high" usw in der Suchspalte (z.B. B) schreibe , sondern als Suchbegriffe die Namen wie Gold etc. nutzen möchte? Denn ansprechend sieht das zum präsentieren nicht aus wenn ich den exakten Linknamen wie pid-3434334 eintrage, weiss niemand was dahintersteckt.

Es interessiert mich sehr, gibt es eine clevere Methode, den Makro so zu stellen, dass er nach allen Namen in meiner Spalte B sucht, also z.b. nach "Platinum" etc. (die ja auch eindeutig sind, kommen nur ein mal vor in der web tabelle) und dann einfach 3 schritte nach rechts zum nächsten Textblock/Spalte wandert um dortige Information zu extrahieren (also der "Hoch"-Kurs wäre 3 spalten weiter rechts )?

Oder andersrum, dass der makro nach der "Hoch" Spalte sucht (wort "hoch" kommt nur einmal vor dort) und dann alle werte dadrunter durchliest und immer die extrahiert, die links zu einem Namen gehören, das auch in meiner Excel Spalte vorkommt...

Gibts da irgendwelche Lösung? Danke dir schon mal für dein Rat!

Kommentar von Ninombre ,

Ich denke es liegt ein falsches Verständnis vom Aufbau des Quelltexts vor. Das ist in der Anzeige zwar wie eine Exceltabelle, man kann aber nicht einfach über die Spalten/Zeilen zugreifen, als ob man direkt in Excel wäre

Das ist nur ein Schnippsel daraus, die Zeile mit
den Werten von Gold. Das wird im Browser zur Tabellenanzeige umgesetzt, in Excel hat man nur diesen Text zum Auslesen

<td class="left noWrap">Dez. 2016 </td>
<td class="pid-8830-last">1.317,45</td>
<td class="pid-8830-high" >1.321,35</td>
<td class="pid-8830-low" >1.316,45</td>
<td class="bold redFont pid-8830-pc" >-0,55</td>
<td class="bold redFont pid-8830-pcp" >-0,04%</td>
<td class="pid-8830-time" data-value="1474002611" >07:10:11</td>
<td class="icon"><span class="greenClockIcon">&nbsp;</span></td>

Ein eindeutiger Schlüssel ist also erforderlich, die Rohstoffnamen selbst tauchen im Quelltext häufiger auf als an der Oberfläche ggf. sichtbar. Du kannst die Suchbegriffe auch erst im Makro ermitteln, also über den Rohstoff die ID zuordnen.Das ist dann etwas Handarbeit, zwei Beispiele habe ich eingetragen. Auf die Art kann man individuell auch die Anzahl der Nachkommastellen festlegen. Das fettgedruckte ist je Rohstoff zu wiederholen:

Sub lesen()
Set appIE = CreateObject("InternetExplorer.Application")
i = 2 'das ist die Zeile mit dem ersten Eintrag (ohne Überschriften also)
surl = "http://de.investing.com/commodities/rohstoffe-futures"
appIE.navigate surl
Do: Loop Until appIE.Busy = False
Do: Loop Until appIE.Busy = False
stxt = appIE.document.DocumentElement.outerHTML

While Cells(i, 1).Value <> "" 'annahme: Rohstoffname steht in Spalte A
Select Case Cells(i, 1).Value
Case "Gold"
suchid = "pid-8830-high"
Cells(i, 3).NumberFormat = "#,##0.00"
Case "Silber"
suchid = "pid-8836-high"
Cells(i, 3).NumberFormat = "#,##0.000"

End Select

Cells(i, 3).Value = Mid(stxt, InStr(stxt, suchid) + Len(suchid) + 2, 10)
Cells(i, 3).Value = Round(Left(Cells(i, 3).Value, InStr(1, Cells(i, 3).Value, "<") - 1), 2)
i = i + 1
Wend
Set appIE = Nothing
Close
End Sub

Eine einfachere Lösung wäre mit dem ursprünglichen Skript möglich, wenn Du die Spalte mit den ID einfach irgendwie hinpackst, wo man sie nicht sieht. Die kann auch ganz ausgeblendet werden. Dann musst nur die Spaltenangabe 2 anpassen, für Z bspw. 26

Cells(i, 2).Value
Kommentar von hayla44 ,

Hi , danke nochmals! Das probiere ich noch aus... aber ich habe heute von anderer stelle ein Tipp erhalten, das wohl beste und effektivste mittel für meine zwecke (für unstrukturierte websites) ist die "dom"  methode "nexxtsibling" etc... sagt das dir etwas?

also scheinbar gibts für excel doch eine methode, wo man den makro so programmieren kann wie praktisch ein sverweis oder ähnliches... also statt konkrete "pid-8908" etc. findet das makro in der tabelle (table quell code) der website  z.B. "gold" und bleibt in der zeile von gold und geht z.B. nach rechts weiter bis es zum "hoch" kurs in der zeile ankommt... irgendwie so, kennst du da ein effektives makro ? Super vielen Dank schon mal.

Kommentar von Ninombre ,

sorry, aber ich hab mit html nicht wirklich viel zu tun. Wenn ich nach DOM google kommt da schon was, das hat aber nicht direkt was mit Excel zu tun. Wenn Du was passendes gefunden hast, aber um so besser.

Keine passende Antwort gefunden?

Fragen Sie die Community