如何使用VBA在Excel中拉取雅虎财经Yahoo Finance的Put Options数据

前置准备 #

• 打开Excel后按Alt+F11进入VBA编辑器，点击顶部菜单「工具-引用」，勾选Microsoft Scripting Runtime、Microsoft XML, v6.0两个选项，确认后关闭引用窗口
• 脚本默认将数据写入当前激活的工作表，运行前可先选中你要存放数据的空白表

完整代码 #

Sub FetchYahooPutOptions()
    Dim http As MSXML2.XMLHTTP60
    Dim json As Object
    Dim targetTicker As String
    Dim putOptions As Object
    Dim i As Integer, outputRow As Integer
    
    ' 此处修改为你要查询的股票代码，比如特斯拉改为"TSLA"
    targetTicker = "AAPL"
    
    Set http = New MSXML2.XMLHTTP60
    ' 构造请求地址
    http.Open "GET", "https://query1.finance.yahoo.com/v7/finance/options/" & targetTicker, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36"
    http.Send
    
    If http.Status <> 200 Then
        MsgBox "请求失败，请检查股票代码是否正确或网络连接", vbExclamation
        Exit Sub
    End If
    
    ' 解析返回的JSON数据
    Set json = JsonConverter.ParseJson(http.ResponseText)
    Set putOptions = json("optionChain")("result")(1)("options")(1)("puts")
    
    ' 写入表头
    outputRow = 1
    Cells(outputRow, 1) = "行权价"
    Cells(outputRow, 2) = "到期日"
    Cells(outputRow, 3) = "买价"
    Cells(outputRow, 4) = "卖价"
    Cells(outputRow, 5) = "成交量"
    Cells(outputRow, 6) = "隐含波动率"
    outputRow = outputRow + 1
    
    ' 循环写入所有认沽期权数据
    For i = 1 To putOptions.Count
        Cells(outputRow, 1) = putOptions(i)("strike")
        Cells(outputRow, 2) = DateAdd("s", putOptions(i)("expiration"), "1970-01-01") ' 转换时间戳为正常日期
        Cells(outputRow, 3) = putOptions(i)("bid")
        Cells(outputRow, 4) = putOptions(i)("ask")
        Cells(outputRow, 5) = putOptions(i)("volume")
        Cells(outputRow, 6) = putOptions(i)("impliedVolatility")
        outputRow = outputRow + 1
    Next i
    
    ' 自动调整列宽
    Columns("A:F").AutoFit
    MsgBox "认沽期权数据拉取完成，共导入" & putOptions.Count & "条记录", vbInformation
    
    Set http = Nothing
    Set json = Nothing
    Set putOptions = Nothing
End Sub

补充说明 #

• 上面代码用到的JsonConverter是VBA常用的JSON解析模块，你可以直接搜索VBA-JSON的公开源码复制到你的工程里即可使用，不需要额外安装插件
• 如果不需要某类字段，直接删除对应写入行的代码即可
• 单次请求默认返回最近到期日的期权数据，如果需要拉取其他到期日的，可以在请求地址后加?date=时间戳参数指定到期日

内容的提问来源于stack exchange，提问作者Zeeshan Hafeez