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

GoogleスプレッドシートS&P500Map風を再現分析ツール

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

S&P500Map

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

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

こんな感じです。

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

ヒートマップの作成手順

データを用意する

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

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

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

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

  • 保有比率
    • 株式・ETF
      • VDC
      • VYM
      • VGT
    • 債券
      • TLT
      • VGIT
      • AGG
      • GLDM

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を入力します。これで本家のヒートマップと似たような設定になります。更に色を以下のように設定すると見た目も近くなります。

最小値の色#F63538
中央値の色#424654
最大値の色#32CC5B
S&P500Map風

これで完成です。

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

ただ、これ使うかな(笑)

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

コメント