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 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.
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 return 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
