Creating Bars#
Tip
OneTick Cloud has daily and minute bars precomputed and available in _BARS databases.
Daily OHLCV data with the official closing prices is also available.
See Data Retrieval - Bars and Daily OHLCV.
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__'
Simple OHLCV Bar Creation#
Calculate trade aggregation, passing in a dictionary of aggregates into the
agg() method of the trade data source.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'COUNT': otp.agg.count(),
})
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | COUNT | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 50.02 | 50.805 | 49.94 | 50.52 | 15008890 | 128622 |
Extended OHLCV Bar Creation#
Calculate trade aggregation, passing in a dictionary of aggregates
into the agg() method of the trade data source.
Additional aggregates are defined for the First and Last time of a trade,
and the timestamp of the High Price and Low Price.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'COUNT': otp.agg.count(),
'OPEN_TIME':otp.agg.first_time(),
'HIGH_TIME':otp.agg.high_time('PRICE'),
'LOW_TIME':otp.agg.low_time('PRICE'),
'CLOSE_TIME':otp.agg.last_time(),
})
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | COUNT | OPEN_TIME | HIGH_TIME | LOW_TIME | CLOSE_TIME | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 50.02 | 50.805 | 49.94 | 50.52 | 15008890 | 128622 | 2024-01-03 09:30:00.065443591 | 2024-01-03 12:56:34.934808989 | 2024-01-03 09:36:06.705386856 | 2024-01-03 15:59:59.994802751 |
Dynamic Bar Creation - 5 Seconds#
Calculate custom trade bars, with a bucket interval defined as 5 seconds.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'COUNT': otp.agg.count(),
}, bucket_interval=5)
# Return first 1000 Rows
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | COUNT | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:05 | 50.0200 | 50.2200 | 50.0020 | 50.0900 | 563231 | 689 |
| 1 | 2024-01-03 09:30:10 | 50.0900 | 50.2300 | 50.0800 | 50.0900 | 20640 | 469 |
| 2 | 2024-01-03 09:30:15 | 50.1041 | 50.2100 | 50.0600 | 50.0900 | 6776 | 257 |
| 3 | 2024-01-03 09:30:20 | 50.0900 | 50.1500 | 50.0900 | 50.0900 | 4071 | 137 |
| 4 | 2024-01-03 09:30:25 | 50.0900 | 50.1500 | 50.0900 | 50.0900 | 3594 | 74 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 10:53:00 | 50.0872 | 50.0872 | 50.0850 | 50.0850 | 310 | 2 |
| 996 | 2024-01-03 10:53:05 | 50.0900 | 50.0900 | 50.0800 | 50.0900 | 244 | 10 |
| 997 | 2024-01-03 10:53:10 | 50.0850 | 50.0900 | 50.0801 | 50.0878 | 341 | 7 |
| 998 | 2024-01-03 10:53:15 | 50.0850 | 50.0850 | 50.0850 | 50.0850 | 979 | 4 |
| 999 | 2024-01-03 10:53:20 | 50.0850 | 50.0888 | 50.0800 | 50.0800 | 2241 | 53 |
1000 rows × 7 columns
Dynamic Bar Creation - 5 Minutes#
Calculate custom trade bars, with a bucket interval defined as 5 minutes.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'COUNT': otp.agg.count(),
}, bucket_interval=otp.Minute(5))
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | COUNT | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 50.0200 | 50.2300 | 50.0000 | 50.0200 | 779467 | 3712 |
| 1 | 2024-01-03 09:40:00 | 50.0100 | 50.1395 | 49.9400 | 50.0833 | 147818 | 1896 |
| 2 | 2024-01-03 09:45:00 | 50.0850 | 50.1400 | 50.0400 | 50.0700 | 143668 | 1433 |
| 3 | 2024-01-03 09:50:00 | 50.0700 | 50.1500 | 50.0500 | 50.1000 | 159612 | 1652 |
| 4 | 2024-01-03 09:55:00 | 50.1100 | 50.1900 | 50.1041 | 50.1600 | 161803 | 1666 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 73 | 2024-01-03 15:40:00 | 50.5700 | 50.6400 | 50.5653 | 50.6128 | 306329 | 2748 |
| 74 | 2024-01-03 15:45:00 | 50.6200 | 50.6300 | 50.5650 | 50.6071 | 457537 | 3299 |
| 75 | 2024-01-03 15:50:00 | 50.6061 | 50.6800 | 50.5800 | 50.6229 | 581197 | 3665 |
| 76 | 2024-01-03 15:55:00 | 50.6100 | 50.6245 | 50.4700 | 50.4701 | 1136217 | 6960 |
| 77 | 2024-01-03 16:00:00 | 50.4800 | 50.5600 | 50.4700 | 50.5200 | 1586705 | 9089 |
78 rows × 7 columns
Dynamic Bar Creation - Tick Bin#
Calculate custom trade bars, with a bucket interval defined as 1000 records (ticks).
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'COUNT': otp.agg.count(),
}, bucket_interval=1000, bucket_units='ticks')
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | COUNT | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:07.396341167 | 50.0200 | 50.2300 | 50.0020 | 50.090 | 577481 | 1000 |
| 1 | 2024-01-03 09:31:04.063236217 | 50.1300 | 50.2100 | 50.0600 | 50.150 | 48329 | 1000 |
| 2 | 2024-01-03 09:33:10.306898441 | 50.1500 | 50.1800 | 50.0300 | 50.055 | 83999 | 1000 |
| 3 | 2024-01-03 09:35:19.497096809 | 50.0583 | 50.0942 | 49.9800 | 49.980 | 95520 | 1000 |
| 4 | 2024-01-03 09:38:04.450428309 | 49.9800 | 50.1395 | 49.9400 | 50.100 | 73858 | 1000 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 124 | 2024-01-03 15:58:34.791550908 | 50.5150 | 50.5400 | 50.5100 | 50.540 | 190858 | 1000 |
| 125 | 2024-01-03 15:59:14.572020429 | 50.5400 | 50.5500 | 50.5193 | 50.530 | 152933 | 1000 |
| 126 | 2024-01-03 15:59:30.057184421 | 50.5300 | 50.5550 | 50.5300 | 50.550 | 195929 | 1000 |
| 127 | 2024-01-03 15:59:46.721898180 | 50.5500 | 50.5600 | 50.5400 | 50.545 | 215317 | 1000 |
| 128 | 2024-01-03 16:00:00.000000000 | 50.5400 | 50.5500 | 50.5200 | 50.520 | 160991 | 622 |
129 rows × 7 columns
VWAP Bar Creation#
Calculate the Volume Weighted Average Price (VWAP) using the vwap aggregate, outputting 5 minute bars.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'AVG_PRICE': otp.agg.average('PRICE'),
'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
}, bucket_interval=otp.Minute(5))
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | AVG_PRICE | VWAP_PRICE | |
|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 50.097136 | 50.090611 |
| 1 | 2024-01-03 09:40:00 | 50.040346 | 50.040330 |
| 2 | 2024-01-03 09:45:00 | 50.083412 | 50.081404 |
| 3 | 2024-01-03 09:50:00 | 50.104652 | 50.102608 |
| 4 | 2024-01-03 09:55:00 | 50.153972 | 50.152176 |
| ... | ... | ... | ... |
| 73 | 2024-01-03 15:40:00 | 50.598669 | 50.599498 |
| 74 | 2024-01-03 15:45:00 | 50.610265 | 50.608622 |
| 75 | 2024-01-03 15:50:00 | 50.640619 | 50.637465 |
| 76 | 2024-01-03 15:55:00 | 50.528556 | 50.528791 |
| 77 | 2024-01-03 16:00:00 | 50.524240 | 50.527153 |
78 rows × 3 columns
TWAP Bar Creation#
Calculate the Time Weighted Average Price (TWAP)
using the tw_average() aggregate, outputting 5 minute bars.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.agg({
'AVG_PRICE': otp.agg.average('PRICE'),
'TWAP_PRICE': otp.agg.tw_average('PRICE'),
}, bucket_interval=otp.Minute(5))
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | AVG_PRICE | TWAP_PRICE | |
|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 50.097136 | 50.075254 |
| 1 | 2024-01-03 09:40:00 | 50.040346 | 50.043442 |
| 2 | 2024-01-03 09:45:00 | 50.083412 | 50.079243 |
| 3 | 2024-01-03 09:50:00 | 50.104652 | 50.105281 |
| 4 | 2024-01-03 09:55:00 | 50.153972 | 50.149405 |
| ... | ... | ... | ... |
| 73 | 2024-01-03 15:40:00 | 50.598669 | 50.599148 |
| 74 | 2024-01-03 15:45:00 | 50.610265 | 50.614215 |
| 75 | 2024-01-03 15:50:00 | 50.640619 | 50.642343 |
| 76 | 2024-01-03 15:55:00 | 50.528556 | 50.520973 |
| 77 | 2024-01-03 16:00:00 | 50.524240 | 50.520339 |
78 rows × 3 columns
Dynamic Bars for Symbols Across Databases#
Retrieve Trades and Calculate Dynamic Bars for Symbols across Databases for the specified time range.
The initial otp.DataSource is defined without specifying the Database or symbol.
The schema of the Data Source is specified manually.
Symbols are specified including the Database name, with format [Database]::[Symbol] e.g. LSE::VOD.
import onetick.py as otp
# Define the Symbol List
sym_list = ['LSE::VOD', 'EURONEXT::AF', 'XETRA::DBK', 'LSE::TSCO',
'LSE::SHEL', 'EURONEXT::AF', 'LSE::VOD', 'XETRA::DBK']
# Define Data Source, in this case without specifying the Database or symbol name.
# As the schema is not yet known, set the schema policy to manual
trd = otp.DataSource(tick_type='TRD', schema_policy='manual')
# Define the output schema
trd.schema.set(
PRICE=float,
SIZE=int,
TRADE_VENUE=str,
BOOK_TYPE=str,
TRADE_PERIOD=str
)
# Specify Output Fields
trd = trd[['PRICE', 'SIZE', 'TRADE_VENUE', 'BOOK_TYPE', 'TRADE_PERIOD']]
# Filter on Lit Order Book
trd = trd.where(trd['BOOK_TYPE'] == '0')
# Filter on Continuous Trading
trd = trd.where(trd['TRADE_PERIOD'] == '-')
# Aggregates All Trades into 5 Minute Buckets
data = trd.agg({
'OPEN': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'CLOSE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'VWAP': otp.agg.vwap('PRICE', 'SIZE'),
'COUNT': otp.agg.count()
}, bucket_interval=otp.Minute(5))
# Create a single output, merging all the inputs into a single resultset.
merged = otp.merge([data], symbols=sym_list, identify_input_ts=True, separate_db_name=True)
# Run the query returning the data in the selected timezone
result = otp.run(merged,
start=otp.datetime(2024, 1, 3, 8),
end=otp.datetime(2024, 1, 4, 16),
timezone='Europe/London')
result
| Time | OPEN | HIGH | LOW | CLOSE | VOLUME | VWAP | COUNT | SYMBOL_NAME | DB_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 08:05:00 | 70.010 | 70.570 | 70.010 | 70.460 | 322654 | 70.460361 | 76 | VOD | LSE | TRD |
| 1 | 2024-01-03 08:05:00 | 13.400 | 13.444 | 13.200 | 13.322 | 30471 | 13.309963 | 128 | AF | EURONEXT | TRD |
| 2 | 2024-01-03 08:05:00 | 12.490 | 12.548 | 12.486 | 12.524 | 305760 | 12.513786 | 158 | DBK | XETRA | TRD |
| 3 | 2024-01-03 08:05:00 | 293.800 | 295.600 | 293.800 | 295.300 | 109553 | 295.061019 | 54 | TSCO | LSE | TRD |
| 4 | 2024-01-03 08:05:00 | 2574.500 | 2581.500 | 2574.000 | 2574.500 | 16334 | 2577.026264 | 55 | SHEL | LSE | TRD |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 3067 | 2024-01-04 16:00:00 | 302.100 | 302.300 | 302.100 | 302.300 | 21193 | 302.235488 | 27 | TSCO | LSE | TRD |
| 3068 | 2024-01-04 16:00:00 | 2605.000 | 2605.000 | 2603.000 | 2603.000 | 25965 | 2604.144367 | 46 | SHEL | LSE | TRD |
| 3069 | 2024-01-04 16:00:00 | 13.080 | 13.080 | 13.062 | 13.066 | 4958 | 13.069942 | 28 | AF | EURONEXT | TRD |
| 3070 | 2024-01-04 16:00:00 | 70.250 | 70.270 | 70.240 | 70.250 | 91909 | 70.248685 | 27 | VOD | LSE | TRD |
| 3071 | 2024-01-04 16:00:00 | 12.512 | 12.514 | 12.506 | 12.506 | 29175 | 12.510519 | 27 | DBK | XETRA | TRD |
3072 rows × 11 columns