[h=2]VBA Moving Average Calculation[/h][INDENT]Good evening,
I hope I am doing this correctly - I've provided a bit of background information into my problem beforehand and the coding has been listed below.
I have been given one hundred stock prices, and I am required to use VBA to create a ten-day Simple Moving Average for this series of stock prices. The observations begin from time = 0 to t = 100, and observations begin from t = 9 [the program naturally requires ten stock prices to create the average]. For each new day, the oldest stock price drops off and the next most recent stock price is used - hence, the calculation alters daily.
What I have done is named the ranges of cells ... StockPrice refers to the original Data, and MovingAverage refers to where the data outputs to. I have then created some formulas to calculate the average and display it in the 'Moving Average' range of cells. Please see attached code for further reference:
VB:
Sub MovingAverage() Dim StockPrice() As Double Dim SumArray() As Double Dim MovingAverage() As Double numRow = Range("StockPrice").Rows.Count numRowb = Range("MovingAverage").Rows.Count Redim StockPrice(numRow) As Double Redim SumArray(numRowb) As Double For y = 0 To numRowb - 1 For x = 0 To 9 SumArray(y) = SumArray(y) + StockPrice(x + y) Next x Next y For i = 1 To numRowb Range("MovingAverage").Cells(i) = SumArray(i - 1) / 10 Next i End SubI have a problem when I run my code. There are no errors, however my output in the result 'MovingAverage' range only displays 0 for the entire range, as opposed to showing the correct average stock prices ... I believe I may have missed a step between 'Next y' and 'For i = 1 To numRowb', towards the end of the coding, but I am not 100% sure as my VBA knowledge is minimal.
If someone could provide me with some assistance as to why my code is only returning 0 for each response, I would be most appreciative!
Kind regards,
Chris[/INDENT]