Excel〜データ取得と整理

データのダウンロード

・ Webクエリを利用してWebサイトから時系列データを取得する方法。

　時系列データは以下のサイトから取得できます。
　　→ 株価データダウンロードサイト…先物、業種別指数など過去1年分
　　→ YAHOO!ファイナンス日経平均株価…1990年以降のすべての上場銘柄と主な指数
　　→ Dow Jones Industrial Average…DOW工業株30種平均株価
他のデータ取得先は、LINK → 時系列データを参照してください。

　ここでは日々更新して使うことを前提にするのでWebクエリを作成します。 基準のセルはA5にするためA5を選択した後、【ツール】→【外部データの取り込み】→【新しいWebクエリ】を選択します。（図1-1

Webクエリ(図1-1

　Webクエリのアドレスバーに目的のHPのアドレスを入力し【移動】をクリック。この際、あらかじめ目的のHPを開いておきアドレスをコピーし貼り付けると効率がいいです。ただしWebクエリに貼り付ける時、右クリックは利かないのでショートカットキーを使うこと。貼り付け…Ctrl+V、コピー…Ctrl+C
　次に選択したいテーブルの左側の[→]をクリックして、【取り込み】をクリックします。
（図1-2はYahoo!ファイナンスから日経平均株価の時系列データを取得しているところ。

データ取得(図1-2

　日々データを更新することを考えて、外部データ範囲のプロパティを開きデータ更新時の処理を【既存のセルを新規で上書きし、使用されていないセルはクリアする】にしておきます。
　データを更新する場合はセル（ここではA5）を右クリックして【データの更新】を選ぶと更新できるます。 また、ファイルを開いた場合に自動でデータを更新したい場合は【ファイルを開くときにデータを更新する】にチェックを入れておきます。
　（図1-3はデータの取得が完了した状態。

日経平均時系列データ取得完了(図1-3

　長期間または複数銘柄のデータを取得する場合は、 → Excelで株式投資さんのHPに掲載されているマクロツールが便利です。他にもネット上のフリーソフトにはいろいろなものがあるので検索すれば比較的簡単に見つけることができます。
　時系列データを利用したテクニカルやグラフは、 → テクニカル(数式とグラフ)を参考にどうぞ。


Excelに戻る


データの整理

・ 異なる市場のデータを統合する際の注意点。

■IF(論理式,真の場合,偽の場合)
論理式の条件が満たされる場合は真の内容を選び満たされない場合は偽の内容を選ぶ。
■VLOOKUP(検索値,範囲,列番号,検索の型)
検索値に指定した値を範囲に指定した表の左端で探し、該当する行の列番号で指定した列からデータを取り出す。検索の型は完全一致の場合FALSE、近似値はTRUEとなる。
■ISERROR(テストの対象)
引数に指定した値がエラーの場合はTRUE、それ以外はFALSEを返します。

　前日のDOW指数を利用したシステムでは違う市場データを統合する必要がありますが、営業日が違うので少し工夫が必要です。
　Sheet1に日経平均株価、Sheet2にDOW指数、Sheet3に統合したデータを記録する場合。 まずSheet3に日付を入力します。A6セルに2008/10/29と入力しA7のセルには【=A6-1】と入力。 そしてオートフィル機能でA7セルを必要な日数分下にコピーします。
データの転記にはさまざまな関数があるので → データを別表から転記する の項を参考にしてください。
ここではVLOOKUP関数を使います。B6セルに【=VLOOKUP($A6,Sheet1!$A$6:$E$55,2,FALSE)】と入力します。これで2008/10/29の日経平均始値が表示されます。
ただしこのままでは参照先にデータがない場合エラーが表示されますのでISERROR関数を使ってエラーが表示されないようにします。
　【=IF(ISERROR(VLOOKUP($A6,Sheet1!$A$6:$E$55,2,FALSE)),"",VLOOKUP($A6,Sheet1!$A$6:$E$55,2,FALSE))】
これで参照先がエラーの場合、空白が表示されるようになります。 （図1-4は日経平均株価とDOW指数のデータを統合した状態。

データ統合(図1-4

　次にデータの更新をすることを考えてシートを改良します。
まず6行をコピーして6行にコピーしたセルを挿入します。次に7行目以降のデータをコピーして値のみを貼り付けます。新しいデータに更新された場合はセルA6に日付をいれればデータが表示されます。 行が足らなくなればその分追加で挿入します。
以上のデータの追加作業はマクロで自動化できますがここでは省略します。
　また、このままだと無駄が多いので余分な行を削除します。
J7に【=IF(AND(E7="",I7=""),0,A7)】と入力。オートフィルで下の行にコピーします。 J7以降のデータを選択後、降順で並び替えします。
これでデータのない行は下側に移動しますので削除します。またJ行の数式も不要なので削除し完了です。（図1-5

完了(図1-5
Excelに戻る


データの配列を変更。

・ INDEX関数を使って縦1列の表を複数列の配置に変更する。

■INDEX(範囲,行番号,列番号)
範囲の中から、指定した位置にあるデータを取出します。

　縦1列に並んだデータを横1列に変更する場合は、
表を選択して【コピー】→基準となるセル選択して右クリック【形式を選択して張り付け】→【行列を入れ替える】にチェックをいれて【ＯＫ】とすれば簡単にできます。（図1-5
　横1列から縦1列に変更する場合も同様です。

行列の入替え(図1-5

　一方縦3列に変更等、縦列を複数列にして表を並び替えたい場合、INDEX関数を使えば簡単にすることができます。ここでは3列に並び替えてみます。
まず、前段階として連番を3列に並べた表を用意します。 1行目の3つのセルに【1】【2】【3】と入力、2行目のセルには【4】【5】【6】と入力しオートフィル機能を使い下のセルにドラグします。（図1-6

別表製作(図1-6

　次に別の表にINDEX関数の数式を入力し連番の表に対応した位置にあるデータを元表から取り出します。
INDEX関数の範囲は銘柄名の表全体を指定し、後からオートフィルすることを考えて絶対参照にしておきます。行番号は銘柄名の表の行数を指定します。
　C14セルに【=INDEX($A$3:$A$32,C2)】と入力すれば【日本水産(株)】と表示されます。あとはこれを表全体にコピーすれば連番表に対応した銘柄名が表示されるようになります。

3列に並び替え完了(図1-7

Excelに戻る 　　次のページへ