08
18
19
20
21
22
23
24
25
26
27
28
29
30
31
   

日経平均採用銘柄を時価総額順に並べる その12

キャプチャ
図1:マネックス証券 Tradestation(トレードステーション)

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その11で日経平均採用銘柄一覧に上場株式数を表示させる計算式を設定しました。




ということで、日経平均採用銘柄の終値を追加していくところですが、「【悲報】またありがたいサイトが終了します」でお知らせしたように、終値をダウンロードしようとあてにしていたサイトのサービスが9月30日で終了することが判明しました。

あまりのショックにこのシリーズも終了しようかとも思いましたが、途中でやめるのは気持ちが悪いので、口座開設の必要はありますが、マネックス証券から提供されているトレードツールTradestation(トレードステーション)を利用して続けることにします。


--みょ 口座を開設する必要があるって優しくないみょ

うん。でも、ブログでご紹介するだれでも無料でダウンロードできるありがたいサイトが次々と閉鎖に追い込まれていますので、新たにご紹介するのはちょっとためらってしまいます。

ここは、ちゃんと口座を開設している会員向けに証券会社が無料で提供してくれているサービスに頼ろうと思ったわけです。それに、Tradestation(トレードステーション)はもきち♪が知っている中で最も多機能なトレードツールですので、使えるようにしておくと何かと便利です。


Tradestation(トレードステーション)の申し込み手順は過去の記事を参照していただくとして、インストールが済んで起動できた状態から続けます。

トレードステーションでは、直接日経平均採用銘柄一覧を表示させることができます。

その13から日経平均採用銘柄一覧からデータをダウンロードして終値を抜き出す手順を確認していきます。

【悲報】またありがたいサイトが終了します

キャプチャ
図1:株価データ(無料)

「日経平均採用銘柄を時価総額順に並べる」シリーズの主役となっている全銘柄の株価一覧がダウンロードできるサイト「株価データ(無料)」の株価データの公開が9月30日で終了するとのアナウンスがありました。


どうもこのブログで「便利でありがたいサイトです」って紹介したサイトがどんどんサービスが終了しています。ひょっとしてデスノートならぬデスブログとなっているのかもしれません。

だとしたら世の個人投資家に大変申し訳ないことをしています。

とは言っても、東証の担当者がこのブログの読者ってことはないと思います。もきち♪が探し出せるサイトはだれでも探せると思いますので、東証の担当者が探すとヒットすると思います。

東証が本気で「個人投資家に無料でデータを提供するのはまかりならん」と考えているということなのだと思います。困ったものです。


今回「日経平均採用銘柄を時価総額順に並べる」シリーズでは「その11」までで日経平均採用銘柄一覧と上場株式数を作成しました。

これと各銘柄の終値から時価総額を計算すれば並べ替えも完了です。

9月30日までは株価データ(無料)サイトでデータをダウンロードできますが、その後は提供を受けられないとのことですので、このまま進めるかどうかは少し考えてみます。

日経平均採用銘柄を時価総額順に並べる その11

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

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その10でみなし額面一覧から日経平均採用銘柄一覧を作成しました。


日経平均採用銘柄一覧と上場株式数の一覧が作成できましたので、2つの表から日経平均採用銘柄の上場株式数一覧表を作成します。

--みょ 225銘柄も数字を拾うのは大変だみょ

うん。手動ではやっていられません。ややこしいことはエクセルに任せます。今回は証券コードでデータを統合できます。エクセルでは「VLOOKUP」関数が使えます。

--みょ 聞いたことがあるみょ

うん。個人投資家には便利な関数です。書式は「VLOOKUP (検索キー, 検索する範囲, 戻り値の列番号, 完全一致か近似一致か)」となります。


ということで、日経平均採用銘柄一覧に上場株式数を追加します。日経平均採用銘柄一覧シートの「C1」セルに見出しとして「上場株式数」を入力します。

「C2」セルを選択して入力窓左の「fx」をクリックします。

「関数の挿入」ウィンドウで「関数の検索」窓に「VLOOKUP」と入力して「検索開始」ボタンをクリックして、「関数名」から「VLOOKUP」を選択して「OK」ボタンをクリックします。

キャプチャ
図2:関数の挿入

「関数の引数」ウィンドウで「検査値」を同じ行のコード「A2」、「範囲」は上場株式数シートの証券コードと上場株式数をすべて指定します。範囲窓を指定して上場株式シートの証券コード「B2」セルをクリックして「Ctrl」キーと「Shift」キーを押したまま右矢印「→」キーを押して続いて下矢印キー「↓」を押して証券コードと上場株式数をすべて選択して「F4」キーを押して絶対参照とします。

「列番号」は2列目の「2」、検索方法は完全一致を指定する「0」を入力して「OKボタンをクリックします。

キャプチャ
図3:関数の引数


計算式ができましたので、その12から表を仕上げていきます。

日経平均採用銘柄を時価総額順に並べる その10

キャプチャ
図1:日経平均プロフィル指数一覧日経平均株価>関連データ

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その9でみなし額面一覧をダウンロードすることとしました。


日経平均プロフィル指数一覧日経平均株価>関連データから「みなし額面一覧」をダウンロードします。

キャプチャ
図2:みなし額面一覧

--みょ ほしいのは日経平均の銘柄一覧だみょ みなし額面はいらないみょ


うん。いらない列はバッサリと削除して、日経平均採用銘柄一覧を作成します。

キャプチャ
図3:みなし額面一覧から不要行を削除

--みょ 日経平均採用銘柄の一覧ができたみょ

うん。列幅を自動調整して、先頭行を固定してみました。

この日経平均採用銘柄一覧を元に時価総額順に並べていきますので、時価総額のシートと一緒にして保存しておきます。

--みょ シートを一緒に保存?

うん。シートのタブ部分で右クリックして「移動またはコピー」を選択してシートを一つのブックにまとめた後に保存します。

キャプチャ
図4:移動またはコピー


日経平均銘柄一覧と、上場株式数一覧が作成できました。

その11から株価と合わせて時価総額を算出していきます。

日経平均採用銘柄を時価総額順に並べる その9

キャプチャ
図1:上場株式数のシート

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その8でPDFから上場株式数のエクセルシートを作成しました。


次に、日経平均採用銘柄のリストを作成します。

--みょ ダウンロードするみょ

うん。上手に一覧表がダウンロードできればいいのですが、一覧表そのものはなかなかダウンロードできません。

日経平均のことは日経の指数公式サイトである日経平均プロフィルで調べるのがよさそうです。

キャプチャ
図2:日経平均プロフィル

「指数一覧」ってのをクリックします。「指数一覧」から「日経平均株価」を選択します。

キャプチャ
図3:指数一覧

--みょ 「関連データ」ってところに「銘柄一覧」ってのがあるみょ

キャプチャ
図4:関連データ

うん。クリックしてみます。

キャプチャ
図5:銘柄一覧

--みょ またエクセルにコピペしていろいろややこしいことするんだみょ 面倒だみょ

う~ん。もう一度「関連データ」に戻ってよく見てみます。図4を見ると「銘柄一覧」にはダウンロードデータが書かれていません。他の項目にはPDFやCSVのダウンロードデータのリンクが用意されています。

そして、どうせダウンロードするのならCSVファイルが扱いやすい。CSVファイルが用意されているのは「日次データ」と「月次データ」と「みなし額面一覧」の3つです。

日次や月次は日経平均そのもののデータですので、採用銘柄はリストアップされていません。ということで、「みなし額面一覧」をダウンロードすることとします。


その10から「みなし額面一覧」のCSVファイルをダウンロードして日経平均採用銘柄一覧シートを作成していきます。

日経平均採用銘柄を時価総額順に並べる その8

キャプチャ
図1:証券コードで並べ替えたシート

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その7でPDFのテキストをエクセルにコピペして、「証券コード」順に並べ替えました。


不要な行を削除します。シートは証券コード順に並べましたので、証券コードが入っていない行は下のほうに固まっています。ということで、証券コードが入っている最終行の次の行からまとめて削除します。

「B1」セルをクリックして「Ctrl」キーを押したまま「↓」を押します。

個別銘柄最終行の次の行を選択して、「Ctrl」キーと「Shift」キーと「↓」キーを押して最終行まで選択して「Delete」を押して不要行をすべて削除します。

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

不要な行が削除できました。


余分な列があっても問題なさそうですが、気持ち悪いので、不要な列も削除しておきます。

まずは空白の「C列」は余分ですので、列頭文字「C」を選択して右クリックで「削除」します。

キャプチャ
図3:C列の削除

お好みで「D列」以降も削除しておきます。


このままでもいいのですが、何のデータかわからななくなると困りますから、項目名を入力しておきます。

--みょ そんなうっかりさんはもきち♪だけだみょ

うん。でも、今日は覚えていても1か月後1年後には忘れちゃうかもしれません。やっぱり項目名は入力しておいたほうがよさそうです。

1行目で右クリックして「挿入」を選択して「A1」セルに「会社名」、「B1」セルに「証券コード」、「C1」セルに「上場株式数」を入力します。

キャプチャ
図4:項目名の入力


上場株式数のシートが完成しました。

その9から終値と組み合わせて時価総額を求めていきます。

日経平均採用銘柄を時価総額順に並べる その7

キャプチャ
図1:上場株式数の抽出

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その6でPDFのテキストをエクセルにコピペして、「会社名」と「証券コード」と「上場株式数」を抽出しました。


PDFからコピペしていますので、余分な行がたくさん含まれています。もともとお手紙から抽出しているのです。

--みょ 余分な行いらないみょ

うん。でも、もともとPDFなので、本来は必要な説明なのです。でも、今回は使いません。バッサリと削除します。

まずは、証券コードが入力されている行と証券コードのない行とに分けます。


--みょ ややこしいのは嫌だみょ

うん。証券コードで並べ替えて、証券コードの有り無しも一気に解決します。

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

「並べ替えウィンドウ」で「最優先されるキー」を「B列」に指定して「OK」ボタンをクリックします。

キャプチャ
図2:並べ替えウィンドウ

証券コードで並び変えられました。

キャプチャ
図3:証券コードで並べ替えたシート


証券コードで並び変えられました。そして、証券コードが入っていない行はひとまとめにできました。

ということで、その8から不要なセルを削除していきます。

日経平均採用銘柄を時価総額順に並べる その6

キャプチャ
図1:証券コードの抽出

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その5でPDFのテキストをコピペしたエクセルデータから「A列」に会社名、「B列」に証券コードを分割しました。


個別銘柄の行では、「C列」に「月末現在上場株式数」以降のデータが入力されています。ここから上場株式数だけ抽出します。

--みょ 今度は「()」とかがないから分割できないみょ

うん。明確に分割するための文字は見当たりません。でも、極洋(1301)を例に「C列」をよく見てみると、スペースで区切られているようです。

「C列」は「 10,928,283 転換社債の行使 0 CB 18. 12 円 0 千円 1,850,000 千円」となっていますが、半角スペースを「_」に置き換えてみると「_10,928,283_転換社債の行使_0_CB_18._12_円_0_千円_1,850,000_千円」となります。

--みょ 半角スペースがいっぱい入っているみょ

うん。必要なのは上場株式数だけですので、それ以降がどれだけセルに分割されても問題ありません。ということで、半角スペースで分割することとします。


「C列」の列頭文字「C」をクリックして「データ」タブの「区切り位置」をクリックします。「区切り位置指定ウィザード」の「1/3」はデフォルトのままで「次へ」ボタンをクリックして「2/3」の「区切り文字」で「スペース」のチェックを入れて「完了」ボタンをクリックします。

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

--みゃっ 個別銘柄のC列が消えちゃったみょ 壊れたみょ

いえ。上の例でも分割前の「C列」は半角スペースで始まっていましたので、最初の半角スペースで空白列ができたのです。ちゃんと「D列」に「上場株式数」が抽出できています。

キャプチャ
図3:上場株式数の抽出


「会社名」と「証券コード」と「上場株式数」が抽出できました。

必要なデータは分割できましたので、このまま「VLOOKUP」とかで利用してもいいのですが、余分な行や列が多すぎますので、気持ち悪い。ということで、その7で不要な行と列を削除していきます。

日経平均採用銘柄を時価総額順に並べる その5

キャプチャ
図1:「(」で区切った個別銘柄

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その4でPDFのテキストをコピペしたエクセルデータを「(」で区切りました。


図1が「(」で区切ったエクセルシートです。

--みょ よくわからないみょ

うん。個別銘柄の行は、「A列」に会社名が入力されて、「B列」に「(」以降のデータが証券コードを先頭に入力されています。

「(」で個別銘柄のデータを区切ることができました。

--みょ なんだか余分なデータがあるみょ

うん。これから削除していきます。


次に、「B列」を証券コードだけにします。「A列」を「(」で分割したように「B列」を「)」で分割します。

「B列」の列頭文字「B」をクリックして「B列」を選択します。「データ」タブの「区切り位置」をクリックして「区切り位置ウィザード 1/3」で「次へ」ボタンをクリックして「区切り位置ウィザード 2/3」で「区切り文字」の「その他」にチェックを入れて入力欄に「)」を入力して「完了」ボタンをクリックします。

キャプチャ
図2:「(」での区切り

証券コードが「B列」に抽出できました。

キャプチャ
図3:証券コードの抽出


「A列」に会社名が、「B列」に証券コードが入力できました。ほしいのは証券コードと月末現在上場株式数ですので、その6で「C列」に月末現在上場株式数を抽出していきます。

日経平均採用銘柄を時価総額順に並べる その4

キャプチャ
図1:コピペしたままの個別銘柄表示

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その3でPDFをエクセルにコピペした個別銘柄をセルに分割する方針が決まりました。


ということで、実際にセルに区切っていきます。

--みょ 銘柄以外の行もあるみょ

うん。一度セルに区切ってからまとめて削除していきます。まずは区切ります。

PDFからコピペしたシートの「A列」の列頭文字「A」をクリックしてA列を選択します。

「データ」タブの「区切り位置」を選択します。

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


「区切り位置ウィザード 1/3」が表示されます。

1ページ目では元データの形式を指定します。ここはデフォルトのまま「カンマやタブなどの・・・」を指定しますので「次へ」ボタンをクリックします。

2ページ目で区切り文字を指定します。「区切り文字」の「その他」にチェックを入れて右の入力窓に「(」を入力して「次へ」ボタンをクリックします。

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

--みょ タブにもチェックが入っているみょ

うん。タブのチェックを外したほうがすっきりしますが、入ったままでも実害はありません。

3ページ目はデフォルトの「標準」のままで大丈夫ですので「完了」ボタンをクリックします。

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


どんな感じで分割されたかは次のお楽しみとして、その5に続きます。

プロフィール

もきち♪

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

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

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

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

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

きほんゆるめに。。。

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


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

スピンオフサイト

ブログ内リンク

全ての記事を表示する
データをダウンロードできるサイト一覧  シーズン2
エクセルでチャートを描いてみよう

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事








使っている証券会社

スポンサードリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

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

広告