Crypto
The Crypto tab tracks your investments live prices and performance of your Crypto holdings.
Please note that to use the Crypto Tab you will need to sign up for a CoinMarketCap API key and enter this into the SheetOptions tab. Please signup for a CoinMarketCap API key here - https://pro.coinmarketcap.com/signup/
Introduction to the Tab
The Crypto tab allows you to track your cryptocurrency investments in real-time with detailed performance statistics. The tab consists of two main sections:
The Watch table at the top displays information about your cryptocurrency investments, including those you've purchased and those you're just monitoring.
The Purchase History table lists all your cryptocurrency transactions. The sheet uses this data to automatically calculate various performance metrics.
Adding a new Crypto to the Watch list
To track a new cryptocurrency that you own or are interested in, simply add a new row to the Watch table and fill in the required information from left to right:
Ticker
Enter the ticker code based on your chosen exchange. For CoinMarketCap, use the abbreviated ticker (e.g., BTC, ETH). For CoinGecko, use the full cryptocurrency name (e.g., "Bitcoin", "Ethereum").
Target Allocation
If you manage your portfolio with target allocations to maintain balance across your holdings, enter your desired allocation percentage here. This helps the sheet calculate rebalancing requirements.
Buying and Selling
Every cryptocurrency transaction must be recorded in the Purchase History table. Here's how to enter your buys and sells:
Ticker
Enter a ticker that matches a cryptocurrency in your Watch table.
Purchase Date
Enter the date of the transaction.
Volume
Enter the number of units involved in the transaction. Use positive numbers for purchases and negative numbers for sales.
Unit Price
Enter the price per unit in your local currency. For trades between cryptocurrencies (e.g., BTC → ETH), you'll need to convert to your local currency (see example below).
Brokerage
Enter any fees or brokerage costs associated with the transaction.
Sold Units (if needed)
This column automatically tracks how much of an original purchase you've sold, using the first-in-first-out (FIFO) principle. You can manually adjust this cell if you prefer a different accounting method.
IMPORTANT: All cryptocurrencies that you currently own (have a positive balance for) and have transaction history for must be included in the Watch table. This is essential because the current price is fetched from this table.
Buying and Selling between non-FIAT pairs
When trading between cryptocurrencies (non-FIAT pairs), you need to convert the transaction value to your local currency for proper tracking. Here's an example:
Example: Purchasing 3 ETH with 0.12463836 BTC
Your Purchase History entry would be:
Ticker: ETH
Volume: 3
Unit Price: $2,115 (calculated below)
Calculating the Unit Price:
3 ETH = 0.12463836 BTC
With BTC price at $50,814, the total transaction value is: 0.12463836 BTC × $50,814 = $6,347
Unit price per ETH = $6,347 ÷ 3 = $2,115
Calculating Fees: If your fee was 0.0012463836 BTC, convert it to your local currency: 0.0012463836 BTC × $50,814 = $63.33
Enter $63.33 as your fee in the Brokerage column.
Choosing your pricing provider (CoinMarketCap vs CoinGecko)
As of v2.12, you can choose between CoinMarketCap or CoinGecko for cryptocurrency pricing. Make your selection in the SheetOptions tab based on these considerations:
CoinMarketCap:
Requires ticker format entries (e.g., "BTC", "ETH")
Requires an API key (sign up here)
Provides reliable price data
Covers approximately 90% of cryptocurrencies
CoinGecko:
Requires full cryptocurrency name entries matching CoinGecko's website (e.g., "Bitcoin", "Ethereum")
No API key required
May experience occasional loading issues due to Google Cloud rate limiting
Offers broader coverage for certain obscure cryptocurrencies
My Live Price says 'Old Sheet', why is that?
If you see 'Old Sheet' instead of live prices, it means you're using a sheet that was previously migrated from (your 'old' sheet). The migration tool deactivates cryptocurrency price fetching in the old sheet to prevent unnecessary API calls that would exhaust your allowance.
To resolve this issue, you have three options:
Use the new sheet that you previously migrated to
Create a brand new sheet and migrate your data to it
Clear the value in SheetOptions!B50 which flags the sheet as 'older'
Crypto Transfer Fees
To account for cryptocurrency transfer fees, record them as sell orders in the Purchase History table:
Ticker: Enter the cryptocurrency ticker
Volume: Enter the fee amount (as a negative number)
Unit Price: Enter the cryptocurrency price at the time of transfer
This method ensures your cryptocurrency balances and performance statistics remain accurate.
Crypto Staking & Interest
As of v2.11, you can track cryptocurrency staking and interest income. Follow these steps:
Enter your staking income in the Dividends tab:
Payment Date: When the staking reward was paid
Ticker: The cryptocurrency ticker
Holding Type: Set to "Crypto"
Ex-Dividend: Set this to the same as the Payment Date (irrelevant for crypto)
Reinvested: Set to "Yes"
Net Amount: The amount received, converted to your sheet's base currency
In the Dividends tab, for first-time entries of a cryptocurrency, set:
Dividend Frequency: How often you receive payouts (e.g., weekly = 52, monthly = 12)
DRP: Set to "YES"
In the Purchase History table, enter the staking amount as a new purchase:
NOTE: The Unit Price must be the cryptocurrency's price on the day the staking reward was paid. Do not set this to $0 as this will cause incorrect capital gains calculations and double-count your gain.
The yield will now appear in your total returns, with an automatically calculated annual yield percentage.
Adding new rows to the Watch table
To expand the Watch table with additional rows:
Step 1 - Right-click on any row containing a yellow holding.
Step 2 - Click Insert 1 above.
Step 3 - Right-click on the row you selected in Step 1 and click Copy.
Step 4 - Right-click on the empty row you created in Step 2 and click Paste.
This process will add a new row with all the required formulas. Repeat as needed to add more rows to your Watch table.
Last updated