Home › Guides › Crypto portfolio tracker in a spreadsheet

Track your crypto portfolio in Excel or Google Sheets

A spreadsheet is still one of the simplest ways to keep track of what you own and what it cost – no account, no API keys on someone else’s server. This guide builds a working tracker step by step: a transactions log, a holdings table with average buy price and profit/loss, and allocation. Then it explains honestly which ways of getting current prices work in Google Sheets and in Excel, and which do not.

1. Sheet 1: Transactions

Create a sheet named Transactions with these columns in row 1. Every buy or sell is one row; never overwrite old rows.

Example data only; column G shows the formula results. In G2 enter the buy cost including the fee, then fill it down:

=IF(C2="Buy",D2*E2+F2,0)

Make the Type column a dropdown so typos like “buy ” do not break the sums: Excel Data → Data Validation → List with Buy,Sell; Google Sheets Data → Data validation → Dropdown. Use the same spelling for coins everywhere (BTC, not Bitcoin in one row and BTC in another).

2. Sheet 2: Holdings with profit/loss

Create a sheet named Holdings. Put one coin per row in column A starting at A2 (BTC, ETH, SOL), and use these formulas in row 2, then fill down:

Totals in row 6 (adjust if you have more coins): E6: =SUM(E2:E4), F6: =SUM(F2:F4), G6: =E6-F6, H6: =IF(F6=0,"",G6/F6). A pie chart of columns A and E gives you the allocation chart.

With the example data and current prices of BTC $75,000, ETH $3,200 and SOL $140 (made-up numbers), you get:

These formulas only use SUMIFS, IF, IFERROR and SUM, which work the same in Excel 2010 and newer, Excel for the web and Mac, Google Sheets and LibreOffice. In a German or other non-English Excel, function names are translated automatically when you open a file; when typing them yourself use your language’s names and separators (e.g. ; instead of ,).

About the average: column C averages all buys of a coin. That equals the usual running “average cost” method as long as every buy of a coin happens before its first sell – as in the example, where both methods give $62,971.43 for BTC. If you sell and later buy again, the running method gives a different result, because the later buy is averaged only with what you still hold. For an exact running average you need helper columns on each transaction row that carry quantity and cost forward.

3. Realized profit/loss on sells

Add column H Realized P/L to Transactions, in H2 and fill down:

=IF(C2="Sell",D2*E2-F2-D2*IFERROR(SUMIFS(G:G,B:B,B2)/SUMIFS(D:D,B:B,B2,C:C,"Buy"),0),"")

That is sale proceeds minus fee minus the sold quantity times the average buy price (same simplification as above). In the example, selling 0.01 BTC at $80,000 with a $2 fee gives $168.29. Sum the column for total realized P/L.

4. Current prices: what works where

Typing prices by hand is the most reliable option and works everywhere. If you want them filled automatically, here is what is actually available without add-ins:

Google Sheets: GOOGLEFINANCE

=GOOGLEFINANCE("CURRENCY:"&A2&"USD")

With A2 = BTC this becomes CURRENCY:BTCUSD. It works for Bitcoin, Ethereum and a number of other large coins, but returns #N/A for many others (including some large ones and most small tokens). Google does not publish a list of supported crypto pairs, and its help page says quotes may be delayed by up to 20 minutes. A safe pattern is a fallback to a manually typed price in column J:

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

Excel: no built-in live crypto formula

Excel has no function like GOOGLEFINANCE. Prices in Excel never update “live” like a ticker; the realistic options are:

The WEBSERVICE variant for Excel on Windows, one coin per cell (CoinGecko id in the URL):

=NUMBERVALUE(MID(WEBSERVICE("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd"),SEARCH("""usd"":",WEBSERVICE("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd"))+6,SEARCH("}",WEBSERVICE("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd"))-SEARCH("""usd"":",WEBSERVICE("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd"))-6),".")

It is long because it extracts the number from the JSON text {"bitcoin":{"usd":77340}}. Tidier: put =WEBSERVICE("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd") in a helper cell, say K2, and parse that cell:

=NUMBERVALUE(MID(K2,SEARCH("""usd"":",K2)+6,SEARCH("}",K2)-SEARCH("""usd"":",K2)-6),".")

NUMBERVALUE(…, ".") makes the decimal point work in Excel versions that use a comma as decimal separator. The public API is rate limited; many cells calling it at once can fail with an error – keep it to a few coins.

Any spreadsheet: a small Python script that writes prices.csv

This script needs only Python 3, no extra packages, and writes current USD prices for your coins to a CSV file:

"""Write current USD prices from CoinGecko into prices.csv (Python 3, no extra packages)."""

import csv
import json
import urllib.parse
import urllib.request
from datetime import datetime, timezone

# Your tickers (as in the Transactions sheet) -> CoinGecko ids
COINS = {"BTC": "bitcoin", "ETH": "ethereum", "SOL": "solana"}

url = "https://api.coingecko.com/api/v3/simple/price?" + urllib.parse.urlencode(
    {"ids": ",".join(COINS.values()), "vs_currencies": "usd"})
request = urllib.request.Request(url, headers={"accept": "application/json",
                                               "user-agent": "portfolio-sheet-example"})
with urllib.request.urlopen(request, timeout=15) as resp:
    data = json.load(resp)

stamp = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC")
with open("prices.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["Coin", "PriceUSD", "Updated"])
    for ticker, coin_id in COINS.items():
        if coin_id in data:
            writer.writerow([ticker, data[coin_id]["usd"], stamp])
        else:
            print(f"No price for {ticker} ({coin_id}) - check the id on coingecko.com")
print(f"Wrote prices.csv at {stamp}")

Example output on 3 November 2026: BTC,77319,… ETH,2281.09,… SOL,93.1,…. Import it as a sheet named Prices (Excel: Data → From Text/CSV, later Refresh All; Google Sheets: File → Import, “Replace current sheet”), then in Holdings!D2:

=IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"")

Run the script whenever you want fresh prices (or schedule it with cron / Task Scheduler).

How we tested this page: we built a workbook with exactly the formulas above (holdings, totals, realized P/L, the VLOOKUP import and the NUMBERVALUE/MID/SEARCH parser on a sample CoinGecko response), recalculated it in LibreOffice Calc 25.2 and compared every result with an independent calculation – all matched, no error cells. The Python script ran against the live CoinGecko API. We could not test GOOGLEFINANCE, the Excel Stocks data type, Power Query or WEBSERVICE inside Google Sheets or Microsoft Excel themselves; the limits above follow Microsoft’s and Google’s documentation.

5. Tips and common mistakes

• Fees in a different coin (e.g. paid in BNB): convert them to USD at the time of the trade and enter that amount, otherwise your cost basis is too low.
• Transfers between your own wallets are not buys or sells – do not log them, or log them in a separate sheet.
• Staking rewards and airdrops: log them as a “Buy” with price 0 if you want them in your quantity, but be aware that this lowers your average buy price.
• If Qty held becomes negative, a sell is missing a buy or a coin is spelled differently.
• Keep a backup copy; spreadsheets make it easy to overwrite a formula by accident. Protect formula columns if you share the file.

Last updated: 3 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