Your ultimate guide to the Basic Crypto&Stocks Investment tracker

General information

Welcome to your comprehensive and user-friendly investment tracker for both cryptocurrencies and stocks! This guide is tailored for the Basic Edition of the Crypto & Stocks Investment Tracker v1.0. If you’re interested in a guide for the premium trackers, kindly choose from the other available guides in the menu.

The Basic Edition provides you with essential tools such as a relevant newsfeed and financial summary tool – all for FREE! This version caters to most investors’ needs, but if you’re looking for advanced features like net worth tracking and realized gains overview (FIFO/LIFO), be sure to check out the Premium Trackers.

In this guide, we’ll walk you through setting up the tracker and acquaint you with the key sheets.

Setting up the tracker

Follow these simple steps to set up your tracker:

• Step 1: In the ‘Setup’ sheet, input your data in the account box (up to 30 entries). For example, enter various brokers and/or exchanges. Need to increase the limit? Contact me for more information.
• Step 2: Go to the ‘History’ sheet and observe how the data is entered. Use the slang of the currency, not the symbol (e.g., “bitcoin” instead of “BTC”). Delete columns A to E but keep column “F” for average price calculation. Delete columns “G” and “H” too. Now, input your transaction history, adding any fees to the total purchase cost. Don’t modify any data here.
• Step 3: In the ‘Positions’ sheet, add an “=” sign in cell A3 to activate the formula. This custom formula calculates symbol, shares, and average cost for shares using FIFO, based on the ‘History’ sheet. Change the input data in the ‘History’ sheet if needed.
• Step 4: Inside the ‘Cryptocurrency’ box, fill in columns “F” (Symbol/Currency) and “G” (Cryptocurrencies Owned) to generate a sparkline in the dashboard.
• Step 5: Choose your desired output currency in the ‘Main Currency’ box (e.g., “USD” for the United States or “EUR” for Europe).
• Step 6: Input the original currency you purchased the stock in the designated box for stocks. Change only the data in column N!
• Step 7: Click ‘Activate Trigger’ to enable the IMPORTHTML functions for updating cryptocurrency prices (every 15 mins) and sector/industry data (every 12 hours). Authorize the script by clicking Continue > Choose your Gmail account > Advanced > Go to the portfolio (unsafe).
• Step 8: Optional: Manually update price data of cryptocurrencies and sector/industry for stocks via the custom menu as needed.

Now, you’re all set to track your investments like a pro with the Basic Crypto & Stocks Investment Tracker!

Additional information

• Cryptocurrency price data is sourced from Coingecko using the IMPORTHTML function, refreshing hourly. Activating a trigger forces a refresh every 10 minutes. Use the slang of the cryptocurrency, not the symbol (e.g., “bitcoin” instead of “BTC”).
• To verify the correct slang, search for your coin on the Coingecko website and check the URL’s last part. For instance, for THETA, the link is “https://www.coingecko.com/en/coins/theta-network“, meaning you should use “theta-network” in the history sheet.
• Stock price data is retrieved through the Googlefinance function, refreshing every 20 minutes. Ensure that the stocks and local currency are written in a format recognizable by Google Finance. If necessary, include the stock exchange before the symbol (e.g., “NYSE:GME” instead of “GME”).
• In the history sheet, the total purchase costs should account for all expenses, including exchange fees and transactional costs.
• The Money-Weighted Rate of Return (MWRR) calculates your total portfolio performance. Ensure accurate cash inflow & outflow entries (e.g., deposits & withdrawals) for reliable results.
• The “cryptofinance” function, used for creating a sparkline in the dashboard, has a daily limit of 25 requests. If the graph doesn’t appear, you may have reached the limit.
• To adjust the spreadsheet size, go to “View” and change the “Zoom” (e.g., 75% for a 15-inch laptop screen).
• The tracker supports up to 100 stocks and 30 cryptocurrencies by default. For more extensive support, contact me for further information.

Description of spreadsheet

Dashboard

The Dashboard is the first sheet you’ll see after the Setup sheet. It provides a summary of the key aspects of your portfolio. At the top, you’ll find daily performance and the performance of your cryptocurrencies and stocks. Additionally, the top right displays your current cash and account balances. In the middle-right section, you can see your total unrealized gains and overall portfolio performance.

To view key metrics for a specific asset, click the arrow next to the symbol’s name (e.g., bitcoin), and the tracker will display financial metrics and a sparkline. The waterfall chart shows the unrealized gains (or losses) of your portfolio and the relative contributions of each asset. For instance, a 90% loss on a stock may have a minimal impact on your portfolio if you initially invested only $100 in that stock.

History

The History sheet serves as the data input area. As a user, you need to fill in this sheet with your transaction data. The spreadsheet uses the information from this sheet to perform all calculations. If you want to modify anything in the rest of the spreadsheet, make changes to the History sheet.

Positions

The Positions sheet is arguably the most important one, as it gives you an overview of all your current holdings. It includes 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.

Analysis of portfolio

This sheet offers a graphical overview of your investments, helping you make informed decisions about your portfolio. By examining various allocations, you can determine if rebalancing is necessary and assess your portfolio’s diversification across industries and sectors. Additionally, the account balance graph shows a summary of your balances per account – hover your mouse over the graph to see specific values.

Investor return

The Investor Return sheet provides an overview of all your returns over a specific holding period. Use the drop-down list in cell “H4” to select your total portfolio, cryptocurrencies, or stocks. The graphs in this sheet dynamically change based on the values of the drop-down list and any updates made in the History sheet. The first chart shows gains or losses for individual assets, while the second chart displays your overall portfolio return. The final charts are gauge meters indicating your total return based on the money-weighted rate of return and the total dividends received.

Newsfeed

In the Newsfeed sheet, click the arrow in cell “D7” to see a drop-down list of all your stock holdings. Choose a stock and click on your preferred news source – “Seeking Alpha” or “Yahoo Finance” for stocks, or “Cryptoninjas,” “Coindesk,” or “Cointelegraph” for general cryptocurrency news.

Financial Screener

The Financial Summary sheet extracts a table from finviz.com, providing financial information on a specific stock. Enter the stock symbol in cell “C3,” and the table should update automatically. Note that Finviz only supports US stocks. A historical price chart is also available, which you can adjust by entering different dates in cells “P4” to “P10.”