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

msaldut

New Member
Joined
Nov 17, 2014
Messages
8
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Just to simplify, 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).
 
Upvote 0
might this be easier to do 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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top