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 today's article, we will cover how to create your own portfolio tracker on Excel, allowing for simpler and quicker insights into your trade history. Streamline your trade history management, automate profit and loss calculations and enjoy a clear overview of your investment performance. We will also be utilizing the CoinGecko API to fetch real-time and historical price data for various cryptocurrencies, and get an accurate and up-to-date record of our investments.

This guide is applicable for both macOS and Windows users.

Pre-requisites

Before diving in, ensure you have the following ready:

• CoinGecko Demo API key
• Microsoft Excel

In order to get your Demo API key, first create an account via our pricing page. Thereafter, you'll be directed to the Developer Dashboard where you can generate your API key. We will be accessing the /coins/markets endpoint to obtain CoinGecko’s price for a list of cryptocurrencies on a specific date. This provides an aggregated price for your selected cryptocurrency, relative to any currency, on the date it was involved in a transaction. 

Setting Up Your Excel Spreadsheet

The Excel document provided in this article is a base template, which you can customize in any way you’d like. Here are some to start:

• Symbol: The symbol of your traded cryptocurrency
• Long/Short: Whether you have sold or bought that currency - this will alter the profit/loss column, which is calculated using the following formula: =IF(ISBLANK(“Not in a position”),"", IF(Position="Short",(Exit Size-Entry Size)*-1, IF(Position="Long", Entry Size-Exit Size, "")))
• 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: Uses the following formula to find the same symbol on the raw data page and the corresponding price, and will be discussed in more detail later on. =INDEX(Raw Data Page!Price Column:Price Column, MATCH(Symbol on current page, Raw Data Page!Symbol Column:Symbol Column, 0))
• Fees: Any gas fees or transaction costs involved
• Entry Position Size & Exit Position Size: Simply the quantity x price of the respective entry and exit trades
• Position P/L: The current profit and loss of a position given the current price of the cryptocurrency, or the price at which you exited the position
• Status: Closed/Open, following an IF statement: =IF(ISBLANK(J26), "Open", "Closed")

There is space at the top of the Excel spreadsheet where you can include an array of metrics, tailored to your own portfolio.

All the values displayed on the template are completely arbitrary and do not resemble actual transaction data.

Getting 'Data from Web' in Excel

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 the date of the requested data and the cryptocurrencies (separated by a comma: ethereum, bitcoin, solana, etc.).

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. 

Cross-Asset Allocation

Should your portfolio extend to other assets beyond crypto, you can simply add them into the respective ‘Symbol’ column. If you’re trading equities, say AAPL, simply add in the details of your position into a row (row 47 in the above sheet) and it will be incorporated into your portfolio analysis. In addition, you can pull live stock prices into your sheet. If you want to add an overview dashboard to your excel file, simply separate each asset, as shown below:

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 ⬇️

Note that you will still need to re-import the data using your own CoinGecko Demo API key, as all values are currently placeholders.

Whether you're using macOS or Windows, manage your trades more efficiently and gain a clearer insight into your portfolio's performance with a crypto portfolio tracker! Happy investing!

Keen to learn more and maximize your crypto data usage? Check out our full list of API resources – we've published guides on developing crypto trading strategies, backtesting and more!