How to use Kaik...

Example Paramet...

Usage

Setup

Using the console

Using custom fu...

Using templates

Helpful Resources:

> This endpoint retrieves the OHLCV history for an instrument on an exchange. The interval parameter is suffixed with s, m, h or d to specify seconds, minutes, hours or days, respectively. By making use of the sort parameter, data can be returned in ascending asc (default) or descending desc order. Access includes daily intervals. For full access to intervals ranging from 1 second to 1 day, please contact Kaiko for an API key.

## How to use Kaiko data in Excel & Googlesheets with Cryptosheets

This article demonstrates three examples of how to pull Kaiko API data into your spreadsheet using the `side panel console`,  `custom functions` and `templates`.

<img src="https://res.cloudinary.com/cryptosheets/image/upload/v1/cryptosheets-docs/providers/kaiko/console/historical-ohlcv-(candles).gif" />

Explore more Kaiko endpoints [here](https://app.cryptosheets.com/#/browse/Kaiko)

## Example Parameters:

- commoditydataversion: `trades.v1`
- data_version: ``
- exchange: `cbse`
- instrumentClass: `spot`
- instrument: `btc-usd`
- start_time: ``
- end_time: ``
- page_size: `100`
- continuation_token: ``
- interval: `1h`
- X-Api-key: `Your X-Api-key`


**Excel:**

<inline-code functionvalue='=CS.QUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})'>=CS.QUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})</inline-code>

**Googlesheets:**

<inline-code functionvalue='=CSQUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})'>=CSQUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})</inline-code>


> NOTE: Some international users may need to substitute commas `","` in custom functions with semicolons `";"` so for something like `=CS.TA("BTC","USD")` try `=CS.TA("BTC";"USD")` instead


## Usage

Cryptosheets data can be pulled through multiple interfaces:

<details>
  <summary>Setup</summary>

### Setup

  1. Install the Cryptosheets add-in from the [Microsoft AppSource Store](https://appsource.microsoft.com/en-us/product/office/WA104381695?src=intercom-Kaiko)

  2. Install the Cryptosheets add-in from the [Google GSuite Marketplace](https://gsuite.google.com/u/2/marketplace/app/cryptosheets/898843532532?src=intercom-Kaiko)

  3. After it is installed, make sure you are logged into your Cryptosheets account through the Cryptosheets side panel

  <img src="https://res.cloudinary.com/cryptosheets/image/upload/v1570534844/cryptosheets-docs/excel/install.png" />

  <img src="https://res.cloudinary.com/cryptosheets/image/upload/v1570537238/cryptosheets-docs/excel/login.png" />

  <img src="https://downloads.intercomcdn.com/i/o/223225643/14f5d809cb6dd07fd8e7b8c1/image.png" />

  <img src="https://cryptosheets.intercom-attachments-1.com/i/o/223227015/6af83d71cec3605f4608e834/cryptosheets-docs_google_login_addons-drop-down-menu.png" />

</details>

<details>
  <summary>Using the console</summary>

### Using the console
  1. From the Cryptosheets **Excel ribbon** click `data`
  2. From the **Googlesheets Add-ons menu** click `Cryptosheets` then select `Launch side-bar`
  3. Type in `Kaiko...` then click on `Historical OHLCV (Candles)` 
  4. Scroll through the console parameters and input your values
  5. Select the cell you want to place the data into
  6. Click `make request`

[![img](https://res.cloudinary.com/cryptosheets/image/upload/v1570538223/cryptosheets-docs/excel/ribbon/data-browse-providers.png)](https://res.cloudinary.com/cryptosheets/image/upload/v1570538223/cryptosheets-docs/excel/ribbon/data-browse-providers.png)

[![img](https://res.cloudinary.com/cryptosheets/image/v1/upload/cryptosheets-docs/google/menu/data-browse-providers.png)](https://res.cloudinary.com/cryptosheets/image/v1/upload/cryptosheets-docs/google/menu/data-browse-providers.png)


<img src="https://res.cloudinary.com/cryptosheets/image/upload/v1568230681/cryptosheets-docs/providers/kaiko/console/historical-ohlcv-(candles).gif" />

</details>

<details>
  <summary>Using custom functions</summary>

### Using custom functions

<video-player></video-player>

> **Tip**: For both Excel and Googlesheeets you can use either the **[QUERY](https://docs.cryptosheets.com/functions/query)** or **[QUERYA](https://docs.cryptosheets.com/functions/querya)** custom function

1. **Excel** In any cell type the following: 

`=CS.QUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})`

2. **Googlesheets** In any cell type the following: 

`=CSQUERY("Kaiko","Historical OHLCV (Candles)",{"commoditydataversion","trades.v1";"data_version","";"exchange","cbse";"instrumentClass","spot";"instrument","btc-usd";"start_time","";"end_time","";"page_size","100";"continuation_token","";"interval","1h";"X-Api-key","Your X-Api-key"})`

3. In any console, after selecting the parameters to configure the query string as desired, simply click the radio button to toggle the the `function/formula builder`, then click the `clipboard icon` to copy the full formula string and paste it into any cell **[see detailed examples and tutorial](https://intercom.help/cryptosheets/en/articles/3472916-using-the-cryptosheets-api-console-query-and-function-formula-builder-for-excel-googlesheets)**

<img src="https://downloads.intercomcdn.com/i/o/161179379/577ba7c6149cd8827b598623/image.png" />

> **Hint**: If you need help remembering the exact parameters for any function, you can always just type in any cell: `=CS.PARAMETERS("Kaiko","Historical OHLCV (Candles)")` for Excel or `=CSPARAMETERS("Kaiko","Historical OHLCV (Candles)")` for Googlesheets

<img src="https://res.cloudinary.com/cryptosheets/image/upload/v1/cryptosheets-docs/providers/kaiko/functions/historical-ohlcv-(candles).gif" />

</details>

<details>
  <summary>Using templates</summary>

### Using templates
  1. From the Cryptosheets Excel ribbon click `templates` OR click on the `templates tab` in the browse section
  2. From the Googlesheets Add-ons menu click `Cryptosheets` then select `Templates`
  2. Load the template called `Query Builder` and type Kaiko and Historical OHLCV (Candles)
  3. You can also try searching "Kaiko..." for other templates 

<img src="https://res.cloudinary.com/cryptosheets/image/upload/v1/cryptosheets-docs/templates/gifs/query-builder-provider-endpoint.gif" />

</details>

[Signing up for Cryptosheets is free](https://app.cryptosheets.com/#/register) get crypto data in 30 seconds or less

<ul>
  <li>
    <b><a href="https://appsource.microsoft.com/en-us/product/office/WA104381695?src=docs-Kaiko">
      Get Cryptosheets for Microsoft Excel
    </a></b>
  </li>
  <li>
   <b><a href="https://gsuite.google.com/u/2/marketplace/app/cryptosheets/898843532532?hl=en&pann=sheets_addon_widget&src=docs-Kaiko">
      Get Cryptosheets for Googlesheets
    </a></b>
  </li>
</ul>

> Note: you must have one of the [supported versions](https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-architecture) of Microsoft Office or Excel for custom functions to work. Learn more [here](https://intercom.help/cryptosheets/en/)

## Helpful Resources:
 - Visit the API documentation for Kaiko **[here](https://kaiko.com)**
 - See tutorials and examples for Kaiko on the **[Cryptosheets YouTube channel](https://www.youtube.com/channel/UCReKkrBSlgKgNJlWHLASiPQ)**
 - Troubleshooting: please reference the following troubleshooting code: QUE-1426-770. Learn more **[here](https://intercom.help/cryptosheets/en/)**

---
Tags: `Historical OHLCV (Candles)` `Kaiko` `endpoint` 

How to pull Historical OHLCV (Candles) data from Kaiko into Excel and Google Sheets