The Stocks data type is considered a linked data type because it has a connection to an online data source. That connection allows you to bring back rich, interesting information that you can work with and refresh.
NOTE: This feature is available to Office 365 and Excel Online users. Fundamental company data, historical data, real-time and delayed prices, fund summary, and performance data are provided by Refinitiv. Use of Refinitiv data must be in accordance with the Microsoft Service Agreement. Refresh rates of exchanges vary between 5 minutes and 24 hours (average of 15 minutes).
Setting Up a Stock Data Type
We begin by creating a Stock Data Type. The Stock Data Type is located on the Data tab in the Data Types group labeled Stocks.
This feature can convert company names or stock ticker symbols (ex: MSFT for Microsoft) to get current stock price information like price, volume, and company information.
Start by entering a company name or known company ticker symbol into a cell. We will use MSFT to look up stock information on Microsoft.
With the cell containing the company name/symbol selected, click Data (tab) -> Data Types (group) -> Stocks.
The Stocks feature will attempt to convert the cell data into a Rich Data Type. We see that it is having a bit of difficulty doing this only because there are several potential exchanges to choose from, such as Nasdaq, Borsa Italiana, SIX Swiss, etc.
Selecting one of these exchanges will complete the conversion process. We will select the Nasdaq exchange for this demonstration.
Observe how our entry of MSFT has been converted into the proper name of the company along with the exchange and ticker symbol. There is also a small icon to the left that looks like a tiny rendition of the New York Stock Exchange building.
The resemblance is uncanny.
The icon indicates that this is now a Rich Data Type. If you click the icon, you are presented with a card that displays a tremendous amount of information about the selected company.
You see information such as stock price, headquarters office address, and even number of employees.
Adding Card Information to a Cell
If you see information on the card that you would like to add to your spreadsheet, hover over the information to reveal the “Extract to Grid” button.
Clicking this will add the selected information to the worksheet to the right of the Rich Data Type of the company.
You can repeat this process of “display card -> click item” to add as many of the card elements as you need.
Rich Data Type Arguments
You can think of a Rich Data Type as a function that has arguments. If we select an existing cell created by the above card insertion routine, we see a cell reference followed by a “dot” and an argument; in this case “Price”.
If we were to manually enter a reference to a cell holding a Rich Data Type and follow it with a “dot”, we are presented a list of the various information elements that can be extracted from the card.
You can manually type or arrow/mouse select an item from the list to complete the request. The more letters you type, the more focused the list becomes.
Invalid References
If you happen to reference a cell that does not contain a Rich Data Type, or the selected information (ex: “Employees”) is not available, you will be presented with a #FIELD! error message.
Alternate Way to Add Information
Another way to add company information to a sheet is to select the cell containing the Rich Data Type and click the Card icon.
The default placement location is to be in the next available cell on the same row as the Rich Data Type. You are free to move these entries or hand create them anywhere on the sheet or even separate sheets.
Setting Up a Stock Portfolio
We begin by entering a list of companies from which we have purchased stocks. Notice that I have a combination of company names, stock ticker symbols, and even an incomplete name for one of the companies.
We select the cells holding the information and click STOCKS from the Data tab.
Excel was able to understand most of our entries but flagged the one that posed a bit of a problem.
If we correct/complete the entry and give Excel another chance, we see that it figures it out properly.
Switching Exchanges
If you wish to switch to a different exchange for an existing Stock Data Type, right-click the cell holding the Stock Data Type and select Data Type -> Change.
This will bring up the list originally seen when creating the Rich Data Type. You can select a different exchange from this list.
Adding Information for Multiple Companies Simultaneously
If you wish to add the same information (ex: Price) to multiple companies, you can short-cut the process by selecting the desired cells and clicking the Card to select the needed information.
The information will be added to all selected companies.
Adding Companies to the List
If you need to add companies to the list and you want the information to carry on to the new companies, you should consider “upgrading” the existing data into a proper Excel Table.
Click a cell in the data and press CTRL-T to activate the Create Table dialog box. Our table does not have headers, so ensure the heading option is cleared.
The table gives us temporary headings, so it’s best to replace the generic headings with proper headings.
Adding More Stock Information
Because we have a proper Excel Table, it is easy to add information to all companies in the table.
If you select the cell that would be the next heading cell and begin typing the name of an argument, the AutoFill feature will present options related to the Stock Data Type.
If you select one of the items presented, the associated formulas will populate the table column.
Extending Our Stock Portfolio
We would like to add the following user input and calculations to our portfolio:
- Shares
- Buy Price
- Portfolio Cost (equals the Shares multiplied by the Buy Price)
We also want the following Stock information:
- Price
- 52 Week High
- 52 Week Low
The next column we want is a calculation of the Portfolio Value. This will be the Price multiplied by the Shares.
Finally, the Up or Down position of the company is the Portfolio Value minus the Portfolio Cost.
Identifying the Losses
We can use a bit of Conditional Formatting to draw attention to companies that we have lost money with our investments.
Select the Position calculations and click Home (tab) -> Styles (group) -> Conditional Formatting -> Highlight Rules -> Less Than.
In the Less Than dialog box, we will set the trigger threshold to 0 (zero) and the color to Light Red.
We can now more readily identify where problems exist.
Visual Adjustments
The table is quite wide, so we will tighten up the table by using the Wrap Text option on the cells for company names as well as the column headers.
The table now appears as follows.
Creating a Total Row
As this is a proper Excel Table, adding a total row is simple.
Click a cell in the table and select Table Design (tab) -> Table Style Options (group) -> Total Row.
We can select the dropdowns for any table column and insert various aggregations.
Adding Records to the Table
If we need to add a company to the bottom of the table, then right-click a cell on the Total row and select Insert -> “Table Row Above”.
The newly inserted row will be temporarily populated with #FIELD! errors. These will go away once we enter the new company name/ticker symbol in the first column.
If we enter a new company, such as American Airlines Group, we see all the stock information, along with Conditional Formatting, populate the table.
Refreshing the Stock Information
To update the table with the latest information, right-click on any cell that contains Rich Data Types and select Refresh.
You can also refresh the data by selecting Data (tab) -> Queries & Connections (group) -> Refresh All.
Converting a Rich Data Type to Text
If you need to remove the Rich Data Type functionality from a cell, right-click on the cell and select Data Type -> Convert to Text.
Potential Language Issue
You may encounter a situation where the language used by the ribbon does not match the language used by the Stock Data Type.
This is due to a setting in Excel for Language Options. Select File -> Options -> Language.
Here you will see the top pane that indicates the preferred language for the Excel program, while the bottom pane indicates the preferred language for things like proofing tools and Rich Data Types.
Changing these options requires a restart of the Excel program.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
