在Excel VBA中提取JSON

问题描述

我想通过Excel VBA从Robin Hood API解析股票报价.

I want to parse stock quotes from the Robin Hood API via Excel VBA.

说我想要亚马逊，它是https://api.robinhood.com/quotes/?symbols=AMZN.

Say I want Amazon, which is https://api.robinhood.com/quotes/?symbols=AMZN.

哪个会产生:

{
   "results":[
      {
         "ask_price":"1592.3900",
         "ask_size":100,
         "bid_price":"1591.0000",
         "bid_size":500,
         "last_trade_price":"1592.3900",
         "last_extended_hours_trade_price":"1592.0000",
         "previous_close":"1600.1400",
         "adjusted_previous_close":"1600.1400",
         "previous_close_date":"2018-05-07",
         "symbol":"AMZN",
         "trading_halted":false,
         "has_traded":true,
         "last_trade_price_source":"consolidated",
         "updated_at":"2018-05-08T23:58:44Z",
         "instrument":"https://api.robinhood.com/instruments/c0bb3aec-bd1e-471e-a4f0-ca011cbec711/"
      }
   ]
}

使用类似此答案的示例，我已经安装了 VBA-JSON 并打开Microsoft脚本运行时.

Using an example like this answer, I have installed VBA-JSON and turned on Microsoft Scripting Runtime.

我的代码:

Public Sub STOCKQUOTE()

 Dim http As Object
 Set http = CreateObject("MSXML2.XMLHTTP")

 Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
 http.Open "GET", sURL, False
 http.send

 Dim jsonResponse As Dictionary
 Set jsonResponse = JsonConverter.ParseJson(http.responseText)

 Dim results As String
 Set results = jsonResponse("results")

 MsgBox results
End Sub

但这不起作用，相反，我在行Set results = jsonResponse("results")上得到了Compiler Error: Object Required.

But this doesn't work, instead I get Compiler Error: Object Required for the line Set results = jsonResponse("results").

如果我添加Debug.Print http.responseText，我会看到正确的JSON，但是知道我在做什么错吗?

If I add Debug.Print http.responseText I see the correct JSON, but any idea what I'm doing wrong?

VBA-JSON已正确安装，因为如果我使用他们的示例，它将工作正常:

VBA-JSON is installed correctly, because if I use their example, it works fine:

Dim Json As Object
Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

但是，如果我尝试将Dictionary更改为Object，则会得到Run-time error '450': Wrong number of arguments or invalid property assignment.

But if I try changing Dictionary to Object, I get Run-time error '450': Wrong number of arguments or invalid property assignment.

推荐答案

您的json有一个名为results的对象.可能存在但没有多个result对象.您只有一个，所以我认为这会导致混乱.每个result都将在jsonResponse词典中获得其自己的条目.该词典中的ITEM本身就是词典.

Your json has an object called results. There could be, but isn't, multiple result objects. You have only one, so I think it's leading to confusion. Each result is going to get it's own entry in your jsonResponse dictionary. The ITEM in that dictionary will, itself, be a dictionary.

处理字典中的字典的最佳方法是声明一个新字典，我在att中调用属性"，然后通过jsonResponse字典的每次迭代填充该字典.尽管您只有一个result:

The best way to deal with iterating through the dictionary in a dictionary is to declare a new dictionary, I'm calling att for "Attributes" and then fill that dictionary with each iteration through the jsonResponse dictionary. It will only iterate once though as you only have one result:

Public Sub STOCKQUOTE()

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
    http.Open "GET", sURL, False
    http.send

    Dim jsonResponse As Dictionary
    Set jsonResponse = JsonConverter.ParseJson(http.responseText)

    Dim att As Dictionary

    For Each att In jsonResponse("results")
     Debug.Print att("last_trade_price")
    Next att
End Sub

或者，因为您只有一个结果，所以可以通过jsonResponse词典中的索引来引用该结果，然后使用它作为属性.这使代码更小，但是如果您从REST查询中获得多个结果，它将永远丢失.没什么大不了的，因为您不希望发生这种情况:

Alternatively, because you have only a single result, you could just refer to that result by it's index in the jsonResponse dictionary and then it's attribute you are after. This makes the code smaller, but if you ever get more than one result from your REST query it will be lost forever. No biggie though since you don't expect that to happen:

Public Sub STOCKQUOTE()

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
    http.Open "GET", sURL, False
    http.send

    Dim jsonResponse As Dictionary
    Set jsonResponse = JsonConverter.ParseJson(http.responseText)

    MsgBox (jsonResponse("results")(1)("last_trade_price"))

End Sub

这篇关于在Excel VBA中提取JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！