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.
Whether it’s a ? or ? market, you can stay up to date on your stocks in #Excel. Get the low down on how below: https://t.co/BJAWJ7hge0 pic.twitter.com/EbgZ3dyLaE
— Microsoft Excel (@msexcel) July 13, 2020
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.
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.
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.
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.
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
When you click on the question mark, a “Data Selector” pane will show up. You can use this pane to identify the correct information.
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.
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.
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.
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.
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 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.
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”.
In the window that pop up, select “is after or equal to”, and put in a date.
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.
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
