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

Excel Stock Quotes Template
Screenshot

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:
http://moneycentral.msn.com/investor/external/excel/quotes.asp

The stock quotes are obtained by appending the URL with:
?SYMBOL=symbol1,symbol2,symbol3

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"

Download

Cost: Free ($0.00)

License: Personal Use Only

File Type: .xls
Size: ~40 KB
Required: Microsoft Excel® 2000, 2002(XP), 2003, or 2007
Disclaimer: This Excel stock quote template is meant for educational purposes only. We believe the spreadsheet to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions.

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.

MSN Money Stock 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".

MSN Money Stock Quotes Toolbar

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:

MSNStockQuote(Symbol,Property,CountryCode)

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.

Stock Quotes Dialog Box

Using this method to insert the MSNStockQuote function also automatically creates cell containing the stock symbol, along with a Smart Tag icon, 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.

Smart Tag for Stock Quote

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:

Enter Stock Symbol:

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.

Advertisement

References

Two URLs related to the MSN Money Stock Quote Add-in that don't work any more:
http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1
http://office.microsoft.com/en-us/assistance/HA010346101033.aspx

More Options

 

Like This Template?

Become a Fan

Vertex42's Facebook Page Vertex42's Google+ Page Vertex42's Pinterest Page
Master Excel - Spreadsheet Tips Workbook