What is it
This is an add-in for Microsoft Excel that provides four functions: FillMissing, MA, HP, and seas.
These functions take “data” as first argument. “data” contains the time series that we work on. The time series are supposed to be organized column-wise. If a single time series is to be worked on, “data” must be a column vector. If “data” contains multiple columns, the individual columns will be dealt with separately.
“data” can be a range in a worksheet or it can be a two-dimensional VBA array.
MA, HP, and seas deal with missing values by first filling those in using FillMissing.
Usage of FillMissing function
FillMissing(data) determines where “data” contains non-numeric or blank components. These components are then filled in using a linear interpolation. FillMissing returns an array with the same dimensions as “data”. Missig values at the edge of the series (the first or last few observations) are not filled, meaning the FillMissing only does intrapolations but no extrapolations.
Usage of MA function
MA(data,width) computes the moving average of time series of a window of a particular “width”. “width” can be any positive number (including non-integer). The larger “width” is chosen, the smoother the resulting moving average. MA returns an array with the same dimensions as “data”.
There is a third, optional argument, MA(data,width,doFill). “doFill” is a Boolean. If True, any missing values will first be replaced in using the FillMissing function. If this is set to False, then missing values will not be filled and MA will compute a weighted average using only the available data points. The result is different if there are missing values in “data” and often less convincing, which is why “doFill” is set to True by default.
Usage of HP function
HP(data,lambda) computes the Hodrick-Prescott low pass filter of time series in “data”.
“lambda” is a smoothing parameter. It must be a strictly positive number. The larger “lambda”, the smoother the trend (i.e. the lower is the cut-off frequency of the low pass filter).
HP returns an array with the same dimensions as “data”. The columns contain the HP-trends of the time series. To compute the cyclical component, simply compute the difference between the data and the trend.
Usage of seas function
seas(data,period,kind,method,lambda) splits data into three components.
inputs:
“data” must be a single column. Unlike the other functions of this add-in, the seas function does not accommodate multiple time series in separate columns.
“period” is the frequency of the seasonal component. For instance, if you have daily data and want to filter out a weakly pattern, set period to 7. If you have monthly data and want to filter out a yearly seasonal pattern, set period to 12.
If “kind” is “additive”, the decomposition is data = trend + seasonal + irregular.
If “kind” is “multiplicative” (or “log”), the decomposition is log(data) = log(trend) + log(seasonal) + log(irregular) or equivalently, data = trend * seasonal * irregular
“method” is either “ma” (for moving average) or “hp” (for Hodrick-Prescott). If using the Hodrick-Prescott, then “lambda” is the HP-smoothing factor. “lambda” is irrelevant when using the moving average. The width used to compute the moving average is set equal to “period”.
outputs:
seas returns data in four columns. The first column contains the trend (i.e. either the moving average or the Hodrick-Prescott filtered data). The second column contains the seasonally adjusted data. The third column contains the seasonal factor. By definition,
seasonally adjusted = data – seasonal factor (if “kind” is “additive”), or
seasonally adjusted = data / seasonal factor (if “kind” is “multiplicative”).
The fourth column contains the remaining part, called the irregular component.
Again, by definition, data = trend + seasonal factor + irregular, hence, irregular = seasonally adjusted – trend.
Note on Range functions
These four function are are so-called Range-Functions. This means that the user must first specify a whole range on the worksheet where the output of the functions will be shown, then enter the function with its parameters in the formula bar, and then complete with Shift-Ctrl-Enter (typing just Enter will not do).
