簿記2級から始める証券アナリスト試験：ゴミ投資家のためのインターネット投資術入門「05 Excelでソニーの「リスク」を散布図にする」

01 Excelでつくる株式市場
02 Excelでつくるロウソク足の日計表
03 Excelでロウソク足をつくる
04 Excelで株価の移動平均を計算する
05 Excelでソニーの「リスク」を散布図にする
06 Excelでソニーとソフトバンク（ソフトバンクグループ）のリスクを比較する
07 Excelで収益率（リターン）の平均値を求める
08 Excelで分散を計算する
09 Excelでソニー、富士通、イトーヨーカ堂（セブン＆アイ・ホールディングス）の株価を比較する
10 Excelで2つの銘柄の相関係数を計算する
11 Excelで3つの銘柄の共分散と標準偏差を計算する
12 Excelでポートフォリオを作成する
13 Excelでポートフォリオの最適な構成比率を求める
14 Excelでベータを計測する
15 Excelで個別銘柄のαとβを求める
16 Excelで割引債の利回りと価格を計算する
17 Excelで利付債の利回りと価格を計算する
18 Excelで投資利回りを比較する
19 Excelで現在価値を割り引いて利回りを計算する
20 Excelで債券の実効利回りを計算する
21 Excelでスポット・レートから価格を求める
22 Excelでヂュレーションを計算する
23 Excelで修正ヂュレーションを計算する

今回は「Excelでソニーの「リスク」を散布図にする」について紹介したいと思います。

データは前回使ったソニー(6758)のデータをそのまま流用します。

I列の見出しに「前日比」と入力し、またI列はパーセント表示に設定します。そしてI3セルに「=(今日の株価-昨日の株価)/昨日の株価」という計算式を入力します。I3セルの場合、今日の株価はE3セル、昨日の株価はE2セルになります。

I4セルから下は、I3セルの式をコピーします。また、I2セルにはゼロを入力します。

I列を指定し、散布図を選びます。

ソニーの散布図ができました。

同様の手順でソフトバンクグループの散布図も作ってみます。ですがソフトバンクグループの場合、データにnullと入っている列があったり

株価が2倍になったりしている行があるため、それを取り除く必要があります。

ざっと見た感じ、日本の祝祭日が関係しているみたいなのですが、とりあえずはおおまかな傾向が読み取れればいいので、nullになっている行と終値が2倍になっている行を削除していきます（ソニーにも出来高がゼロの日はありますが、株価が2倍になったりしてないのでそのままにしてあります）。その結果、ソフトバンクグループの散布図として、以下のようなものが得られました。

どちらもプラスマイナス5％以内に集中しているようですが、5％を超えている部分を見ると、ソフトバンクグループの方がぶれ幅が大きいように思います。東京電力ホールディングス(9501)についても作ってみました。縦軸のスケールがマイナス40％からプラス40％になっているので注意です（ソニー、ソフトバンクグループはマイナス25％からプラス25％）。