Free Stock Quote Tracker
Table of Contents
The Excel Stock Quotes program is a collection of custom Excel functions and macros that allow you to insert stock data in to any cell in any worksheet. For example, if you wanted a stock quote for IBM to appear in a particular cell, you would enter this: =GetQuote(“IBM”). This function would then retrieve the latest quote for IBM. Alternatively, you can use =GetQuote(“A1”) where A1 is a reference to a cell.
NOTE: There's been a change with the source of data used by this tracker and it no longer works. Please wait for a fix before downloading.
If you want to skip all the verbiage, you can go straight to the download now (opens a new window).
Price isn't the only information you can retrieve. Here's a complete list of the functions that you can use to retrieve information:
- GetChange(symbol or cell) – Retrieves the price change for the current day.
- GetChartURL(symbol or cell) – Creates a link you can click on to load the stock's chart on bigcharts.com.
- GetHigh(symbol or cell) – Retrieves the high price for the current day.
- GetLow(symbol or cell) – Retrieves the low price for the current day.
- GetOpen(symbol or cell) – Retrieves the open price for the current day.
- GetPE(symbol or cell) – Retrieves the PE ratio.
- GetPercentChange200Day(symbol or cell) – Retrieves the percentage difference between the current price and the 200 day moving average.
- GetPercentChange50Day(symbol or cell) – Retrieves the percentage difference between the current price and the 50 day moving average.
- Get52wkHigh(symbol or cell) – Retrieves the 52 week high.
- Get52wkLow(symbol or cell) – Retrieves the 52 week low.
- GetPercentChange52wkHigh(symbol or cell) – Retrieves the percentage difference between the current price and the 52 week high.
- GetPercentChange52wkLow(symbol or cell) – Retrieves the percentage difference between the current price and the 52 week low.
- GetPrice(symbol or cell) – Retrieves the current price.
- GetTime(symbol or cell) – Retrieves the time of the last stock quote.
- GetVolume(symbol or cell) – Retrieves the trading volume for the stock for the day.
Also available is the ability to have stock quotes automatically retrieved at an interval that you specify. Alternatively, you can retrieve the quotes whenever you want at the click of a button.
System Requirements and Notes
To run the Excel Stock Quotes program successfully, make sure you have:
- Windows 2000 or Windows XP installed on your computer.
- Excel 2000 or greater installed on your computer.
- You should be familiar with how to use Excel functions and understand the concepts of relative and absolute cell values to get the most from this program.
- This is NOT an add-in. It is an Excel workbook with all the functionality built in to the workbook.
- Internet access is required to retrieve quotes.
- This workbook retrieves stock quote data from Yahoo. If Yahoo changes or discontinues the service (unlikely), this program will stop functioning.
- If you make too many quote requests in too short a period of time, Yahoo may block future requests. I can't do anything about that. In over a year's worth of use, I haven't had any problems.
- Up to a few hundred stock symbols can easily be handled by this program. More are possible, but not guaranteed, depending on your Internet connection and computer speed.
And if you really like it, please consider spreading the word by linking to this page (i.e. https://infolific.com/money-management/investing/free-stock-quote-tracker/). If there's enough demand for this tool, I'll consider adding all of the features people have been requesting.
Instructions are included in the Excel workbook.
Donations
There is no cost to use this software, but donations of any amount are certainly appreciated and will help encourage continued improvement and support of the tracker e.g. fixing bugs! Priority support will be given to those that have contributed. Thanks!
(77 votes, average: 3.61 out of 5)
Loading...
It has been a long time since this broke. I'm pretty confident now that it will never be fixed.
Yahoo was providing the data and no longer does so. I've yet to find another free source of data. If you know of one, let me know.
Sure miss this since it stopped working! Hope you can repair it soon! Tx.
Love your spreadsheet. Just started using in in March 2015. Seems the requirements at Yahoo have changed, price not being retrieved. Hope you can fix it soon.
Thanks for the good idea.
Last week (3-12-15) the function GetPrice(symbol or cell) failed on updating although other functions continue to work. Can you help with this problem?
It appears that the source of quote data has change format slightly. So the price data isn't where it used to be. The fix should be fairly easy to make, but I'm not sure when I'll some time to get to it. Stay tuned!
Thank you. I'm lost without the program now. I have 107 stocks - many in multiple accounts - so manually updating is impossible. So I'm also looking forward to the fix.
I don't use twitter or facebook so how do I download.
Also, has the =getprice() function been fixed yet? I hope so because I'm going nuts right now trying to keep up-to-date.
No fix yet.
You said this was probably an easy fix, but unfortunately, if you can't get around to fixing it a lot of us who depend greatly on this add-in are completely lost without it! Is it maybe possible for you to at least tell us how to fix it ourselves?
And if someone does come up with a fix, could you kindly let us know?
I'm guessing it is an easy fix, but I haven't had the time to look into it. Sorry, but I've got to prioritize my time to focus on things that pay my bills.
For someone else to fix the problem would require that I release the source code which I'm not interested in doing.
Thank you Marios. Wasn't criticizing or looking for the source code, but rather hoped the solution might be as simple as a name change; ie substitute getprice() with getlastprice() for example.
Anyway, if when you do find the time to look into it hopefully there might be a simple workaroud like I've mentioned above, so perhaps just advising us here would be an alternative to rewriting the program.
How do you remove atpvbaen.xls, Internet_Assistant, and any other part of Stock Quote Traker, if you do not wish to use Stock Quote Traker?
I don't know what those parts your mentioned are. The Stock Quote Tracker just uses macros which go along with the spreadsheet, but otherwise don't attach themselves to Excel. So once you close Excel and open another file, the macros will be gone.
Great tool! I use it all the time.
This is an excellent program but I have not been able to find a suitable list of index symbols. Can you suggest a list of index symbol that will work in the Free Stock Quote Tracker spreadsheet?
Looks like a pretty useful tool. On the one hand I understand that you don't want to give out the code. On the other hand, since we don't know you, how can we be sure you do not transfer any data from our PC to yours? It seems you have no intentions of making money with your code which is great. But then, why not make the code available to everybody so that others can possibly even enhance it or its functionality? Well, the idea of open source... Whoever wants gives a little (time and effort) and as a result everybody gets a lot, all transparently - secure and free for everybody.
Hi,
Thank you very much for the stock tracker. May i know why the stock tracker is not able to run in one of my pc?
May you consider to add in the ''previous close price'' for stock in your next revision as this would help us to understand the price change from ''previous close price''.
Hi ETF Guy,
I work in a corporation and they have put up a firewall and hence I keep getting an error " error retreiving stock quotes".
Hence i was wondering if you could help with unlocking the macros so that I can edit the firewall details and allow the file to pull up the data.
Else let me know the server / site where the data is pulled from, so i could try talking to our server team to allow access to that on the firewall. (but thats a lost case anyways)
Regards
Andy Bhai
Great tool.
Hi
This Excel File is amazing. I wanted to known if there is way to change the feed to point to BSE (Bombay Stock Exchange) to get share prices of they market. To be honest I'm in India and invest on the local shares thus wanted your help on customization
Regards,
I'm trying to write a macro to do an EPS and I need to password to add a new sheet, can I get it or can you add EPS from Yahoo?
Thanks
The code is a bunch of VBA macros. As a user, you can't change the URL.
Hey, did you write the formulas or is this in excel 2007? Also is there a way to change the web address they update from yahoo to cnbc? Cnbc gives real time quotes. Thanks!
I find your macro very handful but, since i live in Italy and have italian settings on my excel, prices come with 2 more zeros becouse we use comma for the decimal ald poit for thousands.
Is there a vay to display prices in the right way?
Just for example, price for MSFT now shows 1696.00 instead of 16,96
Thanks in advance.
Stefano
You can get quotes from the TSX by adding .TO to the end of your symbols. For example, I track an iUnit and to do so I use "XIU.TO"
Your tracker collects and calculates exactly the type of info I want to have. It's a very nice piece of work. Unfortunately, I'm in Canada and my investments are all on the TSX. Would you mind helping me plug into the TSX instead of the NYSE?
I'm quite at ease with excel so I think I would just need access to your macros.
Thanks.
Sam,
Unfortunately there is no trick in this case. If you're able to retrieve data for one symbol, but not another and nothing else is different, then that other symbol just isn't supported by the request that the tool makes to Yahoo's servers.
I've been unsuccessful at getting option quotes (puts and calls generally listed at yahoo as YYYY.X
Any instructions for doing so?
Thanks
Ed,
There is no built-in limit to the number of quotes you can retrieve. However, the data comes from Yahoo so if you ask for too much too often they may refuse the request. I retrieve 104 in my copy of the tool. Just make sure the symbols are above the eos cell in the symbols tab. If you need more symbols, insert rows above eos.
As for getting quotes from the TSX venture exchange, I'm not sure how to do that. If you can get a stock quote from finance.yahoo.com then use that same symbol in stock tracker. For example, TSX listings seem to have this format: symbol.TO.
Finally, the error you're getting could be because of a typo in the formula name or that you just haven't refreshed the data after entering the formula. Also, if you're using a symbol within the formula and not a cell reference, be sure the symbol has double quotes around it.
just testing out quote tracker and with the original sybols it works good. When I add my own symbols and try to up date it I get an error message "Error Retrieving Quotes"! How many symbols can be added to the tracker? Can I get data for tsx venture exchange? If not how can I do this? I also looked at the formulas for the information the program already extracts and tried to get the asking price and bid price but excel only returned the NAME#? Error, what is the formula for this information?
Thanks Ed
Paul,
I can confirm that there is a problem as I don't get quotes for those symbols either. However, I'm sure what the issue is. Sorry about that. Hopefully I'll have a chance to dig in soon.
Hi, Its me aqain. Today is April 23, 2008. I'm having problems with getting some quotes that I was receiving before. BA and AIG. The name of the company does not come back when requested. The information that does comes back is the symbol and not the name, so you do not get a quote for the stock. Any help would be greatly appreciated.
Paul G
Any chance in fixing the last sale or closeing price for the DJI? It use to work and has stopped sometime in the last month or so.
Thanks
Lately I am having trouble getting the last sales on DJI and DJC. I'm getting the open, high, low but the last sale (close) is not coming in for these to items. Any suggestions?
can this be run with 52 week % change on mutual funds ie pgbox or any other funds
Hi, Yahoo have updated symbol list and now it is having more characters than Six, perticularly for Indian Stock market. I am getting stock quotes which is less than six characters but for more than that I am not getting stock quotes. e.g. For reliance.ns I am not getting any quote, but for rcom.ns I got the quote.
Please letme know how to overcome such difficulties.
Haso
Thanks for the response. Yahoo does provide a paid service for real time quotes, but I believe its Java based http://billing.finance.yahoo.com/realtime_quotes/signup. If I were to sign up for this service, how could I link it to pull into Excel?
Kevin,
You can't link the Excel tool to another feed. It's something I would need to do because I'm not making the underlying programming code available to the public. However, I'm not really putting time into this tool any more as it does everything I need it to do and I don't charge anything for it.
Kevin,
Linking the sheet to real-time quotes would involve finding a source of real-time quote data. Once the data has been pulled in to Excel, the rest of the application would function the same way. Yahoo used to provide free real-time quotes, but I don't know if they still do. I set up the Excel application to use the real-time quotes if available and then to fall back to delayed quotes otherwise.
ETF Guy,
Any idea on how to link this with real time quotes? I've found plenty of places to get real time quotes, but none that feed into excel. Also I'm thinking about paying someone to write a macro which would log into my brokerage account and trade for me based on the data from excel. Think conditional orders or trade triggers without limitations. Any ideas on a solution?
Jonathan,
You could reproduce the functionality in Access, but it wouldn't be an easy project especially without some previous experience with Access. One advantage with Access is that you would have the ability to store historical data. Not something I need, but I could see others wanting such a feature.
Love the worksheet - I have been using it for some time - works great
I tried to use it in an Access Database, but failed - can this be done by a person just learing Acess - or I'm I biting off more than I can chew.
Thanks
JBC
ETF Guy,
Great tool, it's a life saver.
Is there any you could add the previous close data (Yahoo includes it in their basic stock quote data)? If so, I would be in heaven.
Flittig,
I've got the worksheet pulling from the Toronto Stock Exchange by putting .TO after the symbol e.g. XIU.TO.
Hi there,
Great tool. I was wondering how the worksheet pulls from yahoo. Does it only pull those from us exchanges? There are some international symbols (ie. Canada) that are not pulling. I tried entering the format as it is shown on yahoo, but nothing comes up.
ie. Peer 1 Networks
Yahoo symbol - PIX.V
Full information on Yahoo - CDNX:PIX.V
If your worksheet does pick these up, what format should these be entered?
Thanks!
Devang,
I have no plans to add any additional functionality for other stock exchanges. Sorry!
Hi,
The stock tracker looks gr8!
Do you have a similar tool to track stocks of the Indian Stcok Exchanges?
If not, can you customize ur tool for the same?
Regards,
Devang
Actually, I realized you can choose to create the macro in a personal macro workbook rather than the current workbook and that works out fine, so I don't need the password. Although it would be nice if you could somehow separate what you wrote from the rest of the macros.
Chris,
I'm not giving the password out. The only way I can think of for you to add additional functionality is to create an Excel add-in.
I am using your template--works great, but I wanted to add my own macro and it asks for a password. Is there a way to turn this off or may I have the password so I can create my own macros?
any way to get revenues and other pertinent info? p/e i'm assuming can be manually calculated if we can get a earnings figure.. also, i think the quick ratios are all freely accessible on yahoo.
awesome work by the way.
I have tried your workbook on a PC running Windows XP and it seems to do a great job. Is there any chance of your template running on a Mac v 10.4.10 with Excel v.X. When I try to load the template I get a compiler error.
Thanks,
Mike
Seann,
The stock tracker works with anything that has a stock symbol registered with the major US stock exchanges. Mutual funds usually (always?) have a symbol you can look up.
Do you have a version that works for mutual funds?
-Seann
Rich,
Sorry, but only the data that Yahoo makes available can be displayed in the Excel workbook. The items you listed just aren't part of what Yahoo included when I worked on the stock tracker.
I'd really like to see more basic info about companies, such as trailing Return on Assets/Return on Equity, Book Value, Debt, Cash on Hand, Debt/Equity Ratio, Trailing P/E, Forward P/E.
How can I add these statistics?
Wow, great program!!! I really appreciate you taking the time to write it. I knew it was possible to do this kind of thing, but, I didnt have the programming expertise to do it.
My wife and I track our retirement portfolio in Excel and update it each quarter by manually entering the updated prices. Your program will really help us a lot!
Thank you very much. Your hard work is greatly appreciated.
John A
Hi there,
Nice nifty program, however there's a few problems I encountered.
I can't get the P/E to work.
How do I get the EPS on a column
If Yahoo is not working, can I get quotes from MSN. If so, how is it done.
Thanks.
Al.
Dave,
I've often thought about storing historical data which would cover that scenario you described. However, doing so would take some time to write and test the necessary code. Since the program is free and currently does everything I want it to do, I don't think I'll get to adding such a feature anytime soon. I hope you like it otherwise.
Dear Sir;
Is there a way to put yesterday's close in the Stock Template ? and also the close 2 day's ago ?
Thank's
Sincerely
Dave
Cool,
The stock tracker was built with US-based stocks and exchanges in mind. I didn't test it with international exchanges. Doing so would take a lot of time. Sorry!
Hello
Thanks for your excel utility.
When I have put the Indian symbols (say 50) in the symbols list only 30 symbols gets updated and rest notifies "0". The symbols are correct and verified. Can you please point out why are all not getting updated. Also can Bid and Ask prices be retrieved too?
Thanks
Hello
Thanks for your excel utility.
When I have put the Indian symbols (say50) in the symbols list only 30 symbols gets updated and rest notifies "0". The symbols are correct and verified. Can you pls point out why are all not getting updated.
Thanks
Andy,
It's possible that the default Yahoo site could be changed, but that's not a feature I'm going to pursue. Sorry, but I've got to pick and choose where I spend my time because I've got too much going on.
Thanks for your input.
Gerard,
I found the problem with PHIA.AS. It seems that Yahoo is returning a Euro symbol in the price and my code didn't account for it being there. It's something I should be able to fix. Hopefully no later than the weekend.
Hi,
Can the Default Yahoo website be changed for people living in India. As I beleive, by default, the system checks only on the US website.
Appreciate if you could help me with the details or work out for it to look up from Yahoo India fiance section.
Thanx
Andy
Gerard,
Now that the market is open I can see what the problem is. Yahoo is sending back the Euro symbol in the form of an image. This is confusing my code since it is expecting to see a number. The result is a 0 for the price. I've got an idea how to fix it though so I'll hopefully get some time later this week to address.
Gerard,
I fixed the ticker length issue. PHIA.AS does seem to be working although I'm getting a price of 0 at the moment (after hours). I hoping it'll work tomorrow when the markets open.
Lowercase tickers now work too.
As for returning the last quote instead of 0, that's going to take more time than I have at the moment. Sorry!
Found two problems:
1. Length of ticker is maximised. E.g Philips Electronics on the Amsterdam market is named PHIA.AS but is NOT accepted.
2. Ticker names have to be in uppercase. Lowercase is not accepted.
Nice program, Good work.
Joe,
I don't have a function for getting historical information. If you know a site that provides such information for free, let me know and I may be able to add it.
I tried the 4 symbols you listed and they work fine for me. Did you list them on the All Symbols worksheet above the cell with eos in it?
Darren,
Appreciate you providing your wonderful service for free. Re your question about a site providing historical info for free, I found it right on the Yahoo website, labelled "historical information".
Here is the URL for Citigroup: http://ca.finance.yahoo.com/q/hp?s=C
It seems stock symbols are not recognized, e.g., MMM, AVCI, A, INPC.
Do you have a function for getting historical information, e.g. pass a date and get the stock price.
Bob,
1) It looks like Yahoo, the source of the quote data, has moved to a pay-for-service model. There may be a way for me to still pull PE data for free, but I'll have to find some time to look in to it.
2) You found a legitimate bug. I won't bore you with the details, but I have corrected it and posted a new version to this site.
There is no easy way to apply the fix to your version. You'll have to copy your data over to this new version. Sorry for the inconvenience.
Take care.
Love your program or template.
Questions:
1) It does not retrieve PE Ratio
2) It does not retrieve stock sym C for Citigroup Inc
Any suggestions?
Thanks Bob