Historical Option Trades API (SQL)

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

Endpoint

POST https://www.optiondata.io/api/historical/sql

Execute secure SELECT queries against option trades data.

Headers

Request Body

Data Tables

RawOptionTrades

This table contains all the historical trades.

Notes:

• 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)

Schema Definition

Fields

Derived Fields

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

Examples

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

curl -X POST https://www.optiondata.io/api/historical/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"

Python

import http.client

conn = http.client.HTTPSConnection("www.optiondata.io")
payload = "api_key=YOUR_API_KEY&sql=SELECT%20date%2C%20time%2C%20symbol%2C%20put_call%2C%20strike%2C%20expiration_date%2C%20size%2C%20price%2C%20bid%2C%20ask%20FROM%20RawOptionTrades%20WHERE%20date%20%3D%20(SELECT%20max(date)%20FROM%20RawOptionTrades)%20AND%20symbol%20%3D%20'AAPL'%20ORDER%20BY%20time%20DESC%20LIMIT%2020"
headers = {
  'Content-Type': 'application/x-www-form-urlencoded'
}
conn.request("POST", "/api/historical/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': '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"
});

let config = {
  method: 'post',
  maxBodyLength: Infinity,
  url: 'https://www.optiondata.io/api/historical/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

Latest Trading Date

SELECT max(date) AS latest_date
FROM RawOptionTrades

Latest Symbol Trades

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

Symbol Flow Aggregate

SELECT
  symbol,
  put_call,
  COUNT(*) as total_trades,
  SUM(size) as total_contracts,
  ROUND(SUM(toFloat64(price) * size * 100), 2) as total_premium
FROM RawOptionTrades
WHERE date = (SELECT max(date) FROM RawOptionTrades)
AND symbol IN ('AAPL', 'TSLA', 'SPY')
GROUP BY symbol, put_call
ORDER BY total_premium DESC
LIMIT 12

Large Premium Trades

SELECT date, time, symbol, put_call, strike, expiration_date, size, price,
  ROUND(toFloat64(price) * size * 100, 2) as premium
FROM RawOptionTrades
WHERE date = (SELECT max(date) FROM RawOptionTrades)
AND symbol IN ('AAPL', 'TSLA', 'SPY')
AND size >= 50
ORDER BY premium DESC
LIMIT 20

Date Range Rollup

SELECT
  date,
  symbol,
  put_call,
  COUNT(*) as total_trades,
  SUM(size) as total_contracts,
  ROUND(SUM(toFloat64(price) * size * 100), 2) as total_premium
FROM RawOptionTrades
WHERE date BETWEEN '2025-03-03' AND '2025-03-07'
AND symbol = 'AAPL'
GROUP BY date, symbol, put_call
ORDER BY date DESC, total_premium DESC
LIMIT 50

Review Response

Success (200 OK)

{
    "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
        },
        ...
    ]
}

Errors (400 Bad Request)

Invalid API Key

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

Invalid SQL

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

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? A:

• Real-time Option Data API:
  • 14-day free trial available.
  • Historical SQL included; trial SQL responses are capped at 10 rows.
  • Option Chain REST API included for trialing and active realtime users.
  • 30-day money-back guarantee.
  • No questions asked refund policy.
• Historical SQL:
  • Included with the realtime plan.
  • Paid realtime subscriptions unlock larger historical SQL responses.
  • Endpoint: POST https://www.optiondata.io/api/historical/sql.
• Option Chain REST:
  • Included with the realtime plan.
  • Endpoint: POST https://www.optiondata.io/api/option-chain.