やってみよう分析！ 第6章：Excelソルバーの応用(ポートフォリオ最適化)

#まえがき
今回も始まりました。[やってみよう分析！シリーズ](http://qiita.com/f81@github/items/78c0855408317e83aa66)

[前章](http://qiita.com/msano/items/dc96da37ce8ddd4a7c1d)ではExcelの分析ツールとソルバーを活用して回分析や帰や曲線のフィティングの方法を紹介しました。本章では前章に引き続きソルバーに焦点を当て、ソルバーを使ったポートフォリオ最適化問題の解き方について、基本的事項を紹介します。

本章で扱うポートフォリオ最適化問題は金融の例を扱います。想定しているのは、ある投資家が手持ちの資金をいくつかの証券に分散投資する場面を考えます。この時、どのように資金を配分すると最も利益を得ることができるかを考える。これがポートフォリオ最適化問題のあらましです(本章では金融工学を専門的に扱うことは意図していなので、このくらいのイメージで十分です)。

本章で紹介する項目は下記のとおりです。

* __ポートフォリオ最適化問題__
* __平均-分散モデル__
* __コンパクト分解表現__
* __平均-絶対偏差モデル__
* __Excelソルバーでポートフォリオ最適化問題を解く__

では早速始めましょう！

##ポートフォリオ最適化問題を解くときの方針
 __※本節は難しければこの先の"Excelソルバーで平均-絶対偏差モデルを解く"に進んでください。__

まずはじめに問題の詳細に入る前に、ポートフォリオ最適化問題を解くための方針を説明します。次のような具体的問題設定を考えます。

###問題
投資家が4つの何らかの証券`l_1`から`l_4`に分散投資することを考えます。これら証券の __収益率__が`R_i`(i=,1,2,3,4)で与えられると、全収益率は __投資配分比率__を`w_i`( w_1+w_2+w_3+w_4=1)として次の様に与えられます。

```math
r=\sum_{i=1}^{4} E[R_{i}]w_{i}, \qquad \sum_{i=1}^{4}w_{i} = 1, \qquad w_{i} \geq 0

```
ここでE[*]は期待値を表しています。この全収益率`r`を、 __リスク尺度(risk measure)__ `RM`と呼ばれる量を最小化させつつ、ある目標全収益率`r_target`より大きくさせる投資配分比率を求める問題を考えます。リスク尺度は投資配分比率の関数です(RM=RM(w))。まとめるとここでのポートフォリオ最適化問題は次のように定式化されます。

```math
\text{Minimize}~~  \text{RM}(w)\\
\text{subject to} \\
r=\sum_{i=1}^{4} E[R_{i}]w_{i} \geq r_{\text{target}}, \qquad \sum_{i=1}^{4}w_{i} = 1, \qquad
w_{i} \geq 0　~~(i=1,2,3,4)

```
 __制約条件の数は必要に応じて更に増えることがあります。__

###リスク尺度とは
リスク尺度とは、大雑把には全収益率の期待値がどれくらい上下にブレるか示した量です。リスク尺度が大きくなるように投資配分すると、得られるリターンが期待より大きくなる見込みがある一方で、大きく下ブレることも予想されます。逆にリスク尺度が値が小さくなるような投資配分比率であれば、期待収益率が大きく上振れることは少ないですが大きく下振れることを防ぐことができます。

ポートフォリオ最適化ではリスク尺度を最小化させることをよく考えます。これは暗に __投資家がリスク回避を選好していると考えるためです__。リスク尺度の選択によって投資配分比率や期待収益率のパフォーマンスが異なってきます。

本章では最適化問題をどうやってExcelソルバーで解くのかに焦点を当てています。ポートフォリオの様々なモデルや特徴については下記の文献をご参照ください。

* [金融工学と最適化 (経営科学のニューフロンティア 5)](http://www.amazon.co.jp/dp/4254275153) 枇々木 規雄, 朝倉書店
* [投資信託を用いた個人投資家の資産運用モデル](http://www.jasfp.jp/pdf/08-yamamoto.pdf) 山本零, 『ファイナンシャル・プランニング研究』NO．8 


##平均-分散モデル
平均-分散モデルはポートフォリオ選択問題に対する代表的なモデルです。このモデルで期待収益率の分散をリスク尺度とし、それを最小にするように投資配分比率を選択するモデルになっています。

平均-分散モデルでは各証券の収益率の期待値と分散は次のように過去の収益率時系列データ`R_it`から求めることができるとします。


```math
r_{j} \equiv E[R_{j}] = \frac{1}{T}\sum_{t=1}^{T} R_{jt}  \\
r = \sum_{i=1}^{4} r_{i}w_{i} \\
V(R) = E[(R-E[R])^{2}] = 
\sum_{i=1}^{4} \sum_{j=1}^{4} \Biggl\{ \frac{1}{T} \sum_{t=1}^{T}(R_{it}-r_{i})(R_{jt}-r_{j}) \Biggr\}
w_{i} w_{j}　
= \sum_{i=1}^{4} \sum_{j=1}^{4} \sigma_{ij} w_{i} w_{j}　
```

したがって平均-分散モデルのポートフォリオ最適化問題は次のように定式化されます(下記の条件を満たしつつ分散を最小化させるような投資配分比率`w_i`を見つける問題)。

```math
\text{Minimize}~~  \sum_{i=1}^{4} \sum_{j=1}^{4} \sigma_{ij} w_{i} w_{j}　\\
\text{subject to} \\
r \geq r_{\text{target}}, \qquad \sum_{i=1}^{4}w_{i} = 1, \qquad
w_{i} \geq 0　~~(i=1,2,3,4)

```

###コンパクト分解表現
上記の数理計画問題は __コンパクト分解表現__と呼ばれる形式にも書き換えることが可能です。問題をコンパクト分解表現にすることで問題の見通しが良くなったり、解きやすくなったりするメリットがあります。

平均-分散モデルをコンパクト分解表現に書き換えるために、分散が次のように書き換えることが可能であることに注目します。

```math
V(R) = \frac{1}{T} \sum_{t=1}^{T}
  \Biggl\{ (\sum_{i=1}^{4} R_{it} w_{i}-r)(\sum_{j=1}^{4} R_{jt}w_{j}-r) \Biggr\} 
\equiv \frac{1}{T} \sum_{t=1}^{T} y_{t}^{2},  \\
y_{t} \equiv \sum_{i=1}^{4} R_{it} w_{i}-r

```
`y_t`は新たに導入された変数です。この事実を使うと平均-分散モデルのポートフォリオ最適化問題は次のように書き換えられます。

```math
\text{Minimize}~~  \frac{1}{T} \sum_{t=1}^{T} y_{t}^{2}　\\
\text{subject to} \\
\sum_{i=1}^{4} R_{it} w_{i} - y_{t} = r, \\
r \geq r_{\text{target}}, \qquad \sum_{i=1}^{4}w_{i} = 1, \qquad
w_{i} \geq 0　~~(i=1,2,3,4)

```

##平均-絶対偏差モデル
平均-絶対偏差モデルでは分散の代わりに次の __絶対偏差__(absolute deviance)をリスク尺度として採用します。

```math
AD(R) = \frac{1}{T} \sum_{t=1}^{T} \Biggl| \sum_{i=1}^{4}R_{it}w_{i}-r   \Biggr|
```
この量には絶対値が含まれています。コンパクト分解表現を使うと平均-絶対偏差モデルを線形計画問題として扱うことができます。これを実現させるために次のような関係を定義します。

```math
y^{+}_{t} \geq 0,　\qquad y^{-}_{t} \geq 0, \\
y^{+}_{t} + y^{-}_{t} = \Biggl| \sum_{i=1}^{4}R_{it}w_{i}-r   \Biggr|, \\
y^{+}_{t} - y^{-}_{t} = \sum_{i=1}^{4}R_{it}w_{i}-r \Rightarrow  
\sum_{t=1}^{T} y^{+}_{t}  = 
\begin{cases}
\sum_{t=1}^{T} y_{t} \geq 0 \\
y^{+}_{t}  = \sum_{i=1}^{4}R_{it}w_{i}-r +y_{t} \geq 0
\end{cases}
```

このとき平均-絶対偏差モデルでポートフォリオ最適化問題は次のように線形計画問題として書き表すことができます。

```math
\text{Minimize}~~  \frac{1}{T} \sum_{t=1}^{T} y_{t}　~~~(y^{-}_{t} \equiv y_{t})\\
\text{subject to} \\
\sum_{i=1}^{4} R_{it} w_{i} ＋ y_{t} \geq r ~~(t=1, \cdots , T), \\
y_{t} \geq 0, ~~r \geq r_{\text{target}} ~~(t=1, \cdots , T), \\
\sum_{i=1}^{4}w_{i} = 1, ~~w_{i} \geq 0　~~(i=1,2,3,4)

```

####書き換えるための条件
絶対偏差ADの絶対値の部分を`y_t`で書き表せるためには次の条件が必要です。まず上記の絶対偏差が最小になる解が次の条件を満たしているとします。

```math
y^{+}_{t} > 0, ~y_{t} = y^{-}_{t} >0 \rightarrow ( z^{+}_{t},z^{-}_{t} ) = 
\begin{cases}
(y^{+}_{t} - y^{-}_{t}, ~0) \text{   if } y^{+}_{t} \geq y^{-}_{t}\\
(0, ~y^{-}_{t} - y^{+}_{t} ) \text{   if } y^{+}_{t} < y^{-}_{t}
\end{cases}
```
このとき

```math
z^{+}_{t} + z^{-}_{t} < y^{+}_{t} + y^{-}_{t}
```
となります。これは最小解であると仮定したにもかかわらず、それよりも小さい値が存在することを意味しています。したがって __`y^{+}_{t}`と`y^{-}_{t}`はどちらか一方が0であること__が必要です。逆に最小解が見つかった時にはどちらか一方は0であるという事になります。

##Excelソルバーで平均-絶対偏差モデルを解く
今までは平均-分散モデルや平均-絶対偏差モデルの理論的背景を説明してきました。ここでは具体的に平均-絶対偏差モデルをExcelソルバーを使って説いてみたいと思います。平均-絶対偏差モデルは上述してきたように線形計画問題として定式化できるので、Excelソルバーで簡単に解くことができます(扱える変数の数に制約はありますが)。線形計画問題用のソルバーライブラリなら様々なプログラミング言語向けで比較的フリーのものを見つけやすいので、そういう意味でも平均-絶対偏差モデルは実用的にも扱いやすいと思われます。

ここで扱うテストデータは下記のものです。これはある4つの仮想収益率を時系列で29日分を表しているとしています。

|日付|l_1|l_2|l_3|l_4|
|---|---|---|---|---|
1|-0.00120153065421321|0.0380048089808824|-0.000303873486371429|0.0183133857272363
2|-0.00875319764093676|0.00750730657101174|0.0187881175156959|0.00405168168299995
3|0.0141313057519581|-0.00480224587721186|0.0211116532539269|-0.00859677575024866
4|-0.00026089277347954|-0.00432006835634278|-0.00671115000654706|0.0205754516520216
5|0.0230835221631592|0.0428244722829147|9.90283983480213E-05|-0.00247133285460752
6|0.0278628771011717|-0.019356828954738|-0.00740798431316384|-0.00101736234100942
7|0.0130697045380814|0.0188506465519101|-0.00523275574503766|0.0445910602540186
8|0.0411167849440832|-0.0210227558705826|0.0105982386670339|0.0181642469063737
9|0.00862197349532295|0.0194908416797854|0.0245934046268627|0.00709581280876198
10|0.0166329870758843|0.00983444518545119|0.030223257019714|0.0158169011616007
11|0.0251692450492102|0.0234264530594299|0.0485315350214701|0.0169290052447109
12|0.0036280008797798|-0.0238983867296893|0.0221588090462638|-0.0212142656439686
13|0.0364523781300635|0.0453273177976274|0.00684582294461588|0.0169143763754707
14|0.0212167263941988|0.0351431617328777|-0.0217647873161181|0.0383881403307489
15|-0.0165150470435798|0.0319588787470421|0.0440612973224269|0.0371634615256434
16|-0.0209412151441614|-0.00594844301585869|0.0390005824355782|0.0234988931994846
17|-0.0163049250497025|0.0235517455011055|0.0254367405717981|0.0478825651627125
18|0.0218889495295429|-0.00390039496419029|0.00999515377794965|0.0025776781494086
19|0.000167684857827004|0.000127471697942828|0.0187525342938277|0.0379500995171109
20|0.0320038917185764|-0.0158606942769403|0.0348337189422131|-0.00187666832618083
21|0.0181514768796902|-0.0176344811110534|-0.0189057906647764|-0.0208109253641162
22|0.0241881816658587|0.0455000763886743|0.011393810955836|0.0199381468086756
23|-0.017270902625127|-0.01820057224978|0.0246536997732942|0.0134747443795706
24|0.0336124764557386|0.0369219499655584|0.033199777646845|0.0238727536930151
25|-0.000756539563920317|0.00565398148175538|0.0221568251473784|0.00256183858438883
26|0.0458304879491117|0.00439694780137093|0.025492911991075|0.00558063721177299
27|0.0266866459295242|0.013707194604382|0.0285421934279625|0.0272808773191875
28|0.0371346323314259|0.0509490436623156|-0.0220063375794736|-0.00732380784203699
29|0.0501540409823486|0.0297740391226334|0.0169058439224333|0.00451478718620258

改めて平均-絶対偏差モデルの問題を思い出しておきます。`R_jt`がj番目のt日目収益率を表しているとして下記の問題を解くことで投資配分比率`w_i`を求めます(T=29)。

```math
\text{Minimize}~~  \frac{1}{T} \sum_{t=1}^{T} y_{t}　\\
\text{subject to} \\
r_{j} = \frac{1}{T}\sum_{t=1}^{T} R_{jt}, \qquad r = \sum_{i=1}^{4} r_{i}w_{i} \\
\sum_{i=1}^{4} R_{it} w_{i} ＋ y_{t} \geq r ~~(t=1, \cdots , T), \\
y_{t} \geq 0, ~~r \geq r_{\text{target}} ~~(t=1, \cdots , T), \\
\sum_{i=1}^{4}w_{i} = 1, ~~w_{i} \geq 0　~~(i=1,2,3,4)

```

###Excelシートの準備

上記テーブルのデータをラベルも含めてまるっとエクセルのシートに`A5`セルから貼り付けます。

![fig01.png](https://qiita-image-store.s3.amazonaws.com/0/40982/cfa8e406-fd2b-6c21-0874-3eab27a0a68b.png)

セル`F6`にl_1からl_4までの平均を算出します。その式を29日めまでコピーペーストします。最後にセル`F35`に`F6`から`F34`までの平均値を入力します(`=AVERAGE(F6:F34)`)。


![fig02.png](https://qiita-image-store.s3.amazonaws.com/0/40982/5386a566-5b4a-0d08-720d-11f1ba01e787.png)

次は`G6`セルに`=$H$2*B6+$H$3*C6+$H$4*D6+$H$5*E6`と入力します(投資配分比率がセル`H2`から`H5`まで`w_1`から`w_4`の順で並んでいるとしています)。入力した式を29日めまでコピーペーストします。続いてセル`G35`にセル`G6`から`G34`までの平均値を入力します(`=AVERAGE(G6:G34)`)。

![fig03.png](https://qiita-image-store.s3.amazonaws.com/0/40982/515563a1-e651-c55c-2c06-0223fa87fd58.png)

セル`H1`に`=SUM(H2:H5)`と入力します。またセル`H35`に`=AVERAGE(H6:H34)`と入力します。

![fig04.png](https://qiita-image-store.s3.amazonaws.com/0/40982/61a5b7aa-10af-911c-efb3-e61193be9f80.png)

セル`I6`に`=G6+H6`と入力します。この式をセル`F34`までコピーペーストします。

![fig05.png](https://qiita-image-store.s3.amazonaws.com/0/40982/5c1fade3-819a-a239-7b26-b00665674e08.png)

セル`J6`に`=$G$35`と入力してセル`F34`までコピーペーストします。

![fig06.png](https://qiita-image-store.s3.amazonaws.com/0/40982/6701114a-e479-35b1-6568-5e182ccdf831.png)

###ソルバーで解く
準備してきたシートをもとにソルバーで問題を解いてみます。求めるのは投資配分比率で、エクセルシート上ではセル`H2`から`H5`に対応します。

[前章](http://qiita.com/msano/items/dc96da37ce8ddd4a7c1d)で紹介したように、Excelのデータタブからソルバーを起動させます。ソルバーが起動したら下記の設定をします。

* 目的セル：`$H$35`
* 目標値：最小値
* 変数セルの変更：`$H$2:$H$34`
* 制約条件の対象には下記の3つを設定(追加ボタンより入力)
* `$G$35 >= $F$35 * 1.03`
* `$H$1 = 1`
* `$I$6:$I$34 >= $J$6:$J$34`
* 制約のない変数を非負数にするにチェックを入れる
* 解決方法の選択：シンプレックスLPを選択


![fig07.png](https://qiita-image-store.s3.amazonaws.com/0/40982/581077e6-86de-8fd5-37b8-b09adb9e5b3c.png)

上記のように設定することが、最初に示した平均-絶対偏差モデルの定式化に等しくなっています。設定が完了したら解決ボタンを押します。問題がなければソルバーの結果(投資配分比率)がセル`H2`から`H5`に出力されます。


![fig08.png](https://qiita-image-store.s3.amazonaws.com/0/40982/5095ab1f-7e99-9155-8e02-0526f39ce875.png)


##まとめ
本章ではExcelソルバーを使って最適化問題の一つであるポートフォリオ最適化問題を解くことを考えてきました。金融ポートフォリオ最適化モデルとしてよく知られている平均-分散モデルと平均-絶対偏差モデルの最適化問題としての定式化を紹介しました。コンパクト分解表現を使うことで元の問題が簡潔に表現可能で、特に平均-絶対偏差モデルは線形計画問題で定式化できることを紹介しました。最後に平均-絶対偏差モデルをExcelソルバーで解く紹介をしました。

本章で紹介してきたようにExcelソルバーを使うことで、簡単に制約条件付き目的関数の最小/最大を求めることができるので便利です。Excelソルバーには取り扱える変数に上限がありました。しかし、Excel上でロジックをしっかり理解しておくことは、その他のプログラミング言語でソルバーライブラリを使った実装をするときに役立ちます。ぜひExcelソルバーを活用してみてください！！

===========================================

##こちらもよろしくおねがいいたします。

* [目次](http://qiita.com/f81@github/items/78c0855408317e83aa66)

###入門編

###第1部イントロダクション
* [第1章：分析技術の活用～ビジネス視点から～](http://qiita.com/msano/items/2f39517b4cd65e14003e)
* [第2章：分析手法を眺めてみよう！](http://qiita.com/msano/items/bfdb3c04d59992c7ebcb)

###第2部エクセルで学ぶ分析入門 
* [第3章：ExcelとMySQLの連携](http://qiita.com/msano/items/fdcc0cbb8e41b988ce2d)
* [第4章：ExcelとMySQLの連携2 csvデータのimport ](http://qiita.com/msano/items/7dff8bc241799875b7e9)
* [第5章：Excelの分析ツールとソルバーの活用(回帰、最小2乗法)](http://qiita.com/msano/items/dc96da37ce8ddd4a7c1d)

==================
# お知らせ：Fringe81エンジニアチームでは仲間を募集しています！
　<img src="http://www.fringe81.com/images/common/logo_fringe81.png" border="0"><br>
　チームの雰囲気や採用情報については下記ページをご覧下さい。<br>

- [Fringe81エンジニアチームのページ](http://www.fringe81.com/tech.html)
- [採用情報ページ](http://www.fringe81.com/recruit/)