# 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.
```default
# 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.
```ipython3
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
```
```myst-ansi
Time AGGRESSOR_SIDE BOOK_TYPE DELETED_TIME \
0 2024-01-03 12:30:00 1 1970-01-01 01:00:00
1 2024-01-03 12:30:00 1 1970-01-01 01:00:00
2 2024-01-03 12:30:00 1 1970-01-01 01:00:00
3 2024-01-03 12:30:00 1 1970-01-01 01:00:00
4 2024-01-03 12:30:00 1 1970-01-01 01:00:00
.. ... ... ... ...
26 2024-01-03 12:30:00 0 1970-01-01 01:00:00
27 2024-01-03 12:30:00 0 1970-01-01 01:00:00
28 2024-01-03 12:30:00 0 1970-01-01 01:00:00
29 2024-01-03 12:30:00 0 1970-01-01 01:00:00
30 2024-01-03 12:30:00 0 1970-01-01 01:00:00
EXCH_TIME OMDSEQ PRICE SIZE TICK_STATUS TRADE_ID \
0 2024-01-03 12:13:23.006875 4 68.10 1 0 20715248
1 2024-01-03 10:38:45.104576 6 70.77 20 0 19050498
2 2024-01-02 16:22:11.143823 7 71.17 2 0 28311280
3 2024-01-03 12:13:23.007387 7 68.81 1 0 20715260
4 2024-01-03 12:24:40.380385 10 74.94 1 0 20880021
.. ... ... ... ... ... ...
26 2024-01-03 12:29:53.944614 362 74.71 1 0 20963623
27 2024-01-03 12:29:53.944614 374 75.48 1 0 20963277
28 2024-01-03 12:29:53.944614 397 74.18 1 0 20963622
29 2024-01-03 12:29:53.944614 402 75.23 1 0 20963668
30 2024-01-03 12:29:54.326402 426 75.84 1 0 20966615
TRADE_TYPE
0 LEG
1 LEG
2 LEG
3 LEG
4 LEG
.. ...
26 0
27 0
28 0
29 0
30 0
[31 rows x 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.
```ipython3
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
```
```myst-ansi
Time DB_NAME DB_SYMBOL BSYM NAME \
0 2026-06-11 21:00:00.937 NYMEX CL\H35 CLH35 Comdty Crude Oil Mar35
1 2026-06-11 21:00:00.937 NYMEX CL\G36 CLG36 Comdty Crude Oil Feb36
2 2026-06-11 21:00:00.937 NYMEX CL\J35 CLJ35 Comdty Crude Oil Apr35
3 2026-06-11 21:00:01.137 NYMEX CL\M34 CLM34 Comdty Crude Oil Jun34
4 2026-06-11 21:00:01.138 NYMEX CL\H34 CLH34 Comdty Crude Oil Mar34
.. ... ... ... ... ...
123 2026-06-11 21:00:15.559 NYMEX CL\V35 CLV35 Comdty Crude Oil Oct35
124 2026-06-11 21:00:15.559 NYMEX CL\U35 CLU35 Comdty Crude Oil Sep35
125 2026-06-11 21:00:15.658 NYMEX CL\G35 CLG35 Comdty Crude Oil Feb35
126 2026-06-11 21:00:15.658 NYMEX CL\Z34 CLZ34 Comdty Crude Oil Dec34
127 2026-06-11 21:00:15.659 NYMEX CL\M35 CLM35 Comdty Crude Oil Jun35
SEC_TYPE UNDERLYING_SEC_TYPE PRODUCT_CODE EXPIRATION_DATE
0 Future Energy CL 20350220
1 Future Energy CL 20360122
2 Future Energy CL 20350319
3 Future Energy CL 20340522
4 Future Energy CL 20340221
.. ... ... ... ...
123 Future Energy CL 20350920
124 Future Energy CL 20350821
125 Future Energy CL 20350122
126 Future Energy CL 20341120
127 Future Energy CL 20350522
[128 rows x 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.
```ipython3
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
```
```myst-ansi
Time DB_NAME DB_SYMBOL BSYM \
0 2026-06-11 ABU_DHABI ADCBF\K26
1 2026-06-11 ABU_DHABI ADIBF\K26
2 2026-06-11 ABU_DHABI ADNOCDISTF\K26
3 2026-06-11 ABU_DHABI ADPORTSF\K26
4 2026-06-11 ABU_DHABI ALDARF\K26
.. ... ... ... ...
995 2026-06-11 BSE RADI\M26
996 2026-06-11 BSE RADI\N26
997 2026-06-11 BSE RADI\Q26
998 2026-06-11 BSE RBLB\M26
999 2026-06-11 BSE RBLB\N26
NAME PRODUCT_CODE SEC_TYPE \
0 Abu Dhabi Commercial Bank,May-2026,Composite Future
1 Abu Dhabi Islamic Bank Futures,May-2026,Composite Future
2 Abu Dhabi National Oil Company for Distributio... Future
3 Abu Dhabi Ports Company Pjsc,May-2026,Composite Future
4 Aldar Properties,May-2026,Composite Future
.. ... ... ...
995 RADI Jun26 RADI Future
996 RADI Jul26 RADI Future
997 RADI Aug26 RADI Future
998 RBL Bank Jun26 RBLB Future
999 RBL Bank Jul26 RBLB Future
UNDERLYING_SEC_TYPE EXPIRATION_DATE
0
1
2
3
4
.. ... ...
995 Equity 20260625
996 Equity 20260730
997 Equity 20260827
998 Equity 20260625
999 Equity 20260730
[1000 rows x 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.
```ipython3
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
```
```myst-ansi
Time DB_NAME SEC_TYPE UNDERLYING_SEC_TYPE PRODUCT_CODE \
0 2026-06-12 NYMEX Future Agriculture CJ
1 2026-06-12 NYMEX Future Agriculture KT
2 2026-06-12 NYMEX Future Agriculture TT
3 2026-06-12 NYMEX Future Agriculture YO
4 2026-06-12 NYMEX Future Energy 0A
.. ... ... ... ... ...
893 2026-06-12 NYMEX Future Metal PAT
894 2026-06-12 NYMEX Future Metal PL
895 2026-06-12 NYMEX Future Metal PLM
896 2026-06-12 NYMEX Future Metal PLT
897 2026-06-12 NYMEX Future Other LCS
CONTRACT_COUNT
0 10
1 10
2 10
3 8
4 65
.. ...
893 2
894 14
895 6
896 2
897 21
[898 rows x 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).
```ipython3
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
```
```myst-ansi
Time AGGRESSOR_SIDE BOOK_TYPE BUY_ORDER_ID \
0 2026-06-11 00:00:00.030309517 1
1 2026-06-11 00:00:00.032494863 0
2 2026-06-11 00:00:00.032494863 B 0 8064521763641
3 2026-06-11 00:00:00.032560523 1
4 2026-06-11 00:00:00.032730261 1
.. ... ... ... ...
995 2026-06-11 00:00:00.732578731 1
996 2026-06-11 00:00:00.732578731 1
997 2026-06-11 00:00:00.732578731 1
998 2026-06-11 00:00:00.733675483 1
999 2026-06-11 00:00:00.733675483 1
DELETED_TIME EXCH_TIME OMDSEQ PRICE SELL_ORDER_ID SIZE TICK_STATUS \
0 1970-01-01 1970-01-01 2 NaN 2 0
1 1970-01-01 1970-01-01 12 88.38 8064521763074 1 0
2 1970-01-01 1970-01-01 25 86.21 1 0
3 1970-01-01 1970-01-01 164 NaN 1 0
4 1970-01-01 1970-01-01 181 NaN 1 0
.. ... ... ... ... ... ... ...
995 1970-01-01 1970-01-01 92 NaN 1 0
996 1970-01-01 1970-01-01 135 NaN 2 0
997 1970-01-01 1970-01-01 168 NaN 1 0
998 1970-01-01 1970-01-01 208 NaN 1 0
999 1970-01-01 1970-01-01 209 NaN 1 0
TRADE_ID TRADE_PERIOD TRADE_TYPE
0 - LEG
1 112111789 - IMP
2 112111788 - IMP
3 - LEG
4 - LEG
.. ... ... ...
995 - LEG
996 - LEG
997 - LEG
998 - LEG
999 - LEG
[1000 rows x 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).
```ipython3
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
```
```myst-ansi
Time AGGRESSOR_SIDE BOOK_TYPE BUY_ORDER_ID \
0 2026-06-11 00:00:00.032494863 0 8064521760428
1 2026-06-11 00:00:00.033112153 0
2 2026-06-11 00:00:00.056412059 B 0 8064521764053
3 2026-06-11 00:00:00.069579129 S 0 8064521728133
4 2026-06-11 00:00:00.086482261 B 0 8064521764270
.. ... ... ... ...
995 2026-06-11 00:02:56.714918805 B 0 8064521972085
996 2026-06-11 00:02:56.716533935 B 0 8064521972103
997 2026-06-11 00:02:56.717908133 B 0 8064521972122
998 2026-06-11 00:02:56.719112899 B 0 8064521972126
999 2026-06-11 00:02:56.720581443 B 0 8064521972134
DELETED_TIME EXCH_TIME OMDSEQ PRICE SELL_ORDER_ID SIZE TICK_STATUS \
0 1970-01-01 1970-01-01 13 2.17 1 0
1 1970-01-01 1970-01-01 71 5.67 8064521715068 1 0
2 1970-01-01 1970-01-01 81 0.51 8064521717209 1 0
3 1970-01-01 1970-01-01 0 1.50 8064521764096 1 0
4 1970-01-01 1970-01-01 11 1.88 8064521756816 1 0
.. ... ... ... ... ... ... ...
995 1970-01-01 1970-01-01 30 2.16 8064521967375 1 0
996 1970-01-01 1970-01-01 49 2.16 8064521971577 1 0
997 1970-01-01 1970-01-01 34 2.16 8064521971577 1 0
998 1970-01-01 1970-01-01 0 2.16 8064521971577 1 0
999 1970-01-01 1970-01-01 20 2.16 8064521971577 1 0
TRADE_ID TRADE_PERIOD TRADE_TYPE
0 112111790 - IMP
1 112111803 - IMP
2 112111820 - -
3 112111822 - -
4 112111824 - -
.. ... ... ...
995 112115230 - -
996 112115231 - -
997 112115232 - -
998 112115234 - -
999 112115236 - -
[1000 rows x 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.
```ipython3
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
```
```myst-ansi
Time AGGRESSOR_SIDE BOOK_TYPE BUY_ORDER_ID \
0 2026-06-11 00:00:00.030309517 1
1 2026-06-11 00:00:00.032494863 0
2 2026-06-11 00:00:00.032494863 0 8064521760428
3 2026-06-11 00:00:00.032494863 B 0 8064521763641
4 2026-06-11 00:00:00.032560523 1
.. ... ... ... ...
995 2026-06-11 00:00:00.440854765 S 0 8064521770409
996 2026-06-11 00:00:00.440854765 S 0 8064521770425
997 2026-06-11 00:00:00.449294029 1
998 2026-06-11 00:00:00.449294029 1
999 2026-06-11 00:00:00.450994365 1
DELETED_TIME EXCH_TIME OMDSEQ PRICE SELL_ORDER_ID SIZE TICK_STATUS \
0 1970-01-01 1970-01-01 2 NaN 2 0
1 1970-01-01 1970-01-01 12 88.38 8064521763074 1 0
2 1970-01-01 1970-01-01 13 2.17 1 0
3 1970-01-01 1970-01-01 25 86.21 1 0
4 1970-01-01 1970-01-01 164 NaN 1 0
.. ... ... ... ... ... ... ...
995 1970-01-01 1970-01-01 283 92.38 8064521770862 1 0
996 1970-01-01 1970-01-01 284 92.38 8064521770862 1 0
997 1970-01-01 1970-01-01 309 NaN 21 0
998 1970-01-01 1970-01-01 320 NaN 21 0
999 1970-01-01 1970-01-01 196 NaN 1 0
TRADE_ID TRADE_PERIOD TRADE_TYPE
0 - LEG
1 112111789 - IMP
2 112111790 - IMP
3 112111788 - IMP
4 - LEG
.. ... ... ...
995 112112672 - -
996 112112672 - -
997 - LEG
998 - LEG
999 - LEG
[1000 rows x 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).
```ipython3
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
```
```myst-ansi
Time BLOCK_VOLUME CLOSE ELEC_VOLUME HIGH LOW \
0 2026-06-11 21:30:00 424 75.19 39009 77.80 75.18
1 2026-06-11 21:30:00 0 NaN 0 NaN NaN
2 2026-06-11 21:30:00 0 NaN 0 NaN NaN
3 2026-06-11 21:30:00 0 NaN 0 NaN NaN
4 2026-06-11 21:30:00 0 NaN 0 NaN NaN
.. ... ... ... ... ... ...
123 2026-06-11 21:30:00 0 70.60 3 70.60 70.60
124 2026-06-11 21:30:00 0 NaN 0 NaN NaN
125 2026-06-11 21:30:00 0 NaN 4 NaN NaN
126 2026-06-11 21:30:00 0 NaN 0 NaN NaN
127 2026-06-11 21:30:00 0 73.84 798 74.76 73.76
OMDSEQ OPEN OPEN_INT OPEN_INT_DATE SETTLE_DATE SETTLE_PRICE \
0 34 77.21 140354.0 20260610 20260611 76.02
1 38 NaN NaN 20260611 55.12
2 53 NaN NaN 20260611 58.24
3 70 NaN NaN 20260611 64.85
4 93 NaN NaN 20260611 54.27
.. ... ... ... ... ... ...
123 9241 70.60 3819.0 20260610 20260611 71.37
124 9242 NaN NaN 20260611 62.90
125 9246 NaN 3827.0 20260610 20260611 70.16
126 9278 NaN NaN 20260611 56.98
127 9442 74.76 32721.0 20260610 20260611 74.05
UPDATE_TYPE VOLUME
0 Summary 39433
1 Summary 0
2 Summary 0
3 Summary 0
4 Summary 0
.. ... ...
123 Summary 3
124 Summary 0
125 Summary 4
126 Summary 0
127 Summary 798
[128 rows x 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.
```ipython3
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
```
```myst-ansi
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 x 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.
```ipython3
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
```
```myst-ansi
Time BLOCK_VOLUME CLOSE ELEC_VOLUME HIGH LOW \
0 2024-01-03 17:30:00 0 NaN 0 NaN NaN
1 2024-01-03 17:30:00 0 NaN 0 NaN NaN
2 2024-01-03 17:30:00 0 NaN 0 NaN NaN
3 2024-01-03 17:30:00 0 4842.0 3 4862.25 4842.0
4 2024-01-03 17:30:00 0 NaN 0 NaN NaN
.. ... ... ... ... ... ...
16 2024-01-03 17:30:00 0 NaN 0 NaN NaN
17 2024-01-03 17:30:00 0 NaN 0 NaN NaN
18 2024-01-03 17:30:00 0 NaN 0 NaN NaN
19 2024-01-03 17:30:00 0 4800.0 10495 4841.75 4794.5
20 2024-01-03 17:30:00 0 NaN 0 NaN NaN
OMDSEQ OPEN OPEN_INT SETTLE_DATE SETTLE_PRICE VOLUME SYMBOL_NAME \
0 0 NaN NaN 20240103 5033.00 0 ES\H26
1 0 NaN NaN 20240103 5386.00 0 ES\H29
2 0 NaN NaN 20240103 5301.00 0 ES\M28
3 0 4845.00 1870.0 20240103 4840.75 3 ES\U24
4 0 NaN NaN 20240103 5094.00 0 ES\U26
.. ... ... ... ... ... ... ...
16 3 NaN NaN 20240103 4945.00 0 ES\M25
17 3 NaN NaN 20240103 5329.00 0 ES\U28
18 3 NaN 580.0 20240103 5003.00 0 ES\Z25
19 4 4841.75 13089.0 20240103 4798.00 10495 ES\M24
20 5 NaN NaN 20240103 5060.00 0 ES\M26
TICK_TYPE
0 DAY
1 DAY
2 DAY
3 DAY
4 DAY
.. ...
16 DAY
17 DAY
18 DAY
19 DAY
20 DAY
[21 rows x 14 columns]
```