How to access Excel's revived real-time stock prices.

Author:Collins, J. Carlton

Q. What happened to Excel's ability to import stock prices? That functionality seems to have vanished.

A. Many CPAs treasured the ability to link real-time stock prices in Excel, but Microsoft removed this functionality a couple of years ago, reportedly because it was not fully compatible with Office 365. I'm happy to report that Microsoft has now brought back real-time stock prices, although the steps for creating such a link are different, as illustrated in the following simple example.

I entered the ticker symbol DAL (for Delta Air Lines Inc.) in cell A3, and with cell A3 selected, from the Data tab I selected Stocks from the Data Types group, and then Delta Air Lines Inc from the resulting Data Selector dialog box, as pictured below.

These actions create a live link between your workbook and the Nasdaq exchange database, which includes information from all the major U.S. stock indexes. Next, in cell B3,1 began writing the formula =A3, which pops up a list of available stock data field names that I can insert in my workbook, a partial list of which is pictured above. Finally, I simply selected a field name (Price in this example) and pressed Enter to link the current Delta Air Lines stock price ($58.54 in this example) to my workbook. You can see the resulting formula in cell B3, which returns the current stock price for Delta, is =A3.price.

This functionality works even better when an Excel Table is involved, as follows. With my cursor positioned in cell Al, I inserted a table from the Insert tab by selecting Table and clicking OK to create a table in cells A1:A2. Next, I typed the two column heading names Company and Price in cells A1 and B1, respectively (in this example, my table area expanded...

To continue reading