Download historical stock price data into Excel using the Yahoo CSV Interface

A workaround for the cookie-crumb 2017 change

Pulling Yahoo Finance CSV data into Excel

There's a lot to like about Yahoo Finances' historical data, for one thing it covers all the major stock exchanges. For another, its free. But like many other people, I was very disappointed when Yahoo complicated the ability to download historical stock prices into Excel as CSV in May 2017 by adding a cookie and crumb validation. It made the old versions of SignalSolver completely inoperable, but as it turns out, by careful coding you can still access the data. To minimize the impact on existing users, I have developed a VBA function which takes the original URL as an argument, and returns the data. This function--EmulateURL, minimizes the changes to your existing VBA code.

Below you can download, for free, the VBA code and spreadsheets for this workaround. It has been tested both in the USA and Germany where decimals are comma separated. But please be aware that there exists an alternative which may be a better option for you. Yahoo Finance 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. I have also developed a free Excel VBA interface for this JSON based data so you can readily have a fallback if Yahoo makes any more changes.

A spreadsheet to download historic stock prices


GetHistoricStockPrices.xlsm is a simple spreadsheet for downloading historical stock prices. It uses EmulateURL to do the job of fetching the data. It is unlocked and has all the VBA code you need to do the job. Here is a short video:

Fix, June 2017: Historical Stock Prices for Excel

Download this spreadsheet by clicking below. You will need to "Enable Editing" and "Enable Content" to run the code.

How to emulate the Yahoo ichart historical data URLs in Excel VBA

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 data. 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:

If you also want the output in CSV format, you can add the spreadsheet location:

 

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

 

Giving you, (in addition to the above):

CSV Formatted Historic Stock Price Data

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

Download the .xlsm spreadsheet to see these options in action, it's really pretty straightforward.


VBA Code

A .txt file of the VBA code for the EmulateURL function. Copy and paste it into a module. 

Version 1.0f

An example spreadsheet

With several examples of how to use EmulateURL, as a .xlsm file. You will need to enable macros to run it. VBA Project is unlocked. Version 1.0f

Digitally signed example spreadsheet

If you are nervous about running unsigned code. Signing authority is Thawte, certificate issued to Algorithm Science.

Version 1.0f

Update Oct 16th 2017: Version 1.0f: Improved crumb generation

Thanks to Thomas Eirich of Switzerland for figuring this out. He explains it best:

Hi Andrew,
i recently discovered that Yahoo changed their download API for historical CSV data.
Luckily, I found your page:
http://www.signalsolver.com/wp-content/uploads/2017/09/EmulateURL-V1.0e.txt
explaining how the new API works (crumbs). With this I was able to change my
Python code to work with the new API - **Thanks** for sharing!

While testing my code I noticed the reason for your CrumbTry handling.
The crumb is extracted from a piece of JavaScript code embedded into the
web page. This embedding requires certain characters in the crumb to be escaped
aka not rendered literally.

The forward slash i.e. is represented as \u002F where 2F is the hexadecimal
ASCII code of '/'. You could get rid of the misfiring crumbs by decoding
the pattern \u00xx.
My Python code looks like this (using a regular expression):
        crumb = re.sub(r'\\u00([0-9A-Fa-f]{2})', lambda x: chr(int(x.group(1),16)), crumb)
Unfortunately, I have no idea how an equivalent expression would look like in VB.

Cheers from Switzerland,
Thomas

Thanks Thomas!

I was able to fix the VBA with a "replace" statement:

     Crumb = Replace(Crumb, "\u002F", "/")

Using this, I could not detect any crumb failures.

Andrew

Update Sept 8th 2017: Version 1.0e: Another fix for countries that use comma as the decimal separator

Using MID$(1 / 2, 2, 1) to detect the decimal separator doesn't work everywhere (thanks Ran for testing this) so I've changed the code to use Application.International(xlDecimalSeparator) instead. We may need to change it to Application.DecimalSeparator, not entirely sure which is better. Let me know if you have problems with the CSV as I can only test with USA settings.

Thanks

Andrew

Update Sept 7th 2017: Version 1.0d: Fix for countries that use comma as the decimal separator

Users in countries where "," is the decimal separator were having problems (see Sam's comments below). I added a fix in 1.0d that looks at the decimal separator and figures out how to convert the Yahoo strings to numbers whatever your decimal separator is. Then, if you ask for Comma Separated Value (CSV) format when "," is your separator it uses ";" as the CSV record separator, which is the standard in these countries.

Thanks to Sam and Dr. Volker Wendel for helping me with this piece.

I also changed the DateValue to DateSerial (see Vincent's comment below). Thank you Vincent for pointing this out.

As always, let me know in the comments or email if you have any more issues.

Andrew

Important update July 12th 2017: Version 1.0c

Yahoo have again changed the format of the data, they are now giving split adjusted data in the O,H&L columns. EmulateURL does not emulate the old "unadjusted for splits" price behavior, it simply gives the split adjusted data. This is usually what you need anyway.

There is also a secondary problem with the data--the "Close" price column is actually the split & dividend adjusted close price, and their "Adj Close" is the split adjusted close price. It should be the other way around. Yahoo seem to be in no hurry to fix this, either on the Yahoo Finance website or on the servers which provide the data for EmulateURL. So in Version 1.0c I have added a workaround, adding 3 lines of code to EmulateURL to switch the "Close" and "Adj Close" prices, but only if the Close price is less than the Adj Close price. When they do fix it, the code should still work.

I'm looking out for undesirable side effects, but none have been found so far. Let me know if you have any problems.

Andrew

Important update 6/24/2017: Version 1.0b

Yahoo have just changed the format of the data. Up until now the open, high and low prices were adjusted for dividends. EmulateURL version 1.0a compensated for this. They are now unadjusted, so the compensation is not necessary. If you are using EmulateURL version 1.0a, prices for dividend stocks will no longer be correct. Please upgrade to version 1.0b.

Adj Close Inconsistency in GetHistoricalData

Kudos to Jeff Pietsch for spotting this and providing a solution. I'll let Jeff describe the problem:

Dr. MacLean,
 
Thank you for sharing your good work. I have noted that Yahoo! is serially applying 'Adj Close' adjustments incorrectly to weekly and monthly data under select circumstances.
 
Take for example 'Stock Symbol', SPY -- the 'Daily Adj Close' on June 30th, is currently $240.606 (as of 10/23/17). You will get the same answer if you run 'Weekly Prices'; however, now run 'Monthly Prices', you will see $239.438. This will create a problem in calculating consistent monthly percentage changes. Yahoo! is applying the adjustment formula to the periodic data instead of allowing the correct daily results to simply be restated.

Jeff supplied a version of the spreadsheet which can be used to query the correct weekly and monthly Close and Adj Close prices. I modified the sheet slightly to hide the high, low and open prices and volumes which would need to be derived separately.  Note that the dates on the weekly and monthly fetches are the closing dates of the periods in the query, not the opening dates per the convention.

Thanks Jeff!

Differences between the new and old data

I've noticed a few differences between the old and the new data, even though I tried my best to get them into the same format:

  1. With the new data, weekly data is aligned on weekday not trading day boundaries. If you give it a start date which is on a Thursday, all the dates will be Thursdays and the data appears to be Thursday to Thursday data, which is not what you usually want. In the original query, dates were the first trading day of the week.

2. monthly data has dates of the first of the month, not the first trading day of the month as before.

3. Yahoo! seems to occasionally throw in a daily line as the first line (or last depending on date order) of the returned data on weekly and monthly queries. It happens more out of hours. Bit odd that.

4. Volumes seem to be completely different than the old query. I haven't looked into this as I don't use volume in my work, but you need to be aware. Compare it to your old data, for AAPL monthly for example.

5. (Added 7th July 2017) Prices in the new data are split adjusted. Formerly prices were not adjusted for splits.

 

Leaving a comment, registering for update notifications, contributing

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). Also, if you would like the project to keep going (or are saving a bundle by not using a paid data service), please consider purchasing SignalSolver! To contribute, just enter "contribution" in the UserID field.

Many thanks,

--Andrew MacLean

Algorithm Science

60 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 http://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

Leave a Reply

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

*