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:


    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,

  • 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.

    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.

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

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

    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!