Historical Option Data API

Option Trades API Documentation

Overview

The Option Trades API provides secure access to historical option trading data through SQL queries.

Request

Base URL

https://api.optiondata.io

POST /api-portal/historical-trades-by-sql

Description: Execute secure SELECT queries against option trades data.

Headers

Name	Value
Content-Type	application/json

Body

Name	Type		Description
api_key	string	required	Get it from https://portal.optiondata.io
sql	string	required	The sql query.

Table Name

The API provides access to the following tables:

1. RawOptionTrades - Table that contains all the historical trades.

2. RawOptionTradesMaxDateOnlyMV a materialized view of the RawOptionTrades table containing only data for the most recent date. Query this view for faster access to the latest option trades.

• Table names are case-insensitive

• Invalid table names will result in an error

• Only whitelisted tables are accessible

SQL Query Restrictions

Allowed Operations

• ✅ SELECT statements only

• ✅ Standard SQL functions (COUNT, SUM, AVG, etc.)

• ✅ WHERE clauses with filtering

• ✅ ORDER BY and LIMIT clauses

• ✅ GROUP BY clauses

Forbidden Operations

• ❌ INSERT, UPDATE, DELETE statements

• ❌ DROP, CREATE, ALTER statements

• ❌ UNION operations

• ❌ Stored procedures (EXEC, CALL)

• ❌ Comments in SQL (automatically stripped)

Request

{
  "api_key": "YOUR_API_KEY",
  "sql": "SELECT * FROM RawOptionTrades LIMIT 10"
}

Code Examples

cURL

curl -X POST https://your-api-domain.com/api/option-trades/sql \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT symbol, COUNT(*) as trade_count FROM RawOptionTrades WHERE symbol = '\''AAPL'\'' GROUP BY symbol LIMIT 10"
  }'

Python

import http.client

conn = http.client.HTTPSConnection("api.optiondata.com")
payload = 'api_key=YOUR_API_KEY&sql=SELECT%20*%20FROM%20RawOptionTrades%20LIMIT%201'
headers = {
  'Content-Type': 'application/x-www-form-urlencoded'
}
conn.request("POST", "/api-portal/historical-trades-by-sql", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

Javascript

const axios = require('axios');
const qs = require('qs');
let data = qs.stringify({
  'api_key': 'cus_SfodvTgA4ugKLh',
  'sql': 'SELECT * FROM RawOptionTrades LIMIT 1' 
});

let config = {
  method: 'post',
  maxBodyLength: Infinity,
  url: 'https://api.tradingflow.com/api-portal/historical-trades-by-sql',
  headers: { 
    'Content-Type': 'application/x-www-form-urlencoded'
  },
  data : data
};

axios.request(config)
.then((response) => {
  console.log(JSON.stringify(response.data));
})
.catch((error) => {
  console.log(error);
});

Sample SQL Queries

Basic Data Retrieval

SELECT * FROM RawOptionTrades LIMIT 100

Filter by Symbol

SELECT * FROM RawOptionTrades 
WHERE symbol = 'AAPL' 
ORDER BY timestamp DESC 
LIMIT 50

Aggregate Data

SELECT 
  symbol,
  COUNT(*) as total_trades,
  AVG(premium) as avg_premium,
  SUM(volume) as total_volume
FROM RawOptionTrades 
WHERE expiration_date >= '2024-01-01'
GROUP BY symbol
ORDER BY total_trades DESC
LIMIT 20

Date Range Query

SELECT symbol, option_type, strike_price, premium
FROM RawOptionTrades
WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'
AND option_type = 'CALL'
ORDER BY premium DESC

Name	Type	Description
id	string	unique id for this option trade record
symbol	string	Ticker Symbol (TSLA, MSFT, etc...)
date	string	the date the trade was executed, the date format is YYYY-MM-DD
time	string	The time the trade was created. Format is YYYY-MM-DD HH:MM:SS
put_call	string	Indicates whether this trade is PUT or CALL
strike	float	Strike price of the option trade
expiration_date	string	The date on which the Option expires. The Option becomes invalid after this date and cannot be exercised ex: 2022-04-05
option_activity_type	string	Please refer to https://assets.website-files.com/5ba40927ac854d8c97bc92d7/5bf419828984e605fe3ba2e7_proposed_opra_trade_codes.pfd​https://assets.website-files.com/5ba40927ac854d8c97bc92d7/628bc18445cf7ec41d21ddfa_OPRA%20Pillar%20Output%20Specification.pdf
underlying_type	string	Indicates underlying is Common Stock, ETF, ETN, etc.
oi	integer	Open Interest. The total number of this options contract that are still open.
size	integer	Total order size (either of the 1 trade for the trade of SINGLE or REPEATED, or the sum of trade sizes for a AGGREGATED trade)
price	float	Last price of a trade, or last price of last trade in a sweep.
underlying_price	float	Current stock price of the underlying asset
bid	float	Option contract best bid
bid_size	integer	Option contract bid size on exchange with best bid
ask_size	integer	Option contract bid size on exchange with best ask
ask	float	Option contract best ask
iv	float	Implied volatility (IV) is an estimate of the future volatility of the underlying stock based on options prices.

premium	float	Cost in dollar of the entire sweep or block option trade
sentiment	string	BULLISH, BEARISH, or NEUTRAL The sentiment is estimated based on whether the trade was executed at the bid, ask, or spot price.
trade_count	integer	Number of trades involved in the sweep.
expiry_days	integer	The days left to expiration date.
delta	float	Delta is a measure of the change in an option's price
vega	float	Vega is a Greek that measures how sensitive an option's price is to changes in the implied volatility of the underlying stock
exchange	string	Exchange where the trade executed
daily_volume	integer	Day volume for this option contract including this trade
earning_date	string	next earning date for the underlying asset.
updated_timestamp	string	the UTC timestamp that this data record is created in our system
theta	float	measures the rate at which an option's price decreases as it approaches its expiration date, commonly referred to as "time decay."
rho	float	measures the sensitivity of an option’s price to changes in interest rates.
market_cap	Int	market capitalization, company's total market value, represented in dollars, and calculated by multiplying the current share price by the total number of outstanding shares
option_symbol	string	Option Contract Symbol, for example:TSLA240430P00508000, this refers to a put option contract with a strike price of $508 and an expiration date of April 30, 2024.

Response

200

{
    "status": "SUCCESS",
    "data": [
        {
            "id": "m8fzdi4i",
            "date": "2024-04-29",
            "size": 1,
            "price": 1.5899999,
            "premium": 159,
            "bid": 1.5799999,
            "ask": 1.5899999,
            "time": "2024-04-29 16:08:00",
            "symbol": "TSLA",
            "put_call": "PUT",
            "strike": 508,
            "expiration_date": "2024-04-30",
            "underlying_type": "",
            "option_activity_type": "AUTO",
            "oi": 6227,
            "underlying_price": 510.21,
            "sentiment": "BEARISH",
            "expiry_days": 1,
            "side": "ASK",
            "moneyness": "OTM",
            "iv": 0.049,
            "delta": -0.04,
            "option_symbol": "TSLA240430P00508000",
            "gamma": 0.0663,
            "daily_volume": 84891,
            "earning_date": "",
            "dex": 4,
            "dei": 0,
            "trade_count": 1,
            "updated_timestamp": "1742392278017",
            "ask_size": 39450,
            "bid_size": 42044,
            "exchange": "XBOX",
            "vega": 0.087937,
            "theta": -0.591958,
            "rho": -0.003759,
            "market_cap": "0"
        }
    ]
}

400 - Invalid API Key

{
  "status": "ERROR",
  "errorMsg": "API Key is required"
}

400 - Invalid SQL

{
  "status": "ERROR",
  "errorMsg": "Invalid SQL query: Only SELECT queries are allowed"
}

400 - Missing SQL

{ 
  "status": "ERROR",
  "message": "SQL query is required in request body"
}

FAQ

Q: What database technology do you use?

A: We use ClickHouse as our backend query engine.

Q: What is the rate limit?

A: There are no overall usage caps; however, there is a soft rate limit of 1 request per second. This limit may be adjusted temporarily to ensure system stability and availability for all users.

Q: When is the data updated?

A: Data is refreshed in near real-time during market hours. However, in accordance with OPRA regulations, the data is delayed by 15 minutes. This makes the API ideal for historical analysis. For live data, please use our Realtime Option Data API Realtime Option Data API.

Q: How far back does your data go?

A: Our historical data is available starting from February 28, 2025.

Q: Is the historical option data modified or aggregated?

A: No, the historical option data API provides only raw, unmodified data.Unlike the real-time API which offers both AGGREGATED and RAW modes, the historical API contains the original trade records exactly as they were executed.This means:

• Each trade record represents the actual individual transaction

• No consolidation of simultaneous trades

• All trade_count values are 1 (representing single trades)

• No algorithmic modifications or aggregations applied

If you need aggregated historical data for analysis, you can use SQL functions like SUM(), COUNT(), and GROUP BY in your queries to create your own aggregations based on your specific requirements.

Q: What are the refund and trial policies for different APIs?

Real-time Option Data API:

• 14-day free trial available

• 30-day money back guarantee

• No questions asked refund policy

Historical Option Data API:

• No free trial available

• No refund policy (all sales final)

• Contact support for any technical issues