How to call the free Yahoo Finance API to get Stock Data (Excel & VBA)

Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explain how to get stock data from Yahoo Finance using Excel & VBA. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

In this example, we want to obtain the following Stock info of Google, Facebook and Microsoft.

  • Name of company
  • Last trade date
  • Last trade (price only)
  • Dividend Yield
  • P/E Ratio

Pre-req
Make sure Microsoft WinHTTP Services are enable. To do so , open Visual Basic Editor of your xlsm file. Click Tools >> References. And select Microsoft WinHTTP Services.

Demo

You can also download a demo of this Excel file.

Development Process – Step 1

Range A2 to be taken the head cell. Type “GOOG” in range A3, “FB” in A4 and “MSFT” in range A5. Type “n” in range B3, “d1” in range C3, “l1” in D3, “y” in E3 and “r” in F3. A sample shown below.

Excel VBA Yahoo Finance

Step 2

Open Microsoft Visual Basic for Applications on your Excel. Copy the code and paste it on your sheet’s VBA area.

Double click to select all.

Option Explicit

' Example: Requesting Stock info of GOOG, FB and MSFT.
'
' In this example, we want to obtain the following Stock info
'   - Name of company
'   - Last trade date
'   - Last trade (price only)
'   - Dividend Yield
'   - P/E Ratio
'
' Range A2 to be taken the head cell. Type "GOOG" in range A3,
' "FB" in A4 and "MSFT" in range A5. Type "n" in range B3, "d1"
' in range C3, "l1" in D3, "y" in E3 and "r" in F3.
'
' We use the following URL for getting Stock Quote from Yahoo
' URL: http://finance.yahoo.com/d/quotes.csv?s=FB+MSFT&f=snd1l1yr
'
' The CSV file data looks like the following
'
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' "MSFT","Microsoft Corpora","9/4/2013",31.24,2.89,12.36
'
' The columns in CSV file is separated by comma. Split function
' is used to split the data on each line of CSV file at every
' occurrence of comma. However, some commas in the CSV file are
' part of a string, not separating the columns. For example
' consider the following line,
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' The second comma is not a column separator. But it's part
' of the company's name "Facebook, Inc."
' This program will identify which comma is a column separator
' and which is part of a string

' ************
' Written by http://www.msexcelvancouver.com
' Thanks to http://www.youtube.com/watch?v=iSlBE3CWg5Q

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

    Dim head As Range
    Set head = Range("A2")
    
    Dim i As Integer
    Dim Symbols As String: Symbols = ""
    Dim SpecialTags As String: SpecialTags = ""
    Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://download.finance.yahoo.com/d/quotes.csv?s="
    Dim rng As Range
    Dim cell As Range
    
    ' Get the Stock Symbols
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
        Symbols = Symbols & cell.Value & "+"
    Next cell
    Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'
    
    ' Get the Special Tags
    Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
    For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
        SpecialTags = SpecialTags & cell.Value
    Next
    
    ' Put the desciption/name of each tag in the cell above it
    Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
    Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
    For Each cell In rng
        cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
    Next
    
    
    Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags
    
    Call Print_CSV(Yahoo_Finance_URL, head)
    
End Sub

' Download the CSV file and Print it in the cells
Sub Print_CSV(URL As String, head As Range)

    ' Get the CSV file from Yahoo Finance
    Dim Http As New winhttprequest
    Http.Open "GET", URL, False
    Http.send
    
    Dim Resp As String: Resp = Http.responsetext
    Dim Lines As Variant: Lines = Split(Resp, vbLf)
    Dim sLine As Variant
    Dim Values As Variant
    Dim Value As Variant
    
    Dim i As Integer: i = 1
    Dim next_column As Boolean: next_column = True
    Dim col As Integer
    Dim tmp As String
    
    ' Extract CSV file Data to Cells
    For Each sLine In Lines
        Values = Split(sLine, ",")
        col = 1
        tmp = ""
        For Each Value In Values
            ' Read the info for this program which explains the lines below
            tmp = tmp & Value
            If Left(Value, 1) = """" And Right(Value, 1) <> """" Then next_column = False
            If Not next_column And Right(Value, 1) = """" Then next_column = True
            If next_column Then
                head.Offset(i, col).Value = Replace(tmp, Chr(34), "") ' Remove '"'
                tmp = ""
                col = col + 1
            End If
        Next Value
        i = i + 1
    Next sLine
End Sub

' Find the name of the given tag
Function FindTagName(tag As String, SpecialTags() As String, Tag_Name() As String) As String
    Dim pos As Integer
    Dim tagName As String
    
    On Error GoTo CannotFindTagName
        
    pos = Application.Match(tag, SpecialTags, False) - 1
    
    tagName = Tag_Name(pos)
    FindTagName = tagName
    Exit Function
    
CannotFindTagName:
    MsgBox tag & " not found! Program terminates."
    End
End Function

The following function includes two arrays, one containing the special tags and another containing tag names. You could use these arrays as a reference.

Double click to select all.

' Yahoo Finance Special Tags & Names
Sub Get_Special_Tags(ByRef SpecialTags() As String, ByRef Tag_Name() As String)

    ReDim SpecialTags(0 To 84) As String
    ReDim Tag_Name(0 To 84) As String

    SpecialTags(0) = "a": Tag_Name(0) = "Ask"
    SpecialTags(1) = "a2": Tag_Name(1) = "Average Daily Volume"
    SpecialTags(2) = "a5": Tag_Name(2) = "Ask Size"
    SpecialTags(3) = "b": Tag_Name(3) = "Bid"
    SpecialTags(4) = "b2": Tag_Name(4) = "Ask (Real-time)"
    SpecialTags(5) = "b3": Tag_Name(5) = "Bid (Real-time)"
    SpecialTags(6) = "b4": Tag_Name(6) = "Book Value"
    SpecialTags(7) = "b6": Tag_Name(7) = "Bid Size"
    SpecialTags(8) = "c": Tag_Name(8) = "Change & Percent Change"
    SpecialTags(9) = "c1": Tag_Name(9) = "Change"
    SpecialTags(10) = "c3": Tag_Name(10) = "Commission"
    SpecialTags(11) = "c6": Tag_Name(11) = "Change (Real-time)"
    SpecialTags(12) = "c8": Tag_Name(12) = "After Hours Change (Real-time)"
    SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
    SpecialTags(14) = "d1": Tag_Name(14) = "Last Trade Date"
    SpecialTags(15) = "d2": Tag_Name(15) = "Trade Date"
    SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
    SpecialTags(17) = "e1": Tag_Name(17) = "Error Indication (returned for symbol changed / invalid)"
    SpecialTags(18) = "e7": Tag_Name(18) = "EPS Estimate Current Year"
    SpecialTags(19) = "e8": Tag_Name(19) = "EPS Estimate Next Year"
    SpecialTags(20) = "e9": Tag_Name(20) = "EPS Estimate Next Quarter"
    SpecialTags(21) = "f6": Tag_Name(21) = "Float Shares"
    SpecialTags(22) = "g": Tag_Name(22) = "Day's Low"
    SpecialTags(23) = "h": Tag_Name(23) = "Day's High"
    SpecialTags(24) = "j": Tag_Name(24) = "52-week Low"
    SpecialTags(25) = "k": Tag_Name(25) = "52-week High"
    SpecialTags(26) = "g1": Tag_Name(26) = "Holdings Gain Percent"
    SpecialTags(27) = "g3": Tag_Name(27) = "Annualized Gain"
    SpecialTags(28) = "g4": Tag_Name(28) = "Holdings Gain"
    SpecialTags(29) = "g5": Tag_Name(29) = "Holdings Gain Percent (Real-time)"
    SpecialTags(30) = "g6": Tag_Name(30) = "Holdings Gain (Real-time)"
    SpecialTags(31) = "i": Tag_Name(31) = "More Info"
    SpecialTags(32) = "i5": Tag_Name(32) = "Order Book (Real-time)"
    SpecialTags(33) = "j1": Tag_Name(33) = "Market Capitalization"
    SpecialTags(34) = "j3": Tag_Name(34) = "Market Cap (Real-time)"
    SpecialTags(35) = "j4": Tag_Name(35) = "EBITDA"
    SpecialTags(36) = "j5": Tag_Name(36) = "Change From 52-week Low"
    SpecialTags(37) = "j6": Tag_Name(37) = "Percent Change From 52-week Low"
    SpecialTags(38) = "k1": Tag_Name(38) = "Last Trade (Real-time) With Time"
    SpecialTags(39) = "k2": Tag_Name(39) = "Change Percent (Real-time)"
    SpecialTags(40) = "k3": Tag_Name(40) = "Last Trade Size"
    SpecialTags(41) = "k4": Tag_Name(41) = "Change From 52-week High"
    SpecialTags(42) = "k5": Tag_Name(42) = "Percebt Change From 52-week High"
    SpecialTags(43) = "l": Tag_Name(43) = "Last Trade (With Time)"
    SpecialTags(44) = "l1": Tag_Name(44) = "Last Trade (Price Only)"
    SpecialTags(45) = "l2": Tag_Name(45) = "High Limit"
    SpecialTags(46) = "l3": Tag_Name(46) = "Low Limit"
    SpecialTags(47) = "m": Tag_Name(47) = "Day's Range"
    SpecialTags(48) = "m2": Tag_Name(48) = "Day's Range (Real-time)"
    SpecialTags(49) = "m3": Tag_Name(49) = "50-day Moving Average"
    SpecialTags(50) = "m4": Tag_Name(50) = "200-day Moving Average"
    SpecialTags(51) = "m5": Tag_Name(51) = "Change From 200-day Moving Average"
    SpecialTags(52) = "m6": Tag_Name(52) = "Percent Change From 200-day Moving Average"
    SpecialTags(53) = "m7": Tag_Name(53) = "Change From 50-day Moving Average"
    SpecialTags(54) = "m8": Tag_Name(54) = "Percent Change From 50-day Moving Average"
    SpecialTags(55) = "n": Tag_Name(55) = "Name"
    SpecialTags(56) = "n4": Tag_Name(56) = "Notes"
    SpecialTags(57) = "o": Tag_Name(57) = "Open"
    SpecialTags(58) = "p": Tag_Name(58) = "Previous Close"
    SpecialTags(59) = "p1": Tag_Name(59) = "Price Paid"
    SpecialTags(60) = "p2": Tag_Name(60) = "Change in Percent"
    SpecialTags(61) = "p5": Tag_Name(61) = "Price/Sales"
    SpecialTags(62) = "p6": Tag_Name(62) = "Price/Book"
    SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"
    SpecialTags(64) = "r": Tag_Name(64) = "P/E Ratio"
    SpecialTags(65) = "r1": Tag_Name(65) = "Dividend Pay Date"
    SpecialTags(66) = "r2": Tag_Name(66) = "P/E Ratio (Real-time)"
    SpecialTags(67) = "r5": Tag_Name(67) = "PEG Ratio"
    SpecialTags(68) = "r6": Tag_Name(68) = "Price/EPS Estimate Current Year"
    SpecialTags(69) = "r7": Tag_Name(69) = "Price/EPS Estimate Next Year"
    SpecialTags(70) = "s": Tag_Name(70) = "Symbol"
    SpecialTags(71) = "s1": Tag_Name(71) = "Shares Owned"
    SpecialTags(72) = "s7": Tag_Name(72) = "Short Ratio"
    SpecialTags(73) = "t1": Tag_Name(73) = "Last Trade Time"
    SpecialTags(74) = "t6": Tag_Name(74) = "Trade Links"
    SpecialTags(75) = "t7": Tag_Name(75) = "Ticker Trend"
    SpecialTags(76) = "t8": Tag_Name(76) = "1 yr Target Price"
    SpecialTags(77) = "v": Tag_Name(77) = "Volume"
    SpecialTags(78) = "v1": Tag_Name(78) = "Holdings Value"
    SpecialTags(79) = "v7": Tag_Name(79) = "Holdings Value (Real-time)"
    SpecialTags(80) = "w": Tag_Name(80) = "52-week Range"
    SpecialTags(81) = "w1": Tag_Name(81) = "Day's Value Change"
    SpecialTags(82) = "w4": Tag_Name(82) = "Day's Value Change (Real-time)"
    SpecialTags(83) = "x": Tag_Name(83) = "Stock Exchange"
    SpecialTags(84) = "y": Tag_Name(84) = "Dividend Yield"

End Sub

YouTube Video

We also explained how to use this Excel file, on a YouTube Video.

Need some help?

Please feel free to contact our Excel experts if you are looking for some Excel help.

 

Published by

bSuperior System Ltd Experts

We are experts in customizing Excel files and VBA programming. We create Excel files for specific needs. We develop VBA programs to automate tasks and execute processes automatically . We are based in Vancouver BC, Canada.

75 thoughts on “How to call the free Yahoo Finance API to get Stock Data (Excel & VBA)”

  1. Thanks for putting this together. Since Yahoo finance was recently revamped it is no longer possible to easily retrieve stock / mutual fund prices as a web query withing microsoft excel.

    1. Yes, Yahoo could provide historical data and I could develop a custom program for this purpose. Please email me to discuss more.

  2. hi! thanks for putting this together. Sounds really useful. When i tried to add the code you published into a VBA module, i got an error — seems like VBA doesn’t like the line: “http.send” on line 78. any advice ?

    1. Thanks for your feedback. Maybe your excel has some problems with creating objects or HTTP Send. If you have more details about the error, it would be easier to debug the problem.

      1. Thank you for your kindly help. I met the similar problem. The error is “Run-time error ‘-2147012889(80072ee7)’: The server name or address could not be resolved. ” However, I am sure the link is correct, since I could open the link on my Chrome. Thank you for letting me know if there is any tips on solving this error.

        1. I ran into this same issue – it’s a result of your VBA references not being selected. To fix – in VBA page – Tools > References. In the popup menu, select ‘Microsoft WinHTTP Services, version 5.1’. Save and rerun and this should work.

  3. Works really well! Thanks a ton!

    Quick question – what if I want to extract info on non-US stocks? Say for example Larsen and Toubro traded on NSE, India (Yahoo Code: LT.NS)? When I input the stock code, it doesnt seem to work. Any idea if I should tweak the code before I venture into non-US stocks?

  4. Many thanks for this code, this is very helpful. For specific securities like IE0032769055.IR or IE0030982171.IR I would like to follow, there is no identifier and your macro in VBA does not recognize it, even if I can use this on Yahoo Finance. Can you help?

  5. Hey how could I change the code so that I can write the tag names “52 wk Rg” etc. on the column headers instead of the shortcut “w”

  6. Very useful. However, this does not work very well for mutual funds. For example, none of the dividend tags work – d, y, etc. Do you happen to know how to retrieve those and other data for mutual funds?

  7. This did not quite work on my Excel for Mac 2011. I adapted it to a version that does appear to be working, which required using QueryTables in place of the HTTP Get call, which evidently is not supported by Mac Excel 2011.

    I’ll paste the code below:

    Sub Yahoo_Finance_API_Call_MacExcel2011()
    Dim head As Range
    Set head = Range(“A1”)

    Dim wb As Workbook ‘In the event that you’ll use different workbooks
    Dim src As Worksheet ‘In the event that you’ll use different a source worksheet
    Dim tgt As Worksheet ‘In the event that you’ll use different a target worksheet

    Set wb = ThisWorkbook
    Set src = wb.Sheets(“Sheet1”)
    Set tgt = wb.Sheets(“Sheet1”)

    ‘Assemble Symbols for API Call
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ‘ Starting from a cell below the head cell till the last filled cell
    Symbols = Symbols & cell.Value & “+”
    Next cell
    Symbols = Left(Symbols, Len(Symbols) – 1) ‘ Remove the last ‘+’

    ‘Assemble Tags or API Call
    Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
    For Each cell In rng ‘ Starting from a cell to the right of the head cell till the last filled cell
    tags = tags & cell.Value
    Next cell

    ‘Build URL
    URL = “TEXT;http://finance.yahoo.com/d/quotes.csv?s=” ‘Use TEXT to collect API data below
    URL = URL & Symbols & “&f=” & tags

    ‘Range(“A1”).Value = URL ‘This will output the assembled URL in a1 for QA if need be

    ‘Call API
    With tgt.QueryTables.Add(Connection:= _
    URL, _
    Destination:=Range(head.Offset(1, 1), head.Offset(1, 1).End(xlDown)))

    .RefreshStyle = xlOverwriteCells
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .BackgroundQuery = True
    .TextFileCommaDelimiter = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .TextFilePromptOnRefresh = False
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .SaveData = False
    End With

    End Sub

    1. You could make a loop and use Application.Wait(Time) function to delay for example a minute. However, Yahoo wants to be a delay between API Requests so it’s not a good idea to do an API request continuously with no delay.

  8. You did not define “y” in the Get_Special_Tags().
    Should I add this line at the end?
    SpecialTags(83) = “y”: Tag_Name(83) = “Dividend Yield”

  9. Thank you!!! Not being a Visual Basic Programmer, I was at my wits end trying to figure out what I could do to salvage weeks of work on my excel spreadsheet that was made useless after the MSN Money website changed. My query function no longer returned the expected stock data I needed to track and analyze my portfolio. Your subroutine is just what the Dr. ordered to generate a database from which I can extract the necessary information. Thank you again.

  10. It appears something has changed the week of Mar 9, 2015, in the formatting of the data at Yahoo that is causing the excel formatting to show the data in rows instead of columns. Can you confirm this and let me know how to adjust the macro?

  11. I’ve been using this code for about a year now and it’s worked flawlessly. Today, however, all the results (100+ stocks) are being populated across one row instead of the row with the associated symbol. Can you help?

  12. In case anyone comes across the same issue…for some reason this stopped working for me, and was writing all the data to a single row. After some troubleshooting, I discovered that the Resp string was not splitting at each new line. Changing

    Dim Lines As Variant: Lines = Split(Resp, vbNewLine) to:

    Dim Lines As Variant: Lines = Split(Resp, vbLf)

    solved the issue.

  13. Thanks for the VBA has been of much use, however, recently it is implemented inadequately, print data of all quotes followed in the first row and not in the row corresponding to each symbol. I can guide grateful to correct this problem.

  14. Thank you for the alternative to the Microsoft version. However, the termination character(s) for the “Lines” array may vary. This may be from a change to the Yahoo Finance API, or a side effect of operating system (Mac vs. PC), or of EXCEL version. For my situation, I had to change this line:
    >
    Dim Lines As Variant: Lines = Split(Resp, vbNewLine)
    >
    to this:
    >
    Dim Lines As Variant: Lines = Split(Resp, Chr(10))

  15. Hi admin great file but everytime i run the macro it pastes it horizontally (all data for all tickers on row 1). How do i get it to transpose correctly?

  16. It seems that Yahoo has changed the API response. The return value in Resp is not split into lines anymore using “Lines = Split(Resp, vbNewline)”.

    Any solution available?

  17. Something changed recently and this code no longer works. When I ran it in the debugger it appears that Http.responsetext in Sub Print_CSV no longer contains newline characters – so the Dim Lines As Variant: Lines = Split(Resp, vbNewLine) no longer produces an array of lines one per stock quote. I am no sure why this is – I looked at the data yahoo is returning and each line is delimited with character 0xa which is the unix style line terminator. I have been hacking away on an alternate line parser using the raw data in Http.responsebody but running into a few snags since I am unfamiliar with visual basic programming. Not sure what happened – either yahoo changed their data format or the http response parsing code changed somehow.

    1. Indeed someone changed the API breaking this script as well as many others I suspect. I also had a Perl script broken with this change.

      You can fix the script by

      changing

      Dim Lines As Variant: Lines = Split(Resp, vbNewLine)

      to

      Dim Lines As Variant: Lines = Split(Resp, vbLf)

      This is a very nice implementation and very useful inside of Excell. Good luck

  18. Here is some less than elegant code that seems to work – This replaces your version of Print_CSV. The only thing I don’t like about it is that I allocate a fixed size array of 300 strings to hold the results of the line parse. I tried experimenting with more dynamic allocation methods but did not get too far.

    ‘ Download the CSV file and Print it in the cells
    Sub Print_CSV(URL As String, head As Range)

    ‘ Get the CSV file from Yahoo! Finance
    Dim Http As Object
    Set Http = CreateObject(“WinHttp.WinHttpRequest.5.1”)
    Http.Open “GET”, URL, False
    Http.send

    Dim Raw As Variant: Raw = Http.responsebody
    Dim Raws As Variant
    Dim Values As Variant
    Dim Value As Variant

    Dim i As Integer
    Dim n As Integer: n = 0
    Dim next_column As Boolean: next_column = True
    Dim col As Integer
    Dim tmp As String
    Dim mystr As String
    Dim myLines(1 To 300) As String

    mystr = “”
    For Each Raws In Raw
    If Raws = 10 Then
    n = n + 1
    myLines(n) = mystr
    mystr = “”
    Else
    mystr = mystr & ChrW(Raws)
    End If
    Next Raws

    ‘ Extract CSV file Data to Cells
    For i = 1 To n
    Values = Split(myLines(i), “,”)
    col = 1
    tmp = “”
    For Each Value In Values
    ‘ Read the info for this program which explains the lines below
    tmp = tmp & Value
    If Left(Value, 1) = “””” And Right(Value, 1) “””” Then next_column = False
    If Not next_column And Right(Value, 1) = “””” Then next_column = True
    If next_column Then
    head.Offset(i, col).Value = Replace(tmp, Chr(34), “”) ‘ Remove ‘”‘
    tmp = “”
    col = col + 1
    End If
    Next Value
    Next i
    End Sub

  19. Thanks for the macro, it’s been a great tool, I’ve been using it quite a while now, when it broke I was happy to see it fixed. I hope you can expand on the mutual fund variables, they would be very useful. Thanks again for this tool.

    1. You’re welcome. I’m glad that is useful. I don’t think Yahoo Finance provides a list of mutual funds. I’m not sure if there is any other website offering an API for mutual funds.

  20. How would you loop through a list of ~100 stocks getting values from Yahoo Key Statistics and putting in Excel cells? I can write the simple select statement in Yahoo API (see link below) but have no idea what the result is and how to get the data into Excel?

    Yahoo API select statement link
    https://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys&debug=true#h=select+*+from+yahoo.finance.keystats+where+symbol+in+(%22YHOO%22%2C%22AAPL%22%2C%22GOOG%22%2C%22MSFT%22)

    Not that I have this what do I do?

  21. Here is what I have so far, completely lost on next steps… Do we use Yahoo API to get the xml then parse it into Excel?

    Sub Key_Statistics_from_Yahoo_Finance_API()

    Dim head As Range
    Set head = Range(“A3”)
    Dim i As Integer
    Dim Symbols As String: Symbols = “”
    Dim SpecialTags As String: SpecialTags = “”
    Dim urlP1 As String: urlP1 = “https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.keystats%20where%20symbol%20in%20(%22”
    Dim urlP2 As String: urlP2 = “%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys”
    Dim Yahoo_Finance_URL As String
    Dim rng As Range
    Dim cell As Range

    Application.StatusBar = “Macro running…updating quotes for tickers ……….COWABUNGA!”

    ‘ Clear old data and copy the ticker list
    Sheets(“KeyStats”).Select
    Range(“A3:CH1000”).Select
    Selection.ClearContents

    Sheets(“TickerList”).Select
    Range(“A2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Sheets(“KeyStats”).Select
    Range(“A3”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    ‘ Get the Stock Symbols
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ‘ Starting from a cell below the head cell till the last filled cell
    Symbols = Symbols & cell.Value & “%22%2C%22”
    Next cell
    Symbols = Left(Symbols, Len(Symbols) – 9)

    Debug.Print urlP1 & Symbols & urlP2
    Yahoo_Finance_URL = urlP1 & Symbols & urlP2

  22. Dear,

    Thanks a million for the code. I’ve been using it for a while. I have some minor issues and wish if you can guide me to fix them within your code. The issues are:

    – When I add new column before the “Head” Column, or new row above the “Head” row, the code doesn’t run, The error message is “Head is not found!”. Is it possible to make the head cell dynamic to be anywhere in the sheet?
    – When I add a column in the middle without any yahoo tag to use it for my own calculations, the data gets updated in the columns before the empty one, but the data in the columns after the empty one doesn’t get updated. The same with the rows as well. Any empty row in the middle (without the company’s ticker) will make the data gets updated in the rows above only.

    Please let me know if this can be fixed.

    Kind regards,
    Waleed

  23. It looks like:

    Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = “http://finance.yahoo.com/d/quotes.csv?s=”

    needs to be changed to

    Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = “http://download.finance.yahoo.com/d/quotes.csv?s=”

    I recently got redirect errors on the first URL.

  24. Hi there, thank you for the code it works really well.

    How do i get the pre market value or would that be the after hours one?

  25. Hey thanks for updating the code – much appreciated! It’s been very helpful to me these last couple of years and I was so glad I didn’t have to search for replacement solution.

    I made a couple of changes in the Print_CSV Sub one that speeds up the execution, namely turning off the screen updating while it writes the CSV and the other giving a status bar update message just to let you know what’s going on.

    Here’s the revised code in case anyone wants to use it:

    ‘ Download the CSV file and Print it in the cells
    Sub Print_CSV(URL As String, head As Range)

    ‘ Get the CSV file from Yahoo Finance
    Dim Http As New WinHttpRequest
    Application.StatusBar = “Getting Yahoo data…”
    Http.Open “GET”, URL, False
    Http.Send
    Application.StatusBar = “Yahoo data received”

    Dim Resp As String: Resp = Http.ResponseText
    Dim Lines As Variant: Lines = Split(Resp, vbLf)
    Dim sLine As Variant
    Dim Values As Variant
    Dim Value As Variant

    Dim i As Integer: i = 1
    Dim next_column As Boolean: next_column = True
    Dim col As Integer
    Dim tmp As String
    Application.ScreenUpdating = False

    ‘ Extract CSV file Data to Cells
    For Each sLine In Lines
    Values = Split(sLine, “,”)
    col = 1
    tmp = “”
    For Each Value In Values
    ‘ Read the info for this program which explains the lines below
    tmp = tmp & Value
    If Left(Value, 1) = “””” And Right(Value, 1) “””” Then next_column = False
    If Not next_column And Right(Value, 1) = “””” Then next_column = True
    If next_column Then
    head.Offset(i, col).Value = Replace(tmp, Chr(34), “”) ‘ Remove ‘”‘
    tmp = “”
    col = col + 1
    End If
    Next Value
    i = i + 1
    Next sLine
    Application.ScreenUpdating = True
    Application.StatusBar = “Yahoo Finance data update completed at ” & Now
    End Sub

  26. This looks to be a great program. Unfortunately I am getting a cannot find tag name error. It runs through the Sub Get_Special_Tags subroutine and does assign the tags to the specialtags array, but after 1 pass it hits to on error statement and stops. It never makes it to the pos=Application.Match statement.

  27. I recommend you use MarketXLS.
    They have regular updates and customer support unlike yahoo.
    I heard Yahoo Finance API has been discontinued.

Leave a Reply

Your email address will not be published. Required fields are marked *