Categories
Dividends Investing

Dividend Tracker 0.97 update: Cryptocurrency

Dividend Tracker 0.97 adds cryptocurrency support!

Cryptocurrencies may be a joke to you, but there’s no escaping the fact millions of people are actively trading digital currencies and even governments are starting to take note. If you’re accumulating some Bitcoin or are using digital currencies to store some wealth, I’ve added the ability to track cryptocurrencies in the Dividend Tracker.

I’ve had a fair few investors who have started using my tracking spreadsheet reach out to ask about cryptocurrency, but it was never something I planned on adding anytime soon. I’ve been tracking crypto using the spreadsheet for months now, but now it’s time for me to roll out support so you can all start adding your crypto holdings.

No longer will you need to keep firing up your wallet app to see how much your holding is worth. Cryptocurrency has been added to the templates as a new sector, which can be selected as you would another stock industry. Everything else is added as usual on your native market tab, aside from the ticker symbol.

Adding cryptocurrency transactions is now a thing!

Since we’re technically working with currencies, you’ll need to add the ticker accordingly. For Bitcoin to British Pound, you’ll enter CURRENCY:BTCGBP which would pull down the current Bitcoin price in GBP. For Ethereum in USD, you’d enter CURRENCY:ETHUSD. The spreadsheet can track your purchases and sales on the transactions tab as usual. Remember to factor in miner fees as you enter transactions. You can add them under the fee column in your native currency or subtract it from your transaction amount.

Changelog

  • Changed Summary dividend yield formula. (Thanks, Jarah for the suggestion!)
  • Added Cryptocurrency support.
  • Updated the UK market price formula.

How do I update my tracker?

This is a relatively easy update to apply to your template. If you’ve just started using my spreadsheet, I’d recommend you move anything entered to a fresh copy of the template. Otherwise, follow these steps:

  1. Change each cell in the Summary tab for dividend yield to =IFERROR(AVERAGE(EU!O2:O),0) (changing “EU” to your market tab) or =IFERROR(AVERAGE(UK!P2:P),0) if the UK. This will correctly display a total below, don’t worry if the cells themselves are blank.
  2. Enter =IFS(AND(D2="ETF",G2="p"),GOOGLEFINANCE(C2)/100,AND(D2="ETF",G2="£"),GOOGLEFINANCE(C2),AND(D2="Cryptocurrency",G2="p"),GOOGLEFINANCE(C2)/100,AND(D2="Cryptocurrency",G2="£"),GOOGLEFINANCE(C2),G2="p",GOOGLEFINANCE(C2)/100,G2="£",GOOGLEFINANCE(C2)/100,AND(ISBLANK(D2)),0) into K2 on the UK tab and drag the cell down to copy the formula to each row.
  3. To add the cryptocurrency as a sector, in the Data tab make a new row above the Discretionary row by selecting N16 to R26 and moving the selection down a row. Enter Cryptocurrency into the newly created N16 cell. Then copy down the O15 to R15 cell formulas down.
  4. You can now enter cryptocurrencies into the market tabs. I’d recommend using your native market tab. Add each transaction to the Transactions tab.

By Rich Edmonds

Rich creates content for the top Windows-focused publication, but by night he tries to make his money work for him and rambles far too much here.

Leave a Reply

Your email address will not be published. Required fields are marked *