Phone: +1 (888) 427-9486
+1 (312) 257-3777
Contact Us
WMA
Returns an array of cells for the weighted moving average in Excel time series.
Syntax
X
is the univariate time series data (a one dimensional array of cells (e.g. rows or columns)).
Order
is the time order in the data series (i.e. whether the first data point corresponds to the earliest or latest date (earliest date=1 (default), latest date=0)).
Order | Description |
---|---|
1 | ascending (the first data point corresponds to the earliest date) (default) |
0 | descending (the first data point corresponds to the latest date) |
M
is the size of the weighted moving average in Excel window.
W
is an array of multiplying factors (i.e. weights) for the moving/rolling window data points. If omitted, a default of equal weighting is assumed.
Remarks
- The time series is homogeneous or equally spaced.
- The time series may include missing values (e.g. #N/A) at either end.
- The window size (m) must be less than the time series size, or else an error value (#VALUE!) is returned.
- The weights array should have a size greater than zero and consist of non-negative values.
- The size argument must match the actual size of the passed weight array, or else an error value (#VALUE) is returned.
- The weighted moving average in Excel (WMA) is defined as:
Where:
- is the weight of the i-th data point in the moving/rolling window.
- is the size of the moving/rolling window.
- is the value of the time series at time t.
- IMPORTANT: The first value in the weights array corresponds to the earliest point in the MA window.
- IMPORTANT: To exclude current observation from the moving average in Excel, set the last value (weight) in the given array to zero.
- The size of the weighted moving averqage time series is equal to the input time series.
Examples
Example 1:
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | Date | Data | ||||
2 | January 10, 2008 | -0.30 | -0.30 | -0.30 | ||
3 | January 11, 2008 | -1.28 | -0.79 | -0.86 | ||
4 | January 12, 2008 | 0.24 | -0.45 | -0.39 |
Step |
Weight |
5 | January 13, 2008 | 1.28 | 0.08 | 0.30 | 1 | 1 |
6 | January 14, 2008 | 1.20 | 0.91 | 0.78 | 2 | 2 |
7 | January 15, 2008 | 1.73 | 1.40 | 1.33 | 3 | 3 |
8 | January 16, 2008 | -2.18 | 0.25 | 0.01 | 4 | 4 |
9 | January 17, 2008 | -0.23 | -0.23 | -0.28 | ||
10 | January 18, 2008 | 1.10 | -0.44 | 0.11 | ||
11 | January 19, 2008 | -1.09 | -0.07 | -0.37 | ||
12 | January 20, 2008 | -0.69 | -0.23 | -0.41 | ||
13 | January 21, 2008 | -1.69 | -1.16 | -0.99 | ||
14 | January 22, 2008 | -1.85 | -1.41 | -1.49 | ||
15 | January 23, 2008 | -0.98 | -1.51 | -1.35 | ||
16 | January 24, 2008 | -0.77 | -1.20 | -1.14 | ||
17 | January 25, 2008 | -0.30 | -0.68 | -0.73 | ||
18 | January 26, 2008 | -1.28 | -0.78 | -0.85 | ||
19 | January 27, 2008 | 0.24 | -0.45 | -0.42 | ||
20 | January 28, 2008 | 1.28 | 0.08 | 0.30 | ||
21 | January 29, 2008 | 1.20 | 0.91 | 0.78 | ||
22 | January 30, 2008 | 1.73 | 1.40 | 1.33 | ||
23 | January 31, 2008 | -2.18 | 0.25 | 0.01 | ||
24 | February 1, 2008 | -0.23 | -0.23 | -0.28 | ||
25 | February 2, 2008 | 1.10 | -0.44 | 0.11 | ||
26 | February 3, 2008 | -1.09 | -0.07 | -0.37 | ||
27 | February 4, 2008 | -0.69 | -0.23 | -0.41 | ||
28 | February 5, 2008 | -1.69 | -1.16 | -0.99 | ||
29 | February 6, 2008 | -1.85 | -1.41 | -1.49 | ||
30 | February 7, 2008 | -0.98 | -1.51 | -1.35 |
References
- Hamilton, J .D.; Time Series Analysis , Princeton University Press (1994), ISBN 0-691-04289-6
- Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0-471-690740