One of the most powerful and most useful features in Microsoft Excel is its innate ability to bring in data from outside sources. The list of available sources ranges from other Excel worksheets to other Office documents to databases–and just about everything in between. Among my favorite outside data sources is an add-in called Stock Connector.
Stock Connector provides free access to the current stock price of any security trading on any exchange tracked by Yahoo Finance. You can also use Stock Connector to follow market indexes like the S&P 500 (SPX). Installing and using this free add-in can be accomplished with a few clicks. Here is how you do it.
Installing Stock Connector
The first step is to download Stock Connector from the Microsoft Office App Store. If you reach the Office Store through a web browser, before you click the Add button, you should probably sign in to your Office 365 account if you have one–it’s just easier that way.
You can also reach the Office Store through the Insert tab in Excel. Click the Store button and navigate to the Stock Connector page. No matter how you get there, clicking the Add button (Figure A) will install the Stock Connector app.
Figure A
Once the app is installed, navigate to the Insert tab in Excel and click the My Add-ins button. You’ll be presented with a list of installed add-ins; just click Stock Connector to activate the app. A new window should open and you’ll see a search box where you can enter ticker symbols for the stocks or other securities you want to track (Figure B).
Figure B
To insert a stock price into an Excel worksheet, click the cell where you want the price to display, type the ticker symbol of your stock into the Stock Connector search box, and then click the Connect button. The add-in will search Yahoo Finance for the information and ask you to confirm the cell where you want to deposit it. Assuming the cursor is on the correct cell, all you have to do is click OK.
SEE: Six tips for using Excel sheet groups in easy (but unusual) ways
For an example of how you might use Stock Connector, take a look at Figure C. I made a simple listing of stocks and their ticker symbols. Then I added columns for how many fictional shares I own (C) and what fictional price I paid for them (D). The next column (E) calculates my cost basis by multiplying C x D.
Figure C
The next column (F) is where I inserted the market prices found by Stock Collector. I used that information to calculate the market value of my shares (G) by multiplying C x F. The next column (H) calculates my gain or loss based on the current market price by subtracting C from G. The last column (I) shows how each stock is trending for the day, which comes directly from information gathered by Stock Connector.
To make gains and losses obvious at a glance, I took advantage of Excel’s conditional formatting. This is just a basic example, but with a little imagination you can use other conditional formatting techniques to convey even more detailed information.
Features
Stock Connector will update the stock price instantly when you enter the ticker symbol for the first time and every 15 seconds after that for as long as you have the Excel worksheet open. The add-in works in the Excel Desktop version as well as online. This means you can use One Drive and other cloud storage services for your stock-tracking Excel worksheets and not lose the Stock Connector features.
Stock Connector can retrieve information about any stock, indexes, ETFs, etc., that are supported by the Yahoo Finance API. However, for more obscure securities you may have to do some research to find the right ticker symbol.
I used Excel 2016 for the example in this article, but the add-in was originally designed for Excel 2013 and presumably works in that version as well. Note that while Stock Connector does not cost any money to install and use, it does display a small advertisement in the lower portion of its window. It is unobtrusive, but it is there nonetheless and you may notice it from time to time.
Also read…
- Microsoft updates Office 365 with six new Excel functions
- Get the most out of your Excel PivotTables with these handy tips
- Office Q&A: Add custom AutoComplete items; evaluate groups in an Excel data set
- Filter data even faster with a custom filter technique in Excel
Other add-ins?
As a paid alternative, you can check out Personal Capital.
Do you have a favorite add-in that brings more power or convenience to Excel? Share your recommendations with fellow TechRepublic members.
