dailyAlignedBar

Syntax

dailyAlignedBar(X, timeOffset, n, [timeEnd], [mergeSessionEnd=false])

Related function: bar

Arguments

X is a temporal vector of type SECOND, TIME, NANOTIME, DATETIME, TIMESTAMP or NANOTIMESTAMP.

timeOffset is a scalar/vector of type SECOND, TIME or NANOTIME with the same accuracy of X indicating the left boundary of session(s). If it is a vector, it must be increasing.

n is a positive integer or DURATION type data indicating the window length. If n is a positive integer, its unit is the minimum accuracy of timeOffset. If n is a DURATION type data, its unit cannot be y, M, w, d, B.

timeEnd is an optional parameter of the same type and length of timeOffset indicating the right boundary of session(s).

mergeSessionEnd is a Boolean value. When the right boundary of a session (as specified in timeEnd) is also the right boundary of a window, if mergeSessionEnd=true, the right boundary of the session is merged into the previous window.

Details

Determine windows based on the starting time (specified by timeOffset), window length (specified by n), and possibly ending time (specified by timeEnd). For each element of X, return the starting time of the window it belongs to. Specifically, return X-((X-timeOffset)%n) for each element of X and return a vector with the same length as X.

Generally, a window includes the left boundary but not the right boundary. If mergeSessionEnd=true and the right boundary of a session is also the right boundary of a window, the right boundary of the session is merged into the previous window.

The function supports overnight sessions.

Examples

Please note that the examples below use randomly generated data for the column of price. The result is different each time you execute it.

Ex 1. The Chinese stock market has 2 sessions each day: from 9:30AM to 11:30AM and from 1PM to 3PM. The script below calculates rolling 1-hour average prices for these sessions.

$ sessionsBegin = 09:30:00 13:00:00
$ ts = 2019.11.01T09:30:00..2019.11.01T11:30:00 join 2019.11.01T13:00:00..2019.11.01T15:00:00
$ t = table(ts, rand(10.0, size(ts)) as price);

$ select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessionsBegin, 60*60) as k60;

k60

price

count

2019.11.01T09:30:00

5.031685383252463

3600

2019.11.01T10:30:00

5.022667285786399

3600

2019.11.01T11:30:00

4.930270051117987

1

2019.11.01T13:00:00

4.931854071494632

3600

2019.11.01T14:00:00

4.979529541734115

3600

2019.11.01T15:00:00

0.961996954865754

1

As a window includes the left boundary but not the right boundary, if the right boundary of a session is also the right boundary of a window as in the example above, the right boundary of the session belongs to a window that has no other records if timeEnd and mergeSessionEnd are not specified. In most cases we would like to merge the right boundary of a session to the previous window. Please refer to the example below.

$ sessionsEnd = 11:30:00 15:00:00;
$ select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessionsBegin, 60*60, sessionsEnd, true) as k60;

k60

price

count

2019.11.01T09:30:00

5.031685383252463

3600

2019.11.01T10:30:00

5.022641627015316

3601

2019.11.01T13:00:00

4.931854071494632

3600

2019.11.01T14:00:00

4.978413870368697

3601

Ex 2. The futures market has 2 sessions each day: from 1:30PM to 4:30PM and from 10:30PM to 2:30AM the next day. dailyAlignedBar is used to calculate 7-minute average prices for these sessions. Please note that we simulate 2 days’ data in the example.

$ sessions = 13:30:00 22:30:00
$ ts = 2019.11.01T13:30:00..2019.11.01T16:30:00 join 2019.11.01T22:30:00..2019.11.02T02:30:00
$ ts = ts join (ts+60*60*24)
$ t = table(ts, rand(10.0, size(ts)) as price)
$ select avg(price) as price, count(*) as count from t group by dailyAlignedBar(ts, sessions, 7m) as k7;

Ex 3. When calculating 1-minute OHLC bars, the data type needs to be converted to LONG if n needs to be converted to NANOTIMESTAMP, otherwise an integer overflow will occur.

 1$ n = 1000000
 2$ nano=(09:30:00.000000000 + rand(long(6.5*60*60*1000000000), n)).sort!()
 3$ sessionStartNano=09:30:00.000000000
 4$ price = 100+cumsum(rand(0.02, n)-0.01)
 5$ volume = rand(1000, n)
 6$ symbol = rand(`600519`000001`600000`601766, n)
 7$ tradeNano=table(symbol, nano, price, volume).sortBy!(`symbol`nano)
 8$ undef(`nano`price`volume`symbol)
 9$ barMinutes=7
10$ itv = barMinutes*60*long(1000000000)
11
12$ OHLC_nano=select first(price) as open, max(price) as high, min(price) as low, last(price) as close, sum(volume) as volume from tradeNano group by symbol, dailyAlignedBar(nano, sessionStartNano, itv) as barStart