分散共分散行列を簡単に手っ取り早く求めたい

質問

株価データを使ってポートフォリオの収益率の分散を求めようとしています。（エクセルを使ってます）その過程で分散共分散行列が必要です。

個別銘柄を５０社選び、それぞれの銘柄の月次終値を５年分用意し、それら変化率を求めました。〔（終値t+1）－（終値t）〕÷（終値t）のような感じで変化率を出しました。次にエクセルの共分散を求める関数COVARを使い、共分散を一つ一つ計算しようと思ったんですが、さすがに５０社もあるのでできません。

楽に分散共分散行列を求められる方法はないのでしょうか。
また、数学は苦手ですし、プログラミングもやったことすらありませんので、簡単に求められる方法を教えてください。お願いします。

回答No.3

kumagoro06 · ベストアンサー

「ツール」メニューの「分析ツール」に「共分散」があります．
「ツール」メニューに「分析ツール」が見当たらない場合は，
「ツール」「アドイン」で「分析ツール」にチェックを入れれば，
次から「分析ツール」が表示されます．
分散共分散行列が下三角行列の形で出力されます．

回答No.2

moonliver_2005

＞共分散を一つ一つ計算しようと思ったんですが、さすがに５０社もあるのでできません。

共分散とは、2 組の対応するデータ間での標準偏差の積の平均値ですよね。そうすると１行に個別銘柄N1について月次終値データを入力します。５年分ですから高々60列です。これをN2、N3・・・とすれば50行です。そうすると50行×60列の入力データ表が既に出来ているはずです。

この表の下に50行×50列の表を定義します。行も列も（つまり縦横ともに）見出しは個別銘柄です。個別銘柄iと個別銘柄ｊの交点のセルを共分散（i,j）に入力します。ということは全てのi,jについて１個１個、COVER関数を呼び出し、上の表の１行、１行を引数に入れれば良いでしょう。

50×50回=2500回、関数定義が必要ですが、固定コピー機能（例えば$4$Aみたいな$を付けるとコピーしても移動しない機能）を工夫して使うと、かなり入力が省略できるでしょう。

この結果得られた50行×50列の表は「共分散行列」そのものではないでしょうか？

＞さすがに５０社もあるのでできません。

私はそうは思いませんが、自分でテストしていないので固定コピーではそれ程省力化できないとしましょう。そうすると
仮定として、１セル平均１分でCOVER入力できれば、2500分、約４0時間、５日つまり１週間強かかるでしょう。２分なら倍の２週間強です。

プログラミングもやったことすらありませんので・・・ならこれしか方法ありませんと私は思います。

私はデーターベースを作っていますが、2,3週間毎日入力作業することは、何も苦ではありません。データ処理とはこういうものですと割り切らざるを得ないと私は思います。

東証全銘柄でやったら1400社？の共分散行列になりますよね。これに比べたら50社なんて、かわいいものでしょう。そう思われません？

50社５年間のデータ入力に何時間かかりました？それと同等かそれ以上かけてもそれだけの価値はあるのでは？

ただし、この計算を毎日、毎月とかやる必要がある場合は話しは違います。そうするとインターネット調べて、こういう計算するプログラムを作ってくれる個人とか会社探して、プログラム作ってもらうのが一番簡単でしょう。ただし、何十万円、何百万円のプログラム作成料の支払いが必要となります。

インターネットの調べ方沢山あると思いますが、確か日本テレワーク協会のHPに行くと、個人つまりSOHOでEXCELプログラム作りますみたいな専門家が沢山登録されていて、その数社、数人に見積もり依頼すれば、質問者の回答になると思いますがいかがですか？

１円も払いたくないなら、自分で手作業で入力するか、プログラミングの勉強して（それ程難しくないです。PCスクールに行く手もあるでしょう）自分で作り、計算手間を省くかいずれかでしょう。

回答No.1

backs

確かここは個人ページを掲載してはいけないのですよね。「心理統計学　Excel　マクロ」というキーワードでググッてもらえればそう遠くないうちに質問者さんの求めるものがみつかるはずです。