The Stock Connector add-in lets you link stocks to cells in the spreadsheet and updates them in real time. You can get the add-in free from the Excel Stock Connector Office Store page. Or download this template Excel file that already has the add-in set up with some sample stocks.
The add-in works in Microsoft Excel 2013, Excel 2016 for Mac and PC, Excel Online, and Excel for iPad. It allows you to choose the location where it inserts the price or the daily change. Then those values are updated automatically throughout the trading day at an interval you specify, as quickly as once every 5 seconds. The add-in is intended for creating a variety of portfolio-tracking spreadsheets or more complex stock-management tools in Excel. Any holdings supported by the Yahoo Finance Quotes API are supported by this add-in, including stocks, bonds, FOREX currency exchange rates, market indexes, ETFs, and mutual funds.
The user base is growing (around 40,000 monthly user sessions as of May 2017), but I still only get 20 to 30 requests/bugs/feedback messages per week, so each time you contact me it helps me improve the add-in. So go ahead and tell me about any problems or suggestions from the Stock Connector Uservoice page.
On this page:
If you invest on exchanges outside the United States, the tickers you use for your stocks may not be recognized by Yahoo's Finance API. To find the correct international ticker, search with the Quote Lookup feature on Yahoo Finance.
For example, Canadians may own the iShares Core S&P 500 ETF on the Toronto Stock Exchange, with the ticker XUS. Searching on Yahoo Finance shows the international ticker XUS.TO, which is supported by the Stock Connector add-in.
I don't know the complete list of supported markets and exchanges, but I've had customers ask about accessing the following international ones:
If you hold cash or securities outside in multiple countries, it's likely you'll want to convert between currencies at the up-to-date rates. With the Stock Connector add-in, currencies are treated exactly like stocks. Just enter the currency exchange ticker and choose a cell to connect to the rate.
To find the correct currency exchange ticker, first find the 3-letter currency code for the two currencies you want to convert between. For example, use USD for the American dollar and GBP for the British pound. Then decide which of the two currencies you want to convert to, and place that one last. For example, to convert British pounds (GBP) to American dollars (USD), use the ticker GBPUSD. To convert American dollars (USD) to Euros (EUR), use the ticker USDEUR.
Because currency markets do not operate on a regular daily schedule, it's only possible to get the current exchange rate, not the difference compared to the previous day.
It's important to track how the broader markets are doing, to compare with your portfolio. Market indexes like the S&P 500 are a great way to do that. With the Stock Connector add-in, indexes are treated exactly like stocks. Just enter the index ticker and choose a cell to connect to the rate.
Index tickers on Yahoo's service usually begin with the caret character ^. To find an index ticker, search with the Quote Lookup feature on Yahoo Finance. Here are some common index tickers:
Unfortunately, Yahoo's Finance API blocks the Dow Jones Industrial Average, which is one of the most commonly-cited American indexes. The best alternative to track that one is DIA, which is a popular ETF that tracks the Dow Jones with 99.9% accuracy.
Often you'll want a quote to appear in multiple places on a spreadsheet. Since the Stock Connector add-in only lets you insert each quote in one place, you can use the power of Excel to show it in more places. Let's say you want a price to appear in cell A1 and B1. First connect the price to cell A! as usual. Then type in =A1 in cell B1. Now both cells will be updated automatically as the price changes. If you want to get fancy, you could even create a separate sheet specifically to hold connected stocks, and use a forumla like VLOOKUP to automatically get quotes for any stock on the main sheets.
If you manage a lot of stocks in the same spreadsheet, you might forget which cell you've connected for a particular stock. The solution is simple: just click the price in the task pane and the cell you're looking for will be selected.
By default, quotes are updated automatically every 15 seconds.
You can change the update frequency by clicking the two small arrows to increase or decrease the amount of time between updates.
The minimum interval for automatic refreshing is 5 seconds in Excel 2013 and Excel for iPad, 30 seconds in Excel Online.
At any time you can refresh all your stocks immediately by clicking the word "Refresh".
You can prevent stock prices from updating automatically by clicking the upward-pointing small arrow until the add-in displays a ∞ symbol. Then click Refresh whenever you want to update manually.
Excel 2010 does not support Stock Connector or any other web-based add-ins. If you're using Stock Connector with a workbook and then open that workbook in Excel 2010, you will still be able to use all the normal Excel features, but the stock prices will not update. Once you reopen the workbook in a supported (more-recent) version of Excel, the stock prices will start updating properly again.
In rare cases you might find that Stock Connector seems to be "corrupted". For example, it might prevent you from adding valid stocks or loading any prices for your existing stocks. The exact causes of this bug are unknown, but events like the removal of a stock from an exchange (eg. acquisition) could be one cause.
An effective way to fix these problems is to clean the workbook with the Document Inspector. This action will not affect the data in the cells (including tickers, prices, and other info). But it will remove all other traces of Stock Connector from the workbook (specifically, the task pane and the live "links" to your cells). So in order to reconnect the workbook afterwards, you'll need to manually connect all the tickers with their cells using the task pane. Workbook-cleaning is only available on the Windows versions of Excel. Here are the steps:
If you have suggestions, requests for features you'd find useful, comments, bug reports, or you need help with something that isn't explained well above, please let me know at the Stock Connector Uservoice page or send a request via email at email@example.com.
The add-in never collects, stores, or transmits information that can be used to identify you, except for PayPal data: If you use PayPal to contribute, then PayPal provides Stock Connector with the email address and name from your PayPal account.
The add-in does collect anonymous information about how the add-in is used, such as how often it's launched and which buttons get clicked. These logs are collected through the Google Analytics web service. To distinguish users from each other, the add-in generates a random number for each user, which is stored on your computer and can be removed by clearing your cookies. The information collected is used only to improve the quality of the add-in and is not sent or sold to any third party.
The data provider is Yahoo Finance. In order to retrieve the stock quotes, the add-in sends Yahoo the list of stock tickers for each refresh.
Stock Connector is provided with all its essential features for free.
I fund Stock Connector via $5 voluntary contributions, which can be made through PayPal from the Stock Connector task pane.
I do the development work including bug-fixes myself in my free time, around 4 hours per week.
Once a user contributes, the add-in stores their Office account identifier in a database. From then on, those users get access to bonus features. Those users also get a promise to never see adds in Stock Connector, now and in the future. These bonus features work no matter what computer, document, or operating system you use for Stock Connector. The only requirement is that you sign in to Office.
Here is the current list of bonus features. All of these are awarded to users who contribute:
Here's how to contribute and get the bonus features:
I will continue to maintain and improve Stock Connector as long as the Yahoo Finance service remains viable. Yahoo's recent corporate sale may impact the service in the future if they decide to shut it down. I have no reason to believe that will happen, but it's important to disclose that possibility.
As always, you can of course use the add-in completely free. And I promise to never make you pay for access to features you're already using.