VBA Moving Average Calculation

ckim0922 · Apr 22nd 2016

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

skywriter · Apr 22nd 2016

Re: VBA Moving Average Calculation

Please choose to edit your post, highlight all of your code, choose the # icon on the toolbar and re-save your post.

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.

skywriter · Apr 23rd 2016

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.

VBA Moving Average Calculation

Participate now!

Share