VBA Moving Average Calculation

  • 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

  • Re: VBA Moving Average Calculation


    It would be a little easier with a workbook but simply you have declared the array Stockprice , you have counted how many values in the stockprice named range but there is no data in the array stockprice. If you go into the VBA editor and use F8 to step through your code you will see that the array StockPrice is empty - you need to fill the array with values because as it stands there are no values in the array.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: VBA Moving Average Calculation


    why need of a macro


    see attached sample file "TSKHAD.xls". see the formula in B11 which is copied down.



    easiest way of copying is highlight B11 . take cursor to right bottom of t;hat cell. cursor turns to plus sign (+). click that the cells B11 is copied till the data is there in column A

  • 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

  • Re: VBA Moving Average Calculation


    As this is an assignment I will help you but I will not write it for you. The first thing you need to do is put some values into an array. Once you have the values in the array you will then need to use some logic to calculate the moving average.

    Code
    Dim stockPrice As Variant
    Dim lastStockprice As Long
    lastStockprice = Cells(Rows.Count, "B").End(xlUp).Row
    Dim stockValue As Range
    Set stockValue = Range("B5:B" & lastStockprice)
    stockPrice = stockValue


    This code will find the last row in column B so the end of the range of values you need to work with. I have declared a range called stockValue and declared an array called StockPrice. So with the code above the values in column B are now in an array - the first value of the array is StockPrice(1,1), the second value of the array is StockPrice(2,1). I find it quicker to do it this way but unfortunately it creates a 2 dimensional array (when you only need a one dimensional array). You could make it a 1 dimensional array by


    In this case StockPrice(0) is the first member of the array and so on - making an array with last value stockPrice(100)
    That will at least get the values into the array - You need to think about the logic required now to calculate a moving average. As I said I will help but will not write it for you.


    PS: 2 things should also be in your code. Option Explicit above the sub name - you will need to declare all your variables. Secondly you should have an error handler to deal with potential errors in the code/workbook
    Good luck. I am logging off for the night but I will check your progress tomorrow. If any of the gurus want to help you or critique my code - its out there!

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • 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


    I also have the same problem with entering the values into the array.


    Would anyone give me a hint?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!