How to track performance when investing in Bitcoin, Ethereum, and other cryptoassets with Google Sheet for free (1/2)
[Update 02/02/2018]: Please find the 2nd part of this post here.
By now, I’m quite sure that the interest in the crypto space has spread to your friends and even your parents. The crazy uptake in Bitcoin, Ethereum, and other cryptoasset prices has driven a major inflow of attention from tech and mainstream media. Since December last year, the value appreciation increases have been astronomical: Bitcoin rose by 1654%, Ethereum rose by 4635%, and Litecoin rose by 2346%. (Coinbase, 12/07/2017, 10:30pm CET)
If you’re among many who have brought up this topic during your Thanksgiving dinner, you might wonder how to get started with investing in cryptoassets like Bitcoin and Ethereum. For the theory, I can highly recommend the book “Cryptoassets” by cburniske and Jake Tatar who do a great job in explaning the core concepts in the crypto world and sharing their experience with investing in this space. In practice, you will need to open an account with one of the wallets or exchanges online. Coinbase is probably one of the most well known beginner-friendly providers that can get you started with your first $10-$1000 investments in Bitcoin, Ethereum, or Litecoin. If you don’t have an account yet and want to open one, use this referral link so we both get $10 :)
Once you are deeper into this, I’d recommend everyone to store your own private key and get independent from centralized providers like Coinbase, though (for security, privacy, and many more reasons). Since this article targets beginners, I won’t go into detail here and will stick to Coinbase for an easy start.
I got approached by my friend Diego early last week who found incredibly painful that there is no easy and elegant way to track your investment performance on Coinbase (see Whatsapp message from Monday). Coinbase is not very helpful with bringing ROI visibility.
Usually, exchanges give you insights into your gains and losses of your portfolio. Investors and traders alike want to see how they do in comparison to the market. Even for long-term investors with lower transaction frequency, tracking your portfolio performance is difficult and there are not many free simple tools out there for beginners to start. The Blockfolio app is a good resource for tracking and there are more and more alternatives popping up on ProductHunt each week. However, they have limited functionality and are not very useful. I’d prefer being able to track, analyze, and interact with the data, and do so on a computer screen instead of a smartphone screen. Also, I’d like not to give my data to any (centralized) 3rd party players if it can be avoided.
I have built my own simple solution for that as an alternative which I’d like to share with you. It automatically pulls current prices via the Coinmarketcap API and also exchange rates among cryptoasset pairs via the Cryptocompare API. It shows you the 1h, 24h, and 7 day change in prices and displays your current portfolio allocation among the assets you invested in (similiar to what some wallets such as Exodus do) so you can balance your portfolio with regular frequencies e.g. every 6 months to achieve your desired allocation.
-> You can find my Crypto Investment Performance Tracking Sheet here.
That is an advanced version in case you want to invest in more cryptoassets (beyond Coinbase). You can find a simpler version here that only covers BTC, ETH, and LTC.
Step by step instructions:
- Go on my link to access the Google Sheet. Click on file > make a copy to create your own sheet that you can edit.
- You might need to enable your Google Sheet to run a) the cryptofinance() function and b) the importjson() functions. See instructions for a) here and for b) here.
- You don’t need to change anything on the sheet “Crypto Portfolio” besides the names of the cryptoassets you want to track (needs to be the same as in the sheet “Crypto Trades”). Enter your recent investments, trades, or crypto-to-crypto exchanges in the sheet “Crypto Trades” like in the examples given.
- Add a new row per deposit (transfering money to the wallet or exchange) by entering the data in columns G& H. The grey fields are formulas while the white fields are for your inputs.
- For each transaction, add a new row. Only enter the volumes for the 2 currencies (fiat or crypto) that were affected by the transaction (columns I-P). The exchange rates (columns C & D) will automatically be calculated.
- You can find your token balance on row 2 in this sheet and also on the “Crypto Portfolio” sheet.
- The values and charts on the “Crypto Portfolio” sheet should automatically update once you refresh (Ctrl + F5).
Please note that the Cryptocompare API calls sometimes fail due to high request volume so some fields will show “#VALUE” until you refresh. Best would be for you to copy and paste only the values (Ctrl+shift+v) into the fields for the rows where the API requests were successful so that your sheet won’t need to request everything each time you open it.
It’s a very basic tracking sheet but it can hopefully be helpful for traders and hodlers alike. I hope this is useful to you. Please share your feedback with me if you think the sheet can be improved or if you know better free APIs to get the data in. For example, I’d be interested in an API that gets me an x day moving avg for the price/exchange rate.
If you think this was useful please share your love with a tweet or a like on Medium. Of course, I’d also be happy with a small donation (BTC: 18APi2Nk1p5L7iEmJM7aG5h6GUqxvWEUS7 & ETH: 0x0356fd48514D2EE84B13f06D741B62DEed351e23) :)
Subscribe to my Refind if you want to stay up to date with interesting articles & resources I find in this space.
About the author Ha Duong: I am an Investment Manager at Project A Ventures, a venture capital company based in Berlin. I’m excited about the economical as well as the societal possibilities that blockchain technology brings with it.
A big thank you to Stefano, Max, Gauthier, Bosse, Alexander, Wendy, and Andreas for your feedback on the article and tracking sheet. You’re all awesome!
