Live Deribit Trading Data in Excel

Excel logoTraders 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

=DERIBIT_INDEX()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.

 

BTC Implied Volatility at Historical Low – Time to Buy BTC Options?

.This article is a follow up from Flood‘s great article about our options platform. Flood’s article explained the basic features of BTC options and discussed the effect of implied volatility (IV) on option pricing.

First Implied Volatility Analysis for BTC

Deribit hosts the only liquid crypto options exchange globally and this generates a lot of unique data. This data allowed Flood to present history’s first implied volatility graph for Bitcoin. We updated this graph with some recent data below.

48-hour rolling implied volatility of most liquid deribit options vs bitcoin price

The BTC implied volatility is the expected future volatility of the BTC price that is implied by option prices (premiums) on the Deribit exchange. All other things equal; higher option premiums imply higher implied volatility. For the statistically minded: the implied volatility is the expected annual standard deviation of the BTC price in percentage points.

For the graph we selected the most liquid call and put for every maturity date outstanding and calculated the 48 hour average implied volatility based on all trades in those options. It shows that volatility was highly variable up to December 2017 as the market was still relatively illiquid. Then, in December 2017, IV went up together with the BTC price.

In the equity market, rising stock prices lead to lower implied volatility, as optimism reduces the equity market’s expectation of risk and hence price variation. When equity markets go down, implied equity market volatility tends to go up.

The BTC market shows the opposite pattern. When the Bitcoin price exploded in December, implied volatility went up significantly as traders took the possibility of further price leaps into account. This was a great period to sell volatility (write options and collect the premium).

When the market went down implied volatility came down, especially after March when the options market started to perceive a range bound trading pattern  as more likely. Earlier this June, BTC implied volatility reached a historical low. After the BTC drop last week it went up by a few points but IV has since receded again.

BTC Option Strategy

This low IV might make it attractive to buy options. Call options might be especially interesting (if you’re bullish) because Bitcoin’s implied volatility tends to go up when the price of BTC rises. This would result in a more expensive option and might generate a double boost to a call option price if BTC goes up. Short dated options might be especially interesting if you believe the market is likely to jump up and down in the short term even if it might be range bound in the medium term.

Let’s look at a concrete example: Today the 20th of June at about 3pm CET you can buy a strike 6000, 27th of July ’18 Call option on one Bitcoin for about USD 880.

Options chain

With this option you receive any positive difference between BTC price and 6000 on the expiration date, the 27th of July. This means you will make money if BTC is higher than 6880 in 37 days. You can not lose more than USD 880 however and this effectively protects you against BTC dropping below 6000. The current BTC price is 6626 so by sacrificing USD 254 (USD 6880 – USD 6626), or 3.8% of a Bitcoin, you are effectively protected from BTC dropping under the 6000 level.

Profit and loss - 6000 call option june (on 27 july 2018)

Plans for the Deribit BTC Options Platform

We intend to start talking about our options platform and options strategies a lot over the next few months. We will also introduce many new products going forward. We recently introduced long dated December options. We will soon be introducing options (and futures) on different crypto’s like ETH and BCH.

We are also working on a more formal implied volatility index which is calculated in a similar way as the VIX, the implied volatility index of the S&P 500. This will be a great information source on expected BTC movements. It might even be a good reference asset for an implied volatility futures contract, like the extremely popular VIX futures.

Have a look out our cheat sheet to find out more about options trading.

Don’t hesitate to ask questions in our Telegram group if you want to discuss any topic related to options theory and strategy. We are there to help you out.

Deribit launches December options

Over the last months our option exchange has become significantly more liquid. As this was happening, we received an increased number of customer queries about longer dated options. This is why we decided to introduce the December option yesterday. The underlying of this contract will be our index.

From now on, we will have at least one option with a minimum maturity of 6 months. This means we will launch a March ’19 option before the end of September.

Increase Ticksize

We also increase the ticksize from 0.1 to 0.5 USD to make the order books easier to see. The ticksize from the options went from USD 0.0001 to USD 0.0005.