none
Import Data from Balance Sheets Published on Yahoo Finance RRS feed

  • Question

  • I want to download data from here: https://finance.yahoo.com/q/bs?s=MSFT+Balance+Sheet&annual to my Excel file for dozens of stocks. Any tips on how to do this in VBA will be highly appreciated.

    FYI!

    Mr. G North succeeded to create the code scraping Key Statistics from Yahoo Finance (see below). I need a similar stuff but for Balance Sheet page.

    Hi

    Here's some code to extract data from the microsoft and cisco pages you refer to using Web Queries.  You dont say what info you want from the pages so I picked most of it.  You can record defining a basic web query to limit the data to the parts you want.  The modify it to include the other aspects of this code. 

    I only did two good luck with 600.

    Sub Macro2()
        Dim conString As String
        Dim conName As String
        Dim txtSymbols(0, 2) As String
       
        txtSymbols(0, 1) = "MSFT"
        txtSymbols(0, 2) = "CSCO"
       
        For i = 1 To 2
           
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = txtSymbols(0, i)
           
       
            conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
            conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
           
            With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
                .Name = conName
                .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 = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
        Next i
    End Sub

    You can use

        ActiveWorkbook.RefreshAll

    To refersh the data gathered by these queries

    Or

        Selection.QueryTable.Refresh BackgroundQuery:=False

    to refresh just the query on the current page.

    Hope this points you in the right direction.


    G North MMI


    • Edited by Trafo Friday, April 10, 2015 11:31 AM
    Friday, April 10, 2015 11:31 AM

Answers

  • Hi,

    This is how a simple code for PowerQuery looks like:

    let  Quelle = Web.Page(Web.Contents("https://finance.yahoo.com/q/bs?s=MSFT+Balance+Sheet&annual")),

        Data = Quelle{1}[Data],

        FirstRowHeader = Table.PromoteHeaders(Data)

    in  FirstRowHeader

    If you want make this a batch-report, you need to create a table in your Excel workbook, named “URL” with these 2 columns: Company and URL

    In the company col you enter the Company code that is used in the URL: MSFT

    In the URL col you dynamically create you URL-code like this: ="https://finance.yahoo.com/q/bs?s="&[@Company]&"+Balance+Sheet&annual"

    You create a function called “fnURL”:

    let URL = (Company) =>

    let Quelle =  Web.Page(Web.Contents(Text.Replace("https://finance.yahoo.com/q/bs?s={URL_}+Balance+Sheet&annual", "{URL_}", Company))),

        Data = Quelle{1}[Data]

    in    Data

    in   URL

    and invoke it in this query:

    let

        Quelle = Excel.CurrentWorkbook(){[Name="URL"]}[Content],

        AddCol = Table.AddColumn(Quelle, "_", each fnURL([Company])),

        RemoveCol = Table.RemoveColumns(AddCol,{"URL"}),

        Expand = Table.ExpandTableColumn(RemoveCol, "_", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"})

    in

        Expand

    This will give you a table with all companies’ balance sheets that you simply filter on the company code.

    But this is not why you should use Power Query. It’s what you can do with this data, if you transform and enrich it a little bit and load it to the PowerPivot Data Model. This will give you totally new perspectives – have a look into the file: Report1 & Report2, just to give you an idea…

    https://onedrive.live.com/edit.aspx?cid=DE165DDF5D02DAFF&resid=de165ddf5d02daff%217946&app=Excel


    Imke

    Monday, April 13, 2015 6:36 PM
  • Power Query as suggested by others is a great tool and if possible to get multiple reports from the same query would certainly be the way to go. If Gil or Imke could suggest how for this example please do?

    In the meantime the simple old web query approach for your particular query

    Sub Test()
    Dim sTicker As String, sQT As String
    Dim rInsert As Range
    
        sTicker = "MSFT"
        Set rInsert = ActiveSheet.Range("B3")
        ActiveSheet.Range("A2") = sTicker
    
        sQT = QTBalanceSheet(rInsert, sTicker)
        ActiveSheet.Range("B2") = "Query name: " & sQT '
       ' will need to refresh manually or with code
        
    End Sub
    
    Function QTBalanceSheet(rInsert As Range, sTicker As String)
    Dim sCon As String
    Dim qt As QueryTable
    
        sCon = "URL;https://finance.yahoo.com/q/bs?s=<ticker>+Balance+Sheet&annual"
        sCon = Replace(sCon, "<ticker>", sTicker)
    
        Set qt = ActiveSheet.QueryTables.Add(Connection:=sCon, Destination:=rInsert)
        With qt
            .Name = sTicker & "BalanceSheet&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
            QTBalanceSheet = .Name
        End With
        
    End Function
    


    Monday, April 13, 2015 2:16 PM

All replies

  • You should consider using Power Query add-in for Excel as an easier way to import this data. Power Query is a free Add-in for Excel 2010, 2013 and Office 365 in Office Professional Plus and Excel Standalone licenses. It allows you with simple UI operations to create a refreshable connection that will load the desired data into your worksheet or Data Model.

    After you install it, you can use the "From Web" button, type the URL above and start extracting your data with an simple yet powerful user interface.

    Download Power Query here.

    Note that in Excel 2016 Preview, Power Query is integrated to the Data ribbon. So learning how to use it, can be a worthwhile effort with long term ROI :)

    Friday, April 10, 2015 3:32 PM
  • & you have nice facilities for bulk loading multiple files at once (into one table):

    https://kzhendev.wordpress.com/2014/04/14/scraping-the-web-with-power-query/


    Imke


    Friday, April 10, 2015 4:10 PM
  • Power Query as suggested by others is a great tool and if possible to get multiple reports from the same query would certainly be the way to go. If Gil or Imke could suggest how for this example please do?

    In the meantime the simple old web query approach for your particular query

    Sub Test()
    Dim sTicker As String, sQT As String
    Dim rInsert As Range
    
        sTicker = "MSFT"
        Set rInsert = ActiveSheet.Range("B3")
        ActiveSheet.Range("A2") = sTicker
    
        sQT = QTBalanceSheet(rInsert, sTicker)
        ActiveSheet.Range("B2") = "Query name: " & sQT '
       ' will need to refresh manually or with code
        
    End Sub
    
    Function QTBalanceSheet(rInsert As Range, sTicker As String)
    Dim sCon As String
    Dim qt As QueryTable
    
        sCon = "URL;https://finance.yahoo.com/q/bs?s=<ticker>+Balance+Sheet&annual"
        sCon = Replace(sCon, "<ticker>", sTicker)
    
        Set qt = ActiveSheet.QueryTables.Add(Connection:=sCon, Destination:=rInsert)
        With qt
            .Name = sTicker & "BalanceSheet&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
            QTBalanceSheet = .Name
        End With
        
    End Function
    


    Monday, April 13, 2015 2:16 PM
  • Hi,

    This is how a simple code for PowerQuery looks like:

    let  Quelle = Web.Page(Web.Contents("https://finance.yahoo.com/q/bs?s=MSFT+Balance+Sheet&annual")),

        Data = Quelle{1}[Data],

        FirstRowHeader = Table.PromoteHeaders(Data)

    in  FirstRowHeader

    If you want make this a batch-report, you need to create a table in your Excel workbook, named “URL” with these 2 columns: Company and URL

    In the company col you enter the Company code that is used in the URL: MSFT

    In the URL col you dynamically create you URL-code like this: ="https://finance.yahoo.com/q/bs?s="&[@Company]&"+Balance+Sheet&annual"

    You create a function called “fnURL”:

    let URL = (Company) =>

    let Quelle =  Web.Page(Web.Contents(Text.Replace("https://finance.yahoo.com/q/bs?s={URL_}+Balance+Sheet&annual", "{URL_}", Company))),

        Data = Quelle{1}[Data]

    in    Data

    in   URL

    and invoke it in this query:

    let

        Quelle = Excel.CurrentWorkbook(){[Name="URL"]}[Content],

        AddCol = Table.AddColumn(Quelle, "_", each fnURL([Company])),

        RemoveCol = Table.RemoveColumns(AddCol,{"URL"}),

        Expand = Table.ExpandTableColumn(RemoveCol, "_", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"})

    in

        Expand

    This will give you a table with all companies’ balance sheets that you simply filter on the company code.

    But this is not why you should use Power Query. It’s what you can do with this data, if you transform and enrich it a little bit and load it to the PowerPivot Data Model. This will give you totally new perspectives – have a look into the file: Report1 & Report2, just to give you an idea…

    https://onedrive.live.com/edit.aspx?cid=DE165DDF5D02DAFF&resid=de165ddf5d02daff%217946&app=Excel


    Imke

    Monday, April 13, 2015 6:36 PM
  • Hi Imke,

    This is a fantastic demo, I'm pleased I asked :)

    It shows many interesting features and techniques, and perhaps quite a lot to learn. I will need to spend some time looking in detail. First thing is I need to fix and rebuild are a few "Formula.Firewall: Query"

    Thank you for taking the time to put this together!

    Tuesday, April 14, 2015 11:31 AM
  • Hi Peter,

    great you like it :-)

    Try enable fast combine under: Power Query - Settings - Workbook Settings.


    Imke

    Tuesday, April 14, 2015 1:42 PM
  • AhHa, combine, Formula.Firewall all gone now, that was easy! 

    Just to add all seemed to work after choosing "Ignore the Privacy Levels..."

    Thanks!

    Tuesday, April 14, 2015 4:33 PM
  • Hello, This VBA Code doesn´t work any more. It seems, yahoo finance changed the URL.

    Do you have any change how to fix this problem? Or is there any other solution?

    Best regards,

    Nico

    Tuesday, April 11, 2017 8:58 PM