The first steps in setting up the tracker
This manual refers to the “Crypto Investment Tracker v1.0 – one category v1.0“. I will keep this page updated as newer versions are released. If you wish to learn more about this update, visit my other page. If you are interested in a description of the spreadsheet then visit this page.
Welcome to your newly found unique investment tracker. The tracker works for cryptocurrencies! Not only will it track your investments portfolio for you, it will also provide you with quality tools such as relevant newsfeed and the financial summary tool. This is the only tracker you will need! No paid subscriptions are necessary. You can have it for absolutely free.
This page consists of two parts. In the first part, a description of how to set up the tracker is provided and in the second part a description is provided of all the essential sheets. The goal is that every user is capable of using the spreadsheet optimally.
In the setup sheet, perform the following actions, in these order:
- Step 1: Fill in your data in the account box. For example, enter different brokers and/or exchanges.
- Step 2: Go to the History sheet. Take a look at how the data has been entered. Make sure to enter cryptocurrency symbols. For example use “BTC” instead of “bitcoin”. Then delete column A up and till E. Leave column “F”. This column automatically calculates the average price based on your total purchase costs and the amount of shares. Then delete column “G” and “H”. Now fill in the history sheet with your transaction history. Your broker and/or exchange often provides this data. Add any fee’s to the total purchase cost. Your transaction history is sorted based on type of stockand date to the right of the instruction box. Do not change any data here.
- Step 3: Fill in the currency you wish as output for your portfolio in the “Main currency” box. For example, choose “USD” if from the United States or “EUR” if from Europe.
- Step 4: In the Setup sheet, fill in the “Cryptocurrency” box as listed in the example. It is necessary for the cryptofinance function, which is used to create a sparkline in the dashboard.
- Step 5: Register at coinmarketcap and retrieve your personal API key from here. Go to extra > script editor > CoinAtlas > Add your personal coinmarketcap API key inside the function “coinMarketCap”, specifically here: “X-CMC_PRO_API_KEY”: ‘fbe6a0e0-xxxx-xxxx-xxxx-xxxxxxxxeec9′”
- Step 6: Click on the “Create menu” button inside the Setup sheet. Give permission. Then go to the menu “CoinAtlas”. Press “Build Report”. Now you have an overview of your realized gains in the sheet “Report” which is summarised inside “Summary of RG Report”.
- Step 7: Update price data of cryptocurrencies in the custom menu as required
- Step 8: Optional: Turn off/on newsfeed in custom menu.
- Price data is retrieved from CoinMarketCap (CMC) through an API. There is a limit to 333/calls per day and 10000 calls/month. I expect you will not reach the free limit plan. However, if you require more, you will need to upgrade from the free plan to one of the paid ones.
- Data is extracted for the top 100 coins from CMC. If you have a coin outside the top 100, you are currently not able to track this coin. However, updates in the future will fix this.
- The function “Build report” is a script which automatically calculates howmany shares you own of every asset and the average price you paid for it. If you sell a certain asset, it will use the FIFO method to sell the asset. If you no longer own any shares of the asset, it will be deleted automatically from the sheet “Positions”. It is possible to use this to aid you in creating your tax report. However, everything is still in beta, so proceed with caution as spreadsheet is not desgined to do taxes for you.
- Once you click on the “Build report”, everything inside “Positions” should be calculated automatically.
- The main output currency must be written in such a manner that “Googlefinance” can find it. Test it first in a different cell to prevent errors.
- The tracker supports by default 200 cryptocurrencies, however you can extend the formula’s to support as many cryptocurrencies as you wish.
- The money-weighted-average-return (MWAR) is used to calculate your “total return” over your portfolio. This formula looks at the cash inflows and cash outflows, while taking into account the current value of your portfolio. It also looks at the dates you made contributions. Make sure all these data are correct in order to have an accurate total return.
- If your portfolio is less than one year old, the MWAR will give you back an inflated number. For example if you made 10% return in one month, it will return 120%, because it will assume that you keep making this return. Learn more about the MWAR here.
- If the spreadsheet is too large or too small for your screen, go to “view” and change the “zoom”. For example I use 135% zoom as I have a 2k screen and 27 inch screen.
- The tracker supports by default 30 accounts, however it is possible to increase this if required. You can extend the formula’s yourself or send me a message.
- The script which calculates realized gains is limited to 30 seconds by Google (any script is). If you have an extremely large transactional history, consider cutting it in pieces.