J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す（続き、銘柄リスト取得）

2023年5月1日 17:36

IDトークン取得

前回に続き今度は、IDトークン取得(/token/auth_refresh)のサンプルコードをExcel VBA に変換してみます。
仕様書ページは、こちらです。

公式ページのサンプルコードは以下になります。

# 公式ページIDトークン取得(/token/auth_refresh)のサンプルコード
import requests
import json

REFRESH_TOKEN = "YOUR refreshtokenID"
r_post = requests.post(f"https://api.jquants.com/v1/token/auth_refresh?refreshtoken={REFRESH_TOKEN}")
r_post.json()

やり方は、前回同様にChatGPT「書き直してちょうだい。お願い。」と丸投げするだけです（笑

質問
import requests
import json
REFRESH_TOKEN = "YOUR refreshtokenID"
r_post = requests.post(f"https://api.jquants.com/v1/token/auth_refresh?refreshtoken={REFRESH_TOKEN}")
r_post.json()をExcelVBAで書き直して下さい。

ChatGPT返答
以下は、VBAを使用して、APIリクエストを送信し、JSONレスポンスを解析する方法の例です。

Sub RefreshTokenRequest()
    Dim HttpReq As Object
    Dim Url As String
    Dim RefreshToken As String
    Dim Response As String
    Dim Json As Object
    
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    
    Url = "https://api.jquants.com/v1/token/auth_refresh?refreshtoken="
    RefreshToken = "YOUR refreshtokenID"
    
    Url = Url & RefreshToken
    
    HttpReq.Open "POST", Url, False
    HttpReq.setRequestHeader "Content-Type", "application/json"
    HttpReq.send
    
    Response = HttpReq.responseText
    Set Json = JsonConverter.ParseJson(Response)
    
    'ここからは、JSONレスポンスから必要な情報を抽出する例です
    Dim AccessToken As String
    Dim ExpiresIn As Integer
    
    AccessToken = Json("access_token")
    ExpiresIn = Json("expires_in")
    
End Sub

このコードを実行する前に、VBA-JSONコンバーターをインストールする必要があります。そのために、以下の手順を実行してください。

VBA-JSONのリポジトリから、最新バージョンのzipファイルをダウンロードします。
zipファイルを解凍し、中に含まれる"JsonConverter.bas"ファイルを適当な場所に保存します。
VBAエディタを開き、"ツール"→"参照設定"を選択します。
"参照設定"ダイアログボックスで、"参照の追加"をクリックします。
ファイル選択ダイアログボックスで、"JsonConverter.bas"ファイルを選択します。
"OK"をクリックしてダイアログボックスを閉じます。

--- 以上返答 -------------------

今回は"JsonConverter.bas"の説明が、詳しく丁寧になりました。ChatGPTが参照した元サイトが、良かったのでしょうか。

IdToken取得に成功

さっそくExcel VBA にコピーして動かしてみましょう。

Sub get_idtoken()
'Sub RefreshTokenRequest()
    Dim HttpReq As Object
    Dim Url As String
    Dim RefreshToken As String
    Dim Response As String
    Dim Json As Object
    
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    
    Url = "https://api.jquants.com/v1/token/auth_refresh?refreshtoken="
    ' get_rftokenで取得したリフレッシュトークンを取得するため１行下に置き換え
    'RefreshToken = "YOUR refreshtokenID"
    RefreshToken = Sheets("token").Cells(3, 2).Value
    
    Url = Url & RefreshToken
    
    HttpReq.Open "POST", Url, False
    HttpReq.setRequestHeader "Content-Type", "application/json"
    HttpReq.send
    ' print文を追加
    Debug.Print HttpReq.responseText
    
    Response = HttpReq.responseText
    Set Json = JsonConverter.ParseJson(Response)
    
    'ここからは、JSONレスポンスから必要な情報を抽出する例です
    Dim AccessToken As String
    Dim ExpiresIn As Integer
    
    '次の行はjsonkeyの名前が違うので1行下に置き換える。
    ' AccessToken = Json("access_token")
    AccessToken = Json("idToken")

    '次の行はidtokenには無いjsonkeyなのでコメントアウト。
    ' ExpiresIn = Json("expires_in")
    '指定のセルにidtokenを書き込む。
    Sheets("token").Cells(4, 2).Value = AccessToken
End Sub

ちょこちょこ4行くらい手を入れて完成です。
基本的なhttpアクセスの構造は全く触っていません。
それでもidTokenが取得できました。

次は銘柄情報取得

リフレッシュトークン、IDトークン取得と順調に出来ました。
ここまで来たら実際のデータの取得もChatGPT様に作ってもらいましょう。
欲望には切りがないですね。
おまけに自力で調べる気がなくなってしまいました（笑

公式のサンプルプログラムです。

import requests
import json

idToken = "YOUR idToken"
headers = {'Authorization': 'Bearer {}'.format(idToken)}
r = requests.get("https://api.jquants.com/v1/listed/info", headers=headers)
r.json()

それでは再びChatGTP様に丸投げしてみます。
丸投げばかりしているので、
名前を「丸なげ男」と変えた方がいいかも（笑

質問
import requests
import json
idToken = "YOUR idToken"
headers = {'Authorization': 'Bearer {}'.format(idToken)}
r = requests.get("https://api.jquants.com/v1/listed/info", headers=headers) r.json() をExcel VBAで書き直してください。"JsonConverter.bas"ファイルは設定済みなので解説は不要です。

ChatGPTの返答
以下は、VBAを使用して、APIリクエストを送信し、JSONレスポンスを解析する方法の例です。

Sub GetListedInfoRequest()
    Dim HttpReq As Object
    Dim Url As String
    Dim IdToken As String
    Dim Headers As Object
    Dim Response As String
    Dim Json As Object
    
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    
    Url = "https://api.jquants.com/v1/listed/info"
    IdToken = "YOUR idToken"
    
    Set Headers = CreateObject("Scripting.Dictionary")
    Headers("Authorization") = "Bearer " & IdToken
    
    HttpReq.Open "GET", Url, False
    For Each key In Headers
        HttpReq.setRequestHeader key, Headers(key)
    Next
    HttpReq.send
    
    Response = HttpReq.responseText
    Set Json = JsonConverter.ParseJson(Response)
    
    'ここからは、JSONレスポンスから必要な情報を抽出する例です
    Dim Symbol As String
    Dim Name As String
    Dim Price As Double
    
    Symbol = Json("symbol")
    Name = Json("name")
    Price = Json("price")
    
End Sub

この例では、"Authorization"ヘッダーを設定するために、Scripting.Dictionaryオブジェクトを使用しています。これは、ヘッダー名と値のペアを管理するための便利な方法です。また、"GET"メソッドを使用するため、HttpReq.sendメソッドに引数を渡していません。
--- 以上返答 -------------------

初めてのエラー

デバッグ、VBAProjectのコンパイルを実行すると
「コンパイルエラー
変数が定義されていません。」
と出ました。
初めてのエラーです。

場所は、
For Each key In Headers
の key です。

エラーの修正も聞いてみる

質問
提案のコードをメニューのデバッグ、VBAProjectのコンパイルとすると"For Each key In Headers" の"key"が、コンパイルエラー：変数が定義されていませんとなります。修正してください。

ChatGPTの返答
申し訳ありません。以下のように、変数"key"を宣言する必要があります。

Sub GetListedInfoRequest()
    Dim HttpReq As Object
    Dim Url As String
    Dim IdToken As String
    Dim Headers As Object
    Dim Response As String
    Dim Json As Object
    Dim key As Variant ' keyを宣言する
    
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    
    Url = "https://api.jquants.com/v1/listed/info"
    IdToken = "YOUR idToken"
    
    Set Headers = CreateObject("Scripting.Dictionary")
    Headers("Authorization") = "Bearer " & IdToken
    
    HttpReq.Open "GET", Url, False
    For Each key In Headers
        HttpReq.setRequestHeader key, Headers(key)
    Next
    HttpReq.send
    
    Response = HttpReq.responseText
    Set Json = JsonConverter.ParseJson(Response)
    
    'ここからは、JSONレスポンスから必要な情報を抽出する例です
    Dim Symbol As String
    Dim Name As String
    Dim Price As Double
    
    Symbol = Json("symbol")
    Name = Json("name")
    Price = Json("price")
    
End Sub

このように、変数"key"をVariant型で宣言することで、コンパイルエラーが解決されます。
--- 以上返答 -------------------

ちゃんとvariant型で宣言してきました。
いやー、できる子ですね。
というか、宣言忘れとは、AIもお茶目ですね（笑

上場銘柄一覧(/listed/info)を取得してみる

variant型の宣言でエラーも解決しました。
以下が走らせたコードです。

Sub GetListedInfoRequest()
    Dim HttpReq As Object
    Dim Url As String
    Dim IdToken As String
    Dim Headers As Object
    Dim Response As String
    Dim Json As Object
    Dim key As Variant      ' keyを宣言する。
    
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    
    Url = "https://api.jquants.com/v1/listed/info"
    ' IdTokenは、ワークシート上から取得のため、下の行に書き換える。
    'IdToken = "YOUR idToken"
    IdToken = Sheets("token").Cells(4, 2).Value
    
    Set Headers = CreateObject("Scripting.Dictionary")
    Headers("Authorization") = "Bearer " & IdToken
    
    HttpReq.Open "GET", Url, False
    For Each key In Headers
        HttpReq.setRequestHeader key, Headers(key)
    Next
    HttpReq.send
    
    Response = HttpReq.responseText
    Set Json = JsonConverter.ParseJson(Response)
    ' プリント文を追加。
    Debug.Print HttpReq.responseText
    
    'ここからは、JSONレスポンスから必要な情報を抽出する例です
    Dim Symbol As String
    Dim Name As String
    Dim Price As Double
    
    Symbol = Json("symbol")
    Name = Json("name")
    Price = Json("price")
    
End Sub

結果は成功!

デバッグウインドウにぞろぞろ銘柄リストが流れます。
最後の銘柄の一部を抜き出すと

 , {"Date": "2023-02-06", "Code": "99970", "CompanyName": "ベルーナ", "CompanyNameEnglish": "BELLUNA CO.,LTD.",

という感じです。

さすがにデータの各項目を抜き出す所は、自分で作る必要がありますが認証の部分や、パラメーターの送り方は全く調べる必要がありませんでした。

さらに間違いの修正も出来るのには驚きました。
今日は時間がないのでここまでです。

明日もやってみよーっと・・・仕事しろよ自分（笑

＊関連記事
Excel VBA で、J-Quants API に接続する記事です。
１）J-Quants API への接続をChatGPTに聞いてExcel VBAに書き直してみた
２）J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す（続き、銘柄リスト取得）
３）J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す（続き、銘柄リスト取得その2）
４）J-QuantsAPI のデータをExcelで取得する
（一応の完成形。フリープランの全機能をExcel VBA で実装しました。）

この記事が気に入ったらサポートをしてみませんか？