見出し画像

スプレッドシートで日本株銘柄管理生活

はんぞうもん

すごいぜスプシ

保有銘柄や監視銘柄は、投資家によって管理方法は様々かと思いますが、最近株クラのフレンドさんから面白い管理方法を教わり、掘り下げてみました。

スプレッドシート？

グーグルさんのサービスの1つに、Googleドライブというものがあり、いわゆるクラウド上のハードディスクみたいなイメージなんですが、そこにMicroSoftのExcelのような表計算アプリがあるのです。それをGoogleスプレッドシート（以降スプシ）と呼びます。

なにがすごいの？

掘り下げてみて知ったのですが、スプシはスクレイピングもできるんだ！ということが驚きでした。
スクレイピングとは、例えば皆さんがインターネットを閲覧するときに使うブラウザってあると思うのですが、iOSならSafari、AndroidならGoogleChrome、PCなら、GoogleChome, Edge、などなどのことですね。これに表示している文字や画像などは、HTMLというマークアップ言語で記述されており、「このサイトのこの部分にマークアップされた箇所にある文字をちょーだい」みたいなことをやるのが、スクレイピングだと思ってください。
つまり、このサイトのこの部分だけほしいです、っていう時に便利です。
で、スプシでもエクセルの関数のように、このスクレイピングが実現可能ということでした。

何を管理するの？

まず私が知ったスプシのテンプレートは、
銘柄コードを入力すると、銘柄名、PER、PBR、現在値、配当利回り、が自動で取得、表示できるよというものです。
そこに私は、配当ウサギさん（@bonobo_toushi）という方の固定ツイートを拝見して、「理想利回り」から「買いサイン」を独自に出してみよう、となりました。

Googleスプレッドシートテンプレート

まず、参考にさせていただいたブログはこちらになります。
Jimblog様
配当管理 Googleスプレッドシート
ここで表示する列は、以下のようになっています。

  • 証券コード（B列）

  • 銘柄（C列）

  • PER（D列）

  • PBR（E列）

  • 配当（％）（F列）

  • 現在値（G列）

  • 取得数（H列）

  • 配当金予想（円）（I列）

https://power-bi-seminar.site/5414/  より

この中で黄色の列は自分で入力する項目で、あとは自動で取得、表示してくれる仕組みです。これだけでも素敵ですね。

カスタマイズ後のイメージ

ではここから、「理想利回り」から「買いサイン」を独自に出してみたいと思います。
まず、カスタイマイズ後の表はこのようになります。

様々な項目を一目で

PER x PBR 、理想利回り、買いサイン、1株配当、1株配当更新時株価、という列を追加しています。

PER x PBR

これはシンプルにPERのセルとPBRのセルを掛け算したものです。割安などの目安にも使われる数値（だったはず）です。
日経新聞の記事より

理想利回り

これが一番難しいです。
というか、何を理想とするかは人それぞれなので、勝手に算出してよいのです。
私はここでは、「1株配当」が更新されるタイミング（決算や修正）の最新の日付時点の終値、を基準に、その時点の配当利回りを理想利回り、としています。
ちなみに、配当利回りは、1株配当 ÷ 現在値 x 100 で求められます。
この現在値の部分を、発表があった日の終値、としているだけです。

1株配当

1株当たりの配当金のことです。5円であれば、100株もっていると500円の配当がもらえるかもね、ということです。

1株配当更新時株価

これは先述した「1株配当が更新されるタイミング（決算や修正）の最新の日付時点の終値」のことです。手入力欄です。

いざカスタマイズ

まずは行を追加

上記の列を、まずはそれぞれシートに追加していきます。
自分は「PER x PBR 、理想利回り、買いサイン、1株配当」をそれぞれG、H、I、J列に挿入していますが、見やすい列で構いません。「1株配当更新時株価」は理想利回りを算出するための情報であるため、一番右のN列によけています。
いちいち上部にスクロールするのも面倒でしょうから、同じスクショを貼っておきます。

元のテンプレートに列を追加

とりあえずこの中から、「1株配当」も同じようにスクレイピングで取得してしまおうと考え、このスクレイピングは「株探」を見に行っているようですので、同様に株探から値を拝借したいと思います。
1株配当のスクレイピングは以下のように取得できます。

=IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",B5),"//*[@id='kobetsu_right']/div[3]/table[1]/tbody/tr[3]/td[5]")

"B5"というのは、B列は銘柄コードを指しているので、code=銘柄コード、となります。

これにより1株配当は取得できるのですが、この参考にさせていただいたテンプレートは、実は銘柄数が多くなるとロードが終わらずエラーとなります。
というのもスクレイピングは、実際のそのサイトにアクセスしているため、1つの銘柄コードに対して、銘柄、PER、PBR、配当（％）、現在値という5リクエストしてしまうんですね。なので、銘柄数が10あると、50リクエストということなってしまいます。
そこに「1株配当」が加わると、1つの銘柄で6リクエストとなってしまい、10の銘柄で60リクエスト、ちょっと多いですよね。

そこで1株配当をスクレイピングとしたので、1株配当が取得できたなら、元からある列の「配当（％）」は先述のように計算でだせますね。
こんな感じです。

=J5/K5*100

Jが1株配当、Kは現在値です。
これで、リクエスト数は変わらずに列が増やせました。
しかし、これでもやはり銘柄数を増やすとすぐエラーとなるのは変わりません。
では今度はどのリクエストを削ろうか・・・。
ということで現在値を別な方法で取得することにします。

現在値の自動取得

株探へのリクエスト数を減らすため、現在値を別な方法で取得してみます。
実は冒頭でご紹介している配当ウサギさん（@bonobo_toushi）の固定ページでは、スプシで管理する方法のあれこれの質疑応答が繰り広げられているのですが、
その中に「STOCKPRICEJP関数」というワードが出てきます。
この関数は、そのまま使える関数ではなく、自動で現在値を取得できる関数として、定義してあげないといけません。
そんな難しいことできるかーいと思うわけですが、
STOCKPRICEJPでググると、すぐ見つかります。

AutoWorker ～ Google Apps Script（GAS)とSikuliで始める業務改善入門
スプレッドシートで日本株の株価や投資信託の価格の取得方法(GASオリジナル関数)
ここでSTOCKPRICEJP関数の追加方法がわかりやすく載っているので、追加方法はここを参考いただくとして、少しこれもカスタマイズします。

関数を追加する過程で、下記のようなコードをコピペしたかと思います

function STOCKPRICEJP(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let stockPrice = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();
  return stockPrice;
}

これをそのまま関数としてつかうと、「¥3,123.00」のように取得されます。
実はこのままだとちょっと不都合があり、数字としてスプシが見なしてくれないため、セル間の計算に使えないわけです。
つまり、配当利回りの計算に使えません。
なので、数字として取得できるように、以下のようにします。

function STOCKPRICEJP(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let stockPrice = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();
    stockPrice = stockPrice.replace('¥', '').replace(',', '');
  return Number(stockPrice);
}

違いが分かりづらいかもしれませんが、最後にある、
`return stockPrice;` が `return Number(stockPrice);` となっており、
その直前の行に、
`stockPrice = stockPrice.replace('¥', '').replace(',', '');`
を追加しています。
要するに、¥マークと、カンマが邪魔なので削除して、数値として出力するよ！ということをやっています。
こうすることで、「¥3,123.00」は「3123」となります。
これで現在値が数値で取得できました。あとは現在値の箇所を以下のようにします。

=STOCKPRICEJP(B5)

これにより、1銘柄の株探へのアクセスを4つに減らせました。
結論から言うと、この状態で47銘柄（※）まで追加できます。
それ以上は、やはりエラーとなってしまいます。自分はそこまで多くの銘柄を保有しないので、とりあえず今回はこれで完成としますが、
あとは削るとしたら銘柄名の自動取得あたりですかね・・？
※ どういう条件かわかりませんが、47銘柄でもロードエラーになる場合もあるようです。時間経過で内部的にリロードが走って、それがしきい値を超えてしまうのかもしれません。
近々カスタマイズ Ver1.1 考えようかなぁ…

1株配当更新時株価はどこを参考にする？

自分で入力するこの項目ですが、調べるにはコツがあります。
ここも株探を利用させていただきましょう。
まずは、調べたい銘柄の基本情報を開きます。すると右下の赤枠の部分に最新の発表日が記載されています。これが決算発表や修正で更新されると思われますので、例えばこの23/04/28を覚えておきます。

株探で最新の発表日を確認

そしてすぐさま、チャートのタブへ移動します。
日足チャートが表示されますので、マウスで先ほどの23/04/28を探します。
左の赤枠に日付が表示されますので、すぐ見つけられます。
見つかったら右に赤枠の終値、これが1株配当更新時株価です。

1株配当更新時株価を探す

理想利回り

これで理想利回りが算出できます。

=J5/N5*100

Jは1株配当、Nは先ほど入力した1株配当更新時株価です。

買いサイン

理想利回りより、現在の配当利回りが高いということは、基準とした時よりも株価が安くなっているというわけです。
したがって、ここの計算式はこうなります。

=H5-F5

配当(％) - 理想利回り ですね。これがマイナスになると、狙い時としているわけです。ついでにセルにマイナス値には色を付けるなどするとわかりやすいと思います。

PER x PBR

これは先述もしてますが、ただの掛け算です。

=E5*D5

これでカスタマイズ完了です。

おわりに

理想利回りは、ホント人それぞれです。あくまで参考程度に。
今年は日経平均爆上がりの年なので、決算発表時の配当を基準とすると、まぁ大抵株価はあがっているので、「買いサイン」となるものは多くはないはずです。
これも基準値など決めず、「4％」でいいんだ！というのであれば、1株配当更新時株価の入力も不要ですし、理想利回りのところに「4」と入力するだけで済みます。
ホント人それぞれなので、自分にあったものでお試しください。

自分のスプシを公開してしまうと、アカウント名が自分の名前になっていたりと色々面倒なので、カスタマイズの方法のみを解説していきました。
部分的にでもご参考になれば幸いです。

いいなと思ったら応援しよう！