C
C Brandt
My task is to create a moving average for stocks.
I have downloaded a table of daily prices.
Requested the period for the moving average in weeks
now I would like to create the formula to populate the adjacent column to
calculate the moving average:
10 SA = inputbox("Input Number of weeks to use in Moving average")
' weeks in average calculation
20 MA=SA*-5 ' Provides negative offset to the first
row of the moving average
30 Cells(SA*5+1,10).select ' SA times 5 days plus one row for
header = first row that qualifies for moving average.
40 ActiveCell.FormulaR1C1 = "=AVERAGE(R[MA]C[-3]:R[-1]C[-3])"
50 Selection.Copy
60 Range(Cells(FA * 5 + 1, 10), Cells(DataEnd, 10)).Select ' DataEnd
is the last row of valid data
70 ActiveSheet.Paste
When I try to create the formula, line 40, it gives me a runtime error of
1004 (Application-defined or object-defined error).
Is there a way to create the formula in the manner that I am attempting?
Getting older by the minute,
Craig
I have downloaded a table of daily prices.
Requested the period for the moving average in weeks
now I would like to create the formula to populate the adjacent column to
calculate the moving average:
10 SA = inputbox("Input Number of weeks to use in Moving average")
' weeks in average calculation
20 MA=SA*-5 ' Provides negative offset to the first
row of the moving average
30 Cells(SA*5+1,10).select ' SA times 5 days plus one row for
header = first row that qualifies for moving average.
40 ActiveCell.FormulaR1C1 = "=AVERAGE(R[MA]C[-3]:R[-1]C[-3])"
50 Selection.Copy
60 Range(Cells(FA * 5 + 1, 10), Cells(DataEnd, 10)).Select ' DataEnd
is the last row of valid data
70 ActiveSheet.Paste
When I try to create the formula, line 40, it gives me a runtime error of
1004 (Application-defined or object-defined error).
Is there a way to create the formula in the manner that I am attempting?
Getting older by the minute,
Craig