ポートフォリオ最適化時の共分散とリスクを自動計算

[PR]当サイトはアフィリエイト広告を利用しています。

以前農作物のポートフォリオ最適化でリスクの計算を共分散を使って求めました。

このとき共分散はエクセルの関数を、リスクは数式直接で求めていました。

しかし作物数が増えたら手入力だと限界があると感じました。

そこで共分散とリスクの計算を自動で求めるべく、VBAを使って計算するようにしました。

こんなエクセルを作ります。

それでは共分散を求めてみましょう。

作成したVBAは以下。

Sub co()
    Dim i, j As Long
    Dim year As Long
    Dim item As Long
    year = 3
    item = 3
    For i = 1 To item
        For j = 1 To item
            Set a = Range(Cells(i + 2, 2), Cells(i + 2, 1 + year))
            Set b = Range(Cells(j + 2, 2), Cells(j + 2, 1 + year))
            Cells(i + 2, j + 9) = Application.WorksheetFunction.Covariance_P(a, b)
        Next j
    Next i
End Sub

なんということはない、VBAでエクセルの関数を呼び出して指定したセルで計算させているだけです。

リスクを求めるためのユーザー定義関数が以下。

Function Func1(arg As Range, arg2 As Range)
    Dim re As Double
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    Dim n1 As Long
    v = arg.Value
    v2 = arg2.Value
    n1 = UBound(v, 1)
    re = 0
    For i = 1 To n1
        For j = 1 To n1
            re = re + v(i, j) * v2(1, i) * v2(1, j)
        Next j
    Next i
    Func1 = re
End Function

リスクの式自体は簡単なものなので、コードも簡単です。

むしろ手入力より間違いが少なくなると思います。