All Collections
Custom Functions
CS.OHLCV - Pull Historical OHLCV Data in Cryptosheets using custom functions
CS.OHLCV - Pull Historical OHLCV Data in Cryptosheets using custom functions

Pull 100,000+ rows of OHLCV data from over 11,000 symbols directly into Excel & Googlesheets using =CS.OHLCV

J
Written by John Young
Updated over a week ago

OVERVIEW

The =CS.OHLCV formula is a Cryptosheets core function meaning it can be used without specifying a provider to return detailed historical data for nearly any pair across over 290+ exchanges

šŸ‘‰ Ā In its simplest form, the CS.OHLCV function says:

=CS.OHLCV(what base symbol, what quote symbol, what exchange, what instrument type, what period interval, what start-time, what end-time)

šŸ‘‰ Ā Use Case Scenarios

Use =CS.OHLCV() when you need:Ā 

  • maximum historical depth

  • symbol, exchange diversity

  • data granularity

  • variable resolutions

  • high level of control & flexibilityĀ 

This function is slightly more advanced given the number of parameters it requires and amount of data it can rapidly return.Ā 

TIP: This function is also very powerful when used in conjunction with CS.ORDERBOOKS and CS.TRADES.

Table of Contents

  1. Syntax & Function Arguments

  2. Examples

  3. Additional Resources

________________________________________________________________

1 ) CS.OHLCV - Syntax

Function arguments

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

Values:
=CS.OHLCV("BTC","USD","coinbase","spot","1day")

ā€‹

Function argument descriptions

  • base: The cryptocurrency symbol of interest (required)

  • quote: Symbol of asset or currency to convert into (required)

  • exchange: Name of exchange (required)

  • type: Type of instrument and price ie spot or futures, index, perpetual (required)

  • period: The time period for each candle aka the resolution (ie 1MIN, 1DAY, 1YRS) (required)

  • timeStart: Timeseries starting time

  • timeEnd: Timeseries ending time

  • arguments: Universal argument parameters (optional) configurable for use with any provider or endpoint combination

  • šŸ‘‰ TIP: in Cryptosheets core functions, there is a special formatDates Ā argument available āž”ļø it is an extremely powerful and easy to use date formatting tool you can convert to/from dozens of different types of date formats (it's literally so cool we wrote an entire tutorial and custom template for it)

HINT: if you ever get stuck on the syntax for a specific function formula, you can use the side panel console to look them up - including these tutorials!

PARAMETERS - Absolute vs. Relative

Absolute Parameter Values
Formulas using absolute values (instead of cell references) 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") Ā 


Absolute AND Relative Parameter Values

Formulas also using referenced values for arguments with multiple parameters can be referenced individually by cell address and will look like this:

=CS.OHLCV("btc","usd",L4,L5,L6,L7,L8)


Relative Provider, Endpoint & 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:

=CS.OHLCV(B1,B2,A3:B4)Ā 

(*Note - {"curly_brackets"} are NOT required when using referenced values)

šŸ‘‰ Ā TIP: Use the fields argument to select, reorder and configure the output columns however you want

Data pulls in this section are related to downloading OHLCV (Open, High, Low, Close, Volume) timeseries data. Each data point of this timeseries represents several indicators calculated from transactions activity inside a time range (period).

OptionsĀ 

  • From Symbolā€Šā€”ā€ŠFrom identifier of requested timeseries (ex. Bitcoin)

  • To Symbolā€Šā€”ā€ŠTo identifier of requested timeseries (ex. US Dollar)

  • Exchangeā€Šā€”ā€ŠExchange of requested timeseries (ex. Coinbase)

  • Currentā€Šā€”ā€ŠToggle to pull current vs. historical

  • Limitā€Šā€”ā€ŠRow limiter

  • Headersā€Šā€”ā€ŠToggle for table headers

  • Resolutionsā€Šā€”ā€ŠTimespan for each candlestick

Headers

  • Typeā€Šā€”ā€ŠType of data being pulled

  • Exchangeā€Šā€”ā€ŠExchange where data is pulled from

  • From Symbolā€Šā€”ā€ŠAsset quote

  • To Symbolā€Šā€”ā€ŠAsset base

  • Openā€Šā€”ā€ŠFirst trade price inside period range

  • Highā€Šā€”ā€ŠHighest traded price inside period range

  • Lowā€Šā€”ā€ŠLowest traded price inside period range

  • Closeā€Šā€”ā€ŠLast trade price inside period range

  • Volumeā€Šā€”ā€ŠCumulative base amount traded inside period range

  • Trade Countā€Šā€”ā€ŠAmount of trades executed inside period range

  • Time Start (GMT)ā€Šā€”ā€ŠTime of first trade inside period range

  • Time End (GMT)ā€Šā€”ā€ŠTime of last trade inside period range

________________________________________________________________

2 ) EXAMPLES

TIP: Try copying + pasting the example formulas directly into your worksheet

There are 3 primary ways to use CS.OHLCV in Cryptosheets:

šŸ‘‰ Ā Custom Functions

Example 1:Ā 

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

Example 2:Ā 

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

šŸ‘‰ Ā Quick Start Templates (Excel only)

Quick start templates are a very lightweight version of Cryptosheets regular templates. They are bare bone, without formatting and are designed to let users quickly interact with and learn custom functions. They can also be loaded with one click from the side panel! #HIGHFIVE
ā€‹


ā€‹

šŸ‘‰ Ā Power Templates

Cryptosheets provides dozens of premade templates that can be loaded anywhere anytime with one click and are ready to go. They can be loaded into your existing workbook as new tabs or loaded as entirely new workbooks.

TIP: Power templates are a new generation feature with enormous potential. If you're a power user āž”ļø spend some extra time with these


Example 3:

Chart flawless OHCLV candle charts in seconds
with one click...

Example 4:
Load some of our examples that include built in drop down menus, built in tooltips, guides and more


ā€‹
________________________________________________________________

RESOURCES

Troubleshooting

  1. Check your syntax for =CS.OHLCV() carefully, different functions for the same data may have slightly different required syntax

  2. Check the required arguments for =CS.OHLCV() vs the optional arguments

  3. Check your data & API quotas and limits

  4. Check your formulas carefully

  5. Check your relative references (=CS.OHLCV("BTC", "USD") vs. =CS.OHLCV("A1", "B1"))

  6. Use Excel's error checking tools

  7. Find additional troubleshooting resources and tips here

Other Something else? ā€“ Click here for full list of Excel error explanation

Additional Resources

For Microsoft Excel

Cryptosheets is free to sign up and start using now. What are you waiting for?

Signing up for Cryptosheets is free --> Get crypto data in 30 seconds or less


ADDITIONAL RESOURCES

Website Ā | Ā WebApp Ā | Ā Excel Ā | Ā Googlesheets Ā | Ā Help Center Ā | Blog Ā | Twitter
______________________________________________________________

TAGS : ohlcv, box and whisker, candles, custom functions, historical, excel, googlesheets, spreadsheet, crypto data, bitcoin, blockchain

Did this answer your question?