Stocks Financial Statements from yahoo no longer transferrinf to excel

Question

I have a VBA code which transfers stocks Financial Statements (Income Statement, Balance Sheet, Cash Flows) from yahoo finance to excel, I have used it for a while now, but it seems yahoo has changed the links or something. Could someone help me re-link the links so that coding transfers the pulled information from yahoo to excel once again? Below is the coding

Sub FinancialStatements() Dim ticker As String Dim urlend As String

Application.ScreenUpdating = False


ticker = Sheets("inputs").Cells(2, 1)
If Sheets("Inputs").Shapes("Check Box 14").ControlFormat.Value = 1 Then
    urlend = "&annual"
Else: urlend = ""

End If



Sheets("Income Statement").Select
Cells.Clear

If Sheets("Inputs").Shapes("Check Box 11").ControlFormat.Value = 1 Then

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q/is?s=" & ticker & "" & urlend & "", Destination:=Range("$A$1") _
)
.Name = "is?s=MSFT&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
  End With

      End If


    Sheets("Balance Sheet").Select
Cells.Clear

If Sheets("Inputs").Shapes("Check Box 12").ControlFormat.Value = 1 Then

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q/bs?s=" & ticker & "" & urlend & "", Destination:=Range("$A$1") _
)
.Name = "is?s=MSFT&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
    End With
       End If

Sheets("Cash Flows").Select
Cells.Clear
If Sheets("Inputs").Shapes("Check Box 13").ControlFormat.Value = 1 Then

'
     With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/cf?s=" & ticker & "" & urlend & "", Destination:=Range("$A$1") _
)
.Name = "is?s=MSFT&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
    End With
End If


Application.ScreenUpdating = True

End Sub

You might want to ask Yahoo, or check their documentation? – criticalfix Apr 12 '17 at 19:03 — criticalfix, Apr 12 '17 at 19:03

ASH · Answer 1 · 2017-04-13T03:15:18.830

I think yahoo changed it's site recently. Just check your URL, and get that working first.

http://finance.yahoo.com/quote/IBM/financials?p=IBM

When you know that is right, engineer everything else around that.

Here is a solution that works for me. This imports data for several tickers, listed in a sheet, in cells A2, down to the end of the array.

Sub Dow_HistoricalData()

    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

    ThisSheet = ActiveSheet.Name
    Range("A2").Select
    Do Until ActiveCell.Value = ""
    Symbol = ActiveCell.Value
    Sheets(ThisSheet).Select
    Sheets.Add

    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    ' http://finance.yahoo.com/quote/" & Symbol & "/financials?ltr=1
    xmlHttp.Open "GET", "http://finance.yahoo.com/quote/" & Symbol & "/financials?ltr=1", 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("Lh(1.7) W(100%) M(0)")
    '

    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

Sheets(ActiveSheet.Name).Name = Symbol
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Select

Loop

End Sub

Here is a screen shot of my setup.

I understand that, but I'm not entirely sure how to corrected efficiently. I was not the one who made the coding — Sebastian, Apr 12 '17 at 20:23
I guess my question would be how do I get, or what is the exact API address? — Sebastian, Apr 12 '17 at 20:25
It looks like it is dynamically generated now. It looks like the income statement, balance sheet, and cash flow statement all have table class = Lh(1.7) W(100%) M(0) Yahoo must have changed it recently. — ASH, Apr 12 '17 at 20:37
Okay, I apologize for my ignorance, so that means it cannot be fixed and I should look for other sources? — Sebastian, Apr 12 '17 at 20:44

Stocks Financial Statements from yahoo no longer transferrinf to excel

1 Answers1