エクセルで日経平均株価構成銘柄の寄与度などをリアルタイムに取得
本日は日経平均株価を、それを構成している銘柄から算出してリアルタイムに取得します。
また、それを応用して日経平均に対するそれぞれの銘柄の寄与度もリアルタイムに取得します。
リアルタイムなので楽天証券の楽天RSSを使用します。Realtime Spread Sheetを起動し、マーケットスピードにログインしておく必要があります。
日経平均株価は225銘柄で構成されております。
かといって平均株価は銘柄の株価を全部たしてそれを225で割るわけではありません。
みなし額面、除数という、普段はあまり気にしない値を使用します。
日経平均プロフィル（https://indexes.nikkei.co.jp/nkave）にて確認することができます。
今回はリアルタイムに取得することが目的なので詳しい用語の説明は割愛しますが、覚えておいて損はないです。
みなし額面のCSVファイルも日経平均プロフィルからダウンロードできます。
ただし、値に円が付いていたり、計算式だけになっている銘柄もあるのでそこらへんは自分で修正しておきました。エクセルの計算に使うので数値だけが必要です。
今回は直接ダウンロードせず、エクセルにてWEBからリンクを取得してデータを取得することにしました。リンクアドレスに変更がなければ「更新」にて最新の情報を取得することができます。
それでは各銘柄の現在値を取得します。各構成銘柄には「採用株価」というものがあり、次の計算式で算出できます。採用株価はみなし額面50円あたりの株価ということです。
採用株価＝50×（現在株価÷みなし額面）この採用株価を除数で割ります。小数点以下３位を四捨五入するのが規則ということです。
これを225銘柄全部に適用し、全部たしたものが日経平均株価になります。
せっかくなので各採用銘柄の寄与度も計算してみます。
それには前日の株価が必要ですがRSSで取得可能です。先ほど構成銘柄の採用株価を算出し、除数で割りました。今度は前日の終値に対して同じ作業を行います。除数、みなし額面も前日のものを使う必要がありますが、便宜上、現在のものを使用します。この作業を225銘柄に行います。
これで前日と現在のデータがそろいました。それぞれの銘柄で採用株価を除数で割ったものに対して「現在引く前日」をしたものが寄与度になります。
ですので前日の採用株価を除数で割ったものをすべてたせば、前日の日経平均株価をだすことができます。225銘柄の寄与度全部たせば日経平均の前日比が出ることにもなります。
エクセルファイルで自分で入力するところは除数です。また採用銘柄に変更があった場合コードだけを修正して簡単なマクロを実行するだけです。10月1日に9434のソフトバンクが採用され、4272の日本化薬が除外されます。
今回の記事資料