In an era where crypto portfolio apps are increasingly moving toward paid subscription models or inundating users with ads, Google Sheets remains the ultimate "power user" tool. It is free, infinitely customizable, and offers a level of privacy that third-party apps simply cannot match. If you want to take full control of your financial data, building your own tracker is the best way to do it.
Most investors start with a mobile app, but they eventually hit limitations. Google Sheets solves several problems simultaneously:
Before jumping into automation, you need a clean data structure. Open a new Google Sheet and create the following headers in the first row:
A1: Asset (e.g., BTC)
B1: Quantity (e.g., 0.5)
C1: Purchase Price ($)
D1: Current Price ($)
E1: Total Value ($)
F1: Profit/Loss ($)
G1: Allocation (%)
Fill in your assets and the amounts you hold. Leave the "Current Price" and "Total Value" columns blank for now; we will automate those in the next step.
While Google Sheets has a built-in GOOGLEFINANCE function, it is notoriously unreliable for many cryptocurrencies and often lags. To get accurate, real-time data, we recommend using a simple script to pull data from a free API like CoinGecko.
Step-by-step instructions:
function getPrice(coinId) {
var url = "https://api.coingecko.com/api/v3/simple/price?ids=" + coinId + "&vs_currencies=usd";
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
return json[coinId].usd;
}
=getPrice("bitcoin").Note: You must use the API ID from CoinGecko (e.g., "ethereum", "solana", "cardano").
With your current prices updating automatically, it's time to let math do the work. Use these formulas to populate your tracker:
Total Market Value:
In cell E2, enter: =B2*D2 (Quantity multiplied by Current Price).
Profit/Loss:
In cell F2, enter: =E2-(B2*C2). This subtracts your total cost basis from your current market value.
Portfolio Allocation Percentage:
First, sum up your total portfolio value in a separate cell (e.g., cell E10). Then in cell G2, enter: =E2/$E$10. This tells you exactly how "heavy" you are in a specific asset, which is crucial for risk management.
A professional-grade tracker doesn't just show the current balance; it shows performance trends. To make your sheet more readable, consider adding Conditional Formatting.
Highlight your Profit/Loss column, go to Format > Conditional Formatting, and set a rule: "If greater than 0, color green" and "If less than 0, color red." This provides an instant visual heat-map of your winners and losers.
You can also track Historical Value by creating a second tab and using a script to "snapshot" your total balance once every 24 hours. This allows you to build charts showing your portfolio growth over months or years.
Because your spreadsheet contains sensitive financial data, you must treat it like a bank account:
How often do the prices update?
The script method updates whenever the spreadsheet is refreshed or when you change a cell value. You can also set a "Time-driven trigger" in Apps Script to update the prices every hour automatically.
Can I track NFTs in Google Sheets?
Yes, though it is more complex. You would need to use an OpenSea API or a similar NFT floor price API to pull the "Floor Price" into your sheet using the same UrlFetchApp method described above.
Why is GOOGLEFINANCE showing #N/A for my coin?
Google Finance only supports a limited number of major cryptocurrencies. For altcoins, you are much better off using the CoinGecko script method provided in this guide.
Ledger Nano S Plus Hardware Wallet
View on AmazonMetal Crypto Seed Phrase Storage Kit
View on AmazonShare this guide: