対数正規分布

Sub report()
For j = 1 To 1000
　　For i = 1 To 10
　　　　Cells(j, i) = Sqr(-2 * Log(Rnd())) * Cos(2 * Application.Pi() * Rnd())
　　Next i
　　
　　wa = 0
　　For i = 1 To 10
　　　　wa = wa + Cells(j, i)
　　Next i
　　heikin = wa / 10
　　Cells(j, 12) = heikin
　　
　　wa = 0
　　For i = 1 To 10
　　　　wa = wa + (Cells(j, i) - heikin) ^ 2
　　Next i
　　bunsan = wa / 10
　　Cells(j, 13) = bunsan
Next j

wa = 0
For j = 1 To 1000
　　wa = wa + Cells(j, 12)
Next j
Cells(1, 15) = wa / 1000

wa = 0
For j = 1 To 1000
　　wa = wa + Cells(j, 13)
Next j
Cells(2, 15) = wa / 1000

For j = 1 To 1000
　　wa = 0
　　For i = 1 To 10
　　　　wa = wa + Cells(j, i)
　　Next i
　　heikin = wa / 10
　　
　　wa = 0
　　For i = 1 To 10
　　　　wa = wa + (Cells(j, i) - heikin) ^ 2
　　Next i
　　bunsan = wa / 9
　　Cells(j, 14) = bunsan
Next j
Cells(1, 15) = wa / 1000

wa = 0
For j = 1 To 1000
　　wa = wa + Cells(j, 14)
Next j
Cells(3, 15) = wa / 1000

End Sub

対数変換

一昨日から今日までの株価の変化率と、昨日から今日、一昨日から昨日の変化率の間には
　今日の株価 　今日の株価　昨日の株価
－－－－－－＝－－－－－×－－－－－－
一昨日の株価　昨日の株価　一昨日の株価
という関係があります。よってこの間の平均変化率を求めるには、この右辺の平方根を計算して相乗平均を求める必要があります。
一般に掛け算とかn乗根は計算しづらいですし、統計で広く用いる平均と違うので面倒です。

そこで掛け算を足し算に変換するために、対数を考えることにします。すると
　今日の株価 　今日の株価　昨日の株価
－－－－－－＝－－－－－×－－－－－－
一昨日の株価　昨日の株価　一昨日の株価
は
Log(今日の株価)－Log(一昨日の株価)＝（Log(今日の株価)－Log(昨日の株価)）＋（Log(昨日の株価)－Log(一昨日の株価)）
という、足し算、引き算の式になります。
これを先ほど計算したF列の隣、G列に書きます。
対数の底は10でもeでも上記の計算は成り立ちますが、底がe、つまり自然対数の方が
log(x)≒x-1
という近似式が成り立つので、
log(今日の株価÷昨日の株価)≒今日の株価÷昨日の株価－１≒(今日の株価－昨日の株価)÷昨日の株価
なので先ほど求めた変化率と似た値になります。

計算 G2セルに
=LN(E2)-LN(E3)
と書いても計算できますが、先ほど同様VBAを使って計算して下さい。但し自然対数はExcelのセルに書くときはLnですが、VBAで書くときにはLogです。

練習

TOPIXの自然対数の前日との差を求め、その平均をCells(3,9)、標準偏差を(4,9)に表示するプログラムを書いてください。

ヒストグラム

全部出来た人は、F列の変化率や、G列の対数の差のヒストグラムを書いて見ましょう。Excelでのヒストグラムの書き方の概略は統計学Iに書いています。
ヒストグラムの階級は、-7.5%から7.5%まで0.5%間隔、つまり-0.075から0.075まで0.005間隔にしてみてください。
今回のデータではどちらも正規分布っぽく見えますが、一般には対数の差の方が正規分布に従います。このように、対数にすると正規分布に従う分布を「対数正規分布」と呼びます。