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

出展社一覧に株価や株価指標を追加する

キャプチャ
図1:名証 IR EXPO 2017 ブース出展企業一覧に株価と株価指標を追加

名証 IR EXPO 2017」の記事を書くのに、一応出展企業一覧に株価と株価指標を追加した表を作成しました。


以前「出展社一覧から上場企業を抜き出す」で書いた手順の応用です。

以前の応用ですので、大まかな手順だけおさらいしていきます。

まずは、出展企業一覧です。これは、主催者のサイトに「ブース出展企業一覧」がありますので、エクセルにコピペします。

不要な行がありますので、シート全体を選択して「データ」タブの「並べ替え」で「先頭行をデータの見出しとして使用する」にチェックを入れて、「最優先されるキー」を「銘柄」に指定して並べ替えて不要な行を削除します。

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


株価を追加するには、上場企業株価一覧から「VLOOKUP」関数とかで抽出します。

--みょ 前回は証券コードだけで株価までは抽出してないみょ

うん。応用です。企業名から証券コードを抽出するのも、証券コードから株価を抽出するのもエクセルがやることは一緒なのです。

ということで、株価一覧は「日本株全銘柄の株価一覧表」からダウンロードしました。手順は以前の記事にあります。

「VLOOKUP」関数で抽出するには、範囲を指定して列位置を指定します。必要な列は決まっていますので、間の余計な列を削除します。

まずは「市場」を抽出します。出展社一覧の「C列」にからの行を挿入して、株価一覧の「名称」は邪魔ですので削除して、出展社一覧の「C列」に「=VLOOKUP(B2,'japan-all-stock-prices.csv'!$A$2:$B$3667,2,0)」ってな感じで入力します。

下までオートフィルとかで埋めたら、以前の記事で紹介したように、列全体をコピーして「値のみ」でペーストすることで数式を削除します。

同様に、4本値も抽出します。ただ「日本株全銘柄の株価一覧表」では、20分ぐらいのディレイ株価を表示していますので、4本値の並びになっていません。並べなおすか、列番号の指定で注意する必要があります。

並べなおすと「=VLOOKUP($B2,'japan-all-stock-prices.csv'!$A$2:$E$3667,COLUMN()-2,0)」ってな感じで入力すれば4本値全てで使えます。

--みょ 列が違うのに使えるの?

うん。「COLUMN()」でその列の列数を調べて補正をかけて必要な列位置を指定しています。

こちらも下まで埋めたら数式を消しておきます。


続いて株価指数を追加します。

--みょ また決算一覧から計算するみょ 面倒だみょ

うん。今回は楽をします。「日本株全銘柄の株価一覧表」のサイトからは株価指標もダウンロードできます。

上部メニューの「投資指標データ」のプルダウンメニューから「日本株全銘柄」をクリックします。

キャプチャ
図3:株価指標の表示

株価と同様にダウンロードして「VLOOKUP」関数で必要な指標を抽出します。


一度一覧の作成を確認しておけば、応用で様々な一覧表が作成できます。

一度作成してみることをお勧めします。

出展社一覧から上場企業を抜き出す その9

キャプチャ
図1:エクセルにコピペした出展社一覧

その8」までで、株式会社以外の法人や非上場企業が含まれた出展社一覧表から、エクセルで上場企業を抜き出しました。


ここまでお行儀よく抜き出しましたが、最初はもっとやっつけで抜き出しました。

結果は一緒なのですが、ちょっと皆様にご紹介するのは恥ずかしくて控えていました。でも、懺悔の意味も込めて、あくまでもこんな乱暴な方法もありますよってことで書き残しておきます。

使うのは「置換」です。

--みょ ちかんはいけないみょ

うん。一応お約束ですがその「ちかん」ではありません。置き換える法の「置換」です。

--みょ 株式会社を何かに置き換えるの?

うん。そうです。正確にはスペースを含めた前株の「株式会社 」と、後株の「 株式会社」を「空白」に置き換えます。削除するのです。

ここで、スペースを含めることはとても重要です。半角スペースだけ残ると非常に厄介です。株式会社とセットで削除するのが楽です。


株式会社以外の団体も、そのままにしておきます。そもそも株式がなければ上場しませんから、非上場の株式会社と一緒に処理します。

このあたりがお行儀が悪いというか、ちょっと後ろめたいというか、心配が残ります。

株式会社以外を削除してから置換するというのも考えられますが、それだったら文字列関数で抽出してもあまり変わらないかもしれません。


ということで、「その1」の手順で出展社一覧をエクセルにコピペしたところから「置換」を始めます。

「A列」の列頭文字「A」をクリックして「A列」全体を選択して、「Ctrl」キーを押したまま「H」キーを押すか、「ホーム」タブの「検索と選択」をクリックして「置換」を選択します。

キャプチャ
図2:置換の選択

「検索と置換」ウィンドウで、「置換」タブが選択されていることを確認して、「検索する文字列」窓に前株用に半角スペースを後ろに加えた「株式会社 」を入力して「置換後の文字列」窓には何も入力せずに「すべて置換」ボタンをクリックします。

後株も同様に「検索する文字列」に半角スペースを前に加えた「 株式会社」を入力して「すべて置換」します。

キャプチャ
図3:検索と置換ウィンドウ

「株式会社」の文字が消去できました。


この後の手順は一緒です。「その6」からの手順に従って証券コードを抜き出します。

--みょ ややこしい関数がいらないからこっちのほうが楽だみょ

うん。でも、なんだか背徳感があります。今回は目で見て確認できる大きさですからいいようにも思いますが、もっと大量の処理をする場合には思わぬ結果を招くかもしれません。


何かトピックがあったときに、関連銘柄を調べるのが難しいことがあります。

そんな時には、このような展示会や学会、業界団体の構成リストなどから抽出することも一つのアプローチなのかもしれません。

いろいろな団体のリストから上場企業を抜き出すというのも一度確認しておくといいのかもしれません。

出展社一覧から上場企業を抜き出す その8

キャプチャ
図1:証券コードを抽出(その7-図4)

エクセルで株式会社以外の法人や非上場企業が含まれた一覧表から、上場企業を抜き出しています。


その7」までで、出展社一覧から上場企業の証券コードを抽出できました。

そのままだと少し扱いにくいので、今までの後始末と表の仕上げに入ります。

今は「証券コード」が株価一覧表と関連付いています。株価一覧表は3600行を超える大きな表ですので、これと関連付いたままだと単独で開くとなんだか難しいことを言ってくるし、同時に開いても重いシートになってしまいます。

証券コードはもう抜き出しましたので、この関連を切っても問題ありません。

そこで、「その5」でやったように出展社一覧の「A列」の数式を削除します。「A列」の列頭文字「A」をクリックして列全体を選択してコピーします。

列頭文字「A」の上で右クリックして「貼り付けのオプション」の左から2つ目の「123」と書いてあるアイコンを選択して「値のみ」ペーストします。

キャプチャ
図2:数式の削除

これで株価一覧とのしがらみは消えました。


まだ、上場企業と非上場企業が混在していて見にくいので、非上場企業を削除します。

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

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

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

上場企業が「証券コード」順にまとまりますので、不要な非上場企業をバッサリと削除します。


1行目に見出し行を挿入して、色を付けてみました。

キャプチャ
図4:完成図


これで「AI・人工知能 EXPO」に出展している上場企業一覧が完成しました。

今さらですが、もきち♪はこのシリーズ最初の「AI関連企業を調べる」では、もっとお行儀の悪い方法で抽出しています。

自己申告するにはちょっと恥ずかしい方法ですので迷っていたのですが、勢いに乗って「その9」で懺悔できたらと思っています。

出展社一覧から上場企業を抜き出す その7

キャプチャ
図1:名称と証券コードを入れ替えた株価一覧

エクセルで株式会社以外の法人や非上場企業が含まれた一覧表から、上場企業を抜き出しています。


その6」で、「VLOOKUP」関数を使うための準備ができました。株価一覧から「証券コード」を抽出します。

株価一覧と出展社一覧を開きます。

--みょ 1つのファイルにしなくていいの?

うん。「証券コード」だけ抽出したら株価一覧は不要ですから、同じファイルでも別のファイルでも大丈夫です。

出展社一覧の「A1」セルを選択して、入力窓左の「fx」をクリックします。

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

「関数名」の窓で「VLOOKUP」を選択して「OK」ボタンをクリックします。

キャプチャ
図2:関数の挿入ウィンドウ

「関数の引数」ウィンドウで「検査値」を「B1」に設定して、「範囲」は株価一覧に移って「A2」セルをクリックして「Shift」キーを押したまま「B2」セルをクリックします。続いて「Shift」キーと「Ctrl」キーを押したまま「↓」キーを押します。

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

キャプチャ
図3:関数の引数ウィンドウ

--みょ #N/Aって出ているみょ 壊れたみょ

いえ。壊れたわけではありません。上場されていないので株価一覧で該当する「名称」がないということです。これが非上場となります。「#N/A」の表示が気持ち悪ければ、例えば

=IFERROR(VLOOKUP(B1,'japan-all-stock-prices.csv'!$A$2:$B$3664,2,0),"非上場")

とかして「非上場」と表示させることも可能です。でも「#N/A」を「非上場」に脳内変換したほうが楽です。

式が入力できたセルを下までドラッグしてオートフィルで下までコピーすれば、上場企業の「証券コード」が抽出できました。

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


出展社一覧に上場企業の「証券コード」が表示できました。

でも、まだ扱いにくい表となっていますので、「その8」で後始末をして仕上げていきます。

出展社一覧から上場企業を抜き出す その6

キャプチャ
図1:「株式会社」の文字を消した一覧

エクセルで株式会社以外の法人や非上場企業が含まれた一覧表から、上場企業を抜き出しています。


その5」までで、出展社一覧から「株式会社」を抽出して「株式会社」の文字を消去しました。

ただ、まだ非上場の企業も含まれています。

ここから上場企業を抜き出します。

方針としては、全上場企業の「証券コード」一覧表からそれぞれの「証券コード」を抽出して、「証券コード」のない出展社は非上場と判断します。

そこで、全上場企業の一覧表が必要になります。

例えば、日本取引所グループ(JPX)には東京証券所に上場している銘柄のリストがありますが、これでは地方単独上場の銘柄は漏れてしまいます。

ということで、全銘柄の株価をダウンロードして、そこから証券コードを抜き出します。

今回使用したのは「日本株全銘柄の株価一覧表」です。上場全銘柄の4本値や時価総額などが一気にダウンロードできるありがたいサイトです。


サイトの銘柄一覧の下にある「Save to CSV」をクリックして、保存場所を指定してダウンロードします。

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

ダウンロードした株価一覧表を開いたまま、「その5」で作成した出展社一覧を開きます。

出展社一覧の「B列」の出展社名を使って株価一覧から「証券コード」を抽出します。

ここで使うのは「VLOOKUP」関数です。

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

うん。このブログでも何度か使いました。大きな表から何かを抽出するときには便利な関数です。今までも、日経平均とダウなど祝日が違う場合の日付を合わせるのに使いました。

書式は、「= VLOOKUP (検索する値, 値を検索する範囲, ほしい値の列の番号, 完全一致か近似一致か)」です。


「VLOOKUP」関数は便利なのですが、ちょっと癖があります。

今回は出展社一覧の「B列」に入力されている「出展社名」と株価一覧の「B列」の「名称」が一致した場合に「A列」の「証券コード」を抽出します。

でも、「VLOOKUP」関数では、「検査値」に対応するセルは「検査範囲」の1列目にある必要があります。

ということで、株価一覧の「名称」と「証券コード」を入れ替える必要があります。

株価一覧の「名称」が入っている「B列」を選択して「Ctrl」キーを押しながら「X」キーを押して切り取ります。「A列」で右クリックして「切り取ったセルの入力」を選択します。

キャプチャ
図3:株価一覧の整形


その7」で、「VLOOKUP」関数を仕上げていきます。

プロフィール

もきち♪

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

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

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

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

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

きほんゆるめに。。。

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


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

スピンオフサイト

ブログ内リンク

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

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事

ランキング




経済ニュース













使っている証券会社

スポンサーリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

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

広告