Coinbase

I recently started trading cryptocurrency and wanted to start with the more popular currencies before moving to the altcoins. Therefore, I set up a Coinbase account. Given the weekly trading limits, I knew I was going to be making several trades over a period of time, rather than just putting my money in all at once. This was going to be a real pain to track!

The Problem

I made several transactions over the course of a week and wanted to make sure that I had insight into overall profitability by coin since I could be investing in multiple coins. Hence, my Coinbase Tracker was born!

Coinbase Tracker

Cryptocurrency Investments Table

This spreadsheet has an input tab and a reporting dashboard so that I can see my position on a coin by coin basis. The input tab (bottom screenshot), allows me to input each of my investments as the transaction is made. I enter the coin that I purchased, the date I made the purchase, the total amount I invested (inclusive of fee), the purchase price, and the purchase amount for the transaction. Note: all information can easily be copy/pasted from the Coinbase receipt that gets emailed to you. The last column in this tab is the effective price. A fee is charged on every transaction. The amount is dependent on whether you used a credit card to make the transaction or if you used a bank account. In order for you to calculate your break-even price, you need to account for the price you paid for the coin plus the fee incurred. In my spreadsheet, I call this the effective price (the price paid if you take into account the fee). If you purchase your coin with a credit card (as I have in my examples), you can see the difference in purchase price and the effective price. This tells me that, even though I purchased Bitcoin at 10,060, I cannot sell it at 10,200 or I will not cover my transaction costs.

Dashboard

The next key component of my tracker is the dashboard. There are several components to this dashboard. The upper-left table shows my position in each coin. This table is entirely formula driven and requires no maintenance. It will aggregate the amount of each coin I have in my portfolio, the total amount I invested into each coin, the sell value (more on this later), and the profit as an amount and percent.

In the upper-right table, I have the ticker price of each coin. Coinbase has multiple prices for each coin, the buy price (how much it would cost to buy the coin), the sell price (how much you would get if you sold the coin), and the spot price (which is the average of the buy and sell price). If you were to log into Coinbase, they show you the spot price of the coin. I can't tell you how frustrated I get when I try to buy the coin, only to find out that the price ends up being higher than what I saw on the dashboard. Thus, when I calculate the sale value in the prior table, I use the sell price to determine the value, NOT the spot price which shows in the dashboard. This ensures a realistic picture of the portfolio. I used to manually update the pricing information, but found it to be too cumbersome. I would spend a lot of time trying to get all the prices, only for them to change multiple times. I knew there had to be a better way (and there was)! Starting in Excel 2013, you can issue a web service request in a formula. I now can have pricing updated at any point in the day. There is a catch to web service formulas in that they don't automatically refresh (even if you hit the calculate all button in Excel). As a result, I created a button that will refresh all prices. When you press the button, the last refresh time in cell A1 updates to tell you when the pricing was pulled.

Next, we have two charts to visualize the data. The one on the left shows profitability by coin. The bar will be green if the coin is profitable and red if the coin is losing money. The pie chart shows the portfolio breakdown by coin, so you can see if you are heavily invested in one coin vs. another.

What's Next

In addition to investing, I recently made a small investment in an Ethereum mining contract through HashFlare. I've been tracking my HashFlare payouts in a separate tab of the worksheet but am working on integrating it into the dashboard.

Feedback

I'd love to hear your thoughts on this, so please upvote if you like it, leave a comment, or share. If enough people are interested, I will make this available to everyone.

Thanks!