从Excel VBA调用Bloomberg BQL查询

Sub GetStockPrice()
    Dim BloombergAPI As Object
    Dim QueryResult As Variant
    
    ' 初始化Bloomberg API对象
    Set BloombergAPI = CreateObject("Bloomberg.API")
    
    ' 定义查询语句
    Dim QueryString As String
    QueryString = "GET /api/v1/bql/query?query=SELECT%20*%20FROM%20symbols%20WHERE%20name%20IN%20('AAPL',%20'GOOGL')"
    
    ' 执行查询
    On Error Resume Next
    QueryResult = BloombergAPI.Execute(QueryString)
    If Err.Number <> 0 Then
        MsgBox "Error executing BQL query: " & Err.Description
        Exit Sub
    End If
    
    ' 处理查询结果
    Dim ResultData As Variant
    ResultData = QueryResult("data")
    
    ' 输出结果到Excel
    Dim i As Integer
    i = 1
    For Each Item In ResultData
        Cells(i, 1).Value = Item("name")
        Cells(i, 2).Value = Item("price")
        i = i + 1
    Next Item
    
    MsgBox "Stock prices retrieved successfully!"
End Sub