Crypto Portfolio Tracker in Google Sheets: Free Template & Setup Guide (2026)
Paid crypto portfolio apps charge $10–$25/month and still lose data when exchanges go down. Google Sheets costs nothing, lives in your Drive, and can pull live coin prices with a single formula. Here's how to build a crypto portfolio tracker that actually works — and the columns you need to stay ready for tax season.
Table of Contents
- Why Google Sheets for Crypto Tracking?
- What to Track (The 8 Essential Columns)
- Getting Live Crypto Prices in Google Sheets
- Cost Basis Methods: FIFO vs. HIFO vs. Specific ID
- The 4-Tab Spreadsheet Structure
- Key Formulas for Gains, Losses & P&L
- Building Your Portfolio Dashboard
- Tax Season Prep: What the IRS Wants
- 5 Crypto Tracking Mistakes That Cost You Money
- Want It Pre-Built?
Why Google Sheets for Crypto Tracking?
Most crypto portfolio apps have a fatal flaw: they depend on exchange API connections. When Coinbase changes its API, your data breaks. When the app company shuts down (and many have), your history is gone. When you want to export for your CPA, you get a CSV that needs hours of cleanup.
Google Sheets solves all three problems. Your data lives in your own Drive, under your own Google account. You control the structure. You can export a clean spreadsheet to your accountant in 30 seconds. And with the right formulas, it updates prices automatically.
What Google Sheets Can Do for Crypto
- Pull live coin prices via GOOGLEFINANCE or an import formula
- Calculate unrealized gains/losses per coin and in total
- Track cost basis using FIFO, HIFO, or specific identification
- Log every transaction — buys, sells, transfers, staking rewards
- Visualize allocation with pie and bar charts
- Generate a tax summary your CPA can actually use
Good to know: Google Sheets supports GOOGLEFINANCE("CURRENCY:BTCUSD") for Bitcoin and a handful of major coins. For altcoins, you'll use an IMPORTDATA or IMPORTXML formula pulling from a free price API. Both methods are covered below.
What to Track: The 8 Essential Columns
Before you build formulas, you need the right raw data. These are the 8 columns every crypto tracker needs. Without them, your cost basis is broken and tax season becomes a nightmare.
| Column | What to Record | Why It Matters |
|---|---|---|
| Date | Transaction date (MM/DD/YYYY) | Determines short-term vs. long-term tax treatment (1-year cutoff) |
| Coin | Ticker symbol (BTC, ETH, SOL) | Used in VLOOKUP formulas to pull current price |
| Type | Buy / Sell / Transfer / Reward / Fee | IRS treats each type differently — staking rewards are income |
| Quantity | Amount of coin transacted | Core input for all gain/loss calculations |
| Price Per Coin (USD) | Price at time of transaction | This IS your cost basis — must be logged at transaction time |
| Total Cost (USD) | Quantity × Price Per Coin | What you paid; FIFO lot matching uses this |
| Exchange / Wallet | Coinbase, Kraken, Ledger, MetaMask | Required for reconciliation; transfers between wallets are NOT taxable events |
| Transaction ID / Notes | Hash or reference number | Audit trail if the IRS asks questions |
⚠️ Don't skip the price column. The most common crypto tracking mistake is logging "I bought 0.5 BTC" without recording the price at the time of purchase. Retroactively finding historical prices is painful. Log it the day you transact.
Getting Live Crypto Prices in Google Sheets
There are three methods, ranging from easiest to most flexible. Pick the one that fits your portfolio size.
Method 1: GOOGLEFINANCE (Major Coins Only)
Google Finance supports real-time price data for Bitcoin, Ethereum, Litecoin, and a few other top coins. The formula is simple:
Put each formula in a "Prices" tab, then VLOOKUP against it from your portfolio tab. This works great for BTC/ETH holders and updates automatically every time the sheet recalculates.
Method 2: IMPORTDATA from a Free API
For altcoins not in GOOGLEFINANCE, use CoinGecko's free simple price endpoint. Create a helper tab called "API Prices" and use:
This returns a JSON string you can parse with a combination of REGEXEXTRACT formulas. It's more complex to set up, but covers thousands of coins. CoinGecko's free tier allows 30 calls/minute — more than enough for a personal tracker.
Method 3: Manual Price Tab (Simplest, Lowest Maintenance)
For investors who check their portfolio weekly rather than daily: maintain a dedicated "Prices" tab where you manually paste current prices from any exchange. Use VLOOKUP in your portfolio tab to pull from that tab. Update once a week. Simple, no API rate limits, no broken formulas after API changes.
Recommendation: Start with GOOGLEFINANCE for BTC/ETH. Add IMPORTDATA only if you hold 5+ altcoins that aren't supported. Manual is fine for long-term holders who don't need minute-by-minute data.
Cost Basis Methods: FIFO vs. HIFO vs. Specific ID
Your cost basis method determines how much tax you owe. The IRS allows several approaches, and the difference between them can be thousands of dollars in a volatile year.
| Method | How It Works | Best When | Tax Impact |
|---|---|---|---|
| FIFO | First coin you bought is the first you sold | You've held long-term; prices went up | Often higher gains (older = lower cost basis) |
| HIFO | Highest cost basis lot sold first | You want to minimize current-year gain | Lower short-term gains; legally used |
| Specific ID | You designate exactly which lot you sold | You have good records and want precise control | Most flexible; requires documentation |
| Average Cost | Average all purchase prices | Simplest bookkeeping | IRS does NOT allow average cost for crypto (only stocks) |
⚠️ Average cost is NOT allowed for crypto. It's permitted for mutual funds and stocks, but the IRS requires FIFO (or Specific ID with records) for cryptocurrency. Most tax software defaults to FIFO — check your settings.
Your Google Sheets tracker should implement FIFO by default. This means your transaction log must stay in chronological order and each sale matches against the oldest remaining lots first.
The 4-Tab Spreadsheet Structure
A well-organized crypto tracker has four tabs. Each has a specific job. Don't combine them — when you need to find something at 11pm during tax season, you'll thank yourself for keeping things separated.
Tab 1: Transaction Log
Every single transaction, in chronological order. Buys, sells, transfers, staking rewards, airdrops, fees. This is your source of truth. Nothing gets deleted — if you made a mistake, add a correction row with a note.
Columns: Date | Coin | Type | Quantity | Price/Coin | Total Cost | Exchange | Notes | TX Hash
Tab 2: Current Holdings
Summarized view of what you own right now. One row per coin. Auto-calculated from the Transaction Log using SUMIFS. Shows: quantity held, average cost basis, current price (VLOOKUP from Prices tab), current value, unrealized gain/loss, and unrealized % return.
Tab 3: Prices
A simple two-column table: Ticker | Current Price. Your GOOGLEFINANCE or IMPORTDATA formulas live here. Everything else in the spreadsheet VLOOKUPs this tab for current price data. One tab to update when you switch price sources.
Tab 4: Tax Summary
Realized gains and losses for the current tax year. Pulls from Transaction Log to summarize all completed sales. Shows: coin, date acquired, date sold, proceeds, cost basis, short/long-term classification, net gain/loss. This is what you hand to your CPA.
Key Formulas for Gains, Losses & P&L
These formulas form the core of any working crypto tracker. Adapt cell references to your layout.
Total Quantity Held (per coin)
Average Cost Basis (weighted average, for reference)
Current Coin Value
Unrealized Gain/Loss (USD)
Unrealized Return (%)
Short-Term vs. Long-Term Classification
Building Your Portfolio Dashboard
A dashboard tab gives you the at-a-glance picture. At minimum, your dashboard should show:
Dashboard Metrics
- Total portfolio value (current)
- Total amount invested (cost basis)
- Total unrealized gain/loss ($ and %)
- Best and worst performing coin
- Portfolio allocation chart (pie chart by current value)
- Coin breakdown table (all holdings, sorted by value)
- Realized gains/losses year-to-date
- Estimated tax owed (short-term rate × short-term gains)
Allocation Pie Chart Setup
Select your Holdings tab columns for Coin and Current Value. Insert → Chart → Pie chart. Set "Current Value" as the data series and "Coin" as the label. Google Sheets will auto-update the chart as prices change.
Color-Coding P&L Cells
Use conditional formatting to make gains green and losses red: Format → Conditional Formatting → "Less than 0" → red background. "Greater than 0" → green background. Applies to unrealized gain/loss cells. Visual scanning becomes instant.
Tax Season Prep: What the IRS Wants
The IRS treats crypto as property, not currency. Every sale, every trade (crypto-to-crypto counts), and every use of crypto to buy something is a taxable event. This catches a lot of people off guard.
Taxable Events in Crypto
| Transaction | Taxable? | Tax Treatment |
|---|---|---|
| Buying crypto with USD | No | Just establishes cost basis |
| Selling crypto for USD | Yes | Capital gain/loss (short or long-term) |
| Trading BTC for ETH | Yes | Disposal of BTC at fair market value = capital event |
| Using crypto to buy goods/services | Yes | Capital gain/loss at FMV on transaction date |
| Transferring between your own wallets | No | Not a taxable event; must document ownership |
| Staking rewards / Mining income | Yes | Ordinary income at FMV when received |
| Airdrops | Yes | Ordinary income at FMV when received |
| Receiving crypto as payment | Yes | Ordinary income at FMV; becomes your cost basis |
Your Tax Summary tab should auto-generate Form 8949 data: Date Acquired, Date Disposed, Proceeds, Cost Basis, Adjustment Code, and Net Gain/Loss. Most CPAs can take that table directly into tax software.
Want This Pre-Built?
Skip the setup and grab a ready-to-use Google Sheets financial tracker from SheetStackStudio. Instant download — start tracking today.
Browse Templates on Etsy →5 Crypto Tracking Mistakes That Cost You Money
1. Logging Quantity But Not Price
Writing "bought 2 SOL" without the price at time of purchase means you'll have to retroactively look up historical prices — which is tedious and introduces errors. Always log price/coin on the transaction date.
2. Treating Wallet Transfers as Sales
Moving BTC from Coinbase to your Ledger hardware wallet is NOT a taxable event. But if you log it as a "sell" in your tracker, you'll overstate your gains. Use the Type column to mark these as "Transfer" and exclude them from your Tax Summary.
3. Forgetting About Gas Fees
Ethereum gas fees and exchange transaction fees can be added to your cost basis (for buys) or deducted from proceeds (for sells). Over a year of active trading, fees can meaningfully reduce your taxable gain. Track them in a Fees column.
4. Treating Crypto-to-Crypto Swaps as Non-Events
Trading BTC for ETH is a disposal of BTC. You owe capital gains tax on any appreciation in BTC from your purchase price to the price when you traded it. Many DeFi users discover this the hard way when their 1099-DA arrives with large numbers.
5. Starting the Tracker After Multiple Years of Transactions
If you've been trading for years and just starting a spreadsheet, you need to reconstruct all historical transactions — not just current holdings. The IRS can ask about any prior year. Exchange history exports are your starting point; most major exchanges offer CSV downloads going back years.
Build It or Buy It?
Building this from scratch takes 3–5 hours if you're comfortable with Google Sheets. The result is fully custom to your exact coins and exchanges. The process above covers everything you need.
If you'd rather start tracking today without the setup work, SheetStackStudio's financial templates are built for exactly this kind of practical tracking. Each template comes with a clean tab structure, pre-built formulas, and an instructions tab so you're never guessing.
💡 The Bottom Line
A working crypto portfolio tracker in Google Sheets needs: a complete transaction log with price/coin, a holdings summary with live prices, a cost basis method (FIFO by default), and a tax summary tab. Build it once, maintain it per-transaction, and tax season becomes a 30-minute exercise instead of a week-long panic.