Extract financial fundamental from Yahoo finance with excel

Question

Yahoo changed his structure for the financials (fundamental) report. In the past (last month) at the page https://finance.yahoo.com/quote/AAPL/financials?p=AAPL&guccounter=1 I could download the information regarding the financial Income Statement or Balance Sheet or Cash Flow in VBA Excel as a table. Now they have updated the "new" version that appears as a table but I cannot extract it con VBA Excel. There someone that can help me? If you need I can post the code in VBA regarding the old version, that can download all the tables in the HTML page.

To better explain I need the information in the yellow box. You can see that it appears as a table, but if you look inside in the HTML code there is not the normal tag regarding tables. I identify that the starting "table" has this tag <div class="D(tbrg)" but I don't know how to proceed to extract data. The same "table" is in the Balance Sheet and Cash Flow selection.

Thanks in advance

I do not extract information from Yahoo but I have and do extract information from other websites and electronic files. The authors of such data regularly change the format (an extra column here or a rearrangement there). Perhaps they think they are improving it or perhaps they wish to discourage its use in this way. Either way, there is no point starting this type of extraction unless you are able to update your routines quickly after each format change. If someone here helped you update your routine, it would only delay the need for you to be able do it yourself. — Tony Dallimore
Dear EliteRaceElephant, I tried to build up a new script, but the problem is that I don't know HTML well, so I cannot found the correct way to extract them — bibsn
if you have Excel 2010+, you may be able to obtain the information by using Power Query aka Get & Transform — Ron Rosenfeld

ASH ASH · Accepted Answer · 2019-10-20T20:58:24

Try this.

Sub Yahoo_BS()

Dim xmlHttp As Object
Dim TR_col As Object, Tr As Object
Dim TD_col As Object, Td As Object
Dim row As Long, col As Long

    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    myURL = "https://finance.yahoo.com/quote/SBUX/balance-sheet?p=SBUX"
    xmlHttp.Open "GET", myURL, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send

    Dim html As Object
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = xmlHttp.responseText

    Dim tbl As Object
    Set tbl = html.getElementById("Pos(r)")


    row = 1
    col = 1

    Set TR_col = html.getElementsByTagName("TR")
    For Each Tr In TR_col
        Set TD_col = Tr.getElementsByTagName("TD")
        For Each Td In TD_col
            Cells(row, col) = Td.innerText
            col = col + 1
        Next
        col = 1
        row = row + 1
    Next

End Sub

Keep in mind that this is just one way to do it. I'm sure there are several means to the same end. Also, think about looping through an array of ticker symbols. You can add new sheets for each ticker and import the relevant data for each symbol that you query for.

Please click the check to say the answer is useful if this solution helps you.

Extract financial fundamental from Yahoo finance with excel

1 Answers