Moving Average in VBA

[thefarelkid]

Hi. I've been looking all over MrExcel.com and the rest of the web, but every post about VBA code for moving averages isn't exactly a solution for what I want to do. I'm working with very large amounts of data. About 10 years of daily stock returns, and doing 10 stocks at a time (that's about 20k data points) and I need to calculate a 10, 50, or 100 day moving average for each stock on each day. I know how to do this in regular formula, but that's been slow, and is also prone to user error since 1 missed stroke can throw off years of moving averages.

After about 3 days of work, I found a solution that works, but is way more slow than before! Here is the code that I'm working with right now.


Sub MovingAverage()

Dim i As Long
Dim LastRow As Long

Range("N3:N5000").Clear

LastRow = Range("B" & Rows.Count).End(xlUp).Row
l = Cells(1, "W").Value

If l = 0 Then
MsgBox "Enter Range in C1"
GoTo Lastline
Else

For i = l To LastRow - 2
Range("N" & i + 2).FormulaR1C1 = "=AVERAGE(RC[-12]:R[-" & l - 1 & "]C[-12])"
Next i

Range("N2:N5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "#.00"
Application.CutCopyMode = False
End If

'second column
Range("o35000").Clear

For i = l To LastRow - 2
Range("o" & i + 2).FormulaR1C1 = "=AVERAGE(RC[-12]:R[-" & l - 1 & "]C[-12])"
Next i

Range("o25000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "#.00"
Application.CutCopyMode = False

'... second column repeats 8 more times going to column W
Lastline:
End Sub

This works out very well, from each line beginning at say 10 data points in (for a 10 day moving average) It fills in the code going back ten days at a time. Then when it reaches the end it copies and pastes the values of the column so that the formula doesn't have to recalculate actively from then on. The problem I have found is that going next down the list for 2000 days is unbearably. I was trying to workout a Application.WorksheetFunction.Average, but I couldn't get the range do be defined as the columns X number of days above each line.

Can someone help?