How to track your investment in Excel?

  • Best investing books
    I own a small library of investing books. Right now I have 13 books on the table. Some days ago I ordered 6 new books from […]
  • How to build a stock portfolio tracker into Excel?
    Excel is one of the best places to keep track of your investment portfolio. Yes, there are some cool applications or platforms where you can see […]
  • Investing tips: How to beat 90% of investors?
    History has shown that 80-95% of investors usually lose money. Thus I want to teach you how to avoid losing money. In my first year, I […]

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 to do fundamental and my own portfolio analysis.

If you want to be a good investor, you must have a clear goal that you want to achieve. To achieve this goal, you need to have two things – 1) a solid strategy that should not be deviated from 2) a very good overview of your stocks. The last point is the easiest to do in Excel. Because there you can do it: monthly overviews (which increased/decreased, how big are the profits/losses), annual summaries, portfolio charts, etc.

I started monitoring my investments pretty quickly when I started investing. I bought a nice notebook for it and write down everything I needed to know about my investment. But I soon realized that I wanted charts, and there was no room in this notebook for writing incomes, annual summaries, and so on. So I looked around and found a lot of internet websites where you can track your investments, but none of them are right for me.

So I watched some youtube videos and found that Excel has some perfect formulas to help me out. I even had a subject about data processing in my university so I started to build my own tracker on Excel.

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 functions 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 these interests manually. I’m quite an active investor on Funderbeam and on 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 building this excel in December 2020. I didn’t know the formulas at the time and wrote all the stock quotes by hand until I remembered the formula from my university. This will help me copy these prices from Yahoo Finance. Once that is done, I will face another problem. If I had to schedule, it seemed like b***t, because there were jumps in adding 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, other yellows 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 I did not invest my money skillfully enough in this crisis 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 the 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, 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