moving
Syntax
moving(func, funcArgs, window, [minPeriods])
Arguments
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