How To Get Crypto Derivatives Data

Published in

Spreadstreet

8 min readAug 20, 2020

Crypto derivatives data is challenging to source and use. Cryptosheets changes that by providing instant, unified access to data for tens of thousands of tradable instruments, here’s how.

Crypto derivatives data has never been easier to find & use with Cryptosheets

OVERVIEW

This article is intended for users with some minimum knowledge of or experience with derivatives. The purpose is to demonstrate how to search and get data for different types of crypto & blockchain derivatives. It focuses on using all of the available Cryptosheets tools and techniques to easily find and get live or historical data for tens of thousands of complex derivatives instruments. The best part is you can do it yourself right now for free!

In this article we will:

Background: Review the crypto derivatives universe
How to Get Data: Explore several search methods & data sources
Examples: Get sample data for several example use cases

Getting crypto derivatives data should be this easy → and now it is using Cryptosheets. Try it yourself by pasting this formula into your spreadsheet: **=CS.QUERY(“Coingecko”, ”derivatives”)**

BACKGROUND

What the hell is a derivative? 👉 According to Investopedia…

“A derivative is a contract between two or more parties whose value is based on an agreed-upon underlying financial asset (like a security) or set of assets (like an index). Common underlying instruments include bonds, commodities, currencies, interest rates, market indexes, and stocks.

Generally belonging to the realm of advanced investing, derivatives are secondary securities whose value is solely based (derived) on the value of the primary security that they are linked to. In and of itself a derivative is worthless. Futures contracts, forward contracts, options, swaps, and warrants are commonly used derivatives.”

Crypto derivatives have (until recently) largely followed the structural evolution of traditional derivatives markets. Contractual terms, type, structure & transactional mechanics are similar. Recently more exotic and novel structures have been launched as they find enough liquidity to become more mainstream. The emergence of decentralized exchanges (DEX), liquidity pools, aggregators, automated protocols leveraging stable coins in the defi space is helping to facilitate continued product & market evolution.

Crypto derivatives are a critical necessity for mainstream institutional adoption.

Yes, derivatives allow traders to speculate aggressively across markets with substantial leverage and risk. They also allow large organizations to safely and efficiently hedge their operating risk by balancing inflows vs outflows while dampening the effects of inherent market volatility. All of that means 👉 big companies can tell their stakeholders things like “… YES our crypto risk is hedged and thus shouldn’t impact our earnings…”

Use =CS.TRADES to instantly pull detailed data from the most recent trades

QUICK TIPS:
Derivatives are complex instruments that inherently require a reference of some kind to their origin asset which means their identifiers & names can easily get long 👉 be smarter in your search by understanding common abbreviations & identifier codes and looking for partial matches for symbols, asset names, types and structures

Example: A typical Bitcoin option for (BTC/USD) might have a symbol like this: BTC-USD-11-JUN20-9000P 👉 That means searching for “… Bitcoin futures…” may not return what you’re looking for

Use your tools! Cryptosheets custom function =CS.SYMBOLS allows you to instantly search over 25,000+ crypto derivatives

HOW TO GET DATA

First thing is finding the data you’re looking for. Below are some methods for how to quickly SEARCH ▶️ SOURCE ▶️ FILTER.

TIP: All example formulas are shown using the Microsoft Excel syntax but work for both Excel and Googlesheets — simple remove the “.” after the =CS for any formula to make it work in Googlesheets learn more here

🔎 Search Tools

👉 Browse Data
Available in: Cryptosheets Excel, Googlesheets, WebApp

Use a precanned category filter for providers or endpoints

👉 Global Search
Available in: Cryptosheets Excel, Googlesheets, WebApp

Use the Cryptosheets global search to include all available objects and sources

👉 Cryptosheets Docs Portal search
Available in: Cryptosheets Docs Portal

Use the Cryptosheets Docs for more in depth searches based on keywords, topics and more

👉 Using =CS.SYMBOLS & =CS.IDEAS
Available in: Cryptosheets Excel, Googlesheets

The CS.SYMBOLS custom function is arguably one of our most powerful tools. Use it as a relative reference for other formulas to dynamically look up available instruments inside your existing spreadsheet dashboards and models.

=CS.SYMBOLS(“symbol”,”eth”,”type”,”options”,”name”,”~put”)

=CS.SYMBOLS(“symbol”,”btc”,”type”,”futures”)

CS.IDEAS is quietly one of the most powerful crypto data search tools available (still in preview…)

PRO TIP: Learn how to use the Cryptosheets workspace side panel section to create powerful custom dashboards, workspaces and much more

🔎 Data Sources

👉 Exchanges
At the end of the day the best source for data on a @Bitmex perpetual contract is… @Bitmex (especially if you have capital at risk*). Crypto exchanges maintain a historically unprecedented degree of freedom and lack of reporting requirements allowing them to launch, list, delist trade, distribute etc just about any imaginable derivatives structure or instrument they can think of. That can make it hard for data aggregators and other services to accurately keep up with new innovative instruments.
(FTX has launched some amazingly innovative and dynamic trading products)
👉 Data providers
The big crypto data aggregators like CoinMarketCap, CoinGecko, Cryptocompare, Coin Metrics, Kaiko, CoinAPI and others have continued to improve the depth, accuracy and breadth of crypto derivatives data they collect. This makes them an increasingly convenient source for data.
👉 Investment funds & data analytics specialists
Some crypto funds have developed hybrid research & analytics teams that will put out more advanced & refined derivatives data, but it’s generally in the form of static research. There are some amazing specialty/boutique analytics platforms that are also starting to provide more derivatives data like…
👉 Structuring desks (coming soon to crypto.. ha)
Very common on wall street, structuring desks are usually specialists working for a bank or entity with enough balance sheet to support the structuring, creation & syndication of exotic or plain vanilla derivatives instruments.

🔀 Search Approaches

Your search objective will determine the best approach when searching for crypto derivatives data

There are new types of derivatives instruments being innovated, launched and traded every day. The most commonly traded structures include: futures, options, swaps, perpetuals, contracts, leveraged vehicles (ETFs), bespoke, structured. Generally derivatives will either have a term structure meaning they expire at a specified date, time or event horizon — or they are perpetual meaning they have no expiration or final maturity date.

Instrument Type (ie futures, options, swaps etc)
Underlying asset (ie Bitcoin, Ethereum, the Bitwise 10 index)
Trading Venue (ie centralized exchange, DEX, defi liquidity aggregator)
Style & Objective (ie leveraged, inverse, bearish, bullish, neutral)

EXAMPLES — HOW TO USE DERIVATIVES DATA

We’ll demonstrate three common use cases including exactly how to do each one yourself in Cryptosheets.

📈 Get futures prices & volumes
📈 Get historical derivatives trading data & create a ratio chart of the derivative vs the underlying
📈 Get metrics for derivative lending rates & historical open interest

1) Get futures prices & volumes

For this example we’ll use the FTX — Cryptocurrency Derivatives Exchange exchange API endpoint called “List All Futures” and the “Derivatives” endpoint from CoinGecko.

TIP: 👉 Follow along by copying & pasting any of the formulas below into your own spreadsheet. It’s free and easy!

First let’s navigate to the endpoints:
Docs: https://docs.cryptosheets.com/providers/coingecko/derivatives
Add-in: https://app.cryptosheets.com/#/browse/coingecko/derivatives
Docs: https://docs.cryptosheets.com/providers/ftx/list-all-futures
Add-in: https://app.cryptosheets.com/#/browse/ftx/list-all-futures
Second we’ll pull the @CoinGecko & FTX — Cryptocurrency Derivatives Exchange data into our spreadsheets using custom function formulas so they can easily be adjusted and refreshed
=CS.QUERY("Coingecko", "derivatives") =CS.QUERY("FTX", "list all futures")
Third we’ll adjust each formula using custom functions global arguments to show the columns & sorting we want to see
=CS.QUERY("Coingecko", "derivatives", {"_fields","market,symbol,price,open_interest,volume24"}) =CS.QUERY("FTX", "list all futures", {"_orderBy","-volumeUsd24h";"_fields","name,description,last,volumeUsd24h"})

TIP: 👉 Learning to use Cryptosheets global arguments like _fields, _formatDates, _limit, _orderBy will change your life. #RUMANDCOKEDOIT

Coingecko derivatives endpoint filtered using the _fields global argument

FTX list all futures endpoint for live prices & detailed market data

TIP: For Cryptosheets paid subscriptions → try adding the CS.TIME custom function to automatically refresh the entire data set at any interval

2) Get historical derivatives trading data & create a ratio chart of the derivative vs the underlying

For this example we’re going to use Cryptosheets core custom functions to get data for Bitcoin (BTC-USD) using a global market VWAP and compare it vs the Bitcoin perpetual swap (XBTUSD) from @Bitmex.

Global BTC VWAP:
=CS.OHLCV("BTC","USD",,,,,,{"_fields","time_close,price_close"})
Bitmex XBTUSD Perpetual: =CS.OHLCV("BTC","USD","Bitmex","perp","1day",,,{"_fields","time_close,price_close"})

Using the **_fields & _formatDates** global arguments for Cryptosheets custom functions is a gamechanger

3) Get metrics for derivative lending rates, historical open interest and more

For this example we’re going to use a combination of the BitMEX & Binance Futures APIs.

Bitmex Funding
=CS.QUERY("Bitmex", "funding")
Binance Open Interest Statistics
=CS.QUERYA("Binance","open interest statistics (futures)","X-MBX-APIKEY","Your X-MBX-APIKEY","symbol","BTCUSDT","period","1d")

Binance historical open interest statistics for futures

SUMMARY

Hopefully some of the examples and tutorials in this article have made getting derivatives data less overwhelming and more intuitive. We just scratched the surface on crypto derivatives. As the market evolves and we roll out new data integrations, features and analytics tools we will continue this blog series on derivatives with part II. Sign up for Cryptosheets now for free and follow us on Twitter @Cryptosheets_RT to get all the latest updates.

ADDITIONAL RESOURCES

Crypto derivatives exchanges:
BitMEX, FTX — Cryptocurrency Derivatives Exchange, Deribit Exchange, Deribit Exchange, Binance, Huobi Global, Kraken Exchange, OKEx… and many others

Crypto derivatives data providers:
CoinGecko, CoinMarketCap, Kaiko, glassnode, CoinIndex, IEX, CoinAPI … and many others

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

Get the Excel add-in now | Get the Googlesheet add-on now

TAGS : api, excel, googlesheets, spreadsheet, crypto data, crypto api, bitcoin, blockchain, derivatives, data, symbols, lookup, pairs, options, futures, swaps, perpetuals, leverage, ETFs, binary options, CFD, trading, contracts, speculation, hedging, defi, open interest, margin, lending, funding