none
Pulling sprecific info from Yahoos Key Statistics Page into Excel RRS feed

  • Question

  • Hello, 

    I am currently trying to pull financial information from Yahoo's Key Statistics Page . I was able to find a script online but i cannot figure out how to adjust the script to import all of the data on the same excel tab rather than creating a new separate tab for every ticker. I am also only trying to pull 3 pieces of information: Total Debt (mrq), Total Cash (mrq), Revenue (ttm), NNet Income Avl to Common (ttm): and Market Cap (intraday)5:

    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"",2,3,5,7,9,10,13,17,19,21,23"
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
        Next i
    End Sub

    Can anyone assist? Thank you!!


    • Edited by Solar1234 Monday, November 14, 2016 8:37 PM
    Monday, November 14, 2016 8:36 PM

All replies

  • Hi Solar

    I suggest to add some kind of overview sheet which displays only the required data. This can be done by using worksheet formulas so you do not have to touch the existing code you downloaded.

    The INDIRECT formula is used because it allows to remove the data sheets without breaking the formulas. Once you run your macro the data sheets will be re-created and the formulas on the overview sheet will still work.

    Does this help?

    Cheers, Luca

    Monday, November 14, 2016 9:38 PM
  • Thanks Luca. The problem I am having is that I will have hundreds of tickers that i will need this information for. I am trying to update the macro so that it references an adjacent cell on the active sheet and pulls the information into 3 columns. I just don't know how to update the macro to reference an adjacent cell that includes the ticker symbol and gives me the output on the same active sheet.

    Does that make sense?

    Thank you  

    Monday, November 14, 2016 9:44 PM
  • Hi Solar

    I fear what you are trying to do is not feasible. I do not know this Yahoo Finance portal but I took a quick look and found no option to configure it that just the values you require are visible. It always displays all data. Therefore when making a QueryTable in Excel (and I know of no better option to get data from a web page into Excel) all data will be imported. There's only the "WebTables" property which would allow some filtering, but if my understanding is correct this does not help in your case.

    So the only option I see is to first import the data (like you do it now) and then have a consolidation sheet which just shows the values required, structured as desired. This could be achieved using worksheet formulas (as suggested above) or VBA code. If you are not a programmer it might be easier to implement (and later maintain) the solution with worksheet formulas.

    As you mentioned that there are hundreds of tickers I completely agree to transpose the result table. But still my approach would be the same as described above.

    One more remark: if the list of ticker symbols does not change you won't have to create new QueryTables to update the data. You simply can press "Refresh All" in the ribbon and the existing data connections will be updated.

    Cheers, Luca

    Monday, November 14, 2016 10:38 PM
  • Hi Solar1234,

    I can see that data are come for "MSFT" and "CSCO".

    we can print the information on same sheet and in 3 columns but after we fetch the data from the site and data are available in the sheet. then we can process the data.

    below is the modified code to print the data on same sheet without creating new sheet.

    Option Explicit
    
    Sub Macro2()
        Dim conString As String
        Dim conName As String
        Dim i As Integer
        Dim txtSymbols(0, 2) As String
        Dim rng(2) As String
        rng(1) = "$A$1"
        rng(2) = "$A$70"
        txtSymbols(0, 1) = "MSFT"
        txtSymbols(0, 2) = "CSCO"
        
        Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = txtSymbols(0, 1)
        For i = 1 To 2
            
            
            
        
            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(rng(i)))
                .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"",2,3,5,7,9,10,13,17,19,21,23"
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
        Next i
    End Sub
    

    after that you need to cut and paste the range.

    for cut and paste simple example is mentioned below.

    Sub Cut_Range_To_Clipboard()
    Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard
    'Now you can select any range and paste there
    Range("E2").Select
    ActiveSheet.Paste
    End Sub
    
    

    you can take this range from user so that you can cut and paste accurately and produce the desired result.

    so that you can view the data side by side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 15, 2016 4:03 AM
    Moderator
  • Take a look at this link.

    http://www.financialwisdomforum.org/gummy-stuff/Yahoo-data.htm

    That methodology is infinitely more efficient than yours.

    You can download a working sample from here.

    http://www.mediafire.com/file/ls3md22r201yunb/Yahoo_Price_PE_PEG_PB.xls



    MY BOOK

    Thursday, November 17, 2016 1:24 PM
  • Deepak,

    This works great thank you for your help. However, is there a way to tweak the macro to pull multiple tickers? Currently, the way the macro is written above I have to manually enter the ticker ("MSFT" and "CSCO")  into the macro. Ideally, I would like the macro to reference a column that contains all the ticker symbols and then lists all of the information in one tab.

    Does that make sense? 

    Thanks

    Solar

    Tuesday, December 6, 2016 4:51 PM
  • Hi Solar1234,

    you had asked," I have to manually enter the ticker ("MSFT" and "CSCO")  into the macro. Ideally, I would like the macro to reference a column that contains all the ticker symbols and then lists all of the information in one tab."

    from your description I can understand that you want to store all the tickers in one column and then based on that values you want to produce the data in the same sheet one after another.

    yes it is possible.

    First you need to store the tickers in one column.( you can also try to store the tickers in an array in the code. if you do that then you not need to create an extra column for tickers.)

    then you need to use loop. place all the code inside the loop.

    and I can see that I just did the same in my last example. I store the tickers in an array and used the loop. so you just need to add new elements to array and increment the value in loop.

    if you want to fetch the value from the range then you need to fetch cell value from the range and pass the value in below mentioned 2 lines in place of "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"
    

    then it will work as expected.

    if your issue is solved then I recommend you to mark the suggestion as an Answer which solves your issue. so that it will help us to close this thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 7, 2016 3:00 AM
    Moderator