Free Stock Quote Tracker
All Article Categories
ETF Portfolios
ETF Provider Press Releases
ETF Thoughts
Investing
List of ETFs
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.
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.
It's FREE!
You can download the stock quote tracker.
There is no charge. But if you run a web site, please consider spreading the word by linking to this page (i.e. http://www.etftopics.com/free-stock-quote-tracker/). If there's enough demand for this tool, I'll reconsider adding all of the features people have been requesting.
Instructions are included in the Excel workbook.
Entries (RSS)
February 27th, 2007 at 7:32 am
Hi,
It seems that my conclusion on the closing of the stock market was premature: even during opening times the price for stock on the AEX (e.g PHIA.AS)are 0!
February 27th, 2007 at 8:36 am
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.
February 27th, 2007 at 9:27 am
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
February 27th, 2007 at 8:01 pm
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.
February 27th, 2007 at 8:03 pm
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.
April 18th, 2007 at 10:19 am
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
April 18th, 2007 at 10:20 am
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
April 18th, 2007 at 10:25 am
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!
May 30th, 2007 at 8:54 pm
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
May 30th, 2007 at 9:05 pm
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.
June 19th, 2007 at 11:13 pm
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.
July 11th, 2007 at 8:30 am
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
July 27th, 2007 at 4:12 pm
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?
July 27th, 2007 at 4:24 pm
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.
August 9th, 2007 at 8:39 pm
Do you have a version that works for mutual funds?
-Seann
August 10th, 2007 at 6:59 am
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.
August 13th, 2007 at 6:54 pm
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
August 13th, 2007 at 9:32 pm
Mike,
I don't use a Mac so I can pretty much guarantee that there'll never be a version for a Mac. Glad you like it otherwise.
August 20th, 2007 at 7:35 pm
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.
August 30th, 2007 at 6:33 pm
Wondering if it works with Excel 2007?
September 3rd, 2007 at 3:35 pm
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?
September 3rd, 2007 at 4:17 pm
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.
September 3rd, 2007 at 10:12 pm
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.
September 4th, 2007 at 5:55 am
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
September 4th, 2007 at 7:03 am
Devang,
I have no plans to add any additional functionality for other stock exchanges. Sorry!
September 5th, 2007 at 5:05 pm
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!
September 5th, 2007 at 5:23 pm
Flittig,
I've got the worksheet pulling from the Toronto Stock Exchange by putting .TO after the symbol e.g. XIU.TO.
September 25th, 2007 at 5:12 am
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.
October 8th, 2007 at 12:20 pm
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
October 8th, 2007 at 1:33 pm
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.
October 11th, 2007 at 9:46 pm
I have been using your Stock Tracker software fine, however Excel is now asking for a password for Stock Tracker , could you please send it ?
Thanks
October 11th, 2007 at 10:04 pm
Dave,
A password is not required unless you're attempting to access the code.
October 29th, 2007 at 9:43 am
Hi, how can I make this tracker work with an Italian Excel?
Is there a way to avoid such problem?
December 3rd, 2007 at 1:42 pm
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?
December 3rd, 2007 at 2:39 pm
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.
December 4th, 2007 at 9:40 am
ETF Guy,
Thanks for the response. Yahoo does provide a paid service for real time quotes, but I believe its Java based http://tinyurl.com/2wy7te
If I were to sign up for this service, how could I link it to pull into Excel?
December 4th, 2007 at 10:04 am
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.
December 21st, 2007 at 2:04 am
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
February 19th, 2008 at 6:15 pm
Same with me…. no quotes for PTM.LS, RELAM.LS and SON.LS….
please help me.
tks
February 28th, 2008 at 2:10 pm
can this be run with 52 week % change on mutual funds ie pgbox or any other funds
March 14th, 2008 at 4:56 pm
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?
March 29th, 2008 at 11:39 am
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
April 23rd, 2008 at 6:28 pm
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
April 23rd, 2008 at 7:54 pm
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.
May 16th, 2008 at 6:02 am
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
May 17th, 2008 at 5:28 am
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.
July 8th, 2008 at 2:43 pm
I've been unsuccessful at getting option quotes (puts and calls generally listed at yahoo as YYYY.X
Any instructions for doing so?
Thanks
July 9th, 2008 at 3:40 pm
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.
August 28th, 2008 at 11:30 am
I would be interested in adding features to this, however, you have it password protected. I could write my own from scratch, however, we could pool our resources.
September 30th, 2008 at 3:35 am
Google Spreadsheets has all financial functions to get all that data and, it can import csv files from yahoo finance (historic, and current price).
FYI