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+:
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:
- 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.
- 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.
- 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.
- 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).
- 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+.
I don’t get it, but thanks. Gives me hope.
Check out http://gaiersoftware.com/Excel/
Great stuff! Thanks!
Very helpful. Thanks. Tom
how can get statistics data ? websit is :https://finance.yahoo.com/quote/fb/key-statistics?p=fb
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:
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.
how can I get historical data
Thanks! With my limited knowledge of VBA I would still be struggling to get a solution, but your spreadsheet works like a charm!
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.
I have a solution for that (of course). It’s less tidy. Will think about sharing it…
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).
Thanks! Very helpful!
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.
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
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
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
Try changing “.Open “GET”, URL, True” to “.Open “GET”, URL, False”
Seems to work, thanks. Advantage is slightly quicker?
Thanks for this article. You can also Use IXBRAnalyst to get real-time access to financial data in excel on any of your devices
[…] Near real-time Yahoo stock quotes in Excel – Gerg’s Net […]
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
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
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.
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:
Not immediately relevant to the Excel web query route, but seems one can send a cookie via WinHTTPRequest.5.1 like this: https://www.pcreview.co.uk/threads/excel-vba-xmthttp-cookies.2795790/
That might provide a route to a tidier solution, e.g. via VBA parsing of the Yahoo’s .csv (which I couldn’t make work, perhaps due to lack of a cookie!).
But then, if going there, probs better to just take the JSON route.
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
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
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
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!
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)
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 […]
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 […]
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.
Thank you 🙂
Thank you! good stuff! 🙂
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
Cannot say “Thanx!!” enough. Wish there was a tip jar.
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”
[…] Near real-time Yahoo stock quotes in Excel – Gerg’s Net […]
Try IXBRAnalyst to get real-time access to financial data in excel on any of your devices. You can use this link
https://findynamics.com/ixbrlanalyst/
I find the first workbook still works (Yahoo_StockQuote) and the second workbook does not (Historical_prices_example). Too bad. The problem with the second workbook is getting “working crumb” which Yahoo no longer seems to display.
Thanks for the first workbook anyway.