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





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

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?

Julien Desrosiers 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.

Doug Rickenback 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.

Doug Rickenback 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!.

Doug Rickenback 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.

FlyHigh said...

I had use this. It works great for me.
http://marketxls.com/stock-quotes-in-excel/

Rsc Rsc 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 Griffin said...

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

Kevin Terry said...

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

Kevin Terry said...
This comment has been removed by the author.
Bob Bianca said...

Thank You

Harz said...

For this, I had been using this and it's great.
It has instructions which can help you.
Stock Quotes in Excel
Just visit the site for more info.
They also have a live support and a forum to help you with your questions/concerns.

eFinancial Models said...

Excellent financial model spreadsheet

Dan Wegman 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 Dill said...

Dan, Thanks for identifying the fix for this issue!

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

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

Dan Wegman 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!

Doug Rickenback 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 Griffin said...

Sorry....it's updated now.

Tim Dill 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.

Dan Wegman 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!

Dan Wegman said...

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

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

Dan Wegman 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 Griffin 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."

Scott Lockhart said...

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

Doug Rickenback 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.

Doug Rickenback 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... :-)



Dan Wegman 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 Dill said...

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

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

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

Scott Lockhart 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")

Dan Wegman 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 Dill 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?

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

John Reynolds said...

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

Scott Lockhart 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 Dill 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.

Doug Rickenback 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 Griffin 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 Griffin 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.

Cindy Delfino said...

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

Jason 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 Griffin 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.

Paul Chud said...
This comment has been removed by the author.
Paul Chud said...
This comment has been removed by the author.
Paul Chud 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.

Doug Rickenback 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.

Doug Rickenback 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 ?