VBA - Extract info from Webpage

[tttommy2]

Hi there

I have a spreadsheet with a stockticker in A2. Lets say for example "NKE" which is Nike's stock ticker.

I want some code which will go to the stockticker page on www.earningswhispers.com. So in my example this would be:

https://www.earningswhispers.com/stocks/nke

And then I would like to extract extract from this page the given date("Jun 29") to B2, and given time ("4:15 PM ET") to C2.

I have played about with Data Web Query, however I cant get anything to work.

Thanks

T

 

[Sektor]

Sub FetchWebData()
    Dim doc, req
    Set req = CreateObject("MSXML2.XMLHTTP")
    Set doc = CreateObject("HTMLFile")
    req.Open "GET", "https://www.earningswhispers.com/stocks/" & [A2], False
    req.send
    doc.body.innerHTML = req.responseText
    [B2] = doc.getElementById("datebox").ChildNodes(2).innerHTML
    [C2] = doc.getElementById("earningstime").innerHTML
    doc.Close
End Sub

 

[tttommy2]

Priviet Sektor.

Thats great.

In https://www.earningswhispers.com/stocks/nke, just to right above the earnings date there is a correct mark: "✔" which indicates that the earnings date is confirmed. If the earnings date was unconfirmed it would be a grey circle with white "-" in middle. (eg. https://www.earningswhispers.com/stocks/amzn)

Could I extract that into D2 by means of maybe 1 or 0; "confrmed" or "unconfirmed"; or maybe TRUE or FALSE?

I tried adding code: "[D2] = doc.getElementById("epsconfirmed").innerHTML" to your code however it didnt work.

Bolshoe spasibo

T

Sub FetchWebData()
    Dim doc, req
    Set req = CreateObject("MSXML2.XMLHTTP")
    Set doc = CreateObject("HTMLFile")
    req.Open "GET", "https://www.earningswhispers.com/stocks/" & [A2], False
    req.send
    doc.body.innerHTML = req.responseText
    [B2] = doc.getElementById("datebox").ChildNodes(2).innerHTML
    [C2] = doc.getElementById("earningstime").innerHTML
    doc.Close
End Sub

 

[Domenic]

Try...

    If InStr(1, doc.getElementById("epsconfirmed").getAttribute("classname"), "icon-checkmark", vbTextCompare) > 0 Then
        [D2] = 1
    Else
        [D2] = 0
    End If

Hope this helps!

 

[tttommy2]

Thanks Domenic. That works great.

T

Try...

    If InStr(1, doc.getElementById("epsconfirmed").getAttribute("classname"), "icon-checkmark", vbTextCompare) > 0 Then
        [D2] = 1
    Else
        [D2] = 0
    End If

Hope this helps!

 

[tttommy2]

Sektor's VBA code has worked perfectly for last 4 years until yesterday. I think the HTML code must have changed. I am not really a programmer and my efoorts over last few hours to find a workaround have come to nothing.

Can anyone help me to extract from page for example "www.earningswhispers.com/stocks/nke" the given date("Jun 17")?

Sektor's original code - 3rd line from end "[B2] = doc.getElementById("datebox").ChildNodes(2).innerHTML" no longer works.
The rest is fine

Sub FetchWebData()
    Dim doc, req
    Set req = CreateObject("MSXML2.XMLHTTP")
    Set doc = CreateObject("HTMLFile")
    req.Open "GET", "https://www.earningswhispers.com/stocks/" & [A2], False
    req.send
    doc.body.innerHTML = req.responseText
    [B2] = doc.getElementById("datebox").ChildNodes(2).innerHTML
    [C2] = doc.getElementById("earningstime").innerHTML
    doc.Close
End Sub

 

[Marc L]

​

This code can't work anymore as the data does not exist in the request responseText as it is !​

So you must investigate with your webbrowser inspector tool which request headers are now necessary​

or just leave the request in favour to pilot the webpage under IE …​

 

[Domenic]

Try...

    [B2] = doc.getElementById("datebox").Children(2).innerHTML

Hope this helps!

 

[Marc L]

​

Domenic, do you check the content of responseText ?​

 

[tttommy2]

Thanks Domenic. That works great!

VBA Code:
    [B2] = doc.getElementById("datebox").Children(2).innerHTML