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:



    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:


    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