Sunday, July 7, 2013

Fetching Stock Quotes with Excel 2013 and Windows 8

Unfortunately, the MSN Stock Quote tool that I have been using for years in Microsoft Excel no longer works for fetching stock prices in Excel 2013.  There are some alternatives out there -- namely this option to enable the PSQ function -- however, that solution doesn't seem to work in Windows 8.

Luckily, I have finally found a solution thanks to this blog and the little known WEBSERVICE function in Excel.  Using the Yahoo Stock quote API, you can easily write a formula that can be copied (or "Filled Down") to adjacent cells.

Assuming your ticker symbol is in A1, to fetch a price, a basic formula would be:
=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1")

Oddly enough, Excel returns this not as a number, but as text.  We can fix this easily by rewriting the formulas as:

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

The parts to pay attention to are at the end.  s="&A1&" tells it that the stock ticker is in A1.
&f=l1 tells it to return the last trade price.

To fetch the name of the stock, you would substitue l1 with n.

So for example, if you wanted to have 3 columns with ticker, name, price, you could accomplish this by doing the following.

A1=GOOG
B1=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=n")
C1=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

So now just setup your spreasheet and use Fill Down (Control D) to fill the columns.





80 comments:

ANOOne Digital said...

I have been using the plugin below for quite some time. Works great for me. It use Yahoo API and integrate it seamlessly with Excel as exposed custom functions.

http://www.technitya.com/content/stock-quotes-excel

ANOOne Digital said...

I have been using the plugin below for quite some time. Works great for me. It use Yahoo API and integrate it seamlessly with Excel as exposed custom functions.

http://www.technitya.com/content/stock-quotes-excel

MarkS said...

This works fantastic for stocks and mutual funds. How do I get it to work for stock options?

MarkS said...

This works great for stock, but when I put an option symbol in, such as SWY131221P00027000. It returns 0. How do I fix this?

Dan Griffin said...

MarkS: You are right...I just tried it on my side. I also tried it with some other variables such as fetching the bid or ask. None seem to work on individual option symbols.

John Dugan said...

Hi, this formula works, but how do you get it to update? Even when I close and open the file the data is stale until I cut and paste the formula back in.

Dan Griffin said...

@John Dugan: Control-Alt-F9

jimiyo said...

Thanks for this post!

Tom Kennedy said...

Thanks Dan. I thought I was going to have to go back to excel 2010.

I got all the extra symbols from
http://www.gummy-stuff.org/Yahoo-data.htm

Bill Church said...

I'm still using Excel 2010 and I can't see to get this to work. Is this a 2013 feature?

Bill Church said...

I'm still using Excel 2010 and I can't see to get this to work. Is this a 2013 feature?

Dan Griffin said...

Yes -- good point. According to the Microsoft site the webservice function was introduced in Office 2013. But if you are using Office 2010, you should be albe to use the old Quotes plugin. http://office.microsoft.com/en-us/excel-help/webservice-function-HA102780998.aspx

Jason Cuffel said...

Thanks for all the helpful tips.

I wanted to try the WEBSERVICE function, but kept getting the error "Missing Symbols List." What am I missing here?

Thanks for any help.

Jason

Subhash Jain said...

This works great for stock symbols as shown by you but when I put an symbol like itc.bo or itc.ns from indian stock exchange. It returns 0. How do I fix this?
scj

Dan Griffin said...

Subhash -- It is likely just not available from the API. You could find another web service that has the india exchange and query the API in the same way.

Subhash Jain said...

Thanks Dan for taking interest and prompt reply.I have noticed that the symbols of india exchange which do not includes .(dot) are working well , but the symbols which includes .(dot) are not responding. hope you can resolve the issue and again thanks in advance.
scj

Subhash Jain said...
This comment has been removed by the author.
Steve Reynolds said...

I created a sample spreadsheet which is included in this note:

http://www.evernote.com/shard/s243/sh/dbdfe06d-fde0-4148-b5f7-81eb19e51b81/2ac79d50ee2b1a0df3ef80896d0eecba

akrocket said...

Is there a way to use the yahoo web service in excel 2013 for a historical date range such as to pull history for YHOO from March 13, 2014 to March 31, 2014? I can't seem to get it to work for a historical range. It works well for the current quote only. Any examples you can give would be much appreciated, thanks!

Dan Griffin said...

akrocket: Looks like it is doable. Here are the instructions.
https://code.google.com/p/yahoo-finance-managed/wiki/csvHistQuotesDownload

akrocket said...

Hi Dan,
I tried using the following code in excel 2013: =NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/table.csv?s="&A2&"&a=00&b=02&c=2014&d=07&e=08&f=2014&g=d&ignore=.csv"))... (where A2 = MSFT), but unfortunately I get only the result of "#VALUE!"...can you see anything I am doing wrong and are you able to get this to work? I was expecting to get the daily history for MSFT from January 2, 2014 through August 8,2014, but no luck

Tim Dill said...
This comment has been removed by the author.
Chris said...

I had errors too, this format fixed it

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1"))

Mr. T. said...

I have spreadsheet with NUMBERVALUE(WEBSERVICE(... but it will not refresh data when I open it.

MW said...

Many thanks Dan. MS help is useless, as usual.

Fred said...

Hi Dan

I have a list of ticker symbols and the fetching works fine. But how do I get the webservice to update the prices constantly, or every minute or so? The F9 does not seem to work. I have 2013 exell and windows 8.

Fred

Dan Griffin said...

Constantly? Not sure. But Control-Alt-F9 will refresh all of them at the same time. You could write a script that executes that every few minutes.

Jason Legro said...

Is there a way to have the cells update rather then click in each cell an hit enter? Thanks.

Dan Griffin said...

Jason: control-alt-F9

Brett Sinclair said...

Can you pull other stats like # of shares outstanding and market cap or enterprise value?

Also, thanks this is great.

Dan Griffin said...

Brett -- you can pull all kinds of stuff. Check the table here: http://www.jarloo.com/yahoo_finance/

odelltrclan said...

This was working fine for me for months after reading your page. Then suddenly, a couple of days ago, it stopped working. I get quotes from 2 days ago but nothing new. What possibly could be causing this. Nothing is working for present quotes.

R. W. DelPorto said...

OMG! THANK YOU, THANK YOU. MSN Money just suddenly stopped working yesterday. I've had Excel 2013 for over a year and yesterday, NO QUOTES. This works, is cleaner and easier and has put me back on track! WOW!

Ron DelPorto,
Erie, PA

Peter Kopcha said...

Great post! Much cleaner than the old MSN solution. What are the codes for other fields, such as yesterday's closing price, EPS, PE, etc?

Dan Griffin said...

Peter, check here: http://www.jarloo.com/yahoo_finance/

Lou Mancini said...

Dan - I can't get control-alt-F9 to work? Can you help? Thanks

Fred said...

This is extremely helpful for getting current stock quotes. I'm wondering if there's a simple adjustment to the formula that will generate a historical quote. Suppose I want to get the closing stock price on 12/18/2014. Is there a simple adjustment to the formula that will give me this price? Thanks.

John Gordon said...

Like Ron DelPorto, I was surprised to find MSN quotes had stopped working. I have implemented this scheme and it seems to be working perfectly. The tickers for the indexes work fine ,too, except that the ticker for the Dow Jones Industrial Index ...^DJI...returns "#VALUE". I have tried variations of the ticker without success. Can you help?

Scott Lockhart said...

First off, Dan you have been incredibly helpful, thank you!

Second, does anyone know how to write a script to refresh the data using CTRL-ALT-F9 and what that would be? I'm assuming a snippet of code to put in VBA. Unfortunately I'm just a beginner with VBA so not sure how to write that.

SimCon said...

If you're using Excel 2010 or earlier, then try the VBA function and sample spreadsheet here

JTH said...
This comment has been removed by the author.
JTH said...

This is wonderful, but when I copy the formula down the column the values are all the same (based on the first quote). The formula is updating the cell value down, but the values are not updating per that cells symbol. Any help would be greatly appreciated. Thank you for posting this!

JTH said...

I think it may be because I am using a 64bit version of Excel. Works fine on 32bit. erg...

Dan Griffin said...

JTH -- interesting. What happens if you paste in a few manually instead of doing copy down. I can't understand why 32 vs 64 bit would affect this.

JTH said...

Dan: I figured out that the issue was more with the sheet I am working with than the Webservice function. The sheet is 60MB or more and I was having memory issues, hence switching to 64bit Excel. Once I closed and restarted Excel the function worked properly. I have since condensed the sheet as much as possible to help with the memory issues.

It doesn't appear that I will be able to use this function in this sheet due to the high quantity of symbols, but it will certainly come in handy for smaller upcoming projects so thank you again for bring this to my attention!

John Kelly said...

Dan: great work love the formula. I added a column to the right for number of shares owned, then did a simple C1+D1 in column E1 and get an error message. I was trying to total the value of the shares I own for particular stocks, but get the #value instead. do you have any thoughts?

John

Dan Griffin said...

John -- it returns the value as text. Are you using the "NumberValue" function I recommended above?

Mark J. Guillen said...
This comment has been removed by a blog administrator.
Jimmy Munyemana said...

Thank you so much Dan! This is exactly what I was looking for.

Jose Ignacio Briceno said...

What is the solution to fix the formula when you get #VALUE! error?

Dan Griffin said...

Jose -- good question. It seems like when I see that, it typically goes away if I refresh.
If you want to make it where it says something different instead of #value, you could wrap the formula in an IfError statement.

David Cook said...

Great Post! Any idea what the "&F=" value should be to get it to return yield?

JJ said...
This comment has been removed by a blog administrator.
JJ said...

I really wonder why my comment has been removed by blog administrator !!!

there is one file which pulls indian stock data from web to excel link is given below.
www.optiontradingtips.com/resources/historical-volatility-nopass.xls

then why Dan's excel file is not able to pull data for Indian indices from Yahoo finance.

kcroadie said...

Dan - did you notice this....stopped working?

=CLEAN(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&C19&"&f=l1"))

I wonder if it si somehting specific to my set-up or are others having the same problem?

Dan Griffin said...

Kcroadie -- Not sure why that's not working for you. Working fine for me.
What if you strip out the Clean function? What if you hard code a symbol? Maybe you can narrow down the problem.

kcroadie said...

Thanks for the quick response. I tried both suggestions with no luck. then retyped the instructions and even copied from the entries above. no luck. it is the strangest thing. I have been using this since I switched to Office 2013 with no problems. I use if for 3 portfolios on 3 different spreadsheets. Non work now. Yours is still working?

Any other suggestions? Other ways to pull down quotes? thanks for any help.

Joel said...

mine has worked for the last year until a few hours ago
any suggestions are appreciated

Dan Griffin said...

Man -- I wonder what's going on. Mine still works.
Here is an example of one of mine:

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&K2&"&f=l1"))
where K2 is the cell with the symbol.

Dave LaGrange said...

I am having the same problem as Joel. Everything was working fine last night. This morning when I opened the same program everything looked okay until I changed the stock symbol. Now all I get are #VALUE in the cells where the price used to be.

Any ideas are greatly appreciated!!

Dan Griffin said...

Out of curiosity, what happens if you paste this into your browser?http://finance.yahoo.com/d/quotes.csv?s="wmt"&f=l1

Does it sent you a CSV file with one price listed in it (for walmart)?

Tim Dill said...

Hi Dan - I'm having the same issue. My spreadsheet was working last night. Today I get only #VALUE! If I past the information from above, http://finance.yahoo.com/d/quotes.csv?s="wmt"&f=l1
,into my browser, I get the following:

Your browser sent a request that this server could not understand.


Please check the URL for proper spelling and capitalization. If you're having trouble locating a destination on Yahoo!, try visiting the Yahoo! home page or look through a list of Yahoo!'s online services. Also, you may find what you're looking for if you try searching below.

Dave LaGrange said...

Thanks Dan. I got the same response as Tim for this (Your browser sent a request that this server could not understand). Is this a Yahoo problem? If so, do you know who to contact to let them know of this? Thanks for your help!

John Reynolds said...

I have been using this daily for many months with no problems. It has stopped working for me today, too. It is not an Excel problem. There is something wrong with the Yahoo download, which leads to Excel not getting any data, which causes the #value error. If you post the download query in the browser, it returns an error now, just like Tim said. I don't know if this will be fixed overnight, or if it will be a permanent error. Downloading stock quotes is like Whack a Mole. Every time you think you have it working, another error pops up.

John Reynolds said...

8:04pm Central time, back to working again. I made no changes. I guess it was just down on Yahoo's end.

Dave LaGrange said...

Glad to hear that Yahoo is working for you again. I hope mine comes back soon!

Tim Dill said...

Mine is still down...

Daniel Palm said...

Mine stopped working a few days ago and is still not working.

Dave LaGrange said...

If this is working for some and not for others I'm wondering if there is some kind of setting on either my computer regarding external data or an internet setting?

Daniel Palm said...

Dave -

I am thinking the same thing.

I'm updating this at work which has always worked in the past.

I changed the trust center external content settings and it still does not work.

Maybe the problem is related to network security.

kcroadie said...

Folks -

I am still receiving the $VALUE! error.

Trying to narrow down the problem...if others are having the problem but yet some others are not does that mean it is not likely to be a problem at Yahoo?

So then what changed with my computer or ISP? And is it similar to yours? The security system I use provides frequent automatic updates. Could it be an update created the problem? (sent an e-mail asking...) Could it be Time Warner my ISP? Does anyone else having this problem use Time Warner? Does anyone not having the problem use Time Warner?

Last has anyone found any other fix? Thanks in advance for any help you can provide.

Dave LaGrange said...

I use CenturyLink DSL and the problem happened overnight on both my office and home computers which both are connected using CenturyLink DSL. That maybe the culprit although I'm concerned about trying to work with customer service on this technical of a problem.

John Reynolds said...

I am on Windows 8.1. Yesterday, it stopped working for me. In the evening, an auto update was applied, requiring restart. I restarted and the downloads worked again. I thought it was just timing of a yahoo fix, but since it isn't working for others, there may be a Windows or Office/Excel update that you need to apply and then restart.

Dave LaGrange said...

That's interesting John. I will try this as I see there is an update available. Thanks!

Dave LaGrange said...

The update that was applied didn't seem to fix my problem. Thanks for the info though. Any other ideas are greatly appreciated.

Tim Dill said...

Has anyone contacted Yahoo? Perhaps someone with an IT background could discuss the issue with them. It must be an excel or windows update that has effected the function. Perhaps even an update of Internet Explorer. IE 11 has caused many issues at my workplace with broken data pulls

Dave LaGrange said...

As mysteriously as it stopped working, this morning everything is back to normal and working fine. Still not sure what caused it. Did your's start working yet, Tim?

kcroadie said...

Folks - it has resumed working on my spreadsheets also. I noticed a large amount of Windows updates on another computer I use with the same issue. Perhaps that was it. Anyways thanks to Dan and others for chiming in. Hopefully when we all meet again it will be for happier reasons.

Tim Dill said...

Thanks everyone! Mine is now working. I even printed out the support notes from work to use on my home computer tonight, glad that I don't have to do that!

Regards to everyone!

Joe D said...

When I type this URL in the address bar, I get immediate response from yahoo and a CSV file is created. http://finance.yahoo.com/d/quotes.csv?s=IBM+AAPL+MSFT+GE+FB&f=nl1t8jkc1yr1

When I try to obtain quotes using Excel 2013 =WebService function, it takes Excel forever to retrieve the data. 10 mins for 5 quotes. Can't seem to figure out why Excel is very slow. Running latest 2013 version with all patches applied. Any ideas?