# VBA Moving Average Calculation

• [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:

Code
``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 Sub``

I 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]

• Re: VBA Moving Average Calculation

Your code will then look like this.

Code
``````Sub Test()
End Sub``````

This is called using code tags and is how you should post all code to this board.

Bruce :cool:

• Re: VBA Moving Average Calculation

Hi Chris,

The issue is that you have no code to populate your arrays, you only dimension them.

This gives your arrays their final dimensions.

Code
``````ReDim StockPrice(numRow) As Double
ReDim SumArray(numRowb) As Double``````

This code which is next is just going through the motions with empty arrays.

Code
``````For y = 0 To numRowb - 1
For x = 0 To 9
SumArray(y) = SumArray(y) + StockPrice(x + y)
Next x
Next y``````

You need to loop through the cells where the data is and add the data to your arrays.

Bruce :cool:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!