Visual Newby
06-08-2012, 09:28 AM
Hi, I'm trying to use VBA code to obtain the standard deviation of a range that I have defined.
Basically, I have a table that has various columns, 1 of which needs to include the standard deviation of a series of stock returns.
I have defined the range of stock returns and then basically want to incorporate this into the stdev function within VBA code. The range is changing on a monthly basis so I need to try and reference this when calculating the standard deviation to ensure the process is fully automated and can be done every month at the click of a button.
I then also need to annualise this figure by scaling up by the sqrt of 12.
I've tried a few ways of doing this but nothing has worked so far. Is any one able to give me an example of using the stdev function on a defined range within vba code or provide some insight on how to do so?
My method could be completely wrong so if this can be done in a different way please let me know.
Thanks in advance!
Basically, I have a table that has various columns, 1 of which needs to include the standard deviation of a series of stock returns.
I have defined the range of stock returns and then basically want to incorporate this into the stdev function within VBA code. The range is changing on a monthly basis so I need to try and reference this when calculating the standard deviation to ensure the process is fully automated and can be done every month at the click of a button.
I then also need to annualise this figure by scaling up by the sqrt of 12.
I've tried a few ways of doing this but nothing has worked so far. Is any one able to give me an example of using the stdev function on a defined range within vba code or provide some insight on how to do so?
My method could be completely wrong so if this can be done in a different way please let me know.
Thanks in advance!