Near real-time Yahoo stock quotes in Excel

Updated -- now includes historical prices.

You may know that Yahoo Finance has for a long time provided near real-time global stock quotes via its website and via a web API service, which many have used as a convenient source for Excel data. There have been lots of VBA implementations and even a direct spreadsheet formula implementation (no VBA).

Unfortunately the API service was abused to the extent that some even purported to sell trading applications based around it (go figure). That resulted in a huge load on Yahoo’s service leading to it being discontinued last November. Yahoo has also now included some simple obfuscation in its data download service (which has since been reverse-engineered, and may as a result be further protected).

I’ve written some code to instead get near real-time prices via Yahoo’s JavaScript Object Notation (JSON) data feed. It is implemented via a user-defined function called StockQuote(), which takes the Yahoo ticker code (STOCKCODE.exchangecode). There’s a second function StockName() which returns the long name associated with the ticker code.

It helps if you know some VBA, but that isn’t essential. This stuff is for private use only, and of course comes with no warranty whatsoever — expressed or implied, no support, no responsibility, no liability, nothing. Consider first whether your own skills are sufficient to understand and accept the risks. It needs Excel 2010+:

 
Yahoo_StockQuote
 
 

More — historical prices

A couple of people asked in comments how to get historical stock prices. Historical prices are also available from the JSON feed, but I find it simpler to just used Yahoo’s data download service. There are lots of ways to import that into Excel (including manually, as a csv file), but the method I use is an Excel query table. Query table parsing doesn’t work with this data. Excel will only import it as text, namely as lines of comma separated variables for each date, which need manual parsing. That could be done with VBA or perhaps with the ‘text to columns’ tool, but I’ve chosen to just use spreadsheet formulas. (BTW, if you can’t figure how that ‘between-the-commas’ formula works, you’re not alone. I pinched it from somewhere.)

Things:

  1. The query table has to be separated from the parsing table or it will disrupt the parsing formulas when it updates. That is done by copying it into hidden column C.
  2. The ‘crumb’ identifies you to the Yahoo server, to discourage misuse. The one in the example may not work. You should get your own from Yahoo by right-clicking on their ‘Data Download’ button and copying the URL, which will contain a working crumb — for example here: https://finance.yahoo.com/quote/SPY/history?p=SPY.
  3. If you select a short interval and a long date range the parsing table may need extending to accommodate the data length. It is currently 5000 lines.
  4. If you want to remove the ‘null’s (missing data), you can adjust the parsing formulas. For my purposes I just set empty cells to the value in the cell above (not included in the example).
  5. Auto-refresh is turned off on the query table. You can use it if you like, but it will just repeat the last query you made, which could be up to a date that was quite a while ago. Instead I’ve put a Workbook_Open routine in the ThisWorkbook code area, to invoke the ‘Refresh’ code (which will update the query to finish on today’s date, if you haven’t changed that).

So here is the example. I’m sure there are tidier ways, but it’s what I use.

Again, this is for private use only, and comes with no warranty whatsoever — expressed or implied, no support, no responsibility, no liability, nothing. Consider first whether your own skills are sufficient to understand and accept the risks. It needs Excel 2010+.

 
Historical_prices_example

 
 

19 comments to Near real-time Yahoo stock quotes in Excel

  • Traygus Staveus Pageus Montigue Loring III

    I don’t get it, but thanks. Gives me hope.

  • Dr. Pjuskebusk

    Great stuff! Thanks!

  • Tom Smart

    Very helpful. Thanks. Tom

    • Gerg

      Don’t know, sorry. That page is actually being served (rather than generated locally), so in theory the Excel Web Query tool should work, but I couldn’t get it to. I suppose you could just parse the HTML. For example the first table entry is here:

      
         <span data-reactid="15">Market Cap (intraday)</span>
         <!-- react-text: 16 --><!-- /react-text --><!-- react-text: 17 --><!-- /react-text -->
         <sup aria-label="Shares outstanding is taken from the most recently filed quarterly or annual report and Market Cap is calculated using shares outstanding." data- reactid="18">5</sup></td>
         <td class="Fz(s) Fw(500) Ta(end)" data-reactid="19">464.19B</td></tr>
      

      Beyond my interest I'm afraid. Trick would be to find the relevant bit. Note the 'reactid' -- seems to be different for each entry; handy.

      An easier way may be to look at the MSM Money-based approach Chloe suggested above.

  • Mike

    how can I get historical data

  • Kevin McCormick

    Thanks! With my limited knowledge of VBA I would still be struggling to get a solution, but your spreadsheet works like a charm!

  • Tim

    Great solution.

    Following up on Mike’s comment above: Is it possible to get historical prices? I specifically need a table as follows: dates start in A2 and run down the rows (3/26/18, 3/25/18,……3/1/18) and starting in B2 each column would be an individual stock (APPL, XOM,…….BHP).

    Your sheet is great because it gives me the last posted price for multiple stocks. I just need it to give me a close price either by a specific date or for a range of dates.

    • Gerg

      I have a solution for that (of course). It’s less tidy. Will think about sharing it…

      • Gerg

        The JSON feed also includes historical data — that’s what the Yahoo script seems to be using to draw its graphs (they’re generated locally in your browser, not on their server). At the moment I’m still just using the old data download feature, which works as long as you have a suitable ‘crumb’ (that obfuscation…), like this:

        Public Sub Load_Yahoo()

            Dim Symbol As Variant, Qurl As Variant, startDate As Date, endDate As Date
            On Error Resume Next

            Symbol = [ticker]
            startDate = [StartDate]
            endDate = CDate(Format(Date, “yyyy-mm-dd”))

            Qurl = “https://query1.finance.yahoo.com/v7/finance/download/” & Symbol _
                     & “?period1=” & (startDate – DateSerial(1970, 1, 1)) * 86400 _
                     & “&period2=” & (endDate – DateSerial(1970, 1, 1)) * 86400 _
                     & “&interval=1wk&events=history&crumb=k3gEejzEY5Q”

            With Sheets(“Historical”).[A1].QueryTable
                .Connection = “URL;” & Qurl
                .PostText = True
                .SaveData = True
                .Refresh BackgroundQuery:=False
            End With

        End Sub

        That code of course needs the appropriate query table set up on sheets ‘Historical’; it won’t make its own.

        The ‘crumb’ there may not work for you. As I said, it has been reverse-engineered, but the easiest way for a single user is to just right click on the ‘Data Download’ button at Yahoo and copy the URL, which will contain a working crumb; e.g. here: https://finance.yahoo.com/quote/SPY/history?p=SPY. Apparently crumbs last for 12 months, after which you may need to go get a new one.

        You can also go the JSON route, but the above still works and is pleasantly simple (total 10 lines of code).

  • P.J. Kim

    Thanks! Very helpful!

  • KJ

    I was able to get Yahoo stock history api working again for the last 2-3 weeks with your help on this page. It stopped tonight….and will be looking into shortly. Thanks again for your inputs on this.

  • Pete

    I have also been using the crumb method to obtain historical prices and it seems to have been disabled in late march 2018. It is a totally different application and has worked successfully for many months. my application went out and got a new crumb each time I ran.
    Have you been able to get your historical price spread sheet to work during the month of April 2018?. I downloaded your historical price spread sheet and changed the parameters. It does not work. Again, I don’t believe it is your worksheet program, I think yahoo has recently done something to their website to make the crumb method not work. Do you have a JASON way to obtain the historical prices?
    FYI the current quote system works great. Nice way to do it. can it be used to get the historical prices?
    Thanks for your help
    Pete

  • Felipe

    Thank you for your script. Very helpful! If you mind a suggestion, you could use MSXML2.XMLHTTP as it uses the systems proxy automatically. The modified code is below.

    Dim request As Object
    On Error GoTo Err

    URL = “https://query2.finance.yahoo.com/v7/finance/quote?symbols=” & Trim(ticker)
    Set request = CreateObject(“MSXML2.XMLHTTP”)
    With request
    .Open “GET”, URL, True
    .SetRequestHeader “Content-Type”, “application/x-www-form-urlencoded; charset=UTF-8”
    .Send
    response = .responseText
    End With

    • pete

      Felipe regarding your 12 April post.
      I modified the code as you suggested but it did not work. I copied and pasted the lines you provided over the same lines in the original script. FYI I had to change all the quote marks from “ to ” or I got compile errors. Once that was done, the update ran fine but I got #N/A in all the amount fields. Any suggestions as to what I might be doing wrong. Do I need to make any changes to add any references under the tools menu? FYI I am using Excel 2010.
      Thank you

  • […] Near real-time Yahoo stock quotes in Excel – Gerg’s Net […]

  • Pete

    Felipe,
    That worked great, thanks a lot for your help.
    Does anyone know how to make the historical prices code work?
    I am trying to get historical prices for the last year of a number of stocks. I had code that worked until Yahoo changed their access sometime during the last couple of weeks. It was code that got a new crumb each time and ran fine until just recently.
    Thanks

Leave a Reply