Excel, Wordの自動操作のハードル削減計画・その4

～ WebのテーブルをExcelで取り込む～

2020年5月17日

［はじめに］

　今回は ExcelにおいてWebのテーブル(表)を取り込みます。

　JuseOffice04.zipにサンプルが入っています。

　主に株価データの取得が中心です。

　ExcelのQueryTableを利用するのでその関連の解説も少し加えます。

［目次へ］

1.　[webの表を取り込む]

　JuseOfficeの中で [webの表を取り込む] というサブルーチンを定義しています。

　[ワークシート] が指し示すワークシートにWebのテーブルを取り込むものです。

　引数は次の三つ。

url: WebのURLを指定。
tn: 取り込むTableNumber。"1,2" のように指定。
　"" だと全てのテーブルを取り込む。
　複数のテーブルが空白行で区切られて一つのワークシートに入る。
startCell: 取り込みの開始位置のセル。
　[ワークシート].Range("A1") のように指定。
　"A1" のように番地を文字で指定してもかまわない。

　例としてzipに入っている 01 気象情報.wsf の主要部分を掲げると下のとおり。

　気象庁の「毎日の全国データ一覧表」を取り込む例です。

[ワークブックを開く] "気象情報.xlsx"
url = "http://www.data.jma.go.jp/obd/stats/data/mdrr/synopday/data1s.html"
[webの表を取り込む] url, "", "A1"
[ワークブックを保存]
[エクセルを終了]

［目次へ］

2.　日本の株価データを取得

　ExcelでWebから情報を取得するのであれば、
表計算の対象となるデータということになります。

　その典型が株価データだろうとおもいます。

　ここでは Yahoo Finance のサイトを利用します。

　一つのWebページに20営業日の株価が掲載されるので
過去300日分を得たいときは 15ページにアクセスする必要がありますが、
そのやり方は後に回すとして、まずは 1ページ分(20日分)を取得します。

　url は次のとおりです。

https://info.finance.yahoo.co.jp/history/?code={code}

　{code} の6文字を実際のコードに置き換えます。

日経平均: 998407
トピックス: 998405
MAXIS トピックス上場投信: 1348

　最初の二つ(6桁のコード)は売買しない指標のコード、
最後(4桁のコード)が売買可能な証券コードです。

　売買可能なものには「出来高」がありますが、指標のデータにはありません。

　日付の順番は、最新のものが一番上にきて、古いものほど下になります。

　上の三つを取得するスクリプト 02 株価データ20.wsf の主要部分を掲げます。

stockFile = "株価データ20.xlsx"
[ワークブックを開く] stockFile
urlOrg = "https://info.finance.yahoo.co.jp/history/?code={code}"
For Each code In Split("998407 998405 1348")
    sheetName = "code" & code
    Set ws = [ワークシートを切り替え](sheetName)
    If TypeName(ws) = "Worksheet" Then
        ws.UsedRange.Clear  ' シートの中身を全消去
    Else
        Set ws = [空のワークシート]  ' 空のワークシートを選択
        ws.Name = sheetName
    End If
    url = Replace(urlOrg, "{code}", code)
    [webの表を取り込む] url, "", "A1"
Next
[ワークシートを切り替え](1).Activate  ' 第1シートをアクティブに
[ワークブックを保存]
[エクセルを終了]

　日本の証券市場は 15:00 に閉じますが、
サーバ上の時系列データが直ちに更新されるわけではありません。

　今日のデータを取り込むには 18:00 過ぎが確実かとおもいます。

［目次へ］

3.　過去300日分の株価を取得

　20日分のデータでは分析するのに量的に不足です。

　そこで、300日分のデータを得ることにします。

　そのための方法は少し煩雑になるのでサブルーチンを作りました。

　YahooFinance.vbs に関連の関数やサブルーチンが入っています。

(1)　[過去300日の株価を取り込む]

　YahooFinance.vbsに含まれている [過去300日の株価を取り込む] を使います。

　このサブルーチンの引数は証券コードだけです。

　[過去300日の株価を取り込む] "1348" のように呼び出します。

　株価データを記録したワークシートのシート名は code1348 のように設定されます。

　使い方をスクリプトとして示すと次のとおり。

Include "YahooFinance.vbs"
stockFile = "株価データ300.xlsx"
[ワークブックを開く] stockFile
[過去300日の株価を取り込む] "1348"
[ワークブックを保存]
[エクセルを終了]

　zipに入っている 03 株価データ300.wsf は複数の証券コードを取得する例になっています。

　サブルーチンの処理内容を少し詳しく紹介すると下のとおり。

ワークシート名が code1348 のシートがないか確認し、あれば処理せず終了。
空のワークシートを選択(株価データ記録用)
urlを生成し、[webの表を取り込む] でデータを取り込む。
　これを15回繰り返す。
　途中、取り込んだデータ中に「日付」のセルがなければそこで処理を中断。
　正常に取り込めたときは「日付」が必ずあるはず。
Webの取り込み終了後、データ領域のセル幅を自動調整(AutoFit)。
データ領域の名前を code1348 にする(名前付き範囲の設定)。
ワークシート名を code1348 に変更

　名前付き範囲を設定しているのは、
Excelファイルをデータベースとして扱うときに便利だからです。

(2)　株価の時系列データ取得のためのurl

　過去300日分の株価を得る場合、urlを次のようにして生成します。

　その手順の大枠を列記します。

上限の年・月・日(最新：今日の日付)を算出
下限の年・月・日(古い方の限界の日付)を算出
　300営業日を得るので500日くらい前の年月日を算出
証券コード、上限・下限の年月日、ページ番号(1～15)を組みあらせてurlを生成

　url生成の詳細は、YahooFinance.vbsに含まれている
YahooFinURL(code, num) の中身を参照してください。

(3)　[株価データを更新]

　300日分のデータを得るのには15ページを取得する必要があり時間がかかります。サーバにも負荷をかけます。

　そこで、既に300日分のデータがワークブック内にあるのなら、
最新の1ページだけを取得して既存のデータに追加するよう考えます。

　それをやるためのサブルーチンが [株価データを更新] です。

　これも引数として証券コードだけを与えます。

　使い方は [過去300日の株価を取り込む] と同じです。

　スクリプトの形で示すと次のとおり。

Include "YahooFinance.vbs"
stockFile = "株価データ300.xlsx"
[ワークブックを開く] stockFile
[株価データを更新] "1348"
[ワークブックを保存]
[エクセルを終了]

　ワークブック内に code1348 というワークシートがなければ、
過去300日分のデータを取得します。

　code1348 のワークシートがあれば、最新のデータをそこに追加します。

　最新のデータを読み取ってはみたものの、
すでにワークシートにそれが書かれていれば
その旨のメッセージを出して追加処理はしません。

　過去300日分のデータが既にあるものの、
20営業日以上の空きがあって、しばらくぶりにアクセスする場合は
[株価データを更新] を使うと、取り込めない隙間の期間が生じるので
ご注意ください。

　久しぶりにアクセスするときは、300日分を改めて取得するのが無難です。

　zipに 04 株価データ300更新.wsf が入っているので
よかったら覗いてみてください。

［目次へ］

4.　ボリンジャーバンドにかかわる数値の算出

　株価を分析する手法の一つにボリンジャーバンドがあります。

　25日間の株価の平均値と標準偏差を求めて、それと今日の株価を比較する手法です。

　株価としては通常「終値」を用います。

　75日間というのも比較的よく用いられるようです。

(1)　平均値と標準偏差

　平均値は、25日間の各々の株価の総和を25で割り算したものです。

　標準偏差は、まず期間内の各々の株価と平均値との差の二乗値を総和します。

　そして、その総和を25で割った上でその平方根を求めます。それが標準偏差です。

　わかりにくいですが、要は「ばらつき」の大きさを示す指標です。

　標準偏差をよくσ(シグマ)という記号で表記します。

　このσが大きいほど25日間の変動幅が大きいことになります。

　また、σは統計的に次のような性質を持っています。

「平均値－σ」～「平均値＋σ」の範囲に約68%のデータが入る。
「平均値－2σ」～「平均値＋2σ」の範囲に約95%のデータが入る。

　注目の株価が「平均値＋2σ」以上だと
かなり珍しい高水準といえます。売り時かも(？)

　たとえば平均値が500、σが200の場合、
「平均値＋2σ」＝500＋2×200＝900 となります。

　「平均値－2σ」は 100 です。

　900以上、あるいは 100以下という値は、全体の95%からはみ出した珍しい値です。

　＋－σ、あるいは＋－2σの帯域をボリンジャーバンドといいます。

　ただ、このσに関する統計的な性質がみられるのは
正規分布といわれるデータの集まりの場合です。

　グラフ化すると釣り鐘の形になるのが正規分布。中央の最も高いところが平均値。

　実は株価は正規分布になりません。

　安定した時期に限れば正規分布に近いかもしれませんが、
でも、トレンドが大きく動くことがあり、「いずれ平均値に戻る」とはかぎらない。

　株価が平均値から大きく離れた場合、
「安定期だからいずれ平均値に戻る」とみるのか
「新しいトレンドに入る兆候で、もっと上がる(下がる)」とみるのか
難しいところです。

　ボリンジャーバンドを一つの参考材料にしながらも、
いろいろな角度から考える必要があるのだろうとおもいます。

(2)　Excelにおける平均値等の算出

　Excelでは平均値、標準偏差それぞれを算出する関数があります。

　=average(A1:A25) だと A1～A25の平均が算出され、
=stdev(A1:A25) の場合は標準偏差が算出されます。

　標準偏差を求めるのに stdevp というのもありますが、
普通はどちらでも大差ないとおもいます。

　300にち分のデータがあって、25日を単位とする場合、
276日目から300日目までの25日間が計算できる最後の範囲です。

　277日目以降は残りが24日以下になるため計算できません。

　=average(A1:A25) から始まって、一つずつ下に移動しながら
=average(A276:A300) までをセルに書き込んでいくことになります。

　平均値と標準偏差だけだと、
注目の株価がバンド内でどの辺に位置するか分かりにくいので
「乖離比」というのを算出することにします。

　注目の株価と平均値との差を標準偏差で割って算出。

　平均値が500、標準偏差が200、注目の値が800の場合、
(800 - 500) / 200 = 1.5 を「乖離比」とします。

　800という値は、＋σより大きく＋2σよりは小さいことが分かります。

　注目の値が 400 だと、乖離比は (400 - 500) / 200 = -0.5 なので
平均値よりは小さく、－σよりは大きいことが分かります。

　なお「乖離比」は便宜的につけた名前です。他では通用しないとおもいます。

　私はチャートを見ることができないので、代替の数値として算出したものです。

(3)　[平均等をついか]

　YahooFinance.vbsに [平均等をついか] というサブルーチンがあります。

　株価データの右側に「平均、σ、乖離比」を追加します。

　与える引数は次の三つ。

rng: 株価データの領域(Range)。「日付」を左上端とする領域。
dataName: 平均値等の材料となる項目名。普通は「調整後終値*」または「終値」
days: 単位となる日数。25 とか 75 を指定。

　05 株価データ平均等を追加.wsf の主要部分を掲げておきます。

Include "YahooFinance.vbs"
stockFile = "株価データ300.xlsx"
If [ファイルがある](stockFile) = False Then
    MsgBox "'" & stockFile & "' がみつかりません."
    WScript.Quit
End If
[ワークブックを開く] stockFile
For Each ws In [空でないワークシート群]
    Set startCell = ws.UsedRange.Find("日付")
    If TypeName(startCell) = "Range" Then
        Set rng = startCell.CurrentRegion  ' 株価data全体
        dataName = "調整後終値*"
        If TypeName(rng.Find(dataName)) <> "Range" Then dataName = "終値"
        [平均等を追加] rng, dataName, 25
    End If
Next
[ワークブックを保存]
[エクセルを終了]

［目次へ］

5.　その他

　ニュースでは米国のダウ平均、ナスダックがよく紹介されます。

　そのデータの取得を簡単に取り上げようとおもいます。

　それと、[webの表を取り込む] の中身(QueryTableのプロパティ)に少し触れます。

(1)　米国の株価データ

　アメリカのYahooのサイトで株価をみるときは次のurlです。

https://finance.yahoo.com/quote/{code}/history?p={code}

　{code} の6文字を証券コードに置き換えて使います。

ダウ平均: %5EDJI
ナスダック: %5EIXIC

　アクセスすると100日分のデータを取得できます。

　YahooFinance.vbs に入っているサブルーチンは米国の株価取得には使えません。

　ただ、[平均等を追加] は利用できます。

　06 米国の株価データ.wsf は、ダウ平均とナスダックを取得して、
それに平均等を追加するサンプルです。

　下に主要部分を掲げておきます。

Include "YahooFinance.vbs"
stockFile = "米国の株価データ.xlsx"
[ワークブックを開く] stockFile
stockNames = Array("Dow Jones Industrial Average (^DJI)", _
    "NASDAQ Composite (^IXIC)")
urlOrg = "https://finance.yahoo.com/quote/{code}/history?p={code}"
i = 0
For Each code In Split("%5EDJI %5EIXIC")
    sheetName = "code" & code
    Set ws = [ワークシートを切り替え](sheetName)
    If TypeName(ws) = "Worksheet" Then
        ws.UsedRange.Clear  ' シートの中身を全消去
    Else
        Set ws = [空のワークシート]  ' 空のワークシートを選択
        ws.Name = sheetName
    End If
    ws.Range("A1").Value = stockNames(i)
    url = Replace(urlOrg, "{code}", code)
    [webの表を取り込む] url, "", [新先頭のセルon](ws).Offset(1)
    Set startCell = ws.UsedRange.Find("Date")
    If TypeName(startCell) = "Range" Then
        Set rng = startCell.CurrentRegion  ' 株価data全体
        dataName = "Adj Close**"
        If TypeName(rng.Find(dataName)) <> "Range" Then dataName = "Close*"
        [平均等を追加] rng, dataName, 25
    End If
    i = i + 1
Next
[ワークシートを切り替え](1).Activate  ' 第1シートをアクティブに
[ワークブックを保存]
[エクセルを終了]

(2)　QueryTableのプロパティ

　[webの表を取り込む] の中身を紹介します。

　ExcelのQueryTableにはいろんな使い方があるので
自分なりにカスタマイズしたくなるかもしれません。

　そんなときに参考にしてください。

　中身は下のとおり。

Sub [webの表を取り込む](ByVal url, ByVal tn, startCell)
    Dim ws, qt
    Set ws = [ワークシート]
    Set qt = ws.QueryTables.Add("URL;" & url, startCell)
    If tn = "" Then
        qt.WebSelectionType = 2  ' すべての表を得る
    Else
        qt.WebTables = tn
        qt.WebSelectionType = 3  ' 特定のtableのみ
    End If
    qt.WebFormatting = 3  ' xlWebFormattingNone
    qt.SaveData = False
    qt.RefreshPeriod = 0
    qt.BackgroundQuery = False
    qt.Refresh
End Sub

　QueryTableには様々なプロパティがあります。

　個人的に気になるものを中心に少し解説を加えます。

a.　WebFormatting

　これは、Webを取り込むときにどれだけの書式情報を組み入れるかの指定です。

　上ではデフォルト値の xlWebFormattingNone(数値3)を指定しています。

　書式情報を無視する指定です。

　これに対し xlWebFormattingAll(数値1)を指定すると、
例えば、セル結合情報がワークシート上に反映されます。

　その他、xlWebFormattingRTF(数値2)というのもありますが、
どう違ってくるのか確認していません。

b.　WebSelectionType

　このプロパティに xlSpecifiedTables(数値3)をセットすると、
WebTablesプロパティにセットされた番号のテーブルだけを取り込みます。

　xlAllTables(数値2)をセットしたときは全てのテーブルを取り込みます。

　そのほか xlEntirePage(数値1)をセットすると、
テーブルだけでなくWebページ全体を取り込みます。

　ただし、Excelはブラウザのように動作するわけではありません。

たとえば、ブラウザに対する指示命令である JavaScript が組み込まれていると
正しく取り込めなかったりします。

c.　SaveData

　このプロパティに True をセットすると、
QueryTableがワークシートと一緒に保存されます。

　保存すると、次にワークブックを開いたときに
Refreshするだけでテーブルを更新できます。

　変数 ws にワークシートオブジェクトがセットされていて
それに QueryTable が一つだけ所属している場合、
次のようにして QueryTable をrefreshできます。

Set qt = ws.QueryTable(1)
qt.Refresh

　QueryTable に名前がついていれば、番号でなく名前で指定することもできます。

　qt.Name = "日経平均" のようにして Nameプロパティで名前を設定できます。

～　以上　～

［はじめに］

1. [webの表を取り込む]

2. 日本の株価データを取得

3. 過去300日分の株価を取得

(1) [過去300日の株価を取り込む]

(2) 株価の時系列データ取得のためのurl

(3) [株価データを更新]

4. ボリンジャーバンドにかかわる数値の算出

(1) 平均値と標準偏差

(2) Excelにおける平均値等の算出

(3) [平均等をついか]

5. その他

(1) 米国の株価データ

(2) QueryTableのプロパティ

a. WebFormatting

b. WebSelectionType

c. SaveData

1.　[webの表を取り込む]

2.　日本の株価データを取得

3.　過去300日分の株価を取得

(1)　[過去300日の株価を取り込む]

(2)　株価の時系列データ取得のためのurl

(3)　[株価データを更新]

4.　ボリンジャーバンドにかかわる数値の算出

(1)　平均値と標準偏差

(2)　Excelにおける平均値等の算出

(3)　[平均等をついか]

5.　その他

(1)　米国の株価データ

(2)　QueryTableのプロパティ

a.　WebFormatting

b.　WebSelectionType

c.　SaveData