OHLCV

Get historical open, high, low, close, volume and trades data for any resolution, pair or exchange

In its simplest form, the OHLCV function says

=CS.OHLCV(get historical price, volume and trading data for a given base asset, quote asset, exchange, instrument type and resolution interval for this time period)

image

image

image

Use Case

When you need maximum historical depth, data granularity, variable resolutions and a high level of control & flexibility for specific base, quote and exchange pairs or instrument types

Syntax

Function arguments

Arguments: =CS.OHLCV("base","quote","exchange","type","period","timeStart","timeEnd","args")

Example Values: =CS.OHLCV("BTC","USD","Coinbase","SPOT","1DAY","1/1/2020","2/1/2020")

Function argument descriptions

image

  • base: The cryptocurrency symbol of interest [string] required

  • quote: Symbol of asset or currency to convert into [string] required

  • exchange: Name of exchange [string] required

  • type: Type of price ie spot or futures [string] required

  • period: The time period for each candle aka the resolution [string] optional

  • timeStart: Timeseries starting time in ISO 8601 [string] optional

  • timeEnd: Timeseries ending time in ISO 8601 [string] optional

  • args: Optional: Global arguments, ex. limit, fields [any] optional

Click here to see supported exchanges, assets, symbols & trading pairs

Available resolutions for the period parameter: 1SEC,2SEC,3SEC,4SEC,5SEC,6SEC,10SEC,15SEC,20SEC,30SEC,1MIN,2MIN,3MIN,4MIN,5MIN,6MIN,10MIN,15MIN,20MIN,30MIN,1HRS,2HRS,3HRS,4HRS,6HRS,8HRS,12HRS,1DAY,2DAY,3DAY,5DAY,7DAY,10DAY,1MTH,2MTH,3MTH,4MTH,6MTH,1YRS,2YRS,3YRS,4YRS,5YRS

PARAMETERS

  • Absolute vs. Relative

Absolute Parameter Values

Formulas using absolute values for arguments with multiple parameters must be referenced inside an array using {"curly_brackets"} and will look like this: =CS.OHLCV("BTC","USD","Coinbase","SPOT","1DAY","43446","43811","")

Relative Parameter Values

Formulas using referenced values for arguments with multiple parameters must be referenced inside an array using a defined range and will look like this (*Note - {"curly_brackets"} are NOT required when using referenced values):

=CS.OHLCV(B1,B2,B3,B4,B5,B6,B7)

image

image

Click here to search for data providers and endpoints

Examples

TIP: Try click + copy + paste for any of the example formulas directly into your worksheet

Example 1:

Global weighted VWAP data for the BTC-USD pair

Excel

=CS.OHLCV("btc","usd")

Google Sheets

=CSOHLCV("btc","usd")

Example 2:

Historical daily OHLCV data for the BTC-USD pair traded on Coinbase

Excel

=CS.OHLCV("btc","usd","coinbase","spot","1day")

Google Sheets

=CSOHLCV("btc","usd","coinbase","spot","1day")

image

Example 3:

Historical daily OHLCV data from 2011 for the BTC-USD pair traded on Mt. Gox

Excel

=CS.OHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")

Google Sheets

=CSOHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")

image

Example 4:

Historical 1 minute OHLCV for the BTC Oct-25 7500 calls from the Deribit exchange. Note: for historical derivatives OHLCV including futures, options, perpetuals and more please see our advanced version called OHLCVA

Excel

=CS.OHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})

Google Sheets

=CSOHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})

image

Example 5:

Try some of the free OHLCV query builder templates designed to teach new users how to use, build and customize OHLCV formulas for all supported assets and instrument types

  1. OHLCV Query Builder
  2. OHLCV Futures Query Builder

Troubleshooting

  1. Check your formula syntax for carefully, different functions for the same data may have slightly different required syntax

  2. Check the required arguments for your function vs the optional arguments

  3. Check your data & API quotas and limits

  4. Check your formulas carefully

  5. Check your relative references

  6. Use Excels error checking tools

  7. Check Excels help sections

  8. Error Response Codes Reference

    • #N/A OK – Everything worked as expected
    • #BUSY Heavy query and/or unauthorized – Your User/Password API Keys are incorrect
    • #LIMIT Forbidden – You are not subscribed to the data feed requested
    • Other Something else? Reach out to us directly

Additional Resources

For Microsoft Excel

For Googlesheets