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

19 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.

          • would you happen to have an idea why GetYahooRequest might return a little differently for a few stocks than others? I consistently have some trouble with a few during the hours when the market is closed. Example trouble ticker symbols are TEVA, FCX, TWTR. Vast majority of other ticker symbols work fine whether the market is open or closed. I am using GetYahooRequest with some modification so that I can use it as a cell function.

          • Hi Seth,
            I don’t know what is causing this problem, sorry. I’ll take a look and let you know if I find something strange in the JSON response.
            Andrew

  4. Hi,

    Can this be tweaked to download fundamentals on US listed stocks, e.g., like Revenue, EPS, EBITDA, financial ratios, etc.

    Best,

    Raj

  5. Hi really useful bit of code.

    One question – can I get the stock symbol returned by the function ?

    Tried replacing one of the other parameters like ‘timestamp’ but this does not work.

    Thanks,

    Dave

    • Hello Dave,

      You can extract other JSON fields, using VBA string handling, for example-

      ‘EXTRACT A FIELD, IN THIS CASE “symbol”
      Dim startpos As Long, endpos As Long, fieldToExtract As String
      fieldToExtract = “symbol”
      startpos = InStr(strResponse, fieldToExtract) + Len(fieldToExtract) + 3
      endpos = InStr(startpos + 1, strResponse, “”””)
      fieldToExtract = Mid(strResponse, startpos, endpos – startpos)
      MsgBox fieldToExtract

      This code would go inside of GetYahooRequest before the Exit Function
      Andrew

  6. Thanks Andrew. This got me to where I needed to go to write a UDF to pull a closing price for a given date.

    One note of clarification for people using this and tweaking the fields. There is a comment in the code identifying “‘YOU CAN ADD OR REMOVE AS MANY JSON FIELDS YOU LIKE HERE, OR RE-ORDER THEM:”. That’s mostly true but I found that you cannot remove the “timestamp” field and still have the code work off of a date range so those writing a UDF looking for 1 field for a given date will need to be sure to keep “timestamp” in addition to any other field they want.

  7. Thanks Andrew. This was of great help to me. I found one area of the code that I improved as follows and one item of interest for those wishing to make a UDF.

    What I improved related to using date ranges. I found that the start/end dates were not inclusive which I believe most people would expect if you entered a start date and end date of 3/20/18 that you would get the data for 3/20. After investigating what I found was that the data includes a time of day so the >=EndingDate would always fail. I change the code to assess if the interval was 1d or greater and if so I truncated (INT) the datetime to just a date … and also changed the operator to instead of =. Here is the code.

    If IsNumeric(StrX) Then ‘CONVERT IT ‘1.0c ADDED
    Select Case IntervalPart
    Case “1m”, “2m”, “5m”, “15m”, “1h”:
    LocalDate = CDate(StrX / 86400) + 25569 + (TOffset / 24)
    Case Else:
    LocalDate = CDate(Int(StrX / 86400)) + 25569
    End Select

    For those creating a UDF note that while the comments say you can remove or add whatever JSON fields you like note that you cannot remove “timestamp” and still have the begin/end date work even if you only want 1 field (e.g. “close”). You need to have Array(“timestamp”, “close”) in this example.

  8. Kevin, I’m trying (and failing) to replicate the UDF function you are describing. Can you post the full code you built? I used to have this UDF built for Yahoo and then Google so I really would love to have it back! Thanks.

Leave a Reply

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

*