How to Create an Excel Crypto Tracker

Looking for a simple method to manage your crypto investments? In this guide, we’ll delve into the process of creating a crypto tracker using Excel or Google Sheets in 2024, as well as other alternatives like free portfolio trackers.

Can you track crypto with Excel?

Yes. Platforms like Microsoft Excel and Google Sheets allow you to monitor your crypto holdings, track transactions, compute profits and losses, and visualize market trends using various charts and graphs.

If you’re a Microsoft 365 subscriber, you can utilize Excel to fetch real-time prices of cryptocurrencies such as Bitcoin, Ethereum, Solana, and more.

For tracking the prices of additional cryptocurrencies, you can employ extensions like Cryptosheets or pull data via API connections from crypto price tracking services like CoinMarketCap. Cryptosheets works with both Excel and Google Sheets.

How can you track your crypto portfolio?

Here are several methods to keep track of your crypto portfolio:

Use an existing Excel crypto tracker template

You can use free existing templates to establish your Excel crypto tracker. For example, you can find templates on platforms like Reddit.

It’s essential to note that these templates come with limitations. If you want to track your own portfolio's value, you’ll need to manually input your crypto transactions, which can be time-consuming.

Online crypto profit calculators

If you want to calculate gains and losses from individual trades, you can try using free online crypto profit calculators. However, these tools are often very simple and may not offer a comprehensive view of your entire crypto portfolio.

1. CoinMarketCap Calculator
   Allows users to calculate potential profits or losses by entering the amount of cryptocurrency bought, the purchase price, and the selling price.

2. CryptoCompare Calculator
   Offers a variety of calculators, including mining profitability calculators and investment calculators, which can be useful for both beginners and experienced traders.

3. CoinGecko Calculator
   Provides a simple and easy-to-use calculator that helps users determine the potential return on investment (ROI) for various cryptocurrencies.

4. TradingView Calculator
   Primarily known for its charting tools, TradingView also offers calculators that help traders estimate profits and plan their trades accordingly.

5. Blockchain.com Calculator
   Can be used to calculate the potential profit from Bitcoin investments depending on the buying and selling price.

6. WhatToMine
   Is a calculator that can help you figure out how much profit you can make from mining various cryptocurrencies based on your hardware and electricity costs.

Each of these calculators offers different features and levels of detail, so it might be useful to explore a few to find one that meets your specific needs.

Use portfolio tracking software

Crypto portfolio tracking software simplifies the process of monitoring the value of your holdings. Koinly is an example of a portfolio tracking tool that is dedicated to cryptocurrency. Koinly can sync with your wallets and exchanges, providing you with a live, updated overview of your gains and losses across all your cryptocurrencies, without requiring manual data entry.

Koinly offers free crypto portfolio tracking, with no need to provide credit card information unless you opt to purchase a tax report.

Along with Koinly, here are some well-regarded cryptocurrency portfolio trackers that can help you monitor and manage your investments across various platforms:

1. CoinMarketCap
   Known for its extensive data on cryptocurrency prices, CoinMarketCap also includes a portfolio feature that allows users to track their holdings and see performance over time.

2. CoinGecko
   Provides a straightforward approach to tracking your crypto holdings and also offers data on a wide array of coins and tokens.

3. CryptoCompare
   Offers detailed portfolio tracking and management tools, allowing users to monitor their crypto holdings, transactions, and overall investment performance.

How to create an Excel crypto tracker

If you prefer building your tracker from scratch, here’s a step-by-step guide:

Step 1: Setting up your spreadsheet

Open Microsoft Excel or Google Sheets.

Create a new spreadsheet and label your columns with metrics you want to track, such as "Date", "Cryptocurrency", "Price", "Quantity", and "Total Value".

Step 2: Fetching live price data

Utilize Excel’s built-in STOCKS functionality to track cryptocurrencies supported by Excel. For other coins, fetch data from a reliable cryptocurrency data source with an API, such as CoinGecko or CoinMarketCap. Alternatively, use an extension like Cryptosheets.

Register for an API key if necessary, and follow the documentation to fetch data.

In Excel, use the "DATA" tab and select ‘From Web’ to import data from the API, following the prompts to connect and retrieve live price data.

Step 3: Inputting your crypto transactions

For tracking your portfolio's value, input relevant transaction information such as the cryptocurrency name and amount held.

Utilize Excel formulas to calculate your portfolio's total value, percentage change, and other relevant metrics.

Step 4: Generating cryptocurrency price charts

Click on the "INSERT" tab, then ‘Recommended Charts’.

Choose a chart type and select the data range, including dates and corresponding cryptocurrency prices.

Customize the chart's appearance, labels, and axis titles for clarity.

Step 5: Setting up data refresh

Ensure your spreadsheet reflects the latest cryptocurrency prices by setting up automatic data refresh.

In Excel, go to the "DATA" tab, choose "Queries & Connections," and enable automatic refresh.

Step 6: Regularly updating and analyzing

Update your spreadsheet as often as necessary by refreshing data and inputting new transactions to maintain accuracy.

You're done - your Excel crypto tracker is ready to use!

Save time with a free crypto portfolio tracker

A free crypto portfolio tracker like Koinly can save you hours on spreadsheets. With Koinly, all you need to do is add the exchanges, wallets, and blockchains you use, either via API or by uploading a CSV file and Koinly will automatically import your transaction history and calculate your gains, losses, income, and more. As well as this, Koinly provides live price feeds on your holdings, as well as key data like ROI, balance, cost basis, and more. Best of all, you can use Koinly completely free of charge (up to 10,000 transactions). You'll only ever pay if you want to download a tax report.