Historical crypto prices by date in Google Sheets

By CryptoTrackPro · Published 9 September 2026 · Examples checked against the current formula implementation

Use =cryptotrackproPrice("BTC","2026-08-01","USD") to request Bitcoin's historical price for a past date. When the date lives in a normal spreadsheet date cell, convert it to ISO text with TEXT. That small step avoids a common error: a displayed date and the value passed to an add-on are not always the same representation.

Start with a date you can inspect

After installing CryptoTrackPro, choose Extensions → CryptoTrackPro → Open CryptoTrackPro to connect the account. Create a table with a ticker, date and currency before adding any calculations. In B2, enter =DATE(2026,8,1) to create an actual date value. The examples use commas between arguments; if your spreadsheet uses semicolons, replace those separating commas throughout the formula.

In D2, use:

=cryptotrackproPrice(A2,TEXT(B2,"yyyy-mm-dd"),C2)

Copy D2 down after confirming that the first row returns a number. Keep the currency column visible and format the price cells accordingly. A row labeled EUR should use EUR in the function's third argument; changing the display format alone does not convert the quote.

Why the TEXT wrapper matters

Google documents that date cells arrive in Apps Script as Date objects. The current CryptoTrackPro function converts its date argument to a string, while the price service expects a recognizable calendar-date format. Passing TEXT(B2,"yyyy-mm-dd") produces the explicit year-month-day string it needs.

Alternatively, type a plain-text ISO date into B2 by starting the entry with an apostrophe, for example '2026-08-01. The apostrophe tells Sheets to store text and is not part of the value. For that layout, =cryptotrackproPrice(A2,B2,C2) works directly. Do not mix plain-text dates and date values unpredictably within one input column.

A value such as 08/01/2026 can mean different days in different locales. ISO text removes that ambiguity. Test =TEXT(B2,"yyyy-mm-dd") in a spare cell and check the result against the date you intended before filling a long table.

Use the result as a historical reference

The function is intended for daily historical pricing. For dates covered by Kraken daily candles, it uses the day's closing price. Older requests can use a trade-based approximation, and other coverage can use a fallback historical snapshot. Consequently, do not assume every result represents an identical closing-price methodology across all dates and assets.

For an illustrative valuation, put units held on that date in E2 and use =D2*E2 in F2. If the returned reference price were 80,000 USD and the recorded holding were 0.25 BTC, the reference valuation would be 20,000 USD. This is arithmetic using a historical market reference. It does not identify what you actually paid, the execution venue, transaction fees, or the time of a trade.

Keep exchange statements and actual trade fills in their own columns. A daily closing price should not silently replace an execution price when reconstructing transactions. If you need a documented valuation convention for formal reporting, record the source and review whether its date boundary and methodology fit your specific requirements.

Handle missing dates and preserve your work

Use completed past dates. Today and future dates are rejected, and a coin cannot have reliable market history before a supported market existed. Coverage depends on the asset, quote currency and available provider history; requesting an earlier year is not a guarantee of data. Inspect errors instead of converting every failure to zero with an error-handling formula.

Successful historical results are cached, so repeatedly recalculating the same lookup is not a way to request a different historical methodology. For a reviewed snapshot, copy the result table to a dated archive tab using Paste special → Values only. Preserve the input dates, currencies and a note describing the source. Keep the original formula table available so another reader can understand how the archive was built.

For a continuous six-column candle table instead of selected dates, use the OHLCV guide. Historical single-date prices are available across plans within lookup limits; the OHLCV function requires Base or Pro, including the Base trial.

Frequently asked questions

Why does a date cell produce an unrecognized-date error?

A normal Sheets date reaches the add-on as a Date object. Use TEXT(B2,"yyyy-mm-dd") inside cryptotrackproPrice so the service receives explicit ISO date text.

Is the historical price my purchase price?

No. It is a historical market reference, usually a daily close for supported Kraken candles. Fallback methodology can differ. Your actual purchase price and fees must come from your transaction records.

Continue with: Full formula reference · Report in EUR or GBP · All guides