【株式】KABU+を使ってポートフォリオ最適化をやってみた【ExcelVBA】
サマリ
KABU＋からデータを取得
個人用の株式ポートフォリオ最適化
平均分散モデル＋α
Excelソルバーを使って計算
アップロード
(1) 作ったツール
ツールそのものもアップロードしておきます。
データは公開できないので削除しています
他人が使えるように作ってませんのであくまで参考ということで
KABU＋のファイルから株価の時系列データを取得しようとすると、ファイルが分割されているので自分でファイルを結合する必要があります。ヘッダ付きなのでヘッダもとる必要があります。下のコードが参考です。PowerShellです。
Get-Content コピー元フォルダ\*_*.csv | Select-Object -Skip 1 | Out-File 株価データ.csv -Encoding default(2) 説明動画
ツールの説明動画です。
勢いあまってyoutubeデビューしてみましたが、かなりナイーブです。
興味ある方は先に記事を読んでください。
1．導入・雑談
私が学生だった十数年前から比べると色んなデータが手に入りやすくなった。株に関していえば、今までもYahooファイナンスから取れたものの著作権云々やらクローリング自体も面倒だったが、表題にある「KABU+」のようなお手軽サービスが登場してきた。
昔からやりたかったけど手が出なかった株のポートフォリオ最適化のツールを作ってみました。学生の頃の知識で作ってるので古臭い＆間違いも多いかもしれませんが興味があれば見ていってください。
2．KABU＋とは
公式見て頂くのが早いです。
ざっくりポイントを説明すると
月額最低1650円
株価データだけでなくのデータや銘柄の決算情報なども取れる
過去一年分のデータが取得できる
もっと過去のデータは別料金（１年1万円）
共有ドライブからファイルをコピーする形で取得できる
ファイルコピーが結構遅いが我慢できる範囲
過去データが別料金なのがケチ臭いが仕方ない。今風のAPI系じゃないのが個人的にはうれしい。
3．ポートフォリオの最適化
(1) ポートフォリオとは
元来の意味は書類入れのこと。金融投資の文脈でいうポートフォリオとは『書類入れにどんな株券や債券が何枚入ってるか？』というニュアンスで、資産毎の投資比率を指します。
今回はどの株式を何株持つべきか？を解くことが目的となります。
(2) 最適化とは
応用数学の世界では与えられた前提条件の元で y = f(x) を最小化する x を見つけることを指すことがあります。
(3) 平均分散モデル
「ポートフォリオの最適化」といえば「平均分散モデル」です。決まってます。Wikipedia先生の「平均分散分析」の項をご参照。
資産ｉ への投資金額＝ｘ[ｉ]
資産ｉの期待収益率＝ｕ[ｉ]
資産ｉの分散（ボラティリティ）＝ｖ[ｉ]
資産ｉ,ｊの共分散＝ｃ[ｉｊ] （ｃ[ｉｉ]＝ｖ[ｉ]）
とした場合、ポートフォリオ ∑_ｉ（資産ｉ）について、
期待収益額＝∑_ｉ（ｘ[ｉ] ＊ ｕ[ｉ]）
分散（ボラティリティ）＝∑_ｉｊ（ｘ[ｉ] ＊ ｘ[ｊ]＊ｃ[ｉｊ]）
となる。ポートフォリオの期待収益額に自分の目標値をセットして、その中で一番ボラティリティが低いポートフォリオを作ろうというもの。定式化するとこんな感じ。
最小化：
∑_ｉｊ（ｘ[ｉ] ＊ ｘ[ｊ]＊ｃ[ｉｊ]）
制約 ：
∑_ｉ（ｘ[ｉ] ＊ ｕ[ｉ]）＞目標収益
∑_ｉ（ｘ[ｉ]）＜予算
ｘ[ｉ]＞０
ここで、ｕ[ｉ]やｃ[ｉｊ]は過去データから素直に平均と分散として計算するのが普通。これは二次計画問題といわれるもので、最小化の対象がｘの二次式で制約がｘの一次式であれば解けることが知られている。
4．今回の工夫
(1) 平均分散モデルの問題点
これは私見ないし感想ですが、期待収益率やボラティリティを過去データの平均や分散で置いていいのか？という点。インデックスの長期運用だったらそれもありかと思います。個別株でやるにはちょっと無理があると思います。過去と同じ分布で将来の収益が発生するとはとても思えない。
平均分散モデルの説明で、「過去データから素直に平均と分散として計算するのが普通」と書きましたが、前提条件として与えるものなので、独自に設定してもよい。ここがポイントかなと思います。
(2) 問題への対応方針
まず思いつくのは、DeepLearingを応用して各種指標を入力にして収益率やボラティリティを予測しながら最適化する方法。DeepLearing自体が結局は最適化問題を解いて最も適合するパラメータを探す手法なので、平均分散モデルを組み込んでしまえばいい。ただ複雑になるので今回はパス。
次に考えたのはスクリーニングの応用で、こっちが今回のお話。
スクリーニングとは各種の指標でフィルタすること。
どんな指標でどうスクリーニングするのがベストなのかをDeepLearingで適切に判断するのはさっきの話に戻ってしまう。そうではなくスクリーニング条件は自分の好みで設定する。ただし個別株に対してスクリーニングするのではなく、出来上がりのポートフォリオがお気に入りの条件にフィットするような組み合わせを探す。
(3) 提案手法
結論からいうと、最適化問題を解く。
最小化：
∑_ｉｊ（ｘ[ｉ] ＊ ｘ[ｊ]＊ｃ[ｉｊ]）
制約 ：
∑_ｉ（ｘ[ｉ] ＊ ｕ[ｉ]）＞目標収益
∑_ｉ（ｘ[ｉ] ＊ ａ[ｉ]）＞ａの目標
∑_ｉ（ｘ[ｉ] ＊ ｂ[ｉ]）＞ｂの目標
・・・
∑_ｉ（ｘ[ｉ]）＜予算
ｘ[ｉ]＞０
ａとかｂとかはＰＥＲとかＰＢＲとかそういうもの。これをどうにかこうにか投資額の加重平均で表現できるものならＰＣのスペックが許す限り追加できる。これを実現するツールを作ってみる。
5．実装
(1) 実装方法
EXCELのソルバーを中心にしたVBAツールとする。pythonの方が恰好いいがUI含めて試行錯誤するにはExcelが一番いい。
データの操作にSQLが使いたいのでACCESSを併用する。MySQL等でもいいがセットアップやExcelとの連携が簡単なACCESSを採用した。
(2) 制約
過去データがちょっと高いので1年分のデータでやれる範囲で実装する。本当は「EPSの伸び率」とか入れたかったけど断念。
作ってから気が付いた。Excelソルバーの制約で計算対象の銘柄数は200以下。事前フィルタで200以下に抑える必要がある。
なので事前フィルタも通常のスクリーニング機能として入れたかったけど体力の限界であきらめた。売買金額上位200銘柄を固定で対象とする。
６．まとめ
ということでKABU＋のデータとExcelでツールを作ってみました。記事冒頭にファイルを貼ってるのでよかったら見てください。
この記事が気に入ったらサポートをしてみませんか？