S＆P500MapをGoogleスプレッドシートで再現する方法

米国株投資をしている人であれば「S＆P500Map」を見たことあるのではないでしょうか？こんな感じのやつ。

各銘柄の構成比率と騰落率を一眼で把握できるため便利です。

「このヒートマップを自身が保有している米国株で表現できないかなぁ？」と考えていたら、Googleスプレッドシートのツリーマップでできることがわかりました。

こんな感じです。

早速、作り方を説明していきます。

ヒートマップの作成手順

データを用意する

あるとむ様の「【Googleスプレッドシート】ツリーマップとヒートマップによる保有銘柄比率と株価騰落率の可視化」を参考させていただきました。

用意するデータは以下のような感じです。

A列(1列目)とB列(2列目)：階層の名前を決める

上記の表は以下の階層構造を表しています

C列(3列目)：ツリーマップの用の数値

各銘柄の保有額比率を「10行目の保有額」から算出します。

「6行目の保有株数」と「9行目の株価」を用いて「10行目の保有額」を算出します。

ちなみに現在の株価は「GOOGLEFINANCE関数」を用いて自動取得することができます。

=GOOGLEFINANCE(ティッカーシンボル名,"price")

上記のティッカーシンボルは1行目を参照します。

VDCの現在株価を取得する場合は以下のようになります（ご自身が作成したファイルのセルを指定して下さい）。

=GOOGLEFINANCE(A32,"price")

D列(4列目)：ヒートマップ用の数値

騰落率を「8行目の前日保有額」と「10行目の現在保有額」から算出します。

前日の株価も「GOOGLEFINANCE関数」で取得することができます。

=GOOGLEFINANCE(ティッカーシンボル,"closeyest")

現在の株価でpriceと記載したところをcloseyestと変えるだけです、簡単ですね。

5行目に騰落率を「10行目の現在保有額÷8行目の前日保有額」で算出します。

そして、4行目に5行目を100倍した数値を入力して下さい。S&P500Mapの色の幅は最大+3%、最小-3%となっているのですが、それを表現するために必要な作業になります。

これでデータは完成です。

ツリーマップ（ヒートマップ）を作成する

セルA28〜D45を選択し、挿入＞グラフを選択します。

画面右のグラフの種類から「ツリーマップ（下段の真ん中）」を選択します。

ツリーマップの表示に変わりました。

これでヒートマップが完成です。

見た目を調整する

グラフをクリックすると右上に「3つの点」が表示されますので、クリックしてグラフを編集をクリック。そして、カスタマイズをクリック。

最小値に-3、最大値に3を入力します。これで本家のヒートマップと似たような設定になります。更に色を以下のように設定すると見た目も近くなります。

これで完成です。

Googleスプレッドシートでは「文字の大きさが自動で可変する点」、「騰落率を表示する点」は再現できないようですが、それっぽい雰囲気は表現できているかなと。

ただ、これ使うかな（笑）

普段からGoogleスプレッドシートを使って、資産管理をしている人であれば、作ってみても良いかなという感じのネタでした。