Posts by Tskhad

Re: VBA Moving Average Calculation

Hi smuzoen,

Thank you for your comprehensive reply ... it's really appreciated! Having now defined everything correctly, I used the following code in addition to what you had provided for me, and it gave me the moving average that I required:

numRowb = Range("MovingAverage").Rows.Count
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

I was toying with the code that I had provided to you earlier, and was changing a few things here and there. Somehow, I accidentally stumbled on coding that would calculate the response for me. I will definitely rest easy tonight now!

I really can't thank you enough for your help ... I'm terrible with VBA and I'm so relieved to have solved this part of the assignment! Thanks again.

Re: VBA Moving Average Calculation

Thank you to you both for your response.

venkat1926, unfortunately I have to use VBA, as it is part of an assignment. I certainly wish that it was as easy as plugging in formulas to Excel

smuzoen, with regards to declaring the array Stockprice, should I be entering in the actual 'size' of the array? I'm afraid my VBA knowledge is lousy, so I'm sorry if I have misunderstood what you've said. I have attached the Spreadsheet with this e-mail ... hopefully it will help clarify my query.

Thanks once again,
T

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