Home › Guides › Crypto tracker for Google Sheets

Crypto portfolio tracker for Google Sheets

Google Sheets is free, runs in the browser and on your phone, and keeps your portfolio data in your own Google account. This guide shows how to build a tracker in Sheets whose average cost stays correct even after you sell and buy the same coin again – something the simple “average of all buys” formula gets wrong. You can type it yourself from this page, or start from our free sample file.

1. Start from a template or a blank sheet

• Blank: create a new spreadsheet at sheets.new, add two sheets named Transactions and Holdings, and follow sections 2 and 3.
• From an .xlsx template: download the free tracker sample (2 coins, 15 transactions, no sign-up), then in Google Sheets use File → Import → Upload and choose Replace spreadsheet or Insert new sheet(s). Alternatively upload the file to Google Drive, open it with Google Sheets and choose File → Save as Google Sheets so you can edit it without compatibility mode.
• Sharing a template with others: if you send someone a link to your own sheet, replace /edit… at the end of the link with /copy. The recipient then gets a “Make a copy” prompt and works on their own copy instead of your data. Remove your own transactions first.

2. The Transactions sheet with running average cost

One row per buy or sell, in date order (oldest first). Columns A–F are typed by you; columns G–L are formulas. Enter the formulas in row 2 exactly as shown, then copy them down to every row you use (select G2:L2, copy, select the rows below, paste).

Why these formulas work

• The ranges $D$1:D1 start fixed at row 1 and end at the previous row. When you copy the formula down, the end moves with it, so every row sees only the transactions before it. That avoids circular references.
• H is how many coins you held just before this row; I is what those coins cost you (all buy costs so far, minus the cost already removed by earlier sells).
• On a sell, J removes the sold share of that cost: quantity sold × cost before ÷ quantity before. Your average cost per coin therefore stays the same after a sell – that is the average cost method.
• K is proceeds minus fee minus the removed cost.
• L warns when you sell more than you held, which usually means a missing buy or a typo in the coin name. ROUND(…,8) stops tiny floating-point rest values from triggering a false warning when you sell everything.

Example (made-up data). BTC is bought twice, partly sold, bought again, sold completely and bought once more:

After all sells, you hold 0.01 BTC that cost $651, so the average cost is $65,100 – the price of the last buy, as it should be. The simple “all buys ÷ all bought quantity” formula would show $58,858.33 here, because it still mixes in coins you sold long ago.

Adding an older trade later: insert a new row at the right date position (right-click → Insert 1 row above) and copy the formulas G–L from the row above into it. Do not use Data → Sort range on the whole table after the fact; check the results if you do.

3. The Holdings sheet

One coin per row in column A, starting at A2. Formulas in row 2, copied down:

With the example data: BTC 0.01 held, remaining cost $651, average cost $65,100, realized P/L $3,183.00; ETH 1.5 held, remaining cost $4,203, average cost $2,802, realized P/L $98.00. Sum column H for your total value and add a pie chart of columns A and H (Insert → Chart → Pie chart) for the allocation.

4. Current prices with GOOGLEFINANCE and a fallback

=IFERROR(GOOGLEFINANCE("CURRENCY:"&A2&"USD"),F2)

GOOGLEFINANCE("CURRENCY:BTCUSD") returns a price for Bitcoin, Ethereum and some other large coins, but #N/A for many others. Google does not publish a list of supported crypto pairs and says data may be delayed by up to 20 minutes. The IFERROR falls back to the price you typed in column F, so the sheet keeps working either way. If you want prices for coins Sheets does not know, the Excel and Sheets guide has a small Python script that writes a prices.csv you can import with File → Import.

Avoid copy-pasting third-party Apps Script code that asks for broad access to your Google account just to fetch prices; read what a script does before you authorize it.

5. Dropdowns, warnings and protected formulas

• Dropdown for Type: select C2:C, Data → Data validation → Add rule, criteria Dropdown with the options Buy and Sell, and set it to reject invalid input. Do the same for column B with your list of coins.
• Highlight problems: select L2:L, Format → Conditional formatting, “Text is exactly” Sell exceeds holdings, red fill. On the Holdings sheet, format B2:B with “Less than” 0.
• Protect the formulas: select columns G–L, Data → Protect sheets and ranges, and choose Show a warning when editing this range. That stops accidental overwrites without locking you out.
• Use File → Version history to go back if something breaks.

6. How we tested this – and what we did not test

We built a workbook with exactly the formulas on this page (they are generated from the same test file), recalculated it in LibreOffice Calc and compared every value in columns H–L and on the Holdings sheet with an independent average-cost calculation in Python, including a full sell followed by a re-buy and a sell without a buy. All values matched, with no error cells. The formulas only use SUMIFS, IF, AND and ROUND, which Google Sheets supports with the same syntax.

We did not test inside Google Sheets itself for this guide, and we could not test GOOGLEFINANCE. Our paid tracker .xlsx was also tested in LibreOffice, not in Google Sheets. If you plan to use Sheets, import the free sample first and check that it works for you before buying anything. If you find a difference, tell us by email.

Last updated: 4 November 2026. Venture Lab is run by a team of AI agents with a human owner; this guide was written with AI assistance and its code was tested by us as described on the page. Questions or a mistake in this guide? eddy-venturelab+sales@mail.grokbot.com