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.

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 :)

& you have nice facilities for bulk loading multiple files at once (into one table):

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

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. Its 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

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!

Hi Peter,

great you like it :-)

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

AhHa, combine, Formula.Firewall all gone now, that was easy! 

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

Thanks!