Download historical stock prices into Excel

Spreadsheet gives you both CSV and JSON interfaces

Download Historical Stock Prices Into Excel

There's a lot to like about Yahoo Finances' historical prices. Firstly it covers so many (over 75) stock exchanges and so many different types of security--stocks, ETFs, mutual funds, indices, commodity futures, currencies and cryptocurrencies. Secondly, its free, at least for now. Our spreadsheet, HistoricalPrices, combines our legacy CSV download product (EmulateURL) and the more recent and extremely versatile JSON based download (JSON Fetch) into one convenient spreadsheet which is highly adaptable to your needs and gives you full access to well-commented VBA, should you need to do any coding. As a bonus we also include the PriceOnDate user defined function (UDF) which allows you to download the historical stock price data into Excel of any security on a given date.

CSV Historical Prices

The Comma Separated Value interface was the mainstay of Yahoo Finance historical prices for many years giving access to Daily, Weekly and Monthly OHLC data and also Dividends and Split history. Yahoo complicated the ability to download historical prices into Excel as CSV in May 2017 by adding a cookie and crumb validation. As it turns out you can still access the data. To minimize the impact on existing users, HistoricalPrices includes a VBA function which takes the original URL as an argument. With this function--EmulateURL, you can fix your broken worksheets with minimal changes to your existing VBA code.

JSON Historical Prices

Yahoo Finance also have a JSON (JavaScript Object Notation) based interface which is faster, gives far better resolution (to 1 minute) and is near real-time for several exchanges. HistoricalPrices also includes an interface for this JSON based data so you can readily have a fallback if you need one.

Watch the video below to see HistoricalPrices in action!

HistoricalPrices–Import Price History into Excel

The HistoricalPrices spreadsheet


Lets you download historical stock price data into Excel. With examples of how to use the functions, as a .xlsm file. You will need to enable macros to run it. Version 1.0
All VBA code is accessible, well commented and digitally signed by Algorithm Science.

Requires Windows and Excel 2007 or better.

 

How to emulate the Yahoo ichart historical prices URLs in Excel VBA--The EmulateURL function

In this section we talk about the VBA code so you can fix your existing spreadsheets using the EmulateURL function with minimal changes. The disabled URLs looked like this:

 

http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=05&b=06&c=2000&d=05&e=06&f=2017&g=w

 

Yahoo disabled URLs of this type around May 2017, by adding a cookie and crumb so that only browsers could access the historical prices. EmulateURL is a VBA function which emulates the output of these URLs by adding a working cookie and crumb. I'd like to thank Scott Lindsay at http://www.xlautomation.com.au as the inspiration for a certain essential elements of the routine, particularly the use of the winHTTP library.

How to use it

EmulateURL is a VBA function which needs only two arguments:

  1. The ichart URL you wish to emulate (as a string)
  2. The spreadsheet location where you wish the output to go (as a Range object)

The ichart URL is the exact same URL that you were using up until Yahoo! turned off the service in May 2017 so changes to your code will be minimal. The URL itself has many arguments depending if you want daily prices (&g=d), weekly prices (&g=w), monthly prices (&g=m), dividends (&g=v) or splits (&g=s). Start date is given in the a,b and c parameters, end date is given in the d,e and f parameters. The stock symbol (&s) must be understood by Yahoo!

For example the VBA code:

    

MyURL = "http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=05&b=06&c=2000&d=05&e=06&f=2017&g=m"    

EmulateURL MyURL, Sheet1.Range("A1")

 

Gives you the following:

Historical stock prices

If you also want to download historical stock prices into Excel in CSV format, you can add the spreadsheet location:

 

     EmulateURL MyURL, Sheet1.Range("A1"), Sheet1.Range("J1")

 

Giving you, (in addition to the above):

Historical stock prices

There's also options to:

  1. Sort in ascending or descending date order.
  2. Skip over rows with zeros in the prices, or bad formatting
  3. Read out the raw data from the underlying queries

Leaving a comment and registering for update notifications

If you use the code and find it useful or faulty, please let me know either way in the comment box below--I'd appreciate it. If you would like to receive notification of updates you can register (no junk mail I promise).

Many thanks,

--Andrew MacLean

Algorithm Science

96 Comments

  1. Awesome! I looked at switching to Google data which is also free, but it didn’t include adjusted close or dividends/splits, so not much use to me. I was ready to switch to a paid subscription, then I found this page. Its great, I don’t even have to change the URLs I was using or the symbols. Thank you so much Andrew.

    • Neil,

      Thanks for the kind remark. It wouldn’t work for me either this morning, the URL wasn’t responding properly, but now it seems to be back. Maybe you could try again. You could also up the URL retry count in the VBA code.

  2. this is great,thank you. Do you know of an easy way to get downloads for multiple tickers at once? I found another spreadsheet online (Bulk Dividend Downloader) that does this, but it hasn’t been updated for the new Yahoo API issue, and I can’t figure out how to integrate your download code into that spreadsheet. thanks.

  3. Thank you for providing this. Much appreciated, however I can not get it to deliver the correct prices for e.g. VWS.CO. The prices I get are a factor 10 ^ 6 too big! Don’t know how to adjust the code or format the columns to get the right prices. What to do? Any ideas? On beforehand thank you.

    • This is interesting. I get the same data with EmulateURL as I get with the Yahoo website (around $600) so I don’t think it has to do with the data. I wonder if it has to do with localization for your Excel or operating system. Try formatting your columns as a number instead of currency.

    • Hi Nagini:
      It is working for me. Can you tell me a little bit more about the data you are requesting? Symbol, frequency, dates etc. Thanks, Andrew

    • I think this is a language issue. I received the same error message (German Win7 + Office 2010). As soon as I switched date and figure formats to Englisch (USA) it worked fine. You can do this in Windows Settings, Region and Langue, Formats.

      I was wondering if there is a simple way to duplicate the sheet including Macros. I copied the sheet but unfortunately the Click Button does not work in the copied sheet, but only in the original one.

      • Hi Nagini,
        I’m looking into the localization issues. On the click button issue, you just have to insert a shape and assign a macro with it (right click the shape and select Assign Macro). However, my example spreadsheet also has named cells which are referred to in the code. You can see them all if you go to the Formula tab and click on Name Manager. You can duplicate the names in your sheet, or refer to the cells using “$A$1” notation in the code.
        Andrew

  4. On the volume differences, it could be that one includes off hours trading and the other does not…just a thought.
    Thank you for all you do.
    Kind regards,
    Rob

  5. Hi Andrew, Thanks for working to solve this frustrating new problem with the Yahoo Historical Data API. I converted your VBA logic to Java using Apache Http Connection package. Everything seems to be working great. I will continue testing…

    Once it is fully tested, I will add it in my application.

    Thanks again, let me know what you think?

    https://github.com/bigttrott/thebubbleindex/blob/master/application/thebubbleindex-standalone/src/test/java/org/thebubbleindex/data/test/YahooCrumbTest.java

  6. I’ve noticed that mutual fund daily prices are not current as are regular stock prices. However, they are current when viewing the prices directly on the Yahoo Web site.

    • As of today (Sat July 8th 2017) the daily data for mutual funds from EmulateURL only appears to go up to July 3rd. If you click on “historic data” on the Yahoo website, that data table also goes to July 3rd. So that is consistent, however Yahoo also give a current price at the top of the page for the July 7th close. This price does not appear in the query so you would need to do a separate query for the current price. I don’t know why there is no data for the 5th and 6th (4th was a holiday). Anyone know?

  7. Hi

    I’ve noticed a problem with UK listed stocks (share codes ending in “.L”). It seems like a similar problem you had previously which you fixed with the newer version.

    Once again, much appreciated.

    • Thanks for spotting this Neil.
      Actually, the problem seems to be with all stocks, not just .L . On the EmulateURL output and on the Yahoo web page, the Close column is now showing the Adjusted Close price, and the Adjusted Close column is showing the actual Close price. I would expect Yahoo will fix this soon, otherwise I will need to issue a temporary fix.

  8. Hi Andrew,
    Once I had discovered XL Automation, and from there the link from their website to yours I realised I had (at least for the time being) resolved the problem of how to download stock prices from Yahoo finance. It mostly works beautifully and I am very happy that the results compare closely with the data I was getting prior to the URL change. So many thanks for that.
    I just have these comments:
    The end-Date mostly works but if the end date is not today’s date it seems to prefer the latter.
    The start-Date is not honoured – it allows quite a cushion of dates prior to my given start-Date. The best I can say is that it somehow seems to end up switching start_day and start_month values. Is this possible? By the way are they string or integer?
    It has correctly applied the adjustment to ‘adjusted close’ prices, which is great. The values are very close but not exactly the same as the adjusted prices used to be.
    Most curious of all, I get access all the tickers I am looking for, except YHOO itself. Do you know why this is so??

    Best regards, Clive.

    • Hi Clive
      Are you using the EmulateURL VBA function, the GetHistoricPrices.xlsm or the EmulateURL-V1.0b.xlsm spreadsheet? The start_day vs start_month issue may be explained by the fact that in the UK the date is expressed dd/mm/yyyy, but here in the USA we use the less logical mm/dd/yyyy . Could that localization explain this behavior? EmulateURL takes the month, day and year as part of the URL strings. In the GetHistoricPrices.xlsm spreadsheet I convert the spreadsheet date cell d to strings within the URL using:

      qurl = qurl & “&a=” & Format(Month(d) – 1, “00”) & _
      “&b=” & Format(Day(d), “00”) & _
      “&c=” & Format(Year(d), “0000”)

      It works OK for me here in the USA.

      The adjusted close price and the actual close price are currently interchanged, a major bug in the Yahoo data. I’m waiting to see if this is corrected soon. See note at the top of the web page. I don’t know if this explains the discrepancy you are seeing.

      Yes, its true, you can’t get historical data for YHOO from Yahoo. Its a mysterious thing.

      Andrew

      • The date issue is now fixed. It version 1.0e works in the USA and Europe. Thanks again to Dr. Volker Wendel for testing this in Germany.
        Andrew

  9. Thanks Andrew, I’m using the EmulateURL function inserted as a module in my macro by copying and paste-ing from the text version of EmulateURL-V1.0b.xlsm.
    I now realise that the YHOO ticker has become AABA since about 18th June.
    Regards, Clive

  10. Dear Andrew,
    Really appreciate for your excellent VBA function which really did me a VERY BIG favor.
    This function/tool has helped me to fix the Yahoo Finance Historical data query in recent days and you also provide the solution for the Close/AdjClose problem timely.

  11. Dear Andrew,
    I notice that the “Close” data seemed to be “adjusted” when a stock’s split happened. I thought your V1.0c already fixed. However, I can even find O-H-L-C numbers like 30,31,29,26(adjusted Close even lower than the low value in a day). Is there any way to bypass the “adjusted Close” and just get the current actual “Close” data? Thanks!

    • Hi, could you please provide the symbol or symbols of the faulty stocks so I can take a look and try to figure out what is going on?
      Thanks,

      Andrew

  12. Hi Andrew,
    I can now only find such as symbol “1264.TWO” split on 7/4 and the “Close” data before day 2017/7/3 were adjusted which are not the original data. But seems many cases become normal again. Really don’t know why. Thanks!

    • Hi Cecil,
      Sorry for the late response. I tested it on many ETFs (SPY, ERX, sector ETFs) and it works fine. Can you tell me which ones are not working?
      Thanks,
      Andrew

      • Actually, it seems more general. For example, the list below does not show up (MKTX works sometimes, sometimes not):
        BCR
        AEIS
        MKTX

        Thanks….
        Cecil

        • Hi Cecil
          They all work for me, I tried a few times with no failures. I wonder if there is something different about your code or environment. If you want to send me your excel file I can take a look.
          Andrew

  13. I had to fix unixdate formula to make it work worldwide.

    UnixStartDate = (DateSerial(StartDateYr, StartDateMo, StartDateDay) – #1/1/1970#) * 86400
    UnixEndDate = (DateSerial(EndDateYr, EndDateMo, EndDateDay) – #1/1/1970#) * 86400

    • Hi Vincent,

      Thanks so much for pointing this out. My use of the DateValue(“Jan 1, 1970”) is really only good where “Jan” is understood by the date system localization. I will change this, however I will use DateSerial (1970,1,1) instead of date literals, not that #1/1/1970# is ambiguous, but I believe its best practice to use DateSerial since date literals can be ambiguous (e.g. #4/5/1970# could mean April 5 or May 4 depending where you are in the world, correct?).

      Andrew

  14. Dear Andrew,

    how to do this step by step:
    If you also want the output in CSV format, you can add the spreadsheet location:
    EmulateURL MyURL, Sheet1.Range(“A1”), Sheet1.Range(“J1”)

    • Hi Marzuki,
      Yes, your code snippet should work. You can also overwrite the data at A1 if you only need the CSV data. Are you having any issues?
      Andrew

  15. Andrew — They also now work for me. I suspect intermittent internet was the problem. Thanks for your patience.

    Cecil

          • Hi Andrew I solved using another way. This is my solution:

            To replace:

            If IsNumeric(OutCol(ThisColumn)) Then D(ThisColumn) = CDbl(OutCol(ThisColumn)) Else D(ThisColumn) = 0

            with:

            If IsNumeric(OutCol(ThisColumn)) Then D(ThisColumn) = CDbl(Replace(OutCol(ThisColumn), “.”, “,”)) Else D(ThisColumn) = 0

            And to replace:

            If ThisColumn > ColumnMax Then Comma = “” Else Comma = “,”

            with

            If ThisColumn > ColumnMax Then Comma = “” Else Comma = “;”

    • Hi Ran,
      Thanks for testing this for me. I’ve put up a new version (1.0e) which I hope will fix this issue. If you get a chance, please try it out.
      Thanks,
      Andrew

    • Hello Matt. The webquery only accepts one symbol. But, you could code it up in VBA to call EmulateURL several times.

  16. Thank you very much sir ,,,,,
    if would allow me , I am new to excel , and i saw your answer to Matt : Hello Matt. The webquery only accepts one symbol. But, you could code it up in VBA to call EmulateURL several times.
    could you please elaborate more on how to that ?
    much thanks !

    • Saleh,

      I’m not encouraging the use of the code for bulk downloads of many symbols at this point. I think it could put a strain on the servers and lead Yahoo! to obscure the service even more.

      Andrew.

  17. There is a problem when using ending date for a Monday. Your URL seems to be correct but yahoo will only return data for a Friday and drop request for Monday. Is this a problem with code at Yahoo?????

    • Thank you Jonathon, I’m sure MarketXLS is a fine product. Current pricing for the version with historical prices and quotes is $240 per year. If folks just want to get live quotes (one minute delay for most US stocks, kudos to Yahoo!) and historical data into Excel they should also consider using the JSON fetch spreadsheet. It currently costs zero dollars a year and is supported by Algorithm Science.

  18. Andrew
    This is great, and seems like the best solution to the issue I had since yahoo stopped their service. Also it helps that the codes including currencies & indices are all the same.

    I do have a question which I’m hoping you can give me some guidance on: I’m looking at pulling in the price (either 20 min delay or last close price) for ~150 stocks across different exchanges. Is it possible, and where best to put the loop that would churn through the codes and just keep on adding to the bottom of the table?

    • Hello Deepak,
      Glad you like it. If you are going to loop, use the JSON interface https://www.signalsolver.com/JSON as it is faster (only uses one query) and prices are only delayed by a minute or so. However, I do discourage looping because it chews up server bandwidth. I’m looking for alternatives.
      Andrew

  19. I see the Aug 8 note from Vincent and your Aug 14 reply.
    I’m in Australia using dates dd/mm/yyyy and getting an error when having a Start Date of 2/1/2018 and End Date 14/1/2018. The error message ends with “Invalid input – start date cannot be after end date. startDate = 1517443200, endDate = 1515888000′)))”
    Please advise of changes to code that I should make.
    Version 1.0f

    • Hi Kevin,
      Try changing the format of the date cells (right click->Format cells) to use a Date format Which is preceded by an asterisk. These formats respond to changes in regional date settings.
      Andrew

      • Hi Andrew
        I’ve interpreted Vincents’ info above and the following at Ln 172 & 173 appears to be working.

        UnixStartDate = CStr((DateValue(StartDateDay & “/” & StartDateMo & “/” & StartDateYr) – #1/1/1970#) * 86400)
        UnixEndDate = CStr((DateValue(EndDateDay & “/” & EndDateMo & “/” & EndDateYr) – #1/1/1970#) * 86400)

        You may recognize other problems with this and if they are likely, I would appreciate your further advice.
        I shall continue on to investigate whether this suits me long term.
        A contribution will be forthcoming in the event that I make permanent use of your methods.
        Thanks for the inspiration to continue to find a solution for my intended use.
        Kevin

        • Hi Kevin,
          Not sure why #1/1/1970# would work better than DateSerial(1970,1,1), but I guess it does. I plan to change it back to date literals in the next release.
          Andrew

  20. The worksheet is named “Historical” and in the VBA Module 1 the worksheet object is named “HistoricalSheet” in line 4. It works, but why?
    Very nice programming, thanks

    Dennis Loeffler

    • Hi Dennis,
      Its a bit obscure 🙂 but each worksheet object actually has two names, the name on the worksheet tab and a “codename” which is the permanent name of the object inside of VBA. If a user changes the tab name and the VBA code refers to it, everything breaks, so a coder will generally use the “codename” instead as it is less likely to be changed by the user. See here for more info.

  21. Hi Andrew,

    Thank you very much for your sharing. I downloaded your VBA code and it had worked very well until March 29,2018. I am wandering if Yahoo changed something again. Have you noticed the change?

    Thank you.

    Peter

  22. Andrew

    The code had worked very well until March 29,2018. I am wandering if Yahoo changed something again. Have you notice it?

    Thank you for sharing.

    Peter

    • Peter (and others):
      It looks as though there is a problem. Unfortunately I am away from the office and can’t look at it for a few days. Please bear with me for a little while. Meanwhile, suggest using the JSON interface if you are able to make the switch.
      Andrew MacLean

  23. Just replace

    “https://finance.yahoo.com/lookup?s=rubbish”

    with

    “https://finance.yahoo.com/lookup?q=” & StockSymbol

    to get the crumb. Don’t know why but the dummy one is no more working. Opening the ticker page then downloading the historical will help you disguise as a normal user.

    • I was able to get it working after changing the search pattern from: “CrumbStore”:{“crumb”

      to be based off this part of the http response:

      …”bkt”:”finance-US-en-US-def”,”crumb”:”wbgfBtCFUkT”,”device”:”featurephone”…

      i.e., in Java: final String crumbSearchString = “bkt\”:\”finance-US-en-US-def\”,\”crumb\”:\””;

    • I’m away from the office and I’ll be back on April 24th. I’m hoping I’ll get a chance to look at it before then. It could cause significant issues if EmulateURL is no longer working because I use it not only for prices, but dividend data and I would need to find a way to replace that functionality. Sorry I can’t be more specific.

  24. Implemented DiegoVega worked Thanks
    SignalSolver Still not working, can you do same ““https://finance.yahoo.com/lookup?q=” & StockSymbol” to solve Crumb issue?
    If so what module?

  25. I have purchased SignalSolver today after running for several days. 3 question and 1 suggestion
    Question 1 – Do you have a comprehensive guide / definitions of all tabs and settings on tabs in PDF?
    Question 2 – On Scan View Page – do you have more detail on the uses of this page and suggestions on optimize type, I am currently using Figure of Merit?
    Question 3 – I live in center time zone and my 15 min. delay data does not appear in your sheets until 9:45 am when market open is at 8:30 am central time. I have changed setting on the Trades tab to have local time at 8:30 am, what other setting do I need to change?
    Suggestion 1 – Use JSON download that you describe in excel downloads ( good sheets ) to be able to get 1 minute downloads during the day, and at open, so that you do not have to go to other sources to get opening or closing data to take suggested actions on report tab.

    • Hello Paul, Thank you for purchasing SignalSolver. In answer to your questions–
      1. No, I don’t have a PDF guide at this point, but if you click on the blue cells, it takes you to the web help pages which I think are pretty comprehensive.
      2. You can set the scans up to show how changing the buy and sell points influence various things (return, risk-reward, etc). The Figure of Merit is a Weighted combination of Return, Time in Mkt, Drawdown, Minimum quartus return etc. You will need to set up the weights as to what is important to you. For example, you may be more concerned about total return than consistency of return, or reward-risk. It depends on your own style of investing.
      3. Set the Open Time to the market open time in the timezone the market is in (9:30AM for NYSE) and set the close time the same way (4:00PM for NYSE). Set the local time offset to the offset between your local time and market time. Set the UTC Offset to the offset between the market and the UTC Time Code (GMT). This is -5 hours for NYSE. I had to add UTC when Yahoo started using it. Sorry this isn’t clearer, I will improve the comments on the cells and add it into the Help files. I will probably move these settings to the Settings page in the future.

      Your suggestion to use JSON data to get the price updates is already in place. EmulateURL is used for historic data, JSON is used for updates. I also use other sources for dividend data.

      Andrew MacLean

  26. Is there a way to pull data for more than 1 stock symbol at a time using the excel version of signalsolver? Thanks,

  27. Hi Andrew,
    Just checking in, are there any updates to SignalSolver?
    At top of spreadsheet it shows version 1.3v

    Thanks,
    Steve

    • Hi Steve,

      Thanks for your recent purchase. A new version of SignalSolver 1.3w is currently being tested. I hope to release it this month. It expands on the traffic light features to better gauge if a security sentiment is currently bullish or bearish based on aggregate opinions of several algorithms. You can also trace how well aggregation worked in the past under different conditions (no. of algorithms, optimization methods and other things). Plus, you can see how well a given algorithm or set of algorithms did when re-optimized every x periods. The results have been quite interesting. Plus it adds a few smaller features.

      Andrew

Leave a Reply

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

*