![mochikabu3](https://images.ctfassets.net/efl7hk6qad43/M7y9uYJRYU5QfXrvXumLM/76d02e5cad8a5bda62a5c49dc4d60c5e/mochikabu3.jpg?w=1500&h=1000&fl=progressive&q=50&fm=jpg)
月別騰落率の見える化(データ抽出からグラフ化まで)
持株会の記事で特定銘柄の月別騰落率がポイントになると記事にしましたが、ここではそのデータの抽出と加工、そしてグラフ化し、データの意味の見える化についてまとめます。
1.データ抽出
まずは調べたい銘柄の月別データの抽出をします。
今回はhttps://jp.investing.com/のHPを利用しました。
1-1.検索バーで検索したい会社を検索。今回は例として「日経平均」で検索しました。
![monthly1](https://images.ctfassets.net/efl7hk6qad43/1OQYg8xyTsBCgMJ5r3mP42/e3073aefcbdd26fae0f1dc6685590a7f/monthly1.png?w=1099&h=180&q=50&fm=png)
1-2.HP内にある「過去のデータ」をクリック。
![monthly2](https://images.ctfassets.net/efl7hk6qad43/3dn0y3tE4hsqGKjoT5B9Kv/c3ec5aa32d53a52082ac2d8a92d75523/monthly2.png?w=1083&h=415&q=50&fm=png)
1-3.時間枠から「月間」を選択、また抽出したい期間を選択する。
![monthly3](https://images.ctfassets.net/efl7hk6qad43/3cPu6Q7edjVi88eRZL0rzL/6e24c2defd5927f0ca3d248afc128b08/monthly3.png?w=1092&h=581&q=50&fm=png)
1-4.抽出されたら「データをダウンロードする」をクリックするとデータがダウンロードされます。抽出後のデータをご自身が作業されるスプレッドシートに転写できたら、データ抽出は完了となります。
なお、このHPの変化率とあるのが騰落率になります。数式は以下の通りです。
変化率(%)=(after – before) ÷ before ×100
株価でいえば
株価騰落率(%)=(当日の株価-前日の株価)÷前日の株価×100
例えば前日株価が90円、当日株価が100円ならば、(90-100)÷90=▲11%というわけです。
2. グラフ化(ヒストグラムのひな型)
抽出したデータの変化率の箇所を利用すればそのままヒストグラム化することが可能です。例えばGoogleのスプレッドシートで作成したヒストグラムは以下のようになりました。
![monthly4](https://images.ctfassets.net/efl7hk6qad43/3ZvposFsPEm64USbdfnkq2/8138ee6997f89242af0d8374c1abc489/monthly4.png?w=1200&h=742&q=50&fm=png)
グラフ化だけだったらこれで十分です…が、横軸が「データの個数」であり、割合ではないのでわかりにくいし、縦軸のデータの幅も「端数ばかり」で使いにくいと思います。
0%~3%にデータ個数が一番多くて55個くらいある?とわかったところで、それで?という感じですね…
マイクロソフトのExcelのヒストグラムでも同様の結果になります。
3.データ加工
ひな形のヒストグラムのグラフ化では、横軸が割合じゃないし、縦軸の幅も使いにくい課題があります。それを解決するためにはデータ加工が必要です。まずは抽出した生データを用意します。
注意点:資料の拡張子に気を付けてください。csvファイルでダウンロードされたファイルのまま作業し上書き保存しても、csvの拡張子では保存できず、データが消えてしまう可能性があります。そのまま利用される方は「名前を付けて保存」からファイルの種類(拡張子)を利用可能なもの(xls等)に変更してから利用してください。
3-1.把握したい幅に含まれるデータの数をCOUNTIF関数で数えます。
COUNTIF(範囲, 条件)
例えばG列目に変化率のデータが入っているとして、▲10%以下のデータ個数を数えたい場合は、以下の数式になります。
COUNTIF($G:$G,"<=-10%") …A
$を行列に設定することで絶対参照にすることで、今後他のセルにコピーするとき、ずれなくてよいので便利です。
次に▲8%以下を出したければ、
COUNTIF($G:$G,"<=-8%") …B
で計算をさせて、-10%から-8%の間の数字はB-Aとすれば出てきます。
条件を複数選択できるCOUNTIFS関数も存在します。使い方はCOUNTIFとほぼ同じで、COUNTIF(範囲1,条件1, 範囲2,条件2…)と続いていくだけです。
COUNTIFS($G:$G, ">-10%",$G:$G, "<=-8%")
と設定すれば、B-Aと同じ結果になります。
ただ、抽出したデータ次第ですが、1つずつ、-10%、-8%、-6%…と入力するのは面倒だし、後から幅を変えたくなるかもしれません。入力誤りも発生するかもしれないので、数式を利用することを強くお勧めします。
条件入力箇所で、“<=-10%”と入力する代わりに、”<=”&「▲10%と入力したセル」として、&関数を使うことで符号の直入力と、セルのデータを合わせることができます。
数式イメージはグラフの通りです。K列の%は直打ちで入力が必要ですが、これは皆さんが抽出したデータの幅をどうしたいか次第で調整してください。グラフでは2%刻みのイメージになります。
![monthly5](https://images.ctfassets.net/efl7hk6qad43/3IjbxQ8iIVSnB6kyr6lvIr/b801ef95654f54045a050fc4e0b43c53/monthly5.png?w=971&h=652&q=50&fm=png)
上記数式のときのデータ結果は以下のようになります。
![monthly6](https://images.ctfassets.net/efl7hk6qad43/6VVEUUQq2KvRjx3lulvU8n/8edf44d25a8f35f7eb203033dbaa0b33/monthly6.png?w=437&h=518&q=50&fm=png)
3-2.求めたい幅でのデータ個数(頻度)のデータを元に割合を求める。
上記で求めたデータ(上図のJ列相当)を元に以下のような表を作成します。
![monthly7](https://images.ctfassets.net/efl7hk6qad43/4xx0WCn6oJCHzhCxEzU5g7/bd02d258700c9f128f1e7119da66d0f9/monthly7.png?w=589&h=528&q=50&fm=png)
4.グラフ化(縦棒グラフのひな型)
M列の変化率とO列の割合を選択し縦棒グラフを作成すればグラフ化まであともう少しです。
![monthly8](https://images.ctfassets.net/efl7hk6qad43/3kblacYIPRI8jvmXn1eR5g/af6189b48cae68d54376dffefd73ba80/monthly8.png?w=325&h=518&q=50&fm=png)
上のデータを元にGoogleのスプレッドシートで出来上がったのが下のようになります。
![monthly9](https://images.ctfassets.net/efl7hk6qad43/1u9pbNsHlUf8nK5vmQ6ipN/a57a9d328abfb280fa14d089a4b13798/monthly9.png?w=1200&h=742&q=50&fm=png)
ヒストグラムのひな型で作ったものより見やすいグラフとわかりやすいデータが出来上がったと思います。縦棒の幅を太く調整すれば、より従来 のヒストグラムのような見栄えになります。
この縦棒グラフを利用したヒストグラムでは、既存ヒストグラムでは表現できなかった
・割合でデータを出せる
・表現したい幅でデータを出せる
という2つのメリットがあるのがポイントです。
これにより、1月後株価が▲10%以下になったのは3%の割合だった、と表現することができるようになりました。
これで皆さんが抽出したいデータを表現できるようになりましたら幸いです。
ご一読、ありがとうございました。