The first steps in setting up the tracker
This manual refers to the “Premium Crypto Investment Tracker v1.1“. I will keep this page updated as newer versions are released. If you wish to learn more about this update, visit my other page. Check the FAQ if you still have questions left after reading this guide.
Welcome to your newly found unique investment tracker. This tracker was built for cryptocurrencies! It has some amazing features, including net worth tracking, tax report (FIFO/LIFO), connection with CoinMarketCap API and accounts for lost cryptocurrencies due to transfer (e.g. from wallet to exchange). Not only will it track your investments portfolio for you, it will also provide help you stay up to date with the latest news through the available NewsFeed!.
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 (maximum up to thirty). For example, enter different brokers and/or exchanges. Do not remove CryptoTransfer!
- 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 coins. 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..
Important Note: If your broker does not provide you with a crypto to fiat conversion number, but instead a crypto to crypto conversion number (e.g. bnb/btc conversion), then look up the historical price at time t at your exchange and use that fiat number to sell bnb and buy btc. For example, if 10 BNB was converted into 0.05 bitcoins at 03/01/2021 at 13:00 using binance, then search their website and find the historical price of 10 BNB (=2100 euro) and sell 10 BNB for 1000 euro’s, followed by a second input of buying 0.05 BTC with the total cost of 2100 euro.
- Step 3: Go to Menu>CoinAtlas>Build Report>LIFO/FIFO. Learn more about the difference and implications for taxes here). Make sure to give authorisation by clicking continue > chose your gmail account > advanced > go to portfolio (unsafe). This is standard procedure for running ANY script.
- Step 4: 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 5: In this 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 Watchlist sheet.
- Step 6: Register at coinmarketcap and retrieve your personal API key from here. Go to Menu>App Script>CoinAtlas. Add your personal coinmarketcap API key inside the function “coinMarketCap“, specifically here: “X-CMC_PRO_API_KEY”: ‘fbe6a0e0-xxxx-xxxx-xxxx-xxxxxxxxeec9′”
- Step 7: Once your history is completely filled out, manually fill in the date of today (do not use =TODAY() function!). From this moment onwards, your lifetime net worth will be tracked.
- Step 8: Click on “activate triggers” to start running the functions which track your networth. WARNING: Only click once on the trigger!!! If you accidently clicked twice or more, then you will need to remove the triggers manually. See FAQ for more info.
- Step 9: Optional: Update price data of cryptocurrencies in the custom menu as required. Otherwise it will be updated every 5 minutes.
- Step 10: 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.
- The top 100 coins of coinmarketcap are tracked. If you require more, then consider changing the limit inside the coinmarketcap function (Menu> App Script > CoinAtlas > coinMarketCap function). Specifically in “const url = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1&limit=100`” change limit=100 to any other limit, e.g. 500. Note that this increases your calls per day and this means you might have to downwards adjust your trigger for this function. See FAQ for more information.
- 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 or LIFO 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. Specifically, go to Menu>View>Hidden sheets>Report. This will reveal the “Report” sheet. 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.
- 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 tracker supports by default 30 accounts. For the calculation of the account balance, deposits & sells are considered positive cash flow and buys & withdrawals as negative cashflow. If you move cash from account 1 to account 2, then put a withdrawal in account 1 and deposit same amount in account 2.
Description of spreadsheet
The first sheet you will encounter after the Setup sheet is the Dashboard. Here, you will find a summary of essential parts of your portfolio. On the top you will find your daily performance in absolute terms, percentages, total unrealized gains and realized gains. Moreover, you can view your current cash balance and account balance on the top right. In the middle right you can see your total unrealized & realized gains in absolute terms.
Explanation of the metrics
– Performance today: daily profit or loss expressed as a percentage over total portfolio
– Daily profit (loss): daily profit or loss denominated in local fiat currency
– Total performance UG: performance of unrealized gains for your entire portfolio
– Total performance RG: performance of realized gains for your entire portfolio
– Total absolute return: sum of realized gains and unrealized gains
– Total performance: calculated for entire portfolio using the money-weighted rate of return formula
– Portfolio value: combined value of current holdings
– Available cash: sum of cash available over all accounts
– Unrealized gains: sum of profit (or loss) over all current holdings
– Realized gains: sum of profit (or loss) over all holdings sold completely or partially in the past
Your total net worth is tracker after the first day of setup and recorded twice per day. It is not possible to view further back than the first day of recording.
This is the data input sheet. As a user you are required to fill in this sheet with your data. The spreadsheet uses the information inside the sheet to perform calculations for the rest of the tracker. If you wish to change anything in the rest of the spreadsheet, then change the input data of the history sheet (as opposed to changing the formula’s).
Report & Summary of RG Report
The “Report” sheet can be used to create a tax report. It provides you information regarding the date, quantity, purchase cost and total sell price every time an asset was sold. The “Summary of RG Report” creates a summary of the “Report” sheet. Do not change anything in these sheets.
Analysis of portfolio
This sheet will provide you with a graphical overview of your investments which will help you in your future decision. You may gain some insights by assessing the different allocations within your portfolio and determining if re-balancing is necessary. Furthermore, you may gain various insight from the different allocations in your investment accounts and allocation by market cap size.
The final sheet on the investment tracker will provide you with an overview of all your returns over one holding period. In the drop-down list in cell “H4” you can switch between realized gains and unrealized gains. The graphs in the sheet are linked dynamically, meaning they will change based on the values of the drop-down list and any changes made in the “History” sheet. The first chart will provide you with gains or losses for an individual asset, the second chart will provide you with your portfolio return. This is especially useful since one of your holdings may have lost 90% of its value, however, if you only spend a $100 dollar on that holding the impact on your total return might be minimal. The final charts are gauge meters displaying your total return according to the money-weighted rate of return.
You may view the twenty most recent articles of three different news sources: “Cointelegraph”, “Coindesk” or “CryptoNinjas”. Switch between the sources by changing the value in cell “B7”.
The watchlist tool can help you keep track of what is happening with your current holdings. You can click on cell “C6” and chose any one of your holdings and receive relevent information regarding the price action of the asset.