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 detailed usage of “defg”, refer to: User-Defined Aggregate Functions .
funcArgs are the parameters of func. They can be vectors/dictionaries/tables. 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 |
$ 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);
[,,2,3]
$ moving(avg, 1..4, 3, 1);
[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 Partial Application to generate a new function. Please see the example below:
$ moving(percentile{,50},1..20, 10);
[,,,,,,,,,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