Market Share

Publication:

California CPA Magazine

Date:

June 2016

Author:

Jeff Lenning

If you get stock quotes into Excel by typing or copy/pasting from your web browser, you may be able to get your work done faster if you use a built-in Excel feature. Excel’s external data feature enables you to retrieve data from a variety of sources—one of which happens to provide stock quotes.

Let’s check it out.

In any blank worksheet, enter the desired ticker symbols into a single cell and use commas to separate them. For example, I’ve entered MSFT, AAPL and GOOG into cell A1, as shown in Figure 1.

Figure 1 by Jeff Lenning

Figure 1

Next, use the following Ribbon command:

• Data > Existing Connections

Note: You are looking for the Existing Connections icon, not the Connections icon. Depending on your screen resolution, the Existing Connections command may appear in the Get External Data drop-down.

When you click the Existing Connections command, Excel displays the Existing Connections dialog box (Figure 2).

Figure 2 by Jeff Lenning

Figure 2

Note: If you are working along and see the Workbook Connections dialog instead of the Existing Connections dialog, then you want to close it and click the Existing Connections icon.

Scroll down to the MSN MoneyCentral Investor Stock Quotes option and click Open.

Note: If your Existing Connections dialog doesn’t include the MSN MoneyCentral Investor Stock Quotes option, then you can download it from the link provided below.

Excel displays the Import Data dialog, asking where you’d like to place the stock quotes. Select any cell under the tickers. I used cell A4 (Figure 3).

Figure 3 by Jeff Lenning

Figure 3

Excel then asks for the ticker symbols via the Enter Parameter Value dialog. Although we could enter them manually, we’ll just select cell A1. We want Excel to use the same cell reference each time we refresh, so, we check the corresponding checkbox. Plus, we’d like Excel to automatically refresh the quotes anytime we make a change to the tickers in cell A1, so, we check that checkbox as well (Figure 4).

Figure 4

Figure 4

When we click OK … bam! Our worksheet is populated with the desired quotes (Figure 5).

Figure 5 by Jeff Lenning

Figure 5

Now, going forward, we can easily refresh the quotes anytime by right-clicking and selecting Refresh or by clicking the following command:

• Data > Refresh All

Plus, when we modify our tickers, Excel automatically retrieves the corresponding quotes. That sure seems faster than a copy-paste from a web browser … and way more fun!

If you’d like to download an Excel file that already has the connection set up, feel free to check out the sample file at www.excel-university.com/tickers.

This article was written by Jeff Lenning