Download a copy of Free Excel Investment Portfolio Spreadsheet or follow this post on how to build an investment stock portfolio spreadsheet using “Data Types” in Excel 365. Thanks to “Data types” feature commencing in Excel 365, it is now easier than ever to gather information and put your stock portfolios together in Excel.

If you do not have Excel 365 subscription, you can access the free online version of Excel (you need to have a Microsoft login) from https://www.microsoft.com/en-us/microsoft-365/free-office-online-for-the-web

Free Excel Investment Portfolio Spreadsheet

Download the workbook to see a Free Excel Investment Portfolio Spreadsheet built using the methods described in this post.

Why Use Data Types

There are several benefits to using Data Types feature in Excel.

  • You do not need to connect to a website(s) to access the data. Whereas previously you might get Yahoo Finance data from web in Excel, now all you need is to convert stock symbols into Stocks data type, and information relating to the stocks can be obtained easily.
  • You can find out properties relating to the data without ambiguity. Suppose we have a stock symbol MSFT for Microsoft Corporation. We may assume that it is the MSFT stock on NASDAQ, however it could be the stock listed on Mexico’s Stock Exchange. There is no way to know for sure. With data type feature, you can inspect the actual stock exchange it refers to without a doubt.
  • Formula speaks for itself. Say you see a figure $22, you can click on the cell to see the formula, which might look like “=[@Stock].Open” or “=A2.High”, and it is quite clear what they stand for — it is definitely not about closing price.
  • Properties of data act like a mini Wikipedia. Stock data type does not just give you opening and closing prices, it also provides other information relating to the company, such as the location of Headquarters, number of employees and industry. This information is displayed neatly in a “data card”.
  • AutoComplete feature. You can see properties (called “field name”) of a data when you enter a formula to extract related information.

Data Card

The Data Card feature is a short summary of information relating to the data in the form of a “Card”. It can be accessed by clicking the stock icon or geography icon. You can also use Ctrl+Shift+F5 keyboard shortcut.

data card with company information for portfolio selection

The feature is still being updated, so the layout on your stock card may be different to what is shown above.

Extract to Grid

When you hover your mouse over a piece of information on the data card, an “extract to grid” icon will appear.

If you click on the icon, the information will be loaded to a new column as part of the table.

Available Data Types

Currently there are two data types available: stocks and geography. However, more should be added later on.

How to Use Data Types in Excel to Create an Investment Portfolio Spreadsheet

Suppose we have four stocks from different stock exchanges listed in cells A2 to A5.

sample stocks for investment portfolio spreadsheet

We can firstly convert it to a table by selecting the list including the header. It is not a necessary step but will allow you access to more features of Data Types.

create table excel

Tick “My table has headers” if you selected headers.

Go to Data tab, in Data Types section, select “Stocks”. If you accidentally included the header before selecting “Stocks”, you can right click on the cell and choose “Convert to text” to change it back.

Stocks data type in Excel to construct a portfolio spreadsheet

If Excel cannot find the stock symbol or figure out which stock exchange you refer to, you will see a question mark next to the symbol. You can select the question mark to get more information (keyboard shortcut Ctrl+Shift+F5).

To see a list of supported stock exchanges, go to Microsoft Support

ambiguity in stock

When you click on the question mark, a “Data Selector” pane will show up. You can use this pane to identify the correct information.

stock confirmation for Excel portfolio spreadsheet

To fix the ticker symbol, you can clarify by adding or changing stock exchange. In this case, it is showing a question mark because Excel cannot find the symbol “BAC” in New York Stock Exchange. Upon further research, the correct code is XNYS, which is part of New York Stock Exchange. Excel automatically updates the cell with the stock exchange icon once NYSE is replaced with XNYS.

Stock selection in Excel portfolio spreadsheet

Notice the icon at the top right of the table. It provides further information on the data and the icon feature is only available if you followed the steps above and converted data to a table first. If not, you can still use formulas or do so one by one. More on Formulas below.

data type formula for investment portfolio spreadsheet in Excel

The screenshot above shows the use of formulas in the case where column A was not converted to a table.

Now if we click on the information icon in the table, we will see the same information as formula input, except it is faster without formula entry.

querying information on stock data type to put together a portfolio spreadsheet

Note that if a piece of information is not available, Excel will show the “#FIELD!” error. You can wrap it with IFERROR() function and change “value_if_error” parameter to a pair of double quotation marks (“”) to show blank if error occurs.

AutoComplete Headers

When you start typing a field name in a new column, Excel will give you autocomplete options based on what you typed. This is only if data is in the form of a table. In the screenshot below, we seek the Price of stocks, and as soon as we type “P”, the list of options “P/E”, “Previous close” and “Price” show up.

autocomplete header for stock data type

Loading Stock Price History

At the time of writing, a function named “STOCKHISTORY” is not yet rolled out. Once available, the function will spill out stock price history between dates specified by users.

An alternative is to get stock price history from websites and load it to Power Query Editor as shown in the screenshot below.

Here are two suggested url for price history:

power query load historical financial data

Power Query Editor allows you to change headers and cell formats before loading it to Excel. For more information on how to use Power Query, refer to Power Query Intro

Change Data Type

In Power Query Editor, you can select a field, then go to Transform tab and click on “Data Type:” to change data type.

power query change data type

Change Column Name

You can also rename columns by clicking on a column name to change it.

Filter Date Period

Suppose you want to show only data after a particular date, you can select the Date field dropdown, then “Date Filters”, then “After”.

power query filter dates

In the window that pop up, select “is after or equal to”, and put in a date.

power query filter rows

Close and Load Power Query

Once you are done with data transformation, click on Close & Load in Home tab to load the transformed data. You may see the screenshot below as the data is being updated.

load query for investment portfolio spreadsheet

Formula Input on Linked Data Type

Once a cell (say A1) is converted to a data type, you can use

=A1.Price

or

=FIELDVALUE(A1, “Price”)

to extract stock price information.

Fieldvalue() function is useful say when you want to create a dynamic formula where field name (in this case, “Price”) changes according to column header.

When a cell has linked data (that is, converted to a data type), you will be able to access an autocomplete list of information after you put a dot/ period after the cell address. The list is sorted numbers first, then A to Z.

Summary of Free Excel Investment Portfolio Spreadsheet

The methods explained above will allow you to create a Free Excel Investment Portfolio Spreadsheet based on your stock selection.

In another post, we will add features such as weight of stock, average return and risk and an optimised portfolio.

See our product gallery

Pin It on Pinterest

Share This