I'm familiar with macros, but not a VBA programmer.

I'm trying to create an automated import into Excel from Yahoo Finance's Stock Quote Key Statistics page for my list of about 600 stock symbols. In other words, I'm trying to collect 600 webpages of data into Excel with a single operation.

I've tried to create a macro that executes a hyperlink to each page, followed by a copy/paste into Excel, but it doesn't work. Although it does open each webpage, the copy/paste only gets either the first page or the last page, depending on the design of the macro.

Here's a sample link to the webpage:

http://finance.yahoo.com/q/ks?s=MSFT+Key+Statistics

This must be do-able, but I'm stuck. Any suggestions?

Ms. Zhan,

Your response does not answer, nor even address what I asked.

Please read my post before responding.

instarac

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.

It works fine.

One quick question: could you explain the syntax of the line

.WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"

Many thanks!

instarac

Ms. Zhan,

Your response does not answer, nor even address what I asked.

Please read my post before responding.

instarac

Hi

The list indicates the id use in the HTML of the webpage to identify the table containing the data the query extracts see below:

<table cellpadding="0" cellspacing="0" border="0"><tr><td height="5"></td></tr></table><table border="0" cellspacing="0" cellpadding="2" width="752" id="yfncsubtit" class="yfnc_leftnav1"><tr bgcolor="#EEEEEE"><td height="30" class="ygtb"><b>Microsoft Corporation (MSFT)</b></td>

Where ids are not specified the number represents the order the tables are defined on the page.

Some of the tables are nested so

yfncsbutit = the stock name

8= Valuation Measures

10= the data in the above

11= Financial higlights

13= Fiscal Year

15 =Profitability

17=Management Effectiveness

19=Income Statement

21= Balance sheet

23= Cashflow Statement

Hope that explains.

Dear Mr. North,

Is it possible to modify your code so that it could extract data from the Balance Sheet page of the Yahoo Finance (http://finance.yahoo.com/q/bs?s=MMM+Balance+Sheet&annual)?

Hi G North,

Could you help me. I need to get the data from "income statement" rather than "key statistics". I have downloaded the data and it seems to work just changing the +key+statistics to something like +income+statementannual.

But... I only takes some of the numbers and i guess the reason is, the above numbers 8, 10, 11 etc. how do you find the numbers i need to use, to get the correct numbers. i have tried to read some html on the page, but i couldn't figure it out. Another question, there are 3 columns but i only get the first one. Can i get all 3 coloumns?