Solved: Calculating stock price volatility over and over and over...

[dustarook]

Ok, so i'm writing a sub in excel that calculates price volatility incrementally through a series of data. an earlier, nearly identical program runs just fine. I understand this error {Invalid procedure call or argument (Error 5)} indicates that the number is out of range but i'm just not sure how to correct it. attached is a picture of what the spreadsheet looks like, along with some identifying notes. the code is as follows:
Sub Calc_FieldVolatility()
Dim HorCount As Integer, HorMax As Integer, VerCount As Integer, VerMax As Integer, PerLength As Integer, rgOffset1 As Integer, rgOffset2 As Integer

Dim ChangeSquareSum As Double, PercentSum As Double


'The Range ("Hor_Max") is actually indicative of the vertical max for each column,oops...

'VolPerCount = Range("Volatility_PerCount")
Range("FieldClearer").ClearContents
ChangeSquareSum = 0
PercentSum = 0
HorCount = 0


Do While HorCount < 26

HorCount = Range("HorizontalCount")
VerMax = Range("Day_10").Offset(1, HorCount)

Do While VerCount < VerMax

VerCount = Range("VolFieldStart").Offset(-1, HorCount)
PerLength = Range("Day_10").Offset(0, HorCount)
rgOffset1 = VerCount * PerLength
rgOffset2 = VerCount * PerLength + PerLength
ChangeSquareSum = 0
PercentSum = 0

For rgOffset1 = rgOffset1 To rgOffset2
'This loop sums each part of an array divided into sections equal to the adjusted period length
ChangeSquareSum = ChangeSquareSum + Range("Changed_Sqr").Offset(rgOffset1, 0).Value
PercentSum = PercentSum + Range("Percent_Change").Offset(rgOffset1, 0).Value

Next rgOffset1

ChangeSquareSum = (Sqr((ChangeSquareSum - ((PercentSum) ^ 2) / PerLength) / (PerLength - 1))) * Sqr(252 / PerLength)

Range("VolFieldStart").Offset(VerCount, HorCount).Value = ChangeSquareSum

Loop
Loop


End Sub
PLEASE HELP?!?!

[dustarook]

P.S. the error occurs on this line of code:
ChangeSquareSum = (Sqr((ChangeSquareSum - ((PercentSum) ^ 2) / PerLength) / (PerLength - 1))) * Sqr(252 / PerLength)

[Bob Phillips]

Presumably, the number goes negative and so Sqr fails.

Can you post a workbook?

[dustarook]

The code in question is the second sub in module 5, though i would assume the same correction would be required for the first sub in that module, as the period length decreases as today's date approaches the closing date of the options contracts. The shorter periods seem to be what causes the problem...

[Bob Phillips]

Can you give a test case that I can follow to force the issue, it is not that simple to identfy from the spreadsheet.

[dustarook]

***message deleted***

[dustarook]

...so the error occurs at different places in the spreadsheet for each different data set, but in the same place when run multiple times on the same data set, and always on that same line of code containing the actual volatility calculation.

[dustarook]

ok so i think this might help, here is a specific example if you can read the text box in the attached picture... the error occurs in this line of code only when it encounters a certain combination of data from the spreadsheet i.e. the cells highlighted in the picture or as coded below:

Sub testcalc()
dim ChangeSquareSum as double, PercentSum as double
dim PerLength as integer
'these values are the acual outputs the program retrieves from the highlighted cells in the picture, which in turn cause the error given the following equation...
ChangeSquareSum = 0.0004029
PercentSum = -0.02321014
PerLength = 3

ChangeSquareSum = (Sqr((ChangeSquareSum - ((PercentSum) ^ 2) / PerLength) / (PerLength - 1))) * Sqr(252 / PerLength)
End Sub
I hope this makes things easy to solve, i'm just learning to program so this is all pretty new to me. Really appreciate the help. Also pay no attention to values in column E, they do not correspond to the cells next to them...