How to track your investment in Excel?


If you want to be a good investor then you have to have a good strategy and overview of your investments. I can’t say that, I’m a good investor, because I have made some bad decisions and I’m doing them in the future as well. To avoid these mistakes, I set a goal to make the best Excel table, where I can do fundamental and my own portfolio analysis.

I started to track my investments on paper. But there was hard to make graphs, analysis table and so on. After two year I started to make all this into Excel. I work with Excel every day, thus I have a good knowledge there.

Why Excel?

I’m sure that you have seen the Sharesight ad. But if not then this is a platform where you can track your investments and I’m sure that there are some good function to analyse your portfolio better than you can do it from excel, but If you invest in very different assets like me. Then you can not insert everything there. I invest in loans, startups, stocks, and cryptocurrencies. I can not put my loans and startups investments into Sharesight. The same thing is with Yahoo Finance.

Yahoo Finance and Sharesight are fully automatic, then Excel is not like this. If Funderbeam shows data without forcing me to log in then I can get these data into excel as well. The same thing is with Estateguru, I have to insert this intrest manually. I’m quite an active investor on Funderbeam and the cryptocurrency market, thus these investments will not go automatically into Excel, and I have to write them by myself, but this takes only a few minutes a week. Thus that is not a big problem. Doing this Excel I have a very clear overview of my investments.

I started to build this Excel up in December 2020. I didn’t know formulas back then and I wrote all the stock prices by hand until I remembered a formula that helps me to copy these prices. When this is done I faced with another problem. If I made a chart this looked like crap, because there were jumps when I added more money.

This image has an empty alt attribute; its file name is image.png
You can see these jumps all over the chart

After a few days of thinking, I found the solution to this kind of problem. If I find out every stock daily profit then this problem disappears. I made a new table, which took me at least two days because I made a mistake somewhere. Now, this chart looks much better.

This image has an empty alt attribute; its file name is image-2.png
Here you can see two jumps one is near 14.09.2019 – this was because one of my investments went south over 60% and the second is at the end. There I earned huge profits from Change Invest.

In excel you can see your portfolio movements. You can also bring the S&P500 index into Excel and compare your portfolio movements with it. But I don’t care about this, so I compare yields.

How is my Excel different from others?

I have looked at many different tracking designs, and they are all very similar in structure, namely top-down stocks, crowdfunding, cryptocurrencies, loans, and so on. The columns contain purchase data and returns (monthly, annual, or total returns) and some may have a dividend column. Then the whole portfolio and diversification are definitely shown. But I don’t like this design and I made my own, where everything is in its own place.

This image has an empty alt attribute; its file name is image-4-1024x458.png

Here you can see two graphs, they are the same in content, but the information is different – diversification by sectors and assets. In addition, you will see 3 tables below the graph on the left – it lists all my investments: purchase data and recent values. At the top right is the yield by year (this year it has already been 81%) and the table next to it shows a summary. The colour adds a shade of green because it is the colour of profit, and it can be seen that the last column of each asset table is yellow, green or red, it shows the return on that asset. Since the Change is 400% in profit, there are other yellows that should show a return close to 0, but in fact, there is even a 100% return, it just doesn’t come out of it.

This structure gives me a much better overview because everything is divided into its own table. In addition, this line graph adds value because you can see your best and worst times in real-time. For example, I see that in this crisis, I did not invest my money skillfully enough, because the recovery did not start until November. But I currently have 30% profits Merko for that, because I bought it from the bottom of the crisis. And well Tallink – I should have looked at the whole market, but at that moment I was obsessed with lowering the averages.

In this Excel, I have a few more sheets, where I get all these data that is shown on my (Excel) home page.

In the bottom line

If you know how to use Excel well then you can do awesome things with it. Although there is not every cryptocurrency and there are problems with different investments like I have (Funderbeam, Estateguru). But If you have a few minutes every week then you can insert these prices by hand.

“Spend each day trying to be a little wiser than you were when you woke up.”

CHARLIE MUNGER