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 use 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

 
 

55 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

  • Pete

    Has anyone been able to figure out how to get Historical Quotes.
    Until early April, I used a routine that obtained the crumb from Yahoo. That has stopped working. I think Yahoo has stopped use of the crumb.
    I checked the output of the routine and it no longer fetches the crumb.
    I would appreciate any suggestions on how to get the historical prices for a stock for a year.
    Thanks – Great forum.
    Below is the code which I got from another site and it worked great (for more than a year) until recently.

    Sub getCookieCrumb(crumb As String, cookie As String, validCookieCrumb As Boolean)

    Dim i As Integer
    Dim crumbStartPos As Long
    Dim crumbEndPos As Long
    Dim objRequest

    validCookieCrumb = False

    For i = 0 To 5 ‘ask for a valid crumb 5 times
    Set objRequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)
    With objRequest
    .Open “GET”, “https://finance.yahoo.com/lookup?s=bananas”, False
    .setRequestHeader “Content-Type”, “application/x-www-form-urlencoded; charset=UTF-8”
    .send
    .waitForResponse (10)
    cookie = Split(.getResponseHeader(“Set-Cookie”), “;”)(0)
    crumbStartPos = InStr(1, .ResponseText, “””CrumbStore””:{“”crumb””:”””, vbBinaryCompare) + Len(“””CrumbStore””:{“”crumb””:”””)
    crumbEndPos = InStr(crumbStartPos, .ResponseText, “”””, vbBinaryCompare)
    crumb = Mid(.ResponseText, crumbStartPos, crumbEndPos – crumbStartPos)
    End With

    If Len(crumb) = 11 Then ‘a valid crumb is 11 characters long
    validCookieCrumb = True
    MsgBox “cookie crumb = ” & crumb ‘ for test
    Exit For
    End If:

    ‘ If i = 5 Then ‘ no valid crumb
    ‘ validCookieCrumb = False
    ‘ End If
    Next i
    MsgBox “cookie crumb = ” & crumb & i ‘ for test

    End Sub

    • Gerg

      Apparently that’s the ‘further protected’ thing I mentioned. I guess we now have the usual arms race.

      Yahoo seems to now check that its browser cookie is present and correct as part of its verification. If you first delete Yahoo cookies, browsers will fail to download a valid download URL string (regardless of the crumb). In Firefox (which displays JSON) you get JSON error code: “Unauthorized”, description: “Invalid cookie”.

      But the odd thing is that the ‘historical prices’ code I posted still works fine here (seemingly regardless of the crumb value) … and I’m still using it. I gather that is not the case for others?

      Presumably that means that Excel web query supports cookies and I still have a valid one, perhaps from before the change.

      Next: How does Excel web query manage cookies?
        1. It’s not via IE
        2. It seems to be at the application level (presumably application-user) rather than for a particular instance, because it works across multiple files.

      I guess if one could provide Excel web query with a suitable cookie, the problem would be solved. I’m thinking about it.

      • Gerg

        Something really simple to try for me please. (Care, might cause Excel to hang … you know how to terminate it with Task Manager, don’t you.)

        Open historical_prices_example.xlsm from the link in the main post, and enable editing and content.
        Confirm that it doesn’t load data when you press Refresh or enter a new ticker.
        In Excel, on the Data tab, under Get External Data, click ‘From Web’.
        Then in the Address bar of the dialogue (it’s actually a simple web browser), insert this URL: https://finance.yahoo.com/quote/SPY/history?p=SPY
        Press Go. You will get a whole lot of script errors (the thing may support cookies, but obviously not complex scripting). Just answer yes to them all.

        After the page has completed loading, close that and retry the ‘historical prices’ code — e.g. just enter SPY into the ticker box. Does it load?

        Theory: In other browsers, loading that quote page is what sets the necessary cookies. After you’ve loaded it in web query, you ought to have them, so it ought to work … except the crumb may now not be right. GRRR. Anyway, try it.

        BTW, the cookies you need seem to be these, but I haven’t a clue how to view which ones Excel web query has:

  • Pete

    Reply to Gerg 28 April
    I tried what you said and it still did not work.
    By the way, how did you access the cookies that you showed in your example.
    Thanks
    Pete

    • Gerg

      Thanks, a shame. I don’t have another suggestion atm.

      Cookies shown are from Firefox. You can search and display cookies there with Options | Privacy | Remove individual cookies (you don’t have to remove them).

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

  • Dear Greg,
    I was pleased to find your solution for getting quotes. I downloaded your spreadsheet, put in my own stock list, and bravo! I got current prices. I am totally new to JSON so if there is some kind of code attached to the spreadsheet, I know nothing about it.
    Before Yahoo restricted downloads. one could get numerous types of information. With the JSON approach can you get other statistics? I am especially interested in percent dividend yields. If so, what would I need to do in your spreadsheet to get dividend information?
    Dan

    • Gerg

      The JSON response to the query for say BHP.ax is as below. You could easily make your own function to extract whatever element you want using StockName() as a template. For example make a copy of the VBA code for it and replace “StockName” everywhere (3 places) with say “DivYield”, then replace the search string “longName:” with “trailingAnnualDividendYield:” (needs the colon). You’d also need to change the result assignment that was “StockName = Trim(inbits(i + 1))” to “DivYield = Val(inbits(i + 1))”, because the result will be a number not a text string.

      It’s a little inefficient to use a separate function (hence separate web retrieve) for every data item you want, but it works. If you don’t have a huge number of stocks to obtain, why be efficient — better to do it the easiest and most convenient way.

      E.g. https://query2.finance.yahoo.com/v7/finance/quote?symbols=BHP.ax returns JSON:

      Response
      result
      0
      language “en-US”
      quoteType “EQUITY”
      currency “AUD”
      exchange “ASX”
      marketState “POSTPOST”
      fullExchangeName “ASX”
      longName “BHP Billiton Limited”
      financialCurrency “USD”
      averageDailyVolume3Month 6982986
      averageDailyVolume10Day 6139138
      fiftyTwoWeekLowChange 10.699999
      fiftyTwoWeekLowChangePercent 0.48504075
      fiftyTwoWeekRange “22.06 – 33.0”
      fiftyTwoWeekHighChange -0.24000168
      fiftyTwoWeekHighChangePercent -0.007272778
      fiftyTwoWeekLow 22.06
      fiftyTwoWeekHigh 33
      earningsTimestamp 1519087500
      earningsTimestampStart 1518999300
      earningsTimestampEnd 1519362900
      trailingAnnualDividendRate 0.98
      trailingPE 37.185017
      trailingAnnualDividendYield 0.030444238
      exchangeDataDelayedBy 0
      market “au_market”
      shortName “BHP BLT FPO”
      priceHint 2
      regularMarketChangePercent 1.7707354
      regularMarketDayRange “32.67 – 33.0”
      regularMarketPreviousClose 32.19
      bid 32.75
      ask 32.76
      bidSize 0
      askSize 0
      messageBoardId “finmb_256654”
      epsTrailingTwelveMonths 0.881
      epsForward 1.51
      sharesOutstanding 3211620096
      bookValue 10.704
      fiftyDayAverage 29.978611
      fiftyDayAverageChange 2.7813873
      fiftyDayAverageChangePercent 0.09277906
      twoHundredDayAverage 29.293287
      twoHundredDayAverageChange 3.466711
      twoHundredDayAverageChangePercent 0.118344896
      marketCap 174401781760
      forwardPE 21.695364
      priceToBook 3.0605378
      sourceInterval 20
      exchangeTimezoneName “Australia/Sydney”
      exchangeTimezoneShortName “AEST”
      gmtOffSetMilliseconds 36000000
      esgPopulated false
      tradeable false
      regularMarketPrice 32.76
      regularMarketTime 1525931540
      regularMarketChange 0.5699997
      regularMarketOpen 32.98
      regularMarketDayHigh 33
      regularMarketDayLow 32.67
      regularMarketVolume 5389773
      symbol “BHP.AX”
      error null

  • brianB

    Hello, thanks for the coding! It is most excellent! Does anyone know how to connect to the ‘financials’ tab to grab the annual table of values? Any help would be great!

  • Nathan Meehan

    It is great. Anyway to gt calls and puts?

    • I know it’s a month late, but yes, there is! At Yahoo Finance, just go to whatever security you want the option for, and if you see the one you want, it should have a ticker (the field heading is “Contract Name”) like SPY180615C00277500. So in a cell in your spreadsheet, use =StockQuote(“SPY180615C00277500”) – works like a charm! Alternatively, you could put that “ticker” into a cell in your spreadsheet like B3 and use =StockQuote(B3)

    • Jeff

      The format for puts and calls in Yahoo is xxxxxYYMMDDΩ$$$$$¢¢¢ xxxxx=Stock Symbol no padding 1 character to 5 characters. YYMMDD = expiration date; Ω=P or C for Put or Call, $$$$$¢¢¢ is the strike price with full zero padding i.e. for a strike of 132.50 00132500.

      and thank you GERG for creating this.

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

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

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

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

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

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

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

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

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

  • Just FYI, it does not need Excel 2010+. It works just fine in Excel 2007. Just to be perfectly explicit, the reference you need (at least for Excel 2007) is called “Microsoft WinHTTP Services, version 5.1”. Thanks a ton for an awesome, elegant solution!

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

  • SRB

    There does seem to be some inconsistency in the yahoo data though – for instance the regularMarketPreviousClose value flips between yesterday’s close and the close from the day before, and then back again to yesterdays close when you call the function in Excel 3 times consecutively. Rather odd.

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

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

  • Dave

    Hi Greg, Thanks for this, very helpful. Any thoughts on how to parse out this json?
    https://query2.finance.yahoo.com/v10/finance/quoteSummary/MSFT?&modules=defaultKeyStatistics%2CfinancialData%2CcalendarEvents
    It seems to have a couple of modules and various formats.

  • Jules

    Thank you! good stuff! 🙂

  • Pieter-Jan

    Dear Gerg,

    Thanks for this good functioning tool! It helps me a lot.

    I have just one question. I tried to copy the code to a new workbook and run it, but it didn’t work.
    Then I took your working version, added a new worksheet and placed the name of this worksheet in stead of “Historical”. That didn’t work either.
    Furthermore I tried to chance [A1] to [A2] in the same line and that didn’t work either.

    So I think this has something to do with the QueryTable that I don’t understand.

    Would you mind explaining me?

    Thanks again and kind regards 🙂
    Pieter-Jan

  • Jeff from Texas

    Cannot say “Thanx!!” enough. Wish there was a tip jar.

  • Alex Read

    You can actually get realtime IEX prices through https://intrinio.com. Quality data and you can even access with websockets.

  • Can someone make it work on a mac? It does not support “WinHttp.WinHttpRequest”

Leave a Reply