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:
  1. JSON data goes down to 1 minute granularity (1 day was the most granular for CSV).
  2. JSON data is close to real time, only a minute or two delay on the one minute data it seems, for USA exchanges.
  3. 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).
  4. 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).  
  5. 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:

 

https://query1.finance.yahoo.com/v7/finance/chart/MSFT?range=2y&interval=1d&indicators=quote&includeTimestamps=true

 

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:

Version 1.0d

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.

--Andrew

Version 1.0c

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.

Many thanks,

Andrew MacLean

Algorithm Science

8 Comments

  1. Thanks so much for this. Greatly appreciated.

    Just one question:

    Is there a way to include ‘adjclose’ data from yahoo finance using this solution? When I add the ‘adjclose’ field it always lists the price the same as the ‘close’ without adjusting for splits or dividends?

    Thanks again

    Neil

    • Hello Neil,

      I’m afraid the answer is no. You will need to use EmulateURL to read Adjusted Close (and even that isn’t perfect).. I’m hopeful that Yahoo! may fix the JSON adjusted close in the future. The data is already adjusted for splits, so you could also use EmulateURL to get the dividends then do your own adjustments. Good luck,

      Andrew

  2. Just to let you know that I noticed that the “adjclose” and “unadjclose” do not necessarily equal the “close” when I was seeing some closes lower than the lows on some dates (i.e. SPY on 2015-07-24 )

    the sample spreadsheet wasn’t actually fetching the “close” but rather the “adjclose” even when it was set to “close”

    I fixed it by changing
    StringToFind = strKeyName & “””” & “:[”
    to
    StringToFind = “””” & strKeyName & “””” & “:[”

    Basically making it an exact match.

    The was it was it was matching close”:[ but that matches in adjclose”:[, the new code makes it match to “close”:[ so it’s not picking up the adjclose or unadjclose

    Regard

  3. Do you think I might be able to restructure and modify the code so that I would have a user defined function (used in cell formulas) that would return, to the calling cell, a single day adjusted close? The function would take, as parameters, a date and a stock symbol.

    • Yes, you could do that. Just create a function e.g. getprice(D as date, Symbol as string) As Variant which calls GetYahooRequest with the correct parameters, then modify GetYahooRequest to either return or set a global with FilteredOutputArray(RowCount – FilteredRowCount, 6) and set it to be the return value of getprice. In the cell, have a formula e.g. “=getprice(“AAPL”, #4/5/17#). Something like that.

      • Thanks Andrew. Before I get started on that, can you tell me why I don’t get the expected result when I use the following formula in a cell other than I15? I don’t receive an error, but I also don’t receive data starting at Cell I15. I am running this without having made any changes to the VBA code.

        =GetYahooRequest($I$15,{“timestamp”,”open”,”high”,”low”,”close”,”volume”},”PCLN”,”1d”,”1h”)

        • I tried-

          Sub testit()
          Dim err As String
          err = GetYahooRequest(ActiveSheet.Range(“$I$15”), Array(“timestamp”, “open”, “high”, “low”, “close”, “volume”), “PCLN”, “1d”, “1h”)
          End Sub

          Seems to work

          • Ok. I seemed to have figured out how to get it to do exactly what I wanted it to do. Very nice. Thanks for the help.

Leave a Reply

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

*