株式ポートフォリオ管理シート作成

Excelで資産管理しよう！　第2弾の①です！
株式ポートフォリオ管理シート作成します
保有銘柄の資産状況・配当金効率の確認をしましょう！
リアルタイムで株価を取得しながら、ポートフォリオの状態を確認しましょう！
実質収益・配当金はいくら？
NISA・特定口座別に税金計算 します

黄色の塗り潰しが入力項目です

コード欄に銘柄コードを入力していきます
お持ちの銘柄を入力してください

今回は、楽天証券からスクリーニングしました

国内株式→スーパースクリーナー
おすすめ５　高配当銘柄から　選びました

スクリーニングしたコードを入力します

表をテーブルに…
挿入→テーブル

データの入力規則からリストを選択

元の値

特定かNISAか選択できるようになりました

オートフィルして、全コード口座を選択できるようにします

口座（特定かNISA）は、損益・配当金の税金計算に用います

東証マーケット情報から業種を取得します

この東証上場銘柄一覧（Excelファイル）をダウンロード

コード・銘柄名・33業種区分のみにする

この表に名前を付ける
数式→名前の定義　
名前：コード表　参照範囲（Ctrl＋Shift＋→　，Ctrl＋Shift＋↓）

取得する値は、コード表の3列目

=VLOOKUP([@コード],コード表,3)

オートフィルします

業種が求まりました

MarketSpeedⅡでリアルタイムに値を取得する

現在値

１株配当

詳しい取得方法はこちらを見て下さい！

【EXCEL & VBA】Excelで資産管理しよう！　リアルタイム株価取得　Webから投資信託価格取得＆取得データを最新データに更新　データ変換　マクロで更新　ブックを開いたときに更新するには…

【EXCEL&VBA】Excelで資産管理しよう！　リアルタイムで株価を取得しますWebから投資信託の価格を取得し、最新データに更新しますWebからデータを取得した人は、ぜひデータの更新も見てね！MarketspeedⅡRSS関数、LEFT関数、LEN関数、FIND関数、PowerQueryからWebからデ...

www.youtube.com

・保有株数・取得単価を入力します

・取得価格=[@保有株数]*[@取得単価]
・時価評価額=[@保有株数]*[@現在値]
・損益*＝[@時価評価額]-[@取得価格]
・損益率=[@損益]/[@取得価格]（％表示、小数点以下２桁）
・配当金*＝[@保有株数]*[@１株配当]
・配当利回り（取得）=[@配当金]/[@取得価格]（％表示、小数点以下２桁）
・配当利回り（時価）=[@配当金]/[@時価評価額]（％表示、小数点以下２桁）

*：税金の計算を追加します

損益は、[@時価評価額]-[@取得価格]で求まりますが、
特定口座は損益に税金が掛かります

NISAの場合：[@時価評価額]-[@取得価格]
特定口座の場合 ：([@時価評価額]-[@取得価格])*79.685％)
（79.685％＝100％-20.315％）

=IF([@口座]=”NISA”,[@時価評価額]-[@取得価格],([@時価評価額]-[@取得価格])*0.79685)

税率をセル指定すれば、税率が変更されても修正が簡単ですね！

=IF([@口座]=”NISA”,[@時価評価額]-[@取得価格],([@時価評価額]-[@取得価格])*$U$5)

更に、損失が出ている場合は税金はかからないので…

損出が出ている時 : 取得価格>時価評価額

NISAと損出が出ている場合：[@時価評価額]-[@取得価格]
　　利益がある場合　　　：([@時価評価額]-[@取得価格])*0.79685

IFS関数を使って…

=IFS([@口座]=”NISA”,[@時価評価額]-[@取得価格],[@取得価格]>[@時価評価額],[@時価評価額]-[@取得価格],TRUE,([@時価評価額]-[@取得価格])*0.79685)

IF文にORを使うと…

=IF(OR([@口座]=”NISA”,[@取得価格]>[@時価評価額]),[@時価評価額]-[@取得価格],
　　　　　　　　　　　　　　　　　　　　　([@時価評価額]-[@取得価格])*0.79685)
こっちの方がわかりやすいよね！？

配当金は、[@保有株数]*[@１株配当]で求まりますが、
特定口座は配当金に税金が掛かります

NISAの時：[@保有株数]*[@１株配当]
特定の時　：[@保有株数]*[@１株配当]*0.79685

=IF([@口座]=”NISA”,[@保有株数]*[@１株配当],[@保有株数]*[@１株配当]*0.79685)

テーブルに集計行を追加します

　集計の値
・銘柄名称　：個数
・取得価格　：合計
・時価評価額：合計
・損　　益　：合計
・配　当　金：合計

・損　益　率　　　：損益の合計/取得価格の合計（％表示、小数点以下２桁）
・各時価構成比　　：各時価評価額/時価評価額の合計（％表示、小数点以下２桁）
・配当利回り(取得)：配当金の合計/取得価格の合計（％表示、小数点以下２桁）
・配当利回り(時価)：配当金の合計/時価評価額の合計（％表示、小数点以下２桁）
・各配当金構成比　：各配当金/配当金の合計（％表示、小数点以下２桁）

・時価構成比　：合計（100％）
・配当金構成比：合計（100％）

時価構成比と配当金構成比をデータバーで表示しましょう

ホーム→条件付き書式

データバーの塗り潰しがおかしい
表示を訂正
条件付き書式→ルールの管理→ルールの編集

最大値を１とする

適用先を指定

配当金構成比も同様に
こちらは先に範囲を選択しました

構成比がイメージしやすいですね！

見せたくない行は非表示に…

テーブルが横に長いので、ウインドウ枠の固定を使っても…

書式を揃えます
桁区切り・％表示・小数点以下の表示

口座（NISA/特定）で並び替えても…

ポートフォリオ管理シート完成しました！

作成したポートフォリオ管理シートから、グラフを作成します

こんなのを作りま～す