Tracking Crypto Currency


The world is really moving fast. The time appears short each day. It was only yesterday that you were using dollars to pay credit-card bills etc, while today you have transactions going on using Bitcoins and investments in Utility tokens! Yesterday you just worried about the percentage inflation and the eroding future-value of that hard-earned dollar, and today you are buying Bitcoins to preserve and even enhance its value over-time! What is your frame-of-reference to measure that value? Usually, measuring how-much your Bitcoins are worth in USD today and tomorrow compared to when you bought them. (You probably transacted into crypto-currency multiple times and every time its value was “quite” different, isn’t it?). As if, tracking your credit-cards and (multiple?) bank accounts was not a pain in itself, now you have your foot in the crypto-currency world and every coin/ICO/token you buy into, is a completely new dimension in itself. How do you, then, go about tracking crypto currency?
Do you (like me) find it easier when things are specified in USD (or the base-currency you use in your country)? Calculating the difference between your buy price (USD per BTC) and how much it is worth today (USD per BTC) gets you quite comfortable and happy with that resultant in USD, isnt it?
How about Ethereum (ETH), which you probably bought using your accumulated BTCs (and not directly through USD). Did you have the urge to find how much (in USD) you are paying for that ETH? Even if you didn’t calculate it before buying, you certainly did see its dollar-worth afterwards. As the crypto-currency world was looking quite exciting (both risky and adventurous), you started exploring some ICOs too, or are you not there yet? ICOs are quite risky because there is (yet) no formal method one can use to arrive at a value of their token, and most of them can be intentional scams and some of them even without a worthy block-chain use-case. But after your initial-study and satisfaction, you did buy those tokens today using ETH. The same ETH that you had bought earlier using BTC at an crypto-exchange-rate you don’s seem to remember clearly.

What was your average buy-price into the crypto-world?

The tokens you bought as investment, would be valued at a different rate tomorrow (hopefully much higher) but wait a minute, which rate? Tokens per ETH, or, tokens per BTC, or, per USD? While the value of ETH as well as BTC fluctuates drastically every day, how do you find the worth of your tokens any fine day? Unless you make tokens/ETH/BTC as your base-currency itself, it is quite a brain-wreck.
Consider another case where you think that being on ETH vs BTC trading is going to be better than USD vs BTC trading. So, you want to trade based on the varying differential between ETH and BTC and cash-in on the arbitration. Would you calculate USD per ETH and ETH per BTC for your decision, or maybe AFTER the transaction to see how much you made on that one? How about a third token, let us say ATL (
So evaluate:
Number # of ATL per ETH (get # ETH per BTC (get # USD per BTC)).
Every-time you are thinking about that, the rate is constantly changing, so you think “maybe” when the BTC is able to buy more ETH, you would be better off even getting those ATL (as compared to now). However, that is even more beneficial when you get more BTC with the same amount of USD.
It really starts messing up your mind. In the long term, the USD is itself affected by the inflation. That was the main reason you started moving from fiat money into crypto-currency. Today you have stopped basing the value of USD in gold. Gold remains a hedge but hardly a way to measure the value of your portfolio. So what value do you put to BTC? What is it that ETH is worth, and all those ATL tokens? It will be very different tomorrow! Maybe you will evaluate USD w.r.t. BTC one day, who knows?

Tracking Crypto Currency in Excel

I started putting the following sheet together to get some sanity in this madness. I don’t know what other methods you use (you can tell me about it) but the following started giving me some perspective, of “Where am I?”.
First of all, do the following for USD<->BTC transaction management. (This may very well be provided by your BTC wallet provider, but just be with me and you would start seeing the larger benefit)

Here is the column-wise description (Column B-D is FIAT money tracking while E-J is Bitcoin tracking)

  1. Date of Transaction
  2. Money (USD etc) transferred INTO your crypto-currency (BTC etc) wallet
  3. Money used to buy your crypto-currency
  4. Balance of your FIAT money (USD etc)
  5. Exchange rate of crypto-currency (BTC etc) w.r.t. your fiat currency (USD etc)
    (Get the rate, from here or where you purchased it from)
  6. Description of your crypto-transaction (Buy/Sell BTC, Use BTC with merchant etc)
  7. Crypto-currency purchased (Auto-populated from money used and exchange rate)
  8. Amount used for the crypto-transaction (Column F)
  9. Balance of your crypto-currency in your wallet. (Auto)
  10. Balance of your crypto but shown in you base currency. (Auto)

So, at any point of time you get a full-view of the growth of your portfolio, not just in absolute crypto-coins but also in the base-currency reference. You can readily find out at what levels you purchased some of the coins, historically/averaged, and their value today should you decide to sell them. Now, this was not too difficult (given your wallet already supports this transaction history). The best part starts now, when you have to use multiple wallets and web-services to get into alternate coins (Ethereum etc) and utility tokens (ATL etc) etc and each one has its own transaction history and its own wallet or mobile app.

But, if you are here with me till now, the excel is easy to extend further. Here we use the same sheet but add more  columns for tracking crypto currency Bitcoin to Ethereum exchange.
Bitcoin to Ethereum exchange tracking
And, here goes the simple description of the new columns

  1. Etherum per Bitcoin exchange rate (or any-to-any crypto for that matter)
    (For exmple, you can get it from here or your favorite exchange)
  2. Ethereum coin cost in terms of your base currency USD etc (Auto-derived from ‘E’ and ‘K’)
  3. Amount of Ethereum purchased (Auto-derived from “H” with exchange rate from “K”)
    (Btw, this could have been any other alternate coin, and not just Ethereum)
  4. Amount of ETH used to purchase something else (Tokens or other ICOs)
  5. Your Balance of alternate crypto-currency. (Auto)
  6. Your balance of alternate crypto-currency in USD base-currency. (Auto)

Now, you should start seeing the benefit of this single-sheet as a reference of your USD, BTC, ETH portfolio that you can use for tracking crypto currency investments and its current/historical value for decision making, without getting brain-wrecked in this madness.
Adding an ICO transaction now becomes even easier, as you have your base excel sheet properly set. So, now you decide to purchase a token using ETH (which was bought using BTC at some historical price). The following addition to your tracking crypto currency sheet, gives you immediate reflection of what the token is priced at the current ETH or BTC or USD level. Without further delay, here you have the next set of columns:
Ethereum to Utility Token transactions sheet

  1. The rate of purchase of utility tokens w.r.t. ETH (or BTC, as your specific case may be)
  2. The actual cost of token in USD (Auto-derived from column ‘E’, ‘K’ and ‘Q’)
  3. Numbers of tokens purchased (Auto-derived from column ‘N’ and ‘Q’)
  4. Number of outgoing tokens (if any)
  5. Balance utility tokens (Auto)
  6. Balance of utility tokens but value specified in USD (Auto)
  7. Cumulative portfolio value of all fiat/USD, crypto, alternate and tokens with you (Auto)

So, finally here is what it looks like for my crypto-portfolio, a place for everything and all things in one place:
Crypto Transaction sheet and portfolio
The best-part of this sheet are the columns:

  • (J, P & V) for individual USD values of each crypto
  • Column W for value of full portfolio at any day.
  • Column (L & R) to check the cost for the token in base-currency terms (USD etc)
  • A summation of Column (B): The total money you put into crypto over-time (plus how it is spread/diversified) and compare with ‘W’ portfolio value.

It really helped many times to get an overall view, and one specific time when:
I purchased a few tokens using ETH (exchanged via BTC), and I was trying to check how much my balance had gone down due to the purchase. To my surprise the value was instead showing an increase! A quick look at this sheet really help consolidate and reconcile the fact that the amount used to purchase the tokens was less than the amount of increase that happened in the BTC during this time. It would have otherwise brought in a lot of confusion if I started looking at individual wallets, their balance and old transactions one by one.

Reference Excel Sheet for download

You can download the excel sheet from here for reference and to build more functionality on top of it. You can add graphs or other currencies that you have invested into. (Note: The numbers in the excel are just EXAMPLES that you can change to track your own portfolio)
Hope it helps and you can give me further suggestions…

About the Author

Jagmeet Singh Hanspal is a Software Architect and has worked with various organizations like Ericsson, Juniper Networks, TranSwitch Semiconductors in the field of Telecommunications and Embedded Systems. His interests include Linux, Micro-controllers, Parallel Processing, Networks, Time Synchronization protocols, Data Visualization & Statistical analysis etc. You can connect with him on Linkedin
Get Email Updates
Free subscription to updates
We respect your privacy.

Tags: , , , , , , , , , , , ,

3 thoughts on “Tracking Crypto Currency”

  1. Nice site, It strikes a nice balance of the concept. I had a natural tendency towards ‘mindfulness’ from a young age. I am glad that I will definitely be coming back here more often. Wish I could add to the conversation. Thanks for sharing.
    forex trading signals

Guest Blogging

Write for us - with benefits!

error: Copyright Content. Please refer to content policy,
%d bloggers like this: