Extract data from yahoo finance using Excel VBA

Question

Hi I need help with this code I'm trying to extract data from this page https://finance.yahoo.com/quote/ADM.L/balance-sheet?p=ADM.L , but the problem is page is by default set to annual but I need quarterly values of total assets and total liabilities.

This code runs but most of the time it is picking annual values. Please suggest something what can I do.

Private Sub CommandButton3_Click()
'
Dim ie As Object
Set Rng = Range("A2:A50")
Set Row = Range(Rng.Offset(1, 0), Rng.Offset(1, 0).End(xlDown))
Set ie = CreateObject("InternetExplorer.Application")
With ie
'.Visible = False
For Each Row In Rng
.navigate "https://finance.yahoo.com/quote/" & Range("A" & Row.Row).Value & "/balance-sheet?p=" & Range("A" & Row.Row).Value
'Application.Wait (Now + TimeValue("0:00:02"))
While ie.readyState <> 4
Wend

Do While ie.Busy: DoEvents: Loop

Dim doc As HTMLDocument
Set doc = ie.document

doc.getElementsByClassName("P(0px) M(0px) C($actionBlue) Bd(0px) O(n)")(2).Click

Do While ie.Busy: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:05"))
Range("D" & Row.Row).Value = doc.getElementsByClassName("Fw(b) Fz(s) Ta(end)")(4).innerText
Range("E" & Row.Row).Value = doc.getElementsByClassName("Fw(b) Fz(s) Ta(end)")(12).innerText
Range("F" & Row.Row).Value = doc.getElementsByClassName("C($gray) Ta(end)")(0).innerText


Next Row
End With
ie.Quit
'
End Sub

Please suggest something what can I do. - You can use IE to choose the quarterly data before writing the elements to your worksheet. — Scott Holtzman
I did but it is not working always....mostly it is picking up the quarter data — Dream works
Assuming this line: doc.getElementsByClassName("P(0px) M(0px) C($actionBlue) Bd(0px) O(n)")(2).Click is what clicks the link for quarterly data, place a pause the VBA code for a few seconds to allow the events to catch up. I've found I had to do this often when automating IE with VBA. — Scott Holtzman
I have already put a pause can you please check by running it yourself here are the tickers for url III.L , ABF.L, ADM.L — Dream works

ASH ASH · Accepted Answer · 2017-06-21T21:50:09

This should be a good start for you to get going.

Sub DownloadData()

Set ie = CreateObject("InternetExplorer.application")


With ie
    .Visible = True
    .navigate "https://finance.yahoo.com/quote/ADM.L/balance-sheet?p=ADM.L"

' Wait for the page to fully load; you can't do anything if the page is not fully loaded
Do While .Busy Or _
    .readyState <> 4
    DoEvents
Loop


Set e = ie.Document.GetElementsByClassName("Fz(s) Fw(500) D(ib) Pend(15px) H(18px) C($finDarkLink):h Mend(15px)")(1)
e.Click

    ' Wait for the page to fully load; you can't do anything if the page is not fully loaded
    Do While .Busy Or _
        .readyState <> 4
        DoEvents
    Loop

End With

End Sub

Basically, 'Annual' is the default and you have to click the 'Quarterly' link, to get the quarterly data displayed. I believe yahoo used to have 2 different URLs for Annual and Quarterly. Now, apparently, they give you 2 links to click to toggle back and forth between the 2 frequencies of financial statements.

Extract data from yahoo finance using Excel VBA

3 Answers