What have I gained
Last time I wrote about how to apply a filter to some time series data. The example given at the end of the post showed the effect of applying a filter to some clothing data from the ONS.
However, what did the filter actually do?
As mentioned before, the filter removes some frequencies from the series. The frequencies that have been removed are revealed by looking at the gain function. Although it should be pointed out that we're interested in the periodicities rather than frequencies.
However, what did the filter actually do?
As mentioned before, the filter removes some frequencies from the series. The frequencies that have been removed are revealed by looking at the gain function. Although it should be pointed out that we're interested in the periodicities rather than frequencies.
The macro
It's another array function - this cuts down on processing and makes the calculation within Excel quicker and more efficient.
Without further ado:
--------------------------------------------------------------------------
Function GainFunction(filter As Variant, Optional b 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 c() As Double
Dim d() As Double
Dim size As Integer
Dim g1() As Double
Dim g2() As Double
'Load a into array c
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
With Application.Caller
OutputRows = .Rows.Count
OutputCols = .Columns.Count
End With
ReDim output(1 To OutputRows, 1 To OutputCols)
'Populate output with zeroes
For i = 1 To OutputRows
For j = 1 To OutputCols
output(i, j) = 0
Next
Next
If IsMissing(b) Then
If OutputRows > OutputCols Then
vert = True
size = OutputRows
ReDim g1(1 To OutputRows)
ReDim g2(1 To OutputRows)
ReDim d(1 To OutputRows)
Else
vert = False
size = OutputCols
ReDim g1(1 To OutputCols)
ReDim g2(1 To OutputCols)
ReDim d(1 To OutputCols)
End If
For i = 1 To size
d(i) = i
Next
Else
'Load b into array d
If TypeName(b) = "Range" Then
If b.Rows.Count > b.Columns.Count Then
vert = True
size = b.Rows.Count
If size > OutputRows Then size = OutputRows
If size < OutputRows Then Exit Function
ReDim g1(1 To size)
ReDim g2(1 To size)
ReDim d(1 To size)
For i = 1 To size
d(i) = b(i, 1)
Next
Else
vert = False
size = b.Columns.Count
If size > OutputCols Then size = OutputCols
If size < OutputCols Then Exit Function
ReDim g1(1 To size)
ReDim g2(1 To size)
ReDim d(1 To size)
For i = 1 To UBound(d)
d(i) = b(1, i)
Next
End If
Else
size = UBound(b)
If OutputRows > OutputCols Then
vert = True
If size > OutputRows Then size = OutputRows
If size < OutputRows Then Exit Function
Else
vert = False
If size > OutputCols Then size = OutputCols
If size < OutputCols Then Exit Function
End If
ReDim g1(1 To size)
ReDim g2(1 To size)
ReDim d(1 To size)
For i = 1 To size
d(i) = b(i)
Next
End If
End If
'Create gain function
For i = 1 To size
g1(i) = 0
g2(i) = 0
For j = 1 To UBound(c)
g1(i) = g1(i) + c(j) * Cos(j * 2 * [pi()] / d(i))
g2(i) = g2(i) + c(j) * Sin(j * 2 * [pi()] / d(i))
Next
If vert = True Then
output(i, 1) = Sqr(g1(i) ^ 2 + g2(i) ^ 2)
Else
output(1, i) = Sqr(g1(i) ^ 2 + g2(i) ^ 2)
End If
Next
GainFunction = output
End Function
--------------------------------------------------------------------------
As ever, most of the macro is concerned with setting up the parameters and dealing with the fact that the input data could be horizontal or vertical. Only the last 10 or so lines actually calculate the gain function.
The optional variable/parameter b is the list of periodicities to calculate the function for. You don't have to include it, but I strongly recommend that you do. In this way you can control what you're checking.
I also recommend that you don't bother looking at periodicities less than 1. Below 1 the frequencies tend toward infinity and the gain function becomes very spiky (that's the technical term).
An Example
Using the 13 point symmetric filter (1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13, 1/13) gives the following gain function:
As you can see from the chart, zeroes in the gain function occur at 13, 6.5 and then half the preceding periodicity from then on. These periodicities are removed from the data. And everything from just over 1 to around 14 are, mostly, removed. So most of the short term, high frequency data is smoothed out.
The filter shown here has the same number for each of the 13 terms. It doesn't have to, although it does make it easier to predict what will happen to the data. Essentially the data point is just the average of the surrounding 12 points - 6 from either side.
There is another aspect to filters which I will cover in the next blog. And that is the change to the phasing of the data.
Until next time.
No comments:
Post a Comment