Multiple Stock Quote Downloader for Excel

This Excel spreadsheet downloads multiple historical stock quotes into Excel. Just enter a series of ticker symbols, two dates, and click a button.

Update on 15th September 2024. Yahoo Finance made getting at the data a little trickier, but nothing that a little time and effort can’t resolve. Get the new version below!

Update on 2nd May 2022: Fixed to work past some Yahoo changes.

Update on 14th April 2018: Yahoo changed a few things, so I’ve updated the downloader to work again

Update on 11th July 2017: You can now sort the historical data in ascending or descending date order

Update on 6th July 2017: I’ve uploaded a new version of the Yahoo spreadsheet that now works again.  Download the new version at the bottom of this article. Comments welcome.

Update on 20th May 2017: Yahoo Finance have discontinued their free historical data API. As a workaround, I’ve posted a new version of the spreadsheet that uses Google Finance.

This data can be used for correlation analysis, technical analysis with RSI and ATR, historical back-testing, portfolio optimization and much more.

A previous spreadsheet let you download data for a single ticker symbol. Sometimes, however, data for just one company isn’t enough – you need to compare the relative performance of several companies. That’s when you need this free, user-friendly spreadsheet for bulk data download.

The spreadsheet is simple to use. Start by entering a start and end date, your desired quote frequency (d for daily, m for monthly, y for yearly), and your desired sorting (oldest data first or newest data first).

Excel spreadsheet to download multiple stock quotes from Yahoo Finance

You can also specify if you want the data for each ticker written to separate CSV files. If so, specify an export folder for the files.

Then enter a list of ticker symbols in cell A11 and below (one tick per cell). After you click “Get Bulk Quotes”, the spreadsheet downloads the historical stock quotes into individual sheets. The sheet name is the ticker symbol.

Yahoo Finance in Excel

The spreadsheet downloads the date, open price, high price, low price, closing price, volume and adjusted close price.

AAPL Yahoo Finance

If you add or remove tickers , or refresh the data, the spreadsheet deletes the existing quote sheets, and inserts new sheets with the new data.

If you asked the spreadsheet to export the data, you’ll find a CSV file for each ticker in the folder you specified.

Yahoo Finance Stock Quotes Exported to CSV Files

The file name is constructed from the ticker, start date, end date, and the download frequency.

If you misspell a ticker or leave a blank, the VBA is clever enough to skip over or ignore the error. You won’t get any nasty error messages.

Yahoo Finance VBA

You’ll also get a list of tickers for which no data was found. This list is dynamically updated by the VBA.

I’ve tested the spreadsheet by downloading historical quotes for 180 ticker symbols. Everything worked perfectly, with 180 new sheets added to the workbook, each filled with historical data.

You can also collate the open, high, low, close, adjusted close and volumes for every ticker on the same sheet. That is, you can collect all the open prices on one sheet (named “Open”) together with the corresponding dates, all the high prices on one sheet (named “High”) together with the corresponding dates etc. Simply check the “Collate” button.

stock quotes for multiple tickers collected onto one Excel spreadsheet

The collate feature correctly reconciles tickers that return data with differing time series – dates and values are correctly associated.

The VBA is not protected; you can view and modify the code. Please let me know if you have any suggestions for improvements or additions to the functionality.

Get Excel Spreadsheet to Download Bulk Historical Stock Data from Yahoo

Get Excel Spreadsheet to Download Bulk Historical Stock Data from Google Finance


556 thoughts on “Multiple Stock Quote Downloader for Excel”

  1. Hi Samir, your resolving the Yahoo API issue by calling Google Finance services was as fast as I would expect from a subscription service. As such I felt the need to donate, thank you for your hard work!!

    Reply

  2. Hi Samir,
    Thank you very much for your fast spreadsheet update with google.
    I tried several time with the following tickers and it doesn’t seem to work :
    EPA:SQI
    EPA:CTRG
    EPA:VETO
    EPA:DLT
    EPA:ALMIL
    EBR:VGP
    EPA:CEN
    EPA:HCO
    EPA:ALTVO
    EPA:STAL
    EPA:EXAC
    EPA:SDG
    EPA:PSB
    EPA:SIP9
    EPA:ALPLA
    EPA:FEM
    EPA:NRG
    EPA:PGP
    EBR:IBAB

    Can you help?
    Thank you very much

    Reply

    • Type those ticker into Google finance and look at the historical prices. If you don’t see a Download CSV link, then the spreadsheet can’t download the tickers.

      Reply

  3. I’ve been trying to use Google Sheets to get back our old capability.

    There are two problems with Google Sheets.

    1. It doesn’t give us “Adjusted Close” prices AND that is extremely useful to track total return on a stock. It seems like the same problem that Samir has using his new Google based BulkDownloader.

    I did notice that if you just look up the historical data for a stock “manually” on finance.yahoo.com, then “Adj . Close” is in column 6 and “Volume” is in column 7, but in Samir’s previous Yahoo downloader, “Adj . Close” is in column 7 and “Volume” is in column 6. Is there any chance that the “Adj . Close” data is there at finance.google.com, but we just aren’t looking in the right column?

    2. Of the 200 stocks I am currently track there are some that Google Sheets does not return data for, and some for which you have to find the correct prefix for.
    Examples:
    Need to enter ^MSH as INDEXNYSEGIS:MSH
    Need to enter ^RUT as INDEXRUSSELL:RUT
    Can’t retrieve data for AMT or ETP with Google Sheets even though the Google.com/Finance website will return that data. When it does return, them it returns them as NYSE:ATM and NYSE:ETP, but if I enter NYSE:ATM and NYSE:ETP into the Google Sheets function GoogleFinance(), then it still returns no data.

    So, we are still far from the capability we had with the Yahoo Finance API.

    As noted by Terrance Harter above, https://ichart.finance.yahoo.com/……. returns that silly “error” message he discussed, but https://download.finance.yahoo.com/……. lets you retrieve at least some limited data.

    I don’t know what data are available at https://download.finance.yahoo.com/……., and don’t know yet how to find out.

    Hopefully this discussion doesn’t offend anyone (especially Samir). I’m just a newbie desperate to resurrect my old capability

    Mike Henry

    Reply

  4. Samir-

    #1 Yahoo API historical data is still available for free.
    I am able to get the historical pricing via Yahoo through R and the quantmod after making a few tweeks.

    #2 The price history URL’s appear to have changed:

    Old https://chart.finance.yahoo.com/table.csv?s=AAPL&a=2&b=17&c=2017&d=3&e=17&f=2017&g=d&ignore=.csv

    New: https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1492438581&period2=1495030581&interval=1d&events=history&crumb=XXXXXXX
    I.E.
    The “Download Data” link on the historical data page for AAPL is now:
    https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1492379145&period2=1494971145&interval=1d&events=history&crumb=sgdBTt48gg.

    The new version appends a “crumb” field which appears to reflect cookie information in the user’s browser. It seems they are intentionally blocking automated downloads of price histories and forcing queries to provide information to validate cookies in a web browse

    Reply

  6. Hello Samir I am glad I found your website thank you !

    I have one question for you is it possible to get TSE data from google historical data from the excel file you did ( Multiple Stock Quote Downloader Google Finance )

    For example I am trying to get historical data of the big banks for example https://www.google.ca/finance?cid=674745

    When I write TSE:RY it fail to grab any data am I doing something wrong ?

    Thank you

    Reply

  7. Hi Samir,
    great job, many thanks!
    The google downloader works well for US stocks but I cant get Canadian stocks to work. I tried the prefix TSE: (example TSE:TD) but it doesn’t work.
    Any idea?
    Thanks again
    Thomas

    Reply

  8. Samir,
    Many thanks for the switch to google source, I was lost trying to figure out google sheets
    What a kludgy POS
    Tim

    Reply

  9. Hi Samir,
    I can’t use your Google spreadsheet because none of the tickers that I’m interested in are supported by google api (There is no Download CSV link for them).

    I know that the yahoo link as changed, so, I tried to modify your Yahoo spreadsheet by adding start and end dates in UNIX format in cells D5 and D6 (Unix time is the number of seconds since January 1, 1970) and the crumb in cell D8 (crumb is a key that you can find by clicking right on the download link here :https://finance.yahoo.com/quote/WMT/history?period1=1464472800&period2=1496008800&interval=1d&filter=history&frequency=1d).

    In visual basics, I changed : qurl = “URL;http://ichart.finance.yahoo.com/table.csv?s=” + stockTicker + “&a=” + StartMonth + “&b=” + StartDay + “&c=” + StartYear + “&d=” + EndMonth + “&e=” + EndDay + “&f=” + EndYear + “&g=” + freq + “&ignore=.csv”
    by
    qurl = “URL;http://query1.finance.yahoo.com/v7/finance/download/” + stockTicker + “?period1=” + Worksheets(“Parameters”).Range(“$d$5”) + “&period2=” + Worksheets(“Parameters”).Range(“$d$6”) + “&interval=1d&events=history&crumb=” + Worksheets(“Parameters”).Range(“$d$8”) + “&ignore=.csv”

    But obviously, this was too simple and there is a mistake.
    I’m pretty sure a trick like this could work but, I’m not good enough with your spreadsheet. Do you have any idea of the change we need to do to make this work?

    Thanks again Samir

    Reply

  10. Hi Samir,
    Thanks for the Google update. It works well with exchange stocks, but I have not been ale to download index values (e.g., INDEXDJX:.DJI) or mutual fund prices (MUTF:xxxxx)
    Michael S.

    Reply

  14. Hi Samir,

    I have used your Yahoo Finance multiple quote stock downloader for a few years now. Your program is very helpful in downloading multiple historical stock prices for several stocks at one time. Thank you for creating this! Are you planning on releasing a new Excel file that is able to download Yahoo Finance share price data via the new Yahoo download code? If so, when do you expect to have a new version available? Once again, thank you for your program. They are very helpful!

    Regards,

    Erik

    Have a great day!

    Reply

  15. Samir,

    Appreciate this template very much! I was wondering if it is possible, and if so how, if I can carry the macro over to my stock tracking spreadsheet along with the parameters tab and have it still work without opening up the original template file. Right now I can move the ‘parameter’ sheet over to my stock tracking file but when I click the macro button it both loads the information on my current stock file and opens up the original template as well. Is there a way to bring the macro over to my file so it does not open up the original template every time? And if so, is there a way I can make it so when it gets the quotes it only creates the ‘close’ tab and no others?

    Thanks Samir!

    Reply

  16. Does anyone know how to pull the S&P 500 Index in the Google-based downloader tool? ^GSPC worked for the Yahoo-based tool, but not for this one. The Google website uses INDEX:.INX but that doesn’t seem to work either.

    Reply

  17. Hi Samir,
    Thank you for kindly providing your excel spreadsheet for downloading multiple tickers.
    I have been trying to download historical stock-data from two European stock-exchanges. Tried Frankfurt, (FRA:BMW). No problem! Then Copenhagen (CPH:VWS) and didn’t get any data. By the way, Google finance has historical data for both stocks.
    Any suggestions! Really need to be able to get data from the Copenhagen stock exchange. Any suggestions are most welcome.
    Kind regards, and beforehand, thank you very much
    Erik

    Reply

  18. Samir …

    GREAT spreadsheet. Thank you.
    I am running across a problem in the Google Finance worksheet that I do not remember having with the Yahoo Finance worksheet. I am not sure why because the same issue should cause both worksheets to “hiccup”. If I have LOW (Lowes) in my list of stock tickers the VBA program aborts at the line of code that adds a “Low” worksheet at the end.

    Any suggestions?

    Thanks,
    Randy

    Reply

  21. Hi Samir. I love this excel spreadsheet. The new google version works well, the only problem is that I seem not to be able to download anything from the Toronto Stock Exchange (TSE:xxx is the syntax on google finance). Could you let me know if this is a simple fix?

    Reply

  22. Hi Samir,

    Hope you are doing good. For Indian stock exchanges like BSE,NSE I am not able to get data from Google finance.
    Please do the needful. It works for other exchanges, but not for Indian stocks. Please let me know alternative.

    Thanks
    Venkat

    Reply

  23. I tried to find free or at least cheap alternative and found this one: https://eodhistoricaldata.com/. It provides the data with CSV, exactly the same format as Yahoo Finance, then it’s easy to switch, just by replacing URLs.

    In opposite to Google Finance, EOD has a lot of data and supports everything you can find in Yahoo (as for me at least). And also it’s very easy to switch from Yahoo to EOD: https://eodhistoricaldata.com/knowledgebase/adapt-old-yahoo-scripts-eod-historical-data/.
    Basically, you just need to change one URL, since they built a special API for Yahoo users.

    Reply

  24. Hi Samir,

    Now that the Yahoo API doesn’t work would you please be able to provide me with the password to access your old Yahoo API code – or release an unlocked version now that Yahoo no longer supports it. I am a student studying finance and programming and would love to play around with that model. I feel like it was a lot simpler than this new Google one. It just pulled data and that’s it. All the additional tasks of the Google code confuse me a bit.

    Thanks!
    yagazosuji@gmail.com

    Reply

  26. I appreciate the recent change from Yahoo to Google to download bulk stock quotes. The American market portion works well, however in trying to download some Canadian stocks quotes it shows “failed”. Could you provide a solution so that the Canadian quotes will be successful also.

    Reply

  27. Hi Samir,
    Many thanks!
    The google downloader works well for US stocks but I cant get Denmark stocks to work. I tried the prefix CPH: (example CPH:NOVO-B) but it doesn’t work.
    Any idea?
    Thanks again

    Reply

  28. Hi Samir,

    Incredible job! This spreadsheet is everything that I always wished for my quantitative analysis. Thank you so so so much for the contribution

    However I cannot access index data with your spreadsheet:

    I type INDEXDJX:.DJI for Dow Jones or INDEXBME:IB for IBEX 35 and the information cannot be downloaded.

    The historical data for these instruments is available in Google Finance so i cannot understand what is the problem.

    Thank you very much in advance

    Reply

  29. Samir, excellent work, thank you so much.

    Just a little problem: It will not download index values, e.g. for INDEXDJX:.DJI, INDEXSP:.INX, INDEXNASDAQ:.IXIC, INDEXRUSSELL:.RUT, INDEXCBOE:.TNX, or INDEXCBOE:.VIX, although they are there, cf. https://www.google.com/finance/historical?cid=626307&startdate=Apr%2029%2C%202009&enddate=Jun%2016%2C%202017&num=30&ei=2u9DWem8GJTAswGf8LqQBg&start=2040.

    Any ideas? Once again, I cannot thank you enough for providing us with such a valuable tool.

    Reply

  31. Hi Samir,
    I have been using a customised version of your yahoo finance historical downloader for some time but obviously it’s not supported by yahoo anymore. I tried you google downloader and it work for US Stocks but I cant get it to work on Australian stock market ASX.
    An example is Woodside Petroleum ASX:WPL which works on https://www.google.ca/finance?cid=674745
    However putting the ticker ASX:WPL and a few variations like WPL:ASX does not work. Any ideas for me. I appreciate the work you do very much.

    Reply

  32. Samir,

    this is awesome thanks a lot.
    how can i eliminate the Stock Quotes on cell A1.
    can this start on A1 cell with date?

    thanks again.

    ben

    Reply

  33. hi Samir, there is a function in Pyhton called fix_yahoo_finance(), which allows to fix the problem with downloading data from Yahoo finance. is there anything similar which we can you do download data straight to excel now?

    Reply

  34. Hi Samir,

    Thanks for a great tool. I have downloaded the spreadsheet that uses Google Finance, but I am getting an error:

    Run-time error ‘9’:
    Subscript out of range

    When I click “Debug” it takes me to:

    Worksheets(“Parameters”).Select

    Any tips on how to fix this?

    Many thanks
    Same

    Reply

  36. Hello Samir,
    U have always been a day saver when it comes to stock analysis. I used your bulk downloaded that fetched results flawlessly for all indian stocks (Both NSE and BSE). But i am somehow not able to download data for any of the stocks listen on NSE and BSE from your google stock downloader. I double checked the tickers used by google for a particular stock i want data for but its not just populating results. It is showing the ticket in failed for the stocks catagory. Pls help and suggest and solution. 🙂

    Reply

  37. After the May demise of Yahoo historical data, I have also changed to Google Finance. My query code is the same your download and my portfolio contains mutual funds in addition to equities. When adding a mutual fund, both mine and your spreadsheet do not find the mutual fund. The Yahoo (before May) downloaded my mixed in mutual funds.

    Is there a Google Finance that can download mutual funds closing price so I could On Error call a different query code? I like to calculate RSI, etc.

    Thanks, Bill

    Reply

    • I’ve uploaded a new version of the Yahoo spreadsheet that should work. Yahoo have made it much harder to download historical data, but it’s still possible. Please use the new version and give me your feedback.

      Reply

  38. Hi Samir,

    there is a small error in the new google spreadsheet. I found out that the frequency that was used in the yahoo spreadsheet isn’t necessary anymore. So I am getting an error while creating the *.csv files from my stocks. I fixed the error by editing the following two lines in the “Module1” -> “CopyToCSV”:

    frequency = “d”
    MyPath = Worksheets(“Parameters”).Range(“$b$7”)

    The frequency is now set to a static value “d” for daily. As mentioned above it is not used anymore by the google spreadsheet. The second line directly below links to the path where you want to store your stock files. I just put in the correct value. Now it works perfectly.

    Thanks for those two (the yahoo one i used before) and the google spreadsheet i use now. They are awesome.

    Greetings

    Reply

  39. Hi Samir,
    you have some incredible spreadsheets!! The new yahoo one works well, thank you! However, I notice yahoo does not have historical prices listed for 05-06July2017 on some stocks. ABEO, DGAZ, DUST, JDST, PIRS, SPEX as example. It must be an issue with them since Google does list these on the google website.

    I am having problems with your new and old google spreadsheet working at all this afternoon, 07July2017. The old spreadsheet worked this morning. Any insight would be great appreciated.
    Thanks again! jim

    Reply

    • Not sure what happened or if there was a difference I should watch out for but I’m happy to report the google ones are back working again this morning. Superb! I find google’s data more consistent and prefer using that one. Great product! Genius! Cheers,

      Reply

  41. Hi Samir,
    Thanks you a lot for this updated spreedsheet, it saved me!
    I have just a questions for you, is that still possible to make a datequote function ?
    Thanks again !

    Greg

    Reply

  44. Date sort order is reversed in the new edition? The oldest date is now at the top – formerly was the most recent date. Any plans to update/change this for consistency?
    Samir, thank you for this AWESOME app!
    Steve

    Reply

  45. Hi Samir,
    Great work on getting the Yahoo sheet updated. I got an ActiveX error and debugged it to this line ” Set objRequest = CreateObject(“WinHttp.WinHttpRequest.5.1″) ” . I’m using Excel 2016 for Mac . What do you think is the issue ?

    Reply

    • Claudio, what I do is to replace the null cells with value from the cell directly above (the null cell). I work on the assumption that null cells means non-trading days and take the value of the previous trading day. I use something like this :

      Dim r As Range
      Range(“A2:F269”).Select
      For Each r In Selection
      On Error Resume Next
      If r.Value = “-” Then
      r.Value = r.Offset(-1, 0).Value

      Reply

  47. Hi Samir,

    I get the following error: startDate = (Sheets(“Parameters”).Range(“startDate”) – DateValue(“January 1, 1970”)) * 86400

    Regards.

    Reply

    • Hi,
      in VBA code, search the below section in Module1 and change the month January
      [code] DataValue(“(January, 1, 1970”)) [/code]
      to the number 1 (one), in this manner:

      [code]

      ‘Convert user-specified calendar dates to Unix time
      ‘***************************************************
      startDate = (Sheets(“Parameters”).Range(“startDate”) – DateValue(“1, 1, 1970”)) * 86400
      endDate = (Sheets(“Parameters”).Range(“endDate”) – DateValue(“1, 1, 1970”)) * 86400
      ‘***************************************************

      [/code]

      There is a problem with Excel’s internal dates and your system format date.

      Reply

  48. Samir,

    You are a genius and a life saver. Google Finance never worked for me as my systems relied on monthly adjusted close data. Glad to finally have it back.

    I’ve noticed a couple of glitches with the data however in the new spreadsheet. It appears that the data for the Close is actually for the Adjusted Close value.and vice versa. In the collated tabs, the Close tab has the Adjusted Close value, the Volume tab contains the Close data and the Adjusted Close tab has the Volume data. Also, the last date on each tab is formatted as a number, not a date as in the other rows.

    Thanks for all your help

    Reply

    • Thank you for your comments. Please share investexcel.net on your social media channels or blog if you’re happy with it.

      I’ll make those corrections and re-upload

      Reply

  49. The latest update seems to have fixed several issues… thanks! One more issue I am seeing is that the last date in the specified range is being dropped. It is in the individual spreadsheets if I download them straight from Yahoo, but the spreadsheets always omits the last date specified in the End date field.

    Maintenance of this sheet is greatly appreciated!

    Reply

  50. Hey Samir,

    I’m getting a Microsoft Visual Basic error. “Run-time error ‘429’”: ActiveX component can’t create object”
    I have to click abort or debug. Running macOSx – Excel 2016 (latest on mac)
    Any advice?

    Reply

    • Its Alan again, I also tried the latest Yahoo version on my work computer (PC based) and received this error: “Run-time error ‘-2147012739 (80072f7d)’: An error occurred in the secure channel support” – Sorry, both of these were tested with the Yahoo version.

      Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.