Calling Yahoo Finance API for Stock Data (Excel & VBA)

8.9 min read|Last Updated: June 5th, 2024|Categories: Excel|

Using the Yahoo Finance API in Excel with VBA can provide real-time stock data, enriching your spreadsheets with dynamic information. By leveraging this tool, you can automate the retrieval of stock data directly into your Excel worksheets. In this guide, we’ll explore how to call the free Yahoo Finance API using Excel and VBA, giving you the tools to enhance your financial models and reports.

How to call the free Yahoo Finance API to get Stock Data

Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explains 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 expert team 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

Enhance your Excel experience with our specialized Excel Programming And VBA Macro Development Services, designed to streamline your workflow and meet your specific needs.

Important Update (2020)

Yahoo Finance API is no longer available. The VBA code, explained in this blog, can no longer connect to Yahoo Finance API and is not working.

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.

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.

Step 2

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

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.


' 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 consultant team if you are looking for some Excel help.

Conclusion

Integrating the Yahoo Finance API into Excel using VBA opens up a world of possibilities for accessing real-time stock data effortlessly. By mastering this technique, you can create dynamic spreadsheets that update automatically, saving time and improving accuracy in your financial analysis. Whether you’re a seasoned investor or a financial analyst, harnessing the power of the Yahoo Finance API in Excel empowers you to make more informed decisions.

Our experts will be glad to help you, If this article didn't answer your questions.

Share now:

About the Author: Hajir Hoseini

75 Comments

  1. Kolya November 23, 2013 at 5:00 am - Reply

    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.

    • admin January 12, 2014 at 9:08 am - Reply

      Agree! Yahoo is not the best option but it’s a free option.

  2. Fred January 9, 2014 at 6:10 pm - Reply

    Do you have this code in a format that can be copied? Hoping to not retype all of this

    • admin January 12, 2014 at 9:05 am - Reply

      If you double click the code, you could copy it. I’m not sure how it looks like on your computer.

  3. admin January 12, 2014 at 9:06 am - Reply

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

  4. Rana January 17, 2014 at 6:11 pm - Reply

    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 ?

    • admin March 28, 2014 at 1:25 am - Reply

      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.

      • Datong September 8, 2015 at 9:16 pm - Reply

        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.

        • admin October 26, 2015 at 2:58 am - Reply

          Sorry, it’s not on my knowledge. This sounds to do something with networking or server which is not my field.

        • Phil April 15, 2016 at 6:55 pm - Reply

          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.

  5. Matt February 15, 2014 at 10:31 pm - Reply

    How do I manipulate the code to extract data from key statistics page?

    • admin March 28, 2014 at 1:18 am - Reply

      Could you give an example of key static pages? I’m trying to understand the question better.

  6. IPL7 April 22, 2014 at 2:37 pm - Reply

    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?

    • admin April 20, 2015 at 4:36 pm - Reply

      Maybe Yahoo Finance supports other countries stock exchange. If I get any chance, I’ll look into it.

  7. Jeff May 15, 2014 at 12:45 pm - Reply

    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?

    • admin April 20, 2015 at 4:24 pm - Reply

      Sorry, I’m not familiar with this.

  8. Rob July 3, 2014 at 1:36 am - Reply

    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”

    • admin April 20, 2015 at 6:15 pm - Reply

      Done! Try the new code

  9. raul August 19, 2014 at 6:02 pm - Reply

    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?

    • admin April 20, 2015 at 4:23 pm - Reply

      I haven’t had the chance to write code for them.

  10. Josh August 29, 2014 at 1:31 am - Reply

    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

  11. Peter l September 28, 2014 at 11:31 am - Reply

    How do I test if my internet connection is via a proxy server? Can you give the code. Thanks.

    • admin November 10, 2014 at 5:37 pm - Reply

      sorry, I’m not an expert int this area.

  12. james Peter October 19, 2014 at 6:32 pm - Reply

    the VBA is very useful. tq Admin.
    Anyhow, how to make it update every 1 minute? tq

    • admin November 10, 2014 at 5:36 pm - Reply

      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.

  13. Harry December 8, 2014 at 7:26 am - Reply

    a5 and b6 not working

    both return no data but “N/A”

    would you kindly check and modify VBA lines
    thanks

    • admin April 20, 2015 at 4:20 pm - Reply

      Probably N/A is returned by Yahoo Finance i.e Yahoo doesn’t have value for it.

  14. Jimmy December 14, 2014 at 5:36 pm - Reply

    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”

    • admin April 20, 2015 at 4:19 pm - Reply

      Thank you for mention it. It’s been added now.

  15. Don Montgomery January 8, 2015 at 12:17 am - Reply

    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.

    • admin April 20, 2015 at 4:14 pm - Reply

      I’m glad it was helpful.

  16. Michael March 12, 2015 at 9:23 pm - Reply

    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?

    • admin April 20, 2015 at 4:13 pm - Reply

      Try the new code

  17. Ryan March 12, 2015 at 10:19 pm - Reply

    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?

    • admin April 20, 2015 at 4:13 pm - Reply

      I’m glad someone used it for a year! It’s fixed.

  18. ag March 13, 2015 at 2:54 pm - Reply

    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.

  19. Byron March 16, 2015 at 5:15 pm - Reply

    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.

  20. RC Parker March 17, 2015 at 3:44 am - Reply

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

    • admin April 20, 2015 at 4:11 pm - Reply

      Good Suggestion

  21. Maria March 17, 2015 at 8:33 pm - Reply

    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?

  22. EJ March 18, 2015 at 5:26 am - Reply

    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?

  23. Kolya March 23, 2015 at 5:48 pm - Reply

    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.

    • admin April 20, 2015 at 4:08 pm - Reply

      Thanks for finding the bug. It’s fixed now.

    • admin April 20, 2015 at 4:10 pm - Reply

      It’s been fixed now.

  24. Kolya March 23, 2015 at 6:04 pm - Reply

    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

    • admin April 20, 2015 at 4:10 pm - Reply

      You could RedDim Preserve to make your array bigger.

  25. duane May 8, 2015 at 6:24 pm - Reply

    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.

    • admin August 16, 2015 at 4:17 am - Reply

      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.

  26. P. Arnold July 10, 2016 at 9:45 pm - Reply

    I am running into issues using this version now, has something changed?

  27. munk August 23, 2016 at 3:46 am - Reply

    do you know if there is a tag for company description? Thanks.

  28. B November 7, 2016 at 4:22 pm - Reply

    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?

  29. B November 7, 2016 at 4:32 pm - Reply

    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

  30. sam January 1, 2017 at 5:26 am - Reply

    I need EPS (past) but Yahoo API ‘e’ (SpecialTag 16) – only provides EPS (TTM). I did found out there is EPS (Past) on Analyst estimates page. e.g. https://ca.finance.yahoo.com/q/ae?s=cxw

    I am wondering if there is any way to export EPS (data).
    Thank you

    • bSuperior System Ltd Experts May 29, 2017 at 9:15 pm - Reply

      Thanks for your comment; however, we aren’t familiar with the finance details of Yahoo API. Hopefully someone would comment on your question.

  31. Waleed February 8, 2017 at 7:50 am - Reply

    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

  32. Ramiro May 11, 2017 at 4:37 am - Reply

    The code run yet?
    I’am trying to use it, and i’am having a problem.
    When i put to run the code this is what i obtain:

    Document Has Moved

    Document Has Moved

    <FONT FACE=Helvetica
    Description: The document you requested has moved to a new location. The new location is http://download.finance.yahoo.com/d/quotes.csv?s=GGAL.BA+FB+GOOG&f=ap2.

    I don’t know what happen. Could you help me? (i’am sorry for my english, i’am argentinian)

  33. Kolya May 16, 2017 at 8:47 am - Reply

    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.

  34. George June 25, 2017 at 2:48 pm - Reply

    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?

    • bSuperior System Ltd Experts June 29, 2017 at 8:34 pm - Reply

      To my knowledge, I don’t think they have pre market value. I checked on another blog, they couldn’t find pre market value on Yahoo API.

  35. Justin August 15, 2017 at 5:13 am - Reply

    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

  36. tilbud September 15, 2017 at 7:54 pm - Reply

    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.

  37. P. Arnold November 2, 2017 at 4:21 am - Reply

    Is anyone getting:

    “Yahoo! – 999 Unable to process request at this time — error 999”?

    • P. Verheyde November 2, 2017 at 6:21 pm - Reply

      Yup, I get the same error.

  38. Harold Stevens November 7, 2017 at 9:42 am - Reply

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

  39. Joshua Babashola April 19, 2021 at 8:57 am - Reply

    Thanks for this article. I think Yahoo finance is an outdated method. There are more easier and simpler way to get stock prices directly into your Excel without using any programming language or code. I personally use https://findynamics.com/ixbrlanalyst/
    This is because historical data is dated 10 years with 15 days free trial.

  40. Joshua Babashola April 30, 2021 at 3:02 am - Reply

    You can fetch historical data easily using iXBRANALYST addin. You can download it from microsoft store. This is the easiest way i can Recommend. Let me know if that was helpful

Leave A Comment

contact us

Contact us today at and speak with our specialist.