How to audit crypto trade cost basis with historical price lookups in Google Sheets

The problem with historical crypto price lookups

Exchanges give you CSV files with trade execution times and token quantities. They rarely give you the historical USD spot price at the exact moment your order filled. When tax season arrives or you perform a quarterly portfolio audit, you are stuck filling in missing price data point by point.

Using third-party tax software often means handing over read-access API keys to every wallet and exchange account you own. Spreadsheets give you full ownership of your trade history. However, native Google Sheets functions lack reliable cryptocurrency historical coverage and offer no access to tokenized real-world assets.

You can solve this directly in your spreadsheet. By pairing your trade log with historical price lookups, you build an auditable cost-basis engine that updates automatically without exposed API keys.

Step 1: Set up your trade log architecture

Start with a clean, structured table on a dedicated tab in Google Sheets. Name this tab Trade_Log. Standardize your columns from left to right so calculations remain consistent across all assets.

• Column A: Transaction Date (Format as YYYY-MM-DD)
• Column B: Asset Symbol (For example, BTC-USD, ETH-USD, or SOL-USD)
• Column C: Transaction Type (Buy, Sell, or Staking Reward)
• Column D: Token Quantity (The exact amount acquired or disposed of)
• Column E: Historical Spot Price (USD price on the transaction date)
• Column F: Total Cost Basis (Quantity multiplied by Historical Spot Price)

Structuring your data cleanly upfront prevents formula errors when you build historical lookup queries or aggregate total tax liabilities downstream.

Step 2: Execute historical price lookups

To populate Column E with accurate historical prices, install the CryptoTrackPro add-on from the Google Workspace Marketplace. The add-on fetches data directly from CoinMarketCap without requiring external API keys or developer scripts.

For current price tracking across standard pairs, write a single cell formula like =cryptotrackpro("BTC-USD") or =cryptotrackpro("ETH-EUR"). For historical tax and cost-basis auditing, use the built-in historical price lookup feature.

Enter the historical lookup formula into Column E alongside your transaction date. The formula references the asset pair in Column B and the execution date in Column A, returning the spot price for that day. This replaces manual price searches across web archives with a repeatable formula.

Step 3: Calculate realized gains and cost basis

Once Column E holds your spot prices, calculate your total transaction cost basis in Column F. Multiply your token quantity by the historical spot price:

=D2*E2

Create a second section or tab titled Portfolio_Summary to monitor active market values against those original cost basis figures. In this summary table, pull live market rates using the standard formula in cell C2:

=cryptotrackpro("BTC-USD")

Calculate your unrealized profit and loss in dollar terms with a basic subtraction formula:

=(C2-E2)*D2

You now have an automated audit trail. Your spreadsheet tracks total invested capital alongside real-time asset market values.

Step 4: Incorporate tokenized real-world assets

Modern portfolios frequently hold real-world assets alongside core cryptocurrencies. Tokenized equities, exchange-traded funds, and physical commodities trade directly on public blockchains.

To include these positions in your cost basis sheet, switch to the real-world asset formula syntax. Use =cryptotrackproRWA("AAPL") for Apple, or queries for TSLA, SPY, QQQ, GOLD, and SILVER. Over 7,900 tokenized real-world assets are supported.

Keep these assets in the same trade log. The historical lookup mechanics remain identical. You keep your crypto, tokenized stocks, and tokenized gold inside a single unified ledger without creating separate spreadsheets for different asset classes.

Step 5: Apply technical indicators for tactical execution

Cost basis tracking tells you where you entered a position. Technical indicators help you decide when to rebalance or exit. Base and Pro accounts include historical OHLCV data alongside built-in indicators like RSI and MACD.

Add technical momentum tracking adjacent to your position summary. Calling historical RSI or MACD signals directly inside Google Sheets highlights overbought or oversold conditions across your holdings. You can evaluate whether an asset is trending before taking taxable profits or tax-loss harvesting.

Step 6: Manage refresh limits and tier constraints

Your lookup requirements depend on how many trade executions you audit each day. Align your workload with the appropriate account tier to avoid hitting daily quotas:

• Free Plan: Includes 50 price refreshes per day. Ideal for simple portfolios with infrequent trade logging and casual monitoring.
• Base Plan ($3.99/month): Provides 500 price refreshes per day with native 30-minute auto-refresh intervals. Fits active traders auditing dozens of trades monthly.
• Pro Plan ($11.99/month): Unlocks unlimited price refreshes per day with native 5-minute auto-refresh intervals. Built for high-frequency logs, large multi-asset sheets, and instant on-open refreshes.

If you prefer to bypass sheet setup entirely, download the free pre-built portfolio tracker template. It comes pre-loaded with asset allocation formulas, green and red conditional formatting, and live price bindings. You can insert historical lookup formulas directly into the template's underlying log sheet.