株価データを使ってポートフォリオの収益率の分散を求めようとしています。(エクセルを使ってます)その過程で分散共分散行列が必要です。
個別銘柄を50社選び、それぞれの銘柄の月次終値を5年分用意し、それら変化率を求めました。〔(終値t+1)-(終値t)〕÷(終値t)のような感じで変化率を出しました。次にエクセルの共分散を求める関数COVARを使い、共分散を一つ一つ計算しようと思ったんですが、さすがに50社もあるのでできません。
楽に分散共分散行列を求められる方法はないのでしょうか。
また、数学は苦手ですし、プログラミングもやったことすらありませんので、簡単に求められる方法を教えてください。お願いします。
No.3ベストアンサー
- 回答日時:
「ツール」メニューの「分析ツール」に「共分散」があります.
「ツール」メニューに「分析ツール」が見当たらない場合は,
「ツール」「アドイン」で「分析ツール」にチェックを入れれば,
次から「分析ツール」が表示されます.
分散共分散行列が下三角行列の形で出力されます.
No.2
- 回答日時:
>共分散を一つ一つ計算しようと思ったんですが、さすがに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スクールに行く手もあるでしょう)自分で作り、計算手間を省くかいずれかでしょう。
No.1
- 回答日時:
確かここは個人ページを掲載してはいけないのですよね。
「心理統計学 Excel マクロ」というキーワードでググッてもらえればそう遠くないうちに質問者さんの求めるものがみつかるはずです。お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 数学 数学の問題です。 問1: ある(人数の非常に多い)集団から無作為に6名を選んで身長を測ったところ、そ 2 2022/12/09 12:03
- 数学 確率について 事象Aが起こる確率が0.25である独立行列において、試行回数を5回とした時Aの起こった 2 2022/06/06 19:46
- 統計学 t検定について教えてください 2 2023/02/23 16:35
- 統計学 確率統計でExcelの使い方を教えてください。 3 2022/07/27 19:21
- 数学 【 数Ⅰ 分散 】 問題 20個の値からなるデータがあり, そのうちの8個の値の平均値は3,分散は4 4 2023/02/15 23:28
- 数学 ポアソン分布と二項分布について ・ポアソン分布における期待値と分散は等しくならない場合がある。 ・二 1 2022/06/12 16:29
- 統計学 主成分分析について 1 2022/06/14 14:53
- 数学 以下の数学の問題を教えてください。 確率変数Xは標準正規分布N(0、1)に確率変数Yは平均3のポアソ 3 2022/12/02 19:13
- 統計学 統計学の問題です よろしくお願いします 回帰直線 次のデータから集計表を作成し,以下の問いに答えよ。 2 2023/01/31 23:36
- 数学 数学の答えと解き方を教えてください。 問:ある(人数の非常に多い)集団から無作為に6名を選んで身長を 4 2022/12/14 10:06
このQ&Aを見た人はこんなQ&Aも見ています
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
級数の係数を求める
-
YouTuberでサトマイっていう人...
-
研究室でちょっとういてる?
-
数学II この問題の②について co...
-
(3)のグラフがなぜこうなるの...
-
黄色の式が極値を持ちそれが極...
-
数学II θの範囲に制限がないと...
-
皆さんが高校数学で、一番感動...
-
必要条件 十分条件について
-
次の空欄に当てはまるものを以...
-
以前にも質問させていただいた...
-
微分係数の定義?
-
∫1/lnxdx について
-
連続を示す必要ありますか
-
命題がわかりません!!
-
複素数平面
-
数学II AB=2である2定点A,Bに対...
-
(3)なのですが、どうやら正...
-
行列のべき乗昨日
-
フーリエ変換の相似性(時間軸...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
整数問題 兎に角 難問です 千葉...
-
過去質『すべての自然数とすべ...
-
確率の問題 数学と実生活と
-
微分がムズいです。 新高二です...
-
逆関数の合成関数について質問...
-
初歩的な計算式の問題です。
-
下の画像の中の三角形は正方形...
-
全然わからないので質問する資...
-
a(n)=1/(n+1)! lim[z->π/2](d/d...
-
画像において、質問がございま...
-
虚数の計算を教えてください
-
三角関数の変換で納得いかない...
-
計算手順について
-
過去に 「ii) f(z)=1/(z^2-1) r...
-
数学を勉強すると論理的思考力...
-
【数学・標準偏差σ】標準偏差の...
-
計算式の答えまでの過程を教え...
-
おしえてgooに図形の問題を投稿...
-
こちらの式はtan(z)のローラン...
-
ほんとになんでうごくかわからない
おすすめ情報