Cryptosheets
Published in

Cryptosheets

Crypto Derivatives

Deribit API data in Excel & Google Sheets for Crypto Derivatives Risk Management & Options Analysis

Advanced crypto asset portfolio management strategies and tools

Easily access live trading account data including orders, trades, balance, P&L, margin, greeks & more. Learn how to perform complex analysis & market risk calculations with simple, automatically refreshing formulas using Cryptosheets

Complex crypto derivatives data and options analytics has never been easier to use

OVERVIEW

This article highlights several use case examples from an institutional crypto fund manager including (actual) screenshots of advanced dashboards & models they have built using the Deribit API integration with Cryptosheets. This content is intended for active traders, analysts, institutional investors, portfolio managers and advanced operators.

In this article we will:

Example Crypto Fund Dashboard automatically updating in real time with positions, counterparty exposures, P&L, market betas and much more (see larger)

Deribit has been the best in class crypto options exchange since their inception. Their experienced team, outstanding tech stack, markets and a well maintained, comprehensive API service represent a competitive edge when applied using Cryptosheets.

Crypto derivatives are a critical necessity for mainstream institutional adoption. Options are often used by the most sophisticated funds & trading desks for managing risk.

Deribit is one of the only directly & natively integrated secure exchange APIs (outside of enterprise accounts). This is a genuine gamechanger for options traders, analysts & portfolio managers from small to world class funds who can easily access their entire Deribit account including all the available & advanced data — from the comfort and familiarity of their Excel or Google Spreadsheets.

✅ Securely connect to your Deribit account & subaccounts
✅ Access the entire Deribit API including public & private endpoints
✅ Leverage Cryptosheets tools including global parameters & technical indicator functions with any Deribit data
✅ Automatically refresh any API endpoint data at any custom interval
✅ Combine data including Genesis Volatility (using the fully integrated Gvol.io API) to build dynamic risk management models

HOW TO GET DATA

This article highlights the unique advantages of using the Deribit API through Cryptosheets including several ways to leverage the platform to easily access real time account & market data.

Cryptosheets maintains extensive dedicated documentation for the Deribit API integration including support resources, tutorials, YouTube videos and more
=CS.QUERYA("Deribit","Private Get Account Summary","currency","BTC")

Combining access to real time account data including positions, trades, orders, P&L with advanced market analytics like greeks, implied volatility and much more for thousands of options trading on the Deribit exchange — all through simple, automatically refreshing spreadsheet formulas is a gamechanger.

Accessing historical account trades using the formula builder to easily create simple formulas in Google Sheets or Excel

Simple formulas and basic account data can quickly be modeled into dynamic dashboard providing real time market insights and data driven analysis…

Example Crypto Fund Dashboard automatically updating in real time with positions, counterparty exposures, P&L, market betas and much more (see larger)

NOTE: This post was co-written by a ghostwriter from an active crypto hedge fund and long time institutional Cryptosheets account who has requested to remain anonymous — we are grateful for their help and willingness to share what they have created

BACKGROUND

Deribit API allows you to pull real-time information regarding your portfolio and markets. Harnessing this data as an institutional investor may allow you to make better real-time trading decisions and provide historical tracking of portfolio performance. Using Cryptosheets allows you to pull this information right into excel, making it easy for everyone to analyze this information. We have investors that are not technically proficient enough to use python, and love seeing this data in spreadsheet form.

A few examples of utility:

  • Tracking your Portfolio Balance is a really important step in Portfolio Risk Management. We are given insight into how we have performed in different market regimes. Deribit pulls information from all your subaccounts on the platform, allowing you to take snapshots.
=CS.QUERYA(“Deribit”,”Private Get Subaccounts”,”with_portfolio”,”true”,”_fields”,”portfolio__btc__equity,portfolio__eth__equity”)

Pull BTC / ETH Equity Balances:

=CS.QUERYA(“Deribit”,”Private Get Subaccounts”,”with_portfolio”,”true”,”_fields”,”portfolio__btc__equity,portfolio__eth__equity”)
  • One of the ways one could look at why our portfolio moved in a certain way is by tracking our current and historical delta and theta. This shows how we were positioned ahead of some moves in the market. This is important as you want to make sure you have the correct amount of exposure on, for the move you anticipate,

👉 Get Open Orders:

=CS.QUERYA("Deribit","Private Get Open Orders By Instrument","instrument_name","ETH-22FEB19-120-C")
Accessing open orders on all your positions using the formula builder to easily create simple formulas in Google Sheets or Excel

👉 Get Open Positions:

=CS.QUERYA("Deribit","Private Get Positions","kind","future","currency","BTC","Authorization","Your Auth Token")
Get live market pricing & risk on all your positions using the formula builder to easily create simple formulas in Google Sheets or Excel

… using the Cryptosheets built in global parameters, we can easily transform and format the response to return only the exact data fields we need in the exact order we need… 👇

👉 Get Subaccounts:

=CS.QUERYA(“Deribit”,”Private Get Subaccounts Details”,”currency”,”BTC”,”_fields”,”positions__0__instrument_name, positions__0__size, positions__0__delta, positions__0__average_price,positions__0__mark_price”)

(Keep in mind that you will have to do this number of positions that you have. If you set up the fields in the form of a string, this will be easy to mass pull positions)

  • Other metrics such as the amount of Premium Outstanding allow us to see what our portfolio greeks were composed of. You can further bucket this information into Call Premium & Put Premium. These statistics can be used to understand the exposure of your strategy (IE Call Overwriting / Collaring / Put Underwriting)
  • Combining these tools, we can create dashboards that convey useful information regarding your portfolio.

Other Useful Formulas:

Time Stamp Input in excel:

  • =+(A1-DATE(1970,1,1))*86400000

Order History (12/1/2021 to 12/14/2021):

=CS.QUERYA(“Deribit”,”Private Get Transaction Log”,”currency”,”BTC”,”start_timestamp”, “1638316800000”,”end_timestamp”, “1639440000000”)

Deribit Bankruptcies:

=CS.QUERYA(“Deribit”,”Get last settlements by currency”,”currency”,”ETH”,”type”,”bankruptcy”,”count”,”100",”search_start_timestamp”,”1638316800000”)

Deribit Funding Rate History:

=CS.QUERYA(“Deribit”,”Get funding rate history”,”instrument_name”,”BTC-PERPETUAL”,”start_timestamp”,”1638316800000”,”end_timestamp”,”1638316800000”)

Helpful Tips:

Example using the CS.TIME Cryptosheets formula to automatically refresh any formula at any custom interval… 👇

=CS.QUERYA(“Deribit”,”Private Get Subaccounts Details”,”currency”,”BTC”,”_fields”,”positions__0__instrument_name, positions__0__size, positions__0__delta, positions__0__average_price,positions__0__mark_price”, “time”,CS.TIME(“120”))

NOTE: CS.TIME() requires a premium paid Cryptosheets subscription)

ADDITIONAL RESOURCES

Dedicated Deribit Resources for Cryptosheets:

  1. 👉 Web App / Add-in API Console: https://app.cryptosheets.com/#/browse/deribit
  2. 👉 Docs Portal & Tutorials: https://docs.cryptosheets.com/providers/deribit
  3. 👉 YouTube Channel Examples:
    https://www.youtube.com/playlist?list=PLBg8GR5ao-awZ5ukolzMhckE34AFSMwzj

Cryptosheets is free to sign up for and start using now….
What are you waiting for?
👉 Get crypto data in 30 seconds or less

Get the Excel add-in now

Get the Googlesheets add-in now

IMPORTANT:
Existing
Deribit Users Get 20% discounts on any Cryptosheets paid subscription! Just follow these links

👉 Get the Deribit User Discount for Cryptosheets Subscriptions!

________________________________________________________________

Troubleshooting

Tutorials

ADDITIONAL RESOURCES

Website | WebApp | Docs Portal | Excel | Googlesheets | Help Center | Blog | Twitter | YouTube

TAGS: portfolio, deribit API, options, futures, crypto API, crypto portfolio tracker, derivatives, portfolio management, portfolio analysis, excel, googlesheets, google sheet, option greek, crypto options, blockchain options, crypto hedging

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store