株価データを使ってポートフォリオの収益率の分散を求めようとしています。(エクセルを使ってます)その過程で分散共分散行列が必要です。
個別銘柄を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ランキング
-
過去に 「ii) f(z)=1/(z^2-1) r...
-
確率の問題 数学と実生活と
-
「an=(n-1)/(n+1)のときlim[n→∞...
-
微分とは何か(2)
-
整数問題 兎に角 難問です 千葉...
-
BINGが間違えた、とっても簡単...
-
これの極限値を求める問題で、 ...
-
こちらの式はtan(z)のローラン...
-
30分の動画を2倍速で見たら、3...
-
分子生物
-
指数関数と階乗。グラフで表し...
-
iphone の自分の声が高すぎるん...
-
返信の続きはありますか
-
数学Aについて、4でも6でも割り...
-
△ABCの辺BCの延長上の点Dを通る...
-
連続的ポストごめんなさい
-
2024.4.7 03:42の質問に対する2...
-
逆三角関数の方程式の問題です...
-
ベクトル3重積
-
方程式の計算について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
整数問題 兎に角 難問です 千葉...
-
えこれわかるひといますか?
-
長方形の分類(幾何学)
-
京都大学理系 過去問 整数問題
-
天孫降臨の神武天皇のY染色体...
-
中学数学の図形の問題です。
-
数学I アホらしい質問なのでそ...
-
正方行列Aについて
-
このルートを外す計算どうすれ...
-
素数についての一考察
-
これて最後どうやりますか??
-
この数学の問題はどうやって解...
-
高校数学の整数問題です。
-
暗闇で2人が出会うには両方動...
-
なんでですか?
-
大学入試の数学で、解答を進め...
-
n^2+n-4032はどうやって解くん...
-
(-1) ^2πってなんで1じゃないん...
-
標準偏差
-
一橋大学過去問 整数 素数 かな...
おすすめ情報