05
   

日経平均の構成に従って現物株を購入するにはいくら必要? その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」で「終値」を抽出していきます。

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

キャプチャ
図1:データ形式「文字列」でセルを分割

その3」でスペースを利用してセルを分割しました。


セルに分割しましたが、まだ不要行が残っています。このままでは見にくいのと、やっぱり扱いにくくなります。

ということで、不要行を削除します。

表をよく見ると、1行目に表全体の見出しとなる「日経平均株価みなし額面一覧」と書かれていて、2行目に列見出しとなる「コード」などが入力されています。

2行目の列見出しはそのまま使えますので、まずは1行目全体を選択して右クリックで削除します。

次に、行頭文字の「1」の上、列頭文字の「A」の左をクリック(1)してシート全体を選択して「データ」タブ(2)の「並べ替え」(3)をクリックします。

「並べ替え」ウィンドウで「先頭行をデータの見出しとして使用する」(4)にチェックを入れます。「最優先されるキー」(5)を「コード」に指定して、「順序」を「降順」(6)に指定して「OK」(7)ボタンをクリックします。

キャプチャ
図2:並べ替え

不要な行が、2行目以降に表示されますので、不要行の行頭文字をドラッグして右クリックで「削除」します。

キャプチャ
図3:不要行の削除

この後「VLOOKUP」関数を使います。「VLOOKUP」関数ではキーとなるデータが「昇順」に並んでいる必要がありますので、同様に「コード」の「昇順」に並べ替えておきます。(図2の(1)~(7))


その5」で「みなし額面」の列を整えていきます。

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

キャプチャ
図1:コピペしたエクセルファイル

その2」でPDFデータを1銘柄1行のエクセルデータにできました。


でも、1銘柄1セルに入っています。このままでは計算できません。ということで、「証券コード」と「銘柄名」と「みなし額面」にセルを分割します。

とはいっても、ややこしい計算式を使うのは面倒です。そこで、セル中で区切りに使われているスペース「" "」でセルを分割することにします。

「A列」の列見出し「A」を選択して「A列」全体を選択します。

「データ」タブの「区切り位置」をクリックします。

キャプチャ
図2:区切り位置

今回はスペース「" "」で分割します。「区切り位置指定ウィザード」で1ページ目はそのまま「次へ」ボタンをクリックします。

2ページ目では区切り文字を指定します。ここで、「スペース」にチェックを入れます。「タブ」のチェックはあってもなくてもいいです。

「データのプレビュー」窓でスペースの位置に区切りが表示されているのを確認して「次へ」ボタンをクリックします。

キャプチャ
図3:区切り位置指定ウィザード 2/3

3ページ目では分割した後の「列のデータ形式」を指定します。


ここで一つ注意することがあります。「みなし額面」は計算のもとになるセルですので「数値」にする必要があります。でも、よく見ると「25/3」のように、分数表示になっているものがあります。

初期設定の「G/標準」のままだと、この分数表示「25/3」を「日付」と解釈して「3月25日」とかになってしまいます。

キャプチャ
図3:データ形式「G/標準」でセルを分割

一度日付と解釈されると少し厄介なのです。そこで、まずは「文字列」に指定して分割後に数字に変えていきます。

「区切り位置指定ウィザード」の3ページ目で3列目の見出しになっている「G/標準」をクリックして列を選択します。次に「列のデータ形式」を「文字列」に指定して「完了」ボタンをクリックします。

キャプチャ
図4:データ形式を「文字列」に指定

分数表示の「みなし額面」も安心の文字列表示です。

キャプチャ
図5:データ形式「文字列」でセルを分割

上手に分割できたようにも見えますが、このままでは計算ができません。


ということで、「その4」で計算できる形に整えています。

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

キャプチャ
出所:日経平均プロフィル 関連データ

その1」で進むべき道が確認できました。


日経平均株価の構成銘柄をリストアップする方法はいくつかありますが、今回はそれぞれの「みなし額面」が必要になります。そうなると、やはり日経平均プロフィルのサイトからリストアップするのが良さそうです。

ということで、日経平均プロフィルの関連データにある「みなし額面一覧(PDF)」をクリックします。

みなし額面一覧がPDFで表示されます。この一覧を、セルに分割してエクセルに取り込むことになります。

--みょ コピペすればいいみょ

う~ん。それでは1セルにすべて詰め込まれてしまいます。ダメでした。

そこで、一度テキストファイルにして、エクセル側でセルを分割します。ということで、あらかじめ「メモ帳」を開いておきます。

ちなみに「メモ帳」はすべてのWindowsにおまけでついています。Windows10だと「Windowsアクセサリ」に入っています。念のため。

キャプチャ
図1:メモ帳


PDF内で一度クリックして「Ctrl」キーを押したまま「A」キーを押してPDF内のテキストをすべて選択します。

キャプチャ
図2:みなし額面一覧を全選択

選択した状態で「Ctrl」キーを押したまま「C」キーを押してコピーします。

「メモ帳」を選択して「Ctrl」キーを押したまま「V」キーを押します。

キャプチャ
図3:コピペしたメモ帳

少しお行儀が悪いのですが、そのまま「Ctrl」キーを押したまま「A」キーを押して「メモ帳」の内容をすべて選択して、「Ctrl」キーを押したまま「C」を押してコピーします。

そのままエクセルで新しいシートを開いて、「A1」セルを選択して「Ctrl」キーを押したまま「V」を押して貼り付けます。

--みょ なんだかとってもお行儀が悪いみょ

うん。お作法に従うのなら「CSV」形式で保存してダブルクリックです。

--みゅぅ お行儀が悪くてもいいみょ

うん。おなかに入れば一緒です。エクセルにしてしまえば一緒なのです。

キャプチャ
図4:コピペしたエクセルファイル


銘柄のデータを1行にできましたが、よく見ると、1銘柄1セルになっています。

これでは使えないので「その3」でセルに分割していきます。

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

キャプチャ
図1:日経平均採用銘柄のみなし額面

日経平均の構成に従って現物株を購入した場合に必要となる資金はいくらになるのでしょう。


きっかけは、FISCOソーシャルレポーターの記事を書くのに、日経平均を現物株で買う場合にいくらになるのか知りたかったのです。

日経平均株価の構成銘柄と算出方法は、「日経平均プロフィル」のサイトで公開されています。

キャプチャ
出所:日経平均プロフィル 関連データ


「算出要領」に日経平均株価の計算方法が記載されています。

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

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


ということです。ここで、計算したいのは日経平均株価ではなくて、日経平均株価と同じ構成比率で現物株を購入する場合の合計額ですので、式(1)は計算する必要がありません。とういことで、「除数」は不要です。

必要なのは式(2)の「株価」と「みなし額面」となります。


株式の額面制度は廃止となっていますが、日経平均株価は額面制度が残っていた時から連続して比率が決められています。株式指数は連続性が重要なのです。突然比較できなくなると困ってしまいます。

ということで、銘柄ごとに「みなし額面」が決められています。「みなし額面」も「日経平均プロフィル」のサイトで公開されています。PDFで。

--みょ PDFだと扱いにくいみょ

うん。PDFからエクセルなどの表計算データに変換しないと扱いにくい。ということで、エクセルに変換したのが図1です。

みなし額面50円となるように調整した株価を「採用株価」としています。

この「みなし額面」と「株価」から必要な資金が計算できます。


その2」から実際の計算に進みます。

とはいっても、ほとんどがデータの抽出作業になります。少しマニアック(?)な内容に踏み込むかもしれません。

エクセルが7203 と 7203-T が違うと言いはって困ります その3

キャプチャ
図1:VLOOKUP関数で「?」を使った銘柄名の表示

その2」でVLOOKUP関数を使ってセルの値の一部が一致している行の銘柄名を表示できました。


でも、すべての行でVLOOKUP関数を実行するとエクセルだって大変です。大量のデータを処理しようとすると、やってらんないと言ってすねます。「応答なし」ってやつです。

そこで、検索対象のデータ「1301-T」から「1301」を取り出すことを考えます。

このデータを取り出す方法も、例えば「LEFT」などの関数を使う方法などいくつか考えられますが、関数を使ったのでは結局エクセルの負荷を減らしたことになりません。


今回は、必要な証券コードの後に、必ず「-」が付いています。この「-」以降を消したいのです。

そして、証券コード単独だとエクセルは「数字」として扱います。でも、もとになっているデータは「-T」などが付いているために「文字列」として扱われています。

「文字列」から「文字列」の操作で証券コードを取り出しても「文字列」として認識されます。「数字」と「文字列」は中身が一緒でもエクセルは別物として扱われて抽出できません。

そこで、「-」以降を消して、エクセルに「数字」として認識させることを考えます。


以前SBI証券のHYPER SBIからダウンロードした4本値の「20170525」といった数字8文字の日付を「2017/5/25」といった日付データに変更するのに「区切り位置」を利用しました。

これは一度実行すれば繰り返し計算は必要ありません。エクセルだって安心です。今回は必要なデータと不要部分の区切りとして「-」がありますので、区切り位置とするにはもってこいです。

ということで、「コード」が入っている「A列」の区切り位置を変更して4桁の証券コードを取り出します。

「A列」の列頭文字「A」をクリックして「A列」全体を選択します。「データ」タブの「区切り位置」をクリックします。

「区切り位置指定ウィザード」が起動します。このウィザードは3画面に分かれていて、それぞれの画面下部にその時々のプレビューが表示されています。

キャプチャ
図2:区切り位置

1画面目は初期設定でいいので、「次へ」ボタンをクリックします。2画面目では「区切り文字」を指定します。

ここで、初期状態でチェックが入っている「タブ」のチェックを外して、「その他」にチェックを入れて「-」を入力します。

プレビュー画面で「-」が消えて区切り位置が表示されたら「次へ」ボタンをクリックします。

キャプチャ
図3:区切り文字の指定

3画面目で区切った後のデータ形式が指定できます。ここで、「T」とかが入っている2列目を選択して「削除する」を指定して「完了」ボタンをクリックします。

キャプチャ
図4:区切った後のデータ形式選択

ここで「削除する」を選択しておかないと、右の列を上書きしてしまうことになります。

キャプチャ

図5:右の列を消すかどうかの確認

4桁の証券コードを「数字」として取り出すことができました。

キャプチャ
図6:4桁の証券コードを取り出した一覧表


エクセルに「7203」と「7203-T」とが一緒だよって教える方法はいくつかあります。

どれが優れているというのではなくて、用途によって使い分けるのがいいかと思います。

ということで、当初の目的だった「日経平均の構成に従って現物株を購入した場合に必要となる資金はいくらか」というのもそのうち記事にしようと思います。

エクセルが7203 と 7203-T が違うと言いはって困ります その2

キャプチャ
図1:「-T」を付加して検索した場合の札証・福証

その1」で、「VLOOKUP」関数の「検査値」に「-T」を付けて全銘柄一覧表からピックアップすることができました。


でも、「-T」は東証を表す記号ですから、他の証券取引所の銘柄だと図1のように銘柄名を表示できません。

--うに んでも日経平均構成銘柄は東証のデータだけでいいみょ

うん。今回はそうですが、そのままにしておくと気持ちが悪いので、もう少し汎用的な方法を考えます。

ここで、証券取引所を含めた証券コードの構成をもう一度確認します。


元のコードは、「数字4桁+"-"+取引所を示すアルファベット1文字」で構成されています。これを、数字4桁のみで抽出しようとしています。

この形式で書かれているのですから、数字4桁のみで検出する方法はいくつか考えられます。

まずは、「その1」でも利用した、「検索値」にその後の取引所を表す文字列を付加する方法です。

--みょ でも全部の取引所を指定するのは大変だみょ

うん。一つずつ追加する方法もありますが、そうではなくて、どんなアルファベットでも抽出する方法があります。


それも、「VLOOKUP」や「Microsoft Excel」や「Windows」だけに限定されずに、多くのコンピュータで文字列を扱う場合に活用できるお約束です。

それが「正規表現」です。

--みょ 正規だみょ ちゃんとしたやつだみょ

うん。「正規表現」をちゃんと理解するのはちょっと大変ですが、入り口からちょっと覗くだけでも便利です。それが、「?」と「*」です。

これらもちゃんと理解するのは少し厄介ですが、基本的な使い方は「?」は任意の一文字、「*」は任意の文字列を表します。

例えば「7203-T」を検出するのに「7203-?」、「7203??」、「7203*」が使えるということです。

--みょ 「*」だったら何でも検出できるみょ

うん。その代わり、意図しない文字列も一緒に検出してしまいます。今回は書式が決まっていますので「-?」を使うのが良さそうです。

やることは、「-T」の代わりに「-?」を入力するだけです。

ということで、例えばVLOOKUP関数で銘柄名を表示する計算式は「=VLOOKUP(J2&"-?",$A$2:$B$4029,2,0)」となります。

キャプチャ
図2:VLOOKUP関数で「?」を使った銘柄名の表示


正規表現は便利なのですが、毎回処理が必要になります。データが増えてくると、いかにエクセルといえどもやってられなくなって処理が重くなってきます。

ということで、「その3」で元になっている「7203-T」から「7203」を取り出す方法を確認します。

エクセルが7203 と 7203-T が違うと言いはって困ります その1

キャプチャ
図1:ダウンロードした個別銘柄株価データ

例えば日経平均の構成に従って現物株を購入した場合に必要となる資金はいくらか計算しようと思ったときに、225銘柄それぞれの終値を調べるのって大変です。とてもやってられません。


そんな時には、とりあえず上場している全銘柄の4本値を一括してダウンロードして、日経平均採用銘柄だけピックアップしたくなります。

全銘柄の4本値は、「株価データサイト k-db.com」とか、「日本株全銘柄の株価一覧表」とかのサイトから一括ダウンロードすることができます。

ということで、「株価データサイト k-db.com」からダウンロードしたのが図1です。

誰でも無料で会員登録することもなくダウンロードできます。ありがたいことです。


でも、このデータでは「コード」が「1301-T」のように、証券取引所が付記されています。

ありがたいのですが、このままだとエクセルで必要なデータをピックアップしようとすると「1301-T」と「1301」は違うみょって怒られちゃいます。

「1301」という銘柄コードの銘柄名を「VLOOKUP」関数で表示することを考えます。

キャプチャ
図2:VLOOKUPでは#N/A

--みょ 融通が利かないみょ

うん。でも、これで同じとされると困る場合もあります。仕様が分かったうえで上手に付き合えばいいのです。


図1の例では、「VLOOKUP」関数の「検索値」に「1301」が入力されているセルだけを指定しました。この「検索値」と「1301-T」は一致しません。

それだったら一致するような「検索値」を指定してしまえばいいのです。

--みょ 1301に-Tを付けて検索すればいいんだみょ

うん。「-T」は東京証券取引所を表しています。「-T」を付けてしまうと、他の札幌証券取引所「-S」などのデータは一致しません。

今回の日経平均構成銘柄はすべて東京証券所ですので、まずは試してみます。

エクセルで「検索値」に文字列を追加するには「&」でつなげます。文字列を追加する場合は追加する文字列を「""」で囲います。

キャプチャ
図3:「-T」を付加

これでコードが一致して「銘柄名」が表示できました。

キャプチャ
図4:銘柄名の表示


できるだけ1テーマ1記事に収めようと思っているのですが、エクセルの手順を書くとどうしても長くなってしまいます。

ということで、「その2」で東証だけでなく他の取引所のデータもピックアップする方法を検討します。

日銀によるETF買いの近況


キャプチャ
図1:2017年TOPIX前場と前営業日終値との比率と日銀ETF買い入れ

ロシアゲートなる騒動で、日本株の上昇が一服しています。


日本株の上昇が一服していることで、日本銀行によるETF買いがどうなっているのか気になります。

特に、年度が替わって2カ月たっていませんので、ここまでのETF買入ペースを見ることで、新年度の方針が見えるかもしれません。

ということで、今年のTOPIXの動きと日銀のETF買い入れ結果を並べたのが図1になります。

上段には、前営業日終値と当日前場終値との比率を配置してあります。上昇なら上に、下落なら下に配置してあります。

縦の一点鎖線は年度の変わり目で、右側が今年度となります。横の一点鎖線は前営業日終値比-0.24%です。

中段が日銀によるETF買い入れ結果です。データは日本銀行が公表しているものを使用しています。

下段はTOPIXの日足チャートです。


今年度の買い入れ結果を見ると、スタート時には昨年度のペースを上回るような買い入れを続けていました。

TOPIXのチャートを見ると、ちょうど下落していた時期です。その後TOPIXが上昇に転じると、買入もぴたりと止まっています。

そして、TOPIXの上昇が止まったところでまたペースを上げて買い入れが進んでいます。

キャプチャ
図2:図1に買い入れが止まっている間のブロックを追加

--みょ 上昇しているみょ これでは買い入れられないみょ

うん。上昇している間は買い入れは一時ストップです。

上昇時がありましたのでまだ何とも言えませんが、年度が替わってもペースを変えることなく買い入れを進めているように見えます。

-0.24%のラインはなんとなく引いてみました。もちろんもきち♪が勝手に引いたラインで買い入れが決まっているわけではありませんが、一つの目安となりそうです。


今年度も「下がれば買う」という基本方針は変わらないようです。官製相場は続きそうです。

でも、この先トランプ親方の問題がややこしいことになってくると日銀といえども買い支えられないかもしれません。

結局。トランプ親方次第ということのようです。

プロフィール

もきち♪

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

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

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

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

ブログでは株式投資とコンピュータの話を中心に書いています。

きほんゆるめに。。。

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


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

スピンオフサイト

ブログ内リンク

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

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事

ランキング




経済ニュース













使っている証券会社

スポンサーリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

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

広告