A comment on YouTube with a reader request led me to create this Excel template. It can be used to create an overview of your stock portfolio.
The request was to record a portfolio value history in Excel and to visualize it with a diagram. The total value of the portfolio was to be entered manually every Sunday. A line chart is automatically updated from these values.
In addition, I added a portfolio overview that automatically determines the current prices and can therefore display profit and loss.
Overview of the Performance of the Portfolio (managed manually)
The first worksheet is filled with two values: The year and the deposit value for the individual Sunday. First enter the year at the top. The Excel template now automatically enters all Sundays in the list.
Next, enter the existing values for the Sundays in the past.
Below the diagram you can see the current value and whether a profit or loss has been made.
Incidentally, the entry value is taken from the second worksheet called “Share portfolio”. Of course, you can also enter this manually.
Screenshot of the Performance Portfolio
Overview of the Portfolio with automatic Prices
The individual values of the portfolio are entered in the “Portfolio” worksheet:
- Title
- Security identification number (ISIN) or Symbol
- Number of shares
- Purchase price
- Purchase date
- Purchase fee
- Dividends
Total purchase price is determined automatically. These fields are gray, please leave them untouched – they are programmed and get filled out automatically.
In the right part of the table, which is highlighted in gray, the profit and loss is automatically determined by retrieving the price data.
ATTENTION – this only happens if the price data has been adjusted according to your shares. The values and share titles used here are for demonstration purposes only.
As you may have already seen, the shares from the table are listed again at the bottom, with a small bank icon in front of them and the price next to it.
These values have been inserted using the “Data” function in Excel.
Screenshot of the Stock Portfolio
Adjusting the portfolio to your own shares
First delete the 2 existing stocks and fill out the table with the stocks you bought already.
For example enter all the data for a Colgate-Palmolive share purchase in a new row in the main table.
Click in an empty cell below the listed shares at the bottom (with the bank symbol) and enter the name of the share: For example Colgate-Palmolive.
Normally the Data Selector on the right will open. If not, select the stock symbol from the “Data” ribbon.
A selection of stock exchanges on which the share is traded is now displayed on the right-hand side of Excel. If your stock exchange is missing, click on “Show more results” at the bottom. The trading place of the other stock exchanges should now appear. Click on Select (perhaps a second time).
Congratulations, the link to this share has been established. Now we just need to display the price. Click in this cell and on the drop-down symbol. Select “Price” there. Perfect, you have now displayed the current price in a cell next to it.
Sorry, the context menu is in German – I changed Excel to English, but that menu wasn’t translated 🙁
Finally, we need to link this value to the cell in the top right-hand column for “current value”.
Click in the cell in the “Current price” column and enter “=”. Now move the mouse to the cell containing the current price of the share (bottom left). Click once and confirm the entry with the “Enter” key.
Download the Stock Portfolio Excel Template
You can download the Excel file here:
If you like my templates, I’m looking forward to a little donation 🙂
