Googleスプレッドシートで自分専用の配当金管理シートを作ってみよう

他にも配当管理ツールはありますが、スプレッドシートではデータや図表を思い通りにカスタムできますよ。

自信の投資方法と配当収入に関して、よりスマートな決定を行うための情報を整理できます。

本記事は”InvestSomeMoney.com”様の許可を得て、下記の記事を翻訳し作成しています。

引用元では、Googleフォームから必要事項を記入して送信を押すと、スプレッドシートのテンプレートが閲覧できます。良ければ必要に応じてコピーしてご利用ください。

Googleフォームについて不安だという方は、下記をご覧ください。https://support.google.com/accounts/thread/43606705?hl=ja

GoogleスプレッドシートはおおむねExcelと同じですが、一部様式が異なります。特に本記事で紹介しているピボットテーブルは様式が異なるのでご注意ください。詳細は下記の書籍で細かい操作方法も記載してあるのでお勧めです。Kindleでもお読みいただけるようです。

 

GoogleスプレッドシートではGOOGLEFINANCEという便利な関数があり、筆者の"Stock Portfolio Spreadsheet"でも多用されています。現在の価格を把握するには素晴らしいのですが、それ以外の配当に関する情報は乏しいものです。

銘柄の配当利回り＝（配当金）／（現在の価格）を把握するのは良いのですが、自分自身の配当利回りを知ることの方がもっと重要です。加えて、配当収入の増加速度が堅調か知ることで、目標に対する達成率を測ることが出来ます。

ステップ１－投資データの入力

①　Googleスプレッドシートを新規作成

新しいシートを作成し「Investment　Data」などと名前を付ける。

②　A1からF1のセルに下記のように項目を入力する。

ティッカー‐日付,ティッカー,名称,購入日,購入数量,約定価格

③　A2に自動入力の為の関数を入力する。

=IF(ISBLANK(B2),””,B2&”-“&TEXT(D2,”YYYY/MM/DD”))

上記の関数によって、B2のセルの値に応じた結果がA2のセルに表示されます。

B2のセルが空欄の場合；B1も空欄のまま

B2のセルに値がある場合；「B2の値」＋「‐」＋「D2の値（日付形式）」

上記の例だと、B2に「DEO」と値があり、D2に2015/05/04とあるので、A2には「DEO-2015/05/04」と表示されます。

他の購入履歴についても同様に表示させるめ、A2を下方向にコピーします。

新しく銘柄を購入した場合もA列は常に同じようにコピーすればOKです。

④　残りのセルB列からF列に必要な情報を入力する。

項目に従って購入履歴から必要な情報を入力します。

お疲れ様です。「ステップ１－投資データ」は終了です。下記のように出来上がるはずです。

好みによりますが、関数を入力したセルにだけ塗りつぶしなどの効果をつけておけば、後々メンテナンスする際に便利です。

ステップ２ー配当金データの入力

このステップで作成するシートが後のシートの基礎となります。ステップ１で入力した投資データに応じて、受け取った配当を入力していきます。

①　新しいシートを追加し、Dividend Dataと名前を付け、A1からA11に項目を入力する。

左下の＋ボタンを押すと新しいシートが追加されるので、A1からA11に下記の項目を入力します。

ティッカー‐日付,ティッカー,名称,購入日,購入数量,購入価格,約定価格,受渡価格,配当日,配当金合計

ステップ１とほぼ同じじゃん！と思われた方、そうです同じです。少し面倒に思われるかもしれませんが、実際に入力するのはティッカー-日付、配当日と配当金合計だけです。後々、楽になるので頑張ってください！

②データの入力規則を設定する

A列のティッカー‐日付を入力する際に、プルダウンメニューから入力できるようにします。

A2からA600（もしくはシートの一番下のセル（デフォルトではA1000）まで）を選択し、メニューの「データ」＞「データの入力規則」をクリックします。

下記の通り設定し、保存を押します。

A列に下向き▼が表示され、クリックするとステップ１で入力したティッカー‐日付が表示されています。

次に、A列の選択内容によって自動的にBからG列にティッカー,名称,購入日,購入数量,購入価格,約定価格,受渡価格が表示されるようにします。

③　ステップ１のシートからデータを読み込む

B1からF1に下記の通り入力します。

ご覧頂いた通り、G列を除きほとんど同じ計算式になっています。

違いは最後の方の数字だけですね。この数字は何列目の値が欲しいかを示していて、ステップ１で作成したシートの各項目の列番号に対応しています。

その他、関数について簡単に下記に示します。

④　受渡金額を計算する

受渡金額は購入数量×約定価格です。

ただし余計なエラーや不正確な値を防ぐために、IF関数を使用して入力します。

=if(E2*F2=0,"",E2*F2)

⑤　配当日と配当金合計を入力する

受け取った配当の年月日と合計金額を入力します。

楽天証券であれば「口座管理」＞「取引履歴」＞「配当・分配金」から確認出来ます。ExcelまたはPDF形式での出力も可能です。

これでおおむね準備ができました！次にグラフを書いていきます！

ステップ３－昨年度の配当金と収益率

ゴールは下記のようなパイチャートを作成し、昨年度収益率を求める事です。

ステップ２までに入力し作成したシートからデータを抽出して表示させます。

具体的には「ピボットテーブル」という機能を使用します。私もピボットテーブルを使用したことはありませんでしたが、原文の著者の大変丁寧な説明のおかげで問題なく作成できました。下記の①から順番に進めれば問題ありません。

①　ピボットテーブルを作成する

Dividend DataのシートのA列からI列を選択し、メニュー ＞「データ」 ＞ 「ピボットテーブル」をクリックする。

ポップアップが開くので、「新しいシート」を選択し「作成」をクリックする。

②　テーブルの設定

まずシートの名前を「Prev Year Dividends/Yield」とします。

次に、テーブルの適当な場所をクリックして、右側に表示されたピボットテーブルエディタの「行」の横にある「追加」をクリックし、プルダウンから「ティッカー」を選びます。

ステップ２までに入力したティッカーが表示されます。次に、「値」の横にある「追加」を押して「受渡金額」をクリックします。

続けて、同様に「配当金合計」も追加します。

不格好なのでセルB1とC1をそれぞれ「受渡金額」と「配当金合計」に書き換えます。

さらに、ピボットテーブルエディタの「受渡金額」に表示されている「SUM」を「AVERAGE」に切り替えます。「配当金合計」は「SUM」のままでOKです。

次に「値」＞「追加」＞「計算フィールド」と選択し、「数式」に

「=SUM('配当金合計')/AVERAGE('受渡金額')」と入力します。さらに「集計」を「カスタム」とします。

あと少しです。ピボットテーブルエディタの下部にある「フィルタ」をクリックし、「ティッカー」を選択します。

（空白）のチェックボックスを外すと、2行目の余分な行が削除されます。

同様に「フィルタ」＞「配当日」と選択し、「条件でフィルタ」＞「次より後の日付」＞「正確な日付」をクリックし、下記の式を入力します。

=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())+1)

去年の同じ日より後のデータのみが表示されます。

「計算フィールド」と表示される項目名を「Prev Year Yield」とし、表示形式をパーセントとすればOKです。

※上の図では#ERRORと表示されていますが、計算がうまくいかないときは、数式が間違っていないか、項目の名前が間違っていないかを確認してください。

一度メモ帳などに貼り付けて確認してから貼り付けると間違えが無くて良いです。

③　合計（平均）の計算

ピボットテーブルエディタの「行」＞「ティッカー」を選択し、「総計を表示」のチェックボックスを外します。

ピボットテーブルの上部に2行、左側に1列を追加して見やすくします。

C2のセルに下記を入力します。

＝SUM(C4:C53)

D2のセルに下記を入力します。

=SUM(D4:D53)

保有している銘柄数によっては53行ではたらいない可能性があるので、お好きな数に適宜ご変更ください。

そしてE2のセルに下記を入力します。

=D2/C2

④　パイチャートの作成

ティッカーと昨年度収益率の列を選択し、挿入＞グラフを選択し、グラフエディタで円グラフを選択すると完成です。

題名や文字のフォントなど適宜変更して見やすいグラフにしてください。

あくまで配当金を管理するシートです。

次のステップでは総配当収益をまとめるシートを作成します。

ここらでいったん休憩をー

ステップ４－総配当収益

完成図はこんな感じです。総配当収益率と年次の平均収益率を表示させます。

ステップ３と似たような工程ですので、図は割愛してステップだけご紹介します。

もし不明な点があれば直接ご連絡ください。

①　Dividend DataのシートのA列からI列を選択して、ピボットテーブルを新しいシートに作成します。

②　新しいシートをTotal Dividend/Yieldとでも名付けます。

③　ピボットテーブルエディタで下記のように設定します。

④　総計を表示させる。

ポイントは加重平均をとることですが、今回は難しい話はやめて、「受渡金を荷重値として日付を平均する」ということです。スプレッドシートでは日付に対応した数字が決まっているので、そのまま四則演算しても問題ありません。

ピボットテーブルの左に1列、上に2行を追加して下記を入力します。

D列とE列は列の合計を入力出来ていればOKです。

まとめると下記の通りです。

購入日	受渡金額	配当金合計	総配当収益率	年間収益率
全ての購入日の加重平均	受渡金額の合計	配当金合計の総計	（配当金合計の総計）÷（受渡金額の合計）	購入日から今日までの収益の年平均

⑤　棒グラフとして表示させる。

下図の通り、B3:B10, F3:F10, G3:G10を選択して棒グラフを挿入します。

3行目をラベルとして使用すれば、目的の棒グラフの完成です。

お好みでフォントや色などの装飾をご変更ください。

年次の収益率が計算できる点が重要です。

下記の例を見てみましょう。

ティッカー	総収益率
ABC	50％
DEF	20％

これだけ見るとABCの方がよさそうですが、下記はどうでしょう。

ティッカー	総収益率	購入日
ABC	50％	20年前
DEF	20％	2年前

20年かけて50％の総収益率を出したABCも悪くはありませんが、たった2年で20％の利益を叩き出したDEFはより凄くないでしょうか？より分かりやすくするために、年次収益率（年間配当利回り）を算出すると約4.5倍も大きいのです。

ティッカー	総収益率	購入日	年次収益率
ABC	50％	20年前	2.05％
DEF	20％	2年前	9.54％

20％を2年で割れば10％の年次収益率では？と思うかもしれませんが、年次収益率は収益に時間を乗じた複利計算なので、単純な年次収益率×年数ではありません。

元サイトのページでは、さらに収益の増減をトレースするシートの作成方法も提示されています。記事がかなり長くなったのでこのあたりで終わりにします。ご興味のある方は参考にしてみてください。