Overview

Extending from price syntax, you can get a pair the market average price at a historical date with the following syntax:

=CRYPTOFINANCE("BTC/USD", "price", "2014-12-25")

The date must be following the format YYYY-MM-DD, example: 2017-01-13.

The hour can be specified with this format [email protected]:mm, example:

=CRYPTOFINANCE("BTC/USD", "price", "[email protected]:00")

If you specify the minutes it will be ignored and rounded up to the hour. Timezone is set to UTC time (GMT +00:00).

Referencing Cells to Build the Date

Say A1 contains BTC, and A2 contains 2016–10–22.

First make sure to format A2 as plain text. To do this select cell A2, then go to Format > Number > Plain text.

Then in A3 enter this formula:

=CRYPTOFINANCE(A1 & "/USD", "price", TEXT(A2, "yyyy-mm-dd"))

Note: Here we use & to concatenate (glue together) cells and text to forge the function’s argument.

Example Including The Time

Say A1 contains BTC, A2 contains 2016–10–22, and A3 contains 21:00.

Select A2 and A3, format them both as plain text via Format > Number > Plain text. Then enter in A4:

=CRYPTOFINANCE(A1 & "/USD", "price", TEXT(A2, "yyyy-mm-dd") & "@" & TEXT(A3, "hh:mm"))

Example Using Google Sheets TODAY() Function

Getting the price exactly 10 days ago, we use TODAY()-10:

=CRYPTOFINANCE("BTC/USD", "price", TEXT(TODAY()-10, "yyyy-mm-dd"))

Quota and Limitation

Using CRYPTOFINANCE historical data for free and without any plan subscription is possible, but comes with following limitations:

  • Historical data are available up to a quota of 25 calls per day per user.
  • The full price history listing price_history where you can get the full OHLCV history listing for a market pair is not available.

For unlimited data and full historical listing the Historical Data subscription is available.

OHLCV: Open, High, Low, Close prices and Volume

You can pull any daily or hourly price point using the following syntaxes:

  • Open: =CRYPTOFINANCE("BTC/USD", "open", "2018-12-25")
  • High: =CRYPTOFINANCE("BTC/USD", "high", "2018-12-25")
  • Low: =CRYPTOFINANCE("BTC/USD", "low", "2018-12-25")
  • Close: =CRYPTOFINANCE("BTC/USD", "close", "2018-12-25")
  • Volume: =CRYPTOFINANCE("BTC/USD", "volume", "2018-12-25")

You can get the volume in the base currency (ei: BTC in BTC/USD) using =CRYPTOFINANCE("BTC/USD", "volume_base", "2018-12-25")

By default, using price will return the close price.

Exchange Specific

You can prefix an exchange name, to return data from a specific exchange, and not the market average. 196 exchanges are supported, you can find the list here, along with their supported pairs.

Example getting Kraken BTC/USD open price for November 2nd 2015 at 3pm:

=CRYPTOFINANCE("KRAKEN:BTC/USD", "open", "[email protected]:00")

Using Google Sheets TODAY() function:

=CRYPTOFINANCE("KRAKEN:BTC/USD", "price", TEXT(TODAY(), "yyyy-mm-dd"))