Simple guide to the Premium Crypto Investment Tracker

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. CoinAtlas built this tracker for cryptocurrencies! It has some fantastic 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, but it will also 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 report 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 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 number of coins. Then delete columns “G” and “H.” Now fill in the history sheet with your transaction history. Your broker and exchange often provide this data. Add any fees to the total purchase cost.
• Important Note: If your broker does not provide you with crypto to fiat conversion number, but instead 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 on 03/01/2021 at 13:00 using Binance, then search their website and find the historical price of 10 BNB (=2100 euros) and sell 10 BNB for 1000 euros, 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 authorization by clicking continue > choose your Gmail account > advanced > go to the 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” from Europe.
• Step 5: In this sheet, fill in the “Cryptocurrency” box 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 filled out, manually fill in today’s date (do not use the =TODAY() function!). From this moment onwards, the tracker will track your lifetime net worth.
• Step 8: Click on “activate triggers” to start running the functions which track your net worth. WARNING: Only click once on the trigger! If you accidentally clicked twice or more, 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, the function will update it every 5 minutes.
• Step 10: Optional: Turn off/on the newsfeed in the custom menu.

Additional information

• 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 paid one.
• 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, which means you might have to adjust your trigger for this function downwards. See FAQ for more information.
• The function “Build report” is a script that automatically calculates how many shares you own of every asset and the average price you paid for it. If you sell a particular investment, it will use the FIFO or LIFO method to sell the asset. If you no longer own any investment shares, they 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 the spreadsheet is not designed 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 so that “Google finance” 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 formulas 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 a 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 a 27-inch screen.
• The tracker supports by default 30 accounts; however, it is possible to increase this if required. You can extend the formulas yourself or send me a message.
• The tracker supports, by default, 30 accounts. For calculating the account balance, deposits & sells are considered positive cash flow, and buys & withdrawals as negative cash flow. If you move cash from account 1 to account 2, put a withdrawal in account 1 and deposit the same amount in account 2.

Description of spreadsheet

Dashboard

The first sheet you will encounter after the Setup sheet is the Dashboard. Here, you will find a summary of the 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 the entire portfolio using the money-weighted rate of return formula
– Portfolio value: the combined value of current holdings
– Available cash: sum of cash available over all accounts
– Unrealized gains: sum of profit (or loss) overall current holdings
– Realized gains: sum of profit (or loss) overall holdings sold completely or partially in the past

Your total net worth is tracked 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.

History

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 formulas).

Report & Summary of RG Report

The “Report” sheet can be used to create a tax report. It provides you with 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 insights from the different allocations in your investment accounts and allocation by market cap size.

Investor Return

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 $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.

Newsfeed

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”.

Watchlist

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 relevant information regarding the price action of the asset.