Home › Templates › Crypto portfolio tracker

Crypto Portfolio Tracker Spreadsheet

A free crypto portfolio tracker spreadsheet: log buys, sells, wallet transfers and staking rewards, and it calculates each coin's quantity, average cost, cost basis, unrealized and realized profit or loss, and allocation, with a pie chart.

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no exchange connection. The formulas are plain SUMIFS/IF/SUM.

Coin	Quantity	Cost basis	Avg cost	Price	Market value	Unrealized P/L	Unrealized %	Allocation	Realized P/L
ORB	0.7	$31,782.25	$45,403.21	$58,000.00	$40,600.00	$8,817.75	27.74%	80.04%	$3,229.25
LUM	3.09	$6,223.82	$2,014.18	$2,500.00	$7,725.00	$1,501.18	24.12%	15.23%	$582.82
PXL	3,000	$1,201.20	$0.4004	$0.3500	$1,050.00	-$151.20	-12.59%	2.07%	-$201.80
NOD	150	$1,501.50	$10.01	$9.00	$1,350.00	-$151.50	-10.09%	2.66%	$0.00
Total		$40,708.77			$50,725.00	$10,016.23	24.60%	100.00%	$3,610.27

What's inside the spreadsheet

How to use it

1. On Holdings, type each coin you own once in column A. Use the same spelling everywhere (for example BTC, not Bitcoin in one place and BTC in another).
2. On Transactions, add one row per transaction, oldest first. Quantity is always positive; the Type dropdown sets the direction.
3. Type the price per coin and the fee in USD. If a fee was charged in a coin, convert it to USD at the time.
4. Moving coins between your own wallets: add a Transfer out row and a Transfer in row right after it. The quantity difference is the network fee.
5. A swap (coin A for coin B) is two rows: a Sell of A and a Buy of B at the USD value of the trade.
6. On Holdings, type today's price for each coin and the date in B3. Then read the totals and chart on Dashboard.
7. Fix any row where the Check column does not say OK.

How each transaction type is handled

Coins that leave you for good (spent, gifted, paid as a fee for goods) should be logged as a Sell at the market price, so their cost leaves the holding. Coins you held before you started tracking go in as a Buy with the original date, price and fee.

The formulas, explained

Exact formulas from the file. Transactions data is in rows 5–504: A = date, B = coin, C = type, D = quantity, E = price, F = fee. Row 12 is shown because it is a sell in the example.

The ranges in column L start at the header row (row 4) and stop one row above the current row. SUMIFS ignores the header text, so the first data row works too. Because the average uses only earlier rows, row order matters: keep transactions oldest first.

Average cost vs the tax rules

The template uses one average cost per coin across all your wallets. It is simple and stable: buys change the average, sells do not. Tax rules for working out a gain can be different:

• US: the IRS FAQ on virtual currency transactions says basis is "the amount you spent to acquire the virtual currency, including fees, commissions and other acquisition costs in U.S. dollars." You "may choose which units of virtual currency are deemed to be sold" if you can specifically identify them. If you do not, "the units are deemed to have been sold, exchanged, or otherwise disposed of in chronological order beginning with the earliest unit of the virtual currency you purchased or acquired; that is, on a first in, first out (FIFO) basis." The page also states: "These FAQs generally apply to transactions involving digital assets completed before Jan. 1, 2025." From 2025, basis is tracked per wallet or account; Revenue Procedure 2024-28 provides a safe harbor "to allocate unused basis of digital assets to digital assets held within each wallet or account of the taxpayer as of January 1, 2025."
• UK: HMRC's Cryptoassets Manual, CRYPTO22200, says "Each type of token will need its own pool" and "if a person owns bitcoin, ether and litecoin they would have three pools and each one would have its own ‘pooled allowable cost’ associated with it.". The pool is applied after the same-day rule and the 30-day rule: "If an individual disposes of tokens and then acquires, in the same capacity, tokens of the same type within the next 30 days", the new tokens are matched to the earlier disposal instead of going into the pool.

So the realized P/L in this sheet is a tracking figure. It can differ from the gain on a tax return: US filers use FIFO or specific identification per wallet, and UK filers apply same-day and 30-day matching before the pool. Other countries have their own rules. Check them, or ask a tax professional, before filing.

Where the prices come from

You type them. Each coin has one current price cell on Holdings and one price date (B3), so you always know how old the numbers are. Use the same source and time of day each time you update.

Google's GOOGLEFINANCE help page describes the function as one that "Fetches current or historical securities information from Google Finance" and does not document cryptocurrency symbols, so the template does not depend on it. Third-party add-ons and API imports exist, but they need setup and often an account or key.

Worked example from the sample data

Average cost after two buys: ORB

Buy 0.5 ORB at $40,000 with a $20 fee (cost $20,020.00), then 0.3 ORB at $50,000 with a $15 fee (cost $15,015.00). Holding 0.8 ORB with a cost of $35,035.00, so the average cost is $43,793.75.

A partial sell: ORB

Sell 0.2 ORB at $60,000 with a $12 fee. Proceeds are 0.2 × $60,000 − $12 = $11,988.00. Cost removed is 0.2 × $43,793.75 = $8,758.75. Realized P/L = $11,988.00 − $8,758.75 = $3,229.25. The average cost of the remaining 0.6 ORB does not change. A later buy of 0.1 ORB at $55,000 (+$6 fee) lifts it to $45,403.21 on 0.7 ORB.

A loss: PXL

5,000 PXL bought for $2,002.00 (average $0.4004). Selling 2,000 at $0.30 with a $1 fee gives $599.00 against a cost of $800.80: realized P/L -$201.80.

Transfer and staking: LUM

4 LUM bought for $8,008.00, then moved to a wallet: Transfer out 4, Transfer in 3.99. The 0.01 LUM network fee lowers the quantity but not the cost, so the average rises. Two staking rewards of 0.05 LUM at $2,400 and $2,200 add $230.00 of cost. Before the sale of 1 LUM the holding is 4.09 LUM costing $8,238.00, an average of $2,014.18. Selling at $2,600 with a $3 fee gives a realized P/L of $582.82.

Portfolio totals

At the example prices the coins still held cost $40,708.77 and are worth $50,725.00: unrealized P/L $10,016.23 (24.60%). Realized P/L from the three sells is $3,610.27, so total P/L is $13,626.50. ORB is 80.04% of the portfolio by market value. Fees on buys and sells add up to $68.50.

Common mistakes

• Logging only one side of a transfer. A lone Transfer out makes coins disappear. Add the matching Transfer in on the next row.
• Rows out of date order. The average cost at each sell uses only the rows above it. Sort by date before you trust the realized P/L.
• Typing a negative quantity for a sell. Quantities are always positive; the Type column sets the direction.
• Different spellings for one coin. ETH and eth match in SUMIFS, but ETH and Ether do not. The Check column flags coins missing from Holdings.
• Forgetting fees. Buy fees raise your cost; sell fees lower your proceeds. Leaving them out overstates profit.
• Stale prices. Market value is only as fresh as the prices you typed. Update the price date when you update prices.
• Treating the realized P/L as your tax figure. See the section on tax rules above.

FAQ

How do I track my crypto portfolio in a spreadsheet?

Log every transaction on one row: date, coin, type (buy, sell, transfer, staking reward), quantity, USD price and fee. Then total each coin with SUMIFS to get quantity held and cost basis, type a current price, and calculate market value, unrealized P/L and allocation. The free template on this page does all of this with plain formulas.

How do you calculate cost basis for crypto?

Add up what you paid, including fees. The IRS virtual currency FAQ says basis is the amount you spent to acquire the virtual currency, including fees, commissions and other acquisition costs in U.S. dollars. When you sell part of a holding, the method (average cost, FIFO or specific identification) decides how much of that cost goes with the coins sold.

Can I use average cost for crypto taxes?

It depends on the country. In the UK, HMRC pools each token type in a section 104 pool, which works like an average cost, but same-day and 30-day matching rules come first. In the US, the IRS FAQ says units are treated as sold first in, first out (FIFO) unless you specifically identify them. This template uses average cost for tracking only.

Is moving crypto between my own wallets taxable?

Under the IRS FAQ, a transfer from a wallet, address or account belonging to you to another one that also belongs to you is a non-taxable event. The IRS digital assets page lists paying a transfer fee with digital assets among the disposals that mean answering Yes to the digital assets question on the return, so the network fee can still matter. Other countries have their own rules.

Are staking rewards taxable income?

In the US, IRS Revenue Ruling 2023-14 holds that the fair market value of staking rewards is included in gross income in the year you gain dominion and control over them. The template records each reward at the USD price you enter for the day it was received.

Can Google Sheets pull crypto prices automatically?

Not reliably with built-in tools. Google's GOOGLEFINANCE help page describes it as fetching securities information from Google Finance and does not document cryptocurrency symbols. Add-ons and API imports exist but need setup or keys. This template uses a typed price column and a price date, so it works offline in Excel and Sheets.

Related tools and templates

• Crypto profit calculatorProfit on a single trade
• Capital gains tax calculatorEstimate tax on a sale
• DCA calculatorRegular buys over time
• Average price calculatorAverage cost across buys
• Leverage P&L calculatorMargin and liquidation maths
• Stock portfolio trackerTrades, cost basis, P/L, allocation
• Net worth trackerAssets minus liabilities, monthly
• All templatesFree spreadsheets

Educational template only, not financial, tax or investment advice. Example data is fictional: coins ORB, LUM, PXL and NOD, their prices and the exchanges are made up and do not refer to real assets. Crypto prices can be very volatile. Quotes from the IRS, HMRC and Google were retrieved on 25 October 2026.