MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Stock Data Types in Excel


November 07, 2018 - by Bill Jelen

Stock Data Types in Excel

Microsoft is adding Stock and Geography data types to Excel. While I covered geography data types on Monday, today is a discussion of stock data types. This feature is only available to people with an Office 365 subscription - it will never be included if you purchase Excel 2019, or Excel 2016 or Excel 2013.

To start, type a series of stock symbols in a column in Excel. Format the data as a Table using either Home, Format as Table or Ctrl + T. Select just the cells containing stock symbols and use Data, Stocks as shown below.

The stocks icon is likely in English on your computer
The stocks icon is likely in English on your computer

The stock ticker symbols will change to the official name of the company along with an icon with Roman columns in front of a building. Check to make sure each company converted correctly. In the image below, I had wanted Sony instead of Sanofa SA.

Check that each ticker was converted correctly
Check that each ticker was converted correctly

For any mistakes, re-type the symbol over the company name. Sony is SNE not SNY.


Type a replacement symbol
Type a replacement symbol

In less than a second, Excel returns the new company name, converting SNE to Sony.

Select a cell and press Ctrl + Shift + F5 or simply click on the icon to display a data card for the cell.

Click the Icon to display a card
Click the Icon to display a card

In Monday's article, I joked that the card was mostly useless because we needed the data in the grid. But if you hover long enough over a field, this symbol will appear. Click the symbol to add the data to the Excel grid.

Hover, and this symbol appears.
Hover, and this symbol appears.

The data will appear in the next available column. In the current example, I used column A to remember what I had originally typed. Column B contains the linked data types. Previous Close goes to column C with a formula of =[@Stock].[Previous close].

Here is another awesome trick that works if you've formatted the data as a table. If you know the data name, you can simply type a new heading of "Price". After you press Enter, Excel will fill in a formula of =[@Stock].Price

Type a field name adjacent to the headingsā€¦
Type a field name adjacent to the headingsā€¦

Excel fills in the column with a formula to return stock price.

Typing Price as the heading is faster than typing =B2.Price as the formula
Typing Price as the heading is faster than typing =B2.Price as the formula

Caution

This feature is sure to disappoint any serious day traders out there. It makes an awesome three minute demo but the answer to all of the follow-up questions are going to disappoint you. Can you get historical quotes? No. Can you save quotes every minute to look for trends? No. I've tried showing this feature in my live seminars, but the ensuing disappointment is so disheartening, I don't show this in real life anymore. The geography from Monday's article is awesome because the data for a city only changes once a year. Most serious stock traders will not be able to replace their subscriptions with this feature in Excel. It will be great for getting the current value of a small portfolio. But if you dig deeper here, the odds are that this will disappoint you pretty quickly.

Since Excel has to go out to the Internet to recalc these cells, they are not part of the normal calculation chain. To force Excel to retrieve new data, right-click on a cell containing the stock quote and choose Data Type, Refresh as shown here. Alternatively, use Data, Refresh All to re-load all of the data in the workbook.

Refreshing data is located in the right-click menu
Refreshing data is located in the right-click menu

Bonus content in today's video shows the steps to convert the data in the grid to values so you can send it to someone who does not have Office 365.

Watch Video

Download Excel File

To download the excel file: stock-data-types-in-excel.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don't put blank rows or columns in your "contiguous" data"

Title Photo: Marcela Laskoski on Unsplash


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.