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

 

[Marc L]

​

That's so weird as yesterday when I have checked out the responseText it was 'empty' and today it works !​

​

So my post #7 is wrong, sorry guys …​

 

[Marc L]

As no issue today another way according to each element ID / Class​

(on VBE side the reference Microsoft HTML Object Library must be activated) :​

Sub DemoReq1()
         [B2:D2].ClearContents
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://www.earningswhispers.com/stocks/" & [A2].Text, False
        .setRequestHeader "DNT", "1"
         On Error Resume Next
        .send
         If .Status = 200 Then T$ = .responseText
    End With
         On Error GoTo 0
    If T = "" Then
        Beep
    Else
        With New HTMLDocument
            .body.innerHTML = T
             [B2:D2].Value2 = Array(.querySelector("#datebox>.mainitem").innerText, .all.earningstime.innerText, _
                                    -(InStr(.all.epsconfirmed.title, "not confirmed") = 0))
        End With
    End If
End Sub

 

[tttommy2]

I just realise that also the code Domenic gave me back in June 18, 2017 also stop working the last few days.

Originally if the earnings date is confirmed with a correct mark: "✔" to right above the earnings date then "1" returned in D2 .
And if the earnings date is unconfirmed with grey circle with white "-" in middle to right above the earnings date then "0" returned in D2.

www.earningswhispers.com/stocks/nvda is confirmed with a ✔ so "1" returned in D2.
www.earningswhispers.com/stocks/nke is unconfirmed with grey circle with white "-" in middle so "0" returned in D2.

Any ideas on how to get Dominic's original code below working again?

Try...

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

Hope this helps!

 

[Marc L]

Any ideas on how to get Dominic's original code below working again?

Already done in my post #12 demonstration so try it at least !​

 

[tttommy2]

Sorry Marc L. You right. I'm very much a novice and I couldn't get your code to work yesterday. However today after some googling on your instruction to activate Microsoft HTML Object Library I got it to work perfectly.

Thank you for yor help and patience.

 

[Marc L]

​

As you could ask here ! Anyway, well done and thanks !​