みなさんこんにちは。しうです。
皆さんは自分が運用している投資信託や日本株はどのように管理していますか?
大体の人は証券会社のホームページで月一で確認するとか、家計簿アプリのグラフで確認するぐらいかと思います。
私も最近まではそうでした。しかしエクセルなど手元にデータが残せる形で自分の欲しい情報を残しておきたいという思いが強くなり、様々なホームページを調べては公開しているものを使ってみたりしました。
しかしやはり、他の人の作成したエクセルは自分用に最適化されているわけではないため、自分では使わない機能がついていたり、どう入力するのかが分かりづらかったりすることが多かったです。
本記事でも一応私の作成したエクセルを載せておきますが、一番は自分がどういう考えでこのエクセルを作成したが重要だと思うので、作成の過程についても書いておこうと思います。
作成したエクセル
私の拙作ですが使っていただければ…
注意ですが、まず本エクセルには、投資信託と日本株用のシートしか入っていません。
つまり米国株やETFに関して記載したい場合は自分で作成する必要があります。
作らなかった理由は単純に私が保有していない＋保有していないモノを実装しても複雑になってしまうだけ
と思ったからです。
下に自分なりの作成方法を説明するので参考にしてください。
また、なるべくカット＆ペーストでデータを移動させないでください。関数が崩れて参照がおかしくなる可能性があります。
各ページの解説
本エクセルは具体的に以下のシートに分かれています。
- 資産まとめ
- 日本株
- 投資信託
- 投資信託 推移
日本株
入力するのは薄緑の網掛け部分です。
業種については他のグラフに反映する機能は今のところありませんが、とりあえず私が分類しておきたいので付けました。
業種のセルについては以下のようにプルダウン形式になっており、東証33業種から選択するようにしています。
プルダウンリストの作り方は上部のタブから「データ」→「データ入力の規則」を選択します。
そうすると御覧のようなウィンドウが表示されるのでリストを選びます。
リストで表示される文字列は元の値で決めます。
私のエクセルシートの場合、下のような感じで脇の方に文字列を作成してそれを白文字で隠しているだけです。
保有株数や平均約定単価・現在の株価はおそらく証券会社の画面から確認できると思うのでそれを持ってくれば良いです。
あとはそれを計算させてます。
投資金額 = 平均約定単価 × 保有株数
評価額 ＝ 現在の株価 × 保有株数
損益額 ＝ 評価額 ー 投資金額
で計算しています。
損益額については－の値は▲付で表示させるようにしています。
この設定は右クリックで「セルの書式設定」を選んだあと、分類を数値にして負の表示形式を変更することで可能です。
またこの行に関しては負の値であれば薄赤背景の赤文字で正の値であれば薄青背景の青文字で表示されるようにしています。
やり方は右上の条件付き書式から「新しいルール」を選択して以下のようなウィンドウを表示させます。
ここで一番下のルールを選択して条件式を記入していきます。
ここで注意したいのはセルを選択した際、上記のようにセル値に＄マークがついた絶対参照となっているのでこのままだと拡張した際に最初のセルを参考に色分けしてしまいます。
この値を相対参照になるように2つの＄マークを外しておく必要があります。
最後に「書式」から自分の好きなように色や背景をいじればOKです。
この操作を０＞と＞0の両方定義しておきます。
最後は右側部分についてですが、まず上側の表はSUM関数で纏めただけなので省略します。
下のグラフは評価額を横軸に、損益率を縦軸にした投資銘柄のコスパを表すグラフになっています。
右下に行けば行くほどお金をかけているのに損失が出ているコスパの悪い銘柄、
右上に行けば行くほどお金をかけていてそれでいて利益が多いコスパの良い銘柄となっています。
例として私の100株保有銘柄を示していますが、イオンの損益が目立ちますね…
投資信託
投資信託のシートに関しても基本的には日本株と同じです。
薄緑の網掛け部分を入力します。
一つ違うのが投資信託では投資金額や評価額の計算方法が少し違います。
みなさんは「口数」「平均約定単価(又は基準価格)」「投資金額(又は評価額)」にどのような関係があるか知っていますか?
一般的な投資信託は最初1口=1円で始まり、その基準価格や約定単価は1万口分の価格で表されます。
つまり以下のような式で投資金額(評価額)が計算されます。
投資金額(or評価額) = (口数/10000) × 平均約定単価(or基準価格)
私は今まで上記の事実を知らないまま投資をしていました。恥ずかしい…
右のグラフは「ツリーマップ」と言います。
ツリーマップとは長方形の面積で値の大小を表すグラフとなっています。
エクセル2016以降であれば、標準でグラフの種類から選択できます。
最初はS＆P500で見たことのある下のような図を作りたいと思ってこれは何というグラフかなーと思って調べたのが作成したきっかけです。
ちなみに上の図は厳密にはツリーマップではなく、「ツリーマップ」と「ヒートマップ」を組み合わせたものとなります。
「ツリーマップ」はブロックの大きさで全体に占める割合を、「ヒートマップ」は色で値の大小(この場合、騰落率)を表します。
本シートでは月ごとの値を入力する項目がない＋シートが煩雑になるのでヒートマップに関しては実装していません。
投資信託 推移
本シートでは投資信託の月次推移を記録します。
ひとつ前の「投資信託」のシートから値を持ってくればOKです。
表示できる投資信託は現在5つに設定してあります。
投資信託名の下の欄にはその月の評価額を入れてください。
累計投資金額の下には「投資信託」シートの「投資金額 計」の値を入力してください。
右のグラフは左の表を可視化したものとなっています。
投資信託の評価額は積み上げ面グラフとなっていて、累計投資金額は折れ線グラフとなっています。
積み上げ面グラフが折れ線グラフを上回っていれば、評価額の方が高いということなので、利益が出ているということになります。
資産まとめ
ここでは「日本株」「投資信託」で入力した値を反映したシートとなっています。
入力する項目は有りません。
左から
- 日本株・投資信託を合わせたサンバーストグラフ
- 日本株・投資信託を合わせたツリーマップ
- 日本株のツリーマップ
- 投資信託のツリーマップ
となっております。
最初はドーナツ円グラフを二つ重ねて2重円グラフを作成しようとしていたのですが、銘柄名を表示させるとめっちゃ重なってしまい見た目が汚くなってしまうので、機能が似ているサンバーストグラフにしました。
サンバーストグラフであれば、面積の小さい項目の名前が表示されないので見栄えがキレイです。
ちなみにこのグラフの下に白文字でデータが隠してありますw
まとめ
以上私が作成したポートフォリオエクセルの紹介でした。
もともと他の人が公開していたポートフォリオエクセルを使用していたのですが、自分が作っていないものを使っているという違和感から自作に踏み切りました。
これから毎月のお金の報告をする際はこのエクセルを使おうかと思います。
