見出し画像

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

はんぞうもん

9月のイベントもほぼ終わり

9月はメジャーSQに始まり、いつものCPI、PPI、さらにはFOMCや日銀決定会合がありつつ、最終週には権利落ち銘柄がたくさん。みたいなイベント過多な月です。
はんぞうもんは、現金割合多めで静かに過ごしていましたが、先日の日銀決定会合の発表（金融緩和まだまだやるでーのやつ）があった後に、ウズウズがとまらずエントリーしてしまいました。。
みなさまは、投資生活いかがお過ごしでしょうか。

バージョンあーっぷ

先日、読みづらい文章だったでしょうに、多くの方に見ていただいたスプレッドシートで日本株銘柄管理生活の記事ですが、
銘柄数をあまり増やせないという課題を残しつつ終わりました。
その課題を若干解消したVer1.1を作成してみましたので、またその方法について解説しようかと思います。

配布もやはり考えるんですが、免責事項やら自分のアカウントばれちゃわないかとか、色々考えるのが面倒なので、あくまで作り方だけ載せることにします。
ただし、今回はもう少しスマートに、後半では各セルにはこんな値を設定してねという説明をしていこうかと思います。

何が変わったのか

前回スクレイピングの解説をしましたが、Ver1.0では銘柄コードを入力すると、銘柄名、PER、PBR、1株配当、現在値の5か所に対して、株探やGoogleファイナンスのページへアクセスして、それぞれの値を抽出していました。
つまり、1銘柄に対して5つのリクエストが飛んでしまうわけですね。株探4か所、Googleファイナンス1か所です。
これでも47件くらいが限界で、さらに時間が経過すると再読み込みが走るようで、その度にLoadingからなかなか戻ってこない、などが見受けられました。

今回、銘柄名をインターネットからではなく、一手間あるものの、同じスプシのシートから読み込めないかと改良したものとなります。

銘柄一覧をダウンロード

日本取引所グループのサイトから、毎月銘柄一覧のExcelがダウンロードできます。
東証上場銘柄一覧のサイト
まずはここからExcelをダウンロードしましょう。

銘柄一覧をインポート

ダウンロードしたら、スプシに移動します。
そこで左上の「ファイル」から「インポート」を選択します。

インポートを選択

そうすると、アップロードのダイアログが表示されますので、最初は「マイドライブ」が選択されていますので「アップロード」を選択しましょう。

アップロードを選択

するとキャプチャにもあるように、ファイルをドラッグしてください、とあるので、エクスプローラを開いて先ほどダウンロードしたファイルをここにマウスでドラッグアンドドロップしましょう。

そうすると、インポート場所を選択できるので、必ず「新しいシートを挿入する」を選択します。

新しいシートを挿入するを選択

間違っても、スプレッドシートを置換するを選択しないでください。
今までのスプシがすべて消えて、銘柄一覧に置き換わってしまいます。
この操作はアンドゥ（Ctrl＋Z）などでは戻せませんので、十分ご注意ください。
というかはんぞうもんは、これでデータが消えてしまって涙目になりました笑
すると、下図のようになるので、そのまま「データをインポート」を押下しましょう。

データをインポートを押下

すると、今までのスプシに「Sheet1」というシートが追加され、先ほどダウンロードしたデータが読み込まれているかと思います。

銘柄一覧がインポートされた

まず、Sheet1というシートの名称を、「銘柄一覧」に変更しましょう。シート名の横にある▼を選択するとメニューが開くので、「名前を変更」で変更できます。

「銘柄一覧」に変更しましょう

これで準備完了です！
新規上場銘柄を積極的に入力しないかぎりは、この銘柄一覧シートの更新は、そこまで頻繁でなくてもいいのかなと思います。

XLOOKUP関数で銘柄名を読み込む

では、こちらのシートにもどりまして。

日本株シート

C5のセルは、こちらの手順を参考に進めていくと、`INDEX((IMPORTXML(CONCATENATE～～` のようになっているかと思いますが、これを下記に書き換えます。

=XLOOKUP(B5,'銘柄一覧'!B2:B4189,'銘柄一覧'!C2:C4189)

先頭の「=」を忘れがちですが、これも含めて書き換えます。コピペするのが一番よいでしょう。
書き換えると、先ほどインポートした銘柄一覧シートから、B5で入力した銘柄コードを探しに行ってくれます。
実在する銘柄コードに対して、銘柄名が表示されたら成功です。

これにより、今まで株探へのリクエストが4つだったものが3つに減らすことができました。
一応50の銘柄を登録することに成功していますが、やはり時間経過で内部的にリロードが走るのか、たまにLoadingとなります。
それもしばらく待てば読み込んでくれるのを確認していますが、もしかすると25銘柄程度でシートを分けるのが良いのかもしれません。

作成方法のサクッとおさらい

では各セルにはどのような計算式を入力していくのか、改めておさらいしていきましょう。
なお、文中のスクレイピングの計算式や元となるフォーマットは、以下のサイトを元に作成しております。
Jimblog様
配当管理 Googleスプレッドシート

おさらい

証券コード（B列）

このセルは手入力です。任意の銘柄コードを入力してください。

銘柄（C列）

先述の通り以下の計算式を入力してください。

=XLOOKUP(B5,'銘柄一覧'!B2:B4189,'銘柄一覧'!C2:C4189)

「B5」というのは、私の環境での位置です。入力した銘柄コードを指しています。
なおこの計算式が入力された状態で、セルをコピーして下の行に張り付けると、勝手にB6と変わっていますので、いちいち書き換える必要はありません。後続の説明も同様です。

B5は証券コードを指している

PER（D列）

株探へのリクエスト（スクレイピング）です。

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

PBR(E列）

こちらも株探へのリクエスト。PERとほぼ一緒に見えますが、最後のtd[1]がtd[2]になっています。

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

PERｘPBR（F列）

D列とE列を掛け算しているだけです。

=D5*E5

配当(％)（G列）

後述する1株配当（J列）を現在値（K列）で割り、100倍しています。

=J5/K5*100

理想利回り(%)（H列）

この理想利回りと後述する買いサインについては、
配当ウサギさん（@bonobo_toushi）という方の固定ツイートからヒントを得ております。
前回この値は何でもよいと説明していますが、自分は目安となる株価をもとに算出した配当利回りを出しています。
これはこの銘柄なら4％は欲しい！とかであれば、セルに直接「4」と入力してしまって構いません。
私は目安となる株価をN列に入力しているため、下記のような計算式にしています。

=J5/N5*100

買いサイン（I列）

理想利回り（H列）を現在の利回り（G列）が上回ると、理想の株価より下がっていることを意味します。つまり買いサインです。

=H5-G5

1株配当（J列）

ここもできればスクレイピングしたくないですが、仕方ありません。
これに現在値が取得できれば配当利回り算出できます。

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

現在値（K列）

こちらはSTOCKPRICEJP関数をカスタマイズして作成したのでした。作成方法は前回の記事をご覧ください。以下のようになります。

=STOCKPRICEJP(B5)

カスタマイズ前のSTOCKPRICEJP関数につきまして、以下のサイトを参考にさせていただいております。
AutoWorker ～ Google Apps Script（GAS)とSikuliで始める業務改善入門
スプレッドシートで日本株の株価や投資信託の価格の取得方法(GASオリジナル関数)

取得数（L列）

ここは手入力なので計算式はありません。実際保有している株数を入力してください。

配当金予想(円)（M列）

保有株数に対して、年間どのくらいの配当金がもらえるのか、という予想額です。
1株配当 x 取得数でも良い気はしますが、ここはこのような計算式になっています。

=K5*G5*L5/100

目安株価（円）（N列）

前回は、「1株配当更新時株価」としていた列です。なんか長いしまどろっこしいので「目安株価」と名称変更しています。
ここも手入力です。理想利回りを算出するのに使いますが、要はこの株価を割ったらいいな的な株価を入力します。理想利回り側のセルを直接4などして、決め打ちにする方法でもよいでしょう。その場合この列は使用しません。

おわりに

今年は1月からいきなり「なんか鉄鋼つよくない？」から始まり、気づけばその鉄鋼もここまでずっと強く、6月あたりまでのバフェ爺相場は「なんか自分の保有銘柄はあがってなくない？」からの、「ははーん、バリュ株ね？」ってなって今日に至ってます。
私は気づくのにかなりの時間を要しましたが、わかってる人にとって今年の相場は、かなり稼ぎ時な一年になっているのではないでしょうか。
今年の特徴として、騰がる銘柄（業種）はとことん騰がる、気がしています。極端といいますか。
遅きに失しても、波にはしっかり乗っておけ、という相場に思えます。
来年の話しをすると鬼がなんかにやけるらしいですが、2024年1月からは新NISAもいよいよ始まります。
ここでまた新たなトレンドが形成されていくのだと思いますが、こうした管理表でもいいですし、しっかり自分の監視銘柄を持っておいて、何がつよつよなのかを早くに見極めていきたいですね。

それでは、みなさま良き株ライフを。

この記事が気に入ったらサポートをしてみませんか？