Excel VBAでe-Stat APIを使用して、クラウド上にある政府統計データの取得・加工を行う例を紹介します。e-Statは日本の統計を閲覧できる政府統計ポータルサイトで、開発者向けにAPIによるデータ取得機能も提供しています。
・操作環境：Windows 11
・使用プラン：Microsoft 365 Business Premium(※)
※Excelが使用できれば、上記プランでなくても問題ありません
なお、この記事では、VBAの使用許可や開発メニュー表示といった初期設定や、VBAエディタの使い方の説明は割愛いたします。
- e-Stat APIの使用を開始する
- 統計データのAPIのURLを取得する
- PostmanでAPIの動作確認を行う(推奨)
- Excelファイルを用意する
- Excel VBAでe-Statのデータを取得・加工する
- おわりに
e-Stat APIの使用を開始する
e-Stat APIを使用するには、ユーザー登録が必要です。未登録の場合は、画面右上の[新規登録]から登録します。
※e-Statホーム画面：政府統計の総合窓口
ログイン後、右上の[マイページ]を選択します。
[API機能(アプリケーションID発行)>API機能]を選択し、以下の通り入力し、[発行]を選択します。
・名称：(適宜入力)
・URL：(APIを使用したWebアプリを公開する場合のURL指定します。公開しない場合は、http://test.localhost/とします)
※アプリケーションIDは3個まで発行できます
[appId]欄にアプリケーションIDが表示されたら、値を控えておきます。
※取得したアプリケーションIDは複数の統計で使用できます。アプリ公開目的でなければ、一個発行すればそれを流用できます
左上の[トップページ]を選択して、次の工程に進みます。
統計データのAPIのURLを取得する
右側の[開発者向け]を選択します。
[API機能]を選択します。
[機能概要>提供データ]を選択します。
提供データのリンクを選択します。
[データ種別]が[データベース]となっている統計がAPI機能が提供されているものです。
この例では、統計名のキーワード[サービス]で絞り込み、[サービス産業動向調査]を選択します。
画面下部の[統計データ]のリンクを選択します。
[データベース]の概要リンクを選択します。
この例では、[2013年1月から>月次調査>年次]を選択します。
この例では、[表番号]の[00205]の統計データを使用することとし、その行の[API]を選択します。
[ファイル形式]は[CSV形式]を選択します。[URLをコピー]を選択してこの値を控えておきます。
PostmanでAPIの動作確認を行う(推奨)
いきなりVBAで実装するのではなく、先にAPI開発ツール「Postman」でデータ取得時のデータのやりとりを確認することをおススメします。Postmanの入手方法は以前の記事を参考にしてください。
この例におけるPostmanの設定および動作確認の方法を紹介します。
まずは環境を新規作成します。コレクションに直接設定値をベタ打ちもできますが、同じ接続先に色々なリクエストを試したい場合、ここで設定を変数化しておくと便利です。今回のアプリケーションIDのようなセキュアな値はタイプをシークレットにします。
・app_id：工程1で控えたアプリケーションID
※{{xxx}}はxxxという変数として扱われます
コレクションにデータ取得用のリクエストを新規作成します。
・メソッド：GET
・URL：工程2で控えたAPIのURL
・パラメーター>appId>値：{{app_id}}
※{{app_id}}は環境の画面で定義した変数app_idです。
設定できたら、[送信]を選択します。
送信結果を確認すると、工程2の最後に指定した通り、CSV形式となっています。
この例では28行目までが基本情報、29行目がタイトル行(“tab_code”から始まる行)、30行目以降がデータ行です。なお、統計データにより行数は若干異なります。
このままExcelにコピー&ペーストしても統計データとして使用しにくい状態なので、次の工程のExcel VBAにてAPIからのデータ取得と加工を行います。
Excelファイルを用意する
VBAを実行するExcelファイルを用意します(拡張子はxlsm)。
・Menuシート：[データ取得]ボタン(ActiveXコントロール)を押すと、e-Stat APIに接続し(工程2で控えたAPIのURLを使用)、データ取得と加工を行います。
・Searchシート：Menuシートの[データ取得]ボタンを押すと、e-Statのデータを取得・加工してシートに出力します。
※統計データとして使用しやすいよう、value列の値は数値(データがない場合はブランク)に変換しています
・paramシート：工程1で控えたアプリケーションIDの値を定義しておきます。
・VBAプロジェクト：Mainシート上にソースを実装します。画面に表示されているのは、データ取得ボタンクリック時にデータ取得・加工のプロシージャを呼び出すソースと、クリアボタンクリック時にAPIのURLをクリアするソースです。データ取得・加工のプロシージャの中身は次の工程で紹介します。
[ツール>参照設定]を選択し、[Microsoft Scripting Runtime]のチェックをオンにします。
これが未実施の場合、API呼び出し時のHTTPリクエストオブジェクトの作成でエラーが発生します。
Excel VBAでe-Statのデータを取得・加工する
データ取得・加工のメインとなるプロシージャのソースです。ポイント事項は以下の通りです。
- API実行時のアプリケーションIDはparamシートから取得し、MainシートのURL(TextBox1)に結合している
- 検索結果はString型の変数apiResponseで取得し、エラーがあれば詳細情報を表示(エラー発生時は2行目にエラーコード、3行目にエラー内容が格納されるため、これを表示)している
Private Sub getReseachData()
'変数宣言
Dim wb As Workbook
Dim ws As Worksheet
Dim ws_param As Worksheet
Dim apiMethod As String
Dim apiUrl As String
Dim apiHeaders As New Dictionary
Dim apiResponse As String
Dim data_row As Variant 'apiResponseをlf改行で分割格納した変数
'ワークシートの設定 、データ初期化
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Search")
ws.Rows("1:" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Delete
Set ws_param = wb.Worksheets("param")
'データ取得
apiMethod = "GET"
apiUrl = Left(TextBox1.Text, InStr(TextBox1.Text, "?")) & "appId=" & _
ws_param.Cells(1, 2).Value & _
Mid(TextBox1.Value, InStr(TextBox1.Text, "?") + Len("appID=") + 1)
apiHeaders.RemoveAll
apiHeaders.Add "Content-type", "application/json"
apiResponse = callRestApi(apiMethod, apiUrl, apiHeaders) 'API実行
data_row = Split(apiResponse, vbLf)
'データ取得でエラーが発生した場合、ダイアログにエラー番号と内容を表示して終了
If UBound(data_row) < 4 Then
MsgBox "データ取得でエラーが発生しました。:" & _
data_row(1) & ":" & data_row(2), vbOKOnly, "データ取得エラー"
Exit Sub
End If
'取得したデータを加工し、searchシートに書き出す
Call writeSearchData(ws, data_row)
End Sub
データ取得・加工のメインプロシージャから呼び出しているサブ関数・プロシージャのソースです(API実行、データ加工・書き出し)。ポイント事項は以下の通りです。
- この例ではAPIで取得するデータの形式をCSVとしたため、API実行関数の戻り値はJson型ではなくString型としている
- データ加工・書き出し関数では、"tab_code"で始まる行をタイトル行とし、以降の行をデータ行とし、Searchシートへの書き出し対象としている
- データ加工・書き出し関数では、各項目データの囲み文字""を除き、value列のデータ行以外は文字列として頭に'をつけている
- データ加工・書き出し関数では、value列のデータ行は数値として扱い、統計データがない場合(元データは"-"となっている)はブランクに置き換えている
'*****************************************
' callRestApi：API実行(CSVデータ取得)
'*****************************************
Private Function callRestApi(method As String, url As String, headers As Dictionary) As String
Dim http As Object
Dim cnt As Long
'httpリクエストオブジェクトの設定
Set http = CreateObject("msxml2.xmlhttp") '参照設定でMicrosoft Scripting Runtimeが必要
http.Open method, url, False
'httpリクエストヘッダーの設定
For cnt = 0 To headers.Count - 1
http.setRequestHeader headers.keys(cnt), headers.Items(cnt)
Next cnt
'リクエスト送信
http.send
'レスポンスの文字列をそのまま返す
callRestApi = http.responseText
End Function
'*****************************************
' writeSearchData：取得データのsearchシートへの書き出し
'*****************************************
Private Sub writeSearchData(ws As Worksheet, data_row As Variant)
Dim data_col As Variant 'data_rowを,で分割格納した変数
Dim cnt_row As Long
Dim cnt_col As Long
Dim start_row As Long 'タイトル行の行番号(行が"tab_code"で始まる)
Dim value_col As Long 'value列の列番号(タイトルが"value")
For cnt_row = 0 To UBound(data_row)
If data_row(cnt_row) Like """tab_code""" & "*" Then
start_row = cnt_row '"tab_code"で始まる行はタイトル行であり、行番号を格納
End If
If cnt_row >= start_row Then 'タイトル行以降を書き出し対象とする
data_col = Split(data_row(cnt_row), ",")
For cnt_col = 0 To UBound(data_col)
If cnt_row = start_row And data_col(cnt_col) = """value""" Then
value_col = cnt_col 'タイトル行かつvalue列の場合、列番号を格納
End If
If cnt_row > start_row And cnt_col = value_col Then 'データ行かつvalue列の場合
If data_col(cnt_col) <> """-""" Then '値が"-"でない場合、"を除き出力
ws.Cells(cnt_row - start_row + 1, cnt_col + 1).Value = _
Replace(data_col(cnt_col), """", "")
End If
Else 'データ行でないかvalue列でない場合、先頭に'を付加し、"を除き出力
ws.Cells(cnt_row - start_row + 1, cnt_col + 1).Value = _
"'" & Replace(data_col(cnt_col), """", "")
End If
Next cnt_col
End If
Next cnt_row
End Sub
おわりに
この記事で紹介した通り、e-StatのAPIを使ったデータ取得は比較的簡単に実施できます。e-Statは情報量が非常に多く、APIの扱いよりも、欲しいデータに辿りつくまでの方が苦労しそうだと思いました。
情シス担当の仕事としてe-Statのデータを使う機会は少ないかもしれませんが、参考になれば幸いです。
