Get 50% off on all plans for the first year – contact sales for a promotional code.

Historical Option Data API

Query historical option trades and market data with SQL

Database Access

Historical Data API

$299/ month

Direct SQL access to historical data

ClickHouse high-performance queries

Custom analytics & filtering

Technical support included

Performance

•Always use LIMIT to control result size

•Filter by symbol (primary key) first

•Add date partition filter for speed

Indexed Columns

•Primary: symbol, time

•Indexed: expiration_date, strike

•Partition: date

Patterns

•SELECT max(date) AS latest_date FROM RawOptionTrades

•WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL'

•ORDER BY time DESC LIMIT 20

Avoid

•Queries without LIMIT

•SELECT * (select specific columns)

•Full table scans without filters

Demo Mode

Sign in to execute queries. This portal returns only the first 10 rows; for unlimited data, subscribe to a paid plan and use the API.

Quick Examples

Queries are executed against our ClickHouse database.

Demo mode: Showing 2 of limited results. For full, unlimited data, subscribe to a paid plan and use the API.

Showing 1-2 of 2 results

Rows:

1 / 1

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/x-www-form-urlencoded

Body

Name	Type	Required	Description
api_key	string	required	Get it from https://optiondata.io
sql	string	required	The SQL query to execute.

Table Name

The API provides access to the following table:

RawOptionTrades - This table contains all the historical trades.

Table names are case-insensitive.
Invalid table names will result in an error.
Only whitelisted tables are accessible.

SQL 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 Example

api_key=YOUR_API_KEY&sql=SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask FROM RawOptionTrades WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL' ORDER BY time DESC LIMIT 20

Code Examples

curl -X POST https://api.optiondata.io/api-portal/historical-trades-by-sql \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "api_key=YOUR_API_KEY" \
  --data-urlencode "sql=SELECT date, time, symbol, put_call, strike, expiration_date, size, price, bid, ask FROM RawOptionTrades WHERE date = (SELECT max(date) FROM RawOptionTrades) AND symbol = 'AAPL' ORDER BY time DESC LIMIT 20"

Sample SQL Queries

SELECT max(date) AS latest_date
FROM RawOptionTrades

Fields

Name	Type	Description
date	Date	The date the trade was executed, format: YYYY-MM-DD. This column is the partition key.
time	DateTime64(3, 'America/New_York')	The timestamp of the trade with millisecond precision in America/New_York timezone. Format: YYYY-MM-DD HH:MM:SS.mmm. Part of primary key.
symbol	LowCardinality(String)	Ticker symbol (TSLA, AAPL, SPY, etc.). Part of primary key - filter by symbol for best performance.
put_call	Enum8('CALL' = 1, 'PUT' = 2)	Option type: 'CALL' or 'PUT'.
strike	Decimal(9,3)	Strike price of the option contract. Indexed column.
expiration_date	Date	The date on which the option expires, format: YYYY-MM-DD. Indexed column.
size	UInt32	Number of contracts traded in this transaction.
price	Decimal(9,4)	Trade price per contract.
bid	Decimal(9,4)	Best bid price at time of trade.
ask	Decimal(9,4)	Best ask price at time of trade.
underlying_price	Decimal(9,4)	Price of the underlying stock at time of trade.
iv	Decimal(9,4)	Implied volatility (decimal, e.g. 0.35 = 35%).
delta	Decimal(9,4)	Option delta (-1 to 1).
gamma	Decimal(9,6)	Option gamma.
oi	UInt32	Open Interest - total number of outstanding option contracts.
dei	Decimal(9,4)	Delta exposure impact relative to daily stock volume.

Derived Fields

These fields are not stored in RawOptionTrades. You can derive them in your application logic after receiving the raw data.

Column	Status	Reason	Formula
id	Derivable	Frontend generates component keys	`sipHash64(concat(time, symbol, strike, price, size))`
trade_count	Derivable	RawOptionTrades stores individual trades only	`1`
expiry_days	Derivable	Simple date arithmetic	`dateDiff('day', date, expiration_date)`
premium	Derivable	Simple multiplication	`toFloat64(price) * size * 100`
option_symbol	Derivable	OCC format from components	`concat(symbol, YYMMDD, P/C, strike*1000)`
moneyness	Derivable	Compare strike vs underlying	`IF strike ~= underlying_price THEN 'ATM' ELSE IF ITM/OTM by put_call`
sentiment	Derivable	Inferred from side + put_call	`IF CALL+BUY='BULLISH', PUT+BUY='BEARISH', etc.`
side	Derivable	Compare price to bid/ask	`IF price > ask THEN 'AASK' ELSE IF price >= ask THEN 'ASK' ...`
daily_volume	Derivable	Aggregate from trades	`SUM(size) OVER (PARTITION BY date, symbol, strike, put_call, expiration_date)`
dex	Derivable	Delta exposure - equivalent shares the dealer must hedge	`delta * size * 100`

Response

{
    "status": "SUCCESS",
    "data": [
        {
            "date": "2025-01-17",
            "time": "2025-01-17 09:30:15.123",
            "symbol": "TSLA",
            "put_call": "CALL",
            "strike": 420.000,
            "expiration_date": "2025-01-24",
            "size": 10,
            "price": 4.2500,
            "bid": 4.2000,
            "ask": 4.3000,
            "underlying_price": 418.5200,
            "iv": 0.4523,
            "delta": 0.5234,
            "gamma": 0.012345,
            "oi": 15234,
            "dei": 0.0012
        },
        {
            "date": "2025-01-17",
            "time": "2025-01-17 09:30:18.456",
            "symbol": "AAPL",
            "put_call": "PUT",
            "strike": 230.000,
            "expiration_date": "2025-02-21",
            "size": 25,
            "price": 2.8900,
            "bid": 2.8500,
            "ask": 2.9200,
            "underlying_price": 232.1500,
            "iv": 0.2845,
            "delta": -0.3521,
            "gamma": 0.008765,
            "oi": 8923,
            "dei": 0.0008
        }
    ]
}

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.
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:

7-day free trial available (extendable upon request).
30-day money-back guarantee.
Contact support for any technical issues.