Free Stock Quotes in Excel
NOTICE 1/8/2015: The Excel stock quote template below has been updated to use the following MSN stock quote data source: http://www.msn.com/en-us/money/quoteslookup?symbol=MSFT
There are many ways to get stock quotes into Excel. There are commercial add-ins that can download historical stock quotes into Excel and provide tools for analyzing the information, creating charts, and providing oodles of technical analysis functions. This page describes using Excel web queries to get simple refreshable stock quotes in Excel.
Excel web queries allow you to import information from a web page into Excel, including free stock quotes. In the past, Microsoft provided a free MSN Money Stock Quote add-in for importing financial data, but they stopped supporting the add-in. However, quote data can still be obtained via a web query using the following data source, appending the URL with ?symbol=MSFT,GOOG
For more information about creating web queries, see "Excel Web Query Secrets Revealed."
Excel Stock Quote Template
Downloading stock quotes into Excel can be a convenient way to maintain a watch list, do your own analysis, or keep track of your overall stock portfolio. This template provides a web query set up to access the above MSN data source.
You can add a limited number of stock symbols by editing the list where shown, separating each symbol with a comma. You can include index symbols such as $INDU and $INX.
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. 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.
Excel 2007+ Users: For information about how to refresh the web query, see the following article: "Refresh connected (imported) data."
Stock Quotes in Excel via Google Sheets
The GOOGLEFINANCE function in Google Sheets allows you to get delayed quotes for stocks and mutual funds. After creating a table for the symbols that you want, you can publish the sheet as a web page (via File > Publish to the Web). You can then import the data into Excel via a web query that uses the Google Sheet as the data source. To do this, follow the steps below.
Step 1: Start with a Google Sheets Template
We've created a Google Sheets template that uses the GOOGLEFINANCE function to display stock quotes. You can make a copy of that template and update it with your own list of stocks and mutual funds.GET TEMPLATE
Step 2: Add Stocks and Mutual Funds
To add a new stock symbol to the Google Sheet, type the symbol in column A, then copy the formulas from previous rows.
Step 3: Publish as a Web Page
To connect your Google Sheet document with Excel, first publish your Google Sheet document to the web. In Google Sheets, go to File > Publish to the Web.
From the dropdown box, select the StockQuotes worksheet. Then click the "Publish" button and copy the URL that is shown highlighted in the screenshot below.
This will make the worksheet viewable to anybody who ends up with the link to it. For security, you should only use the Google Sheet to list the stock quotes. Don't add other personal financial data to the published sheet.
Step 4: Import into Excel via a Web Query
Now you can use the URL of the published Google Sheet to import the data into Excel. Create a new Excel worksheet, then go to Data > Get External Data > From Web.
Paste the URL you copied from Google Sheets into the Address bar, then finish the wizard.
This will put your Google Sheet data directly into Excel:
To update the web query, go to Data > Refresh All.
With the data now in Excel, you can use VLOOKUP or INDEX/MATCH functions to reference the data in the web query. For example use a formula like =INDEX(D:D,MATCH("MSFT",B:B,0)) to get the Price for MSFT.
MSN Money Stock Quote Add-in
One 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 add-in used a data source that appears to be inactive now.
- New MSN Stock Quotes Lookup Source at http://www.msn.com/en-us/money/quoteslookup - To get stock quote data, you add ?SYMBOL=symbol1,symbol2 to the end of the URL.
- Inactive MSN Stock Quote Data Source: http://moneycentral.msn.com/investor/external/excel/quotes.asp
- Discussion about the disappearance of the MSN Money Stock Quote add-in at answers.microsoft.com
Stock Quotes via Yahoo Finance
There are places on the internet where you may find instructions on how to get stock quotes for multiple stock symbols from Yahoo Finance via a CSV file using a URL like the following:
You may find this used in VBA code, used in web queries, and even recommended for use in Excel 2013 via the new WEBSERVICE() function. However it is unclear to me whether Yahoo approves of this approach for personal use [ref].
This may return values as text, but you can use =VALUE(...) to convert the result into a numeric value.
Historical Stock Quotes via Yahoo Finance
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.
- 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