none
Import Data from Yahoo Finance webpages into Excel 2007 for a list of stocks RRS feed

  • Question

  • 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?


    instarac
    Thursday, November 25, 2010 9:07 PM

Answers

  • 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
    Wednesday, December 1, 2010 9:04 PM

All replies

  • Ms. Zhan,

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

    Please read my post before responding.

    instarac

     


    instarac
    Wednesday, December 1, 2010 8:11 PM
  • 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
    Wednesday, December 1, 2010 9:04 PM
  • 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


    instarac
    Wednesday, December 1, 2010 11:43 PM
  • Ms. Zhan,

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

    Please read my post before responding.

    instarac

     


    instarac

    Sorry, I misunderstand your meaning. So I delete my post.
    Thursday, December 2, 2010 7:08 AM
    Moderator
  • 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.


    G North MMI
    Thursday, December 2, 2010 9:48 AM
  • G North, would you please give me a link of an example of worksheet that using your vba macro ? Cause I am stupid and don't have a programming experience at all. Please Help Me
    Sunday, September 2, 2012 3:56 PM
  • Try this Excel worksheet
    Friday, November 16, 2012 1:32 PM
  • You can use Spearian for Excel, http://spearianforexcel.com, to get this done very comfortably.

    Jiri Pik, jiri@jiripik.com Web: http://jiripik.com, LinkedIn: http://www.linkedin.com/in/jiripik, Twitter: http://twitter.com/@JiriPik

    Friday, April 19, 2013 5:24 AM
  • Is there a way to do this all in the same sheet as opposed to creating a new sheet every time?
    Friday, July 11, 2014 5:57 PM
  • 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)?

    Friday, April 10, 2015 10:26 AM
  • You can download the balance sheet from Yahoo Finance to Excel using http://spearianforexcel.com and its WebRead function - see http://docs.spearian.com/SpearianForExcel/Reference/topics/excel-functions-webread.htm

    Jiri Pik, jiri@jiripik.com Web: http://jiripik.com, LinkedIn: http://www.linkedin.com/in/jiripik, Twitter: http://twitter.com/@JiriPik

    Friday, April 10, 2015 10:34 AM
  • 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?

    Tuesday, September 1, 2015 9:02 PM