How to Create Your Own Excel Crypto Tracker in 2024

Looking for an easy way to track your crypto portfolio? In this guide, we’ll break down everything you need to know to create a crypto tracker for Excel and Google Sheets. 

Can you track cryptocurrency on Excel? 

Yes. With platforms like Excel and Google Sheets, you can track your cryptocurrency holdings, monitor transactions, calculate profits and losses, and even visualize market trends using charts and graphs. 

If you have a Microsoft 365 subscription, you can use Excel to pull in the live price of cryptocurrencies like Bitcoin (BTC), Bitcoin Cash (BCH), Litecoin (LTC), Ripple (XRP), and Ethereum (ETH). 

If you’re looking to track the price of more cryptocurrencies, you can use an extension like Cryptosheets or pull data through an API connection from crypto price trackers like CoinMarketCap. 

How can I track my portfolio? 

Let’s walk through a few ways that you can keep track of your gains and losses from crypto.

Use an existing Excel crypto tracker template 

You can use a free existing template to create your Excel crypto tracker. Here’s an example template from Reddit! 

It’s important to remember that these pre-built templates have limitations. If you want to track the value of your own portfolio, you’ll need to manually input your cryptocurrency transactions — which means that keeping your spreadsheet up-to-date can take a lot of time and effort! 

Online profit calculators

If you're looking to calculate gains and losses from individual trades, you can try using a free online Crypto Profit Calculator. While these tools are very easy to use, they can't give you a comprehensive view of your crypto portfolio.

Use a portfolio tracker software

Crypto portfolio tracking software like CoinLedger can make it easier than ever to track the value of your holdings. CoinLedger can connect with your wallets and exchanges and give you a live, up-to-date look at your gains and losses across all of your cryptocurrencies — no manual entry required! 

The platform is completely free to use for portfolio tracking! There’s no need to enter your credit card information unless you wish to purchase a tax report. 

Get started for free today. 

How to create your own Excel crypto tracker 

Want to create your own Excel crypto tracker? While it can take time and effort, the process can be rewarding if you prefer to build your tracker from scratch. 

Here’s a step-by-step guide to creating an Excel crypto tracker. 

Step 1: Set Up Your Spreadsheet

• Open Microsoft Excel or Google Sheets, depending on your preference.
• Create a new spreadsheet and label your columns based on what metrics you wish to track. Include headers such as "Date," "Cryptocurrency," "Price," "Quantity," "Total Value," and any additional data you find important. 

Step 2: Fetch Live Price Data

• You can use Excel’s built in STOCKS functionality to track your cryptocurrency. However, if you wish to track coins outside of the ones supported by EXCEL, you’ll need to pull in data from a third-party source. 
• Choose a reliable cryptocurrency data source with an API, such as CoinGecko or  CoinMarketCap.  Alternatively, you can use an extension like Cryptosheets. 
• Register for an API key if required, and review the documentation to understand how to fetch data.
• In Excel, use the "DATA" tab and select ‘From Web’ to import data from the API. Follow the prompts to connect to the API and retrieve live price data for your chosen cryptocurrencies.

Step 3: Input Your Crypto Transactions 

• If you wish to track the value of your own portfolio, you’ll need to input relevant information for each of your transactions — such as the name and amount of each cryptocurrency that you hold. 
• Use Excel formulas to calculate your portfolio's total value, percentage change, and other relevant metrics.

Step 4: Generate Cryptocurrency Price Charts

• Click on the "INSERT’ tab, then ‘Recommended Charts’. Choose a chart type, such as a line chart, area chart, or candlestick chart.
• Select the data range you want to include in your chart, including dates and corresponding cryptocurrency prices.
• Customize the chart's appearance, labels, and axis titles for clarity.

Step 5: Set Up Data Refresh

• You may need to set up automatic data refresh. This ensures that your spreadsheet reflects the latest cryptocurrency prices.
• In Excel, go to the "DATA" tab, choose "Queries & Connections," and enable automatic refresh.

Step 6: Regularly Update and Analyze

• Periodically update your spreadsheet. You should update your crypto transactions and refresh data to ensure accurate and up-to-date information.

Once you’re done, you’ll have your own Excel crypto tracker!