Simple guide to the Premium Crypto&Stock Investment Tracker

The first steps in setting up the tracker

This manual pertains to the “Crypto Investment Tracker v1.1 – Premium edition” As newer versions are released, this page will be updated accordingly. Learn more about the update here. A video tutorial is available for an older version of the tracker (v1.0), which can be viewed below. The remainder of this manual is applicable to the most recent version. If you have any questions after reading this guide, please refer to the FAQ.

Welcome to your comprehensive Premium Investment Tracker. Designed to manage both cryptocurrency and stock investments, this tracker will not only monitor your portfolio but also track your net worth, assist with tax preparation, and provide valuable tools like a relevant newsfeed and financial summary.

This guide consists of two parts: the first part covers the tracker setup process, while the second part provides a detailed description of all essential sheets. My aim is to ensure that every user can make the most of the spreadsheet.

In the setup sheet, follow these steps in the given order:

• Step 1: Fill in your data in the account box, entering different brokers and/or exchanges, up to a maximum of thirty.
• Step 2:  Go to the History sheet, observe the data entry, and use cryptocurrency symbols like “BTC” instead of “bitcoin”. Delete columns A to E, but leave column F. Fill in the history sheet with your transaction history.
• *Note: If your broker provides crypto-to-crypto conversion instead of crypto-to-fiat, look up the historical price in fiat and use that number to sell and buy the respective cryptocurrencies. For instance, if you converted 10 BNB into 0.05 bitcoins on 03/01/2021 at 13:00 using Binance, search their website for the historical price of 10 BNB (which equals 2100 euros). In the spreadsheet, first record the sale of 10 BNB at a total cost of 2100 euros, then input a separate transaction to purchase 0.05 BTC at the same total cost of 2100 euros.
• Step 3: Access Menu > CoinAtlas > Build Report > LIFO/FIFO (learn about the tax implications here). Authorize by clicking continue > select your Gmail account > advanced > go to the portfolio (unsafe) – a standard procedure for running any script!
• Step 4: Choose your desired currency (e.g., USD or EUR) for portfolio output in the “Main currency” box.
• Step 5: Within the cryptocurrency box, you will discover a comprehensive list of your owned cryptocurrencies, displayed in column “I.” Kindly input the respective slug for each cryptocurrency in column “H” and the corresponding symbol or currency in column “J,” emulating the given example. Please note that the information in columns “H” and “J” is crucial for the effective utilization of the “Watchlist” feature.
• Step 6: Register at coinmarketcap and retrieve your personal API key from here. Go to Menu>App Script>CoinAtlas. Insert your personal CoinMarketCap API key within the “coinMarketCap” function, specifically in the following location: “X-CMC_PRO_API_KEY”: ‘fbe6a0e0-xxxx-xxxx-xxxx-xxxxxxxxeec9′”.
• Step 7: Fill in the original currency you purchased the stock in the designated box for stocks.
• Step 8: With your history complete, manually input today’s date (avoid the =TODAY() function). This will initiate lifetime net worth tracking.
  *Warning: Only click once on the trigger! Remove additional triggers manually if needed. See FAQ for more info.
• Step 9: Update cryptocurrency price data in the custom menu as needed; otherwise, the function will refresh every 5 minutes.
• Step 10: Update the sector/industry for stocks in the custom menu as required.
• Step 11: Enable or disable the newsfeed in the custom menu.

Additional information

• Price data is obtained from CoinMarketCap (CMC) via an API, with a daily limit of 333 calls and a monthly limit of 10,000 calls. The free plan should suffice for most users, but if you need more, you may need to upgrade to a paid plan.
• The top 100 coins on CoinMarketCap are tracked by default. If you need to track more, adjust the limit within the coinMarketCap function (Menu > App Script > CoinAtlas > coinMarketCap function). For example, in “const URL = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1&limit=100`”, change limit=100 to your desired limit, such as 500. Keep in mind that increasing the limit will also increase your daily API calls, which may necessitate adjusting your trigger for this function. Refer to the FAQ for further details.
• The “Build Report” function is a script that automatically calculates the number of shares you own for each asset and the average price you paid. When selling an investment, the script employs the FIFO or LIFO method. Once an investment’s shares are depleted, they will be automatically removed from the “Positions” sheet. This function can be helpful for generating tax reports, but use caution, as it is still in beta and not specifically designed for tax purposes.
• Please ensure that stocks and currencies are entered in a format that is recognized by Google Finance. In case the price displayed is incorrect, include the stock exchange before the symbol (e.g., NYSE:GME instead of GME) to obtain accurate pricing information.
• Upon clicking “Build Report,” all calculations in the “Positions” sheet should be automated. Ensure that the main output currency is recognizable by “Google Finance” to prevent errors. Test it in a separate cell before inputting it into the tracker.
• Please ensure that the main output currency is entered in a format recognized by “Googlefinance.” To prevent errors, test the currency format in a separate cell before using it.
• Industry and sector data are extracted from Fidelity.
• The tracker comes with default support for 200 cryptocurrencies and 200 stocks. However, you can expand the formulas as needed to accommodate a larger number of stocks.
• The money-weighted-average-return (MWAR) is used to calculate your portfolio’s “total return.” Keep in mind that if your portfolio is less than a year old, the MWAR might yield inflated results. Learn more about the MWAR here.
• If the spreadsheet appears too large or small on your screen, adjust the “zoom” under “view.” For instance, a 135% zoom may be suitable for a 27-inch, 2K screen.
• By default, the tracker supports 30 accounts. To calculate account balances, deposits and sells are treated as positive cash flow, while buys and withdrawals are treated as negative cash flow. To transfer cash between accounts, record a withdrawal in one account and a corresponding deposit in the other. If you need to track more than 30 accounts, you can extend the formulas yourself or contact me for assistance.

Description of spreadsheet

Dashboard

The Dashboard serves as the central hub for a summary of crucial portfolio data. It displays daily performance in absolute terms, percentages, total unrealized gains, and realized gains. Moreover, you’ll find your current cash and account balances at the top right. The middle-right section shows the total unrealized and realized gains in absolute terms.

Metric explanations

• Performance Today: Represents the daily profit or loss as a percentage of the total portfolio.
• Daily Profit (Loss): Indicates the daily profit or loss in local fiat currency.
• Performance Cryptocurrency: Represents the total performance of the cryptocurrency category, considering both unrealized and realized gains.
• Performance Stocks: Reflects the total performance of the stocks category, accounting for both unrealized and realized gains.
• Total Absolute Return: Measures the total performance of the entire portfolio, including both unrealized and realized gains.
• Total Performance: Calculated for the entire portfolio using the money-weighted rate of return formula.
• Portfolio Value: The combined value of all current holdings.
• Available Cash: The total amount of cash available across all accounts.
• Unrealized Gains: The total profit (or loss) for all current holdings.
• Realized Gains: The total profit (or loss) for all holdings that have been sold completely or partially in the past.

Your total net worth is recorded twice daily after the initial setup day. Data is unavailable before the first recording day.

History

The History sheet requires user input and serves as the foundation for calculations throughout the tracker. To modify the tracker, adjust the History sheet’s input data rather than altering formulas.

Report & Summary of Report

The “Report” sheet assists in tax report generation, displaying information such as date, quantity, purchase cost, and total sell price for each sold asset. The “Summary of RG Report” offers a condensed version of the “Report” sheet. Avoid making changes to these sheets.

Positions

Arguably, the most crucial sheet can be found in the “Positions” tab. This sheet offers a comprehensive overview of all your current holdings and incorporates essential information such as average cost basis, portfolio allocation, cost basis, sector, industry, market cap, daily price change, purchase cost, position value, gains or losses, and portfolio return. All the data is calculated automatically based on the History sheet. If you make any changes to the History sheet, ensure you update the tracker through the CoinAtlas menu.

Portfolio Analysis

This sheet offers a visual representation of your investments, which can aid in future decision-making. By evaluating the various allocations within your portfolio, you may gain insights and determine if rebalancing is necessary. Additionally, the insights derived from examining the different allocations in your investment accounts, industry, sector, and market cap size can further enhance your understanding of your investment strategy.

Investor Return

The final sheet in the investment tracker gives you a comprehensive overview of your returns across a specific holding period. You can select your desired view, such as total portfolio, cryptocurrencies, or stocks, from the drop-down list in cell “K4”. Additionally, you can toggle between realized and unrealized gains by changing the value in cell “F4”. The graphs within the sheet are dynamic, adapting to the values in the drop-down list and any modifications made in the “History” sheet.

The first chart displays gains or losses for individual assets, while the second chart shows your portfolio return. This is particularly helpful because a holding that has lost 90% of its value may have a minimal impact on your overall return if the initial investment was only $100. The third chart illustrates your total absolute return in your local fiat currency, encompassing both realized and unrealized gains. Lastly, the final charts are gauge meters that depict your total return based on the money-weighted rate of return and the total dividends received.

Newsfeed

In the Newsfeed sheet, navigate to cell “D7” and click on the arrow to access a drop-down list featuring all your stock holdings. Choose a stock holding, then select the preferred news source for information on that stock. For stocks, you can opt for “Seeking Alpha” or “Yahoo Finance”. If you’re more interested in general cryptocurrency news, you can also choose from sources like “Cryptoninjas”, “Coindesk”, or “Cointelegraph”.

Financial Screener

The “Financial Summary” is another useful tool in the investment tracker. This sheet extracts a table from finviz.com, providing financial information on a specific stock. To access information on a stock, enter its symbol in cell “C3”, and the table should update automatically. Please note that finviz only supports US stocks.

Moreover, a graph displaying the historical price is included in the sheet. You can adjust the graph by entering different dates in cells “P4” through “P10”. This allows you to analyze the stock’s performance over various timeframes.

Watchlist

The Watchlist tool is designed to help you monitor the performance of your current holdings, both cryptocurrencies and stocks. By clicking on cell “C9”, you can select any of your assets from the drop-down list and receive relevant information about its price action. This feature allows you to stay updated on the market movements of your investments, enabling you to make informed decisions about your portfolio.