投資

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

こんにちは、タマです。

皆さんはこんなヒートマップを見たことがありますか?

こちらはfinvizのS&P500MAPです。

今回はこのようなヒートマップをスプレッドシートで再現しよう!というお話です。

より精度の高いヒートマップをスプレッドシートで再現しました。

こちらの記事よりスプレッドシートのデータを公開していますが、下記条件を満たしている方のみの限定公開記事となっています。

  • 私(@p_boy_tama)のTwitterアカウントをフォローする
  • トミィさん(@toushi_tommy)のTwitterアカウントをフォローする

興味がある方は私のTwitterアカウントへお問い合わせください。

ヒートマップの作り方

先に完成図を見せると下図のようになります。

今回はスプレッドシートのツリーマップを使ってこれを実現します。

スプレッドシートの使い方がわからない方は別途調べてください。

※前提条件

まず前提条件からです。

  1. 各要素には「ティッカー」「株価」「株価変動率」を表示する
  2. 各要素のサイズは統一する
  3. 各要素の色は前取引日の終値からの株価の変動率を基準とする

通常のS&P500MAPは時価総額の大きさによって各要素のサイズが変わりますが、今回は見やすくするために全て同じサイズにしています。本来のS&P500MAPのようにしたい場合は時価総額を基準にしたヒートマップにしましょう。

また、表示する色は株価の変動率を基準にしています。しかし、ここで一つ注意しなければいけないことがありまして、スプレッドシートの場合は中間の色が中央値になってしまいます。

詳細は後述しますが、スプレッドシートのヒートマップは株価変動率を基準としたグラフとしてあまり適さないので、そこは注意してください。




①データの準備

ではヒートマップ用のデータを準備します。

上図のようなデータが準備できればヒートマップは簡単に作成できます。

今回は色別に解説するので、図を見ながら適宜調整してください。

A列:灰色

まずはA列の灰色の箇所です。

こちらはティッカーシンボルを入力するエリアになります。

今回のデータではA18から順にティッカーシンボルが入力されていますが、あくまでも参照用のデータなので関数は使っていません。

自分が表示させたい銘柄のティッカーシンボルを同じように入力してください。




B列:黄色、赤色

こちらはヒートマップを作成するときの階層ごとの名前になります。

今回のグラフは次のような階層構造になっています。

・ヒートマップ
 ・GAFAM
・GOOGL
・AAPL
・FB
・AMZN
・MSFT
 ・FANG+
・NVDA
・TSLA
・(以降続く)

よって、全体を「ヒートマップ」、各階層の名前を「GAFAM」「FANG+」「半導体」などで表記しています。

ヒートマップでは上図のように対応しています。




B列:青色

B列の青色の箇所は「ティッカーシンボル」「株価」「株価変動率」が3行に分かれて表示されています。

これは次のような関数を使って表現しています。

=A18 & char(10) & GOOGLEFINANCE(A18,“price”) & char(10) & GOOGLEFINANCE(A18,“changepct”) & “%”

少し分かりづらいですが、使っているのは大きく4つの関数です。

  1. A18:ティッカーシンボル
  2. GOOGLEFINANCE(A18,”price”):株価
  3. GOOGLEFINANCE(A18.”changepct”):株価変動率
  4. char(10):改行

①〜③は「ティッカーシンボル」「株価」「株価変動率」を表示するための関数です。A18はA列の灰色箇所で入力したティッカーシンボルを参照しているだけなので、各自で自分のセルに合わせて調整してください。

一番の注意点は「char(10)」と「&」です。

「char(10)」は改行を表す関数なので、それぞれの関数と「&」を組み合わせることで3行に分けて表示することができます。

したがって、日本語で表すと下記のようになります。

ティッカー & [改行] & 株価 & [改行] & 株価変動率 & “%”

ティッカーシンボル自体はA列から参照できるので、元となる関数さえ入力してしまえば自動で表示されます。

なお、リアルタイムの株価ではなく、前日の終値を表示させたい場合は次の関数を使用してください。

=GOOGLEFINANCE(A18,“closeyest”)




C列:黄色、赤色

こちらは階層と各要素を対応させるための列です。

黄色はそれぞれ階層を表す名前なので全てに「ヒートマップ」と入力しています。

そして赤色はそれぞれの要素が属する階層名を入力します。

例えばGOOGLの場合は「GAFAM」、NVDAの場合は「FANG+」のような感じです。

各自のヒートマップに合わせて割り当ててください。

D列:紫色

こちらはサイズを決める列です。

今回は全てのサイズを同じにするために「1」を入力しています。

また、2行目(D2)にはサイズの合計値を入力していますが、これ自体は特に意味はありません。ただし、何らかの数値を入力しないと親要素として認識してくれないみたいなので、何かしらの数値は入れてください。

もし時価総額ごとにサイズを変えたい場合は、下記関数を使用してください。

=GOOGLEFINANCE(A18,“marketcap”)

なお、この時価総額の関数はあまり精度がよろしくないようです。




E列:緑色

こちらは株価変動率を表しています。

=GOOGLEFINANCE(A18,“changepct”)

関数は先ほども紹介したものです。

今回のヒートマップはこの株価変動率に合わせて色が変化します。

②ヒートマップを作る

ここまでで準備したデータを用いてヒートマップを作成したいと思います。

今回はA1からデータが入っていますが、A列、1行目は使用しないのでB2からE列の下までを選択してグラフを作成してください。




ヒートマップを選択する

単にグラフを作成すると棒グラフが表示されるので、グラフの種類を「ツリーマップ」にしましょう。

サイズと色の範囲を変更する

ツリーマップに変えた状態だとサイズと色の選択範囲が間違っている可能性があります。

今回はサイズをD列、色をE列で指定しているのでそれに合わせて変更してください。
※本記事の通りに作成した場合は変更する必要はないと思います。




色を変更する

ここまでの設定だと上図のような微妙なヒートマップが出来上がります。これだとダサいので色の調整を行いましょう。

グラフの「設定」の右隣にある「カスタマイズ」から、「ツリーマップ」を選択します。

こちらから最大値の色やその基準値を設定できます。

私の場合は下記のように変更しました。

  • 最小値:-3
  • 最大値:3
  • 最小値の色:赤
  • 最大値の色:緑
  • 中央値の色:暗めの灰色

これに関してはお好みで調整してください。




最初の方にも触れましたが、スプレッドシートで指定できるのは最大値と最小値と”中央値”です。

今回は株価変動率を色の基準に使っていますが、○○ショックのような大暴落が来て全銘柄がマイナスになった場合、当然ながら中央値もマイナスになるため色の表示がおかしくなります。

今回も使用したデータに偏りがあったので、黄色で示した銘柄はマイナスにもかかわらず緑が混じった色になっていることがわかるかと思います。

S&P500MAPのような綺麗なヒートマップにはなりませんので、この点はご注意ください。

以上で、ヒートマップの作成は終了です。

必要に応じて適宜設定してください。

各要素のサイズは自動調整されるので、グラフそのもののサイズを変えながらちょうど良いサイズを見つけましょう。




まとめ

今回はスプレッドシートを用いてS&P500MAPのようなヒートマップを作成する方法を解説しました。

スプレッドシートは今回のようなヒートマップにはあまり適しませんが、上手く調整できれば視覚的にわかりやすいグラフが作成できるので皆さんも試してみてください。

この記事はトミィ@NISA芸人さん(@toushi_tommy)のツイートをきっかけに作成しました。

トミィさんのヒートマップのほうが正確だと思うので、情報収集を目的とする場合はトミィさんのツイートをウォッチしましょう。

ABOUT ME
タマ
【学費自腹で借金640万円】貧乏人が経済的自由を目指す | 30歳までに資産2,000万円 | 社会人2年目 | 23歳 | 一人暮らし | つみたてNISA | 投資信託 | 米国株 | ブログ | 奨学金