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.





22 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.