VBA - struggling to calc and write StDev data into an array with a For Next loop

[MJS]

I have made several attempts, this is the latest iteration. It shouldn't produce meaningful output until the minimum periods have been looped thru (volstperiod = 10).

--PctChg1() is an array which holds percent change data from i=2 to i = 2541... declared as variant
--volstperiod = 10 ...declared as integer
--i is a counter ...declared as integer
--VolST1() is an empty array which I hope to populate with annualized volatilities for a rolling x day period ...declared as variant
--Option Base 1 and Option Explicit are on

For i = 2 To 2541
If IsNumeric(i) And i <> 0 Then
VolST1(i, 1) = Application.WorksheetFunction.stdev(Range(PctChg1(i, 1).Offset(-volstperiod, 0), PctChg1(i, 0)))
Else
VolST1(i, 1) = 0
End If
Next i

Any guidance would be immensely appreciated. Thanks!

[Kenneth Hobs]

Please paste code between code tags. Click the # icon to insert the tags.

Can you post and example workbook or fill the PctChng1() with 20 filled elements or so so we can test it for you.

Posting an example with input and expected output best helps us help you.

[MJS]

Hi Kenneth- Thanks for your reply. Just to keep it simple, this is how I would express it in a worksheet formula...

=IF(ISNUMBER(OFFSET($E3,-volstperiod+1,0)),STDEV(OFFSET($E3,0,0,-volstperiod)),0)

...with "volstperiod" = 10 and column E holding 1 period %chg data (ie =$E3/$E2-1).

Col E	Output
0.1316	0.0000
0.0115	0.0000
0.0494	0.0000
(0.0069)	0.0000
0.0011	0.0000
0.0099	0.0000
(0.0111)	0.0000
0.0030	0.0000
0.0088	0.0000
0.0486	0.0429
0.0376	0.0221
0.0040	0.0224
0.0009	0.0189
0.0200	0.0182
0.0338	0.0189
(0.0155)	0.0213
0.0050	0.0199

[MJS]

Still trying to figure this out - might it be easier by creating a smaller array which could roll through the larger array? ...then any necessary calcs could be done on the entirety of the small array.

I cannot figure out how to isolate just a (rolling) subset of an array. The rolling subset could be used for moving averages, standard devs, max/min, etc.

[Kenneth Hobs]

With all the effort you did making post 3, why not just post the workbook as I requested in post 2? Remember, when you lookup the definition of lazy you will find my name...

Are you looking for a formula solution or a VBA solution? There are ways to do a moving average using built-in formula functions.

[MJS]

I am looking for a VBA solution, but I have had a hard time isolating just a small subset of data from an array. For example, if i have 100 data points in a single dimension array ("price_array"), I cannot figure out how to get the max, min or average for just 10 at a time. this doesn't seem to work: application.max(range(price_array(1),price_array(10))) - I would want the max of all the values in between those 2 coordinates, not just the larger of those 2.

I wasn't aware that i could attach a workbook. I will try to trim it down and post it.

Thanks again for following up Kenneth.

[Kenneth Hobs]

Click the Go Advanced button on bottom right during a reply and click the paperclip icon to browse and attach a file. Something simple with just say 20 or so to show the pattern should be enough.

We could probably do it by standard formulas but doing it just by VBA which can access those formulas as well if needed, will give you a good solution as well.

[MJS]

Hi Kenneth- workbook attached; I have passed the data in the attached workbook into an array and am trying to work with it there before passing results back to the worksheet. Currently, I have a 3 dimension array - 1 = number of periods, 2 = number of stocks and 3 = stock data (price, for example). So array(1,1,1) is (period=1,stock=1,price).

So I guess I am trying to figure out a piece of code that returns the rolling max(i-10:i) for a dynamic array. I want to loop through this array of daily stock prices and each day generate the max/min/stdev/etc for the most recent x days - and feed it into the 3rd dimension.

Appreciate your help, sorry if I am making this more confusing than it needs to be.

[Kenneth Hobs]

Not sure that I understand the period deal. Maybe if you wrote what the array should have would clear it up.

e.g. Roughly shown for 2 periods and 2 stocks where period 1 is first 10 prices averaged and period 2 is prices 2-11 averaged:
1,CSCO,24.57
1,MSFT,47.23
2,CSCO,24.71
2,MSFT,47.53

I attached a workbook where I used formulas and did the average and max values in a running last 10 scenario. Other stats like Stddev.P() or StdDev.S() and Min() can be done in a similar way. Those can be on another sheet if needed as well.

[MJS]

Hi Ken - I have attached my workbook with a partial macro. I am attempting to send data for 5 stocks into the array and then do the calculations there (and keep in dimension 3).

I am very familiar with worksheet functionality but am struggling to translate it to VBA. My goal is to do this for a much larger chunk of data which will include enough calculations to grind a worksheet to a halt.