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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (3件)

「ツール」メニューの「分析ツール」に「共分散」があります.


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

>共分散を一つ一つ計算しようと思ったんですが、さすがに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スクールに行く手もあるでしょう)自分で作り、計算手間を省くかいずれかでしょう。
    • good
    • 0

確かここは個人ページを掲載してはいけないのですよね。

「心理統計学 Excel マクロ」というキーワードでググッてもらえればそう遠くないうちに質問者さんの求めるものがみつかるはずです。
    • good
    • 0

このQ&Aに関連する人気のQ&A

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

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

このQ&Aと関連する良く見られている質問

Qエクセル プルダウンの作り方

お世話になります。
エクセルでプルダウンの作り方を教えていただけませんでしょうか?
住所を▼のボタンでクリックしたら【北海道,青森,秋田,岩手・・・】などの選択ができるようにしたいのです。
宜しくお願いします。

Aベストアンサー

メニューから「データ」、「入力規則」、「設定」で「入力値の種類」を「リスト」を選択します。
そうすると「元の値」という表示がでますので、そこで前もって作っておいたリストの範囲を指定します。
多くないのでしたら、そのままそこにカンマで区切って入力しても出来ます。

Q分散共分散行列の逆行列

以下の行列を考えます.
行列X(M行N列,成分の値は分散1のガウスノイズ)
行列Xの分散共分散行列S(M行M列),
行列Sの逆行列Y(M行M列)

N=M+1のときはN≠M+1のときに比べて行列Yの各成分の大きさがかなり大きくなる,という結果が得られました.
(計算にはMATLABの関数covとpinvを用いました.)
これは数学的に正しいことなのでしょうか.
また,正しいとしたらどうしてこのようなことが起こるのでしょうか.

行列Yの計算結果の例を以下に示します.(行列Yを500回算出し,それらの平均値を示しています.)
http://wisteria.orz.ne.jp/download/pinvcovX.jpg
縦・横方向の軸は行列Yのインデックスを,高さ方向の軸は行列Yの成分の値を示しています.
上述した現象の原因についてご教授いただけると幸いです.

Aベストアンサー

Rで試してみたら同じような結果になりました。
その結果が添付画像です。
それぞれのz軸の上限は「z:上限」で表しています。

繰り返し回数を増やしたら各成分が小さくなると思っていたのですが、逆に大きくなりました。

Nが大きくなるにつれ、単位行列に近づいていくようです。
これ自体は分散共分散行列が単位行列に近づくことから当然ですか。

試したコード
func1 <- function(x, M)
{
y <- matrix(x, ncol = M)
z <- cov(y)
c(as.vector(z), as.vector(solve(z)))
}

func2 <- function(M = 20, N = 21, R = 500)
{
x <- matrix(rnorm(M*N*R), nrow = R)
y <- apply(x, 1, function(t) func1(t, M))
z <- rowMeans(y)
list(cov = matrix(z[1:(M*M)], ncol = M), covinv = matrix(z[(M*M+1):(2*M*M)], ncol = M))
}

x <- func2(20, 21, 10000)
y <- func2(20, 21, 500)
z <- func2(20, 22, 500)
w <- func2(20, 100, 500)

par(mfrow = c(2, 2))
persp(x$covinv, zlim = c(-0.5, 10^10), xlab = "x", main = "M = 20, N = 21, 10000回の平均, z:10^10")
persp(y$covinv, zlim = c(-0.5, 10^5), xlab = "x", main = "M = 20, N = 21, 500回の平均, z:10^5")
persp(z$covinv, zlim = c(-0.5, 10^3), xlab = "x", main = "M = 20, N = 22, 500回の平均, z:10^3")
persp(w$covinv, zlim = c(-0.5, 10), xlab = "x", main = "M = 20, N = 100, 500回の平均, z:10")
par(mfrow = c(1, 1))

Rで試してみたら同じような結果になりました。
その結果が添付画像です。
それぞれのz軸の上限は「z:上限」で表しています。

繰り返し回数を増やしたら各成分が小さくなると思っていたのですが、逆に大きくなりました。

Nが大きくなるにつれ、単位行列に近づいていくようです。
これ自体は分散共分散行列が単位行列に近づくことから当然ですか。

試したコード
func1 <- function(x, M)
{
y <- matrix(x, ncol = M)
z <- cov(y)
c(as.vector(z), as.vector(solve(z)))
}

func2 <- function(M = 20, N = 21, R =...続きを読む

Qエクセルでプルダウンメニューの作り方

  エクセルの画面で、よく三角形を逆さまにした形をクリックするといくつかメニューが出てき、どれかを選べるようになっていますが、その作り方を教えてください。
 会社で人事を担当していますが、三角形(プルダウンボタン)をクリックすると社員氏名一覧が表示され、そこから選択できるようにしたいのです。
 しばらく自力でいろいろやってみましたが、さっぱり見当がつかず、どうやればいいのか分かりませんでした。よろしくお願いします。

Aベストアンサー

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならないでしょうか?
Excel2007の場合は↓のURLが参考になるかもしれません。

http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-ny_kis2.html

尚、同一Sheetに「名簿表」を作成する場合は名前定義する必要はなくて
「元の値」の右側の四角をクリックし、リスト表示したいセルをそのまま範囲指定すればOKです。

以上、お役に立てば良いのですが・・・m(_ _)m

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならない...続きを読む

Q共分散行列と分散共分散行列の違いとはなんですか?

共分散行列と分散共分散行列の違いとはなんですか?
現在、共分散行列について調べているのですが、分散共分散行列ばかりが引っかかって混乱しています。

Aベストアンサー

両者は、全く同じものです。
共分散行列の対角要素が、
各確率変数の分散になっているため、
一部の流派では、分散共分散行列と呼ぶのです。
分散行列と呼んでしまう流派もあります。
もともと、分散と共分散を区別することが
つまらないことなのかもしれません。
ベクトルの内積を考えるとき、
a・b と a・a で用語を変えたりは
しませんよね。

Qエクセル(Excel) 納品書の作り方【画像修正版

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が見にくかったのでシート<CENTER></CENTER>だけにしました。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルでコピー!
そのまま最後の24行目までコピーしておきます。

F4セルには
=IF(COUNTBLANK(B4:E4),"",D4*E4)
という数式を入れ、F24までオートフィルでコピー!

これでB列に商品番号を入力すればSheet2のデータが反映され、
E列に数量を入力でF列に金額が表示されると思います。

最後に合計金額のF26セルは
=IF(COUNT(F4:F24),SUM(F4:F24),"")
手数料のF27セルは
=IF(F26="","",F26*0.2)

これで何とか形にならないでしょうか?

※ 振込金額の欄は不明ですので手を付けていません。

参考になりますかね?m(_ _)m

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルで...続きを読む

Q分散共分散行列

分散共分散行列

分散共分散行列とはどのような行列のことを言いますか?
どのようなものが分散共分散行列であり、どのようなものが分散共分散行列なり得ないのかわからないので教えてもらえますか?
たとえば

  
A=|1 1 |
  |1 -1|

B=|7 3|
|3 7|
の行列は分散共分散行列となりますか?
できたら理由も教えてください。

Aベストアンサー

x_i, x_jの共分散をγ_ij
γ_ij = E{(x_i-m_i)(x_j-m_j)}  (i≠j)

行列A=(γ_ij) を共分散行列といいます。
Aは、
(1)対称行列 γ_ij = γ_ji
(2)正値行列 Σ[1,k]Σ[1,k]γijx_ix_j >0 (x_i≠0,x_j≠0)
の性質をもちます。

ですから、(1),(2)を確かめればいいですね。
ご自分で確かめて下さい。

Qエクセル(Excel) 納品書の作り方【改めて】

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が貼り付けてあります。商品名は1番以外伏せさせていただいています。
くっつけてありますが、左側がシート1・右側がシート2です。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

画像がいまいちよく見えないのですが、納品書の項目は左から、No、商品番号、商品名、単価、数量、金額でいいのでしょうか(名前は多少違っていても意味があっていればもんだいないです)

でしたら、
C1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,2,FALSE))
D1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,3,FALSE))
E1セルは空白で
F1セルに=IF(D2="","",D2*E2)
といれて、C1からF1までをコピーしてその下の行にタテに貼り付ければ出来ますよ。
おそらくエラーが出たのは、コピーしたときにVLOOKUP関数の最初のセルの指定がずれてしまっているのでは無いかと思いますよ。     

Q共分散行列の求め方

数個のデータから共分散行列を生成するプログラムを実装しようと
考えています。そこで、次のようなプログラムを書きましたが、
結果があっているかがわかりません。共分散行列の生成方法に
詳しい方がおられましたら、どうか教えて頂きたいと思います。
共分散行列生成に用いた式は
Σ = E[(X-E[X])(X-E[X])^T]  です。
(Σ:共分散行列 X:データ E[X]:Xの平均 ^T:転置行列)


double xave = 0.0; //式のE[x]にあたる部分
double work [ ]; //データを入れる一次元配列(式のXにあたる)
double a[][]; //共分散行列を入れる配列

work[0] = 3; //5つのデータを格納
work[1] = -2;
work[2] = 5;
work[3] = -3;
work[4] = 2;

for(i = 0; i < N; i++){   //E[x]を求めるためにxaveに全要素合計を入れる
xave += work[i];
}
xave /= N;        //全要素合計をデータ数で割る
for(i = 0; i < N; i++){   //要素Xから平均を引く(X-E[x]にあたる)
work[i] -= xave;
}
for(i = 0; i < N; i++){
for(j = 0; j < N; j++){
a[i][j] = work[i]*work[j]/N;  //a[][]に値を格納 Nで割っているのは式の一番外側のEにあたる
}
}

<実行結果>

0.0 | 0.0 | 0.0 | 0.0 | 0.0
0.0 | 4.0 | -6.0 | 8.0 | -8.0
0.0 | -6.0 | 9.0 | -12.0 | 12.0
0.0 | 8.0 | -12.0 | 16.0 | -16.0
0.0 | -8.0 | 12.0 | -16.0 | 16.0

数個のデータから共分散行列を生成するプログラムを実装しようと
考えています。そこで、次のようなプログラムを書きましたが、
結果があっているかがわかりません。共分散行列の生成方法に
詳しい方がおられましたら、どうか教えて頂きたいと思います。
共分散行列生成に用いた式は
Σ = E[(X-E[X])(X-E[X])^T]  です。
(Σ:共分散行列 X:データ E[X]:Xの平均 ^T:転置行列)


double xave = 0.0; //式のE[x]にあたる部分
double work [ ]; //データを入れる一次元配列(式のXにあたる)
double a[][]; //...続きを読む

Aベストアンサー

> ・言語は一応Javaでやっています。

Javaでしたか、ずいぶん前にちょっと使ってみただけでしたので思いつきませんでした。

> Σ = E[(X-E[X])(X-E[X])^T] の式から、(X-E[X])を一列の列ベクトル、
> (X-E[X])^Tを一行の行ベクトルと考え、二つの行列の積を考えるとちょうど
> 正方行列になるので、これでいいかと思っていました。しかし、この考え方
> では間違っているとういことでしょうか。

はい、そうなります。
E[X]は列ベクトルとなりますが、Xの要素が異なる変数なので、それぞれの期待値が等しくなるとは限りません。
なので、

> for(i = 0; i < N; i++){   //要素Xから平均を引く(X-E[x]にあたる)
> work[i] -= xave;
> }

とするのはおかしいのです。
引くなら、work[i]の値を引くべきです。
(データ数が1個なので平均はwork[i]そのもの。その結果共分散行列は5×5の零行列になる)


> それならば、共分散行列を生成するには、二次元以上のデータが必要ということでしょうか。

例えば、下記のようなデータなら計算できます。

番号  変数1  変数2  変数3  変数4  変数5
 1    5     3     4     6    1
 2    …


上のような形式のデータなら変数ごとの分散と変数間の共分散を求めて、共分散行列を得ることが出来ます。

> ・言語は一応Javaでやっています。

Javaでしたか、ずいぶん前にちょっと使ってみただけでしたので思いつきませんでした。

> Σ = E[(X-E[X])(X-E[X])^T] の式から、(X-E[X])を一列の列ベクトル、
> (X-E[X])^Tを一行の行ベクトルと考え、二つの行列の積を考えるとちょうど
> 正方行列になるので、これでいいかと思っていました。しかし、この考え方
> では間違っているとういことでしょうか。

はい、そうなります。
E[X]は列ベクトルとなりますが、Xの要素が異なる変数なので、それぞれの期待値が等しくなると...続きを読む

Qエクセル2007でプルダウンで選んだものに反応

Excel2007でプルダウンで選んだものに反応して隣のセルが自動入力される方法(エクセル2007)
A1をプルダウンで「猫」「犬」から選べるようにし、「猫」を選んだ場合B1に自動に「111」が、「犬」を選んだ場合B1に自動に「222」と入力されるようにしたいです。
ご教授の程、宜しくお願いします。

Aベストアンサー

VLOOKUP関数での方法です。
(1)別シートに入力文字列と対応コード表を作成。(仮にSheet2のA:B列範囲で順不同)
(2)B1に=IF(COUNTIF(Sheet2!A:A,A1),VLOOKUP(A1,Sheet2!A:B,2FALSE),"")を設定
   入力文字列が存在しない場合は空白としています。

Q共分散行列の固有値・固有ベクトルの行列

以下のようなデータを用いて、共分散行列を生成するとします。
(各No.にはそれぞれx1~x5の5つのデータ)
x1 x2 x3 x4 x5
No.1 [2 4 5 2 1]
No.2 [3 10 8 7 9]
No.3 [11 3 2 1 6]

すると、共分散行列は3×3の正方行列になり、その固有値も3つ求まりますよね。
しかし、固有ベクトルに関してはデータがx1,x2,..,x5と5次元で考えているので、
ひとつの固有値に対して5つの成分を持つ固有ベクトルが求まりますよね。
よって、共分散行列の固有値行列は必ず正方行列になりますが、固有ベクトルの
行列は上の例の場合なら5×3行列(列は対応する固有値の数、行はベクトルの成分の数)となり、
必ずしも正方行列にはなりませんよね?そのあたりを教えて頂きたいと思います。
よろしくお願いします。

Aベストアンサー

共分散行列のik成分をA_ikと書くと,
A_ik=(1/N)Σ[j=1 to N](xij-μi)(xkj-μk)
となります。Nはデータ件数,xijはj件目のデータにおける第i変数の値,
jはデータ件数を走る添え字,
i,kは変数次元を走る添え字で,μkは第k変数の平均値
を表します。
(例で言うと,Nは生徒の人数,jは出席番号,μ1は英語の平均点,μ2は国語の平均点etc)

>共分散行列 = E[(x-E[x])(x-E[x])^t] ^tは転置 Eは期待値(平均値)
の表記に合わせると,xは列ベクトルで
2   3    11
4   10   3
5とか,8とか,2
2   7    1
1   9    6
です。
期待値をとるE{}演算は,データ件数について加えて平均することになります。

期待値の操作を行列でやりたいとすると,

>No.1の行の平均をμ1, No.2の行の平均をμ2, No.3の行の平均をμ3とします。)
>[ 2-μ1 3-μ2 11-μ3 ]
>[ 4-μ1 10-μ2 3-μ3 ]
>[ 5-μ1 8-μ2 2-μ3 ]
>[ 2-μ1 7-μ2 1-μ3 ]
>[ 1-μ1 9-μ2 6-μ3 ]
はすこし違います。平均値は,
データ件数個つくるの(生徒毎の平均)ではなく,
変数次元個つくります(科目毎の平均)。

μ1=(2+3+11)/3 第1科目(英語?)の平均点
μ2=(4+10+3)/3 第2科目(国語?)の平均点
μ3=(5+8+2)/3 第3科目の平均点
μ4=(2+7+1)/3 第4科目の平均点
μ5=(1+9+6)/3 第5科目の平均点
とした上で,
[ 2-μ1 3-μ1 11-μ1 ]
[ 4-μ2 10-μ2 3-μ2 ]
[ 5-μ3 8-μ3 2-μ3 ]
[ 2-μ4 7-μ4 1-μ4 ]
[ 1-μ5 9-μ5 6-μ5 ]
すなわち,縦に科目番号,横に生徒の出席番号で並べた行列をつくり,これを左から
転置した行列を右から掛け算します。
結果として,変数次元×変数次元の正方行列ができ,その各要素はデータ件数分を平均した値です。

共分散行列のik成分をA_ikと書くと,
A_ik=(1/N)Σ[j=1 to N](xij-μi)(xkj-μk)
となります。Nはデータ件数,xijはj件目のデータにおける第i変数の値,
jはデータ件数を走る添え字,
i,kは変数次元を走る添え字で,μkは第k変数の平均値
を表します。
(例で言うと,Nは生徒の人数,jは出席番号,μ1は英語の平均点,μ2は国語の平均点etc)

>共分散行列 = E[(x-E[x])(x-E[x])^t] ^tは転置 Eは期待値(平均値)
の表記に合わせると,xは列ベクトルで
2   3    11
4   10   3
5とか,8とか,2
2   7  ...続きを読む


人気Q&Aランキング

おすすめ情報