株価の評価をするExcelシート作成してみた1
はじめに
はじめまして、ご視聴ありがとうございます。最近、株価が急騰して投資の需要も高まっていると考えています。今回から、新シリーズをストックビジネスのマガジンで連載します。
その内容なんですが、タイトルにもある通り、株価の評価について、私がエクセルシートを作成したのでソレの使い方になります。それで使い方などをこちらにまとめて完成したものについて、noteで公開してくという記事になります。
ちなみに、なぜ筆者がこのようなエクセルシートを作ったのかと言うと、①ＰＥＲとかＰＢＲとかEV/EBITDAとかＲＯＥの数値を見ても割高割安の判断が難しいこと、②株価のリアルタイムで表示させ、値幅や相場情報をわかりやすくしたいから、という二つの目的があります。
必要なもの
マイクロソフトofficeのExcel必須（Excelが必要な理由はRSSとVBA）、楽天マーケットスピード2必須、マンゴー製作所が作成したスプレッドシートStock Price Valuation Calculator(君)ver0.1、表計算ソフトを使うことができる知能
エクセルシートのダウンロードは一番下までスライドしてください。
Excelとマーケットスピード2で株価を分析する方法①
この記事に関する動画を作成しました閲覧よろしくお願いします。
1.Stock Price Valuation Calculator(君)ver0.1
ＳＰＶＣ君ver0.1
まず、この「Stock Price Valuation Calculator(君)ver0.1」とは、なんなのかというと、スプレッドシートのファイル名である。次のスライドを見ていただきたいが、起動すると一番目のシートであるファンダメンタルという画面が表示される。
ここではまだ、楽天マーケットスピード２を起動していないので、左側の部分に何も表示されていないが、マーケットスピードに接続すると、株価、証券コード、会社名、優先市場、その他が表示される。
仕組みについて
このシートの仕組みだが、マーケットスピード２からＲＳＳ情報を取得しその情報から、株式会社の公開しているＰＥＲ情報や売上高そして配当利回りなどから、ファンダメンタルによる分析を行うことができるスプレッドシートである。
このシートで株価の割高割安を計算する場合に、入力する必要があるのが、白いセルの部分である。これについて右から順序良くあげると、総資産、ＰＥＲ（会社予想）、ＰＢＲ、EV/EBITDA、配当利回り、売上高、当期純利益、自己資本、などの手動入力が必要だ。
※配当利回りを基準に株価を評価するので、配当利回りが0の場合は正しく評価を行うことができないので、仮に0.0001と入力する必要がある。
電子書籍☝
2.ファンダメンタルシートの使い方について
アドインの設定
このシートの使い方であるが、次の画像シートを確認していただきたい。まず、エクセルのファイルを開き、アドインを実行する。指定するのは、お使いのPCのビットに対応したRSS情報の設定である。下記のように、Marketspeed2_RssにRssとVbaがあるので両方を選択してＯＫボタンをして使用可能にしていただきたい。
ちなみに、この設定を行わないと自動で株価を取得することはできないので注意していただきたい。RSSが設定されると、上のタブにマーケットスピード２というタブが表示され、それをクリックすると、左端のリボンに接続ボタンが表示されるのでそれをクリックすると、マーケットスピード２と通信を行う仕組みとなっている。
ここで、アドインの設定ができない場合は以下のサイトを参考にしていただきたい。重要なので再度書くが、Excelが必須な理由はRSS情報の取得とVBRなどのプログラミングが使えることである。
実際に、接続ボタンを押すと、次の画像シートように株価情報が反映される。
おわかりいただけただろうか？画面右端に、将来の利回り予想と投資適格が反映されている。また、右側の白いセルの部分は適当に入力したものであるが、これを正確に入力することで、割高割安の判断を瞬時に行うことができるようになった。
計算の説明
ちなみに、どのような計算をしているのかと言うと上記のスライドを見ていただきたいが、まず株価が自動出力されるので、そこに総資産、ＰＥＲ、ＰＢＲ、EV/EBITDA、配当利回り、売上高、営業利益、当期純利益を入力すると、純利益率、ＲＯＥ、自己資本比率などを計算する仕組みだ。
そして、①NOPATを計算するそこから②の議事配当総額を計算して、そこに自己資本を入力すると、③疑似ＲＯＥが計算される。なぜ疑似なのかと言うと、これは将来の利回りを計算するための数値となる。そこに、利回り数を掛けると、④将来の配当金が算出できるのでそこから⑤の利回りの成長率を計算する。
それらの、数値をもとに将来の利回り予想をたて投資適格を判断するしくみだ。これについてはより正確に判断できるように改善していこうと考えている。
電子書籍☝
3.株価のスプレッドシートについて
株価シートの使い方
次に、株価シートの使い方について説明するが、次のスライドシートを見ていただきたい。マーケットスピード2に接続すると、証券コードや株価それらに関連する情報が出力される。
今回は、ver0.1なのでこれらを手作業で入力する必要があるのだが、次回ではこれを証券コードの入力だけで簡単に使えるようにしたい。
検索と置換
ここでExcelの便利なデータの入力方法をご紹介しよう。まず、タブのホームをクリックし、変更したい列を指定する。今回は、下のスライドの青いマーカーの部分に注目していただきたい。この列の中の証券コードを変更したい。そこで、リボンの検索と選択から検索と置換を表示して、検索する文字列(N)：9432を入力し、次に、置換後の文字列(Ｅ)：1475を入力して、全て置き換え(Ａ)をクリックする。
すると、次のスライド画像のように9件の同じ行のセルをまとめて置換することができる。これを使えば、大量のデータの特定の部分をまとめて置換することができるようになるが、この機能でいちいち変更するのはめんどくさいので次回改良したスプレッドシートを次のマガジン記事で公開することにする。
それでは、ファンダメンタルシートに戻って、証券コードとなどが正しく入力されているか確認する。
このように、1475のiシェアーズＴＯＰＩＸＥＴＦに正しく更新されていた。
このようにExcelでは数値を入力することでセルの計算を自動で行ってくれるのである。これは非常に便利な機能であり、我々素人にはわかりにくいPERとかPBRとか利回りからの判断を瞬時に行うには最適なツールとなった。
4.次回予告
株価シートのアップデート
次回は、株価シートのアップデートを行っていく、次のシートを確認していただきたい。次の、記事では、コード入力をすると、銘柄名、株価、時刻、前日比(円)、前日比率、を自動で算出し、ＬからＸまでの情報を出力し、年末以来の高値と安値そして市場部名称を表示できるようにする。
さらに色を付けて割高割安がテクニカルでわかりやすくなるようにアップデートする。
ver1は非常に便利になったので期待してほしい。
5.スプレッドシートの配布
スプレッドシートのダウンロード
それではStock Price Valuation Calculator(君)ver0.1スプレッドシートを配布する。
この程度なら、自分で作れるという人はぜひそうしていただきたいが、できない人も多いので、今回はStock Price Valuation Calculator(君)ver0.1を無料配布するのでぜひこれを改造して使って頂きたい。そして、第2回の記事では、これをさらにバージョンアップしたものを公開するので、楽しみにしていただきたい。
※このエクセルシートにはマーケットスピード2のRSSを利用するためのExcelVBAが含まれています。それらを使用したことによるいかなる責任も筆者は負いません自己責任でお願いします。
次回
いいなと思ったら応援しよう！
