株主優待収益自動計算ツール(Excelを使用)

スポンサーリンク

年間を通して株主優待を狙っていると、どの優待でどの程度得をしているのか、何月にいくら現金を用意しなければならないのか、そもそもどの優待を狙っていたのか・・・都度確認するのが面倒になり、Excelで自分用に計算ツールかつ確認用ツールを作成しました。自分が使いやすいように作っているので他人様の評価は未知ですが、使えるようでしたら使ってください。

以下、ダウンロードリンクです。(yuutai_upα1.xlsm)

2017.6.30  yuutai_upα1

2017.7.3 yuutai_upα1.1 X列のコスト計にR列の買金利(1日)が含まれてないのを修正。

2017.9.8    yuutaiα2_kcom プルダウンメニューの動作を修正。カブドットコム証券に対応(暫定)、貸株日数の入力に対応

2017.9.10 買建、売建で証券会社を選択するプルダウンメニュー(プルダウンリスト)が表示されないというご指摘をいただいてます。私の環境(Excel2010、windows10)では問題なく使用でき、原因の特定ができていません。ダウンロードされた皆さんより動作の状況を教えていただけないでしょうか?バグやご意見を含めて、些細なことでもコメント欄よりいただけましたら嬉しく思います。

2017.9.25 yuutaiα2_kcom_2007   Excel2007に対応。(プルダウンメニューの動作)Excel2010による互換性チェック(2007)ではエラーはなくなりました。実際に2007で確認はできていませんので、問題があれば教えてください。再度調べます。2007では、他シートを参照できないようですので、手数料の表を同じシートにコピーしました。絶対参照としていましたので、単純にカット&ペーストしています。私はカブコム未開設で、恥ずかしながらカブコムの手数料体系に明るくありません。このシートで使えるようでしたら、細部を詰めますので、お手数をおかけしますがご意見ご要望をよろしくお願いいたします。

画像1.「集計」シート

画像2.「銘柄コード指定」シート

画像3.「銘柄コード指定」シート(2)

4.「データコピー元」シート

1.特徴

  • 銘柄ごとの収益が確認できます。
  • 月ごとの狙う銘柄が確認できます。
  • 銘柄ごとの資金効率を比較できます。
  • 証券会社や取引の種類、約定金額により、手数料の目安が確認できます。

2.主に使うシートの見かた(上画像、集計シート)

  • A列 コード おなじみ、4桁の数字です。自動入力されます。(画像では隠れています)
  • B列 銘柄 自動入力されます。
  • C列 優待権利確定月 手動入力です。(必須
  • D列 権利確定日 手動入力です。必要なければ未入力で構いません。
  • E列 株価 自動入力です。
  • F列 株数 手動入力です。優待に必要な株数を入力してください。(必須
  • H列 建てる際の約定額です。自動入力です。
  • I列 クロスするにあたり最低必要な資金です。自動入力です。(現物買+信用売(現物×0.35))
  • J列 手数料を算出するための内部的な区分です。(使用者は無視してOK)
  • K列 優待の額面 手入力です。未入力でも構いません。
  • L列 優待の本質的な価格です。手動で必ず入力してください。(各自の評価を入力、必須)
  • M列N列 買い建て売り建ての取引の種類と証券会社をプルダウンリストから選んでください。現在、私が利用している証券会社以外は選択できません。(必須
  • O列P列 売買手数料 自動入力です。
  • Q列S列 信用取引の利率です。自動入力。
  • R列T列 信用取引の金利です。買いは現引きしますので1日分、売りは5日で計算しています。
  • U列V列 制度信用でクロスする場合、逆日歩の予想を入力してください。手動入力です。
  • W列 U列V列に入力された数値をもとに逆日歩を計算、コストに算入します。(自動入力)
  • X列 売買手数料、金利、逆日歩の合計です。優待獲得の総コストです。(自動入力)
  • Y列 L列の実質的値段からコストを引いた額です。優待の収支なります。(自動入力)
  • Z列 Y列の収支をL列の必要資金で割った値です。資金拘束1万円あたりの収益です。(自動入力)

3.使い方

  • 画像2の「銘柄コード指定シート」に、4桁の証券コード入力してください。黄色の列、A列の2段目より、間を開けずに入力してください。
  • コードの入力後、「表示」→「マクロ」→「マクロの表示」→「株価データ取得」→「実行」とクリック。
  • 画像3となりますので、「取引値のみ」、「銘柄コード指定シートで・・・」にチェックして「データ取得」をクリック。シート名は「任意」にチェックされ「一時データ」としています。シート名は変更しないでください。
  • 「一時データ」シートが作成されますので、シート全体をコピーして、「データコピー元」シート貼り付けてください。貼り付け完了後、自動作成された「一時データ」シートは削除してください。
  • 「集計」シートを表示して、C列F列L列M列N列を入力してください。黄色の列は手動入力必須、水色の列は未入力でも構いません。「集計」シートは、黄色と水色の列以外は触らないでください
  • 収支と資金効率が自動計算されます。
  • 特定月の銘柄を表示したい場合は、C列に設定しているフィルターを使用してください。必要な月の数字にチェックを入れて表させます。この際、「並べ替え」は使用しないでください。

4.注意点

  • 各手数料は変更される場合があります。各証券会社を確認して手数料計算のデータ表を作成しましたが、少々間違っていても影響が小さいので結構いい加減かもしれません。
  • 銘柄の追加や削除は、「銘柄コード指定シート」からコードを追加削除してデータをダウンロードしなおしてください。
  • 各シートは削除しないでください。削除可能なシートは、データのダウンロードで自動作成される「一時データシート」のみです。
  • 計算式保護のため、シートを保護しています。改変は自由ですので、計算式の修正やデータの変更をする場合は保護を解除してください。改変後は私にも使用させてください。(笑)
  • シート保護解除のパスワードは「0」です。
  • 株価のダウンロードは、以下のツールを使用させていただきました。作者様に感謝いたします。http://happy.kabu-web.net/kabu_tool_3.html
  • 株価データ等は、上記URLのツールの仕様により、Yahooファイナンスより取得されます。取得データは私的利用の範囲内とし、営利目的に使用しないでください。
  • 不明な点や不具合、ご希望がございましたらご一報ください。可能な限り対応させていただきます。
  • このツールを使用して発生したいかなる損害に対しても作者は一切責任を負いません。

それでは、株主優待でしっかり儲けましょう!

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

コメント

  1. しゅう より:

    ん、こんばんは^^。

    素早い対応ありがとうございます。

    ただ、こちらの環境が悪いのか、プルダウンは出てきませんでした。
    何か設定がいるのでしょうか?

    カブコムの対応ありがとうございます。

    今後ともどうぞよろしくお願いいたします。

    • カーボ より:

      ご連絡ありがとうございます。
      N列O列の1行目でなく2行目より下のセルを選択しても出ませんか?何の設定も不要のはずですが、他の環境で試したことがないので原因不明です。すみません。
      こちらではダウンロードして確認すると問題ありませんでしたので、Excelのバージョン(互換性)に問題があるのかもしれません。Excel2010ですが、取り急ぎ他のバージョンで試してみます。

  2. しゅう より:

    カーボさん、こんばんは^^。

    遅くなってすいません。

    私はエクセルの2007を使っています。

    どうやら、2010からプルダウンメニューが変わったようです。

    http://nasunoblog.blogspot.jp/2013/09/excel-dropdown-list-do-not-show.html

    どうも手持ちのエクセルのバージョンのせいのようですね^^。