株価の評価をするExcelシート作成してみた2
はじめに
はじめまして、前回Excelシートを公開したマンゴー製作所です。ご視聴ありがとうございます。最近、株価が急騰して投資の需要も高まっていると考えています。
前回に引き続き、今回はエクセルシートの、Stock Price Valuation Calculator(君)ver1の記事を書いてファイルを公開しますのでマガジンフォローよろしくお願いします。
前回の記事はこちらになります☟
そのver1の内容ですが、株価の評価シートの見た目を新しくしました。そしてソレの使い方ついては、このnoteで解説して、公開してくという記事の内容になります。
ちなみに、なぜ筆者がこのようなエクセルシートを作りはじめたのかと言うと、①ＰＥＲとかＰＢＲとかEV/EBITDAとかＲＯＥの数値を見ても割高割安の判断が難しいこと、②株価のリアルタイムで表示させ、値幅や相場情報をわかりやすくしたいから、という二つの目的があります。
必要なもの
マイクロソフトofficeのExcel必須（Excelが必要な理由はRSSとVBA）
楽天マーケットスピード2必須
マンゴー製作所が作成したスプレッドシートStock Price Valuation Calculator(君)ver1
表計算ソフトを使うことができる知能
Excelに代わるRSSとVBAの機能をフルバージョンで使用できる表計算ソフトウエアは今のところありません。
マーケットスピード2は楽天証券会員なら無料でダウンロードできます。
エクセルシートのダウンロードは一番下までスライドしてください。
動画版
一応動画版も作成しました。話し方がおかしいので普通に記事を読んだ方がいいと思います。睡眠導入剤に使いたい人は動画版をよろしくお願いします。
目次↓
1.Stock Price Valuation Calculator(君)ver1
仕組みについて
まずは、楽天マーケットスピード2のRSS情報を入手するためにアドインを有効化する必要がある。これに関しては前回の記事でも書いたとおりである。設定は、スライド1のようにファイルのオプションからアドインを指定ウすることができる。
アドインに関しては、楽天証券のヘルプを確認いただけると詳しい設定方法が掲載されているのでご確認いただきたい。
RSS情報の取得が完了したらスライド2の上のタブのマーケットスピード2をクリックして青枠で囲んだリボンの右端の接続ボタンをクリックする。これを行うとリアルタイムで株価が反映される。
2.ファンダメンタルシートの使い方について
会社情報の手入力
それでは、次にスライド3をご確認いただきたい。ここでは、取得されたRSS情報が赤枠に反映される。実際に接続ボタンを押すとそれに反応してリアルタイムで株価を表示してくれる。
ちなみに、このスライド3~4の青枠は手入力する必要がある。実際に使ったらわかるが、株価が自動出力されるので、白いセルに総資産、ＰＥＲ、ＰＢＲ、EV/EBITDA、配当利回り、売上高、営業利益、当期純利益を入力すると、純利益率、ＲＯＥ、自己資本比率などを計算する。
そして、①NOPATを計算するそこから②の議事配当総額を計算して、そこに自己資本を入力すると、③疑似ＲＯＥが計算される。
なぜ疑似なのかと言うと、これは将来の利回りを計算するための数値となる。
そこに、利回り数を掛けると、④将来の配当金が算出できるのでそこから⑤の利回りの成長率を計算する。それらの、数値をもとに将来の利回り予想をたて投資適格を判断するしくみだ。
以上をまとめるが、これらの項目を入力し利用者が割高割安を求めたい任意の会社が公開している情報や四季報から各項目を手動で入力してやると最終的に、左端の行に将来の利回りと投資適格が反映される。
※注意
分配利回りの項目について、分配利回りを基準に将来の利回りを予想して株価の割高割安を表示するので利回りが0％の場合は、利回りの列に0.0001と入力する必要があります。
3.株価のスプレッドシートについて
株価シートの使い方
お待ちかねのこの記事の本題に入るが前回のバージョン0.1から大きく見た目を偏向することになった。それはなぜかというと前回のバージョンは単純に作ったものなのでRSS情報を生かしきれていなかった。今回のバージョンは楽天のサンプルシートを参考に株価シートを作成した。
ちなみにスライド5の青枠で囲んだ部分に、証券コード、保有数量、平均取得価額、時価評価額、などを入力する必要がある。
また、評価損益額と、評価損益率に関しては順次説明していくので参考にしていただきたい。
まず、証券コードを入力すると、株価Cが表示されるので、自分の保有枚数Gを入力していただきたい。ここでは、100株とする。そして、その平均取得単価Ｈを入力していただきたい。
次に、時価評価額Iを計算する。時価評価額Iは、（株価×保有数量＝時価評価額）で計算することができる。これによって現在保有している株の価値を瞬時に計算することができ、さらにマケスピ２のRSSによってリアルタイムで株価を表示させることができるようになった。
評価損益額と評価損益率の計算式
ここからが重要なのだが、評価損益額の計算方法これに関しては単なる引き算なので、これを読んだ人はすぐに思い浮かんだかもしれないが、評価損益率の方は掛け算をして割り算をしてそれに100を掛け算した数値を%にして表示する必要がある。
まず、評価損益額だが、これは、時価評価額-平均取得額=評価損益額という単純な引き算で計算できる。この計算によって出てきた数値が利益であれば+で表示され、損失であれば-で表示される。
そして、評価損益率だが、まず、平均取得単価×保有数量＝取得価格を計算する。次に、先ほど計算した評価損益額を使って、評価損益額÷取得価格×100＝評価損益率を計算することができるようになる。計算方法を以下にまとめたので参考にしていただきたい。
・評価損益額の計算式
評価損益額J=時価評価額I-平均取得価格H
・評価損益率の計算式
評価損益率K=評価損益額J/(平均取得価格H*保有数量G)*100
これらをスライド6の赤枠に入力すると株価シートで現在の保有株の価値をリアルタイムで確認することができるようになる。さらにこのエクセルシートは、L~Xに株価の詳細な情報を表示させてくれるので現在株価がどれだけ上がっていたり、下がっていたりするのかを一目で確認できる。
それでは上記に書いた計算式をもとに実際に、スライド7のように入力して計算させてみたが正確に算出することができた。今回入力したのは、1475のIシェアーズＴＯＰＩＸのコードの、株価C、仮の保有数量G、仮の平均取得単価H、これらを入力し、時価評価額Iを株価Cと仮の保有数量Gで算出する。
そして、時価評価額Iから平均取得価格Hを引くと評価損益額Jを算出して、上記に書いた評価損益率Kの計算式を使って、最終的な利益率を表示することができた。
最後に、スライド8で、ファンダメンタルシートを確認していただきたい。1475IシェアーズＴＯＰＩＸの、コード番号、証券名称、株価、優先市場が藩兵されているのが、おわかりいただけただろうか？以上が、今回のExcelシートの変更点である。
このように、様々な機能を簡単に使うことができるのが、マケスピ２RSSとマイクロソフトofficeのExcelである。今のところこれらの機能がフルで使えるのはこの製品の組み合わせなので、ケチらずにExcelを購入していただきたい。
4.次回予告
株価シートのアップデート
次回は株価シートをより、使いやすくするために手動入力する場所を証券コード、保有数量、平均取得価格のみにして、証券コード入力ボタンと日付セルを追加する。
そして、拡張子をxlsxからxlsmに変更することになる。理由は、エクセルシートの更新でマクロやVBAの機能を組み込むからである。単純なものにはなるが、筆者もこれから勉強してより便利なエクセルシートを作成したいと考えている。
スライド9を見ていただければわかるが、手入力の必要な場所を減らすことにした。理由は、先ほどの③項で書いた計算式を使えばエクセルがすべて計算してくれるからである。
手入力が必要なのは、証券コード、保有数量、平均取得価格となる。これらを青枠に入力すると、赤枠に計算結果が表示される仕組みだ。
青、手入力
赤、前回から改良された部分
さらに、おまけとして証券コード入力ボタンの追加した。次の、スライド10を見ていただきたい。このボタンを押せば入力フォームが表示され、数値入力がテンキーとEnterで瞬時に入力することができ、いちいち開いているセルを探さなくてよくなった。
そして、スライド11のように時価評価額、評価損益額、評価損益率、などの計算をするために保有数量と平均取得単価を入力する必要がある次回のアップデートでは、このように少ない入力で済むように変更することになった。
青に保有数量と平均取得単価を入力することができる
さらにおまけとして、スライド12の黄色マーカーに日付と注意項目の追加
これで入力場所を間違えることも無くなっただろう。そして、緑枠では、今回の説明を生かして、少ない入力で計算を行うことができるようになった。
黄にコード入力ボタンと日付時刻と※書きが追加
以上の項目が、次回のExcelスプレッドシートver2の変更予定である。ちなみに次回から、拡張子がxlsmになるのでセキュリティに注意してご利用していただきたい。
5.スプレッドシートの配布
スプレッドシートのダウンロード
それではStock Price Valuation Calculator(君)ver1スプレッドシートを配布する。
この程度なら、自分で作れるという人はぜひそうしていただきたいが、できない人も多いので、今回はStock Price Valuation Calculator(君)ver1を無料配布するのでぜひこれを改造して使って頂きたい。そして、第3回の記事では、これをさらにバージョンアップしたものを公開するので、楽しみにしていただきたい。
※このエクセルシートにはマーケットスピード2のRSSを利用するためのExcelVBAが含まれています。それらを使用したことによるいかなる責任も筆者は負いません自己責任でお願いします。
次回
いいなと思ったら応援しよう！
