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://download.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://download.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://download.finance.yahoo.com/d/quotes.csv?s="&A1&"&f=n")
C1=NUMBERVALUE(WEBSERVICE("http://download.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.

UPDATE:  As of May, 2017, Yahoo changed the address.  I have updated the address above.  However, if you had it working previously, you may need to change the address in the formula from http://finance.yahoo.com to http://download.finance.yahoo.com.  Thanks to a reader for figuring this out and posting the solution in comments.

If you already have your spreadsheet setup with the old method, you can do a 'replace all' by changing "/finance" to "/download.finance".





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

Unknown 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 said...

@John Dugan: Control-Alt-F9

jimiyo said...

Thanks for this post!

Unknown 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

Unknown said...

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

Unknown said...

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

Dan 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

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

Unknown 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

Unknown said...
This comment has been removed by the author.
Unknown 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 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 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"))

Thaddeus Konar 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 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.

Unknown said...

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

Dan said...

Jason: control-alt-F9

Unknown said...

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

Also, thanks this is great.

Dan 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

Unknown 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 said...

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

Unknown said...

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

Gary B 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?

Unknown 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 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!

Unknown 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 said...

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

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

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

Unknown said...

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

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

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

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

Unknown 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!

J.R. 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.

J.R. said...

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

Unknown said...

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

Tim said...

Mine is still down...

Unknown said...

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

Unknown 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?

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

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

J.R. 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.

Unknown said...

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

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

Unknown 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 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?

wen vu said...

Joe D,

I'm having the same problem, wherein Excel takes forever to calculate and oftentimes freezes. Any solutions yet guys?

wen vu said...

Guys,

I've noticed that the WEBSERVICE function and Alt+Ctrl+F9 works perfectly when using a wired LAN connection. However, it does not work well with wireless connected computers. Anyone noticing this same result?

Jurookie said...

Hi guys,

I think this is the answer you were looking for:
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&$C6&"&f=j");".";0)

This is what I used to get rid of the value error and get a workable number to use in further equations.

regards

Henry said...

Dan, thanks for this very useful article and your responses to questions from readers concerning their how-to questions. The WEBSERVICE info worked perfectly for me and was very easy to implement by following your instructions.

Unknown said...

Dan, Thanks a bunch! This is working GREAT for me.
I am having a problem with the Dow Jones index though...

Yahoo shows it as ^DJI, but that isn't working. (^GSPC and ^IXIC are working fine.)

Doug

KS said...

I had the same problem with ^DJI. I use DIA instead.

Unknown said...

Thanks KS! I just switched it out with success.
Just using it as a benchmark to track my returns against the "index".

KS said...

My stock quotes stopped working today. Anyone else having the same problem? Been using this for about a month with no problem, then today all fields are #VALUE!.

Unknown said...

Mine is still working fine. Must be something local to your system.
Try refreshing the spreadsheet, or closing other apps to free up memory.

Unknown said...

Mine has stopped working for past 2 days. Anyone else having this problem.

Also does anyone know how to get this working with google finance?

captain_insano said...

This formula "=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))
" works for me in Excel for Windows. When i try to open the same spreadsheet on a Mac, and do a 'Ctrl+Alt+F9' to refresh the quotes, it doesnt work.

Is there a different shortcut/workaround for 'Excel for Mac'? Any idea?

Thanks in advance.

Dan said...

Sorry -- I don't know what the Mac specific commands are.

Unknown said...

Is there a way to apply this functionality to another source, e.g. wsj.com?

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

Thank You

eFinancial Models said...

Excellent financial model spreadsheet

wegmand said...

My spreadsheet stopped retrieving quotes yesterday so I investigated. It looks like Yahoo changed the URL for the data:

OLD URL: http://finance.yahoo.com
NEW URL: http://download.finance.yahoo.com

I fixed my spreadsheet with a 'replace all' by changing "/finance" to "/download.finance"

John Gordon said...

Dan, I experienced the same outage at the original Yahoo address and found the same change fixed the problem. I could not find my link to this blog to post the correction, so thanks for posting for all of us.

Tim said...

Dan, Thanks for identifying the fix for this issue!

Dan said...

@Dan Wegman -- Thanks so much! I'll update this blog.

Denis Alaev said...

Hi, Dan Griffin!

It seems that after several changes Yahoo Finance closed their API forever. I think, it’s a part of cost cutting strategy from Verizon. The API was closed on May 15, 4 months ago.

I've tested several alternatives and found that https://eodhistoricaldata.com the best one for those who used Yahoo Finance. They even have "Migration Manual": https://eodhistoricaldata.com/knowledgebase/adapt-old-yahoo-scripts-eod-historical-data/.

Highly recommend you to test this service.

wegmand said...

Thanks Denis, but I'm not confident that Dan Griffin will update this blog. Despite his statement to do so, he still hasn't updated the blog to reflect my comments of May 31st. It's unfortunate since this blog still comes up when searching for this topic. But I guess as long as the rest of us augment the comment section, the blog will remain relevant.

So far, my spreadsheet still works using the data from http://download.finance.yahoo.com

wegmand said...
This comment has been removed by the author.
Denis Alaev said...

Hi Dan Wegman, I see, thanks for information. Perhaps, someday Dan Griffin will back and update.

Good luck!

Unknown said...

The Yahoo link is still working fine for me also. If anyone finds a better "free" source, hopefully they will post it here and elsewhere so we can find it. ;-)

Doug

Dan said...

Sorry....it's updated now.

Tim said...

The API seems to not be working today.

Clinton said...

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?
September 4, 2013 at 2:40 PM

Can someone tell me if we can get option info such as the option symbol and option last price in a similar was as we get stock last price?

Chris Wong said...

http://download.finance.yahoo.com ... the API is not working since Nov 1, 2017

Denis Alaev said...

There is an API for former Yahoo! users:
https://eodhistoricaldata.com/knowledgebase/adapt-old-yahoo-scripts-eod-historical-data/
It's very easy to migrate.

wegmand said...

Maybe easy for you, but I don't want to create a separate worksheet full of historical data for each stock. I need to know EXACTLY how to change the formula in the cells that used to fetch stock data, e.g: AAPL...

Last trade:
=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1")
Previous close:
=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=p")
Change:
=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=c1")

Any ideas if this is even possible, like maybe with Google Finance?

MarkS said...

It seems to me this should not be rocket science. All we want to do is have our portfolio spreadsheets updated with the closing stock price from the previous day. NOBODY has a quick and easy link to accomplish this? I find that hard to believe.

Unknown said...

Same problem here - stopped updating current stock price earlier this week. Wonder if anyone has found a fix (preferably through yahoo). Except for having to do a Ctl + Alt + Shift + F9 for each update, the formula =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&D4&"&f=l1")) has worked for quite a while. Hope someone can post in the comments on how to fix!

wegmand said...

Yahoo pulled the plug: https://yahoo.sdx.socialdynamx.com/portal/conversation/19248672

wegmand said...

Looking for an alternative to Yahoo Finance, I discovered a free API here:

https://iextrading.com/developer/docs/#iex-api-1-0

I'm not a coder so I'm only able to use it to import data into Excel in a very cumbersome way. It's ugly, but it works. I'll post an example if anyone's interested but I'm hoping for a more elegant solution from someone who's better at this stuff.

John Gordon said...

Dan, the code is all Greek to me. If you can post an example of fetching last price that that we can put into Excel, that will be extraordinarily helpful. Thanks.

wegmand said...

Okay, just copy the following line into an Excel cell for the last price of AAPL (or aapl). The symbol isn't case sensitive:

=WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/price")

The result is a number. The other parameters like 'change' and 'previousClose' return text strings like:
{"change":4.39}
{"previousClose":168.11}

I was able to figure out a lengthy formula for text strings that figures out the string length, cuts out the non-numeric characters, and finally converts the result to a number:

=NUMBERVALUE(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/quote?filter=change"),11,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/quote?filter=change"))-11))

=NUMBERVALUE(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/quote?filter=previousClose"),18,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/quote?filter=previousClose"))-18))

See the 11's in the first one and the 18's in the second one? They're related to the number of characters in the parameter name. To figure out the correct number, count the number of characters (change=6, previousClose=13) and add 5 (to account for the {"":} characters in the string value.

The NUMBERVALUE function converts the string to a number so you can perform math functions.

Finally, if you have a list of stock tickers in your spreadsheet, you can replace the symbol (like AAPL) with its cell reference. So if AAPL is in cell C24, replace AAPL in these formulas with "&C24&" And yes, you must include the quotes. (Having your symbols in a list makes it easy to auto-fill the cells with the formula.)

I sent an email to the developer asking if there was a more elegant way to get the numeric result for the parameters directly without having to use these cumbersome conversion formulas. I'll post an answer if I get it.

MarkS said...

Dan-

This works great for stocks. Do you have something similar for mutual fund quotes? Using just the five charchter symbol returns #VALUE, so does changing/stock/ to /mf/ or /mutualfund/

Dan said...

Interesting. When I go to that url we use in excel in a browser, it says "It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com."

Unknown said...

This is helpful. Dan W. - do you know the parameters for extracting company name, 52 wk hi, low, etc.?

Unknown said...

Dan,
Great work! I just tested it to grab a stock price & it works like a charm.
I made the following change to simplify things.
=DOLLAR(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B3&"/price"),2)

The stock symbol is in cell B3 and the dollar function converts the result to currency with 2 decimal places.

Unknown said...

I found it needed one additional change.
The Yahoo function expected BRK-B, this works with BRK.B

Now we need to get it working for Mutual Funds... :-)



wegmand said...

To Scott L: The names for other parameters can be found on the user's guide of the API at this page: https://iextrading.com/developer/docs/#quote

There are many parameters available! It's a long list, but here are the 3 you asked about.

Company Name = companyName
52 week hi = week52Hi
52 week low = week52Low

To Doug R: Yes, I'm hoping that Mutual Funds will be available from IEX too. There's already a request for it (https://github.com/iexg/IEX-API/issues/16). Please notice that despite all of its awesome capability, this API is still in Beta phase (https://iextrading.com/developer/docs/#roadmap).

Tim said...

The current price works well but I am unable to get any other data such as the previous close.

wegmand said...

Tim, see my previous post from November 3,2017 at 7:41 PM:

http://slowwealth.blogspot.com/2013/07/fetching-stock-quotes-with-excel-2013.html?showComment=1509752498891#c5163202872637158319

Tim said...

Hi Dan - Thanks for this very valuable information and help. I'm able to get the price, previous close, and open. I'm unable to get the high, low, and float. I'm also unable to get the Nasdaq and S&P 500 price, high, and low. I would appreciate any help on this one.

wegmand said...

Tim, the documentation page is pretty long. Be sure to scroll through the list, or while viewing the web page in your browser, click Control-F to open a search box and enter the term you seek.

I found 'high' and 'low' under "Stocks" in the "Previous" section:
https://iextrading.com/developer/docs/#previous

'float' is found a little further down in the 'Key stats' section:
https://iextrading.com/developer/docs/#key-stats

I can't find any parameters for the various market indices but maybe they will be available in a future version of the API. Or maybe someone else can figure out how to retrieve them.

Unknown said...

What are the correct formulas for the other metrics on that list? The price & previous close work for me but when I tried swapping out the "price" part and replacing with other metrics I didn't seem to get it to work:

=WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/week52High")

wegmand said...

Hi Scott,
You almost have it, but notice that the 'week52High' parameter is under the "Quote" section which includes a bunch of string output parameters. So you have to use a formula that filters out only the 'week52High' parameter from the 'quote' group:

=WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/quote?filter=week52High")
The result should be:
{"week52High":174.26}

If need just the raw number, refer to my previous post from November 3,2017 @ 7:41 PM. The number of characters you need to remove for 'week52High' is 15 (and for 'week52Low' it's 14).

(I feel like we've hijacked Dan Griffin's blog!)

Tim said...

Yes, that is my problem.

The following formula produces an open for a symbol in A1:

=DOLLAR(NUMBERVALUE(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&$A$1&"/quote?filter=open"),9,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&$A$1&"/quote?filter=open"))-9)))

The following formula will not return the high for a symbol in A1:

=DOLLAR(NUMBERVALUE(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&$A$1&"/quote?filter=high"),9,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&$A$1&"/quote?filter=high"))-9)))

Not sure why?

wegmand said...

Tim,
I don't get an error when using your formula for 'open'. Are you using a foreign stock or mutual fund symbol? So far this API only works for individual US stocks. Test with AAPL, VZ, GOOG, etc.

The 'high' parameter is in the "previous" group, not the "quote" group as your formula is written. Just change the occurrences of 'quote' to 'previous' and it should work.

Notice however, that 'week52High' is in the 'quote' group. Evidently, 'high' = previous session high whereas 'week52High' is... well, the 52-week high.

J.R. said...

It works for ADR's of foreign stocks, such as RDS.B, BP, RY, etc.

Unknown said...

Perfect, thanks Dan, this works now. And for all the "string" type metrics (ie companyName) I should just have to remove the "NUMBERVALUE" portion of the equation found at the beginning and it looks like it should work. Thanks!!

Tim said...

Thanks Dan, that fixes my problem! I didn't understand the commands were in sub-headings such as quote and previous.

Chris Wong said...

thanks all... but doesn't work for foreign stocks like Hong Kong Stock Exchange and...

John Gordon said...

Thanks to all contributors. This solves the problem for domestic stocks and some ADRs like VOD, but not NSRGY or ADDYY. Also eager to get mutual fund prices.

Unknown said...

I'm looking for and testing anything I find for Mutual Fund prices. If I find something that works, I'll post it here & am hoping others will do the same.

Denis Alaev said...

Hi all! I have a solution which supports foreign stocks and Mutual Funds. I carefully follow your discussion here and I think we can suggest you the solution:

With EODHistoricalData service (https://eodhistoricaldata.com/) and parameter 'filter' it's easy to get any field from Live (or Realtime) API with only one number:

=WEBSERVICE(https://eodhistoricaldata.com/api/real-time/AAPL.US?api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX&fmt=json&filter=close)

And nothing more, you need no parse and format this data, we checked in Excel recently.

This API is not free, but it costs not a small amount and, in compare with IEX, it supports foreign stocks, mutual funds and everything else.


More information can be found here: https://eodhistoricaldata.com/knowledgebase/live-realtime-stocks-api/

I hope, it was helpful for you.

Dan said...

I discovered something that Excel calls "Additional Actions." It sounds promising, but at the moment, only the "date" tags are showing up on my excel -- but supposedly it offers stock price updates. There is likely a way to enable the stock functionality documented here: https://support.office.com/en-us/article/Built-in-Actions-8d01d867-4feb-4910-9072-afd2f9836609?AssetID=HA001050482&ver=15&app=excel.exe&CorrelationId=8112f756-1bce-4b37-8dff-c7e322bc9923&ui=en-US&rs=en-US&ad=US&ocmsassetID=HA001050482

Glenn said...

I like to show the last update date/time for each stock on my spreadsheet. B20 is the cell reference to the stock symbol in the spreadsheet. The latestUpdate value in the quote section is given in milliseconds since Jan 1, 1970. The formula below converts that to the current date/time. The 1000, 60, 60, & 24 converts from milliseconds to seconds, seconds to minutes, minutes to hours, and hours to days, respectively. The 25569 is the date number for Jan 1, 1970 in Excel. The resultant date/time answer given is GMT, so I had to subtract 0.25 to deduct 6 hours (1/4th of a day) to show the date/time for my Central US Time Zone. Eastern time would use 5 instead of 6. I believe the time for the lastUpdate value is 15 minutes delayed.

=MID((WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B20&"/quote?filter=latestUpdate")),17,13)/1000/60/60/24+25569-0.25

Dan said...

Nice work Glenn. I was trying to solve the same thing. So for eastern time, it is:
=MID((WEBSERVICE("https://api.iextrading.com/1.0/stock/"&K2&"/quote?filter=latestUpdate")),17,13)/1000/60/60/24+25569-(5/24)

Glenn said...

That’s correct Dan – 5/24 for Eastern Time. I suppose we’ll have to change that when we go back on Savings Time. Each time you press Control+Alt+F9, you can see the time change on a fast-trading stock.

I don’t know how to solve the mutual fund issue, but I am using a work-around that permits me to keep using my simple main spreadsheet. Basically, I use the watch list feature offered by my brokerage firm’s web site to list the mutual funds I have. The watch list gives me a choice of many parameters (price, 52 week high, etc.). I export that watch list as an Excel file and then copy that data to a set location (such as cell A1) on my main spreadsheet. The main spreadsheet has already been mapped to use the new data, so when I paste the new data, the new values automatically go to where I need them on the spreadsheet. For example, when I paste the new data, the newly pasted price for mutual fund #1 may be in cell B1 and I need that price info to be in cell K22. In cell K22, I already have the formula “=B1” (without the quotes). Since the mutual fund parameters I am interested in change only once per day a few hours after the market closes, I only need to do this export, copy, and paste operation once in the evening.

MarkS said...

The lack of a previous close link for mutual funds is very perplexing to me. Way back, there was a function in Excel that worked with Microsoft Money, I believe, that allowed me to link stocks and mutual funds previous close price to a cell in Excel. When that API stopped working, I found the Yahoo one, which worked in a similar fashion (i.e. it did not matter if the ticker symbol was a stock or a mutual fund). Now that the Yahoo API has been shut off I am searching for an API that will return previous close for a mutual fund in an Excel spreadsheet.

Unknown said...

I recommend using MarketXLS. It is very helpful and it works for me.

Unknown said...

I've looked at the MarketXLS and see several of their function names but can't get any to work. IG: Ask(Symbol). I've tried =Ask(MSFT) and get #NAME?. I'm making the assumption you'd need at least the basic version at $190/year for it to work. Is that correct?

Denis Alaev said...

Try to use these guys:
https://eodhistoricaldata.com/knowledgebase/excel-vba-stock-api-example/

much-much cheaper, starts from $9.99 per month.

Dan said...

$10 a month to fill in a few excel fields seems high. Especially since iextrading is mostly working.

Denis Alaev said...

iextrading has only top 6000 US stocks, while EOD has data for more than 95k stocks including ETFs and Mutual Funds, which iextrading never had.

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

Thank you all SO MUCH. The IEX API does the trick.
Since Yahoo Finance (aka Verizon) discontinued support for WebService, I've been pulling out my few remaining hairs, trying to restore my models in Excel. I spent a good part of this week experimenting with TDAmeritrade's Thinkorswim RTD function, but in situations where the workbook is set to calculate manually, it's flaky, at best, and doesn't work at all when calculating via VBA. TDAmeritrade has been entirely unhelful - I guess I won't be opening an account there.

One think I noticed is that the "..../dividends" function often fails (e.g., with SYmbols, T, VZ, CTL, GME...) although most of the data I need can be retrieved from other functions (e.g., .../stats).

If I can return the favor...

Glenn said...

Paul, This is how I get the annual dividend per share. It has worked with T and the other stocks I have. Stock symbol is in cell B22.

=(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B22&"/stats?filter=dividendRate"),17,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B22&"/stats?filter=dividendRate"))-17))

Gibberish said...

Anyone come up with a solution for mutual funds yet?

Denis Alaev said...

Yes, all Mutual Funds fully supported in https://eodhistoricaldata.com.

Gibberish said...

Denis,
Not sure if you work for this company or what, but I am looking for a free service, not a service with a recurring monthly fee. I only need the data to help me rebalance my portfolio.

Denis Alaev said...

Collecting and storing the data, checking for errors, providing an API for it with good support costs money, it couldn't be provided for free with a good quality.

What's better for you: pay $9.99 per month (two pints of beer, actually) for good service or find something without any service and guarantee and then lost a lot of money only because your portfolio re-balancing wasn't good enough because of lack of the data or gaps or other errors?

It's up for you, of course, but in my opinion, miser pays twice.

Unknown said...

Frankly, Denis you're missing the point for many of us. We don't need gigabytes of data and we aren't going to be hitting the api hundreds of times a day, we just want an easy way to grab yesterday's closing price for a few mutual funds.

With "current" stock & mutual fund data available on literally hundreds of websites, it's a shame no one is interested in providing this simple service to consumers.

Why not offer a "registered user" tier, with limited functionality ("current/last" price) to introduce users to your offerings?

Denis Alaev said...

"Why not offer a "registered user" tier, with limited functionality ("current/last" price) to introduce users to your offerings?"

Because "registered users" should be supported too and it costs money.

"We don't need gigabytes of data and we aren't going to be hitting the api hundreds of times a day, we just want an easy way to grab yesterday's closing price for a few mutual funds."

I understand it, but actually gigabytes of data usually costs hundreds and thousands of dollars, you can ask Thomson Reuters, for example.

Frankly, I do not understand you. You invest thousands of dollars and do not want to pay even $10 dollars per month just for good data? Just to be sure you can keep your thousands for safely? What's the point? I could understand if service will cost $100-200-300 or if it will yearly only, but it's not $100 or even $50 dollars.
Two cups of coffee in Starbucks or two pints of beer costs the same price.

Actually, I read this thread and got that some of you already waste much more money, trying to find a free solution with doubtful data and without any support (I guess, your hour costs a lot), than a yearly subscription with good data and support.

Unknown said...

Denis,

Thank you for your response.

I can't speak for others here, but I'm retired and for $10 a month, I can look up the 15 fund prices I need manually in 5 minutes or so. Hell, I'm looking at news & prices regularly anyway, so keeping my spreadsheet up in the background isn't a big deal.

I manage my portfolio myself because I don't want to pay others for things I can do. I enjoy the learning process and the sense of accomplishment for building my own tools. I'd rather it was "automatic", and maybe I'd find a use for your other functions, but the last thing I need is another monthly "subscription".

I wish you & your company well & if you ever offer an "entry-level" or "no support" tier or pricing, please let us know.

Doug

Chris Wong said...

I read about Alpha Vantage on the web, is it a good free source replacing Yahoo ?

wegmand said...

Just checked up on an Excel addin that I once tried and later rejected once I figured out how to use the WEBSERVICE formula and Yahoo Finance data. The free "Stock Connector" Excel addin by Michael Saunders can be found on the Microsoft Store:

https://appsource.microsoft.com/en-us/product/office/WA104379220?src=office&corrid=ad2fed9a-d01e-482e-bd9e-5c1a7d12de7f&omexanonuid=83812aa9-be2c-46e1-ac75-a8476c95766e

Reading the description, it seems that Mr. Saunders was also impacted by the demise of the Yahoo Finance data source so he's updated his tool to use the free data from IEX too. (Great minds think alike?) Kudos to Mr. Saunders for creating a cool addin for grabbing stock data and keeping it updated. It provides many features - more than I need at this time so I'll continue to use in-cell formulas for my worksheets. But the Sock Connector addin may be a good fit for more data hungry users, and definitely worth a look.

fausto said...

I may have missed it in the thread, but can anybody suggest an Excel cell formula formula to replace the old webservice one that looks up a ticker symbol and returns the full company name? I had been using =WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=[ticker symbol]&f=n") but it looks as though Yahoo discontinued that too.

Denis Alaev said...

You can use it with EOD Historical Data API:
https://eodhistoricaldata.com/knowledgebase/api-for-historical-data-and-volumes/#Filter_fields_WEBSERVICE_and_YAHOO_Support

WEBSERVICE Excel function works perfect with parameter "filter", like this:

=WEBSERVICE("https://eodhistoricaldata.com/api/eod/AAPL.US?api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX&fmt=json&filter=last_close")

fausto said...

Denis, your formula takes the ticker symbol and returns the price. I'm looking for a formula that takes the ticker and returns the name of the company.

Denis Alaev said...

Then you need something like this:

=WEBSERVICE("http://eodhistoricaldata.com/api/fundamentals/AAPL.US?api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX&filter=General::Name")

Denis Alaev said...

From Fundamentals API Endpoint: https://eodhistoricaldata.com/knowledgebase/stock-etfs-fundamental-data-feeds/

Glenn said...

fausto, I have been using this. The ticker symbol would be in cell B68 in this example:

=(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B68&"/quote?filter=companyName"),15,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B68&"/quote?filter=companyName"))-15))

fausto said...

Thanks, Glenn! That worked for me! I have been stuck without either price or name formulas ever since Yahoo killed its links. I had been following a lot of stocks and entering data by hand was overwhelming. I didn't know what my problem was until I stumbled across this discussion. You guys are lifesavers.

Glenn said...

fausto: You’re welcome. We all design our spreadsheets differently. My sheet uses the iextrading.com information for the following items: company name, sector, previous day’s close, current price, PE, 52 week lo, 52 week hi, ex-div date, date & time of latest price update, and annual dividend per share. Let us know if you need any of these. At a minimum, I suggest you use the date & time of the latest price update. That tells you if the website is working properly. If there has been no change to the date & time of the latest price update on your sheet after you press Ctl/Alt/F9, that tells you something is wrong (unless the stock price doesn’t change much during the day due to a low trading volume).

Dan said...

Do any of these options support mutual fund quotes? I've been typing in the mutual fund quotes manually for the last several months.

Glenn said...

Dan Griffin: No more info since we last discussed the mutual fund issue here on Nov 8, 2017. I still have my mutual fund tickers permanently in my brokerage’s web site watch list. I export the watch list tickers and prices each night, and copy and paste them into my stock spreadsheet. Since the fund prices change only once a day, I only have to do this copy-and-paste exercise only once a day. I'm happy with this method to get my mutual fund info into my stock spreadsheet.

Dan said...

Yep -- that's what I've been doing as well. Probably not every night -- just once or twice a week at most. Just seems like there has to be a way. Thanks for the update.

Denis Alaev said...

Dan Griffin: Iextrading supports only around 5500-6000 most active US stocks. Basically, only stocks that are traded on this exchange. eodhistoricaldata.com supports all 45 000 US symbols + foreign exchanges.

Chris Wong said...

does eod support penny stocks (overseas exchanges) with 3 decimal places?

Denis Alaev said...

Chris: in general, yes, but it depends on the exchange, you can ask the support directly about the particular exchange and even symbols: support@eodhistoricaldata.com, usually you will get an answer in several hours or less.

wegmand said...

I notice that this thread remains dormant until there's a change in where we get our data, then there's a flurry of activity. I'll start it off this time...

If you've been using the WEBSERVICE from iextrading.com, you'll notice that it doesn't work anymore. BUT, they've updated everything so you'll just have to change your Excel formulas to keep getting the data. A few of the things that have changed:

1. You'll have to sign up for a FREE account to get a required API token. Start here:
https://iexcloud.io/
You can use a secret token or a publishable one, but it must be included in each cell where you use the WEBSERVICE function.

2. Read about how to use the new data in Excel here:
https://iexcloud.io/docs/api/#excel-how-to
There's a sample Excel file on the site that you can download to examine the formulas.

3. Good news!! Mutual Fund quotes are supported!

John Gordon said...

Good news, indeed.
I can confirm the post above. I made the changes to the API calls and it began working today, including for mutual funds. Indexes like SP500, Nasdaq and the Dow must be updated manually. The presentation at https://iexcloud.io/ is not particularly clear, but if you follow the steps in the post above, it should work at least for fetching security prices. I had to modify some simple functions I have embedded in my Excel spreadsheets that work with the prices returned by iex.

J.R. said...

I was able make this work, too. It is nice that mutual funds are included now. However, beware, the mutual fund end of day prices are not updated at the 6pm Eastern posting deadline time like they are on yahoo and other sites. Today, after 8pm, the mutual fund prices are still the prior day's closing prices. I don't know what time overnight the correct daily mutual fund closing prices are posted, but they are there the next morning.

MarkS said...

Just tried it for mutual funds and it works great! Thank you!

Tim said...

Hello. Sorry for being slow... I was able to make

=WEBSERVICE("https://cloud.iexapis.com/stable/stock/aapl/quote/latestPrice?token=YOUR_TOKEN_HERE")

work in Excel but unable to reference a cell such as A1. How do I do that? A1 contains the ticker symbol.

odelltrclan said...

Does anyone know if it works on preferred stocks?

John Gordon said...


Tim, replace aapl with "&A1&" including the quote marks in the WEBSERVICE call.

wegmand said...

Tim,
Download and examine the Excel sample file from the "Download it here" link just below your example formula on the same page: https://iexcloud.io/docs/api/#excel-how-to

You'll have to use the NUMBERVALUE, WEBSERVICE, and CONCATENATE functions in the formula to build the textual reference to the data page. You're formula will eventually look something like this one that I use:

=NUMBERVALUE(WEBSERVICE(CONCATENATE($B$16,B21,"/quote/",$C$17,"?token=",$B$15)))

$B$16 = https://cloud.iexapis.com/stable/stock/
B21 = AAPL
$c$17 - latestPrice
$B$15 = YOUR_TOKEN

Copy the formula above for each cell containing a ticker value (B21 in my example).

Tim said...

Thank you John and Wegmand! I was able to make John's solution work on my spreadsheet. Thank you for help!

One last question, is there a ticker for the NASDAQ and SP500 besides the ETF versions?

Glenn said...


Has anyone developed a formula to get the dividend for all of the stocks in their portfolio? Modifying the formula Wegmand posted on June 21, 2019, I have found the following formula that works for many, but not all, of my stocks:

=NUMBERVALUE(WEBSERVICE(CONCATENATE($B$16,B21,"/stats/",$C$17,"?token=",$B$15)))

Note that /quote/ in Wegmand's formula was changed to /stats/

$B$16 = https://cloud.iexapis.com/stable/stock/
B21 = T
$C$17 = dividendYield
$B$15 = YOUR_TOKEN

That formula gives the annual percent yield (for this AT&T example and it would give about a 6% yield).

Sometimes there is more than one way to get the same information. So, has anyone found a different formula to get the annual dividend yield (either percent or dollar amount)?

Glenn said...

Perhaps this is nothing new to members of the blog, but it has been a long time since I last worked on fixing my spreadsheet so that I can update the stock prices using control-alt-F9. This is what I came up with:

=(WEBSERVICE(CONCATENATE(Y2,J17,"/quote/",W16,"?token=",Y1)))

Where the contents of the Excell cells are as follows:
Y2 = https://cloud.iexapis.com/stable/stock/
For AT&T
J17 = T
W16 = latestPrice
Y1 = YOUR_TOKEN

This has also worked to get the latest price for my mutual funds.
However, I have not been successful in getting other parameters such as the dividend yield or the dividend amount per share.

Chris Wong said...

Microsoft released a feature to Office 365 that allows Excel users to pull real-time stock prices into their spreadsheets.
https://www.thespreadsheetguru.com/blog/add-real-time-stock-prices-and-metrics-to-excel

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