金融市場祝日カレンダーの自動取得
0
回答する


Excel VBA

金融市場祝日カレンダーをどこかのウェブサイトから社内で管理しているエクセルに自動で取り込みたいと思っているのですが、どのようにすれば実現できるでしょうか?

具体的に参照する(信頼性のある)ホームページとそれをエクセルに落とす関数・VBAのサンプルを教えていただけると助かります。

祝日カレンダーは国によっては年の途中で変更される可能性もあると理解しているため、都度ウェブサイトに参照しにいく仕組みなのかな、と考えています。

よろしくお願いします。

Naoki 2019.02.14 07:37:34 361

コメントする

コメントするには ログイン していただく必要があります。

回答 2


単純な表ならデータタブのWebからでできるのですが、最近は取り込めない場合もあるようです。

もしテキストファイルで配布されているのであれば、データタブのデータの取得の中のテキストファイルとかでURLを指定して取り込めばいいのではないかと思います。

これらで取り込んだものは、リアルタイムでは更新されないのですが、そのブックを開いたタイミングで読み込み自動で更新してくれます。

ただこれらの方法で取り込めない時もたくさんあるので、そうなってくるとVBAで取り込む必要も出てきます。

でもVBAで作ればそれなりにコストもかかるでしょうから、その前にWEBSERVICE関数とかFILTERXML関数とかでなんとかできないか考えてみたほうがいいでしょう。

佐藤嘉浩(Excelの魔法使い・スピラー) 2019.02.16 23:39:02

追記です。 http://klug-fx.jp/holiday/ ここのURLでカレンダーをWebからで取り込めましたね。 うまく文字を処理すれば関数だけでなんとかなりそうな気もしないでもないですね。ただ、何月かの情報がとれないです・・・
さらに追記です。http://klug-fx.jp/holiday/?calendar=201903のように年月でURLを指定すればその月のカレンダーになりそうなので、VBAと組み合わせればさらにさらにいけると思います。でもそれだとWebから取り込みじゃなくてフルVBAにした方がいいんですかねー。
Naoki 2019.03.07 20:44:41
ありがとうございます。やはり世界各国のカレンダーを取得しようとするこのサイトがいいのかもしれないですね。関数だけで解決できる方法があったらかなりとっつきやすいのですが、VBAとなると少し敷居が高そうです。関数のスペシャリストが現れることを期待します!
多分、WEEKDAY関数とかと組み合わせればカレンダー文字が何曜日かとか分類できると思うので組み合わせればできそうな気がします。最近はカレンダーとWEEKDAY関数の組み合わせの情報もググれば出てくるようなので、使用用途に合ったやり方をチョイスするといいかなと思います。

コメントする

コメントするには ログイン していただく必要があります。


金融市場のカレンダーでしたらJPX(東証)のサイトの情報が信頼できるのではないでしょうか?

https://www.jpx.co.jp/corporate/about-jpx/calendar/

Image from clipboard


VBAでこのカレンダーから祝日のリストを取得するには、次のステップで行います。

  1. JPXのサイトをスクレイピング
  2. DOMを解析
  3. 日付をExcelに貼り付け


1. JPXのサイトをスクレイピング

VBAでウェブサイトに接続してデータを取得する方法はいくつかありますが、「MSXML2.XMLHTTP」オブジェクトを利用するのが手軽だと思います。

Dim http: Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://www.jpx.co.jp/corporate/about-jpx/calendar/", False
http.send
Dim res: res = http.responseText

以上で、変数resにはJPXのサイトから取得したHTML文字列が格納されます。


2. DOMを解析

次に取得したHTMLから目的となる休日データを抽出します。VBAでHTMLを解析するには「MSHTML.HTMLDocument(htmlfile)」オブジェクトを利用するのが便利です。

Dim doc: Set doc = CreateObject("htmlfile")
doc.write res

これだけで、文字列のHTMLがパースされHTMLDocumentオブジェクトとして扱えるようになります。


3. 日付をExcelに貼り付け

最後に、HTMLDocumentオブジェクトを通じて日付情報を取得し、Excelのシートに出力します。今回はA列の一番上のセルから下方向に日付を入力していきます。

Dim i As Integer: i = 1
Dim table
For Each table In doc.getElementsByTagName("table")
    Dim tr
    For Each tr In table.getElementsByTagName("tr")
        Dim d: d = Left(tr.FirstChild.innerText, 10)
        If IsDate(d) Then
            Cells(i, 1).Value = CDate(d)
            i = i + 1
        End If
    Next
Next

まず、HTMLに含まれるテーブル(table)要素を抽出し、For Eachを使って一つ一つ処理します。そして、それぞれのテーブルに対して、テーブルに含まれる行(tr)要素を同様に抽出します。日付はtr要素の最初の子要素であるtd要素に含まれているので、FirstChild.innerTextによってその値を取得することができます。

取得した日付は「2019/01/01(火)」という形式になっていますので、Left関数を使って曜日以外の部分を抽出します。そして、IsDate関数を使って日付形式として正しいかどうかをチェックした上で、CDate関数を使って文字列としての日付を日付形式に変換しています。最後にA列のセルにその値を設定して終わりです。

全体のコードは次になります。

Option Explicit

Sub GetHolidays()

    'JPXのサイトをスクレイピング
    Dim http: Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://www.jpx.co.jp/corporate/about-jpx/calendar/", False
    http.send
    Dim res: res = http.responseText

    'DOMを解析
    Dim doc: Set doc = CreateObject("htmlfile")
    doc.write res

    '日付をExcelに貼り付け
    Dim i As Integer: i = 1
    Dim table
    For Each table In doc.getElementsByTagName("table")
        Dim tr
        For Each tr In table.getElementsByTagName("tr")
            Dim d: d = Left(tr.FirstChild.innerText, 10)
            If IsDate(d) Then
              Cells(i, 1).Value = CDate(d)
              i = i + 1
            End If
        Next
    Next
End Sub

※この投稿は祝日カレンダーをVBAを使って取得する技術的な方法を紹介するが目的です。実際にプログラムを使ってJPXサイトからデータを取得することの可否はJPX社にお尋ねください。

shirohonoka 2019.03.01 01:19:10 (2019.03.01 01:26:26 更新)

Naoki 2019.03.07 20:47:59
ありがとうございます。確かにJPXサイトなら信頼性のあるデータが取得できますね。ただ、私の質問の仕方も不十分でしたが、世界中の金融市場のカレンダーを取得するためには、やはり佐藤嘉浩(Excelの魔法使い・スピラー)さんが紹介いただいているようなまとめサイトを使うしかないのかと思ってます。

コメントする

コメントするには ログイン していただく必要があります。


回答する
質問に回答するには ログイン していただく必要があります。

一覧に戻る