Stock Data Types in Excel
November 07, 2018 - by Bill Jelen
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 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.
For any mistakes, re-type the symbol over the company name. Sony is SNE not SNY.
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.
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.
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
Excel fills in the column with a formula to return stock price.
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.
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
