Aggregation#

Results can be aggregated by specifying an a dictionary of aggregation methods using ComputeFields, and then applying them in the Compute object along with the desired grouping, bucket period and method. The following aggregations are supported:

Sum, Average & Avg, TwAverage, Min & Low, Max & High, Count & NumTicks, First, Last, Variance, Stddev, FirstTime, LastTime, HighTime, LowTime, Vwap, Median, Correlation, ExpTwAverage, EXP_W_AVERAGE(), ExpWAverage, Ranking.

Results can be grouped with either:

A field from the table. e.g. EXCHANGE specified in the group_by attribute
A bucket interval using the bucket_interval attribute
A bucket interval units using the bucket_interval_units attribute

A series of simple examples are provided below showing how to retrieve aggregated market data from OneTick Cloud.

Common Aggregations#

Common aggregation methods include Sum, Avg, Min, Max, Count, Variance, Stddev.

Common Aggregations for Vodafone retrieval#

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'mean_price':otq.Average(In='PRICE'),
        'variance_price':otq.Variance(In='PRICE'),
        'stddev_price':otq.Stddev(In='PRICE'),
        'max_price':otq.Max(In='PRICE'),
        'min_price':otq.Min(In='PRICE'),
        'sum_size':otq.Sum(In='SIZE'),
        'count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(agg_stats(),
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Common Aggregations for Cisco retrieval results#
	Time	mean_price	variance_price	stddev_price	max_price	min_price	sum_size	count_price
0	2024-01-03 16:00:00	50.39247720774259	0.03541441488152422	0.18818718043884983	50.805	49.94	15008890.0	128622.0

Group by Field#

Data can be grouped by field such as TRADE_CURRENCY, using the Compute group_by attribute.

Common Aggregations for Vodafone grouped by Trade Currency retrieval#

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE,TRADE_CURRENCY').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'mean_price':otq.Average(In='PRICE'),
        'variance_price':otq.Variance(In='PRICE'),
        'stddev_price':otq.Stddev(In='PRICE'),
        'max_price':otq.Max(In='PRICE'),
        'min_price':otq.Min(In='PRICE'),
        'sum_size':otq.Sum(In='SIZE'),
        'count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,group_by='TRADE_CURRENCY',append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(agg_stats(),
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,8,0,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='Europe/London',
    symbols='LSE_SAMPLE::VOD'
    )

Common Aggregations for Vodafone grouped by Trade Currency retrieval results#
	Time	mean_price	variance_price	stddev_price	max_price	min_price	sum_size	count_price	TRADE_CURRENCY
0	2024-01-03 16:00:00	0.8179242424242427	3.2745638200183425e-06	0.001809575591131341	0.8215	0.8135	2078.0	33.0	EUR
1	2024-01-03 16:00:00	70.24008818255918	0.06472923392561547	0.2544194055602195	71.0166	69.62	67891479.0	6135.0	GBX

Group by Time Bucket#

Data can be grouped by time bucket, with the bucket_interval attribute, which specifies an interval in seconds.

Common Aggregations for Vodafone grouped by 5 Minute bar retrieval#

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'mean_price':otq.Average(In='PRICE'),
        'variance_price':otq.Variance(In='PRICE'),
        'stddev_price':otq.Stddev(In='PRICE'),
        'max_price':otq.Max(In='PRICE'),
        'min_price':otq.Min(In='PRICE'),
        'sum_size':otq.Sum(In='SIZE'),
        'count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,bucket_interval=300,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(agg_stats(),
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,8,0,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='Europe/London',
    symbols='LSE_SAMPLE::VOD'
    )

Common Aggregations for Vodafone grouped by 5 Minute time bucket retrieval results#
	Time	mean_price	variance_price	stddev_price	max_price	min_price	sum_size	count_price
0	2024-01-03 08:05:00	69.51150526315801	59.18892437740271	7.693433848250254	70.57	0.8175	711209.0	323.0
1	2024-01-03 08:10:00	69.14827685185186	88.09916282363076	9.386115427781121	70.54	0.8185	181979.0	108.0
2	2024-01-03 08:15:00	68.4568388888889	130.72549948876542	11.433525243281943	70.54	0.817	193178.0	72.0
3	2024-01-03 08:20:00	69.44766363636361	54.14805069049587	7.358535906720567	70.3798	0.816	308094.0	88.0
4	2024-01-03 08:25:00	69.45550133333327	63.664224576398226	7.978986437912915	70.45	0.818	59217.0	75.0
5	2024-01-03 08:30:00	68.07685967741935	150.80224820885795	12.280156685028818	70.48	0.817	170563.0	62.0
6	2024-01-03 08:35:00	65.17488499999997	335.829754072775	18.32565835305174	70.49	0.816	111955.0	40.0
7	2024-01-03 08:40:00	70.49129777777776	0.00038296821728400386	0.019569573763472823	70.54	70.4598	152794.0	45.0
8	2024-01-03 08:45:00	69.6322601449275	69.63640813920871	8.344843206388525	70.72	0.8195	926490.0	138.0
9	2024-01-03 08:50:00	70.65360000000003	0.00033177191489408304	0.018214607184731793	70.7	70.62	130943.0	47.0

Sub-second time buckets can be defined by setting bucket_interval to a decimal, e.g. bucket_interval=0.5.

Longer time buckets can be defined by additionally setting bucket_interval_units. The default value is SECONDS, but it can also be set to: DAYS, and MONTHS.

Group by Record Bucket#

Data can be grouped by count of records, with the bucket_interval_units attribute set to TICKS, which specifies the number of records to group on.

Common Aggregations for Vodafone grouped by 1000 records#

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'mean_price':otq.Average(In='PRICE'),
        'variance_price':otq.Variance(In='PRICE'),
        'stddev_price':otq.Stddev(In='PRICE'),
        'max_price':otq.Max(In='PRICE'),
        'min_price':otq.Min(In='PRICE'),
        'sum_size':otq.Sum(In='SIZE'),
        'count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,bucket_interval=1000,bucket_interval_units='TICKS',append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(agg_stats(),
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,8,0,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='Europe/London',
    symbols='LSE_SAMPLE::VOD'
    )

Common Aggregations for Vodafone grouped by 1000 Records retrieval results#
	Time	mean_price	variance_price	stddev_price	max_price	min_price	sum_size	count_price
0	2024-01-03 08:50:40.916	69.24078970000011	80.98814589834379	8.99934141470051	70.72	0.816	2951889.0	1000.0
1	2024-01-03 09:56:27.519	70.10265420000002	33.845474953362334	5.817686391802358	71.0166	0.818	5662152.0	1000.0
2	2024-01-03 11:30:39.417	70.10082600000005	14.459129472464003	3.8025162027878334	70.54	0.8165	48907554.0	1000.0
3	2024-01-03 13:35:26.643	70.12389239999996	4.815183342662229	2.1943526021727293	70.46	0.8175	2829333.0	1000.0
4	2024-01-03 14:42:02.384	69.8510987	14.358625114808321	3.789277650794188	70.33	0.814	3617405.0	1000.0
5	2024-01-03 15:48:14.860	69.88592240000004	4.779352033998229	2.186172919509852	70.11	0.8165	3508338.0	1000.0
6	2024-01-03 16:00:00.000	69.31398273809522	28.103589858213933	5.301281907068698	70.06	0.8135	416886.0	168.0

Generating Bars#

Intraday bars can be generated by querying with the Compute object with both

bucket_interval

Additional aggregations for FIRST, LAST, FIRST_TIME, HIGH_TIME, LOW_TIME, LAST_TIME

5 Minute Bars for Cisco retrieval#

def extended_ohlc():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'first_price':otq.First(In='PRICE'),
        'high_price':otq.High(In='PRICE'),
        'low_price':otq.Low(In='PRICE'),
        'last_price':otq.Last(In='PRICE'),
        'first_price_time':otq.FirstTime(),
        'high_price_time':otq.HighTime(In='PRICE'),
        'low_price_time':otq.LowTime(In='PRICE'),
        'last_price_time':otq.LastTime(),
        'sum_size':otq.Sum(In='SIZE'),
        'trade_count':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,bucket_interval=300,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(extended_ohlc(),
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

5 Minute Bars for Cisco retrieval results#
	Time	first_price	high_price	low_price	last_price	first_price_time	high_price_time	low_price_time	last_price_time	sum_size	trade_count
0	2024-01-03 09:35:00	50.02	50.23	50.0	50.02	2024-01-03 09:30:00.065443591	2024-01-03 09:30:05.174806860	2024-01-03 09:33:51.493753463	2024-01-03 09:34:59.547741776	779467.0	3712.0
1	2024-01-03 09:40:00	50.01	50.1395	49.94	50.0833	2024-01-03 09:35:00.095922915	2024-01-03 09:37:25.276672540	2024-01-03 09:36:06.705386856	2024-01-03 09:39:59.651495204	147818.0	1896.0
2	2024-01-03 09:45:00	50.085	50.14	50.04	50.07	2024-01-03 09:40:00.015000664	2024-01-03 09:40:18.138603509	2024-01-03 09:43:00.516838233	2024-01-03 09:44:59.912387013	143668.0	1433.0
3	2024-01-03 09:50:00	50.07	50.15	50.05	50.1	2024-01-03 09:45:00.013469654	2024-01-03 09:46:32.741722528	2024-01-03 09:49:48.540425333	2024-01-03 09:49:59.706588751	159612.0	1652.0
4	2024-01-03 09:55:00	50.11	50.19	50.1041	50.16	2024-01-03 09:50:00.389976008	2024-01-03 09:51:50.005109227	2024-01-03 09:50:00.947211308	2024-01-03 09:54:59.901791627	161803.0	1666.0
5	2024-01-03 10:00:00	50.16	50.22	50.14	50.205	2024-01-03 09:55:00.005706945	2024-01-03 09:59:22.005392587	2024-01-03 09:58:16.679655044	2024-01-03 09:59:59.144159543	161422.0	1671.0
6	2024-01-03 10:05:00	50.205	50.29	50.1719	50.268	2024-01-03 10:00:00.007939526	2024-01-03 10:00:21.986992990	2024-01-03 10:00:05.113317108	2024-01-03 10:04:59.951975207	214031.0	1988.0
7	2024-01-03 10:10:00	50.2681	50.32	50.185	50.315	2024-01-03 10:05:00.788414080	2024-01-03 10:07:37.790829427	2024-01-03 10:08:37.928301939	2024-01-03 10:09:59.441812631	127437.0	1194.0
8	2024-01-03 10:15:00	50.315	50.36	50.185	50.195	2024-01-03 10:10:00.284141079	2024-01-03 10:10:55.022511296	2024-01-03 10:13:21.447207443	2024-01-03 10:14:59.948432438	130511.0	1242.0
9	2024-01-03 10:20:00	50.2	50.25	50.16	50.17	2024-01-03 10:15:00.181994075	2024-01-03 10:17:06.483267067	2024-01-03 10:19:33.891143248	2024-01-03 10:19:59.101628980	124294.0	1506.0

VWAP (Volume Weighted Average Price)#

VWAP Bars can be calculated with the Vwap aggregate, by specifying the price_field_name and size_field_name attributes. :

5 Minute VWAP Bars for Vodafone retrieval#

def bar_creation():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'avg_price':otq.Avg(In='PRICE'),
        'vwa_price':otq.Vwap(price_field_name='PRICE',size_field_name='SIZE'),
       })
    comp = otq.Compute(compute=comp_fields,bucket_interval=300,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

query = bar_creation()

result = otq.run(query,
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

5 Minute VWAP Bars for Cisco retrieval results#
	Time	avg_price	vwa_price
0	2024-01-03 09:35:00	50.09713636853415	50.090611162627766
1	2024-01-03 09:40:00	50.04034646624484	50.04032952076192
2	2024-01-03 09:45:00	50.0834117236568	50.08140410042594
3	2024-01-03 09:50:00	50.10465242130783	50.102608470540915
4	2024-01-03 09:55:00	50.15397154861881	50.152175912683916
5	2024-01-03 10:00:00	50.16676696588909	50.16621400676494
6	2024-01-03 10:05:00	50.24540989788723	50.24709429735875
7	2024-01-03 10:10:00	50.29460758961489	50.29582476969793
8	2024-01-03 10:15:00	50.229827133655085	50.22988002926964
9	2024-01-03 10:20:00	50.1999912350597	50.19942339050965

TWAP (Time Weghted Average Price)#

TWAP Bars are calculated ussing the TwAverage aggregate.

5 Minute TWAP Bars for Cisco retrieval#

def bar_creation():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
        'avg_price':otq.Avg(In='PRICE'),
        'twa_price':otq.TwAverage(In='PRICE'),
       })
    comp = otq.Compute(compute=comp_fields,bucket_interval=300,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

result = otq.run(bar_creation(),
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

5 Minute TWAP Bars for Cisco retrieval results#
	Time	avg_price	twa_price
0	2024-01-03 09:35:00	50.09713636853415	50.07525358496618
1	2024-01-03 09:40:00	50.04034646624484	50.043441644537054
2	2024-01-03 09:45:00	50.0834117236568	50.07924286250883
3	2024-01-03 09:50:00	50.10465242130783	50.105280701937176
4	2024-01-03 09:55:00	50.15397154861881	50.14940462844198
5	2024-01-03 10:00:00	50.16676696588909	50.16806008470172
6	2024-01-03 10:05:00	50.24540989788723	50.249991936891156
7	2024-01-03 10:10:00	50.29460758961489	50.293147505767514
8	2024-01-03 10:15:00	50.229827133655085	50.22773611548845
9	2024-01-03 10:20:00	50.1999912350597	50.20105303086952

Exponential Moving Averages#

Two exponential moving average aggregates are supported:

ExpWAverage - Exponential Weighted Average
ExpTwAverage - Exponential Time Weighted Average

Both expect a field to aggregate upon, and a decay value. In case of ExpWAverage the decay value type is lambda. In the case of ExpTwAverage the decay value type is the half life in seconds.

Exponential Moving Averages for Cisco retrieval#

def ewa():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    ewa_price = otq.ExpWAverage(In='PRICE',decay=0.01,Out='ewa_price', is_running_aggr=True,all_fields_for_sliding=True)
    etwa_price = otq.ExpTwAverage(In='PRICE',decay=300,Out='etwa_price', is_running_aggr=True,all_fields_for_sliding=True)
    graph = otq.Graph(trd_in >> ewa_price >> etwa_price)
    return graph

result = otq.run(ewa(),
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,40,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Exponential Moving Averages for Cisco retrieval results#
	Time	PRICE	SIZE	ewa_price	etwa_price
0	2024-01-03 09:30:00.065443591	50.02	2	50.02	50.02
1	2024-01-03 09:30:00.111130049	50.16	3	50.090349997083365	50.02
2	2024-01-03 09:30:00.127459523	50.17	100	50.117165936129354	50.05613095222338
3	2024-01-03 09:30:00.128498068	50.17	5	50.130573235281794	50.05793852688784
4	2024-01-03 09:30:00.135190071	50.13	46	50.130456283896734	50.06914548980511
5	2024-01-03 09:30:00.232554141	50.13	117	50.13037832280345	50.1044949798276
6	2024-01-03 09:30:00.232556132	50.05	83	50.118548373622104	50.1044949798276
7	2024-01-03 09:30:00.280877749	50.002	200	50.103464859255894	50.09232631144301
8	2024-01-03 09:30:00.321495830	50.002	9	50.091734800913684	50.077856394772525
9	2024-01-03 09:30:00.372805563	50.085	1	50.091030612488204	50.06525111673731

Ranking & Row Numbering#

Both the RANK and ROW_NUMBER aggregates can be used to rank results. They both use the over clause to define the field and sort order to rank over.

Ranking by Trade Size descending retrieval#

def ranking():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    rank = otq.Ranking(rank_by='SIZE desc')
    update_ts = otq.UpdateField(field='TIMESTAMP',value='TICK_TIME')
    drop_tt = otq.Passthrough(fields='TICK_TIME',drop_fields=True)
    graph = otq.Graph(trd_in >> rank >> update_ts >> drop_tt)
    return graph

result = otq.run(ranking(),
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Ranking by Trade Size descending retrieval results#
	Time	PRICE	SIZE	RANKING
0	2024-01-03 09:30:00.065443591	50.02	2	109524.0
1	2024-01-03 09:30:00.111130049	50.16	3	107868.0
2	2024-01-03 09:30:00.127459523	50.17	100	23810.0
3	2024-01-03 09:30:00.128498068	50.17	5	103653.0
4	2024-01-03 09:30:00.135190071	50.13	46	79252.0
5	2024-01-03 09:30:00.232554141	50.13	117	22530.0
6	2024-01-03 09:30:00.232556132	50.05	83	68838.0
7	2024-01-03 09:30:00.280877749	50.002	200	9946.0
8	2024-01-03 09:30:00.321495830	50.002	9	99668.0
9	2024-01-03 09:30:00.372805563	50.085	1	116449.0

Row Number by Trade Size descending retrieval#

def row_number():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    order_by = otq.OrderBy(order_by='SIZE desc')
    row_number = otq.Count(is_running_aggr=True,all_fields_for_sliding='True',output_field_name='ROW_ID')
    graph = otq.Graph(trd_in >> order_by >> row_number)
    return graph

result = otq.run(row_number(),
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Row Number by Trade Size descending retrieval results#
	Time	PRICE	SIZE	ROW_ID
0	2024-01-03 09:30:00.887879969	50.09	262623	1.0
1	2024-01-03 09:30:00.888066374	50.09	262623	2.0
2	2024-01-03 15:52:44.480586353	50.53	42200	3.0
3	2024-01-03 12:58:55.735861511	50.4	40299	4.0
4	2024-01-03 13:48:06.134503605	50.51	36600	5.0
5	2024-01-03 12:46:27.480551453	50.41	29928	6.0
6	2024-01-03 15:40:17.109779197	50.605	27638	7.0
7	2024-01-03 15:44:28.782357709	50.6	25900	8.0
8	2024-01-03 15:17:20.177891647	50.51	21110	9.0
9	2024-01-03 10:06:46.688581240	50.295	19233	10.0

Ranking can also be used in a nested query to filter the returned results to the top n per group.

Top 3 Trades by Trade Size desc, across each Exchange#

def top_ten_per_group():
    trd_in= otq.Passthrough(fields='PRICE,SIZE,EXCHANGE').tick_type('TRD')
    rank = otq.Ranking(rank_by='SIZE desc',group_by='EXCHANGE')
    top_ten_filter = otq.WhereClause(where='RANKING <= 3')
    drop_tt = otq.Passthrough(fields='TICK_TIME',drop_fields=True)
    graph = otq.Graph(trd_in >> rank >> top_ten_filter >> drop_tt)
    return graph

result = otq.run(top_ten_per_group(),
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,16,0,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Top 3 Trades by Trade Size desc, across each Exchange#
	Time	PRICE	SIZE	EXCHANGE	RANKING
0	2024-01-03 16:00:00	50.51	400	A	3.0
1	2024-01-03 16:00:00	50.535	400	A	3.0
2	2024-01-03 16:00:00	50.5	567	A	2.0
3	2024-01-03 16:00:00	50.5	634	A	1.0
4	2024-01-03 16:00:00	50.29	522	B	2.0
5	2024-01-03 16:00:00	50.2	500	B	3.0
6	2024-01-03 16:00:00	50.2	500	B	3.0
7	2024-01-03 16:00:00	50.51	500	B	3.0
8	2024-01-03 16:00:00	50.55	500	B	3.0
9	2024-01-03 16:00:00	50.55	1000	B	1.0