Tracking your crypto trades can get complicated when prices move every second. This free crypto trading journal Excel template helps you stay disciplined by logging your trades, automatically calculating profit and loss (PnL), and analyzing your overall performance.

By integrating the CoinGecko API, this template retrieves real-time price data for a wide range of assets, from large-cap coins to newly launched on-chain tokens, directly into Excel. It’s designed for traders of all levels who need a structured, code-free solution to track and analyze trades with real-time market data. To follow along and get started, you can download the Excel template at the end of this article.

Note: To provide the best experience, this trading journal is built with modern spreadsheet functions and is fully compatible with Excel 2019 or newer versions, including Microsoft 365, on both Windows and Mac.

Prerequisites

Before you begin, ensure you have the following prerequisites such that the template functions correctly.

1. Microsoft Excel 2019 or later / Microsoft 365:
   The template utilizes Power Query to fetch real-time market data from CoinGecko API. This is a standard feature in modern Excel versions for both Windows and Mac.
2. 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.

How to Use the Crypto Trading Journal Excel Template

This section provides a step-by-step guide on how to set up the template. Follow these instructions to add your CoinGecko API key, customize your coin list, set up automatic data refreshes, and log your first trade.

Setting up the CoinGecko API Key in Excel

First, connect the template to your CoinGecko API key. To do this, open the “Queries & Connections” pane by navigating to the “Data” tab.

1. Select the Data > Queries & Connections.
2. Edit the specific ApiKey query based on your subscription level and paste your CoinGecko API key into the “Current Value” field.

3. The “BaseUrl” query already contains the Demo API URL. To use the Pro API, replace the existing URL with the Pro API URL:
   https://pro-api.coingecko.com
4. Click “Close & Load” to save your changes.

Adding Coins to the Template

The template tracks the top 250 coins by default. To track other assets, such as specific on-chain tokens, you must add them manually in the Add Coins sheet. The information required depends on whether the asset is listed on CoinGecko or is an on-chain token tracked on GeckoTerminal.

• For Coins found on CoinGecko:
  Set the Source to CoinGecko and enter its API ID. You can find the API ID on any individual coin page on the CoinGecko website.

• For Onchain Tokens:
  Set the Source to GeckoTerminal, select the Network ID (for example, eth  for Ethereum), and enter the Token Contract Address. To find the Network ID and Token Contract Address, head to the individual token page on GeckoTerminal:
  • The Network ID is found in the URL path (e.g., eth in https://www.geckoterminal.com/eth/pools/0xa43fe16908251ee70ef74718545e4fe6c5ccec9f)
  • Token Contract Address for the base token is listed in the pool details.

After adding assets, go to Data > Refresh All. New assets appear in AvailableCoins Sheet from row 252 onward. Your newly added assets should now appear in the AvailableCoins sheet, starting from row 252. You only need to add an asset once for it to be available for logging.

If a coin doesn’t appear, verify Source, CoinGecko ID, Network ID, and Token Contract Address, then refresh.

Logging Your Trades in the Trading Journal Sheet

Once your coins are added, you can start recording your trades in the Trading Journal Sheet. This is where you log every buy or sell to track your performance and portfolio value accurately.

To customize the dropdown options for trading categories, navigate to column “AF” in the Trading Journal sheet and edit the reference list. Using categories helps you analyze which trading strategies (e.g., ‘Scalping’, ‘Swing Trade’, ‘HODL’) are most profitable over time.

Analyzing Trades in the Dashboard

Once your trades are logged, head to the Portfolio Dashboard and Asset Analysis sheets to review your performance. Click “Data” > “Refresh All” to load the latest prices and market data for your coins. Subsequently, the dashboards will be updated automatically.

Portfolio Dashboard Sheet

This sheet provides a comprehensive overview of your trading portfolio and is split into two sections:

1. Portfolio Overview: Provides a snapshot of your overall portfolio health. It combines your realized profits, open position values, and asset allocations to show how your balance changes over time and where your capital is distributed across different coins.

• Balance Overview: Portfolio Trend based on Realised PnL and Position values.

• Current Holding’s Distribution: Portfolio distribution based on open positions’ values in USD.

• Traded Coins Analytics: A summary table listing all open and closed positions. Each coin is sorted by name with details such as position  value, position size, total trades, realized and unrealized PnL, and current trade status.

2. Performance Insights: Highlights overall trading performance.

• 
  A summary table showing total trades, win rate, average gain, average loss, and maximum drawdown.

• Balance Breakdown: Displays how each coin has contributed to your portfolio through realized profits and open position values.

• Trades Distribution: Shows the frequency of trades by category, helping you understand which trading styles you use most often.

Asset Analysis Sheet

This sheet allows you to perform a deep-dive analysis on a single asset. In cell “A2”, select a coin from the dropdown menu to populate the dashboard with its specific performance data.

1. Individual Asset Analysis

A breakdown on your performance for a specified asset.

• Total PnL: Combines realized and unrealized profit or loss for the selected coin. It reflects your overall performance from all trades involving that asset.

• Profit by Trading Style: Breaks down profits and losses based on your chosen trade categories. This helps identify which strategies are performing best.

• Trade Breakdown: Lists every trade for the selected coin, including details like date, price, quantity, and PnL. The final column allows space for reflections, short notes on what went right or wrong in each trade.

2. Asset Performance: Shows live metrics including the asset’s current price, market cap, and 24-hour change in market cap.

These dashboards provide both a high-level portfolio overview and in-depth performance insights for each asset, making it easy to track progress and refine your trading approach.

Creating a Crypto Trading Journal in Excel

Creating a crypto trading journal in Excel involves three core components: a data source for live prices, a structured log for recording trades, and a dashboard for analysis.

The template integrates these elements into a cohesive system:

1. Real-time Crypto Market Data: Integrate CoinGecko API to fetch the latest prices for a wide range of crypto assets, from large-cap coins to long-tail tokens directly into the Excel spreadsheet.
2. Excel-Based Formulas and Tables: Use Excel functions to automatically calculate key metrics such as position value, realized and unrealized PnL, and total portfolio balance. Pivot tables and charts display these results clearly on the dashboard pages.
3. Structured Trade Logging: A dedicated sheet to record all transaction details, including date, coin, action (buy/sell), quantity, price, and strategy category. This forms the foundation for all analysis.
4. Dashboards for Visualization: Use pivot tables and charts to create dashboards that summarize performance, asset allocation, and trading patterns visually for quick insights.

How to Get Crypto Data in Excel?

You can get real-time crypto data in Excel by using its built-in Power Query tool to fetch data directly from CoinGecko API.

1. Open Power Query: In Excel, go to the “Data” tab and select “From Web”.
2. Enter API URL: In the dialog box, enter the API request URL:
   • Demo API:
     https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=30&interval=daily&precision=full&x_cg_demo_api_key=YOUR_API_KEY
   • Pro API:
     https://pro-api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=30&interval=daily&precision=full&x_cg_pro_api_key=YOUR_API_KEY
3. Transform and Load: The Power Query Editor will open, allowing you to transform the JSON response into a table before loading it into your worksheet for download and export

For a detailed walkthrough with screenshots, read our full guide on how to import crypto prices into Excel.

The crypto trading journal Excel template is powered by a combination of real-time data from the following endpoints:

• /coins/markets 

Fetches the latest prices and market caps of the top 250 coins. 

• /onchain/networks/{network}/tokens/{address} 

Fetches the latest prices and market data of a specified token contract address on a specified network.

Creating a Crypto Trading Log Sheet in Excel

To calculate your portfolio performance, you first need a record of every trade. Creating a trading log sheet lets you record all buy and sell transactions in one place and automatically calculates profit, loss, and position value using live market data.

Each log entry includes:

• Date of the trade
• Coin name
• Action (Buy or Sell)
• Quantity (number of tokens)
• Average entry/exit price (USD)
• Category (speculation, momentum, news-based, etc.)
• Notes

Creating a Dashboard for Trade Analysis in Excel

After logging your trades, the next step is to create a dashboard to analyze your performance. A dashboard in Excel uses formulas to calculate key metrics and charts to visualize the data, giving you a clear overview of your trading activity.

To build an effective analysis dashboard, you will need to implement several key functions and calculation formulas:

Essential Excel Functions

• SUMIFS : Use this to calculate totals based on specific criteria, such as the total value of open positions for a particular coin or your total realized PnL.
• AVERAGEIFS : This is useful for finding the average entry price for your holdings or the average gain/loss per winning or losing trade.
• COUNTIFS : Use this to count the number of trades that meet certain conditions, which is essential for calculating your win rate.
• INDEX/MATCH: This combination is a powerful way to look up the current live price of a specific coin from your crypto market data feed and bring it into your calculations.

Calculation Logic

With the functions above, you can compute the primary metrics needed to assess your performance:

• Realised PnL: Taking the difference between the sell price and the average buy cost before the trade, then multiplying by the quantity sold.
• Unrealised PnL: The difference between the current live price and your buy price, multiplied by the quantity still held.
• Win Rate: The number of winning trades divided by total trades.
• Max Drawdown: Largest loss amount among all trades when sorted by PnL.
• Portfolio Value: The current value is a sum of your cash balance (from realized profits) and the market value of all your open positions.

Common Issues & Fixes

If you encounter errors or missing data while using the trading journal, here are a few common issues and how to resolve them quickly.

1. API Rate Limit (Error 429)

This happens when the free CoinGecko Demo API hits its request limit. Wait a few minutes before refreshing again, or switch to a Pro API key for uninterrupted updates.

If you receive other error codes, refer to the full list of CoinGecko API status codes to troubleshoot them.

2. Missing or Incorrect Coin Data: If a coin’s data doesn’t appear after refreshing:
   • Check that the Coin ID or Contract Address in the AddCoins sheet is correct.
   • Make sure the Source and Network ID fields are filled in properly.
   • Refresh again after fixing the entry.
3. Values Showing as “###”: This usually means the column width is too narrow to display large numbers. Simply widen the column to view the full value.

4. Anonymous Access / Privacy Level Error: If Excel displays a message like “Access to the resource is forbidden due to privacy levels” or “Using anonymous access”:
   • Go to “Data” > “ Get Data”  > “Data Source Settings”.
   • Select your CoinGecko API source and click “Edit Permissions”.
   • Under “Privacy Level”, set it to “Public”, then click “Save”.
   • Close and reload the queries.

If the problem persists, re-enter your API Key credentials.

Further Enhancements

You can further enhance this crypto trading journal spreadsheet to better suit your analysis style. Here are a few ideas that leverage other CoinGecko API endpoints:

• Fetch Historical Price Data

Pull Historical Prices for specific coins for more in-depth performance analysis. You can follow our guide on how to pull crypto historical data in Excel and visualize long-term trends.

• Add OHLC (Open, High, Low, Close) Data

If you want more granular price insights, consider fetching OHLC data for each coin. Check out our step-by-step guide on how to pull crypto OHLC data into Excel spreadsheet.

• Create a Crypto Tax Calculator

Extend your Trading Journal by integrating cost-basis tracking and realized profit summaries to estimate taxes. You can use our Crypto Tax Calculator Excel template as a reference for adding to your Crypto Trading Journal spreadsheet.

Conclusion

A trading journal is one of the most effective tools for improving discipline and decision-making as a crypto trader. This free crypto trading journal Excel template,powered by the CoinGecko API, provides a simple yet robust solution for logging trades, monitoring real-time prices, and analyzing your performance in a spreadsheet.

The dashboard offers a clear overview of your portfolio’s growth, while the asset analysis sheet helps you identify which strategies and coins deliver the best results. Whether you trade casually or actively, keeping a consistent record helps you refine your approach and make more data-driven decisions.

If you require more frequent data updates or higher API rate limits for advanced analysis, consider subscribing to a paid API plan to unlock the full potential of your trading journal.

Download Your Free Template ⬇️