The Stock Connector add-in lets you link US stocks, market indexes, international stocks, currencies, crypto, or ETFs to cells in the spreadsheet and updates them automatically. You can get the add-in free from the Excel Stock Connector AppSource 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 365, Excel 2013, Excel 2016, and Excel 2019 for Mac and Windows, 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 10 seconds. Real-time data for US stocks is available for contributors. 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 IEX exchange are supported by this add-in, including US stocks, market indexes, mutual funds, international stocks (updated daily), OTC stocks (updated daily), and ETFs.
The user base is growing (around 290,000 monthly user sessions as of November 2020), but I still only get 60 to 80 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 firstname.lastname@example.org.
One easy way to get started is to simply download this template Excel file. When you open the workbook you'll need to click through a few prompts to trust the add-in. Then the task pane will appear with several stocks and currencies already connected. In this sample, the stocks are connected to the second sheet, and they're referenced in a portfolio view on the first sheet, which uses Excel's =VLOOKUP() function to refer to the live connected prices.
If you don't know the ticker symbol for the company you want to track, enter the company name in the white text box at the top of Stock Connector and click Connect. If the name is recognized, you'll see a list of up to 10 symbols with the full company name and country code. This feature is especially useful for finding the symbols for international stocks.
Stock connector supports several market indexes, which have symbols beginning with the "^" character. Here are the supported indexes today:
|S&P 500 Index||^GSPC||USD||realtime|
|Dow Jones Industrial Average Index||^DJI||USD||realtime|
|NASDAQ Composite Index||^IXIC||USD||realtime|
|Russell 2000 Index||^RUT||USD||realtime|
|FTSE 100 Index||^FTSE||GBP||daily|
Stock Connector supports stocks in 14 non-US exchanges across 13 countries (and 8 US exchanges). Non-US stocks update only at the end of each trading day, not in real time throughout the day. Here are the supported regions:
|Toronto Stock Exchange||TSE||Canada||-CT|
|TSX Venture Exchange||TSX||Canada||-CV|
|Mexican Stock Exchange||MEX||Mexico||-MM|
|London Stock Exchange||LON||United Kingdom||-LN|
|Abu Dhabi Securities Exchange||ADS||United Arab Emirates||-DH|
|Tel Aviv Stock Exchange||TAE||Israel||-IT|
|Korea Exchange||KRX||South Korea||-KP|
In order to add the stock you want, take the symbol and append the suffix above. For example, to add Volkswagen on the German exchange, type VOW-GY. You can also use the Search function in Stock Connector to find international stocks.
First, find the currency code of the 2 currencies you want to compare. In Stock Connector, enter both currency codes together, separated by a slash (/) symbol. The second symbol should be the base currency - the one you want to use to express the value of the first symbol. For example:
First, find the currency code of cryptocurrency you're interested in, for example, BTC for Bitcoin. Then find the currency code of your base currency, for example, USD for American dollars. In Stock Connector, enter these two currency codes together, separated by a slash (/) symbol. For example:
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 enter stocks in a sheet directly with Stock Connector and then sort the sheet, the connections may break.
To solve this problem, create a new sheet called "stockconnector" whose only purpose is to hold a list of tickers in column A, with their price and change% in columns B and C. You will not sort this sheet. On the sheet you want to sort (for example, your portfolio sheet), let's say you want the price of Google's stock. Then you can use the formula "=VLOOKUP("GOOG",stockconnector!A$1:C$1000,2,FALSE). Then when you sort the portfolio sheet, all the connections continue to work. Another advantage of this approach is that you can include the same stock multiple times (for example, maybe you hold a stock in multiple accounts). If you want to make it even easier, you can reference a cell on your portfolio shee with the ticker instead of typing the stock, like "GOOG".
You can see an example of this setup working in this sample portfolio spreadsheet using Stock Connector.
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 premium users can refresh all 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.
The number of securities per workbook was not limited with Yahoo's data source (now discontinued). The new data source supports a maximum of 100 stocks per workbook. However, as of 12/29/2017 I have implemented a fix that allows an unlimited number of stocks with the new data source.
There is no way to automatically connect large numbers of stocks all at once using Stock Connector. They must all be entered individually. However, I'm working on a new add-in which will enable this type of functionality in the future.
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 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 IEX. In order to retrieve the stock quotes, the add-in sends IEX the list of stock tickers for each refresh.
The cloud services provider is Microsoft Azure. All assets and functionality are served by Microsoft Azure.
This policy is updated regularly whenever new service partners/vendors are added to Stock Connector.
Stock Connector is provided with all its core features for free. I fund Stock Connector via voluntary contributions, which can be made through PayPal from the Stock Connector task pane. My main monthly cost apart from my time is the data source itself, as well as the cloud services to run the app. I do the development work including bug-fixes myself, part-time.
Once a user contributes through the add-in, the add-in stores their Office account identifier in a database. From then on, those users get access to premium features. Those users also get a promise to never see ads in Stock Connector, now and in the future. These premium 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 with the account you used when you made the payment. The PayPal account you use does not matter: the only important account for Stock Connector is the one you use to sign in to Office.
Monthly $3 subscriptions through PayPal are currently in the process of being rolled out for users who have not contributed. If you're using a monthly subscription, Stock Connector checks to see whether the payment is on file for your account in the last 31 days. To end your subscription, sign in to www.paypal.com, click one of the transactions for this add-in, then click Cancel. This action will prevent future payments and also cancel your premium features within 31 days - no further action is required.
Here is the current list of premium features. All of these are awarded to users who contribute (either via one-time contribution or via monthly fee):
Here's how to contribute and get the premium features:
I will continue to maintain and improve Stock Connector as long as I'm able. Changes in financial data providers have the potential to prevent Stock Connector from working in the future, so this service may not be available indefinitely, but in the past I've been able to find replacement sources when that happened.
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. Note that as of July 2019, I must pay the data provider based on the amount of data that the app receives, so please contribute if you're able.
The data is provided by IEX Cloud. Please do not abuse the data source by attempting to request large amounts of data with very high frequency. The data retrieval costs money, so I may disable it for users who use it in ways that aren't intended (eg. triggering programmatic refreshes every second with additional software).
You can view the latest updates and bug info at the Updates and Bugs Page.
I'm a Canadian who likes to make stuff. I work at Google as a Product Manager, and previously worked at Microsoft on Excel's developer platform. https://www.linkedin.com/in/saundersmichael/