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
- 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
Tools for getting Yahoo JSON historical stock data into Excel
Pull stock prices into Excel–near real time, 1min res, Yahoo JSON
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 ' 'ARGUMENTS: '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 'OPTIONAL: '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.