Hello steemians,
In this article, I will give away one of my dearest tools as a crypto investor. Over the course of 5 months I created an excel template to track and trace your whole crypto portfolio. In this article, I will give it away for free and explain how I use it and how why this can be useful for you. After reading this article you will have the tools and know how to monitor your crypto portfolio and know exactly how much progress you are making.
A chaotic crypto portfolio
when I started trading cryptocurrencies I noticed that I quickly lost track of the current value and division of my portfolio. I bought some bitcoins on random times and diverted those bitcoins into random crypto currencies among different exchanges and wallets whenever I felt like it. While being new to all and full of FOMO I bought and sold everything I could get my hands on. I also started investing in cloud mining contracts from different providers to find out the most profitable one. After a while I had no idea how much I invested, how my money was divided and if I was actually making an ROI. I felt like I was walking in the dark with no idea if I was making any progress or not.
I decided I needed to manage my portfolio better and find out about the following points:
• ROI and average gains over time
• Clear overview of my portfolio
• Portfolio history and development over time
• Current profits vs profits If I would have just held bitcoins (instead of trading)
The benefits of tracking your portfolio
When I started to track my portfolio I was seriously amazed by how many insights I got. Sometimes I had no idea how high percentage of my holdings I had in a specific coin without even realizing it. When comparing everything to dollar prices you really become aware of how your holdings are divided and how well you are doing compared to your initial investment. You get a direct view of how your actions influence your portfolio. For me this meant I got a better feeling of the risks and rewards related to my actions and thus increased my decisions making skills significantly over time.
The crypto portfolio templates
In this sheet I created a template for managing your portfolio. I will explain the sheet as simple and detailed as possible, also for anyone who isn’t familiar with google sheets. Some parts will be filled in automatically and some parts require your input. I use this same google sheet for all my comparisons and crypto calculations and will keep updating and adding more sheets and comparisons to it. I am planning to compare exchanges, wallets, lending platforms etc so safe the sheet carefully and share it with everyone to whom you think it might be valuable to. If you have any sheet on request you can say it in the comments below.
Bitcoin & cloud mining investments
If you open the google sheet you find a black and yellow table on the left side. This is the section where you have to fill in your crypto investments. Every time you invest new money into cryptocurrencies (so trading between USD and BTC shouldn’t be included) you fill it in in one of those sheets. Most people won’t invest anything in cloud mining, if this is the case you can skip this table (I will create another sheet later to manage your cloud mining investments and daily income). Fill in the date, investment and the bitcoin value of this investment in current bitcoin prices (the price of bitcoin during your purchase).
Current portfolio
In this sheet you fill in your current crypto portfolio. If you have the coinigy platform this will be very easy. You can easily copy and paste your portfolio and fill in the coin abbreviations and amounts. The sheet will do the rest for you. If you fill in BTC, the sheet will automatically take the BTC price from your COINS & PRICES sheet. This means you only have to fill in the exchange/wallet where you store those coins, the abbreviations of the coins and the amounts.
Portfolio history
In order to know in what days you did well on what exchanges etc I created the portfolio history. Whenever you filled in the current portfolio, the history should automatically show your current portfolio values on different exchanges/wallets on the “now” row. I already set up the “sum” function for two exchanges/wallets. If you add more coins/exchanges you need to manually adapt this in the “now” row by using the “sum” function. After you filled in the “current portfolio”, you copy->paste the “now” row underneath and put a date to it. Make sure you paste only the values and not the formulas (otherwise the “history” changes when you change your current portfolio). Do this by clicking right mouse -> paste special -> only values.
Coins & Prices
In this sheet you fill in all the abbreviations of the coins you have ever or will ever use. I put a formula that automatically looks for the abbreviation in the “Prices” sheet and gives the prices. Therefore, every time you use the sheet you have to go to "prices" sheet and copy ALL the cryptocurrency prices. When you copied all of them, go to the “coinmarketcap” sheet and copy the prices on top of the current prices. This way the “coins and prices” table will automatically update with the current prices of all your coins. I already put in a lot of coins (all the coins I ever invested in). If you use other coins or want to add coins, just go to the right top corner of the cells and drag them down. This way you extend my formula and can easily add more coins just by filling in the abbreviation.
Portfolio calculations
In this sheet, you don’t have to fill in anything. It combines the results of all sheets and shows you various interesting results of your investing. The “initial” column only looks at your investments in bitcoin prices and shows you the profit you would have had if you just kept your bitcoins and never traded in the first place. I think this is a very important factor to take into account. Just making ROI in terms of dollars is very easy in the crypto world and shouldn't be enough. You should make sure you make ROI in terms of bitcoins! Therefore, in the “current” column it shows your current value compared to your investment and calculates how much profit you made in total and how much profit you make daily, monthly, yearly. The difference columns compare the two. If these numbers are positive, your efforts were not for nothing and you should keep trading. If they are negative, this indicates you should change your trading strategy or just stop trading and only hold your bitcoins.
Please let me know if this was helpful to you or not by giving me a + or a – vote so I can keep adding value for you. I always appreciate feedback so if anything is not clear or vague, please let me know so I can make this guide clear for everyone!
Follow me for more crypto tips, tricks, tools and comparisons!
thank you so very very much. i shall give this a try.
Hi, this is absolutely great but..... is it a good idea to have all your crypto info online? You can't download the file right?!
yes! I would advise you to download it
Thanks for sharing. Is this supposed to be a "live-updating" spreadsheet?
The Coin Market Cap price sheet has only old data, and I'm wondering how to get it to update?
i update multiple sheets, but not the coinigy one. Whenever you are using it you can simply update it by copy and pasting it from coinmarketcap (like described in the article).
Spreadsheets can very soon become limiting. It is always better to use a full fledged portfolio tracker. You should check out Cointracking Portfolio Manager.
It has
For details of how to use these features checkout this post.