I'm trying to fetch data from this site using VBA in Excel. What I tried to do and what worked was using InternetExplorer object like this:
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.Navigate "http://zertifikate.finanztreff.de"
IE.document.getElementById("USFsecuritySearchDropDown").Value = "DE000BP5TBQ2"
IE.document.getElementById("USFsecuritySearchDropDownForm").submit
Do While IE.Busy Or IE.readyState <> 4 'wait until page is loaded
Application.Wait DateAdd("s", 1, Now)
Loop
MsgBox IE.document.getElementById("BP5TBQ~30~5").innerHTML
However this worked very slow and didn't get always the right results. I suspect that sometimes it didn't wait until webpage was loaded. I tried to look for answers and I found this answer on stackoverflow. Now I'm trying to figure out how to rewrite my macro using MSXML2 and MSHTML. So far I was able to do this :
Dim IE As MSXML2.XMLHTTP60
Set IE = New MSXML2.XMLHTTP60
IE.Open "GET", "http://zertifikate.finanztreff.de", False
IE.send
While IE.ReadyState <> 4
DoEvents
Wend
Dim HTMLDoc As MSHTML.HTMLDocument
Dim htmlBody As MSHTML.htmlBody
Set HTMLDoc = New MSHTML.HTMLDocument
Set htmlBody = HTMLDoc.body
htmlBody.innerHTML = IE.responseText
HTMLDoc.getElementById("USFsecuritySearchDropDown").Value = "DE000BP5TBQ2"
please, why HTMLDoc has method getElementById and htmlBody doesn't ? How could I submit form "USFsecuritySearchDropDownForm". I tried this :
HTMLDoc.getElementById("USFsecuritySearchDropDownForm").submit
, but it always open new window in my default browser, I would like to have it hidden. It seems to me that I am missing difference between XMLHTTP60 and MSHTML.HTMLDocument. If you could please help me or at least show me where I can find this information I would be really thankful...
XMLHTTP sends an http request to the webserver and receives back a response. MSHTML receives a string and renders the HTML. When you use them together, XMLHTTP gets the webserver response and MSHTML puts that response in a form you can use.
I think you don't need to submit anything. If you go to the site and type in the ticker, you get to a page like
That has the ticker in it. You can "GET" that URL directly and get whatever information you need from the html that's returned. This example gets what I assume is the stock price.
Sub GetPrice()
Dim xHttp As MSXML2.XMLHTTP
Dim hDoc As MSHTML.HTMLDocument
Dim hDiv As HTMLDivElement
Dim hTbl As HTMLTable
Const sTICKER As String = "DE000BP5TBQ2"
Set xHttp = New MSXML2.XMLHTTP
xHttp.Open "GET", "http://zertifikate.finanztreff.de/dvt_einzelkurs_uebersicht.htn?seite=zertifikate&i=22558284&suchbegriff=" & sTICKER & "&exitPoint="
xHttp.send
Do Until xHttp.readyState = 4
DoEvents
Loop
If xHttp.Status = 200 Then
Set hDoc = New MSHTML.HTMLDocument
hDoc.body.innerHTML = xHttp.responseText
'Get the third TD in the first TABLE in the first DIV whose class is 'tape'
Set hDiv = hDoc.getElementsByClassName("tape").Item(0)
Set hTbl = hDiv.getElementsByTagName("table").Item(0)
Debug.Print hTbl.getElementsByTagName("td").Item(2).innerText
End If
End Sub
Post Example
Sub GetPriceByPost()
Dim xHttp As MSXML2.XMLHTTP
Dim hDoc As MSHTML.HTMLDocument
Dim hDiv As HTMLDivElement
Dim hTbl As HTMLTable
Const sTICKER As String = "i=635957"
Set xHttp = New MSXML2.XMLHTTP
xHttp.Open "POST", "http://fonds.finanztreff.de/fonds_einzelkurs_uebersicht.htn"
xHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xHttp.send sTICKER
Do Until xHttp.readyState = 4
DoEvents
Loop
If xHttp.Status = 200 Then
Set hDoc = New MSHTML.HTMLDocument
hDoc.body.innerHTML = xHttp.responseText
'Get the third TD in the first TABLE in the first DIV whose class is 'tape'
Set hDiv = hDoc.getElementsByClassName("tape").Item(0)
Set hTbl = hDiv.getElementsByTagName("table").Item(0)
Debug.Print hTbl.getElementsByTagName("td").Item(2).innerText
Else
Debug.Print xHttp.statusText
End If
End Sub