株価データ取得関数の補足説明

7月6日のコラムでご紹介いたしました「当日株価データ取得ユーザー定義関数」（株価データ取得関数またはDataUpdate関数）ですが、そのままでは非常に使い難いかと思います。
そこで今回は、同関数（マクロ）に若干の変更を行うと共に、ワークシート上で処理を行うことにより、セル単位で終値など個々のデータを取り出す方法をご紹介いたします。

まず最初に、株価データ取得関数を再計算可能とするために、マクロの最初（Set objXml ・・・の行の直前）に次行を追加します。

Application.Volatile

これを記述することで、株価データ取得関数が自動再計算される関数になり、セルに変更があったり「F9」キーを押した場合に、再計算が実行されます。
すなわち、「F9」キーを押すことで、株価データを更新することができるわけです。

続いて、DataUpdate関数の結果を取り出す数式（DataUpdate = cellData(3) & ・・・）を、次式のように修正します。

DataUpdate = "a" & cellData(3) & "b" & cellData(4) _
& "c" & cellData(5) & "d" & cellData(6) _
& "e" & cellData(7) & "f" & cellData(8) _
& "g" & cellData(9) & "h" & cellData(10) _
& "i" & cellData(11) & "j"

すなわち、従来は各データの間を"/"で区切って出力していたのですが、それを異なった文字（アルファベット）で区切るようにしたわけです。
その理由については、後で明らかになります。

マクロの変更は以上です。あとはVBEを閉じて、ブックを保存してください。

前回同様この関数を実行すると、関数を入力したセルに、cellData(3)からcellData(11)までの各データが、aからjのアルファベットで区切られて表示されます。
これはもちろん、ひとまとまりの文字列であり、そのままでは個別にデータを処理することはできません。

そこで、DataUpdate関数の出力を分解して、個々のデータを各セルに割り振るための数式をワークシート上に記述します。
これによって、例えば始値や終値などを個別のセルに表示できるようになります。

次図をご覧ください。これは、DataUpdate関数から始値データを取り出すための数式です。

この例では、A4052セルとB4052セルを引数として、DataUpdate関数をN4052セルに記述しています。そして、D4052セルで始値データを計算しているわけです。
同様に、E列で高値、F列で安値、G列で終値、H列で出来高、I列で前日終値、J列で前日比（値）、K列で前日比（率）、L列で単元株数を計算しています。

数式の意味ですが、DataUpdate関数の結果から必要なデータを取り出すために、MID関数とFIND関数を用いています。
FIND関数で、DataUpdate関数の出力に付加したアルファベットの位置を割り出し、それを基準としてMID関数で目的のデータのみを抽出しているわけです。

ただし、抽出したデータは文字列ですので、それを数値に変換するためにVALUE関数を用いています。しかし、サイトにデータが存在しない場合は、"---"という文字列を返すようにしているため、これにVALUE関数を適用するとエラーとなってしまいます。

そこで、抽出したデータが"---"の場合は、VALUE関数を適用せずにそのまま"---"を出力するようにしています。そのための条件分岐が、IF関数ということになります。
ちなみに、今回の例では得られるデータは数値か"---"ですので、区切りとしてアルファベットを用いていますが、銘柄名等を取得する場合は、区切り記号を変える必要があるかもしれません。

続いて、E4052セルに計算式を入力するには、D4052セルの数式をE4052セルにコピーし、"a"を"b"に、"b"を"c"に書き換えるだけです。
以降同様に、L4052セルまで計算式を入力して行きます。

なお、ワークシートにDataUpdate関数が入力されていると、上記のようにセル内容を変更する度に再計算が行われてしまいますので、計算式を入力している間は同関数は削除しておいた方がよろしいかと思います。

必要なセルへの計算式入力や書式の設定等が全て完了した段階で、N列にDataUpdate関数を記述してやれば、再計算でのイライラは軽減されます。
なお、再計算と言いましても、これはデータをヤフーファイナンスのサイトから再取得するわけですから、それなりに時間が掛かります。

Core2Quad Q6700 @2.66GHz、RAM 3GB、Windows VISTA HP SP2、Excel2003 SP3、Internet Explorer 8、ADSL（実効1.8Mbps）の環境下で、1銘柄（1行）当り0.3秒ほどでした。
100銘柄もあると、1回の更新毎に30秒ほどの時間が掛かってしまうことになります。

これは、複数のブックに銘柄を分散しても、同一のエクセルで実行する限り、全銘柄数分の時間が掛かってしまいます。
それを避けるためには、複数のエクセルを起動し、それぞれに銘柄を分散させれば、更新時間の短縮が可能かと思います。

ただし、CPU能力やメモリ容量が十分でない場合は、複数のエクセルを起動するだけでシステムに多大な負荷を掛ける場合がありますから、注意が必要です。
同時に起動するエクセルの数は、CPUのコア数の2～3倍程度に留めておくのが無難かと思います。

以上、今回は株価データ取得関数の利用方法についてご説明いたしました。

タグ：VBA 関数 ヤフーファイナンス エクセル マクロ 株 株価データ データ取得 ワークシート