Crypto Portfolio Spreadsheet Tracker (Free Excel Template)

It can be tedious to keep track of your investment history, especially in crypto – given the pace of market changes, transaction frequency, and diverse portfolio assets necessitate an efficient and reliable tracking system.

In this article, we will cover how to create your own portfolio tracker in Excel using the CoinGecko API, allowing for simpler and quicker insights into your trade history. The template helps you manage your trade history, automate profit and loss calculations, and get a clear overview of your portfolio performance. You can connect the template using the official CoinGecko Excel add-in or set it up manually. When using the add-in, live =CG.PRICE() formulas fetch real-time prices for your holdings directly in Excel. To follow along and get started, you can download the free Excel template at the end of this article.

This guide is applicable for both macOS and Windows users.

Prerequisites

Before diving in, ensure you have the following ready:

• Microsoft Excel 2019 or later / Microsoft 365
  The template uses modern Excel functions and is fully compatible with both Windows and Mac.

• CoinGecko API Key
  A CoinGecko API key is required to fetch live crypto prices. The free Demo API key is sufficient to get started, while a Pro plan offers higher rate limits for more frequent data refreshes. You can follow our guide to get your free Demo API key.

• CoinGecko Excel Add-in (optional)
  For a quicker and more convenient setup, you can use the official CoinGecko Excel add-in to fetch live crypto prices directly into your spreadsheet using =CG.PRICE() formulas.

Note: This guide is applicable for both macOS and Windows users. To provide the best experience, this portfolio tracker is built with modern spreadsheet functions and is fully compatible with Excel 2019 or newer versions, including Microsoft 365, on both Windows and Mac.

Setting Up Your Excel Spreadsheet

The Excel document provided in this article is a base template that you can customize in any way you’d like. Here are some ideas to get you started, including how to customize the Dynamic tab.

• API ID (Symbol): The CoinGecko API ID of your traded cryptocurrency.
• Long/Short: Whether you bought or sold the asset. This determines how Position Profit/Loss is calculated.
• Entry Price, Quantity, and Open Date: The price, quantity, and date of your initial trade to enter the position
• Strategy: Whatever trading strategy you have utilized with this position
• Risk:Reward: Measure of your prospective reward given the risk you’ve undertaken
• Stop Loss: If you happen to have a stop loss currently placed
• Exit Date: Left blank if you are currently in the position
• Current Price: =INDEX(Data!B:B, MATCH(Symbol, Data!A:A, 0)) looks up the current price for the position’s symbol from the Data sheet. The Data sheet is populated using =CG.PRICE() with the CoinGecko Excel add-in, or through the Web Data template.
• Fees: Any gas fees or transaction costs involved
• Entry Position Size: Calculated as Quantity × Entry Price. Enter the Quantity and Entry Price manually, and the field is calculated using a formula that references those cells.
• Exit Position Size: Calculated automatically as Current Price × Quantity using the current price.
• Position Profit/Loss: =IF(ISBLANK(Exit Date), "", IF(Long/Short="Short", (Exit Position Size − Entry Position Size)×−1, IF(Long/Short="Long", Exit Position Size − Entry Position Size, ""))) calculates the profit or loss based on whether the position is Long or Short.
• Profit/Loss %: Position profit or loss expressed as a percentage.
• Status: =IF(ISBLANK(Exit Date), "Open", "Closed") automatically shows whether the position is Open or Closed based on the Exit Date.

There is space at the top of the Excel spreadsheet where you can add a range of metrics tailored to your portfolio.

All values shown in the template are for illustration purposes only and do not represent actual transaction data.

How to Use the Crypto Portfolio Tracker Excel Template

Option A: Using the CoinGecko Excel add-in

For a quicker setup, install the official CoinGecko Excel add-in and configure your CoinGecko API key by following the setup guide. Once connected, add each coin’s API ID to the Data sheet. The add-in’s =CG.PRICE() formulas automatically fetch live crypto prices, giving the Current Price formula above the data it needs to look up. Once the add-in status is Live, you can start customizing the template by adding the tokens you want to track in the section below.

Adding Coins to the Template

You can find a coin’s API ID on its CoinGecko page. Once you have the API ID, enter it in Column A of the Data sheet. The CoinGecko Excel add-in will then use the =CG.PRICE() formulas to automatically fetch the latest price data for your selected assets. For example, entering tether in Column A drives a formula like =CG.PRICE("tether") on the Data sheet, which returns USDT’s current price.

Logging Your Trades in the Dynamic Sheet

Once your coins are added, you can start recording your trades in the Dynamic Sheet. This is where you log every buy or sell, including your coin, position direction, entry details, strategy, and risk parameters. The template then automatically calculates the Exit Position Size and Position Profit/Loss, allowing you to track your performance and portfolio value accurately.

Analyzing Trades in the Dashboard Sheet

Once your trades are logged, head to the Dashboard sheet to review your performance. All charts and summary tables update automatically whenever new trades are added or live market data is refreshed, giving you an up-to-date view of your P&L, portfolio distribution, and asset allocation.

Option B: Getting 'Data from Web' Manually

How to Import Crypto Prices into Excel (for Windows Users)

The process to receive data from the web on Windows is quite simple:

1. Click the cell you would like the data to be imported into.
2. Select the Data Ribbon and go to 'From Web'.
3. Enter the URL as copied from the CoinGecko API documentation as shown below, and click 'Connect' to select and import web data.

For the endpoint request, you can specify which cryptocurrencies to return, separated by commas (e.g., ethereum, bitcoin, solana).

Once the parameters are added, you can find the corresponding URL in the curl request after you’ve selected ‘Try It’. This is shown in the orange box above. Simply copy this URL and place it into the 'From Web' request on Excel.

How to Import Crypto Prices into Excel (for Mac Users)

On a Mac operating system, there is no 'Data From Web' option on the data ribbon tab of your Microsoft Excel.

To circumvent this issue, we will take the URL from the CoinGecko API, as done before, and open TextEdit. 

1. The quickest way to do this would be to open your search bar by hitting “F4” and then typing in TextEdit.

2. After you have selected the application, paste the same URL found above in the CoinGecko API documentation. This will appear like below: 

3. Following this screenshot, select 'Format' and 'Make Plain Text'. This turns the document into the required format.

4. The final step before we can input our data into Excel is to save the document as a ‘.iqy’ file. This will allow us to run a mac Web query on Excel. To do this simply add ‘.iqy’ after the name of your file and unselect the box labeled “If no extension is provided use ‘txt’”. 

5. Return to your Excel spreadsheet and select ‘Data’ from the bar at the top of your screen, not the Data ribbon on excel (note that these are two different things).
   
   After selecting from the bar at the top of your screen, highlight ‘Get External Data’ and finally select ‘Run Web Query’. Navigate to the location where you saved your .iqy file and select it, then click Get Data. You will be prompted where to insert this data.

6. Place the queried data on a separate tab in the Excel template (titled 'Data'). Here you can input the Web Query and sift through to find the required price data. From the main portfolio page, we've written an Index/Match function to find the required price for a given cryptocurrency symbol. 

Creating a Dashboard for Portfolio Analysis in Excel

After logging your positions, the Dashboard sheet summarizes your portfolio using key metrics, charts, and asset allocation data.

The template uses these core Excel functions:

• SUMIFS: Calculates total position values and profit/loss
• INDEX + MATCH: Looks up current prices from the Data sheet
• IF: Determines position direction and open or closed status

These formulas calculate the key portfolio metrics:

• Exit Position Size: Current price × quantity
• Position Profit/Loss: Difference between Exit Position Size and Entry Position Size, adjusted for Long or Short positions
• Profit/Loss %: Position profit or loss as a percentage of the Entry Position Size
• Portfolio Distribution: Shows how your portfolio is distributed across asset categories

Together, these metrics power the Dashboard charts and summary tables, giving you a quick view of your portfolio performance and allocation.

Cross-Asset Allocation

The template can also track assets beyond crypto. Add the asset to the relevant Symbol column and enter its position details. For example, if you’re tracking an equity such as AAPL, add it to a new row and it will be included in your portfolio analysis.

You can also pull live stock prices into Excel and use the Dashboard to view your overall portfolio allocation across different asset types.

Conclusion

Managing a crypto portfolio across multiple assets and strategies can be complex. This free Crypto Portfolio Tracker Excel template, powered by the CoinGecko API, gives you a streamlined way to monitor your positions, track performance, and visualize your asset allocation in one place.

Whether you're managing a handful of positions or a diversified multi-asset portfolio, the template automatically calculates your P&L and keeps your dashboard updated with the latest market data. If you need more frequent data updates or higher rate limits, you can explore our paid API plans.

Keen to learn more? Check out our full list of crypto spreadsheet templates and API resources. We’ve also published guides on trading strategies, backtesting, and more.

Download Your Free Crypto Portfolio Tracker Spreadsheet

If you'd like to just grab the free template instead of creating your own, then you've come to the right place! Our template is already set up, so grab it below ⬇️