質問

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

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

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

通報する

回答 (3件)

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

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

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

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

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

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

>さすがに50社もあるのでできません。

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

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

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

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

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

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

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

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

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

このQ&Aは役に立ちましたか?1 件

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

新しく質問する

注目の記事

おしトピにAndroid版アプリが登場

話題のトピックにさくっとコメントできる「おしトピ」に Android版アプリが登場! もっと身近に使いやすくなりました。
今ならダウンロードで話題の掃除ロボットや全天球カメラが 当たるプレゼントキャンペーンも実施中。

このQ&Aを見た人が検索しているワード


新しく質問する

このカテゴリの人気Q&Aランキング

毎日見よう!教えて!gooトゥディ

べんりQ&A特集