わたくし千鳥足、週に1回および月に1回リスク資産の評価額を整理しているのですが、銘柄毎の騰落の影響度合いを視覚で把握したいなあとかねがね思っておりました。
EXCELの「ツリーマップ」と簡単なマクロを使って、いわゆる「ヒートマップ」を作成してみましたよ。
参考にするS&P500のヒートマップ
作りたいのはこんなものです。箱の大きさが時価総額を、色が騰落率をあらわしています。
これ超 COOL ですよね。こんな感じで我が家のリスク資産の増減を可視化したいと思うのです。
我が家のリスク資産の増減をヒートマップで表してみた
EXCELのチャートの機能とVBAを使って、我が家のリスク資産をヒートマップにしてみました。2020年12月31日～2021年1月23日の増減率によりプラスは緑、マイナスは赤を基調とした配色にしています。
見栄えはS&P500のオリジナルに敵いませんが「可視化する」と云う目的は果たせてそうなので良しとします。
この期間で見ると、[TSLA]テスラ や [5108]ブリヂストン の増加率が強いこと、[V]ビザ が割と強めに下落していることが判ります。
投資資産のヒートマップを作成するEXCELツール
需要があるかは謎なのですけれども、ヒートマップを作成するEXCELツールを貼っておきます。
ヒートマップ作成ツールをダウンロードする（2021年1月24日版）
EXCELのグラフ「ツリーマップ」の機能を使用しています。EXCEL2016以降で有効なようです。動作確認は Office 365 の EXCEL で行っています。
2019年8月12日版に対し以下の変更を加えています
2019年8月12日版からの変更点
- ヒートマップの背景色を指定できるようにしました。
- サンプルのシートを3つ追加しました
ツールの利用手順
利用手順を簡単に記載します。
マクロ付きのEXCELなのでそのままアップロードすることができないので、ZIPに固めてあります。ダウンロードしたら解凍してください。ファイルを開くとセキュリティの警告が出ます。ヒートマップを更新するマクロをONにするために許可してください。
- 種別、銘柄、評価額、％の列を入力してください。
- B列「種別」がヒートマップ上のグループになります。Ｂ列「種別」を編集した場合には同じ「種別」が連続して並ぶよう並び替えてください。行が離れてしまうと別のグループと見做されてしまいます。
- C列「銘柄」がヒートマップの各要素の名称になります。
- D列・E列は数式が入っています。いじらないでください。以外の値・数式は任意で書き換えてください。
- F列「評価額」でヒートマップ上の要素の大きさ、G列「％」で色が決まります。
- G列「％」などの算出に当たり必要な場合にはH列以降に列を挿入し、算出してください。
- 閾値、および背景色（高・中・低）を指定してください。
- 閾値に任意の値を入れてください。
- 高・中・低のセルに任意の背景色を設定してください。
- 各要素の背景色は、閾値をMAXとして、高～中～低の範囲で変化します。
- 「ヒートマップの更新」ボタンをクリックしてください。ヒートマップの配色が決まります。
基準額と評価額の更新の仕方
ご自身の資産は、みなさんそれぞれの方法で工夫して管理されていると思うため、このツールでは敢えて手入力としています。ご自身が管理されている数値を転記してください。EXCELで管理されている場合には、シートを複写する等して1つのファイルにまとめて数式で参照するなどご自由に改変ください。
前述のとおり、テーブルのG列「％」の数式は自由に変えて問題ありませんし、H列以降に列をガンガン追加しても問題ありません。
ちなみにわたくし千鳥足は、資産の内訳はマネーフォワードの「資産内訳」のデータを取得し、そのまま利用しています。マネーフォワードの「資産内訳」から現在の評価額をぽんっと取得するブックマークレットも公開しているので貼っておきます。
【ブックマークレット】マネフォワPON! マネーフォワードME 資産内訳の明細をぽんっ♪と取得する
また、保有銘柄の株価を記録して騰落率を、保有株数を入力して評価額を算出する場合には、米国株・国内株の株価取得に対応した以下のツールをよろしければご利用ください。
【EXCELで株価取得】国内株・米国株の株価を銘柄・日付指定で取得＆記録するツール
ちなみに今日の「評価額」は、後日比較対象となる「基準額」として使う可能性が大です。後日再利用できるようにシートを複写する等して、記録しておくと良いですね。
VBAマクロでやっていること
汚いVBAのコードが付いています。
ヒートマップの各項目のデータラベルから増減率の部分を除いた銘柄名のみ取り出して、C列「銘柄」をVLOOKUPしてG列「％」を取得し配色を決めています。
使用例として以下のシートを残してあります。ご覧ください。
- サンプル（評価額前年比）
- サンプル（評価損益率）
- サンプル（配当利回り）
