Key takeaways
- You can build a working crypto portfolio tracker in Google Sheets in about fifteen minutes with two tables and a few formulas.
- GOOGLEFINANCE has undocumented and unreliable crypto support, so a free public price API pulled in with IMPORTDATA is more dependable.
- A cloud spreadsheet stores your holdings on Google's servers, which is a real privacy trade-off for anyone tracking sizeable positions.
- SUMIF, weighted averages and simple percentage formulas turn a price list into allocation, cost basis and unrealized gains.
- If you want zero cloud exposure, a phone app that keeps data on your device is a stronger fit than a shared spreadsheet.
A crypto portfolio tracker in Google Sheets takes about fifteen minutes to build: one table for your holdings, one formula to pull live prices, and a handful of columns that turn those prices into allocation, cost basis and gains. This guide walks through each step with formulas you can copy, then covers the privacy trade-off of keeping your holdings in a cloud spreadsheet and a local alternative for anyone who wants none of that exposure.
You do not need any coding background, and you never enter a wallet key. A tracker only needs three facts per coin: what you hold, how much, and what you paid.
Setting up a crypto portfolio tracker in Google Sheets from scratch
Open a blank Google Sheet and build a single holdings table. Put these headers in row 1:
- A: Coin (the CoinGecko id, such as
bitcoinorethereum) - B: Symbol (BTC, ETH)
- C: Quantity
- D: Avg buy price (USD)
- E: Live price (USD)
- F: Market value
- G: Cost basis
- H: Unrealized gain
- I: Allocation %
Fill in columns A to D by hand. The coin id in column A matters, because the price API expects the full id (bitcoin), not the ticker (BTC). Everything from column E onward is calculated, so leave those cells empty for now.
Keep one row per coin. If you bought the same coin several times at different prices, you can either log each buy on its own row or store a single blended average in column D. The averaging section below shows how to compute that blend.
Fetching live crypto prices with formulas
Google Sheets ships with a built-in function called GOOGLEFINANCE. It works well for stocks and currency pairs, and the official Google documentation notes that quotes may be delayed up to 20 minutes and are provided for informational purposes only. For crypto, though, GOOGLEFINANCE is a weak choice: cryptocurrency tickers are not part of its documented, supported list, and community reports describe it returning prices inconsistently or not at all for certain coins. You may see =GOOGLEFINANCE("CURRENCY:BTCUSD") work one day and break the next.
A free public price API is more dependable. CoinGecko offers a keyless endpoint that needs no signup and no API key. Its public API documentation describes the base pattern, and the simple price endpoint looks like this:
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd
Visited in a browser, that URL returns a small piece of JSON: {"bitcoin":{"usd":97500}}. Google Sheets can read a URL with IMPORTDATA, then you pull the number out with REGEXEXTRACT. Put this in E2 for a Bitcoin row:
=VALUE(REGEXEXTRACT(IMPORTDATA("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd"),""""usd"":([0-9.]+)"))
To make the formula reusable across rows, build the URL from the coin id in column A:
=VALUE(REGEXEXTRACT(IMPORTDATA("https://api.coingecko.com/api/v3/simple/price?ids="&A2&"&vs_currencies=usd"),""""usd"":([0-9.]+)"))
Two practical notes. IMPORTDATA refreshes roughly once an hour on its own, so this is a slow-moving tracker rather than a live ticker. And free APIs cap how often you may call them, so a sheet with dozens of separate IMPORTDATA calls firing at once can hit a rate limit and return errors. Keep the coin count modest, or batch several ids into one call with ids=bitcoin,ethereum,solana.
Auto-calculating allocation, gains, and averages
With a live price in column E, the rest is straightforward arithmetic. Enter these in row 2 and drag them down.
Market value (F2):
=C2*E2
Cost basis (G2):
=C2*D2
Unrealized gain in dollars (H2):
=F2-G2
Allocation, the share each coin makes up of your total portfolio (I2):
=F2/SUM($F$2:$F$100)
Format column I as a percentage. The $F$2:$F$100 range is fixed so every row divides by the same total. A quick portfolio total in a spare cell is just =SUM(F2:F100), and total gain is =SUM(H2:H100).
To compute a weighted average buy price when you have added to a position over time, log each purchase on its own row with its quantity and the price you paid, then blend them. If your buys for one coin sit in rows 2 to 5, the average cost is total spent divided by total quantity:
=SUMPRODUCT(C2:C5,D2:D5)/SUM(C2:C5)
SUMPRODUCT multiplies each quantity by its price and adds the results, so a large buy counts more than a small one. That single number is your true average entry, and it feeds cost basis and gains correctly.
If you also want percentage gain per coin, add a column with =H2/G2 and format it as a percentage. A green-and-red conditional format on that column makes winners and losers obvious at a glance.
Privacy trade-offs of cloud spreadsheets
A Google Sheet is convenient because it lives in the cloud. That same fact is the trade-off. Your holdings, quantities and cost basis sit on Google's servers, and their safety depends entirely on your account security. Anyone who gains access to your Google account, or a link you shared and forgot about, can read a full inventory of what you own and roughly what it is worth.
The market itself is enormous and crowded, with millions of tokens now in circulation and thousands more appearing regularly, so it is easy to end up tracking a long, revealing list. A spreadsheet does not know or care how sensitive that list is. Three habits reduce the risk:
- Never turn on link sharing for the file. Keep it private to your account only.
- Store amounts, not identities. A tracker needs quantity and price, never a wallet address or a private key.
- Turn on two-factor authentication on the Google account that owns the sheet.
Even with those habits, the underlying reality holds: a cloud spreadsheet is a copy of your financial position stored on someone else's computer. For small or casual portfolios that may be an acceptable trade. For larger positions it is worth thinking harder about where the data rests. Tracking never requires giving up custody or keys, and you can read more on that principle in Crypto App No Custody: Track Coins Without Keys.
A local alternative when you want zero cloud exposure
If the cloud copy is the part that bothers you, the fix is to keep the data on a device you control. An app that stores your portfolio on your own phone, rather than syncing it to a server, removes the shared-document risk entirely. There is no link to leak and no server-side copy to breach.
QbyteLab's Crypto AI Agent is built around that model. It tracks a portfolio and wallet balances on live market data, sends price alerts, and exports reports, without taking custody of funds or asking for private keys. It also includes a converter with QR tools for quick lookups. The design goal is the same one a careful spreadsheet user is reaching for: watch your positions without handing a full inventory to a third party.
Alerts are the one place a spreadsheet genuinely cannot compete. IMPORTDATA refreshes about once an hour and cannot notify you of anything, so a target price can come and go while the sheet sits idle. A dedicated alert system watches continuously and pings you when a level is hit, which is worth having whether you keep the spreadsheet or replace it.
Start with the spreadsheet this weekend to learn how your allocation actually breaks down. Build the holdings table, wire in one IMPORTDATA price, and add the allocation and gains columns. Once you can see your portfolio clearly and you decide the cloud copy is more exposure than you want, move the same three facts per coin into the Crypto AI Agent and keep the data on your own device.
Frequently asked questions
Does GOOGLEFINANCE work for cryptocurrency prices?
It sometimes returns crypto prices with a format like CURRENCY:BTCUSD, but crypto support is undocumented and has known bugs. A free public price API is more reliable.
Can I pull live crypto prices into Google Sheets for free?
Yes. The CoinGecko public API needs no key, and you can read its price data into a cell with IMPORTDATA plus a REGEXEXTRACT to parse the number.
How often do the prices update in a Google Sheets tracker?
IMPORTDATA refreshes roughly every hour on its own. You can force a refresh by editing the sheet, and free APIs also limit how often you may call them.
Is it safe to keep my crypto holdings in Google Sheets?
Your amounts and cost basis live on Google's servers and are only as safe as your account. For sensitive positions, an on-device app avoids that exposure entirely.
Do I need wallet keys to track a portfolio?
No. Tracking only needs the coin, the quantity and your buy price. You never enter private keys, and no reputable tracker should ask for them.
Leave a Reply