I encourage everyone lucky enough to own stocks to contribute to people who need it. I donate 10% of the profits from Stock Connector to givedirectly.org, which distributes the money mostly in Kenya, Rwanda, and the United States.

Stock Connector add-in for Excel

The Stock Connector add-in lets you link US stocks, market indexes, mutual funds, international stocks, preferred shares, currencies, crypto, precious metals futures, 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 2021, 2019, 2016, 2013, and the versions for Windows, Mac, 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. The add-in supports US stocks, market indexes, international stocks (updated daily), preferred shares, OTC stocks (updated daily), gold, silver, ETFs, cryptocurrencies, international currencies, and mutual funds.

Tell me about any problems or suggestions from the stockconnectorapp@outlook.com. If you noticed a new bug, always start by checking the status page making sure you have the latest code updates, since I might have already fixed it.

How To

Insert Stock Connector in a blank workbook

  1. Open a new Excel workbook and go to Insert > Get Add-ins
  2. Search for "stock connector" and click Add.
  3. Stock connector is immediately installed. Click the Stock Connector button in the ribbon on the Home tab to insert the task pane.

Start from an Excel template

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.

Connect your First Stock

  1. Enter the desired ticker in the input box (eg. "MSFT" for Microsoft). If you don't know the ticker for the stock you want, you can search on the IEX exchange.
  2. Choose Connect.
  3. A prompt will appear asking you to select any cell. Choose a cell in the Excel workbook where you want the live updating price to appear.
  4. The add-in writes the price in the selected cell and also gives more information in the task pane, including the ticker, the last trade price, the percent change since the previous close, and the time of the last reported trade. This information is displayed for the time zone of the stock's exchange and is delayed by default or available without delay (real-time) as a premium feature for contributors.
  5. That's it! To insert the daily change % into the spreadsheet as well, select the change value for that stock in the task pane. Then the add-in will prompt for a location in the same way that it did to add the stock initially.
  6. To insert the dollar value change (per-share) in an Excel cell, you can use an Excel formula with the other two values. You'll need to connect the [Price] (eg. 47 for $47.00) and the [PercentChange] (eg. 0.01 for +1%) connected first. Then use the following formula: = [Price] - ([Price] / (1 + [PercentChange]))
  7. Clear the contents of a cell to unlink it from the add-in.

Connect to market indexes

Stock connector supports several market indexes, which have symbols beginning with the "^" character. Here are the supported indexes today:

NameSymbolCurrencyUpdate interval
S&P 500 Index^GSPCUSD realtime
Dow Jones Industrial Average Index ^DJIUSD realtime
NASDAQ Composite Index ^IXICUSDrealtime
Russell 2000 Index ^RUTUSD realtime
FTSE 100 Index ^FTSEGBPdaily

Connect to precious metals futures prices (commodities)

Stock connector supports precious metals, which have symbols beginning with the "^" character. Here are the supported ones today:

NameSymbolCurrencyUpdate interval
Gold futures^GC=FUSD realtime, US market hours
Silver futures ^SI=FUSDrealtime, US market hours

Connect to international stocks

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:

CountryNameCurrencySymbol suffixExample
BelgiumEuronext BrusselsEUR -BEKBCA-BE
BrazilSao Paolo Stock Exchange BRL-BR MGLU-BR
CanadaToronto Stock ExchangeCAD -CA TD-CA
ChinaHong Kong Stock ExchangeHKD -HK5-HK
FranceEuronext Paris EUR -FRMC-FR
GermanyXETRA EUR-DE VOW-DE
IndiaNational Stock Exchange of IndiaIST -INTATAMOTORS-IN
Israel Tel Aviv Stock ExchangeILS-ILTEVA-IL
JapanTokyo Stock ExchangeJPY .T-JP7203.T-JP
NetherlandsEuronext Amsterdam EUR-NLUNA-NL
TaiwanTaiwan Stock ExchangeTWD -TW2330-TW
United KingdomLondon Stock Exchange GBP -GBLLOY-GB

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.

Connect preferred shares

For preferred shares that contain a single-character suffix and a separator, use the hyphen as a separator. For example, for the Bank of America class B preferred shares, use BAC-B.

Connect to FOREX currency exchange rates

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:

Connect to cryptocurrencies like Bitcoin

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:

Connect a stock to more than one cell

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.

Sort spreadsheets without breaking connections

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.

Find a cell you already connected a stock to

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.

Change the refresh interval

By default, quotes are updated automatically every 15 seconds. This fast updating is available for premium users and anyone with fewer than 5 stocks. 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 10 seconds in Excel 2013 and Excel for iPad, 30 seconds in Excel Online. If you don't interact with Excel or Stock Connector for a while, Stock Connector will gradually slow the updates to conserve data. Once you click inside Stock Connector again or click on a cell in Excel, the updates will resume again at their normal pace.

Refresh immediately

At any time premium users can refresh all stocks immediately by clicking the word "Refresh".

Turn off automatic refreshing

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.

Connect to a large number of stocks

Stock Connector supports a maximum of 50 stocks per workbook for the free version, and 1000 for contributors.

There is no way to automatically connect large numbers of stocks all at once using Stock Connector. They must all be entered individually.

Clean a workbook to get out of a bad or corrupted state with Stock Connector

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.

Before you follow these steps, please check to make sure you have the latest updates to Stock Connector

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:

  1. Make a copy of the document as a backup (recommended)
  2. File > Info > Check for Issues > Inspect Document
  3. Select only Task Pane Add-ins and choose Inspect
  4. Remove All
  5. Once you're done, you can insert Stock Connector again and start reconnecting prices.

Download the latest code updates for Stock Connector

If there's a bug caused by one of my service providers I may need to release new code updates to fix the problem. You'll get the updates automatically after around 24 hours, but these instructions explain how to get them immediately:

To determine your current version of Stock Connector, look for the small grey version number in the top right corner, like this:

Compare this with the latest version number listed on the Updates and Bugs Page. If you have the latest version listed or a higher number, you're already done.

If you have an out-of-date version, try the following steps to update (after you follow any of these steps, all your previously-added stocks will still be connected). The first few steps might fix the problem or you may need to keep moving down the list.

  1. Open Stock Connector's info menu (top right), then choose Reload
  2. If you're using a Mac, choose Clear Web Cache from the info menu.
  3. Click the x button in the top right corner of Stock Connector to remove it. Then relaunch it with the Launch button for Stock Connector in the Home tab (or, if you don't have the button, click the Add-ins button, then More add-ins, then add it from the Store).
  4. If you're using Windows, in Excel go to File > Options > Trust Center > Trust Center Settings > Trusted Add-in Catalogs and check the box "Next time Office starts, clear all previously-started web add-ins cache." and then OK.
  5. Close all MS Office programs (Excel, Outlook, Word, etc.) and then reopen your file and try again.
  6. Close all MS Office programs, then in your Windows file system delete the file %userprofile%\AppData\Local\Packages\Microsoft.Win32WebViewHost_cw5n1h2txyewy\AC\#!123\INetCache\ and try Stock Connector again.
  7. Finally, if you're using a recent version of Windows, click the info menu and choose Attach Debugger. Then click Application at the top of the debugging screen and choose Storage on the left. Click the Clear site data button, then close the developer tools and go back to step 1.
  8. If none of these steps worked, Stock Connector should automatically update within 24 hours.

To stay up to date on the latest fixes and communications, you can always go to the Updates and Bugs Page.

Support and feedback

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 stockconnectorapp@outlook.com.

Privacy

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.

Contributing with PayPal and getting premium features

Stock Connector is provided with 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.

If you'd like to contribute more from outside the add-in, you can do so here (does not enable any premium features):
https://www.paypal.com/donate/?hosted_button_id=2HKWEU63CNYDC

Once a user contributes through the add-in, the add-in stores their account identifier in a database. From then on, those users get access to premium features for one year. Those users also get a promise to never see ads in Stock Connector. These premium features work no matter what computer, document, or operating system you use for Stock Connector. The premium features are associated with your signed in ID, which Stock Connector can get based on the ID you used to sign in to Excel OR from your sign in to Stock Connector directly (with a Google account or another email provider). If you don't have a PayPal account, you can do a guest checkout on PayPal using a credit card.

Here is the current list of premium features. All of these are awarded to users who contribute, for one year:

I will continue to add a combination of some features for everyone and some features for contributors only.

Here's how to contribute and get the premium features:

  1. Use Excel on a PC or Mac (if you're an iPad user, the premium features will still work on your computer, but only if you contribute from a Windows or OSX computer first).
  2. Make sure you have at least 4 stocks in your list to trigger the contribution prompt.
  3. If you see the contribution prompt with the PayPal button, skip to step #5.
  4. If you don't see the PayPal button, this is because Excel couldn't find an account ID to send to Stock Connector. In that case you will be shown a button to "Sign in" to Stock Connector directly. When you sign in with Google, Stock Connector does not get access to any of your Google data, except your email address and display name. If you don't want to use a Google account or another supported email provider, you can also try the following steps to connect your Office account:
  5. Click the PayPal button and follow the instructions in the new window to contribute.
  6. 10-15 seconds after the payment is completed, the ad should disappear and the premium features should be activated.
  7. If PayPal gives you an error saying that your web browser is too old, close the PayPal window and copy the URL provided in yellow text in Stock Connector. Paste it into a modern browser (eg. Firefox, Edge, Chrome, etc.) to pay for Stock Connector outside the app.
To access your premium features on another computer, sign in with your Microsoft account to Excel (or, if you used a Google account or another provider's email address, sign in to Stock Connector via that account).

If your premium features stop working, this is either because the one year is complete, or because Excel doesn't recognize your account. In the latter case, you will see a "Sign-in" button at the bottom of the screen. Sign in to Stock Connector using the same email address you used to pay with PayPal, and then Stock Connector should recognize your premium account.

Terms and data sources

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).

Latest updates

You can view the latest updates and bug info at the Updates and Bugs Page.

About me

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/