Moving average every n-th column

[vadius]

Hi there

I have a sheet with the following columns
Column a = date
Column b = price
Column c = number of shares
Column d = Factor

1/ Calculate for each day column b * column c * column d and put the result in column e, and thus for each day
2/ Get the 252-day moving average average of column e (as each day, a new row of data appears).
3/ The set up I have is the same for 20 stocks. So I would like to have the 252-day moving average for each stock, so for every 4th column (column e, then column I correspondong to the product column f * column g * column h) etc etc...

Below is the code I have started to build, but I am stuck on several points. The forst one is to calculate the daily product (column b * column c * column d) and the second one is to calculate the 252-day moving average. In clear I am lost and my below code is a really messy one.

I hope you can help me on this.

thanks

Sub moyennemobile()
Dim j, n, a As Integer
Dim table As Double
Dim plage As Variant
With Worksheets("SMI_Ranking")
Set plage = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
'count the number of cells of the first column to have the number of rows
n = WorksheetFunction.Count(plage)
For j = 1 To 81 Step 4
     For i = 2 To n
     'try to calculate the product column b * column b * column c for each row
    
    Range(Cells(i, j), Cells(i, j)).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-2]*RC[-1]"
    Range(Cells(i, j), Cells(i, j)).Select
    Selection.AutoFill Destination:=Range(Cells(i, j), Cells(i, n))
     
     Next i
     
     Next j
     
For j = 1 To 81 Step 4
Set plage = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
'count the number of cells of the first column to have the number of rows
n = WorksheetFunction.Count(plage)
ReDim table(n, 1)
table(n, j) = WorksheetFunction.Average(Cells(n - 252, j), Cells(n, j))
'for each column (every 4th column) get the 252-day moving average
Add Worksheets.Name = rankings
Worksheets("Rankings").Activate
a = 1
Cells(1, a).Value = tableau(n, j)
a = a + 1
'put the result in the "ranking" sheet
Next j
End Sub

 

[vadius]

No one can help ?

I have something like this

Column A .....Column B .....Column C ......Column D ......Column E
29jul11 ......51.5 .........45,000,000 .....0.95 = 51.5*45,000,000*0.95
30jul11 ........49.8 ............45,000,000 .....0.95 ..= 49.8*45,000,000*0.95
..... .......
etc
etc

..................................................................252-day Moving average (of column E)

I want the macro to calculate figures in column E and then the 252-day moving average.

Thanks<!-- / message -->