06
24
25
26
27
28
29
30
   

日経平均の構成に従って現物株を購入するにはいくら必要? その9

キャプチャ
図1:50円÷みなし額面を計算したシート

その8」までで、「みなし額面」と「株価」と「単元株」の一覧を作って「50円÷みなし額面」を計算しました。


日経平均株価の構成と、売買単位(単元株)がわかりましたので、ここから日経平均株価の構成で現物株を購入するための最低必要株数を計算します。

そもそもこの計算は、FISCOさんに納品する記事に数字を入れたくて始めたことです。でも、最初はこの部分を勘違いしていて、FISCOさんに指摘してもらいました。面目ない。

ということで、もきち♪は信用ならないのでエクセルに計算してもらいます。

--みょ もきち♪は信用できないみょ エクセルに任せたほうがいいみょ

うん。でも、エクセルを操作しているのはもきち♪だったりするのですが、気にせず進めます。

きっとちゃんとした計算方法があるとは思いますが、ちょっとお行儀の悪い方法で進めます。


お行儀の悪い計算なので、一気に行きます。

まずは、計算する列を追加します。

キャプチャ
図2:計算列の追加

必要株数:日経平均株価と同じ構成比率で購入するときに必要となる株数です。

必要株数=みなし額面50円株の株数×50円÷みなし額面

単元数:その銘柄で何単元買えばいいかを表示しています。小数点以下は切り捨てています。エクセルでは「QUOTIENT」関数を使います。

単元数=必要株数÷単元株 の整数部分

余り:その銘柄を単元数分購入してまだ足りない株数です。エクセルでは「MOD」関数を使います。

余り=必要株数÷単元株 の余り

倍数:「余り」を単元株単位で購入するためには何倍の購入をすればいいのかを表示します。

倍数=単元株÷余り

必要金額:「株価」に「必要株数」をかけています。

必要金額=終値×必要株数

倍数の最大値:倍数の最下セルに倍数の最大値を表示しています。「みなし額面50円」株の「必要株数」を倍数の最大値倍して再計算して、この数字がゼロになるまで繰り返します。

念のために、「余り」の列に数字が入っているセルを赤く表示して、赤いセルがなくなることを確認してダブルチェックしています。


結局。みなし額面50円の銘柄を2000株購入すると、日経平均株価の構成で現物株を購入することができて、2017年6月2日終値で、必要な総額は「約10.6億円」となりました。

--みょ 合っているの?

う~ん。まず、すべての銘柄で単元株の整数倍になっていて、売買単位1000株でみなし額面50円の銘柄がありますし、<5214> 日本電気硝子は3単元、<8630> SOMPOホールディングスは5単元と素数になっていますので、公約数はありません。最低必要数ということでよさそうです。

みなし額面50円株換算で2000株は必要です。

そして、2017年6月2日現在の除数「26.301」から計算した日経平均株価は「20,177.28 円」となって、公表されている日経平均株価と一致します。

キャプチャ
図3:結果と検算

--むにゃ 良さそうだみょ


ちょうど日経平均が2万円を超えてきました。このシリーズの最終回で2万円越えというのも何だか感慨深いものがあります。

今回日経平均採用銘柄の構成で現物株を購入するには、みなし額面50円株を2000株購入する必要があることが分かりました。

一度計算しておけば、構成銘柄やみなし額面や単元株が変わらなければ、日経平均株価算出式を逆にたどって、日経平均株価×除数×2000で現物株を購入するための最低必要額が算出できるということになります。

とは言っても、単元株は100株への統一が進んでいますし、銘柄入れ替えがありますので、やっぱり「今は」約10億円必要ということのようです。

日経平均の構成に従って現物株を購入するにはいくら必要? その8

キャプチャ
図1:「みなし額面」と「終値」を合体した一覧表

その7」で「みなし原価」と「終値」を1つの表に合体できました。


ここで、「その1」で確認した日経平均の計算式をおさらいします。

日経平均株価=構成銘柄の採用株価合計÷除数 ---(1)

構成銘柄の採用株価合計=株価×50(円)÷みなし額面(円) ---(2)


ここで、今回は実際に日経平均株価を計算するわけではないので、式(1)は不要です。よって、「除数」も不要です。

ただし、実際に購入する場合の総額を知りたいのですから、同じ構成ですべての構成銘柄が購入できる単位である必要があります。株は「売買単位」が決まっています。「売買単位」の整数倍しか購入できないのです。


ということで、「売買単位(単元株)」が必要になります。現在。売買単位100株への統一化が進んでいて、現在は100株と1000株の2種類まで統一が進んでいます。

まだ売買単位1000株の銘柄もあるのです。ということで、「売買単位」の一覧も合体します。「単元株」は「日本株全銘柄の株価一覧表」のサイトからダウンロードできます。

「投資指標データ」の「日経225指標データ」を選択して下段にある「Save to CSV」をクリックします。

キャプチャ
図2:日本株全銘柄の株価一覧表

ダウンロードした一覧表から「単元株」をコピペします。

キャプチャ
図3:「みなし額面」と「終値」と「単元株」一覧


必要なデータがそろいましたので、計算に入ります。

まずは、「みなし額面」を50円に統一するための係数「50円÷みなし額面」を計算します。

例として「F1」セルに「50円÷みなし額面」を入力して「F2」セルに「=50/C2」を入力して下までオートフィルで埋めていきます。

キャプチャ
図4:50円÷みなし額面の計算


その9」で日経平均の構成比率に沿った現物株を購入するための計算に進みます。

日経平均の構成に従って現物株を購入するにはいくら必要? その7

キャプチャ
図1:日経平均構成銘柄終値

その6」で証券コードと終値の表が作成できました。


その6」では、日経平均採用銘柄終値一覧をダウンロードできるサイトは見つからないと書きましたが、その後あっさりと見つかりました。

それも、いつもお世話になっている「日本株全銘柄の株価一覧表」のサイトでした。使い方を知らなかっただけです。情けない。

図1のように、トップメニューの「株価一覧表」から「・日経225構成銘柄」を選択するだけです。そのまま下段にある「Save to CSV」をクリックするだけでダウンロードできます。

キャプチャ
図2:日経平均採用銘柄終値一覧

--みょ みなし額面もあるみょ

うん。でも、小数点以下1桁までしかありませんので、3倍しても整数になりません。このみなし額面を使うなら、分数になっているみなし額面は手直しが必要です。


ということで、こういうものもあるということを踏まえたうえで進めたいと思います。

「みなし額面」のシートでは、「みなし額面」が2列表示されていますので、「数値」になっているほうの列をコピーして、値のみで上書きして1列にしておきます。

「D1」セルに「終値」を入力して「D2」セルをクリックして数式入力窓左の「fx」をクリックして、「関数の挿入」ウィンドウで「関数の検索」窓に「VLOOKUP」を入力して「検索開始」ボタンをクリックします。

キャプチャ
図3:「関数の挿入」ウィンドウ

「VLOOKUP」を選択して「OK」ボタンをクリックします。

「関数の引数」ウィンドウで、「検査値」には「みなし額面」シートの「コード」を、「範囲」には「終値」一覧のコードから終値までを全行で指定します。ここで、下までコピペしますので「F4」キーを押して「絶対参照」にしておきます。

「列番号」は「終値」一覧の「コード」から数えた「終値」の列数を、「検査方法」は「一致する値だけ検索」する「FALSE」を表す「0」を指定して「OK」ボタンをクリックします。

セル右下の「■」を下までドラッグしてオートフィルで埋めていきます。

キャプチャ
図4:終値を抽出

「みなし額面」と「終値」が合体できました。ただ、このままでは「終値」は毎回「VLOOKUP」で抽出されていて、「終値」のシートと関連付けられていて扱いにくいので、「終値」列をコピーして「値」で上書きして計算式を消しておきます。

キャプチャ
図5:合体した一覧表


「みなし額面」と「終値」が合体できました。

その8」で日経平均の構成で現物株を購入する計算に入ります。

日経平均の構成に従って現物株を購入するにはいくら必要? その6

キャプチャ
図1:「数値」に変換された「みなし額面」

その5」で「みなし額面」を数値に変換しました。


続いて終値を取得していきます。

例えば「マネックス証券」のトレードツール「Tradestation(トレードステーション)」を使うと、日経平均構成銘柄の終値一覧をダウンロードできます。でも口座開設が必要ですので、ここでは無料で会員登録も不要なサイトで進めます。

個人的に日経平均採用銘柄の終値が取得できる方法を持っている人は、もちろんそちらを使うほうが楽です。


無料で会員登録なしで日経平均採用銘柄の終値一覧をダウンロードできるサイトは残念ながら見当たりませんでしたので、すべての上場銘柄の4本値をダウンロードしてそこから必要なデータを抽出します。

国内株式の4本値をダウンロードできるサイトは、以前「会員登録とかしなくても相場関連データがダウンロードできるありがたいサイト 一覧」でまとめた以下の2つが使いやすいかと思います。

◆株価データサイト k-db.com
◆日本株全銘柄の株価一覧表


今回は1つ目の「◆株価データサイト k-db.com」で進めます。

全銘柄の4本値を表示するには、トップページの「個別株」をクリックします。

キャプチャ
図2:◆株価データサイト k-db.comトップページ

全銘柄の4本値が表示されますので、「日足」になっていることを確認して、「CSVダウンロード」をクリックします。

キャプチャ
図3:4本値のダウンロード


デスクトップなど適当な位置にダウンロードして表示します。

キャプチャ
図4:全銘柄の4本値

よく見ると「コード」が「1301-T」のように証券市場が付いた形になっています。このままでは検索できませんので、以前「エクセルが7203 と 7203-T が違うと言いはって困ります その3」でやったように「1301」のように4桁に修正します。


今回必要なのは「終値」だけで、証券コードがあれば検索可能です。縁起物として銘柄名は残すとしても、あとの列は不要です。バッサリと削除してしまいます。

キャプチャ
図5:不要な列を削除

すっきりしました。

ファイルをいくつも作るのも何ですので、シートを「みなし額面」一覧のファイルに移動して保存しておきます。シートのタブで右クリックして「移動またはコピー」を選択して移動しておきます。


その7」で「みなし額面」と「終値」を合体していきます。


15:45 追記:日経平均採用銘柄の終値をダウンロードできることが判明しました。後日詳しく紹介します。

日経平均の構成に従って現物株を購入するにはいくら必要? その5

キャプチャ
チャート1:不要な行を削除したシート

その4」までで、「みなし額面」に必要な行がそろいました。


ここから「みなし額面」を計算できる形に変えていきます。

まずは、ざっくりと表を見てみます。よく見ると、社名がいくつかのセルに分割されている銘柄があります。

キャプチャ
図2:社名が複数セルに分割されている銘柄

これは、スペースでセルを分割したために、社名にスペースが入っている銘柄ではセルが分割されてしまっているのです。「みなし額面」の列に社名の一部が含まれていると計算できません。

これを「社名」を修正しようとすると「=B85&" "&C85&" "&D85&" "&E85」とかで一度結合して「値のみ」でコピペしてってことになります。でも、コードはありますし、今回は社名自体は重要ではないので、「みなし額面」の列にそのまま数字をコピペしてしまいます。

--みょ 乱暴だみょ

うん。それなりにちゃんとした社名が必要なら「終値」と一緒に株価一覧表から「VLOOKUP」関数なりで抽出すればいいのです。この表はあくまでも「みなし額面」が重要なのです。

今回は225銘柄ですので、スクロールしながら探せますが、データが多い場合は「D」列で「Ctrl」キーを押したまま「↓」キーを押すと入力されているセルが簡単に探せます。


「みなし額面」列ができましたが、まだ「文字列」になっています。この「文字列」を「数値」に変換していきます。ここで、分数表示が無ければ「=C2*1」とかで変換できますが、分数表示があるとうまくいきません。

--みょ そもそも分数があるからややこしいんだみょ 分数が数字にならなきゃ意味がないみょ

うん。そこで、少しだけマニアックな世界に足を踏み入れます。ちょっとだけです。

--みょ マニアック?

うん。きっと教科書とかには載っていない方法です。ひょっとするとバージョンアップとかで利用できなくなるかもしれません。よい子は正攻法で進めたいのですが、正攻法だと少しややこしいのです。

ということで、「=」を追加して「=」を「=」に置換します。

--みょ 何が違うの?

いや。同じ「=」ですが、エクセルは「=」に置換されたら計算したくなる習性を利用します。

「D2」セルに「="="&C2」を入力します。「C」列の最初に「=」を追加しています。そのまま下までドラッグしてオートフィルで埋めていきます。

キャプチャ
図3:「=」を追加

分数だってばっちりです。でも、あくまでも文字列です。数値に変換します。

「D」列の列頭文字「D」を選択して「D」列すべてを選択します。右クリックして「コピー」を選択します。そのまま右クリックで「貼り付けのオプション」で「値」を選択して上書きする形で貼り付けます。

キャプチャ
図4:「値」で上書き

「D」列全体を選択して「ホーム」タブの(「編集」にある)「検索と選択」の「置換」をクリックします。

キャプチャ
図5:置換

「検索と選択」ウィンドウで「置換」の「検索する文字列」入力窓に「=」を、「置換後の文字列」入力窓に「=」を入力します。繰り返しになりますが、同じものです。

キャプチャ
図6:検索と選択ウィンドウ

「すべて置換」ボタンをクリックすると、分数も含めて「数値」に変換されます。

キャプチャ
図7:「数値」に変換された「みなし額面」

--みょ マニアックだみょ ややこしいことは考えずに、セルを分割して日付になっているところを手直しするみょ

うん。今回は225行とデータも少ないので、それが正解かもしれません。


「みなし額面」の一覧表が作成できました。「その6」で「終値」を抽出していきます。

プロフィール

もきち♪

Author:もきち♪
個人事業主ですが株式投資のほうが主体になっています。

投資スタイルは逆張りナンピン。チキンになりきれないひよこ投資家™でピヨピヨトレードです。

2007年に投資信託を始めて、2009年に国内株式の個別銘柄投資を始めました。

中小企業診断士(診断業務休止中)でオンライン情報処理技術者です。

ブログでは株式投資とコンピュータの話がメインになるかな?

きほんゆるめに。。。

【FISCOソーシャルレポーター】ってのに公認されました。


キャプチャ
もきち♪への直通メール

スピンオフサイト

ブログ内リンク

全ての記事を表示する
データをダウンロードできるサイト一覧

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事

ランキング




経済ニュース












株式会社ゴゴジャン NTT光回線キャンペーン×Forex Racco V2


使っている証券会社

スポンサーリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

クラウドソーシング「ランサーズ」

広告