Historical Option Trades API (SQL)

The Option Trades API provides secure access to historical option trading data through SQL queries. Every visible trade is at least 15 minutes old, measured from its execution timestamp. Paid (active) subscriptions can query only the past 15 days, a rolling 360-hour window measured at query execution. Older trade rows are excluded before SQL expressions, joins, subqueries, and aggregations run, even when no date filter is supplied. A query spanning the boundary includes only eligible trade rows. An older-only row query returns an empty result; aggregate queries retain normal SQL empty-input behavior. Trial access and synthetic test-mode samples are unchanged.

Endpoint

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

Execute secure SELECT queries against option trades data.

Headers

The bearer header takes precedence over body api_key. A malformed or unsupported Authorization header is rejected rather than falling back to the body key. JSON and form-encoded request bodies are supported.

Request Body

Data Tables

RawOptionTrades

This table contains stored individual trade records. Availability is subject to the access window and coverage limitations below.

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 (rejected, not 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" \
  -H "Authorization: Bearer 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 today() - INTERVAL 7 DAY AND today()
AND symbol = 'AAPL'
GROUP BY date, symbol, put_call
ORDER BY date DESC, total_premium DESC
LIMIT 50

Review Response

This is an illustrative response with fixed sample timestamps, not a query for currently available trades.

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
        }
    ],
    "meta": {
        "entitlement": "active",
        "row_limit": 10000,
        "capped": false,
        "minimum_data_delay_minutes": 15,
        "lookback_days": 15
    }
}

Errors

Invalid API Key

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

Invalid SQL

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

Invalid query fields or expressions (422 Unprocessable Content)

{
  "status": "ERROR",
  "errorCode": "INVALID_QUERY",
  "reason": "UNKNOWN_IDENTIFIER",
  "errorMsg": "The query references a column, alias, or table identifier that is not available."
}

Use reason to correct the query without relying on database-internal error text. Possible values are UNKNOWN_IDENTIFIER, UNKNOWN_FUNCTION, TYPE_MISMATCH, NUMERIC_OVERFLOW, SYNTAX_ERROR, INVALID_AGGREGATION, INVALID_ARGUMENTS, and INVALID_EXPRESSION.

NUMERIC_OVERFLOW means an arithmetic expression exceeded its supported numeric precision or range. Cast fixed-precision operands before multiplying or aggregating them, for example: SUM(toFloat64(price) * size * 100).

QUERY_TOO_BROAD also uses HTTP 422 when a valid query exceeds server read limits. QUERY_TIMEOUT uses HTTP 504. Unexpected service or infrastructure failures use HTTP 500 with INTERNAL_ERROR.

Unexpected HTTP 500 responses include an X-Request-Id response header. Include that reference when contacting support; it identifies the request without exposing the submitted SQL.

FAQ

Q: What database technology do you use?
A: ClickHouse over HTTPS. Submit SELECT-only SQL to POST /api/historical/sql against whitelisted tables (RawOptionTrades and related MVs).

Q: What are the rate and row limits?
A: The current default is about 60 requests per 60 seconds per customer on this endpoint, for trial and paid users, enforced on a best-effort basis. HTTP 429 includes Retry-After.

Planned, not yet active: trial 5/minute and 100/hour; paid Pro 10/minute and 300/hour. Once enabled, both limits apply per customer across their keys and IPs; reaching either triggers rate limiting. No effective date has been announced. Enterprise limits are contract-specific. See API rate limits for the complete policy.

Row caps are unchanged: trialing Pro users get up to 10 rows per response; paid Pro unlocks up to about 10,000 rows per response (server-configured). Prefer LIMIT, symbol, and date filters. Overly broad queries may return QUERY_TOO_BROAD or timeout.

Q: When is the data updated (delay)?
A: The API enforces a minimum 15-minute delay from each trade’s execution timestamp. At 10:00:00 ET, the newest visible trade is from 09:45:00 ET or earlier. For live prints, use the Realtime WebSocket API.

Q: How far back does historical data go?
A: Paid subscriptions expose trades from the past 15 days only, inclusive of the lower timestamp boundary, with the newest 15 minutes excluded. This is 360 elapsed hours, not 15 trading sessions. Successful paid responses include meta.lookback_days: 15. The latest-date metadata table does not expose older trade rows.

Q: What is typical daily volume on the related realtime stream?
A: On the order of 10M+ prints/day overall. AGGREGATED mode ~4M records/day; RAW mode ~7M records/day. Historical SQL stores the raw tape.

Q: Is the historical option data modified or aggregated?
A: Historical SQL returns individual stored trade records without realtime trade aggregation. RAW does not mean an untouched exchange-event archive: records include normalized and calculated fields, and recovery or backfill can revise enrichment. You can aggregate the available rows with SUM, COUNT and GROUP BY.

Q: How does the free trial work? Can it be extended?
A: Complete qualification with an invitation code, then explicitly activate the 14-day no-card Pro trial when you are ready. It includes Historical SQL (10-row cap), Realtime WebSocket, Option Chain, and Market Structure under one key. Eligible trial users can receive 50% off the first year; the authenticated portal presents the conversion steps. Trials are not auto-extended.

Q: What does the Pro plan include / refunds?
A: One Pro subscription covers all four products. Eligible trial users can receive 50% off the first year, with next steps shown inside the authenticated portal. 30-day money-back guarantee; cancel anytime. Refunds: email support@optiondata.io from your login address within 30 days of purchase.

Historical research and data quality

Q: Do all historical trades retain original milliseconds, and how should I handle unusual times?

A: No. The timestamp column supports milliseconds, but older records were stored at whole-second precision. Missing milliseconds cannot be recovered from those rows alone. Timestamp anomalies and missing coverage require separate verification; do not apply a blanket timezone offset. Contact support@optiondata.io with the symbol, date and a small example, without API credentials.

Q: When is open interest updated?

A: Open interest is updated on a T+1 basis: positions from trading day T become available on the next trading day. OI accompanying a trade generally reflects the prior trading day, not an intraday position count. Historical rows do not include an OI publication timestamp or revision identifier, so exact publication-before-execution timing is not certified for every row.

Q: Are IV, Greeks and underlying prices point-in-time values? Does zero mean missing?

A: These values are saved during trade processing; calculations and reference-price fallbacks may be used when information is unavailable. Historical rows do not expose every field observation time, calculation input or model version, and recovery/backfill may revise enrichment. They are not a certified point-in-time revision archive. Zero can represent an actual zero, missing OI or unavailable/unsuccessful enrichment; there is no universal zero-versus-missing distinction.

Q: Does paid Pro add trade conditions, exchange, sequence IDs or contract history?

A: No. Paid Pro uses the same historical schema. Historical RAW does not expose original conditions such as MLET/SLAN, exchange identifiers, original sequence IDs, correction/cancellation records, contract deliverables or historical ticker mappings. Do not assume those fields are available in a paid export; any separate offering requires written confirmation.

Q: What can I reconstruct from historical RAW, and will it match AGGREGATED?

A: You can calculate trade counts, total contracts and premium (price × size × 100 for standard contracts), infer price side from price/bid/ask, and group by contract and second. The aggregation rules use arithmetic average price/bid/ask, side recalculated from those averages, and maximum OI. Exact realtime parity is not guaranteed: missing prints, timestamp precision, inclusion rules and unavailable tie-order identifiers can change results. Omitted conditions cannot be reconstructed. Deliverables/multiplier metadata is absent, so the standard premium formula is not assured for adjusted contracts.

Q: Does Pro include a complete, resumable full-history export?

A: No. Standard paid SQL access is limited to the rolling 15-day window; a complete full-history export is not included. Coverage gaps and delisted-symbol completeness are not universally certified. Preserve identical-row multiplicity: a content hash is not a unique execution ID, and timestamp-only pagination can skip ties at a response limit. A separate export requires written scope, coverage, resumption/checksum guarantees, throughput and pricing; do not treat request limits as a delivery SLA.

Q: Can I keep raw data or detailed feature caches after cancellation?

A: Under the standard Terms, raw Data must be deleted when the subscription terminates. Detailed feature caches are not automatically exempt. Qualifying derived outputs must not recreate the original Data or a reasonable substitute. Query access and retention rights are separate; any extended retention requires a written agreement, and no separate retention-license price is published.

Q: Can I publish derived signal probabilities without sharing raw data?

A: Not automatically. Not publishing raw rows does not by itself establish publication rights. The Terms distinguish qualifying non-reconstructable derived outputs from Data and restrict public/commercial use. Contact support@optiondata.io with the proposed outputs and use for written clarification before publication.

See the Terms of Service for licensing and API rate limits for current and planned request limits. Customer-specific promotions do not expand data access or licensing rights.