Windowing#

A series of time windowing examples are provided showing how to aggregate across time windows, and retrieve time and record relative values, using OneTick Cloud sample databases.

Accumulative Sum#

Accumulative sums are returned through use of the Sum class with both:

  • is_running_aggr=True. To define a running aggregation.

  • all_fields_for_sliding=True. To define that all other fields should be output.

Additionally:

  • In attribute defines the input field

  • Out attribute defines the output field

Accumulative Sum of Trade Size across the time window#
def acc_sum():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    acc_sum = otq.Sum(In='SIZE',is_running_aggr=True,all_fields_for_sliding=True,Out='ACC_SIZE')
    graph = otq.Graph(trd_in >> acc_sum)
    return graph

result = otq.run(acc_sum(),
    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'
    )
Accumulative Sum of SIZE across the time window results#

Time

PRICE

SIZE

ACC_SIZE

0

2024-01-03 09:30:00.065443591

50.02

2

2.0

1

2024-01-03 09:30:00.111130049

50.16

3

5.0

2

2024-01-03 09:30:00.127459523

50.17

100

105.0

3

2024-01-03 09:30:00.128498068

50.17

5

110.0

4

2024-01-03 09:30:00.135190071

50.13

46

156.0

5

2024-01-03 09:30:00.232554141

50.13

117

273.0

6

2024-01-03 09:30:00.232556132

50.05

83

356.0

7

2024-01-03 09:30:00.280877749

50.002

200

556.0

8

2024-01-03 09:30:00.321495830

50.002

9

565.0

9

2024-01-03 09:30:00.372805563

50.085

1

566.0

Rolling Sum#

Rolling sums are returned with a syntax similar to the Accumulative Sum. However in this case the bucket_interval attribute is specified. e.g. bucket_interval=60

Rolling Sum of Trade Size across a 60 second rolling period#
def rolling_sum():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    rolling_sum = otq.Sum(In='SIZE',bucket_interval=60,is_running_aggr=True,all_fields_for_sliding=True,Out='ROLLING_SIZE_1M')
    graph = otq.Graph(trd_in >> rolling_sum)
    return graph

result = otq.run(rolling_sum(),
    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'
    )
Rolling Sum of Trade Size across a 60 second rolling period results#

Time

PRICE

SIZE

ROLLING_SIZE_1M

0

2024-01-03 09:30:00.065443591

50.02

2

2.0

1

2024-01-03 09:30:00.111130049

50.16

3

5.0

2

2024-01-03 09:30:00.127459523

50.17

100

105.0

3

2024-01-03 09:30:00.128498068

50.17

5

110.0

4

2024-01-03 09:30:00.135190071

50.13

46

156.0

5

2024-01-03 09:30:00.232554141

50.13

117

273.0

6

2024-01-03 09:30:00.232556132

50.05

83

356.0

7

2024-01-03 09:30:00.280877749

50.002

200

556.0

8

2024-01-03 09:30:00.321495830

50.002

9

565.0

9

2024-01-03 09:30:00.372805563

50.085

1

566.0

Moving Average in Time#

Simple moving averages are returned with a similar syntax to rolling sums, using the Average class, and the attributes:

  • bucket_interval=60 - 60 second time window

  • is_running_aggr=True - Calculate a rolling window

  • all_fields_for_sliding=True - Output all existing fields

Trade Price plus 1 and 5 Minute Moving averages retrieval.#
def sma():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    sma1_price = otq.Average(In='PRICE',bucket_interval=60,is_running_aggr=True,all_fields_for_sliding=True,Out='SMA_1M')
    sma5_price = otq.Average(In='PRICE',bucket_interval=300,is_running_aggr=True,all_fields_for_sliding=True,Out='SMA_5M')
    graph = otq.Graph(trd_in >> sma1_price >> sma5_price)
    return graph

result = otq.run(sma(),
    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'
    )
Trade Price plus 1 and 5 Minute Moving averages retrieval results.#

Time

PRICE

SIZE

SMA_1M

SMA_5M

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.09

50.09

2

2024-01-03 09:30:00.127459523

50.17

100

50.116666666666674

50.116666666666674

3

2024-01-03 09:30:00.128498068

50.17

5

50.13000000000001

50.13000000000001

4

2024-01-03 09:30:00.135190071

50.13

46

50.13000000000001

50.13000000000001

5

2024-01-03 09:30:00.232554141

50.13

117

50.13

50.13

6

2024-01-03 09:30:00.232556132

50.05

83

50.118571428571435

50.118571428571435

7

2024-01-03 09:30:00.280877749

50.002

200

50.104000000000006

50.104000000000006

8

2024-01-03 09:30:00.321495830

50.002

9

50.09266666666667

50.09266666666667

9

2024-01-03 09:30:00.372805563

50.085

1

50.0919

50.0919

Moving Average in Records#

Moving averages can be calculated based on a number of rows, rather than a across a time period. In this case the bucket_interval attribute is changed from it’s default of SECONDS to TICKS.

5 Row Moving Average retrieval#
def sma():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    mavg_price = otq.Average(In='PRICE',bucket_interval=5,bucket_interval_units='TICKS',is_running_aggr=True,all_fields_for_sliding=True,Out='MAVG_PRICE')
    graph = otq.Graph(trd_in >> mavg_price)
    return graph

result = otq.run(sma(),
    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'
    )
5 Row Moving Average retrieval results.#

Time

PRICE

SIZE

MAVG_PRICE

0

2024-01-03 09:30:00.065443591

50.02

2

50.02

1

2024-01-03 09:30:00.111130049

50.16

3

50.09

2

2024-01-03 09:30:00.127459523

50.17

100

50.116666666666674

3

2024-01-03 09:30:00.128498068

50.17

5

50.13000000000001

4

2024-01-03 09:30:00.135190071

50.13

46

50.13000000000001

5

2024-01-03 09:30:00.232554141

50.13

117

50.152

6

2024-01-03 09:30:00.232556132

50.05

83

50.13000000000001

7

2024-01-03 09:30:00.280877749

50.002

200

50.0964

8

2024-01-03 09:30:00.321495830

50.002

9

50.0628

9

2024-01-03 09:30:00.372805563

50.085

1

50.05380000000001

Time Shifts#

Values can be retrieved relative to the current row by a specified time period in milliseconds using the time_shift function. , which requires specification of the desired field, plus the time shift in milliseconds. Negative time shifts retrieve the prevailing value at the specified number of milliseconds before the row timestamp. Positive time shifts retrieve the prevailing value at the specified number of milliseconds after the row timestamp.

Trades with Time shifts 1, 10, and 60 seconds before and after each trade retrieval#
def time_shifts():
    qte_in= otq.Passthrough(fields='BID_PRICE,ASK_PRICE').tick_type('QTE')
    mid = otq.AddField(field='MID_PRICE',value='(BID_PRICE + ASK_PRICE)/2')
    b_1s = otq.AddField(field='MID_BACK_1S',value='TIME_SHIFT("MID_PRICE",-1000)')
    b_10s = otq.AddField(field='MID_BACK_10S',value='TIME_SHIFT("MID_PRICE",-10000)')
    b_60s = otq.AddField(field='MID_BACK_60S',value='TIME_SHIFT("MID_PRICE",-60000)')
    f_1s = otq.AddField(field='MID_FWD_1S',value='TIME_SHIFT("MID_PRICE",1000)')
    f_10s = otq.AddField(field='MID_FWD_10S',value='TIME_SHIFT("MID_PRICE",10000)')
    f_60s = otq.AddField(field='MID_FWD_60S',value='TIME_SHIFT("MID_PRICE",60000)')
    graph = otq.Graph(qte_in >> mid >> b_1s >> b_10s >> b_60s >> f_1s >> f_10s >> f_60s)
    return graph

result = otq.run(time_shifts(),
    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'
    )
Trades with Time shifts 1, 10, and 60 seconds before and after each trade retrieval results#

Time

BID_PRICE

ASK_PRICE

MID_PRICE

MID_BACK_1S

MID_BACK_10S

MID_BACK_60S

MID_FWD_1S

MID_FWD_10S

MID_FWD_60S

0

2024-01-03 09:30:00.001830617

50.0

50.18

50.09

50.095

50.105000000000004

50.16

1

2024-01-03 09:30:00.002078349

49.8

50.37

50.084999999999994

50.095

50.105000000000004

50.16

2

2024-01-03 09:30:00.002215206

50.0

50.2

50.1

50.095

50.105000000000004

50.16

3

2024-01-03 09:30:00.002341904

49.8

50.37

50.084999999999994

50.084999999999994

50.105000000000004

50.16

4

2024-01-03 09:30:00.002524728

50.0

50.18

50.09

50.084999999999994

50.105000000000004

50.16

5

2024-01-03 09:30:00.002857475

0.0

0.0

0.0

25.045

50.105000000000004

50.16

6

2024-01-03 09:30:00.003295288

50.0

50.18

50.09

50.105000000000004

50.105000000000004

50.16

7

2024-01-03 09:30:00.003751324

49.8

50.21

50.004999999999995

50.105000000000004

50.105000000000004

50.16

8

2024-01-03 09:30:00.005117010

49.92

50.21

50.065

50.105000000000004

50.105000000000004

50.16

9

2024-01-03 09:30:00.006821601

50.0

50.18

50.09

50.105000000000004

50.105000000000004

50.16

Record Shifts / Lag & Lead#

Values can be retrieved relative to the current row by a specified number of records using the square bracket [] notation.

  • MID_PRICE[-1] - Return the MID_PRICE shifted back 1 record

  • MID_PRICE[+5] - Return the MID_PRICE shifted forwards 5 records

Positive offsets should be added as new fields using the AddField class.

Quotes with shifts 1, 10 and 100 records before and after each record#
def tick_shifts():
    qte_in= otq.Passthrough(fields='BID_PRICE,ASK_PRICE').tick_type('QTE')
    mid = otq.AddField(field='MID_PRICE',value='(BID_PRICE + ASK_PRICE)/2')
    b_1t = otq.AddField(field='MID_BACK_1T',value='MID_PRICE[-1]')
    b_10t = otq.AddField(field='MID_BACK_10T',value='MID_PRICE[-10]')
    b_100t = otq.AddField(field='MID_BACK_100T',value='MID_PRICE[-100]')
    f_1t = otq.AddField(field='MID_FWD_1T',value='MID_PRICE[1]')
    f_10t = otq.AddField(field='MID_FWD_10T',value='MID_PRICE[10]')
    f_100t = otq.AddField(field='MID_FWD_100T',value='MID_PRICE[100]')
    graph = otq.Graph(qte_in >> mid >> b_1t >> b_10t >> b_100t >> f_1t >> f_10t >> f_100t)
    return graph

result = otq.run(tick_shifts(),
    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'
    )
Quotes with shifts 1, 10 and 100 records before and after each record retrieval results#

Time

BID_PRICE

ASK_PRICE

MID_PRICE

MID_BACK_1T

MID_BACK_10T

MID_BACK_100T

MID_FWD_1T

MID_FWD_10T

MID_FWD_100T

0

2024-01-03 09:30:00.001830617

50.0

50.18

50.09

50.085

50.130

50.085

1

2024-01-03 09:30:00.002078349

49.8

50.37

50.085

50.09

50.1

50.09

50.085

2

2024-01-03 09:30:00.002215206

50.0

50.2

50.1

50.085

50.085

50.130

50.085

3

2024-01-03 09:30:00.002341904

49.8

50.37

50.085

50.1

50.09

50.09

50.085

4

2024-01-03 09:30:00.002524728

50.0

50.18

50.09

50.085

0.0

50.085

50.085

5

2024-01-03 09:30:00.002857475

0.0

0.0

0.0

50.09

50.09

50.07

50.085

6

2024-01-03 09:30:00.003295288

50.0

50.18

50.09

0.0

50.005

49.99

50.11

7

2024-01-03 09:30:00.003751324

49.8

50.21

50.005

50.09

50.065

50.085

50.115

8

2024-01-03 09:30:00.005117010

49.92

50.21

50.065

50.005

50.09

49.84

50.115

9

2024-01-03 09:30:00.006821601

50.0

50.18

50.09

50.065

50.130

50.095

50.115