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