Analyzing Bitcoin Options Data with QuestDB

Yitaek Hwang
Yitaek HwangContributor
Tags:
RedditHackerNewsX
QuestDB is a next-generation database for market data. It offers premium ingestion throughput, enhanced SQL analytics that can power through analysis, and cost-saving hardware efficiency. It's open source, applies open formats, and is ideal for tick data.

On October 18, the Securities and Exchange Commission (SEC) approved the New York Stock Exchange to list and trade options tied to three spot bitcoin ETFs including Fidelity, Ark21Shares, and Grayscale. CBOE Global Markets also followed suit by listing its first cash-settled Bitcoin ETF options on December 2.

We now have more options for Bitcoin investors than ever, and it’s an exciting time to start analyzing that public data. And we also have an incoming US regime that appears to be crypto-friendly, and a heckuva bull run that's pushed Bitcoin to new all-time highs.

What does the future have in-store for Bitcoin? Who knows - but with this data and QuestDB, in just a few minutes, you'll be well-equipped to find out.

Using the Deribit API

To begin, we'll need some data. Our ideal source will be both public and accessible via an API, at little (or preferably no) cost. For this, we'll use Deribit's public API. What's Derebit?

Deribit is a cryptocurrency derivatives platform. Launched in June 2016 after two years of dedicated development, Deribit appeared as the world's first cryptocurrency options exchange.

Even though Deribit is not licensed to operate in the US, we can still grab data from its public endpoint. Let’s start out by exploring which Bitcoin options are available for us to consume:

import requests


BASE_URL = "https://www.deribit.com/api/v2"


def get_options_data():  
   # Endpoint to get the available Bitcoin options  
   endpoint = "/public/get_instruments"  
   params = {  
       "currency": "BTC",  
       "kind": "option"  # Fetch only options instruments  
   }


   response = requests.get(BASE_URL + endpoint, params=params)


   if response.status_code == 200:  
       data = response.json()  
       if data["result"]:  
           print("Available Options Instruments:")  
           for option in data["result"]:  
               print(f"Option Symbol: {option['instrument_name']}, Type: {option['option_type']}")  
       else:  
           print("No options data available.")  
   else:  
       print("Error fetching options data:", response.status_code)


if __name__ == "__main__":  
   get_options_data()

This script fetchs and analyzes Bitcoin options trade data. It uses the requests library to retrieve the latest trades for a specified option symbol and limit. Upon a successful API call, it extracts key details like timestamp, price, and direction.

This will list our instruments like:

Option SymbolType
BTC-9DEC24-88000-Ccall
BTC-9DEC24-88000-Pput

The format is BTC-<DATE>-<PRICE>-C/P. We'll unpack the option symbol later in the article, but for now let’s just pick an option and grab some data points:

import requests  
import time


BASE_URL = "https://www.deribit.com/api/v2"


def get_option_trade_history(option_symbol, limit=10):  
   endpoint = "/public/get_last_trades_by_instrument"  
   params = {  
       "instrument_name": option_symbol,  
       "count": limit,  
   }


   response = requests.get(BASE_URL + endpoint, params=params)


   if response.status_code == 200:  
       data = response.json()  
       trades = data.get("result", {}).get("trades", [])  
        
       if trades:  
           print(f"Trade History for {option_symbol}:n")  
           for trade in trades:  
               # Extract details from each trade  
               timestamp = trade.get('timestamp')  
               iv = trade.get('iv')  
               price = trade.get('price')  
               direction = trade.get('direction')  
               index_price = trade.get('index_price')  
               amount = trade.get('amount')  
               mark_price = trade.get('mark_price')  
               contracts = trade.get('contracts')


               # Format timestamp  
               timestamp_str = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(timestamp / 1000)) if timestamp else "N/A"


               # Print trade details  
               print(f"Timestamp: {timestamp_str}, Price: {price}, IV: {iv}, Direction: {direction}, "  
                     f"Index Price: {index_price}, Amount: {amount}, Mark Price: {mark_price}, Contracts: {contracts}n")  
       else:  
           print(f"No trade history available for {option_symbol}.")  
   else:  
       print(f"Error fetching trade history for {option_symbol}: {response.status_code}")  
       print(f"Response: {response.text}")


if __name__ == "__main__":  
   option_symbol = "BTC-9DEC24-102000-C"  
   limit = 10


   get_option_trade_history(option_symbol, limit)

The script defines a function, get_option_trade_history, which constructs a request to retrieve the last trades for the given option symbol and limit. That options symbol is BTC-9DEC24-102000-C, or: a call of $102,000USD on December 9, 2024.

Upon receiving a successful response, the script extracts key trade details such as:

  • timestamp
  • price
  • implied volatility (IV)
  • direction
  • index price
  • amount
  • mark price
  • contracts

It formats the timestamp for readability and prints the trade information. If no trades are found or if there's an error in the API call, appropriate messages are displayed.

The script runs the function on the symbol and limit when executed, allowing us to easily review recent trade data. It will print out something like the following - keep scrolling to the right to see the full table:

TimestampPriceIVDirectionIndex PriceAmountMark PriceContracts
2024-12-08 19:05:540.00147.5sell99736.330.10.001124180.1
2024-12-08 15:20:110.001145.08sell99567.810.10.001212240.1
2024-12-08 15:07:240.001245.95sell99544.180.20.001126080.2

Ingesting Data into QuestDB

Instead of just printing the data, we'll update our script to send it to QuestDB using a new insert_into_questdb function, which formats the timestamp appropriately.

Not running? Check out our quick start guide.

For this, we start QuestDB via Docker:

docker run \
  -p 9000:9000 \
  -p 9009:9009 \
  -p 8812:8812 \
  -p 9003:9003 \
  questdb/questdb:8.2.1

We will then use the QuestDB Python client library so let’s install that first:

pip install questdb

After that, we'll import that library and write a little helper function to publish data into QuestDB:

from questdb.ingress import Sender, TimestampNanos


…


def insert_into_questdb(trade_data):  
   try:  
       conf = "http::addr=localhost:9000;username=admin;password=quest;"  
       with Sender.from_conf(conf) as sender:  
           for trade in trade_data:  
               # Send the row to QuestDB  
               sender.row(  
                   TABLE_NAME,  
                   symbols={'option_symbol': trade["option_symbol"], 'direction': trade["direction"]},  
                   columns={  
                       "price": trade["price"],  
                       "index_price": trade["index_price"],  
                       "amount": trade["amount"],  
                       "mark_price": trade["mark_price"],  
                       "contracts": trade["contracts"]  
                   },
                   # Wrap nanos to match QuestDB's default timestamp format
                   at=TimestampNanos(trade["timestamp"] * 1_000_000)  
               )  
           sender.flush()

The insert_into_questdb function sends trade data to QuestDB for storage. It establishes a connection using our desired configuration and iterates through the provided trade data.

For each trade, it sends relevant details — such as option symbol, price, index price, amount, mark price, and contracts — along with a timestamp formatted in nanoseconds. After processing all trades, it flushes the sender to ensure the data is committed to QuestDB.

We'll start with the full code, then break it down in an explanation:

import requests  
import time  
from questdb.ingress import Sender, TimestampNanos


BASE_URL = "https://www.deribit.com/api/v2"  
TABLE_NAME = "btc_option_trades"


# Function to fetch last trades for a specific option  
def get_option_trade_history(option_symbol, limit=1000):  
   endpoint = "/public/get_last_trades_by_instrument"  
   params = {  
       "instrument_name": option_symbol,  # Specify the option symbol like BTC-9DEC24-88000-P  
       "count": limit,  # Limit the number of trades returned (e.g., 10, 100, etc.)  
   }


   print(f"Request Parameters:nInstrument: {option_symbol}nLimit: {limit}")


   response = requests.get(BASE_URL + endpoint, params=params)


   if response.status_code == 200:  
       data = response.json()  
       trades = data.get("result", {}).get("trades", [])  
        
       if trades:  
           print(f"Trade History for {option_symbol}:n")  
           trade_data = []


           for trade in trades:  
               timestamp = trade.get('timestamp')  
               iv = trade.get('iv')  
               price = trade.get('price')  
               direction = trade.get('direction')  
               index_price = trade.get('index_price')  
               amount = trade.get('amount')  
               mark_price = trade.get('mark_price')  
               contracts = trade.get('contracts')


               trade_data.append({  
                   "timestamp": timestamp,  
                   "option_symbol": option_symbol,  
                   "price": price,  
                   "iv": iv,  
                   "direction": direction,  
                   "index_price": index_price,  
                   "amount": amount,  
                   "mark_price": mark_price,  
                   "contracts": contracts  
               })


           # Insert data into QuestDB  
           insert_into_questdb(trade_data)  
       else:  
           print(f"No trade history available for {option_symbol}.")  
   else:  
       print(f"Error fetching trade history for {option_symbol}: {response.status_code}")  
       print(f"Response: {response.text}")


def insert_into_questdb(trade_data):  
   try:  
       conf = "http::addr=localhost:9000;username=admin;password=quest;"  
       with Sender.from_conf(conf) as sender:  
           for trade in trade_data:  
               sender.row(  
                   TABLE_NAME,  
                   symbols={'option_symbol': trade["option_symbol"], 'direction': trade["direction"]},  
                   columns={  
                       "price": trade["price"],  
                       "index_price": trade["index_price"],  
                       "amount": trade["amount"],  
                       "mark_price": trade["mark_price"],  
                       "contracts": trade["contracts"]  
                   },  
                   at=TimestampNanos(trade["timestamp"] * 1_000_000)  
               )  
           sender.flush()


           print("Data successfully inserted into QuestDB!")  
            
   except Exception as e:  
       print(f"Error inserting data into QuestDB: {e}")


if __name__ == "__main__":  
   option_symbol = "BTC-9DEC24-102000-C"  # Replace with any instrument symbol you get  
   limit = 1000  # Number of trades to fetch (you can adjust this value)


   get_option_trade_history(option_symbol, limit)

Our above starter script defines two main functions:

  • get_option_trade_history: Fetches the last trades for our chosen Bitcoin options symbol. It then constructs an API request, processes the response, and extracts key trade details such as timestamp, price, and direction. If trades are found, it compiles the data into a list for further processing.

  • insert_into_questdb: Takes the compiled trade data and sends it to QuestDB for storage. It establishes a connection, formats the timestamp in nanoseconds, and inserts relevant trade details. After all trades are sent, it flushes the sender to ensure the data is committed.

After we run this code, we can see our data arrive into QuestDB:

QuestDB Data

Alright, we've got some juicy market data into QuestDB.

What now?

Querying the data

Let's put together some powerful queries to see what we can learn.

We'll look at a few different window functions.

Moving average

To calculate a moving average price of the last 10 trades, we can set a ROWS-based frame:

Computing a moving average in QuestDB
SELECT
    timestamp,
    option_symbol,
    price,
    avg(price) OVER (
        PARTITION BY option_symbol
        ORDER BY timestamp
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS moving_avg_price_last_10
FROM btc_option_trades
WHERE option_symbol = 'BTC-9DEC24-102000-C'
ORDER BY timestamp;

For each trade of the given option_symbol's, this query averages the current and the previous 9 trades (10 total) to provide a rolling average price:

timestampoption_symbolpricemoving_avg_price_last_10
2024-12-06T20:02:12.186000ZBTC-9DEC24-102000-C0.0140.014
2024-12-06T20:02:35.032000ZBTC-9DEC24-102000-C0.01350.01375
2024-12-06T20:04:46.643000ZBTC-9DEC24-102000-C0.01350.0136666666666
2024-12-06T20:07:12.377000ZBTC-9DEC24-102000-C0.0130.0135

5-minute rolling average

Time-series databases love time.

Let's look at a 5-minute rolling average of the price for our chosen option symbol:

Computing a 5-minute rolling average in QuestDB
SELECT
    timestamp,
    option_symbol,
    price,
    avg(price) OVER (
        PARTITION BY option_symbol
        ORDER BY timestamp
        RANGE BETWEEN '5' MINUTE PRECEDING AND CURRENT ROW
    ) AS avg_price_last_5m
FROM btc_option_trades
WHERE option_symbol = 'BTC-9DEC24-102000-C'
ORDER BY timestamp;

For each trade, QuestDB finds all trades from the same option_symbol that occurred in the last 5 minutes (relative to the current row’s timestamp) and calculates the average price over that interval:

timestampoption_symbolpriceavg_price_last_5m
2024-12-06T20:02:12.186000ZBTC-9DEC24-102000-C0.0140.014
2024-12-06T20:02:35.032000ZBTC-9DEC24-102000-C0.01350.01375
2024-12-06T20:02:35.032000ZBTC-9DEC24-102000-C0.01350.0136666666666
2024-12-06T20:04:46.643000ZBTC-9DEC24-102000-C0.01350.013625

Separate direction, summarize volume

Now, how can we compare the volume difference between buy and sell trades?

A rolling 15-minute window of traded amount could help:

Comparing buy and sell volume
SELECT
    timestamp,
    option_symbol,
    direction,
    amount,
    sum(amount) OVER (
        PARTITION BY option_symbol, direction
        ORDER BY timestamp
        RANGE BETWEEN '15' MINUTE PRECEDING AND CURRENT ROW
    ) AS amount_15m
FROM btc_option_trades
ORDER BY timestamp;

For each row, we take trades from the same option_symbol and direction in the past 15 minutes and sums their amounts. This can help reveal short-term buying or selling pressure trends:

timestampoption_symboldirectionamountamount_15m
2024-12-06T20:02:12.186000ZBTC-9DEC24-102000-Cbuy0.60.6
2024-12-06T20:02:35.032000ZBTC-9DEC24-102000-Csell0.70.7
2024-12-06T20:02:35.032000ZBTC-9DEC24-102000-Csell0.71.4
2024-12-06T20:04:46.643000ZBTC-9DEC24-102000-Csell0.62.0

Extending our script

You know that old saying "the riper the berry, the sweeter the juice"? Well, in capital markets, the more data we have, the more we can learn. Let's see how we can ripen the berry, as it were.

Data fetch intervals

Our demo only pulls in 10000 rows of data. To ingest more data, we can pull the data in chunks by running our script on an interval. The public APIs are rate-limited, to what extent is not immediately clear in the documentation. However, an agreeable amount of data appears to be 1000 rows every 60 seconds.

To handle that, we can update our script to look something like so:

from datetime import datetime


...


def continuous_data_collection(option_symbol, limit=1000, interval_seconds=60):


    print(f"Starting continuous data collection for {option_symbol}")
    print(f"Fetching {limit} trades every {interval_seconds} seconds")
    
    try:
        while True:
            current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            print(f"\n[{current_time}] Fetching new batch of trades...")
            
            get_option_trade_history(option_symbol, limit)
            
            print(f"Waiting {interval_seconds} seconds until next fetch...")
            time.sleep(interval_seconds)
            
    except KeyboardInterrupt:
        print("\nData collection stopped by user")
    except Exception as e:
        print(f"\nError occurred: {e}")


if __name__ == "__main__":
    option_symbol = "BTC-9DEC24-102000-C"
    continuous_data_collection(
        option_symbol,
        limit=1000,          # Fetch 1000 trades per request
        interval_seconds=60  # Wait 1 minute between requests
    )

This will create a steady flow of fresh data for our target option symbol into QuestDB.

Alternatively, if we are able to get a Deribit API license, we can subscribe to the price and market data websocket to stream data instead. This opens a whole new world of possibilities. And QuestDB is well-suited for that.

Unpacking options data

Above, we chose one option symbol: BTC-9DEC24-102000-C.

Let's break down what each part means:

Underlying Asset (BTC)

  • Indicates that the option is based on Bitcoin
  • We could have chosen Ethereum, or any other provided asset

Expiration Date (9DEC24)

  • Day: 9th
  • Month: December (DEC)
  • Year: 2024 (24)
  • Full date: December 9, 2024

Strike Price (102000)

  • Represents $102,000 USD per BTC
  • This is the price at which the option can be exercised

Option Type (C)

  • C = Call option
  • P = Put option (for other contracts)

So putting it all together:

BTC-9DEC24-102000-C
│   │      │      │
│   │      │      └─ Call option
│   │      └────────  Strike price ($102,000)
│   └─────────────── Expires Dec 9, 2024
└─────────────────── Bitcoin underlying

This represents a Bitcoin call option that expires on December 9, 2024, with a strike price of $102,000. We can use the first script we demonstrated to fetch new options, and plug them as needed.

Summary

This article fetched Bitcoin options data from Deribit’s public API. After that, we stored it in QuestDB for deeper analysis, and then ran a few powerful SQL queries to uncover trends. We also looked at how to parse an option symbol like BTC-9DEC24-102000-C.

By integrating Python code for data retrieval and QuestDB’s powerful analytical window functions, we were able to compute rolling averages, summarize trade directions, and handle continuous data collection — all in just a few minutes and from publicly accessible Bitcoin options data sources.

Want to chat with us? Holler on social media or join in our engaging Community Forum or our public Slack.

For more financial blogs, checkout:

RedditHackerNewsX
Subscribe to our newsletters for the latest. Secure and never shared or sold.