Overview
All historical data is from the CryptoCompare Pro API.
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 > > .
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 > > . 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"))