# Creating Bars
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__'
```
## Simple OHLCV Bar Creation
Calculate trade aggregation, passing in a dictionary of aggregates into the
[`agg()`](https://docs.pip.distribution.sol.onetick.com/api/source/agg.html.md#onetick.py.Source.agg) method of the trade data source.
```ipython3
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
```
```myst-ansi
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()`](https://docs.pip.distribution.sol.onetick.com/api/source/agg.html.md#onetick.py.Source.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.
```ipython3
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
```
```myst-ansi
Time OPEN HIGH LOW CLOSE VOLUME COUNT \
0 2024-01-03 16:00:00 50.02 50.805 49.94 50.52 15008890 128622
OPEN_TIME HIGH_TIME \
0 2024-01-03 09:30:00.065443591 2024-01-03 12:56:34.934808989
LOW_TIME CLOSE_TIME
0 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.
```ipython3
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
```
```myst-ansi
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 x 7 columns]
```
## Dynamic Bar Creation - 5 Minutes
Calculate custom trade bars, with a bucket interval defined as 5 minutes.
```ipython3
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
```
```myst-ansi
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 x 7 columns]
```
## Dynamic Bar Creation - Tick Bin
Calculate custom trade bars, with a bucket interval defined as 1000 records (ticks).
```ipython3
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
```
```myst-ansi
Time OPEN HIGH LOW CLOSE VOLUME \
0 2024-01-03 09:30:07.396341167 50.0200 50.2300 50.0020 50.090 577481
1 2024-01-03 09:31:04.063236217 50.1300 50.2100 50.0600 50.150 48329
2 2024-01-03 09:33:10.306898441 50.1500 50.1800 50.0300 50.055 83999
3 2024-01-03 09:35:19.497096809 50.0583 50.0942 49.9800 49.980 95520
4 2024-01-03 09:38:04.450428309 49.9800 50.1395 49.9400 50.100 73858
.. ... ... ... ... ... ...
124 2024-01-03 15:58:34.791550908 50.5150 50.5400 50.5100 50.540 190858
125 2024-01-03 15:59:14.572020429 50.5400 50.5500 50.5193 50.530 152933
126 2024-01-03 15:59:30.057184421 50.5300 50.5550 50.5300 50.550 195929
127 2024-01-03 15:59:46.721898180 50.5500 50.5600 50.5400 50.545 215317
128 2024-01-03 16:00:00.000000000 50.5400 50.5500 50.5200 50.520 160991
COUNT
0 1000
1 1000
2 1000
3 1000
4 1000
.. ...
124 1000
125 1000
126 1000
127 1000
128 622
[129 rows x 7 columns]
```
## VWAP Bar Creation
Calculate the Volume Weighted Average Price (VWAP) using the vwap aggregate, outputting 5 minute bars.
```ipython3
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
```
```myst-ansi
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 x 3 columns]
```
## TWAP Bar Creation
Calculate the Time Weighted Average Price (TWAP)
using the [`tw_average()`](https://docs.pip.distribution.sol.onetick.com/api/aggregations/tw_average.html.md#onetick.py.agg.tw_average) aggregate, outputting 5 minute bars.
```ipython3
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
```
```myst-ansi
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 x 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`](https://docs.pip.distribution.sol.onetick.com/api/sources/data_source.html.md#onetick.py.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`.
```ipython3
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
```
```myst-ansi
Time OPEN HIGH LOW CLOSE VOLUME \
0 2024-01-03 08:05:00 70.010 70.570 70.010 70.460 322654
1 2024-01-03 08:05:00 13.400 13.444 13.200 13.322 30471
2 2024-01-03 08:05:00 12.490 12.548 12.486 12.524 305760
3 2024-01-03 08:05:00 293.800 295.600 293.800 295.300 109553
4 2024-01-03 08:05:00 2574.500 2581.500 2574.000 2574.500 16334
... ... ... ... ... ... ...
3067 2024-01-04 16:00:00 302.100 302.300 302.100 302.300 21193
3068 2024-01-04 16:00:00 2605.000 2605.000 2603.000 2603.000 25965
3069 2024-01-04 16:00:00 13.080 13.080 13.062 13.066 4958
3070 2024-01-04 16:00:00 70.250 70.270 70.240 70.250 91909
3071 2024-01-04 16:00:00 12.512 12.514 12.506 12.506 29175
VWAP COUNT SYMBOL_NAME DB_NAME TICK_TYPE
0 70.460361 76 VOD LSE TRD
1 13.309963 128 AF EURONEXT TRD
2 12.513786 158 DBK XETRA TRD
3 295.061019 54 TSCO LSE TRD
4 2577.026264 55 SHEL LSE TRD
... ... ... ... ... ...
3067 302.235488 27 TSCO LSE TRD
3068 2604.144367 46 SHEL LSE TRD
3069 13.069942 28 AF EURONEXT TRD
3070 70.248685 27 VOD LSE TRD
3071 12.510519 27 DBK XETRA TRD
[3072 rows x 11 columns]
```