Futures#
This section contains 10 examples for Futures using the onetick-py.
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.
# onetick-py WebAPI configuration for OneTick Cloud
import os
os.environ['OTP_WEBAPI'] = '1'
os.environ['OTP_HTTP_ADDRESS'] = 'https://rest.cloud.onetick.com'
os.environ['OTP_ACCESS_TOKEN_URL'] = 'https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token'
os.environ['OTP_CLIENT_ID'] = '__FILL_IN__'
os.environ['OTP_CLIENT_SECRET'] = '__FILL_IN__'
Calculates Point in Time Trade Snapshot for Futures Product (Futures Chain)#
A specific point in time is selected (e.g. 2024-01-03 12:30:00 Europe/London).
All futures symbols for the
product are retrieved with a symbol pattern (e.g. BRN____ for Brent Crude on ICE Europe Commodities).
A lookback of up to 1 day (86400s) returns the prevailing trade before the selected time, per contract.
import onetick.py as otp
# The snapshot time of interest.
snapshot_time = otp.dt(2024, 1, 3, 12, 30)
# Trade data for the ICE Europe Brent Crude futures chain, looking back up to 1 day for the prevailing trade.
trd = otp.DataSource(db='ICE_EU_COM_SAMPLE', tick_type='TRD', back_to_first_tick=86400)
# Keep only the last (prevailing) tick up to the snapshot time, per contract.
trd = trd.last()
# Merge the prevailing trade for every matching BRN futures contract into a single stream.
merged = otp.merge(
[trd],
symbols=otp.Symbols('ICE_EU_COM_SAMPLE', pattern='BRN____', for_tick_type='TRD')
)
# A zero-length window ending at the snapshot time returns the prevailing values as of that time.
result = otp.run(
merged,
start=snapshot_time,
end=snapshot_time,
timezone='Europe/London'
)
result
| Time | AGGRESSOR_SIDE | BOOK_TYPE | DELETED_TIME | EXCH_TIME | OMDSEQ | PRICE | SIZE | TICK_STATUS | TRADE_ID | TRADE_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 12:30:00 | 1 | 1970-01-01 01:00:00 | 2024-01-03 12:13:23.006875 | 4 | 68.10 | 1 | 0 | 20715248 | LEG | |
| 1 | 2024-01-03 12:30:00 | 1 | 1970-01-01 01:00:00 | 2024-01-03 10:38:45.104576 | 6 | 70.77 | 20 | 0 | 19050498 | LEG | |
| 2 | 2024-01-03 12:30:00 | 1 | 1970-01-01 01:00:00 | 2024-01-02 16:22:11.143823 | 7 | 71.17 | 2 | 0 | 28311280 | LEG | |
| 3 | 2024-01-03 12:30:00 | 1 | 1970-01-01 01:00:00 | 2024-01-03 12:13:23.007387 | 7 | 68.81 | 1 | 0 | 20715260 | LEG | |
| 4 | 2024-01-03 12:30:00 | 1 | 1970-01-01 01:00:00 | 2024-01-03 12:24:40.380385 | 10 | 74.94 | 1 | 0 | 20880021 | LEG | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 26 | 2024-01-03 12:30:00 | 0 | 1970-01-01 01:00:00 | 2024-01-03 12:29:53.944614 | 362 | 74.71 | 1 | 0 | 20963623 | 0 | |
| 27 | 2024-01-03 12:30:00 | 0 | 1970-01-01 01:00:00 | 2024-01-03 12:29:53.944614 | 374 | 75.48 | 1 | 0 | 20963277 | 0 | |
| 28 | 2024-01-03 12:30:00 | 0 | 1970-01-01 01:00:00 | 2024-01-03 12:29:53.944614 | 397 | 74.18 | 1 | 0 | 20963622 | 0 | |
| 29 | 2024-01-03 12:30:00 | 0 | 1970-01-01 01:00:00 | 2024-01-03 12:29:53.944614 | 402 | 75.23 | 1 | 0 | 20963668 | 0 | |
| 30 | 2024-01-03 12:30:00 | 0 | 1970-01-01 01:00:00 | 2024-01-03 12:29:54.326402 | 426 | 75.84 | 1 | 0 | 20966615 | 0 |
31 rows × 11 columns
Return the Futures Chain of contracts for a NYMEX Product from the Symbol Universe#
Filtering with NYMEX Future selects the symbols that correspond to NYMEX Futures.
Additionally filtering on PRODUCT_CODE equal to CL, the NYMEX Product Code for Crude Oil.
import onetick.py as otp
# The Symbol Universe static records are stored in the SYMBOL_UNIVERSE database, STAT tick type.
# The SYMBOL_NAME 'NYMEX Future' groups the NYMEX futures contracts.
data = otp.DataSource(db='SYMBOL_UNIVERSE', tick_type='STAT')
# Filter to the NYMEX Crude Oil product (PRODUCT_CODE 'CL').
data = data.where(data['PRODUCT_CODE'] == 'CL')
# Select the descriptive fields of interest.
data = data[['DB_NAME', 'DB_SYMBOL', 'BSYM', 'NAME', 'SEC_TYPE',
'UNDERLYING_SEC_TYPE', 'PRODUCT_CODE', 'EXPIRATION_DATE']]
# Return first 1000 rows
data = data.limit(1000)
result = otp.run(
data,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC',
symbols='NYMEX Future'
)
result
| Time | DB_NAME | DB_SYMBOL | BSYM | NAME | SEC_TYPE | UNDERLYING_SEC_TYPE | PRODUCT_CODE | EXPIRATION_DATE | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 21:00:00.937 | NYMEX | CL\H35 | CLH35 Comdty | Crude Oil Mar35 | Future | Energy | CL | 20350220 |
| 1 | 2026-06-11 21:00:00.937 | NYMEX | CL\G36 | CLG36 Comdty | Crude Oil Feb36 | Future | Energy | CL | 20360122 |
| 2 | 2026-06-11 21:00:00.937 | NYMEX | CL\J35 | CLJ35 Comdty | Crude Oil Apr35 | Future | Energy | CL | 20350319 |
| 3 | 2026-06-11 21:00:01.137 | NYMEX | CL\M34 | CLM34 Comdty | Crude Oil Jun34 | Future | Energy | CL | 20340522 |
| 4 | 2026-06-11 21:00:01.138 | NYMEX | CL\H34 | CLH34 Comdty | Crude Oil Mar34 | Future | Energy | CL | 20340221 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 123 | 2026-06-11 21:00:15.559 | NYMEX | CL\V35 | CLV35 Comdty | Crude Oil Oct35 | Future | Energy | CL | 20350920 |
| 124 | 2026-06-11 21:00:15.559 | NYMEX | CL\U35 | CLU35 Comdty | Crude Oil Sep35 | Future | Energy | CL | 20350821 |
| 125 | 2026-06-11 21:00:15.658 | NYMEX | CL\G35 | CLG35 Comdty | Crude Oil Feb35 | Future | Energy | CL | 20350122 |
| 126 | 2026-06-11 21:00:15.658 | NYMEX | CL\Z34 | CLZ34 Comdty | Crude Oil Dec34 | Future | Energy | CL | 20341120 |
| 127 | 2026-06-11 21:00:15.659 | NYMEX | CL\M35 | CLM35 Comdty | Crude Oil Jun35 | Future | Energy | CL | 20350522 |
128 rows × 9 columns
Return the first 1000 Futures from the Symbol Universe#
The Symbol Universe groups contracts by a SYMBOL_NAME marker.
The % Future markers cover every database that includes Futures.
A one-day time window returns the futures contracts active in that period.
import onetick.py as otp
# The Symbol Universe static records are stored in the SYMBOL_UNIVERSE database, STAT tick type.
# Query each futures marker symbol and merge the results into one stream.
data = otp.DataSource(db='SYMBOL_UNIVERSE', tick_type='STAT')
data = data[['DB_NAME', 'DB_SYMBOL', 'BSYM', 'NAME', 'PRODUCT_CODE',
'SEC_TYPE', 'UNDERLYING_SEC_TYPE', 'EXPIRATION_DATE']]
# Merge across all marker symbols that end in ' Future' (e.g. 'NYMEX Future', 'CME Future', ...).
merged = otp.merge(
[data],
symbols=otp.Symbols('SYMBOL_UNIVERSE', pattern='% Future', for_tick_type='STAT')
)
# Return first 1000 rows
merged = merged.limit(1000)
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | DB_NAME | DB_SYMBOL | BSYM | NAME | PRODUCT_CODE | SEC_TYPE | UNDERLYING_SEC_TYPE | EXPIRATION_DATE | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 | ABU_DHABI | ADCBF\K26 | Abu Dhabi Commercial Bank,May-2026,Composite | Future | ||||
| 1 | 2026-06-11 | ABU_DHABI | ADIBF\K26 | Abu Dhabi Islamic Bank Futures,May-2026,Composite | Future | ||||
| 2 | 2026-06-11 | ABU_DHABI | ADNOCDISTF\K26 | Abu Dhabi National Oil Company for Distributio... | Future | ||||
| 3 | 2026-06-11 | ABU_DHABI | ADPORTSF\K26 | Abu Dhabi Ports Company Pjsc,May-2026,Composite | Future | ||||
| 4 | 2026-06-11 | ABU_DHABI | ALDARF\K26 | Aldar Properties,May-2026,Composite | Future | ||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-06-11 | BSE | RADI\M26 | RADI Jun26 | RADI | Future | Equity | 20260625 | |
| 996 | 2026-06-11 | BSE | RADI\N26 | RADI Jul26 | RADI | Future | Equity | 20260730 | |
| 997 | 2026-06-11 | BSE | RADI\Q26 | RADI Aug26 | RADI | Future | Equity | 20260827 | |
| 998 | 2026-06-11 | BSE | RBLB\M26 | RBL Bank Jun26 | RBLB | Future | Equity | 20260625 | |
| 999 | 2026-06-11 | BSE | RBLB\N26 | RBL Bank Jul26 | RBLB | Future | Equity | 20260730 |
1000 rows × 9 columns
Return the number of Futures contracts for NYMEX from the Symbol Universe#
Filtering with NYMEX Future selects the symbols that correspond to NYMEX Futures.
NYMEX populates UNDERLYING_SEC_TYPE, allowing Products to be grouped.
import onetick.py as otp
# The Symbol Universe static records are stored in the SYMBOL_UNIVERSE database, STAT tick type.
# The SYMBOL_NAME 'NYMEX Future' groups the NYMEX futures contracts.
data = otp.DataSource(db='SYMBOL_UNIVERSE', tick_type='STAT')
# Count the contracts, grouped by database, security type, underlying security type and product code.
summary = data.agg(
{'CONTRACT_COUNT': otp.agg.count()},
group_by=['DB_NAME', 'SEC_TYPE', 'UNDERLYING_SEC_TYPE', 'PRODUCT_CODE']
)
# Return first 1000 rows
summary = summary.limit(1000)
result = otp.run(
summary,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC',
symbols='NYMEX Future'
)
result
| Time | DB_NAME | SEC_TYPE | UNDERLYING_SEC_TYPE | PRODUCT_CODE | CONTRACT_COUNT | |
|---|---|---|---|---|---|---|
| 0 | 2026-06-12 | NYMEX | Future | Agriculture | CJ | 10 |
| 1 | 2026-06-12 | NYMEX | Future | Agriculture | KT | 10 |
| 2 | 2026-06-12 | NYMEX | Future | Agriculture | TT | 10 |
| 3 | 2026-06-12 | NYMEX | Future | Agriculture | YO | 8 |
| 4 | 2026-06-12 | NYMEX | Future | Energy | 0A | 65 |
| ... | ... | ... | ... | ... | ... | ... |
| 893 | 2026-06-12 | NYMEX | Future | Metal | PAT | 2 |
| 894 | 2026-06-12 | NYMEX | Future | Metal | PL | 14 |
| 895 | 2026-06-12 | NYMEX | Future | Metal | PLM | 6 |
| 896 | 2026-06-12 | NYMEX | Future | Metal | PLT | 2 |
| 897 | 2026-06-12 | NYMEX | Future | Other | LCS | 21 |
898 rows × 6 columns
Trades for Product / Futures Chain#
Return the first 1000 trades for Crude Oil Futures contracts (Futures Chain) trading on NYMEX,
product code CL.
Futures symbols have the structure [Product Code]\[Expiry Month & Year], e.g. CL\N26.
The symbol pattern CL____ selects the futures chain: the product code CL followed by the backslash and the
three-character expiry code (single-character wildcards each match one character, including the backslash).
import onetick.py as otp
# Trade data for the NYMEX Crude Oil futures chain.
trd = otp.DataSource(db='NYMEX', tick_type='TRD')
# Merge every matching CL futures contract into a single stream.
merged = otp.merge(
[trd],
symbols=otp.Symbols('NYMEX', pattern='CL____', for_tick_type='TRD')
)
# Return first 1000 rows
merged = merged.limit(1000)
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | AGGRESSOR_SIDE | BOOK_TYPE | BUY_ORDER_ID | DELETED_TIME | EXCH_TIME | OMDSEQ | PRICE | SELL_ORDER_ID | SIZE | TICK_STATUS | TRADE_ID | TRADE_PERIOD | TRADE_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 00:00:00.030309517 | 1 | 1970-01-01 | 1970-01-01 | 2 | NaN | 2 | 0 | - | LEG | ||||
| 1 | 2026-06-11 00:00:00.032494863 | 0 | 1970-01-01 | 1970-01-01 | 12 | 88.38 | 8064521763074 | 1 | 0 | 112111789 | - | IMP | ||
| 2 | 2026-06-11 00:00:00.032494863 | B | 0 | 8064521763641 | 1970-01-01 | 1970-01-01 | 25 | 86.21 | 1 | 0 | 112111788 | - | IMP | |
| 3 | 2026-06-11 00:00:00.032560523 | 1 | 1970-01-01 | 1970-01-01 | 164 | NaN | 1 | 0 | - | LEG | ||||
| 4 | 2026-06-11 00:00:00.032730261 | 1 | 1970-01-01 | 1970-01-01 | 181 | NaN | 1 | 0 | - | LEG | ||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-06-11 00:00:00.732578731 | 1 | 1970-01-01 | 1970-01-01 | 92 | NaN | 1 | 0 | - | LEG | ||||
| 996 | 2026-06-11 00:00:00.732578731 | 1 | 1970-01-01 | 1970-01-01 | 135 | NaN | 2 | 0 | - | LEG | ||||
| 997 | 2026-06-11 00:00:00.732578731 | 1 | 1970-01-01 | 1970-01-01 | 168 | NaN | 1 | 0 | - | LEG | ||||
| 998 | 2026-06-11 00:00:00.733675483 | 1 | 1970-01-01 | 1970-01-01 | 208 | NaN | 1 | 0 | - | LEG | ||||
| 999 | 2026-06-11 00:00:00.733675483 | 1 | 1970-01-01 | 1970-01-01 | 209 | NaN | 1 | 0 | - | LEG |
1000 rows × 14 columns
Trades for Product / Futures Spreads Chain#
Return the first 1000 trades for Crude Oil Futures Spreads contracts (Futures Spreads Chain) trading on
NYMEX, product code CL.
Futures Spread symbols have the structure
[Product Code]\[Expiry Month & Year]\[Expiry Month & Year], e.g. CL\N26\Z26.
The symbol pattern ‘CL________’ selects the spreads chain: the product code CL followed by eight
characters (both backslashes and the two three-character expiry codes).
import onetick.py as otp
# Trade data for the NYMEX Crude Oil futures spreads chain.
trd = otp.DataSource(db='NYMEX', tick_type='TRD')
# Merge every matching CL spread contract into a single stream.
merged = otp.merge(
[trd],
symbols=otp.Symbols('NYMEX', pattern='CL________', for_tick_type='TRD')
)
# Return first 1000 rows
merged = merged.limit(1000)
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | AGGRESSOR_SIDE | BOOK_TYPE | BUY_ORDER_ID | DELETED_TIME | EXCH_TIME | OMDSEQ | PRICE | SELL_ORDER_ID | SIZE | TICK_STATUS | TRADE_ID | TRADE_PERIOD | TRADE_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 00:00:00.032494863 | 0 | 8064521760428 | 1970-01-01 | 1970-01-01 | 13 | 2.17 | 1 | 0 | 112111790 | - | IMP | ||
| 1 | 2026-06-11 00:00:00.033112153 | 0 | 1970-01-01 | 1970-01-01 | 71 | 5.67 | 8064521715068 | 1 | 0 | 112111803 | - | IMP | ||
| 2 | 2026-06-11 00:00:00.056412059 | B | 0 | 8064521764053 | 1970-01-01 | 1970-01-01 | 81 | 0.51 | 8064521717209 | 1 | 0 | 112111820 | - | - |
| 3 | 2026-06-11 00:00:00.069579129 | S | 0 | 8064521728133 | 1970-01-01 | 1970-01-01 | 0 | 1.50 | 8064521764096 | 1 | 0 | 112111822 | - | - |
| 4 | 2026-06-11 00:00:00.086482261 | B | 0 | 8064521764270 | 1970-01-01 | 1970-01-01 | 11 | 1.88 | 8064521756816 | 1 | 0 | 112111824 | - | - |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-06-11 00:02:56.714918805 | B | 0 | 8064521972085 | 1970-01-01 | 1970-01-01 | 30 | 2.16 | 8064521967375 | 1 | 0 | 112115230 | - | - |
| 996 | 2026-06-11 00:02:56.716533935 | B | 0 | 8064521972103 | 1970-01-01 | 1970-01-01 | 49 | 2.16 | 8064521971577 | 1 | 0 | 112115231 | - | - |
| 997 | 2026-06-11 00:02:56.717908133 | B | 0 | 8064521972122 | 1970-01-01 | 1970-01-01 | 34 | 2.16 | 8064521971577 | 1 | 0 | 112115232 | - | - |
| 998 | 2026-06-11 00:02:56.719112899 | B | 0 | 8064521972126 | 1970-01-01 | 1970-01-01 | 0 | 2.16 | 8064521971577 | 1 | 0 | 112115234 | - | - |
| 999 | 2026-06-11 00:02:56.720581443 | B | 0 | 8064521972134 | 1970-01-01 | 1970-01-01 | 20 | 2.16 | 8064521971577 | 1 | 0 | 112115236 | - | - |
1000 rows × 14 columns
Trades for Product for both Futures and Spreads#
Return the first 1000 trades for Crude Oil contracts, whether Futures or Spreads, trading on NYMEX,
product code CL.
Futures symbols have the structure [Product Code]\[Expiry Month & Year] (e.g. CL\N26),
and Futures Spread symbols the structure [Product Code]\[Expiry Month & Year]\[Expiry Month & Year]
(e.g. CL\N26\Z26).
Both chains are selected by their symbol patterns and merged together.
import onetick.py as otp
# Trade data for the NYMEX Crude Oil contracts.
trd = otp.DataSource(db='NYMEX', tick_type='TRD')
# Select the outright futures chain ('CL____') and the spreads chain ('CL________').
futures = otp.Symbols('NYMEX', pattern='CL____', for_tick_type='TRD')
spreads = otp.Symbols('NYMEX', pattern='CL________', for_tick_type='TRD')
# Merge trades from both chains into a single stream.
merged = otp.merge(
[trd],
symbols=otp.merge([futures, spreads])
)
# Return first 1000 rows
merged = merged.limit(1000)
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | AGGRESSOR_SIDE | BOOK_TYPE | BUY_ORDER_ID | DELETED_TIME | EXCH_TIME | OMDSEQ | PRICE | SELL_ORDER_ID | SIZE | TICK_STATUS | TRADE_ID | TRADE_PERIOD | TRADE_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 00:00:00.030309517 | 1 | 1970-01-01 | 1970-01-01 | 2 | NaN | 2 | 0 | - | LEG | ||||
| 1 | 2026-06-11 00:00:00.032494863 | 0 | 1970-01-01 | 1970-01-01 | 12 | 88.38 | 8064521763074 | 1 | 0 | 112111789 | - | IMP | ||
| 2 | 2026-06-11 00:00:00.032494863 | 0 | 8064521760428 | 1970-01-01 | 1970-01-01 | 13 | 2.17 | 1 | 0 | 112111790 | - | IMP | ||
| 3 | 2026-06-11 00:00:00.032494863 | B | 0 | 8064521763641 | 1970-01-01 | 1970-01-01 | 25 | 86.21 | 1 | 0 | 112111788 | - | IMP | |
| 4 | 2026-06-11 00:00:00.032560523 | 1 | 1970-01-01 | 1970-01-01 | 164 | NaN | 1 | 0 | - | LEG | ||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-06-11 00:00:00.440854765 | S | 0 | 8064521770409 | 1970-01-01 | 1970-01-01 | 283 | 92.38 | 8064521770862 | 1 | 0 | 112112672 | - | - |
| 996 | 2026-06-11 00:00:00.440854765 | S | 0 | 8064521770425 | 1970-01-01 | 1970-01-01 | 284 | 92.38 | 8064521770862 | 1 | 0 | 112112672 | - | - |
| 997 | 2026-06-11 00:00:00.449294029 | 1 | 1970-01-01 | 1970-01-01 | 309 | NaN | 21 | 0 | - | LEG | ||||
| 998 | 2026-06-11 00:00:00.449294029 | 1 | 1970-01-01 | 1970-01-01 | 320 | NaN | 21 | 0 | - | LEG | ||||
| 999 | 2026-06-11 00:00:00.450994365 | 1 | 1970-01-01 | 1970-01-01 | 196 | NaN | 1 | 0 | - | LEG |
1000 rows × 14 columns
Volume and Open Interest (OI) for Product / Futures Chain#
Return the Volume and Open Interest for the first 1000 Crude Oil Futures contracts (Futures Chain)
trading on NYMEX, product code CL.
The symbol pattern CL____ selects the futures chain.
UPDATE_TYPE is filtered to Summary to return the final daily combination of both Volume and Open
Interest (other records carry only Volume or only Open Interest updates).
import onetick.py as otp
# Daily records for the NYMEX Crude Oil futures chain.
day = otp.DataSource(db='NYMEX_DAILY', tick_type='DAY')
# Keep only the daily Summary records that carry both Volume and Open Interest.
day = day.where(day['UPDATE_TYPE'] == 'Summary')
# Merge every matching CL futures contract into a single stream.
merged = otp.merge(
[day],
symbols=otp.Symbols('NYMEX_DAILY', pattern='CL____', for_tick_type='DAY')
)
# Return first 1000 rows
merged = merged.limit(1000)
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | BLOCK_VOLUME | CLOSE | ELEC_VOLUME | HIGH | LOW | OMDSEQ | OPEN | OPEN_INT | OPEN_INT_DATE | SETTLE_DATE | SETTLE_PRICE | UPDATE_TYPE | VOLUME | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-06-11 21:30:00 | 424 | 75.19 | 39009 | 77.80 | 75.18 | 34 | 77.21 | 140354.0 | 20260610 | 20260611 | 76.02 | Summary | 39433 |
| 1 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 38 | NaN | NaN | 20260611 | 55.12 | Summary | 0 | |
| 2 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 53 | NaN | NaN | 20260611 | 58.24 | Summary | 0 | |
| 3 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 70 | NaN | NaN | 20260611 | 64.85 | Summary | 0 | |
| 4 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 93 | NaN | NaN | 20260611 | 54.27 | Summary | 0 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 123 | 2026-06-11 21:30:00 | 0 | 70.60 | 3 | 70.60 | 70.60 | 9241 | 70.60 | 3819.0 | 20260610 | 20260611 | 71.37 | Summary | 3 |
| 124 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 9242 | NaN | NaN | 20260611 | 62.90 | Summary | 0 | |
| 125 | 2026-06-11 21:30:00 | 0 | NaN | 4 | NaN | NaN | 9246 | NaN | 3827.0 | 20260610 | 20260611 | 70.16 | Summary | 4 |
| 126 | 2026-06-11 21:30:00 | 0 | NaN | 0 | NaN | NaN | 9278 | NaN | NaN | 20260611 | 56.98 | Summary | 0 | |
| 127 | 2026-06-11 21:30:00 | 0 | 73.84 | 798 | 74.76 | 73.76 | 9442 | 74.76 | 32721.0 | 20260610 | 20260611 | 74.05 | Summary | 798 |
128 rows × 14 columns
Volume and Open Interest (OI) by Expiry for Product / Futures Chain#
Return the Volume and Open Interest for the Crude Oil Futures contracts (Futures Chain) trading on NYMEX,
product code CL, together with the Expiration Date.
The symbol pattern CL____ selects the futures chain.
UPDATE_TYPE is filtered to Summary to return the final daily combination of both Volume and Open Interest.
The daily DAY records are joined by time to the static STAT records (which carry the Expiration Date),
with a lookback of up to 1 day (86400s) so the prevailing static record is picked up.
Results are ordered by Expiration Date.
import onetick.py as otp
# Daily records for the NYMEX Crude Oil futures chain.
day = otp.DataSource(db='NYMEX_DAILY', tick_type='DAY')
day = day.where(day['UPDATE_TYPE'] == 'Summary')
day = day[['VOLUME', 'OPEN_INT']]
# Static (reference) data carrying the Expiration Date, looking back up to 1 day for the prevailing record.
stat = otp.DataSource(db='NYMEX_DAILY', tick_type='STAT', back_to_first_tick=86400)
stat = stat[['EXPIRATION_DATE']]
# Join each daily tick to the prevailing static record (asof join).
joined = otp.join_by_time([day, stat])
# Merge every matching CL futures contract into a single stream.
merged = otp.merge(
[joined],
symbols=otp.Symbols('NYMEX_DAILY', pattern='CL____', for_tick_type='DAY')
)
# Order the contracts by Expiration Date.
merged = merged.sort('EXPIRATION_DATE')
result = otp.run(
merged,
start=otp.dt(2026, 6, 11),
end=otp.dt(2026, 6, 12),
timezone='UTC'
)
result
| Time | VOLUME | OPEN_INT | EXPIRATION_DATE | |
|---|---|---|---|---|
| 0 | 2026-06-11 21:30:00 | 294694 | 138626.0 | 20260622 |
| 1 | 2026-06-11 21:30:00 | 182617 | 227467.0 | 20260721 |
| 2 | 2026-06-11 21:30:00 | 134327 | 175226.0 | 20260820 |
| 3 | 2026-06-11 21:30:00 | 79859 | 102906.0 | 20260922 |
| 4 | 2026-06-11 21:30:00 | 56608 | 69854.0 | 20261020 |
| ... | ... | ... | ... | ... |
| 123 | 2026-06-11 21:30:00 | 0 | NaN | 20360922 |
| 124 | 2026-06-11 21:30:00 | 0 | NaN | 20361021 |
| 125 | 2026-06-11 21:30:00 | 0 | 20.0 | 20361120 |
| 126 | 2026-06-11 21:30:00 | 0 | NaN | 20361219 |
| 127 | 2026-06-11 21:30:00 | 0 | NaN | 20370120 |
128 rows × 4 columns
Getting Daily Trade Bars for Product#
Query CME E-mini S&P 500 futures contracts daily bar data matching the pattern ES____ for a single day.
import onetick.py as otp
# Define the time range
start = otp.dt(2024, 1, 3)
end = otp.dt(2024, 1, 4)
# Get all symbols matching 'ES____' (ES + 4 wildcard characters)
symbols = otp.Symbols(
db='CME_SAMPLE_DAILY',
pattern='ES____',
for_tick_type='DAY'
)
# Define the data source for the DAY tick type
data = otp.DataSource(db='CME_SAMPLE_DAILY', tick_type='DAY')
# merging all symbols into a single flow
data = otp.merge([data], symbols=symbols, identify_input_ts=True)
# Run the query
result = otp.run(
data,
start=start,
end=end,
timezone='America/New_York'
)
result
| Time | BLOCK_VOLUME | CLOSE | ELEC_VOLUME | HIGH | LOW | OMDSEQ | OPEN | OPEN_INT | SETTLE_DATE | SETTLE_PRICE | VOLUME | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 0 | NaN | NaN | 20240103 | 5033.00 | 0 | ES\H26 | DAY |
| 1 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 0 | NaN | NaN | 20240103 | 5386.00 | 0 | ES\H29 | DAY |
| 2 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 0 | NaN | NaN | 20240103 | 5301.00 | 0 | ES\M28 | DAY |
| 3 | 2024-01-03 17:30:00 | 0 | 4842.0 | 3 | 4862.25 | 4842.0 | 0 | 4845.00 | 1870.0 | 20240103 | 4840.75 | 3 | ES\U24 | DAY |
| 4 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 0 | NaN | NaN | 20240103 | 5094.00 | 0 | ES\U26 | DAY |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 16 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 3 | NaN | NaN | 20240103 | 4945.00 | 0 | ES\M25 | DAY |
| 17 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 3 | NaN | NaN | 20240103 | 5329.00 | 0 | ES\U28 | DAY |
| 18 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 3 | NaN | 580.0 | 20240103 | 5003.00 | 0 | ES\Z25 | DAY |
| 19 | 2024-01-03 17:30:00 | 0 | 4800.0 | 10495 | 4841.75 | 4794.5 | 4 | 4841.75 | 13089.0 | 20240103 | 4798.00 | 10495 | ES\M24 | DAY |
| 20 | 2024-01-03 17:30:00 | 0 | NaN | 0 | NaN | NaN | 5 | NaN | NaN | 20240103 | 5060.00 | 0 | ES\M26 | DAY |
21 rows × 14 columns