Traders often use spreadsheets to model how derivatives will behave.

While adding live data can be done by copy/pasting data from our trading application, our traders deserve better. Therefore, we have developed an Excel sheet which uses macros to retrieve live data from our API. Using this sheet as a basis, you can easily access live pricing data for instruments on our platform. You can obtain the Excel sheet here. After you’ve Downloaded the sheet, Excel will prompt you to confirm that you wish to enable macros. If you don’t enable the macros, you can still review the sheet, but the data won’t be live.

Using the formulas in Excel

The Excel macros provides several different functions, which you can use in your formulas. The most useful function is probably DERIBIT_INDEX. This function provides the current value of the Deribit index. This index serves as the underlying of all of our futures, and some of our options. To use it in a sheet, type =DERIBIT_INDEX().

An equally interesting function would be DERIBIT_PRICE, which retrieves the price for a specific instrument. For example =DERIBIT_PRICE(“BTC-29JUN18-6000-C”) would result in 0.0475 (at the time of writing). This is the latest trade price for the call option with strike $6000, expiration date june 2018. Similarly, DERIBIT_MID provides the current order book midpoint. The following price information formulas are available:

  • DERIBIT_PRICE provides the latest trade price
  • DERIBIT_MID provides the current order book midpoint
  • DERIBIT_ASK provides the current best ask price
  • DERIBIT_BID provides the current best bid price
  • DERIBIT_MARK_PRICE provides the current mark price
  • DERIBIT_BID provides the current best bid price
  • DERIBIT_24H_VOLUME provides the current best bid price
  • DERIBIT_IV provides the implied volatility for an option (using the current mark price)
  • DERIBIT_ASK_IV provides the implied volatility for an option (using the current best ask)
  • DERIBIT_BID_IV provides the implied volatility for an option (using the current best bid)
  • DERIBIT_DELIVERY_DATE provides the delivery date.

Accessing historic data

When DERIBIT_PRICE or DERIBIT_IV is provided with a date in its second parameter, it will provide the price or implied volatility for that date. For example: =DERIBIT_PRICE(“BTC-29JUN18-6000-C”,DATEVALUE(“2018-06-19 06:00:00”)) will result in 0.0850, the latest trade price for the $6000 call option at jun 19th, at 6 am (UTC).

Generating instrument names

While users can copy and paste instrument names into their Excel sheets, sometimes it is useful to generate instrument names from delivery dates, strikes and option types. To do so, use DERIBIT_OPTION_NAME or DERIBIT_FUTURE_NAME. For example, =DERIBIT_OPTION_NAME(DATEVALUE(“2018-06-19”), 6000, “call”) will produce BTC-19JUN18-6000-C.

Data timeliness

Our Excel macros provide price data with a maximum delay of 15 seconds, although you may need to perform a recalculation using F9 to actually get the latest data. Despite the short delay, we advise users to verify the prices before making a trade, especially on fast moving markets.

Example strategies

When you’ve downloaded the Excel sheet, you’ll find a worksheet with formula examples, and several future and option strategies. Most of these example strategies focus on return on investment, on different settlement prices. Of course, these strategies are just a small sample of the possible alternatives.

AUTHOR(S)

Deribit

We are the best resource for Crypto Derivatives trading. Deribit is not available in the United States or other restricted countries.

RECENT ARTICLES