# Moving Average in VBA

#### thefarelkid

##### New Member
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?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...