Use Case

Syntax

Function arguments

Function argume...

PARAMETERS

Examples

Troubleshooting

Additional Reso...

> 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](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-syntax.png)

![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-autocomplete.png)

![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/google/functions/csohlcv-autocomplete.png)


<iframe
  style={{ maxWidth: "850px" }}
  width=849
  height=849
  src="https://www.youtube.com/embed/JPjbVo7ZIHA"
  frameborder="0"
  allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture"
  allowfullscreen
></iframe>



## 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](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-syntax-autocomplete.png)  

- **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](https://docs.cryptosheets.com/metadata/)

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](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-relative-cells.png)  

![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-relative-example.gif)  

<a href="https://app.cryptosheets.com/#/browse" rel="noopener noreferrer" target="_blank">Click here</a> 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_

> <inline-code functionvalue='=CS.OHLCV("btc","usd")'>=CS.OHLCV("btc","usd")</inline-code>

_Google Sheets_

> <inline-code functionvalue='=CSOHLCV("btc","usd")'>=CSOHLCV("btc","usd")</inline-code>

**Example 2:**  

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

_Excel_

> <inline-code functionvalue='=CS.OHLCV("btc","usd","coinbase","spot","1day")'>=CS.OHLCV("btc","usd","coinbase","spot","1day")</inline-code>

_Google Sheets_

> <inline-code functionvalue='=CSOHLCV("btc","usd","coinbase","spot","1day")'>=CSOHLCV("btc","usd","coinbase","spot","1day")</inline-code> 

![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-example1.png)  

**Example 3:**  

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

_Excel_

> <inline-code functionvalue='=CS.OHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")'>=CS.OHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")</inline-code>

_Google Sheets_

> <inline-code functionvalue='=CSOHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")'>=CSOHLCV("btc","usd","mtgox","spot","1day","1/1/2011","1/1/2012")</inline-code>
 
![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-example2.png)  

**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](https://docs.cryptosheets.com/functions/ohlcva/)

_Excel_

> <inline-code functionvalue='=CS.OHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})'>=CS.OHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})</inline-code>

_Google Sheets_

> <inline-code functionvalue='=CSOHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})'>=CSOHLCV("btc","usd","deribit","opt","1min",43721,43766,{"optionExpirationTime",191025;"optionStrikePrice",7500;"optionType","c"})</inline-code>

  
![image](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/excel/functions/csOHLCV-example3.png)  

**Example 5:**

Try some of the [free OHLCV query builder templates](https://docs.cryptosheets.com/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](https://docs.cryptosheets.com/templates/ohlcv-query-builder/)**
2. **[OHLCV Futures Query Builder](https://docs.cryptosheets.com/templates/ohlcv-futures-query-builder/)**

[![](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/templates/thumbs/ohlcv-query-builder.png)](https://docs.cryptosheets.com/templates/ohlcv-query-builder/)

[![](https://res.cloudinary.com/cryptosheets/image/upload/cryptosheets-docs/templates/thumbs/ohlcv-futures-query-builder.png)](https://docs.cryptosheets.com/templates/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. <a href="https://support.office.com/en-us/article/function-arguments-65b29fb5-ec7b-4c0b-a54b-a67923571519" rel="noopener noreferrer" target="_blank">Check Excels help sections</a>
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_

- <a href="https://docs.microsoft.com/en-us/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins" rel="noopener noreferrer" target="_blank">Web browsers used by add-ins</a>
- <a href="https://docs.microsoft.com/en-us/office/dev/add-ins/develop/office-versions-and-requirement-sets" rel="noopener noreferrer" target="_blank">Office versions and requirement sets</a>
- <a href="https://docs.microsoft.com/en-us/office/dev/add-ins/develop/office-versions-and-requirement-sets#how-to-check-your-office-version" rel="noopener noreferrer" target="_blank">How to check your Office version</a>
- <a href="https://docs.microsoft.com/en-us/office/dev/add-ins/develop/install-latest-office-version" rel="noopener noreferrer" target="_blank">Install the latest version of Office</a>
- <a href="https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-architecture" rel="noopener noreferrer" target="_blank">Custom Function Requirements</a>

_For Googlesheets_

- <a href="https://developers.google.com/apps-script/guides/services/quotas" rel="noopener noreferrer" target="_blank">Throughput Quotas & Limits</a>

How to use the OHLCV custom function in Excel and Google Sheets