Free Crypto Portfolio Tracker Templates for Google Sheets

You can create a few different types of sheets on Google Sheets to keep track of crypto-related information, depending on your needs.

Download our Simple Crypto Portfolio Tracker Template by clicking on File > Make a Copy.

This first template is a pretty basic one. It allows you to track all your crypto holdings by manually entering the quantity you’re holding and then importing the current price of the currency from another source. You can use it to keep track of your holdings as well as their prices and the total value of your holdings. The prices update every 15 minutes.

Download our Advanced Crypto Portfolio Tracker Template by clicking on File > Make a Copy.

This second Google Sheets crypto tracker template is slightly more complex and uses Google Apps Script and CoinMarketCap to keep track of all Crypto Updates.

In addition, you can add various other functionalities to your Google Sheets crypto spreadsheet. Such as connecting your crypto wallet to your spreadsheet for cryptocurrency to keep track of your purchase records in real-time and adding an auto refresh formula, among other things.

7.11.2024. I have constructed a diversified cryptocurrency portfolio by carefully selecting 20 potential tokens, investing an initial $100 in each. This strategy aims to mitigate risk while capitalizing on growth opportunities across various sectors within the blockchain industry, such as decentralized finance, NFT markets, payment solutions, smart contract platforms, and more. By balancing my holdings, I intend to hold these tokens for the long term, hoping for steady appreciation amidst the volatility of the cryptocurrency market. This portfolio reflects my confidence in the future of blockchain technology and my thoughtful approach to asset security and growth.

Breaking it Down

The first crypto portfolio tracker is the best compared to other methods. It uses the ImportHtml formula to get price updates.

Although you get a pre-written source code, using Google Apps Script is not for everyone. This is because Google Apps Script makes customization a lot more complicated than regular Google Sheets customization since it requires some level of programming. Besides, ImportHtml does the job just the same, and if you only intend to get price updates, it is definitely the way to go.

Google Sheets Crypto Portfolio Tracker: Step-by-Step Guide

Creating the two templates above is similar. The essential information you need is the name of the currency, the currency identifier, the investment you made, and the buying price. So, let’s take the first example and create a Google Sheets Crypto Portfolio Tracker.

Step 1. Create a custom table to keep track of your portfolio.

I have added the following headers:

Currency: To specify the currency.

Currency Identifier: This is the column with the URL slugs that I will use to search for the current price.

Investment: This is the column where I will enter the amount I invested (in US dollars).

Buying Price: This is the column where I will enter the buying price of the cryptocurrency.

Holding: This is the column that will determine the total currency amount held.

Current Price: This column will specify the current price.

Present Value: This column will calculate the value of my holdings based on the current price.

Profit/Loss: This column will determine how much I have gained or lost when comparing my buying price to the current price.

Step 2. Fill in your essential information (currency name and identifier) and purchase records (investment amount and buying price).

Step 3. For the current price column, use the ImportHtml formula to pull the price corresponding to the currency.

In the table above, I have used the formula:=GOOGLEFINANCE("CURRENCY:ADAUSD") 

This formula searches in the defined cell and pulls the corresponding price from the price source (CoinmarketCap in this case). You must remember to use the URL slugs (entered in the currency identifier column) from CoinMarketCap URLs for different currencies, such as ethereum for Ethereum, bitcoin for Bitcoin, and usd-coin for USD coin.

Step 4. Use formulas to automate the calculation for the holdings, present value, and profit/loss columns.

Holdings

=DIVIDE(C3,SUBSTITUTE(E3,"$", " "))

Using the above formula, I am dividing the investment amount (starting from cell C3) into the buying price (starting from cell E3). I have also used the substitute formula to ignore the “$” sign in the buying price column. I can simply drag the formula down to apply it to different currencies.

Present Value

=MULTIPLY(SUBSTITUTE(F3,"$",""),D3)

Using the above formula, I am multiplying my current price with my holding to determine the present value of my investment. Also, I am using the substitute formula again to ignore the “$” sign in the current price.

Profit/Loss

=MINUS(G3,C3)

Using the above formula, I am deducting the present value from my investment to determine the amount I have gained or lost on my investment.

Adding and Removing Purchase Records

Adding

Adding new records in the cryptocurrency spreadsheet tracker is pretty simple. Enter the essential information (currency name and currency identifier) and your purchase records (investment and buying price), and simply drag down all the other columns (holding, current price, present value, and profit/loss).

Removing

If you wish to remove purchase records, you can simply do that by editing the records from the table.

If you have sold a partial amount of a cryptocurrency, you need to deduct that from your crypto trading spreadsheet.

If you have sold the entire amount entered in a previous purchase record, you can remove the entire record from the table.

You can erase all prior records for a cryptocurrency if you consider DCA and only sell a portion of it, and then you can create a new record with the updated price.

Conclusion

As elaborated above, Google Sheets is one of the most amazing tools to keep track of your crypto portfolio. Using Google Sheets, crypto prices can be tracked easily and free of cost.

⬅️ 1️⃣ 2️⃣ 3️⃣ 4️⃣ 5️⃣ 6️⃣ 7️⃣ 8️⃣ 9️⃣ 🔟 ➡️