Posts by ckim0922

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