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
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:
- Background: Showcase the advantages of using Deribit API data with the Cryptosheets add-in
- How to Get Data: Explore different ways to search, find & extract key data
- Examples: Highlight relevant examples using specific API endpoints & formulas
- UPDATE: You can now also access & easily use the Genesis Volatility GraphQL API endpoints (both professional and API LITE versions) in Cryptosheets along with the Deribit API!
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.
=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.
Simple formulas and basic account data can quickly be modeled into dynamic dashboard providing real time market insights and data driven analysis…
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")
👉 Get Open Positions:
=CS.QUERYA("Deribit","Private Get Positions","kind","future","currency","BTC","Authorization","Your Auth Token")
… 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:
- CS.TIME() for refresh
- https://docs.cryptosheets.com/cryptosheets-concepts/data-refresh/
- At the end of your query add “time”, CS.Time(“120”) to refresh every 2 minutes (see example below)
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:
- 👉 Web App / Add-in API Console: https://app.cryptosheets.com/#/browse/deribit
- 👉 Docs Portal & Tutorials: https://docs.cryptosheets.com/providers/deribit
- 👉 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 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
- See FAQ and troubleshooting help topic in the help center here
- Explore troubleshooting tips & tricks in the quick docs section here
Tutorials
- Explore tutorials here
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
