How to: Obtain stock quotes from Yahoo finance (you can query them via Excel too)
How to obtain stock quotes in Excel
I was working on creating a spreadsheet to calculate profits and losses on options positions but didn’t know how to populate excel with stock quotes. Back in the day there used to be an interface to get stock quotes with the MSN Money site but it is not supported anymore. The idea behind this spreadsheet was to use the latest and historic quotes to calculate intrinsic values of options and P&L for expired ones. Kind of just trying to keep track of my record and evaluate performance. Of course the issue we face is that stock prices move every second and maintaining all that data manually is not worth it. After some research I tried using Google Finance to populate Excel to no avail but found Yahoo Finance supports this more easily.
In order to query stock quotes from yahoo all you need to do is use the following URL and provide the required parameters:
http://finance.yahoo.com/d/quotes.csv?s={Stock symbols separated by the ‘+’ character}&f={letters which represent a column of information you wish to be provided}
Keep in mind you need to use the Excel feature of “External data source” under the “Data section”. There you’ll find the option for “HTML” or similar. I use Excel for Mac in spanish so the names might be slightly different in the PC version.
So really there are 2 pieces of information you provide via the URL to get your stock quotes. First the list of stocks you wish to get a quote on separated by a ‘+’ sign like ‘GS+BAC+MSFT+AAPL‘ etc. The second part are the columns of information you wish to be provided like the last quoted price, the symbol, the full name, and much more. Below is a table with all the different columns you may request back as part of the csv:
| a | Ask | a2 | Average Daily Volume | a5 | Ask Size |
| b | Bid | b2 | Ask (Real-time) | b3 | Bid (Real-time) |
| b4 | Book Value | b6 | Bid Size | c | Change & Percent Change |
| c1 | Change | c3 | Commission | c6 | Change (Real-time) |
| c8 | After Hours Change (Real-time) | d | Dividend/Share | d1 | Last Trade Date |
| d2 | Trade Date | e | Earnings/Share | e1 | Error Indication (returned for symbol changed / invalid) |
| e7 | EPS Estimate Current Year | e8 | EPS Estimate Next Year | e9 | EPS Estimate Next Quarter |
| f6 | Float Shares | g | Day’s Low | h | Day’s High |
| j | 52-week Low | k | 52-week High | g1 | Holdings Gain Percent |
| g3 | Annualized Gain | g4 | Holdings Gain | g5 | Holdings Gain Percent (Real-time) |
| g6 | Holdings Gain (Real-time) | i | More Info | i5 | Order Book (Real-time) |
| j1 | Market Capitalization | j3 | Market Cap (Real-time) | j4 | EBITDA |
| j5 | Change From 52-week Low | j6 | Percent Change From 52-week Low | k1 | Last Trade (Real-time) With Time |
| k2 | Change Percent (Real-time) | k3 | Last Trade Size | k4 | Change From 52-week High |
| k5 | Percebt Change From 52-week High | l | Last Trade (With Time) | l1 | Last Trade (Price Only) |
| l2 | High Limit | l3 | Low Limit | m | Day’s Range |
| m2 | Day’s Range (Real-time) | m3 | 50-day Moving Average | m4 | 200-day Moving Average |
| m5 | Change From 200-day Moving Average | m6 | Percent Change From 200-day Moving Average | m7 | Change From 50-day Moving Average |
| m8 | Percent Change From 50-day Moving Average | n | Name | n4 | Notes |
| o | Open | p | Previous Close | p1 | Price Paid |
| p2 | Change in Percent | p5 | Price/Sales | p6 | Price/Book |
| q | Ex-Dividend Date | r | P/E Ratio | r1 | Dividend Pay Date |
| r2 | P/E Ratio (Real-time) | r5 | PEG Ratio | r6 | Price/EPS Estimate Current Year |
| r7 | Price/EPS Estimate Next Year | s | Symbol | s1 | Shares Owned |
| s7 | Short Ratio | t1 | Last Trade Time | t6 | Trade Links |
| t7 | Ticker Trend | t8 | 1 yr Target Price | v | Volume |
| v1 | Holdings Value | v7 | Holdings Value (Real-time) | w | 52-week Range |
| w1 | Day’s Value Change | w4 | Day’s Value Change (Real-time) | x | Stock Exchange |
| y | Dividend Yield |
In my case this was a very simple query in which all I needed was the stock symbol and the last quoted price so I can do a vlookup and plug in the required price in my calculations. The URL I used looks somewhat like this (you can click it to see the resulting data set):
http://finance.yahoo.com/d/quotes.csv?s=bac+bp+c+msft+aapl&f=sl1
This downloads a CSV into your browser but if you put it in as a file connection in excel you’re able to populate a sheet with your stock quotes and then just run a vlookup against it for any information you need.
In Excel you can find the import HTML menu under Data, in the External Data Sources section.
If you can’t find that menu, you need to use the Web Query functionality. From what I see basically you need a Web Query Definition file even for the option presented above. If you need help setting up the definition file or need help accessing that option in Excel, check this post out: How to: Create Web query files for use with Excel for Mac. This should do the trick on a Excel 2011 for Mac.
Related:
If you want to query historical closing prices, you should check out this post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)
If you happen to know cooler tricks or suggestions let me know!
Using Mac for Excel. Under the Data / Get External Data option menu there is no option for HTML.
Any suggstions?
Hi,
I am using Excel 2011 for Mac and under Data there is a group called External Data Sources where the fourth option is HTML. If you have Office for Mac 2011 then you should have it available out of the box, but it definitively is there even if you have to activate the option manually. Please provide us with your version and a screen shot if possible, Thanks!
It’s version 14.4.9.
Under Data, the option you are describing is not coming up.
I have a screen shot, but I don’t know how to attach it to this reply.
I have version 14.5.1 so we’re basically on the same major version and not too far off. There is an alternative method for Excel for Mac, let me document that quickly.
Okay, basically you need to use something called WebQuery. I haven’t used it myself so let me know if it works for you. I’ll try it later on when I have some time. You can read about it here if you like: How to: Create Web query files for use with Excel for Mac
Yes. Tried it, couldn’t get that to work, either.
Also, if I am tracking 100 stocks, wouldn’t I need to build a 100 Web Query files? Doesn’t seem practical.
If you could send me a screen shot of your Excel menu I could call Microsoft and ask what is up and how to get that installed and functioning.
Also, Mac 2015 is due out in a few months.
On the URL you can specify several quotes to get in one web call like this: http://finance.yahoo.com/d/quotes.csv?s=bac+bp+c+msft+aapl+ms+gs+ge+goog+anf+t+m+vod+tef+www&f=sl1. Would that work? Give me a few and I’ll upload a screenshot or email it to you so you can ask Microsoft about it. UPDATE: I added the screenshot to the article so you can copy it.
Try the following. Assume Column A has ticker symbols. Put the following wherever you want a quote to appear:
=IF(A7″”,VALUE(CLEAN(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))),””)
Thanks. Can’t get this to work, though.
When I paste it into a cell, it gives me the exact formula in the cell (in other words, no calculation is taking place) and when I then change the reference cell of A7 to some other cell, it returns an error.
Very strange.
Somehow the copy-n-paste didn’t work right. Let me try again:
=IF(A7″”,NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″)),””)
Somehow the “notequal” () got dropped.
Note that I also changed the formula slightly to use
NUMBERVALUE(WEBSERVICE instead of
VALUE(CLEAN(WEBSERVICE
One less nested function call
Well, that notequal symbol simply will not make it through. You want IF(A7 notequal “”, … but substitute the correct symbol for “notequal” — left angle bracket followed by right angle bracket.
The heart of it is:
NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))
if A7 contains “MSFT” that becomes
=NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=MSFT&f=l1”))
I hope that works!
Still not working.
I had use this. It works great for me.
http://marketxls.com/stock-quotes-in-excel/
NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))
This works fine if entered from the worksheet, but it gives an VBA syntax error if in a macro.
What I’m entering is: Sheets(“Sheet1″).cells(7,”B”)=NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=”&A7&”&f=l1”))
I would send you screen shots, but I don’t know how to attach it to this commenting system.
I think this may work …
http://i1251.photobucket.com/albums/hh541/bertilak/GetQuote_zpsoerttiyh.gif~original
Hey, how did you get that WEBSERVICE function into your Excel, please?
When I try it in my Excel for Mac 2011, it’s simply not available. 🙁
FYI – WEBSERVICE only works in later versions of Excel (at least after 2013). You can still do this with a VBA function – there are a number of web postings out there – for earlier versions of Mac / Windows Excel.
Be careful, though, as I’ve heard there are some discrepancies between the VBA coding for Mac / Windows versions.
Thanks for the tips to capture stock quote Juan.
Any one knows how to capture the option quotes from Yahoo Finance webpage? I am having a difficulty to figure out the table of different columns for option quote using either Excel or Google Spreadsheet.
Any help would be greatly appreciated!
I’ve had no luck with that, but you could give Yahoo Query Language a try: https://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=desc+yahoo.finance.oquote. Let me know if you get it to work, I only played with it for a few minutes but didn’t quite figure it out. Good luck!
Is there a way to pull figures from the “Key Statistics” section of the Yahoo finance? For example, the Quarterly Revenue Growth (YOY).
From what I know, I haven’t seen an option for that (key stats into an CSV file). If you want to do .Net you could use https://code.google.com/p/yahoo-finance-managed/. Another option would be to use Yahoo! Query Language (https://developer.yahoo.com/yql/). There is an API called yahoo.finance.keystats (https://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=desc+yahoo.finance.keystats) that probably does what you’re looking for. Let us know how it goes!
Thanks Juan 🙂
You’re welcome, glad it helped!
Is there any tag to make it date specific? Let’s say I’m looking for the closing value on a specific date.
It is an entire different call. I’ll add a post later on about it but basically here is an example: http://ichart.yahoo.com/table.csv?s=GE&a={date.addMonths(-2).format(‘MM’)}&b={date.today.format(‘dd’)}&c={date.today.format(‘yyyy’)}&d={date.addMonths(-1).format(‘MM’)}&e={date.today.format(‘dd’)}&f={date.today.format(‘yyyy’)}&g=d&ignore=.csv
So, for AAPL dates ranging from the beginning of 2010 to the end of 2013, you would use: http://ichart.finance.yahoo.com/table.csv?s=AAPL&a=00&b=01&c=2010&d=11&e=31&f=2013&g=d&ignore=.csv
Okay, got it documented here now: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)
=WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=”&A4&”&f=r1”)
This returns a dividend pay date of, for ex., “3/10/16”, including the double quotes. Excel doesn’t recognize it as a date so I can’t subtract it from another date. What must I do to have Excel recognize this as a valid date and display it in the requested format?
Thanks for your help.
I’m working with Excel in Spanish so the formula names are in Spanish (and I can’t recall the exact names in English but I hope this helps). Basically we are going to remove the first and last characters of the text you get and then convert it into a DateValue:
=SERVICIOWEB(“http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=r1”)
=LARGO(B3)
=DERECHA(IZQUIERDA(B3,B4-2),B4-3)
=FECHANUMERO(B5)
Largo is Len if I’m not mistaken, Derecha is right, Izquierda is Left and FechaNumero is DateNumber/DateValue. Basically you get the length of the text and remove the quotes by only returning the text minus the first or last character:
Formula Description (Result)
=LEFT(A2, LEN(A2)-2)
Removes last two characters from contents of A2
=RIGHT(A3, LEN(A3)-8)
Removes first 8 characters from A3
Thanks LokiKun. Your idea opened my mind to exploring Excel functions I wasn’t familiar with. The date returned from Yahoo actually looks like “m/d/yyyy” or “m/dd/yyyy” or “mm/d/yyyy” or “mm/dd/yyyy”. I ended up with:
=VALUE(IF(LEN(H4)=11,MID(H4,2,8),IF(LEN(H4)=12,MID(H4,2,9),IF(LEN(H4)=13,MID(H4,2,10),””))))
Fail to get quote from SET (Stock Exchange of Thailand) via excel. for example
=WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=CPF.BK&f=N”)
Does this service support the information from SET or do I miss anything?
It is no longer working from May 2017, what should we do?
Try to use these guys: https://eodhistoricaldata.com/knowledgebase/excel-vba-example/.
Unfortunately, Yahoo Finance no longer work, but I found nice and cheap alternative with examples how to use it with VBA – https://eodhistoricaldata.com/knowledgebase/excel-vba-example/.
=NUMBERVALUE(WEBSERVICE(“http://download.finance.yahoo.com/d/quotes.csv?s=”&A2&”&f=l1”))
I was recently having problems getting stock prices for example, a ticker symbol in column A row 2, until I discovered I had to include “download.” as part of the IRL. Now it works fine.
Hi Henry,
Can you be more specific about what you did to get this working again?
I mispoke, meant URL not IRL. Please notice I use download.finance.yahoo as opposed to finance.yahoo.
Juan, thank you for this…I’ve been looking for a way to get automatic updates of current stock prices. ie: AAPL in my Excel SS. This great!
Can you tell me if it is possible to get the same for option symbols? ie: AAPL170818C00155000. I’ve played with it some, but have not been able to get anything to work.
I heard Yahoo Finance API had been discontinued.
I use MarketXLS instead.
Hi Juan,
Thanks for the great info and help you have provided.
It appears Yahoo Finance has discontinued this very good service. Is there another place we can get this info from that can be downloaded into an excel sheet?
Carlos
I use MarketXLS for this. It really worked well for me.