Crypto
The Crypto tab tracks your investments live prices and performance of your Crypto holdings.
Manual User Cells:
• Watch Table - Ticker Code
• Watch Table - Target Allocation
• Purchase History in the Purchase History table towards the bottom
This feature is only available in the v2 Sheet due to API requirements.
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/
The Crypto tab tracks your crypto investments live and shows various kinds of performance statistics. At the top of the tab you have the Watch table, which holds information about your investments (both those you've invested in and haven't invested in but are following).
The Purchase History table contains a list of all your purchases to date. Based on these exact prices and dates the Sheet automatically creates a variety of performance statistics.
To start tracking a new Crypto that you own or have an interest in, simply start from left to right and fill out a new row.
Column | Explanation |
Ticker | This refers to the ticker code according to your chosen exchange. For CoinMarketCap, this is the abbreviated Ticker (ie. BTC, ETH), for CoinGecko this is the full cryptocurrency name (ie. "Bitcoin", Ethereum") |
Target Allocation | For those who like to apply allocations to their portfolios to always keep all their holdings in a target balance, this is where you would set that allocation. |
Every time you buy and sell a Crypto holding, you must enter it into the Purchase Table.
Column | Explanation |
Ticker | This is a ticker matching the holding in the Watch table. |
Purchase Date | The date of purchase. |
Volume | The amount of units moved. This is a positive number when purchasing, negative number when selling. |
Unit Price | This is the price you purchased/sold at in your local currency. If you purchased with a non-FIAT currency (ie BTC -> ETH), you will need to convert the amount of whatever you sold into your local currency. |
Brokerage | The fee or brokerage on your buy/sell. |
Sold Units (if needed) | This is the column that automatically keeps track of how much of an original purchase you've sold. This should be automatically calculated based on the first-in-first-out (FIFO) principal. If you would prefer a system other than this, feel free to update this cell. |
IMPORTANT: All Crypto holdings that you still own (ie. have a positive balance for) and have a purchase history for must be in the Watch table. This is where the current price is fetched from.
When buying and selling between non-FIAT pairs you must convert the equivalent purchase price back to AUD for the purposes of the Sheet.
Example: Purchasing 3 ETH with 0.12463836 BTC. The entry in the Purchase Table would be:
Ticker: ETH
Volume: 3
Unit Price:
3 ETH = 0.12463836 BTC
3 ETH = 0.12463836 BTC x $50,814 (to convert the total price to AUD)
3 ETH = $6,347 (total price in AUD)
1 ETH = $2,115 (unit price in AUD)
The first step is finding the equivalent total purchase price in AUD. With a BTC price of $50,814 the total price of the transaction is $6,347 for 3 ETH.
The Unit Price (what the Sheet needs) refers to the price of a single ETH, so the unit price that you would enter is $6,347 / 3 = $2,115
Fees:
Continuing on the example above, find your equivalent fee (in this case priced in BTC) by converting it to your local currency.
ie. The fee is 0.0012463836 BTC. Therefore 0.0012463836 x $50,814 (BTC Price) = $63.33.
The total fee is $63.33.
As of v2.12, there is support a choice of either CoinMarketCap or CoinGecko for pricing support. This choice is made in the SheetOptions tab. Please consider your choice according to the below:
CoinMarketCap:
- All holdings must be in ticker format (ie. "BTC", "ETH")
- Very robust price fetching
- Covers 90% of Crypto trickers
CoinGecko:
- All holdings must be in full name format matching CoinGeckos website (ie. "Bitcoin", "Ethereum")
- No API Key needed
- Spotty price fetching, frequently can get caught on "Loading..." due to Google Cloud rate limiting
- Has an increased range of tickers available in comparison to CMC for some obscure coins
To account for Crypto Transfers and associated fees, enter the transfer fee as a sell order. Enter in the fee according to the below:
- Ticker: Associated Crypto Ticker
- Volume: The Fee volume
- Unit Price: The price at the time of the Transfer
This will ensure your Crypto balances and performance statistics are accurate.
As of v2.11, the Sheet can account for any income associated with Crypto staking. To record this accurately, please follow the steps below:
1.Enter your Staking income into the Dividends Tab as per below:
- Payment Date - When the Staking return was paid out
- Ticker - The ticker of the Cryptocurrency
- Holding Type - Set this to Crypto
- Ex-Dividend - This is irrelevant for Crypto, please set to Payment Date
- Reinvested - Set to Yes
- Net Amount - The equivalent amount you were paid, converted to your Sheets base currency.
2. In the dividends tab if this is your first time entering for this Crypto currency, make sure to set the Dividend Frequency to how many times a year you are paid out. This could be weekly (52) or monthly (12) for example. Set DRP to YES.
3. In the Purchase History Table in the History Tab, enter the Staking amount as a new purchase.
NOTE: The Unit Price must be the Crypto price as of the day the staking was paid out. Do not set this to $0 as this is incorrect. This will double report your gain and result in incorrect capital gains calculations.
4. You will now see the yield reported in your total returns, and an associated yield %/annum automatically calculated.
If you would like to expand the watch table, please follow the instructions below:
Step 1 - Right click on a row that contains a yellow holding.
Step 2 - Click Insert 1 above.
Step 3 - A new row should have been created but it will be empty of all formulas. To fill it with the correct formulas, right click on the row you clicked on in Step 1 and click on Copy.
Step 4 - Click on the empty row you created in Step 2, right click and click on paste.
This should expand the table and fill the new row with all the required formulas. Please repeat this as often as is needed.
Last modified 1yr ago