対象銘柄を切り替えるためのコンボボックスの作成

まず、調べたい銘柄の名称とコードを、MSNマネーのサイトから先述の手順に従って調べ、クエリーテーブルの右側に入力しておきます。そのデータ範囲を選択し、右クリックして［名前の定義］をクリックし、「codeTable」という名前を付けておいてください（タイトル行は含みません）。

次に［開発］タブの［挿入］ボタンをクリックし、［ActiveXコントロール］の［コンボボックス］をクリックし、一番上に配置します。名前は、「cbName」に変更しておきます。

図10．コンボボックス、クエリーテーブルとcodeTable

ここまでで、対象銘柄を切り替えるための準備ができました。

銘柄の切り替え、データの取り込み、株価チャート作成を実現するVBAの実装

まず、銘柄の切り替えの選択肢を設定するVBAを実装します。 コンボボックスに設定するデータは、毎回クリアされてしまいますので、Excelを開いた時に自動実行させるようにします。

[Alt]+[F11]キーでVBエディタを開き、左ペインで［ThisWorkbook］をダブルクリックし、コードウィンドウの上部で、［Workbook］、［Open］を選択します。「Private Sub Workbook_Open()」は開いた時に自動実行されますので、この中にコンボボックスへ選択肢を設定するVBAを記述します（図11）。

図11．コンボボックスへ選択肢を設定するVBAのコード

［リスト1］コンボボックスへ選択肢を設定する

Private Sub Workbook_Open()
    Sheets("Sheet1").Activate
    Dim i As Integer
    For i = 1 To Range("codeTable").Rows.Count '(1)
        ActiveSheet.cbName.AddItem Range("codeTable").Cells(i, 1) '(2)
    Next i
End Sub

このコードでは、銘柄の数だけ(1)、銘柄の名称をコンボボックスに追加しています(2)。 コードの記述が終わったら、開き直しをしなくても値が設定されるように、[F5]キーを押して１回実行しておきましょう。

次にデータを取り込むVBAを実装します。データの取り込みは銘柄を選択しなおしたタイミングで行いたいので、コンボボックスの選択肢を変更した時の処理として実装する必要があります。

コンボボックスの外をクリックして選択状態が解除されていることを確認してください。そして、［開発］タブの［デザインモード］をONの状態にして、再度コンボボックスをダブルクリックすると、VBエディタが開きます。

この中に記述したコードが、コンボボックス「cbName」の選択肢を変更したときに実行されますので、プロシージャの名称「Private Sub cbName_Change()」は変えないでください。選択された銘柄のデータを取り込むコードを以下のように記述します。

図12．選択された銘柄のデータを取り込むVBAのコード

［リスト2］選択された銘柄のデータを取り込む

Private Sub cbName_Change()

    '株価チャートが存在すれば削除
   （スクリプト省略）

    Dim conn As String
    conn = "URL;http://jp.moneycentral.msn.com/investor/charts/historicdata.aspx?symbol=" _
            & Replace(WorksheetFunction.VLookup(cbName.Value, Range("codeTable"), 2, False), ":", "%3a")
            '(1)
    Range("priceTable").QueryTable.Connection = conn '(2)
    ActiveWorkbook.Connections("接続").Refresh '(3)

    '株価チャートの描画
    （スクリプト省略）

End Sub

(1)では、MSNマネーで当該銘柄の株価の時系列データを表示する接続文字列を組み立てています。httpの前の「URL;」は、接続の種類がWebクエリであることを識別する文字列です。

そのために、選択した銘柄名「cbName.Value」からワークシート関数のvlookup（対応表の範囲の左端列を検索し、一致した行の指定列のセルの値を返す関数）を使って「codeTable」から銘柄コードを検索しています。

さらに、replace関数を使って「:」を「%3a」に置換しています。これは、URLで英数字以外の予約文字を使う場合、「%xx」といった文字列に置換するルール（「:」の場合は「%3a」に置換）があるためです。「URLエンコード」と呼ばれるこのルールについては、次回以降に説明する予定です。

(2)では、(1)で組み立てた接続文字列をクエリーテーブルの接続用に設定しています。 (3)では、"接続"というブックの接続を更新しています。

以上で銘柄の変更とデータの更新は完成です。VBエディタを閉じ、［開発］タブの［デザインモード］をOFFの状態にして、コンボボックスの銘柄を選択しなおすと、データが更新されることを確認してみてください。

株価チャートを作成する部分については、本論から外れますので詳細な説明は省略しますが、priceTableの列を株価チャート用に入れ替え、データの部分を選択し、タイトルに銘柄を表示しています。完全なコードは、下記からダウンロードしてご覧ください。

以下説明するリストはsample.lzhの中に収録しています。サンプルとしてご活用ください。

まとめ

今回は株価の例で説明しましたが、Webクエリを作る手順でURLを変更すれば、外国為替、投資信託などについても同様に取得できます。ぜひ、一度お試しください。

WINGSプロジェクト 遠藤 存著/山田祥寛監修
WINGS プロジェクトについて
テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。2011年5月時点での登録メンバは35名で、現在も一緒に執筆をできる有志を募集中。