A macro to filter time series data in Excel

Introduction

In this post I want to show one way to filter time series data using a custom Excel function. For the theory I rely heavily on a course by the Australian Bureau of Statistics which can be found here.

So, why would you want to filter time series data? What is a filter? And what is a time series?

A time series is a collection of data points observed through time. For example, the monthly retail sales of clothing and footwear would be one such series.

A filter in this sense is a set of numbers that are applied to the time series to produce a moving average. So, for example, a filter could be the set of numbers (0.2, 0.6, 0.2). This would be applied to a data point and the points before and after (in the case of a symmetric filter) to produce an average i.e.:

y'(i) = 0.2*y(i-1) + 0.6*y(i) + 0.2*y(i+1)

The whole point of this is to remove frequencies or seasonality within the data. For instance with monthly time series you might want to remove events which occur every 12 months.

Mentioned in passing above was the fact that the example given is a symmetric filter. The other type is, unsurprisingly called an asymmetric filter. Symmetric filters have an odd number of weights and these weights are the same around the central weight i.e. w(i) = w(-i).

The macro

The easiest and most efficient way to apply this filter in Excel is by writing an array function in VBA. The code is shown below.

---------------------------------------------------------------------------------

Function SeasonFilter(dataRange As range, filter As Variant) As Variant

    Dim OutputRows As Long
    Dim OutputCols As Long
    Dim output() As Double
    Dim vert As Boolean
    Dim i As Integer
    Dim j As Integer
    Dim size As Integer
    Dim width As Integer
    Dim b As Double
    Dim c() As Double
    
    If TypeName(filter) = "Range" Then
        If filter.Rows.Count > filter.Columns.Count Then
            ReDim c(1 To filter.Rows.Count)
            For i = 1 To UBound(c)
                c(i) = filter(i, 1)
            Next
        Else
            ReDim c(1 To filter.Columns.Count)
            For i = 1 To UBound(c)
                c(i) = filter(1, i)
            Next
        End If
    Else
        ReDim c(1 To UBound(filter))
        For i = 1 To UBound(c)
            c(i) = filter(i)
        Next
    End If
    
    'Is the size of the filter odd?
    width = Int(UBound(c) / 2)
    If width = UBound(c) / 2 Then Exit Function
    
    vert = False
    size = dataRange.Columns.Count
    If dataRange.Rows.Count > dataRange.Columns.Count Then
        vert = True
        size = dataRange.Rows.Count
    End If
    
    With Application.Caller
        OutputRows = .Rows.Count
        OutputCols = .Columns.Count
    End With
    ReDim output(1 To OutputRows, 1 To OutputCols)
    
    'Check output is the same size as input
    If dataRange.Rows.Count <> OutputCols And dataRange.Columns.Count <> OutputCols Then
        Exit Function
    End If
    
    'Populate output with zeroes
    For i = 1 To OutputRows
        For j = 1 To OutputCols
            output(i, j) = 0
        Next
    Next
    
    'Filter data
    For i = width + 1 To size - width
        b = 0
        'For j = i - width To i + width
        For j = 1 To 2 * width + 1
            If vert = True Then
                b = b + dataRange(i + j - width - 1, 1) * c(j)
            Else
                b = b + dataRange(1, i + j - width - 1) * c(j)
            End If
        Next
        If vert = True Then
            output(i, 1) = b
        Else
            output(1, i) = b
        End If
    Next
    
    SeasonFilter = output
    
End Function
---------------------------------------------------------------------------------

As you can see most of the macro is concerned with setting up the parameters. Only the part towards the end applies the filter. Creating array functions in VBA is covered in detail here.

An Example

For this example I use the clothing retail sales for the UK supplied by those lovely people at the ONS. The February 2013 data can be downloaded from here. The data covers the period from Jan 1988 to Feb 2013. Enough data to show what a simple filter can do.

In fact, the filter I'm using is a symmetric 13 term filter with all equal weights of 1/13. The weights add to one so that the average of the new series equals the average of the unadjusted data.

I'm assuming that you know how to use array functions. If you don't, read this article.

Clothing retail sales index for the UK - ONS data

As you can see the data has been smoothed with some cycles removed. This is just a simple example and more filters could be applied to remove different frequencies/cycles.

However, how do you know which cycles are being removed? The answer will be revealed next time when I provide an Excel macro to calculate the gain function.

Until next time!