# 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**
#### 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.
{% hint style="info" %}
* Table names are case-insensitive
* Invalid table names will result in an error
* Only whitelisted tables are accessible
{% endhint %}
{% columns %}
{% column %}
#### 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
{% endcolumn %}
{% column %}
Forbidden Operations
* ❌ `INSERT`, `UPDATE`, `DELETE` statements
* ❌ `DROP`, `CREATE`, `ALTER` statements
* ❌ `UNION` operations
* ❌ Stored procedures (`EXEC`, `CALL`)
* ❌ Comments in SQL (automatically stripped)
{% endcolumn %}
{% endcolumns %}
{% tabs %}
{% tab title="Request" %}
```json
{
"api_key": "YOUR_API_KEY",
"sql": "SELECT * FROM RawOptionTrades LIMIT 10"
}
```
{% endtab %}
{% endtabs %}
#### Code Examples
{% tabs %}
{% tab title="cURL" %}
```json
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"
}'
```
{% endtab %}
{% tab title="Python" %}
```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"))
```
{% endtab %}
{% tab title="Javascript" %}
```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);
});
```
{% endtab %}
{% endtabs %}
#### Sample SQL Queries
{% tabs %}
{% tab title="Basic Data Retrieval" %}
```sql
SELECT * FROM RawOptionTrades LIMIT 100
```
{% endtab %}
{% tab title="Filter by Symbol" %}
```sql
SELECT * FROM RawOptionTrades
WHERE symbol = 'AAPL'
ORDER BY timestamp DESC
LIMIT 50
```
{% endtab %}
{% tab title="Aggregate Data" %}
```sql
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
```
{% endtab %}
{% tab title="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
{% endtab %}
{% endtabs %}
| 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/5bf419828984e605fe3ba2e7_proposed_opra_trade_codes.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**
{% tabs %}
{% tab title="200" %}
```json
{
"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"
}
]
}
```
{% endtab %}
{% tab title="400 - Invalid API Key" %}
```json
{
"status": "ERROR",
"errorMsg": "API Key is required"
}
```
{% endtab %}
{% tab title="400 - Invalid SQL" %}
```json
{
"status": "ERROR",
"errorMsg": "Invalid SQL query: Only SELECT queries are allowed"
}
```
{% endtab %}
{% tab title="400 - Missing SQL" %}
```json
{
"status": "ERROR",
"message": "SQL query is required in request body"
}
```
{% endtab %}
{% endtabs %}
###
### 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](/websocket-data-api/realtime-option-data-api.md).
**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
##
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.optiondata.io/http-data-api/historical-option-data-api.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.