The crypto industry is booming, with market trends constantly changing. As a crypto investor, you always have to know the most up-to-date information in the most organized fashion possible.
There are various great crypto portfolio trackers out there that can help you stay updated. But for most of them, there’s one major downside: they come with a price tag. Their price tag reduces their appeal so much that most people don’t even consider them. This is where Google Sheets comes in.
Google Sheets provides an excellent alternative to paid options. You can track your crypto portfolio with the easy-to-use interface of Google Sheets. You can make use of basic as well as advanced level tracking features to stay in the loop. It doesn’t have any unnecessary complexities, which makes it less confusing to get started with, even for beginners.
In this article, I will discuss everything you need to know on how to track your crypto portfolio with Google Sheets. I will try to list every essential detail, from its perks and uses to the templates you can use. I will also provide a step-by-step guide showing you how to create a crypto tracking spreadsheet on your own.
Table of Contents
Perks of Using a Crypto Portfolio Tracker in Google Sheets
Some of the main reasons why I suggest you choose a Google Sheet crypto portfolio tracker are:
- Google Sheets offers an intuitive dashboard view and is able to store unlimited trades, withdrawals, etc.
- With Google Sheets, you’re able to create various reports, including profit & loss, coin location, coin performance, etc.
- You can use the existing Google Sheets features that you love with your portfolio and reports. i.e., creating a table for better viewing or a chart to understand data more quickly.
- You can use existing extensions of Google Sheets with your crypto portfolio spreadsheet. Making you feel right at home.
- You can make use of easy import options, including manual copy/paste, which is compatible with many applications. You also have additional import options available for your convenience.
- With Google Sheets, your data is privately held in your drive. You can choose to restrict access to only yourself or share with anyone you want.
- You have maximum freedom with your data in Google Sheets. You make any changes you want. For example, adding/removing columns, rows, etc. You can also toggle with the source code as it is an open source.
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.
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:
=if(isblank(cell_number_with_identifier),"",index(IMPORTHTML(CONCATENATE("price_source",url_slug),"table",1),1,2)).
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.
I suggest you practice the parts of the crypto tracking spreadsheet you find most complex after reading this article. It helps you grasp the concept better and clear out any confusion. I hope this article and our free templates were of help to you!
Frequently Asked Questions
Does Google Sheets support crypto?
Yes, Google Sheets does support crypto tracking. I find it to be one of the most reliable and intuitive options out there for tracking cryptocurrency portfolios for everyone. It also incorporates an option to convert currencies, which makes tracking very convenient for users based anywhere in the world.
Does Google Finance work for crypto?
The Google Finance formula can only pull two cryptocurrencies at the moment, which are Bitcoin and Ethereum. This means that you can use it for a Bitcoin spreadsheet or a cryptocurrency day trading spreadsheet for Ethereum only. If you have invested in other cryptocurrencies, you cannot use this formula to track their prices.
How do you keep track of a crypto portfolio?
Tracking a crypto portfolio in Google Sheets is simple once you get going, but it may take some practice to get going. In this article, I have provided two templates for you to get started. I have tried to make them as simplified as possible.
The first template allows you to keep track of all your cryptocurrency holdings by manually entering the amount you own and then importing the currency’s current price from another source. It can be used to keep track of your holdings, their prices, and the total value of your holdings.
The second template is a little more technical, relying on Google Apps Script and CoinMarketCap to keep track of all Crypto Updates.
Related:
- Free Google Sheets Profit and Loss Template
- Free Google Sheets Sales Templates (& How to Use)
- The Free Google Sheets Task List Template [Easy Guide]
- The GOOGLEFINANCE Function: An Easy Beginner’s Guide
- Google Sheets Compound Interest:Â How to Calculate [Easy Formula]
- An Easy Guide to Google Sheets Currency Conversion
- Top 22 Google Sheets Tips and Tricks to Learn
