Googleスプレッドシートを使って優待利回り実績の棚卸する方法

保有する株が多くなって来たときに、1年間の優待利回りや配当利回りの実績がどうなっていたのか気になることがあるのですが、今までは、全体的に見ることはなく、適当にサンプリングしてランダムにみる程度でした。

これって、一般的にはどのようにやっていくのがいいんですかね。

配当利回りだけでいいのであれば、ポートフォリオの機能を利用すればできそうです。楽天証券だと

マイメニュー→資産残高・保有商品→ポートフォリオ

のバリエーションのタブやカスタマイズのタブあたりからできそうです。

しかし、優待の情報を絡めて、総合利回りを求めようとするとひと手間かけないといけなさそうな気がしています。

ちょっと、効率的な方法もよくわからなかったので、Googleスプレッドシートの機能をいろいろと使って配当利回り状況を確認する方法を作ってみましたのでここで紹介してみます。

こちらの方法は、Googleのアカウントがあれば、実行できると思います。こちらのページで以下の紹介で利用するCSVファイルは、楽天証券のサイトからダウンロードできるcsvを利用していますが、後述する、スプレッドシートの関数を調整することにより他のCSVファイルでも流用できると思います。

まず、最終系の説明をします。下記のように、1年間の運用利回りを出せるようにします。

やりたいこととしては、「1年間の運用利回りを振り返り、期待している利回りが出ているかどうかを確認する」ということを簡単にやりたいなと思っています。


こちらのように利回りが２％切ったものは赤色に、３％を超えているものは青色に表示するようにしていきたいと思います。

利用する入力は下記の3点です。

  • 配当受け取り記録 (楽天証券でダウンロードできるcsvファイル)
  • 優待記録  (手入力)
  • 持ち株一覧 (楽天証券でダウンロードできるcsvファイル)

このうち2点は、楽天証券を利用している場合には、csvファイルとして出力することができます。

実際に、私の方で作成してみたスプレッドシートは、こちらです。必要であれば、こちらから自分のアカウントにコピーしてご利用してみてください。

こちらのシートは、6つのシートからなっています。

  • 配当受け取り記録 … 入力用
  • 優待記録  … 入力用
  • 持ち株一覧 … 入力用
  • 持ち株年間配当 … 中間用シート。最終結果を出すための一時利用用です。
  • 持ち株年間優待額 … 中間用シート。最終結果を出すための一時利用用です。
  • 最終結果 … 中間用シートから算出
以下にそれぞれのシートについて説明していきます。

配当受け取り記録シート


「配当受け取り記録」は下記のような値を入力するシートです。


楽天証券を利用している場合には、下記からダウンロードできます。

マイメニュー→取引履歴→取引履歴（商品別売買履歴）→国内株式


約定日に抽出したい期間を入れて「CSV形式で保存」ボタンを押すと、CSVが出力されるので、このファイルをスプレッドシートの「配当受け取り記録」シートに取り込みます。

優待記録シート

優待記録については、特に出力することができないので、地道に記録しておく必要があります。私は、到着するごとにスプレッドシートに記録していっています。
下記のような感じで入力していきます。


持ち株一覧シート

持ち株一覧については、下記のような値を入力していきます。


楽天証券の場合には、下記のページからcsvファイルをダウンロードできるので概ねこちらを利用できます。
国内株式→保有商品一覧
にある、「CSVで保存」というボタンを押すとファイルが作成されます。
こちらを、持ち株一覧のページに貼り付けます。

持ち株年間配当シート

持ち株年間配当というシートは、前に入力した配当受け取り記録を加工するためのシートです。このシートでは、左上のセルに、QUERYという関数を使って受け取りシートの情報を加工して、1株あたりに年間配当を算出しています。楽天証券からダウンロードしているファイルを利用している場合にはこのシートを修正する必要はありません。

=QUERY('配当受け取り記録'!A1:J,"SELECT D,AVG(G),SUM(H) GROUP BY D")


※QUERY関数というのは、上のようにシートにある値をSQLのような文法でデータを取得することができます。ＳＱＬというのは、データベースからデータを取得するときに利用されるような言語なのですが、ここでは、下記のように理解いただけると思います。

'配当受け取り記録'!A1:J …配当受け取り記録シートのＡ～Ｊ列の1行目から全てのデータを対象範囲とするの意味

"SELECT D,AVG(G),SUM(H) GROUP BY D" …Ｄ列の一覧を取得してSUM(H)はＨ列の合計値の取得し、AVG(G)はG列の平均値を取得するの意味



このクエリによりA列からC列まで値が埋まりますので、D3セルには下記の式を入力することにより、1株あたりに配当金を算出しています。

=ARRAYFORMULA(IFERROR(C3:C/B3:B,""))

ここで、=ARRAYFORMULA(IFERROR(C3:C/B3:B,"")) は、C列÷B列をそれぞれ行うという意味になります。3と書いてあるのは、3行目からスタートするという意味合いになります。

ARRAYFORMULAは使いこなせるようになると非常に便利な関数です。これを使うことによりすべての行に対して同じ演算式を適用することができるようになります。

CSVの型がここで紹介している例と違っている場合には、列の部分を適当に変更してもらえると、いいかと思います。

持ち株年間優待額シート

持ち株年間優待額も同様に左上のA1セルにQUERY関数を記述しています。こちらも
楽天証券からダウンロードしているファイルを利用している場合にはこのシートを
修正する必要はありません。

=QUERY('優待記録'!A1:E,"SELECT B,SUM(E) GROUP BY B")

こちらも同じように、優待記録のシートのA～E列を対象範囲として、B列の一覧とその合計値を出力するというようなクエリで、これによりA～B列の値が補完されます。


最終結果シート

最終結果シートは、上記のシートがきちんと入力されていれば自動的に保管されていきます。

ここでは、VLOOKUP関数というのを利用して、前述で計算した値を取り出していきます。
このシートの行は、持ち株一覧から持ってきていて、その値と他のシートの値を一緒に出すという
部分についてVLOOKUP関数を利用しています。

また、行ごとに色を変えるという部分は、表示形式→条件付き書式というのを使って実行しています。
こちらからカスタム数式というのを選び

=$I3>0.03

という式を入力します。こちらは、３％を超えるものという意味で、この条件を満たすと背景が
青くなるというような設定を行っています。



以上のようにスプレッドシートを使うとデータを分析したり自分の思う形に変形したり
ということができるようになります。

今回は、配当利回りや優待利回りの一覧を作成して、利回りが期待したとおりでないものが
ないかどうかを確認したいというようなことを実現するためのサンプルを紹介しました。

おまけとして、各行からYahooの株情報へのリンクを生成してみることもやってみます。


スプレッドシートに記述する式としては下記です。

=ARRAYFORMULA(IF(A3:A="","",CONCAT(CONCAT("https://finance.yahoo.co.jp/quote/",A3:A),".T")))


ここでは、CONCAT関数というのを利用していて、文字列を結合しています。A列には証券コードがはいっています。CONCATENATEという関数もありますが、ARRAYFORMULAの文法とは相性が悪いので、ここでは、CONCATを２つ使って3文字を1つの文字に結合していくことにしています。
"https://finance.yahoo.co.jp/quote/"と「証券コード」と".T"という３つの文字をつなげるという感じじです。。


他にもこんなことできるかとか、こんなことしてみたいとかありましたら、コメント欄から
コメントいただければと思います。


時刻:

0 件のコメント:

コメントを投稿