CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Calculating an exponentially weighted moving average

I am trying a fairly simple function to calculate an exponentially weighted moving average volatility in Excel VBA, following. However, I think there is some error in my function that I can't pinpoint, because I don't get the correct solution.

 Function EWMA(numbers As Range, Lambda As Single) As Double

Dim mean As Double

Dim x As Double

Dim c As Range

Dim n As Integer

mean = WorksheetFunction.Average(numbers)

n = WorksheetFunction.Count(numbers)

For Each c In numbers

    x = x + (Lambda ^ (n - c.Count)) * ((c.Value - mean) ^ 2)

Next c

EWMA = (1 - Lambda) * x

End Function

The values I am using and the target volatility (calculated using a spreadsheet EWMA) are here.

What am I doing wrong?

Update: using Ron Rosenfeld's solution: 

Option Explicit

Function EWMA(Zero As Range, Lambda As Double) As Double

    Dim vZero As Variant
    Dim SumWtdRtn As Double
    Dim I As Long
    Dim vPrices As Variant

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double

    vZero = Zero

For I = 2 To UBound(vZero, 1)

    vPrices = 1 / ((1 + vZero(I, 1)) ^ (3 / 12))
    LogRtn = Log(vPrices(I - 1, 1) / vPrices(I, 1))
    RtnSQ = LogRtn ^ 2
    WT = (1 - Lambda) * Lambda ^ (I - 2)
    WtdRtn = WT * RtnSQ
    SumWtdRtn = SumWtdRtn + WtdRtn

Next I

EWMA = SumWtdRtn ^ (1 / 2)

End Function

Upvotes: 1

Views: 6237

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60354

Here's a little bit different way of doing it as a VBA function. The inputs are an array of prices, which is assumed to be in descending order as you show, and Lambda. Hopefully, the names of the variables will let you see the logic:

Option Explicit
Function EWMA2(Prices As Range, Lambda As Double) As Double
    Dim vPrices As Variant
    Dim dSumWtdRtn As Double
    Dim I As Long

    Dim dLogRtn As Double, dRtnSQ As Double, dWT As Double, dWtdRtn As Double

vPrices = Prices
For I = 2 To UBound(vPrices, 1)
    dLogRtn = Log(vPrices(I - 1, 1) / vPrices(I, 1))
    dRtnSQ = dLogRtn ^ 2
    dWT = (1 - Lambda) * Lambda ^ (I - 2)
    dWtdRtn = dWT * dRtnSQ
    dSumWtdRtn = dSumWtdRtn + dWtdRtn
Next I

EWMA2 = dSumWtdRtn ^ (1 / 2)

End Function

With your data, it gives the same results as the spreadsheet calculation (within the limits of precision of the data types)

EDIT

If you want to input the 3M CAD Zero Rates as the range input, and not the pricing, then you could modify the above to compute the two relevant prices from the returns data. In this case, it would be:

Option Explicit
Function EWMAV(Zeros As Range, Lambda As Double) As Double
    Dim vZeros() As Variant
    Dim dPrice1 As Double, dPrice2 As Double
    Dim dSumWtdRtn As Double
    Dim I As Long

    Dim dLogRtn As Double, dRtnSQ As Double, dWT As Double, dWtdRtn As Double

vZeros = Zeros
For I = 2 To UBound(vZeros, 1)
    dPrice1 = 1 / ((1 + vZeros(I - 1, 1)) ^ (3 / 12))
    dPrice2 = 1 / ((1 + vZeros(I, 1)) ^ (3 / 12))

    dLogRtn = Log(dPrice1 / dPrice2)
    dRtnSQ = dLogRtn ^ 2
    dWT = (1 - Lambda) * Lambda ^ (I - 2)
    dWtdRtn = dWT * dRtnSQ
    dSumWtdRtn = dSumWtdRtn + dWtdRtn
Next I

EWMAV = dSumWtdRtn ^ (1 / 2)

End Function

Upvotes: 1

Related Questions