Accessing Stock Information

Tony likes to keep track of his relatively small stock portfolio in an Excel worksheet. He's been manually entering stock values, but now Tony is wondering if there is a way to have stock prices update automatically in the worksheet.

Keeping track of stock information is, for many people, a common task in Excel. If you are using Office 365, Microsoft recently (end of March 2019) made it possible to add dynamic stock information to a worksheet. (The capability was actually added last year, but it wasn't fully rolled out to all Office 365 users until March 2019.) All you need to do is follow these general steps:

1. In a column of your worksheet, type the stock symbols for the companies for which you want data. For instance, you might type MSFT, AAPL, TSLA, AMD, or GOOG into the cells. (You should place only one company stock symbol per cell.)
2. Display the Data tab of the ribbon.
3. Select the cells containing the stock symbols. (See Figure 1.)



Figure 1. Ready to grab stock information in Excel.

4. Click the Stocks tool in the Data Types group. Excel converts the stock symbols to the full company names, the exchange name, and the stock symbol. (See Figure 2.)



Figure 2. The stock symbols are converted to fuller information.

5. It is possible that not all of the stock symbols will convert. This is especially true if there is some ambiguity as to what particular stock you want with the symbol you entered. In that case, Excel displays at the right side of the screen a task pane that allows you to specify, exactly, which stock you want.
6. With the cells still selected, click the small icon that appears at the upper-right of the selected cells. Excel displays an assortment of statistics available for the stocks. (There are quite a few; you can scroll down to see them all.) (See Figure 3.)



Figure 3. Data available about your stocks.

7. Pick the statistic you want to display in the worksheet. Excel adds it just to the right of the stock symbol cells; the stock symbol cells remain selected. (See Figure 4.)



Figure 4. Stock prices added to the right of the stock symbols.

8. Repeat steps 6 and 7 to continue adding statistics about the stocks. Excel adds each new statistic to the right of the existing statistics.
9. Adjust column widths and add your own column headings above the stock information, as desired.

That's it; you now have your dynamic stock information in your worksheet. That's not all, though. You'll notice that immediately to the left of the stock symbols (in the same cell) there is a small icon. This is called a "card," and if you click on it, you'll see summary information about the stock. (See Figure 5.)

Figure 5. Displaying the card information for a stock.

According to Microsoft, the information provided by this stock capability is real-time. More information about this can be found at this terribly long URL:

https://www.microsoft.com/en-us/microsoft-365/blog/2019/06/05/stocks-data-type-microsoft-nasdaq-refinitiv-empower-investors-with-real-time-data/

You should note that one of the drawbacks of the built-in stocks in Excel is that they won't work with foreign exchanges. They are great, however, for US stock exchanges.

What if you aren't using Office 365 or you need the capability to access foreign stock information? Are you out of luck? Not really, but your available solutions won't be as integrated into Excel as you might like. You can, for instance, use PowerQuery to extract stock information from a website and store it in a worksheet.

You could also add a third-party tool to do the grabbing and analysis for you. ExcelTips subscribers have suggested the following possible tools:

https://www.quotelink.net/
http://www.michael-saunders.com/stocksapp/pages/info.html
https://www.powerusersoftwares.com/so/8fMbYWA94

We haven't tried these tools out, but they (and other similar tools) may work for your purposes.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13646) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.