moving

Syntax

moving(func, funcArgs, window, [minPeriods])

Arguments

func is an aggregate function.
Note: When using this parameter, the keyword used to define the corresponding aggregation function is defg. For details, refer to Tutorial: User Defined Aggregate Functions.

funcArgs are the parameters of func. They can be vectors/dictionaries/tables/matrices. It is a tuple if there are more than one parameter of func, and all parameters must have the same size.

window is the moving window size.

minPeriods is a positive integer indicating the minimum number of observations in a window in order to generate a result. The default value is the value of window.

Details

Apply the function/operator to a moving window of the given objects.

The moving template always returns a vector with the same number of elements as the number of rows in the input arguments. It starts calculating when the moving window size is reached for the first time, and the moving window is always shifted by 1 element to the right thereafter.

Each of the built-in moving functions such as msum, mcount and mavg is optimized for its specific task. Therefore, they have much better performance than the moving template.

Examples

Calculate the moving beta of AAPL against the market (SPY) with a 10-day moving window.

date=2016.08.01..2016.08.31
date=date[1<=weekday(date)<=5]
aaplRet=0.0177 -0.0148 0.0125 0.0008 0.0152 0.0083 0.0041 -0.0074 -0.0006 0.0023 0.0120 -0.0009 -0.0015 -0.0013 0.0026 -0.0078 0.0031 -0.0075 -0.0043 -0.0059 -0.0011 -0.0077 0.0009
spyRet=-0.0008 -0.0064 0.0029 0.0011 0.0082 -0.0006 0.0006 -0.0025 0.0046 -0.0009 0.0029 -0.0052 0.0019 0.0022 -0.0015 0.0000 0.0020 -0.0051 -0.0007 -0.0019 0.0049 -0.0016 -0.0028
t=table(date, aaplRet, spyRet);
t;
date aaplRet spyRet
2016.08.01 0.0177 -0.0008
2016.08.02 -0.0148 -0.0064
2016.08.03 0.0125 0.0029
2016.08.04 0.0008 0.0011
2016.08.05 0.0152 0.0082
2016.08.08 0.0083 -0.0006
2016.08.09 0.0041 0.0006
2016.08.10 -0.0074 -0.0025
2016.08.11 -0.0006 0.0046
2016.08.12 0.0023 -0.0009
2016.08.15 0.012 0.0029
2016.08.16 -0.0009 -0.0052
2016.08.17 -0.0015 0.0019
2016.08.18 -0.0013 0.0022
2016.08.19 0.0026 -0.0015
2016.08.22 -0.0078 0
2016.08.23 0.0031 0.002
2016.08.24 -0.0075 -0.0051
2016.08.25 -0.0043 -0.0007
2016.08.26 -0.0059 -0.0019
2016.08.29 -0.0011 0.0049
2016.08.30 -0.0077 -0.0016
2016.08.31 0.0009 -0.0028
// calculate moving beta
update t set beta=moving(beta, [aaplRet, spyRet],10);
t;
date aaplRet spyRet beta
2016.08.01 0.0177 -0.0008
2016.08.02 -0.0148 -0.0064
2016.08.03 0.0125 0.0029
2016.08.04 0.0008 0.0011
2016.08.05 0.0152 0.0082
2016.08.08 0.0083 -0.0006
2016.08.09 0.0041 0.0006
2016.08.10 -0.0074 -0.0025
2016.08.11 -0.0006 0.0046
2016.08.12 0.0023 -0.0009 1.601173
2016.08.15 0.012 0.0029 1.859846
2016.08.16 -0.0009 -0.0052 1.248804
2016.08.17 -0.0015 0.0019 1.114282
2016.08.18 -0.0013 0.0022 1.064296
2016.08.19 0.0026 -0.0015 0.512656
2016.08.22 -0.0078 0 0.614963
2016.08.23 0.0031 0.002 0.642491
2016.08.24 -0.0075 -0.0051 0.70836
2016.08.25 -0.0043 -0.0007 0.977279
2016.08.26 -0.0059 -0.0019 1.064465
2016.08.29 -0.0011 0.0049 0.422221
2016.08.30 -0.0077 -0.0016 0.793236
2016.08.31 0.0009 -0.0028 0.588027

About the role of minPeriods :

moving(avg, 1..4, 3);
// output
[,,2,3]

moving(avg, 1..4, 3, 1);
// output
[1,1.5,2,3]

If the function used in the moving template has multiple parameters, they must have the same size. For functions with parameters of different sizes, such as percentile, we can use PartialApplication to generate a new function. Please see the example below:

moving(percentile{,50},1..20, 10);
// output
[,,,,,,,,,5.5,6.5,7.5,8.5,9.5,10.5,11.5,12.5,13.5,14.5,15.5]

Performance Tip

When we calculate moving averages, we should use the built-in function of mavg instead of the moving template, as the built-in functions are optimized and run faster than template functions.

n=1000000
x=norm(0,1, n);
timer mavg(x, 10);
Time elapsed: 3.501ms

timer moving(avg, x, 10);
Time elapsed: 976.03ms