WEB上の株式関連データをEXCELにダウンロード＆分析する方法(複数WEBサイト情報の一覧化)

前回の記事でWEB上の情報をEXCELに表示する方法を書きました。

WEB上の株式関連データをEXCELにダウンロード＆分析する方法(データ収集編)

欲しいデータのダウンロードが出来たら後はEXCELで好きに加工＆分析すれば良いのですが、多少なりとも効率的にデータを管理するためのやり方を本記事にて説明をします。

主に使うのは以下のEXCEL関数になります。
この辺の使い方が分かる方は、適当に読み飛ばしてください。

=VLOOKUP()
=INDIRECT()

基本はVLOOKUP()がメインでこれ自体は仕事とかで使っている人も多いと思いますが、INDIRECT()が効率化のポイントで、これは知らない人も多いんじゃないかと思います。

作成する表（サンプル）

この記事では以下のような複数のWEBサイトの株式関連時系列データを一覧表示する手順を説明します。

以下の記事は説明のため細かく書いていますが、慣れれば5分～10分もあれば同じような表を作れるようになると思います。

複数のWEBサイトの情報（時系列データ）を一覧化する方法

①欲しい情報を全てEXCELに連携する

まずは、前回記事の手順で一覧化したいデータがあるWEBサイトを全てエクセルにまとめます。
※手順は以下の記事にあります。

WEB上の株式関連データをEXCELにダウンロード＆分析する方法(データ収集編)

今回のサンプルは以下の3つのサイトを使っています。

• ヤフーファイナンス（日経平均）
• 日経平均PER 日経平均比較チャート
• 恐怖指数 [VIX・日経VI・VSTOXX] 日経平均比較チャート

②シートに名前を付ける

そのままだとどのシートに何があるのか分かり辛いので、

シートに適当に名前を付けます。

③各シートの中身を確認

必要な情報がどのシートのどこにあるのかを確認します。

今回のサンプルでは、時系列の日経平均、日経平均PER・PBR・VIXを使うので、それぞれ以下のシート＆列が該当します。

日経平均→「日経平均」シートの5行目
日経平均PER・PBR→「PER」シートの5行目と6行目
日経平均VI→「VIX」シートの5行目

④一覧表の見出しを作る

ここから最終的にまとめる一覧を作ります。まずは見出し部分を作りますが、

3行目に実際の表の見出しを作り
1行目・2行目は③の必要な情報が「どのシート」の「何列目」を記載します。

⑤一覧表の日付を設定

今回は時系列データの一覧を作るので、日付が軸になります。
日付はどこから取っても良いのですが、今回は「日経平均」シートの1列目から取ります。

具体的には、「A4」セル（↓で2018/4/6が設定されているセル）に「=日経平均!A2」と入力。
A5セル～A23セルはA4セルをコピペ＆貼り付けします。

⑥一覧表のデータを設定

まずは「B4」セルに以下の計算式を設定します。（「一覧」は今入力しているシート名）
=VLOOKUP($A4,INDIRECT(B$1&”!A:Z”),一覧!B$2,FALSE)

計算式の意味は、4/6の「日経平均」シートの「5列目」（日経平均）を表示する。という意味で、この計算式はそのまま他の日付・項目でも使えます。

これで日経平均が設定されれればOK。
もし「#REF!」「#N/A」となった場合は計算式や1行目・2行目のシート名と列数を見直してください。

⑦一覧表のデータを設定

⑥で値を設定した「B4」セルをコピーして、E23までの全セルに貼り付けします。

↑の画像の赤枠の部分がコピペの範囲になります。
画像のように全て値が設定されればOK。
もし「#REF!」「#N/A」となった場合は⑤と同様、計算式や1行目・2行目のシート名と列数を見直してください。

⑧表を整形する

これで完了でも良いのですが、あと直すとしたら、

E列
→文字が左寄りになっている
→数値ではなく文字列として定義されてしまっている
→現在入っている計算式をvalue()関数で囲う。

※E4セルの場合
=VLOOKUP($A4,INDIRECT(E$1&”!A:Z”),一覧!E$2,FALSE)
↓
=VALUE(VLOOKUP($A4,INDIRECT(E$1&”!A:Z”),一覧!E$2,FALSE))

B列～E列の小数点を2桁固定で表示する
→右クリック：セルの書式設定から、小数点以下の桁数を「2」に設定。
↓以下に設定イメージ

計算式を崩さなければ基本的に何をしてもOKなので、自分の好きなようにカスタマイズ可能です。

完成

そんなこんなで適当に整形したら完成です。

このEXCELを保存しておけば、あとは日付が変わる度「データ」＞「すべて更新」を押せばデータが最新化されます。

おわりに

今回はサンプルとして表を作りましたが、作り方さえ分かれば皆さまが欲しい項目の表を作るのも手間は変わらないです。
基本的にはあとはEXCELの世界なので何なりと好きなようにデータ加工＆分析をして貰えればと思います。

ブログランキング参加中です。クリックお願いします！
↓↓↓