This free Excel crypto portfolio tracker gives serious multi-venue traders a single source of truth for CEX trades, DEX swaps, DeFi positions, gas fees, staking rewards, and capital gains — all in one spreadsheet. Download it to replace the scattered mix of exchange dashboards, DeFi dashboards, and tax exports most crypto traders are currently using.
What’s Included
- Trade Log tab — Columns for date, exchange, trading pair, buy/sell type, entry price, exit price, quantity, fee in USD, and auto-calculated net P&L. Supports both CEX trades (Binance, Coinbase, Kraken, Bybit) and DEX swaps (Uniswap, dYdX) with a consistent format.
- Portfolio Snapshot tab — Current holdings per exchange with cost basis calculated using either FIFO or AVCO, toggled from the Settings tab. Shows unrealized P&L per position and total portfolio value.
- DeFi tab — LP position tracker with an impermanent loss calculator (using the formula IL% = 2·√(price_ratio)/(1+price_ratio)−1, where price_ratio = current_price / entry_price), staking rewards log with daily and monthly accrual, and a yield farming APY tracker.
- Gas Fee Ledger — Dedicated tab for ETH, SOL, and other on-chain transaction fees, logged by network, transaction type, native token amount, and USD value at time of transaction. Bridging costs between L1 and L2 chains are tracked separately from swap fees.
- Capital Gains tab — Reads from the Trade Log and categorizes each closed position as short-term (365 days or under) or long-term (over 365 days), aligned with IRS Notice 2014-21 treatment of crypto as property.
- Stablecoin Yield Tracker — Rows for USDC and USDT lending on Aave, Compound, and CEX Earn products, with accrued interest calculated from your entered principal and APY.
- Portfolio Rebalancing View — Enter target allocation percentages per coin or sector; the sheet compares them to actual allocations and flags positions that have drifted beyond a configurable threshold (default: 5%).
How to Use
Step 1: Set Up Your Exchange List
Open the Settings tab and enter your active exchanges in cells B2:B10 — for example, Coinbase, Binance, Kraken, Bybit, and Uniswap. This populates the Exchange dropdown on the Trade Log, ensuring consistent labels across all pivot tables and filters. If you trade on DEXes, add your wallet address in column C to distinguish on-chain activity from CEX trades.
Step 2: Choose Your Cost Basis Method
In cell D2 of the Settings tab, select FIFO or AVCO. FIFO (first-in, first-out) is the IRS default and aligns with how brokers must report under the Form 1099-DA rules effective 2025 — if you’re building documentation for a potential audit, FIFO is the safer choice. AVCO (average cost) smooths out cost basis across all lots and can reduce record-keeping complexity for traders with many small buys.
Step 3: Log Trades in the Trade Log Tab
Enter each trade across columns A through J: Date, Exchange, Pair (e.g., ETH/USDC), Type (Buy, Sell, or Swap), Entry Price, Exit Price, Quantity, Fee in USD, Net P&L (auto-calculated in column I), and Notes. For DEX swaps, enter the gas cost in the Fee column and mirror that amount in the Gas Fee Ledger tab — the ledger uses a Linked Trade ID column (column F) to avoid double-counting fees in your P&L.
Step 4: Record DeFi Positions
In the DeFi tab, enter each LP or staking position: Protocol, Pair or Asset, Entry Date, Entry Price, Quantity, and Current Pool Ratio. The impermanent loss calculator in columns I through K updates when you enter the current price in column H. For staking, log rewards in the adjacent rows using the daily accrual rate — the monthly total in column N rolls up automatically. Impermanent loss on Uniswap V3 concentrated positions can reach 5-20% when price moves outside the set range, so checking this tab weekly matters on active positions.
Step 5: Track Gas Fees as a Separate Cost Center
Use the Gas Fee Ledger to record every on-chain transaction: Network, Transaction Type (Swap, Bridge, Claim, Stake, Deploy), Gas Paid in the native token, USD Value at time, and Linked Trade ID. Gas fees on Ethereum mainnet average $2-$15 per swap under normal conditions, spiking to $50-$200 or more during high-congestion events. A stablecoin lending position earning 4.2% APY on $2,000 USDC generates roughly $84/year — but if gas cost $18 to enter the position and $12 to claim rewards ($30 total), the effective APY on deployed capital drops to approximately 2.7% in the first year ($54 net / $2,000 principal). On a smaller $500 position that same $30 in gas nearly wipes out the first year of yield entirely.
Step 6: Review Capital Gains and Rebalancing
The Capital Gains tab reads from the Trade Log and flags each closed position as short-term or long-term based on the holding period. Filter by tax year using the dropdown in cell B1 before exporting for your accountant. The Rebalancing View on the Portfolio Snapshot tab compares your target allocation percentages to actual positions and highlights any coin or sector that has drifted beyond your set threshold.
Key Benefits
- Single ledger for all venues — manage positions on Binance, Coinbase, Kraken, Bybit, Uniswap, and dYdX without juggling separate files or dashboards
- Gas costs surfaced, not hidden — Ethereum gas fees logged as a standalone cost center give a true picture of DeFi profitability on smaller positions
- Impermanent loss quantified — the DeFi tab calculates IL in real time for Uniswap V3 concentrated liquidity positions, where losses of 5-20% are possible in volatile markets
- Tax-ready structure — FIFO/AVCO toggle and short-term vs. long-term gain categorization align with IRS Notice 2014-21 and Form 1099-DA self-documentation requirements
- Rebalancing built in — target vs. actual allocation comparison prevents portfolio drift from going unnoticed across a multi-asset crypto book
Template vs JournalPlus App
| Feature | This Template | JournalPlus App |
|---|---|---|
| Trade Import | Manual entry | Automatic from 50+ brokers and exchanges |
| Multi-Exchange Support | Manual dropdown, any exchange | Native API connections to major CEXes |
| DeFi / LP Tracking | Manual entry, built-in IL calculator | Automated LP sync via wallet connection |
| Gas Fee Tracking | Separate ledger, manually entered | On-chain fees pulled per transaction |
| Capital Gains Report | FIFO/AVCO toggle, manual tax-year filter | Real-time gains report, exportable |
| Performance Analytics | Basic charts and P&L totals | 30+ metrics: win rate, expectancy, drawdown |
| Price | Free | $159 one-time, lifetime access |
This template is a genuine free resource — no email, no paywall, no stripped-down demo version. When trade volume grows and manual entry across 3-5 exchanges becomes the bottleneck, JournalPlus connects directly to your exchanges and wallets to automate what this spreadsheet handles by hand.
Download
Download the free Crypto Portfolio and Trade Tracker Spreadsheet and start logging your trades, DeFi positions, and gas fees today. No account required — open in Excel 2016 or later, or upload to Google Sheets.
Frequently Asked Questions
What is a crypto portfolio tracker spreadsheet?
A crypto portfolio tracker spreadsheet is an Excel or Google Sheets file that consolidates your cryptocurrency trades, current holdings, DeFi positions, and fees in one place. Unlike exchange dashboards that show only one venue at a time, a spreadsheet-based crypto trading journal handles multi-exchange data, gas costs, LP impermanent loss, and staking rewards in a single view.
How do I track trades across multiple crypto exchanges in one spreadsheet?
Use a Trade Log tab with an Exchange column and a pre-built dropdown that lists your venues — Coinbase, Binance, Kraken, Bybit, Uniswap, and any others. Consistent exchange labels let you filter and pivot by venue to compare performance across platforms. This template includes that dropdown pre-configured. For context on tracking exchange-specific risk, see the Kraken integration page and Bybit integration page.
Does this crypto trading journal template handle DeFi and LP positions?
Yes. The DeFi tab tracks LP positions with a built-in impermanent loss calculator, staking reward accruals by day and month, and yield farming APY with an accrued interest column. The Gas Fee Ledger keeps on-chain costs separate from trade P&L, which is essential for evaluating whether a DeFi position is actually profitable after fees.
How does the FIFO vs. AVCO cost basis toggle work for crypto taxes?
Selecting FIFO in the Settings tab applies first-in-first-out lot matching: the oldest lots are sold first, which is the IRS default and the method brokers must report under Form 1099-DA starting in 2025. Selecting AVCO calculates an average cost basis per asset across all buys, which smooths out P&L on frequent buyers. The Capital Gains tab recalculates automatically when you switch methods.
What’s the difference between this free Excel template and a paid crypto trading journal app?
The primary differences are data entry and analytics depth. This Excel crypto portfolio tracker requires manual entry — roughly 2-5 minutes per trade — and produces basic charts and P&L summaries. A dedicated app like JournalPlus connects to exchanges via API, imports trades automatically, and provides advanced metrics including win rate, average holding period, and drawdown analysis. The spreadsheet is an excellent starting point with zero cost; an app becomes worthwhile once manual entry creates a meaningful time cost or audit risk from transcription errors.
