順序統計量 - 模擬與交易.5

Public RankSort(1 To 8) As Variant
Const FixConst As Variant = 0.398942284 '1/sqr(2*3.1415926)
Public Sub avgRank()
'模擬常態分配隨機變數的順序統計量期望值
Dim seed, seedCalc, nextSeed, NorValue As Variant
Dim ArrNorRandom(1 To 8) As Variant
Dim iith, testNum As Long
For testNum = 1 To 10000
iith = 1
Do
seed = Int(Rnd() * 1000000)
seedCalc = ((seed - 500000) * 0.000001) * 8 '模擬4個標準差範圍的 N(0,1) 常態分配變數
NorValue = Format(FixConst * Exp(-0.5 * seedCalc * seedCalc), "0.000000") '常態分配機率函數的數值
nextSeed = 0
nextSeed = Int(0.001 * (seed * seed))
nextSeed = Format(0.000001 * nextSeed - Int(0.000001 * nextSeed), "0.000000")
nextSeed = Int(1000000 * nextSeed)
nextSeed = Int(((23 + iith * 0.01) * nextSeed + 1011 * iith)) Mod 999999
nextSeed = Format(nextSeed * 0.00001 * FixConst, "0.000000")
'Debug.Print seedCalc, NorValue, nextSeed
If (nextSeed) < NorValue Then
ArrNorRandom(iith) = seedCalc
'Debug.Print iith, ArrNorRandom(iith)
'Sheets("工作表3").Cells(iith, 1) = ArrNorRandom(iith)
iith = iith + 1
End If
Loop While iith <= 8
'泡沫排序法
Dim swapTemp, ii, jj As Variant
For jj = 0 To 6
For ii = 1 To 7 - jj
If ArrNorRandom(ii) > ArrNorRandom(ii + 1) Then
swapTemp = ""
swapTemp = ArrNorRandom(ii + 1)
ArrNorRandom(ii + 1) = ArrNorRandom(ii)
ArrNorRandom(ii) = swapTemp
End If
Next ii
Next jj
'Debug.Print ArrNorRandom(1), ",", ArrNorRandom(2), ",", ArrNorRandom(3), ",", ArrNorRandom(4)
'Debug.Print ArrNorRandom(5), ",", ArrNorRandom(6), ",", ArrNorRandom(7), ",", ArrNorRandom(8)

For ii = 1 To 8
RankSort(ii) = RankSort(ii) + ArrNorRandom(ii)
Next ii
Next testNum

For ii = 1 To 8
RankSort(ii) = RankSort(ii) / testNum
Next ii
'Debug.Print RankSort(1), ",", RankSort(2), ",", RankSort(3), ",", RankSort(4)
'Debug.Print RankSort(5), ",", RankSort(6), ",", RankSort(7), ",", RankSort(8)
End Sub

Public Sub RankLevel()
Dim seed, seedCalc, nextSeed, NorValue As Variant
Dim ArrNorRandom(1 To 8), sqrDiff(1 To 100000) As Variant
Dim iith, testNum As Long
avgRank '事前取得順序統計量的期望值，並宣告為全域變數

For testNum = 1 To 100000 '模擬次數
iith = 1
Do
seed = Int(Rnd() * 1000000)
seedCalc = ((seed - 500000) * 0.000001) * 8
'模擬4個標準差範圍的 N(0,1) 常態分配變數
NorValue = Format(FixConst * Exp(-0.5 * seedCalc * seedCalc), "0.000000") '常態分配機率函數的數值
nextSeed = 0
nextSeed = Int(0.001 * (seed * seed))
nextSeed = Format(0.000001 * nextSeed - Int(0.000001 * nextSeed), "0.000000")
nextSeed = Int(1000000 * nextSeed)
nextSeed = Int(((23 + iith * 0.01) * nextSeed + 1011 * iith)) Mod 999999
nextSeed = Format(nextSeed * 0.00001 * FixConst, "0.000000")

If (nextSeed) < NorValue Then
ArrNorRandom(iith) = seedCalc
iith = iith + 1
End If
Loop While iith <= 8

'泡沫排序法
Dim swapTemp, ii, jj As Variant
For jj = 0 To 6
For ii = 1 To 7 - jj
If ArrNorRandom(ii) > ArrNorRandom(ii + 1) Then
swapTemp = ""
swapTemp = ArrNorRandom(ii + 1)
ArrNorRandom(ii + 1) = ArrNorRandom(ii)
ArrNorRandom(ii) = swapTemp
End If
Next ii
Next jj

'本次產生的隨機亂數排序後與對應的序列期望值做運算
For ii = 1 To 8
xTemp = ArrNorRandom(ii) - RankSort(ii)
sqrDiff(testNum) = sqrDiff(testNum) + xTemp * xTemp
Next ii
Next testNum '本次模擬次數，testNum=100000

'sqrDiff陣列共計10萬筆數據，泡沫排序，求解第95001序位
For jj = 0 To 4999 '資料數據共計10萬筆，求解第95001序位
For ii = 1 To 100000 - 1 - jj
If sqrDiff(ii) > sqrDiff(ii + 1) Then
swapTemp = ""
swapTemp = sqrDiff(ii + 1)
sqrDiff(ii + 1) = sqrDiff(ii)
sqrDiff(ii) = swapTemp
End If
Next ii
Next jj
Debug.Print sqrDiff(95001)
End Sub