PULL YAHOO JSON HISTORICAL STOCK DATA INTO EXCEL
An alternative to the EmulateURL CSV interface--Now with Adjusted Close prices
Yahoo's JSON based interface
Many people were blindsided in May 2017 by Yahoo Finance complicating the ancient and trusty http://ichart.finance.yahoo.com/table.csv URLs which returned Comma Separated Values (CSV) for historical stock data. I have published a workaround for that problem, but I also wanted to have a backup ready in case the interface gets even more obscure or goes away entirely.
Another free source of historical stock data which in many ways is more powerful is the https://query1.finance.yahoo.com/v7/finance/chart URLs, however these URLs return JSON (Java Script Object Notation) format data, so are a little harder for Excel to deal with than the old CSV format.
There are a few distinct differences:
- JSON data goes down to 1 minute granularity (1 day was the most granular for CSV).
- JSON data is close to real time, only a minute or two delay on the one minute data it seems, for USA exchanges.
- You can't get dividends and splits with the JSON interface, at least I haven't figured out how. Luckily the data is split adjusted already, which is what I need. But you need to go elsewhere for dividends (EmulateURL works).
- You can't specify a start date and end date with JSON like you could with CSV, but you need to specify a range (e.g. "4y" would return four years of data).
- All timestamps are GMT (also known as UTC) with JSON
Note that Yahoo! appear to have fixed the Adjusted Close problem, so adjusted close prices are now available with this interface.
Tools for getting Yahoo JSON historical stock data into Excel
We've developed a VBA function for requesting JSON historical stock data then putting the data into spreadsheet format, and have incorporated that code into a simple spreadsheet. Here is a short video of the code in action:
Pull stock prices into Excel–near real time, 1min res, Yahoo JSON
You can download the latest version of this spreadsheet by clicking below. You will need to "Enable Editing" and "Enable Content" to run it. The spreadsheet is completely unlocked.
How it works
A typical URL for the JSON interface takes the form:
The VBA code uses a function "GetYahooRequest" to construct the URL:
Function GetYahooRequest( _
DestinationRange As Range, _
FieldsToExtract As Variant, _
StockSymbol As String, _
RangePart As String, _
IntervalPart As String, _
Optional DateOrder As Integer = xlDescending, _
Optional TOffset As Double = 0, _
Optional FilterBadPrices As Boolean = False, _
Optional StartingDate As Date = 0, _
Optional EndingDate As Date = 0 _
) As String
'FETCHES HISTORICAL STOCK PRICES FROM YAHOO FINANCE USING THE query1.finance.yahoo.com/v7/finance/chart INTERFACE
'WRITES THE PARSED JSON DATA TO A WORKSHEET AFTER CLEARING THE CONTENTS OF THE REGION
'RETURNS AN EMPTY STRING IF NO ERROR DETECTED, OTHERWISE RETURNS A POSSIBLY MEANINGFUL ERROR MESSAGE
'DestinationRange As Range, THE TOP LEFTMOST CELL OF THE TABLE WHERE THE DATA SHOULD BE PUT E.G. Sheet1.Range("A20")
'FieldsToExtract As Variant, AN ARRAY OF JSON KEYS E.G. = Array("timestamp", "open", "high", "low", "close", "volume")
'StockSymbol As String: THE STOCK SYMBOL, E.G. AAPL, BP.L, ^GDAXI
'RangePart As String: THE OVERALL TIME RANGE OF THE DATA: CAN BE max, ytd, Xy, Xm, Xd, Xh, Xm WHERE X IS AN INTEGER, E.G. "50d"
'IntervalPart As String: THE TIME INTERVAL OF EACH DATA BAR: 1m, 2m, 5m, 15m, 1h, 1d, 1mo, 3mo
'DateOrder As Integer: CAN ONLY BE xlAscending OR xlDescending. DEFAULT IS DESCENDING
'TOffest As Double: A NUMBER REPRESENTING A TIME OFFSET IN HOURS. DATES WILL BE OFFSET BY THIS AMOUNT FROM GMT. CAN BE NEGATIVE, DEFAULT 0
'FilterBadPrices As Boolean: IF True, REMOVES ALL LINES WITH "nulls" OR ZEROS IN THE PRICES. DEFAULT IS False
'StartingDate as Date: ANY ROW WITH A TIMESTAMP + TOffset (i.e LOCAL TIME) BEFORE THIS DATE WILL BE FILTERED OUT. ZERO STOPS FILTERING
'EndingDate as Date: ANY ROW WITH A TIMESTAMP + TOffset (i.e LOCAL TIME) AFTER THIS DATE WILL BE FILTERED OUT. ZERO STOPS FILTERING
The function populates the URL fields with the appropriate values then passes it to a WinHTTPRequest for the data. If you copy/paste the code, and the compiler doesn't recognize WinHttp, simply make sure that "Microsoft WinHTTP Services" are enabled under Tools->References on the VBA tab.
When the response arrives, the program looks for each of the requested JSON keys ("timestamp", "open", "high", "low", "close", "volume"), translates the string entries into an appropriate data type and stores it in a variant array. Optionally, bad lines (lines with "null" which are quite common on the higher resolutions) are filtered out, then the entire variant array along with headers is written to the spreadsheet.
Here is the VBA code in the form of a text file which you can readily cut and paste into an Excel VBA module:
11/20/2017: Adjusted close prices are now reported through the Yahoo!'s JSON interface--Version 1.0d addresses issues related to this. It fixes a problem reported by Jonathon Garneau (see comments) where version 1.0c reports adjusted close prices instead of close prices. I've also added an Adjusted Close column for intervals of 1 day or greater. Yahoo doesn't report Adj Close for intervals less than 1 day, so the column is not present for shorter intervals. If you are using version 1.0c you should update.
Version 1.0c adds the ability to filter out by date using the fields StartingDate and EndingDate. This simple filter operates on the fetched data and does not change the query, so you still have to make sure the date range requested is large enough to cover the dates you are filtering on.
Please comment, register or contribute
If you use the code and find it either useful or faulty, please let us know either way in the comment box below--we appreciate it. Comments are moderated so don't appear immediately. If you would like to receive notification of updates you can register (no junk mail I promise). Also, if you would like to help keep the updates coming (or are saving a bundle by not using a paid data service), please consider purchasing SignalSolver. To contribute any amount without a purchase, just enter "contribution" in the UserID field.