【EXCELでヒートマップ】リスク資産の増減率をヒートマップで可視化するツール

週に1回および月に1回リスク資産の評価額を整理しているのですが、銘柄毎の騰落の影響度合いを視覚で把握したいなあとかねがね思っておりました。

EXCELの「ツリーマップ」と簡単なマクロを使って、いわゆる「ヒートマップ」を作成してみましたよ。

参考にするS&P500のヒートマップ

作りたいのはこんなものです。箱の大きさが時価総額を、色が騰落率をあらわしています。

S&P 500 Map

これ超 COOL ですよね。こんな感じで我が家のリスク資産の増減を可視化したいと思うのです。

我が家のリスク資産の増減をヒートマップで表してみた

EXCELのチャートの機能とVBAを使って、我が家のリスク資産をヒートマップにしてみました。2019年7月31日～8月10日の増減率によりプラスは緑、マイナスは赤を基調とした配色にしています。

見栄えはS&P500のオリジナルに敵いませんが「可視化する」と云う目的は果たせてそうなので良しとします。

ドル円の下落によってレバレッジの効いたFXの評価額が激減していること、[6464]ツバキ・ナカシマ の急落が痛かったこと、[6197]ソラスト および [9873]日本KFC の上昇が素晴らしかったこと、現金を60万円ほど注入したもののまだ何も買えていないこと等、ヒートマップに添えるべき説明は2019年8月10日時点のリスク資産評価額としてすでに記事にしているとおりです。

2019年8月10日(土)時点のリスク資産評価額、2週間で76万投入したのに101万減ってるんです…という修行の夏。

ヒートマップを作成するEXCELツール

あんまりお見せできるような代物ではないし需要があるか謎なのですけれども、ヒートマップを作成するEXCELツールを貼っておきます。

ヒートマップ作成ツールをダウンロードする（2019年8月12日版）

EXCELのグラフ「ツリーマップ」の機能を使用しています。EXCEL2016以降で有効なようです。動作確認は Office 365 の EXCEL で行っています。

ツールの利用手順

利用手順を簡単に記載します。

マクロ付きのEXCELなのでそのままアップロードすることができないので、ZIPに固めてあります。ダウンロードしたら解凍してください。

1. ファイルを開くとセキュリティの警告が出ます。ヒートマップを更新するマクロをONにするために許可してください。
2. B列～H列を編集します。B列「種別」がヒートマップ上のグループになります。D列・E列・H列は数式が入っています。いじらないでください。
3. 「ヒートマップの更新」ボタンをクリックしてください。基準額に対する評価額の増減率によって、ヒートマップの配色が決まります。

もしB列「種別」を編集した場合には、同じ「種別」が連続して並ぶよう並び替えてください。行が離れてしまうと別のグループと見做されてしまいます。

増減率の分布によって「閾値」を変更してヒートマップの配色を調整してください。「閾値」を超える振れ幅の増減率は、一律に最大振れ幅の配色になります。

基準額と評価額の更新の仕方

現状手入力です。笑　ご自身が管理されている内容から転記してください。

以前作成した株価取得ツールと連動させようかと思いましたが、投資信託やドル資産の円換算とかやらなくちゃいけないこと盛り沢山すぎてやめました。

実際には、マネーフォワードの「資産内訳」からデータを貼り付けて編集しています。マネーフォワードの「資産内訳」の内容は接続している証券会社・銀行等の仕様によって左右されるので一律に変換できないのですよね。なのでツールとしては手入力のままとしました。

今日の「評価額」は、後日比較対象となる「基準額」として使う可能性が大です。後日再利用できるように記録しておくと良いですね。

VBAマクロでやっていること

汚いVBAのコードが付いています。

ヒートマップの各項目のデータラベルから増減率の部分を除いた銘柄名のみ取り出して、C列をVLOOKUPしてH列の増減率を取得し配色を決めています。

こう書いていてデータラベルの増減率をそのまま使ったほうが賢いんじゃ…と気付きましたが、もう面倒くさいのでこのままでいきますｗ

今後、週に1回および月に1回リスク資産の評価額を整理する際に使用したいと思います。