在百度找到的程序,关于hurst指数计算。但是数据超过500后,excel卡死,请教高手调试,能计算数据达到2万,谢谢了
Option Base 1
'This program calculates an estimate of the Hurst coefficient.
'Written by Martin Sewell <martin@martinsewell.com>
Sub Hurst()
Dim Data()
Dim Array1()
Dim Array2()
Dim Mean
Dim Result()
Dim NoOfDataPoints As Integer
Dim NoOfPlottedPoints As Integer
Dim PlottedPointNo As Integer
Dim NoOfPeriods As Integer
Dim PeriodNo As Integer
Dim n As Integer
Dim i As Integer
Dim m As Integer
Dim logten
Dim R
Dim S
Dim RS
Dim SumSquared
logten = Log(10)
'Delete any previous results
Worksheets("Data").Range("C3").Value = Null
Worksheets("Data").Range("D:D").Value = Null
Worksheets("Data").Range("E:E").Value = Null
'Get total number of data points
NoOfDataPoints = Worksheets("Data").Range("C1").Value
ReDim Data(NoOfDataPoints)
'Get data, ignoring any spaces
i = 1
counter = 1
Do While counter <= NoOfDataPoints
Set curCell = Worksheets("Data").Cells(i, 1)
If Application.WorksheetFunction.IsNumber(curCell.Value) Then
Data(counter) = curCell.Value
counter = counter + 1
End If
i = i + 1
Loop
NoOfPlottedPoints = NoOfDataPoints - 2
ReDim Result(NoOfPlottedPoints, 2)
'Begin main loop
For n = 50 To NoOfDataPoints
totalR = 0
totalS = 0
NoOfPeriods = NoOfDataPoints - n + 1
For PeriodNo = 1 To NoOfPeriods
ReDim Array1(n)
ReDim Array2(n)
For i = 1 To n
Array1(i) = Data((PeriodNo - 1) + i)
Array2(i) = 0
Next i
Summ = 0
SumSquared = 0
For i = 1 To n
Summ = Summ + Array1(i)
SumSquared = SumSquared + ((Array1(i)) * (Array1(i)))
Next i
Mean = Summ / n
'STDEV
'S = Sqr((SumSquared - (Summ * Summ) / N) / (N - 1))
'STDEVP
S = Sqr((SumSquared - (Summ * Summ) / n) / n)
For i = 1 To n
Array1(i) = Array1(i) - Mean
Next i
For i = 1 To n
For j = 1 To i
Array2(i) = Array2(i) + Array1(j)
Next j
Next i
Maxi = Array2(1)
Mini = Array2(1)
For i = 1 To n
If Array2(i) > Maxi Then Maxi = Array2(i)
If Array2(i) < Mini Then Mini = Array2(i)
Next i
R = Maxi - Mini
totalR = totalR + R
totalS = totalS + S
Next PeriodNo
R = totalR / NoOfPeriods
S = totalS / NoOfPeriods
RS = R / S
PlottedPointNo = n - 2
Result(PlottedPointNo, 1) = (Log(n)) / logten
Result(PlottedPointNo, 2) = (Log(RS)) / logten
Next n
sumx = 0
Sumy = 0
Sumxy = 0
Sumxx = 0
For i = 1 To NoOfPlottedPoints
Worksheets("Data").Cells(i + 6, 4).Value = Result(i, 1)
Worksheets("Data").Cells(i + 6, 5).Value = Result(i, 2)
sumx = sumx + Result(i, 1)
Sumy = Sumy + Result(i, 2)
Sumxy = Sumxy + (Result(i, 1)) * (Result(i, 2))
Sumxx = Sumxx + (Result(i, 1)) * (Result(i, 1))
Next i
'Calculate Hurst coefficient
H = (Sumxy - ((sumx * Sumy) / NoOfPlottedPoints)) / (Sumxx - ((sumx * sumx) / NoOfPlottedPoints))
Worksheets("Data").Range("C3").Value = H
End Sub