J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す(続き、銘柄リスト取得)
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で取得したリフレッシュトークンを取得するため1行下に置き換え
'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 に接続する記事です。
1)J-Quants API への接続をChatGPTに聞いてExcel VBAに書き直してみた
2)J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す(続き、銘柄リスト取得)
3)J-QuantsAPI をChatGPTに聞いてExcel VBAで書き直す(続き、銘柄リスト取得 その2)
<完成版>
4)J-QuantsAPI のデータをExcelで取得する
(一応の完成形。フリープランの全機能をExcel VBA で実装しました。)