Excel’s Stock Data Types

Office 365’s new stock data types allow you to type a company name or stock ticker symbol in Excel and write formulas that extract information about the company to a different cell. The file Stockdatatypes.xlsx contains our work.

To begin we typed in the company names and ticker symbols shown in D6:D9 into cells E6:E9. After selecting the range E6:E9 copy this range. If you now select Stocks in the Data Types group on the Data Tab you will see the icons shown in E6:E9. The presence of these icons indicates that Excel now recognizes the data in E6:E9 as companies.

figure one showing how to use the stock data types in Microsoft Excel
Figure 1: Using the Stock Data Types

Clicking on the icon in cell E6 displays (see Figure 2) a subset of the types of information about Microsoft that you can extract to your spreadsheet.

Microsoft Corp. company data displayed in Excel
Figure 2: Microsoft Data Options

Once you type an equal sign followed by a cell containing a stock data type, you are presented with a dropdown menu listing all available data. This makes it easy to enter formulas involving the new data types. Copying from F6 to F7:F9 the formula =E6.Employees returns each company’s number of employees. In a similar fashion copying from G6 to G7:G9 the formula  = E6.Price returns  each company’s last  price.  Copying from H6 to H7:H9 the  formula  = E6.[Last trade time] returns the last trade  time.  Finally, copying from I6 to I7:I9 the formula = E6.Beta returns the Beta of each stock. The larger the Beta, the more sensitive the stock is to market fluctuations.  Thus, Wal-Mart’s stock price is least sensitive to market changes while Southwest Airlines’ stock price is most sensitive.

If you select a cell containing a stock data type, then right-clicking and selecting Data Type and then Refresh will update the data.

If you enter company names with a heading and use Control T to select the names and heading (in our case E11:E15)  as a table,  then (as shown in Figure 3)  after entering in  cell  F12 the formula  E12.Headquarters and hitting the enter key Excel automatically copies the formula to the bottom of the table (F12:F15)  and enters the address for each company headquarters.  Note the formula now shows as =[@Company].Headquarters,which indicates that Excel will extract the Headquarters Address based  on the entry in the same row of the company column.  Of course, if you enter a new company name or ticker symbol in cell  E16, then cell F16 will automatically fill with the address for the newly entered company headquarters.

Figure 3 Using the Stock Data Types with an Excel Table
Figure 3: Using the Stock Data Types with an Excel Table
Dr. Wayne Winston is a Visiting Professor at Wake Forest University and Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in mathematics from MIT and a Ph.D. in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Excel 2016 Data Analysis and Business Modeling", and "Mathletics". Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.