excel 用重标极差法求Hurst指数，总是显示溢出-经管之家官网！

扫码加入统计交流群

Sub Hurst()

'变量和数组的定义

Dim Data()

Dim Array1()

Dim Array2()

Dim R()

Dim S()

Dim Result()

DimNoOfDataPoints As Integer

DimNoOfPlottedPoints As Integer

Dim NoOfPeriods

Dim PeriodNo

Dim n As Integer

Dim A As Integer

Dim i As Integer

Dim m

Dim e

Dim RS

'验证A 列中是否输入数据

IfWorksheets("Sheet1").Range("A1").Value = 0 Then MsgBox("请在A 列输入数据!"): Exit Sub

'清空主要的单元格

Worksheets("Sheet1").Range("B3").Value= "Hurst = "

Worksheets("Sheet1").Range("C3").Value= Null

'统计数据的个数

i = 1

Do While i <10000

i = i + 1

If Worksheets("Sheet1").Cells(i,1).Value = 0 Then Exit Do

Loop

NoOfDataPoints =i - 1

ReDimData(NoOfDataPoints)

'验证A 列的数据后将其加载到数组中

i = 1

counter = 1

Do While counter<= NoOfDataPoints

Set curCell =Worksheets("Sheet1").Cells(i, 1)

If Application.WorksheetFunction.IsNumber(curCell.Value)Then

Data(counter) =curCell.Value

counter = counter+ 1

End If

i = i + 1

Loop

'运行以下代码则可以直接输入原数据

'i=2

'Do While i <=NoOfDataPoints

'Data(i - 1) =Log(Data(i) / Data(i - 1))

'i = i + 1

'Loop

ReDim Result(NoOfDataPoints/ 2, 2)

'进入主循环

A = 2

Do While A <=NoOfDataPoints / 2

'再次定义数组变量

NoOfPeriods =NoOfDataPoints / A

ReDimArray1(Int(NoOfPeriods))

ReDim Array2(A,NoOfPeriods)

ReDimS(Int(NoOfPeriods))

ReDimR(Int(NoOfPeriods))

RS = 0

'求得各个子区间均值

i = 1

Do While i <=NoOfPeriods

e = 0

For PeriodNo = 1To A

e = e +Data(PeriodNo + (i - 1) * A)

Next PeriodNo

Array1(i) = e / A

i = i + 1

Loop

'求得各个子区间的累积截距和极差

i = 1

Do While i <NoOfPeriods

m = 0

e = 0

For PeriodNo = 1To A

m = m +((Data(PeriodNo + (i - 1) * A) - Array1(i)) ^ 2)

e = e +(Data(PeriodNo + (i - 1) * A) - Array1(i))

Array2(PeriodNo,i) = e

Next PeriodNo

'比较最大值与最小值

Maxi = Array2(1,i)

Mini = Array2(1,i)

For n = 1 To A

If Array2(n, i)> Maxi Then Maxi = Array2(n, i)

If Array2(n, i)< Mini Then Mini = Array2(n, i)

Next n

'求得R/S 值

R(i) = Maxi -Mini

S(i) = Sqr(m / A)

RS = RS + R(i) /S(i)

i = i + 1

Loop

'将V 统计量表的数据输出到Excel表格中

Worksheets("sheet1").Cells(A+ 2, 5).Value = (RS / NoOfPeriods) / Sqr(A)

Worksheets("sheet1").Cells(A+ 2, 6).Value = Log(A)

'将计算结果装入Result（）数组中

Result(A, 1) =Log(A)

Result(A, 2) =Log(RS / NoOfPeriods)

A = A + 1

Loop

'对方程Log(R/S)=Log(c)+ H·Log(n)+ε进行线性回归，估计出斜率H 就是Hurst 指数

sumx = 0

Sumy = 0

Sumxy = 0

Sumxx = 0

NoOfPlottedPoints= NoOfDataPoints / 2

For i = 2 ToNoOfPlottedPoints

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

H = (Sumxy -((sumx * Sumy) / NoOfPlottedPoints)) / (Sumxx - ((sumx * sumx) /NoOfPlottedPoints))

Worksheets("sheet1").Range("C3").Value= H

End Sub

这个程序是一篇论文的附录，应该没问题

我实验了，用excel的VBA运行后发现用整数得出的结果应该是正确的，而用我的数据（有很多位小数）就无法运行，总是提示溢出，求解，很急！！！

「经管之家」APP：经管人学习、答疑、交友，就上经管之家！
免流量费下载资料----在经管之家app可以下载论坛上的所有资源，并且不额外收取下载高峰期的论坛币。
涵盖所有经管领域的优秀内容----覆盖经济、管理、金融投资、计量统计、数据分析、国贸、财会等专业的学习宝库，各类资料应有尽有。
来自五湖四海的经管达人----已经有上千万的经管人来到这里，你可以找到任何学科方向、有共同话题的朋友。
经管之家（原人大经济论坛），跨越高校的围墙，带你走进经管知识的新世界。
扫描下方二维码下载并注册APP