Free Stock Quotes in Excel
There are many ways to get free stock quotes into Excel, including the use of simple Excel web queries, the MSN Money Stock Quote add-in, and other commercial add-ins that not only download historical stock quotes into Excel, but provide tools for analyzing the information, creating charts, and providing oodles of technical analysis functions.
Excel Stock Quote Template
Using Excel web queries, it is possible to grab information from the internet. One of the main uses for web queries is to download free stock quotes. It is extremely simple to set up the web query, because the Excel comes with a ready-made query that you can access by going to Data > Import External Data > Import Data...
The web query gets the data from MSN MoneyCentral, but it is possible to set up queries to get data from other websites. For more information, see "Excel Web Query Secrets Revealed."
If you're like me, the reason for downloading stock quotes into Excel is so that you can create functions to analyze the data or to keep track of your stock portfolio. The following template may help you get started setting things up. However, if you are constantly changing your stock portfolio, this template may not be what you want.
Excel Stock Quotes Template
Download stock quotes into Excel using this web query template. Choose multiple ticker symbols. This query accesses free delayed stock quote data from MSN MoneyCentral, which you can view here.
The stock quotes are obtained by appending the URL with:
Though the add-in described below is no longer provided by Microsoft, the data source used by this template is still available. There is always the risk when using online sources that the provider will decide to stop sharing a particular data stream or change it in some way.
Note (9/12/2012): Microsoft has made a change to the data source recently. Excel is now automatically converting values for "% Change" from 0.11 to 11% (instead of 0.11%), so be aware that those values are off by a factor of 100.
"No Installation, No Macros - Just a simple spreadsheet"
License: Personal Use Only
File Type: .xls
Required: Microsoft Excel 2000 or later
Excel 2007+ Users: For information about how to refresh the web query, see the following article: "Refresh connected (imported) data."
MSN Money Stock Quote Add-in
Another way to work with stock quotes in Excel used to be the free MSN Money Stock Quote Add-in for Excel versions 2002 and 2003 (and 2007). But, Microsoft says that the add-in has "expired" and there won't be any more updates of the add-in. The overview on the download page was never very descriptive, but here are some screenshots and a little more about how the add-in worked:
The add-in installs a new toolbar, shown below, which has buttons for inserting new stock quotes and updating the quotes.
To shorten the toolbar (showing only the button images), go to Tools > Customize, then right-click on a toolbar button and select "Default Style".
The main feature of the add-in is the custom function, MSNStockQuote(). You can either enter the function into a cell directly, or use the Insert Stock Quotes button on the toolbar. The syntax for the function (taken from the online help file) is:
Symbol is a stock symbol (for example: "MSFT", "AOL", "IBM", "AAPL").
Property is a piece of information regarding the stock, such as Ask Price or Close Price. This is an optional parameter. If omitted, the default is Last Price. You can either type the indicated property as text, or use a value from 1 to 25 to represent the property you want to return.
This function has many advantages over the use of a web query, the main one being that you can place the function wherever you want in your spreadsheet.
An easy way to start using the function is to use the Insert Stock Quotes button on the toolbar, which brings up the following dialog box.
Using this method to insert the MSNStockQuote function also automatically creates cell containing the stock symbol, along with a Smart Tag , marked by a small purple triangle in the lower right of the cell, which brings up a menu that can take you online to view the quote, charts, news, and other company research.
Notice that one of the menu items is "Smart Tag Options...". This pulls up a dialog box and if you go to the Smart Tags tab and click on the "Check Workbook..." button, any other cells that contain stock symbols will also be identified with smart tags! The other way to get to the Smart Tags options is to go to Tools > AutoCorrect Options...
Hint: Using smart tags for stock symbols is a great way to enhance your spreadsheet!
Hint for advanced users: You can create a web query to pull the company report information into Excel. Use the smart tag to link to the company report, copy the URL, then go to Data > Import External Data > New Web Query... and paste the URL into the Address field.
Upgrading to Excel 2007: The MSN Money Stock Quote Add-in was created for Excel 2002/2003, but it also works with Excel 2007. If you have recently upgraded, you may need to re-download and re-install the add-in.
One last thing ... to update the stock quotes, you will need to use the Update Quotes button in the MSN Money Stock Quotes toolbar. (The stock quotes can only be updated every 5 minutes, but considering that they are delayed quotes anyway, it shouldn't matter.)
Historical Stock Quotes
It may be possible to create a web query to access historical stock quotes from Yahoo! Finance or some other site, but I have found this approach to be somewhat cumbersome. Perhaps a better way to do this is to download historical stock quote data into Excel from Yahoo Finance:
After you enter the symbol, choose the date range you want and then press the "Download to Spreadsheet" button at the bottom of the table. This will download the entire date range rather than just the stock quote data shown on the page.
AnalyzerXL.com sells a tool called DownloaderXL that allows you to download tabulated historical stock quotes into Excel. They also sell other add-ins related to technical analysis, portfolio tracking, options, etc.
- MSN Stock Quote Data Source at moneycentral.msn.com - To get stock quote data, you add ?SYMBOL=symbol1,symbol2 to the end of the URL.
- Discussion about the disappearance of the MSN Money Stock Quote add-in at answers.microsoft.com
Two URLs related to the MSN Money Stock Quote Add-in that don't work any more:
- Stock Quotes in a Google Spreadsheet - at support.google.com - Google got it right. You can use the =GoogleFinance function to get stock quote data into your Google Spreadsheet directly from Google Finance. View an example template by Google here.
- Get Stock Quotes using Excel Macros - at chandoo.org - Describes a way to use VBA to get stock quote data from Yahoo Finance
- Download Stock Data from Yahoo - at gummy-stuff.org - A great resource that explains how to get stock quote information from finance.yahoo.com into Excel.