日付からその日の気象データを検索・取得してセルに自動入力する

Excel

Excelで報告書を作成する作業は、もはや誰も疑問を抱かないくらい当たり前のこととなり、Wordというソフトウェアが忘れ去られて久しい今日この頃、皆様いかがお過ごしでしょうか。

報告書にその日の気象条件を記載する場面は多々あるかと思います。現地で実測していれば、その値を書けばいいのですが、そんなにシビアな情報は必要なく、だいたいの温度湿度と晴れていたのか、雨だったのかくらいがわかればいいことが大半です。そんなときは私は毎度、気象庁のWEBサイトから過去の気象データ検索ページで調べて記載しています。

検索結果はWEBサイトに表示されるので、Excelへの転記が必要になるわけですが、このちょっとした手間さえも面倒に感じて自動化できないかと思うのがプロ(?)のVBAerです。コンピューターにやらせましょう。

Pythonスクリプトで気象庁の気象データを取得

VBAオンリーでできないこともなさそうですが、コスパがめちゃ悪くなりそうなのでPythonスクリプトでWEBスクレイピングし取得したデータをExcelに渡してVBAでセルにぶち込む方法を考えます。PythonにはBeautifulSoupという奇天烈な名前ながら優秀なWEBスクレイピングツールが存在しますので簡単にできます。

気象庁の過去データは観測地点、日付等の情報をURLに含めて送ることで、どの日のデータを表示するか決定されています。通常はWEBサイト上のリンクをクリックしていってそれらを決定するのですが、URLとして直打ちしても同じことです。

次のURLは埼玉県さいたま市2022年1月1日の1時間ごとの気象データを表示するページです。

https://www.data.jma.go.jp/obd/stats/etrn/view/hourly_a1.php?prec_no=43&block_no=0363&year=2022&month=01&day=01&view=p1

URLの以降を変更することで地点、日付を任意に指定できます。それぞれが何を示すかは文字列を見るとだいたいわかると思います。

結果データは次のような表形式(テーブル)で構成されています。時をあらわす行と項目ごとの列の位置関係は変わらないので、表から行・列数を指定して必要なデータのみを取得できます。

ということで、まずはPythonスクリプトだけを組みます。最初は確認も兼ねてCUIで日付と時刻を与えたら結果を返すようにします。ソースコードです。

import datetime
import requests
from bs4 import BeautifulSoup

# 埼玉県さいたま市
prec_no = '43'
block_no = '0363'

# 必要な情報の列の位置
COL_HOUR = '1'
COL_PRECIPITATION = '2'
COL_TEMPERATURE = '3'
COL_HUMIDITY = '6'

# タイトル2行分ずらして時をカウントする
TITLE_ROW = 2

print("さいたま市過去気象データ検索システム\n気象庁のWebサイトから指定日時のデータを取得します。")
di = input("日付を指定してください。yyyy/m/d >>")
ds = datetime.datetime.strptime(di, '%Y/%m/%d')
h = input("時刻を指定してください。h >>")

y = ds.year
m = ds.month
d = ds.day

url = f'https://www.data.jma.go.jp/obd/stats/etrn/view/hourly_a1.php'
query = f'?prec_no={prec_no}&block_no={block_no}&year={y}&month={m}&day={d}'

res = requests.get(url + query)
html = BeautifulSoup(res.text, 'html.parser')

tgt_row = str(TITLE_ROW + int(h))
tgt_row_selector = f'table#tablefix1 > tr:nth-child({tgt_row})'
hour = html.select_one(tgt_row_selector + f' > td:nth-child({COL_HOUR})').text
precipitation = html.select_one(tgt_row_selector + f' > td:nth-child({COL_PRECIPITATION})').text
temperature = html.select_one(tgt_row_selector + f' > td:nth-child({COL_TEMPERATURE})').text
humidity = html.select_one(tgt_row_selector + f' > td:nth-child({COL_HUMIDITY})').text

print(f'{di} {hour}時の気温は{temperature}℃ 湿度は{humidity}% 降水量は{precipitation}mm')

なお2022年12月現在、気象庁では過去の気象データを配信するAPIは運用されていないので、このようなWEBページから取得する手法を使わざるを得ません。

実行すると

上の表と比較してもらうと、ちゃんと取得できていることがわかります。

Excelと連携させる

Pythonスクリプトの動作は問題ないので、裏に引っ込んでもらいます。日付、時刻などのパラメーターはコマンドライン引数として与えるようにします。また、もうソースコードをいじることはないので定数をインライン化してコード量を減らします。

import datetime
import requests
import sys
from bs4 import BeautifulSoup

ds = datetime.datetime.strptime(sys.argv[1], '%Y/%m/%d')
h = sys.argv[2]
prec_no = sys.argv[3]
block_no = sys.argv[4]

url = f'https://www.data.jma.go.jp/obd/stats/etrn/view/hourly_a1.php'
query = f'?prec_no={prec_no}&block_no={block_no}&year={ds.year}&month={ds.month}&day={ds.day}'
res = requests.get(url + query)
html = BeautifulSoup(res.text, 'html.parser')
tgt_row = str(2 + int(h))
tgt_row_selector = f'table#tablefix1 > tr:nth-child({tgt_row})'
hour = html.select_one(tgt_row_selector + f' > td:nth-child(1)').text
precipitation = html.select_one(tgt_row_selector + f' > td:nth-child(2)').text
temperature = html.select_one(tgt_row_selector + f' > td:nth-child(3)').text
humidity = html.select_one(tgt_row_selector + f' > td:nth-child(6)').text

print(hour + ',' + temperature + ',' + humidity + ',' + precipitation)

これをPyinstallerで実行ファイルにします(Python環境完備ならしなくていいです)。

実行してみると

同じようにデータが取得できました。

Excel側でVBAからこのファイルを実行し、結果を受け取りセルに代入すればいいですね。

パラメーターを設定する枠と結果を受け取る枠をシートに作成します。

それに合わせてコードを書きます。シートごと他へコピーして使い回したいので、標準モジュールではなくシートモジュールに記述しておきます。

セルの値を引数にしてWScript.Shellで実行ファイル(get_weather.exe)をコマンドラインで起動します。実行ファイルが気象庁のWebサイトから与えられたパラメーターに従ってデータを取得してきます。結果は標準出力としてCSV形式で出力されるのでStdOut.ReadLineでVBA側でそれを取得し、Splitで「,」区切りで配列にし、セルに代入します。

Sub getWeather()
    Dim wss As Object
    Set wss = CreateObject("WScript.Shell")
    
    Dim cmd As String
    Dim d As String, h As String, prec As String, block As String, fp As String
    d = Range("b1").Value
    h = Range("b2").Value
    prec = Range("b3").Value
    block = Range("b4").Value
    fp = Range("b5").Value
    cmd = fp & " " & d & " " & h & " " & prec & " " & block
    
    Dim wse As Object
    Set wse = wss.exec(cmd)
    
    Do While wse.Status = 0
        DoEvents
    Loop

    Dim result
    result = Split(wse.StdOut.ReadLine, ",")
    Range("b7").Value = Val(result(1))
    Range("b8").Value = Val(result(2))
    Range("b9").Value = Val(result(3))
End Sub

プロシージャをボタンに割り当てて実行すると

あとは実際に表示したいセルにここから参照を作れば完成です。

WEBSERVICE関数でできる?→ダメ

ExcelにWEBSERVICEという関数があります。引数にURLをわたすとインターネットからデータを引っ張ってきてセルに表示できます。

これとFILTERXML関数を組み合わせてつかうと、WEB-APIが配信しているxmlをWEBSERVICEで取得してFILTERXMLで解析することで必要なデータを関数だけで取り出せます。

おっ?htmlもxmlとたいして変わらんし、これで今回のネタもいけんじゃね?と思いましたが、結果は「ダメ」です。エラーになります。どこでコケているのか調べるために各関数を別個で出力するとこうなります。

WEBSERVICE関数は正常に取得できています。FILTERXML関数でエラーになっています。なんでかなと要素をどんどん削っていってみると、

ここまで減らしてもだめで

ここまで減らして正常に取得できました。

すなわちFILTERXML関数は1つでも閉じていないタグが存在するとエラーするということですね。htmlの解析では使いものにならず、xml専用です。

FILTERXMLでやらずにテキスト解析系(LEFT、RIGHT、MIDなど)でやればできそうですが、気が狂いそうなほどのネストが必要な予感がするのでやりません。

使用方法

1.ダウンロードボタンでzipファイルを入手し展開します。

2.tenki.xlsmのシートを実際に使用したいファイルにコピー(シート名右クリックから)します。

3.get_weather.exeを適当な場所へ配置しパスをコピーします。

4.コピーしたパスをシートのスクリプトパスへ貼り付けます。

5.データ取得ボタンを右クリックでマクロの登録→wsWeather.getWeatherを登録します。

6.日付 時刻 都道府県№ ブロック№ を入力します

都道府県№とブロック№は、一度普通にWEBページにアクセスして確認するか、ここまできたら気象庁のページからスクレイピングして全部一覧にしてやろうかと思っていたら、すでにやっている方がいらっしゃったので、そちらを参照してください。

アメダス観測所一覧

7.データ取得ボタンを押すとデータが取得されます。

一瞬黒いウィンドウがでますが、WEBスクレイピングの結果を出力するためのものです。

サンプルファイルを入手する

Python環境があるPCではソースコードをコピペしてお使いいただくほうがよろしいです。Pyinstallerで実行ファイル化するとどうしてもファイルサイズが大きくなってしまいます。

いつも通りエラーハンドラーはないので、ご了承ください。

例のマルウェアの影響でMicrosoftが得体の知れない出所のマクロ実行に、さらなる制限をかけるようになりました普通には実行できずにひと手間必要です。詳細はマクロ実行警告メッセージのヘルプを参照してください。

利用上のご注意

  • ダウンロードしたファイルを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • Windows11 Microsoft365 環境でのみ動作確認済み。

Downloadボタンを押下した時点で注意事項に同意したものとみなします。

tenki.zip

おわり。