This post will show you how to create a Crypto Portfolio Tracker in Google Sheets. With this Google Sheet, you can track your cryptocurrency purchases. And also, you can view a summary of your total assets with the Dollar Cost Average (DCA) for each cryptocurrency. In addition to that, you can get the present value of cryptocurrencies with a single click. So, you can directly see the present value of your crypto assets.
Table of Contents
Benefits of using Google Sheets to track Crypto Portfolio
There are several benefits in using Google Sheets to track your cryptocurrency portfolio.
Google Sheets can be accessed from any device and anywhere, so your portfolio too. Most importantly, it is free and comes with your Google Account.
With Google Apps Script, you can get public and your private data from various Cryptocurrency platforms to Google Sheets using their APIs.
You can do your own analysis in the Google Sheets itself.
How to create a crypto portfolio tracker in Google Sheets
This article aims to show you how to create a simple crypto portfolio tracker in Google Sheets. With this Sheet, you can,
- Add entries for your cryptocurrency purchases
- View a summary of all cryptocurrencies you are holding
- View your total investment
- Get the current price of each cryptocurrency from CoinMarketCap with a single click
- View the DCA of each cryptocurrency
- View the DCA only for selected entries of each cryptocurrency
- View the present value of your crypto portfolio
- View the present value by cryptocurrency type
The following video demonstrates the features of this crypto tracker.
What do you need to create this Google Sheets Crypto Tracker
If you want to use the below Google Sheet as it is, you only need to have a Google Account and CoinMarketCap free account.
But, having some knowledge of Google Sheets and Google Apps Script may help you customize the Google Sheets according to your requirement.
Copy the Google Sheet to your Drive
First, make a copy of the Google Sheet from the link given below.
Create a Free CoinMarketCap account, get the API key, and Add it to Google Sheet
According to them, “CoinMarketCap is the world’s most-referenced price-tracking website for crypto assets in the rapidly growing cryptocurrency space. Its mission is to make crypto discoverable and efficient globally by empowering retail users with unbiased, high quality, and accurate information for drawing their own informed conclusions.”
With CoinMarketCap free API, you can get accurate real-time or historical cryptocurrency prices to your Google Sheets.
First, create a CoinMarketCap account from this link.
Once you log in to the account, you can see your API key. Hover over and click “COPY KEY” to copy the API key.
In order to get crypto price updates, you need to add your API key to the Google Sheets.
Paste the API key in cell B3 of the Google Sheet. When you click the “Get Quotes” button, the Apps Script will look for the API key in this cell.
Getting crypto price updates to Google Sheets with Google Apps Script
The Google Sheet you have already copied to your Google Drive contains the following code snippet.
To open the code file (the Apps Script editor), go to Extension > Apps Script. This script looks for the crypto symbols given in the range B7:B and obtains the price updates for that currencies from the CoinMarketCap website.
I have assigned this getCryptoUpdates() function to the “Get Quotes” button in the Google Sheet.
To retrieve price updates from CoinMarketCap,
- In cell A7 add your cryptocurrency symbol (Ex. BTC, ETH, BNB)
- Then click “Get Quotes” button. Once you click the button for the first time, it will ask to grant your permission to run the Apps Script in your Google Account. Watch the video below and follow the steps to grant permission. Then click the “Get Quotes“button again.
- You can add more coins in the collumn A, after the cell A7, and click the “Get Quotes” button to get price updates. (! make sure to maintain one blank row between transaction table and this criptocurrency list)
- You should click the “Get Quotes” button everytime you need to see the latest price of the cryptocurrencies. You have 333 free API calls per day for your free CoinMarketCap account.
How to use this Google Sheet Crypto Portfolio Tracker
This Google Sheet is constructed to add a record for each of your cryptocurrency purchases.
Add a new cryptocurrency to your portfolio
When you buy a new cryptocurrency type, you need to add it to the table Left to the “Get Quotes” button. And should run the “Get Quotes” button to get the latest price of that coin.
Add crypto purchase records
You can add your purchase records to the table at the bottom of the Sheet (From row 19 onwards).
You need to add the transaction date, coin symbol, investment amount, and the buying price.
View the summary of your cryptocurrency portfolio
The summary table is located on the top right side of the Sheet. In this table, you can view the total investment, total investment in each currency type, their present value, DCA, and the profit or loss.
You can also view the summary for selected records. To do that, first, select the checkbox of each record you want to view the summary. Then, select the checkbox in cell “I1“.
The summary table is generated from Google Sheet’s QUERY function in cell D2.
Remove crypto purchase record
If you have sold some amount of cryptocurrencies, you need to deduct it from your portfolio.
If you have sold an equal amount to a previous purchase record, you can remove that previous record from the transaction table.
However, if you consider DCA and sell only a certain amount of a coin, you can delete all the previous records for that coin and create a new record with the price you sold for the balance amount.
Wrapping Up
Using a Google Sheet to track your cryptocurrency portfolio has several advantages. You can add records of cryptocurrency purchases from several platforms in one Google Sheet. And also, you can do your analysis with it. You can also use Google Apps Script to build several custom features to it. Most importantly, all these features are free.
This article will help you create your own crypto portfolio tracker in Google Sheets. You can also grab present crypto prices from CoinMarketCap to this Sheet with a single click. You can copy this Google Sheet with the Google Apps Script to your Google Drive using the link provided and start using it immediately.
