Json VBA分析-运行时错误424-需要对象

Question

我正在尝试从Yahoo API json响应中提取regularMarketPreviousClose的值。

我在-Set price = reply("result")行中得到一个运行时错误424-需要对象。

有人能找出这个代码中的错误吗。我已经提供了我的代码和从Yahoo API获得的json数据。

这是我从雅虎API 得到的json响应文本

{
"quoteResponse": {
"result": [
{
"language": "en-US",
"region": "US",
"quoteType": "EQUITY",
"typeDisp": "Equity",
"quoteSourceName": "Delayed Quote",
"triggerable": false,
"customPriceAlertConfidence": "LOW",
"currency": "USD",
"tradeable": false,
"exchange": "PNK",
"shortName": "Tandy Leather Factory, Inc.",
"longName": "Tandy Leather Factory, Inc.",
"messageBoardId": "finmb_30741",
"exchangeTimezoneName": "America/New_York",
"exchangeTimezoneShortName": "EDT",
"gmtOffSetMilliseconds": -14400000,
"market": "us_market",
"esgPopulated": false,
"marketState": "REGULAR",
"fullExchangeName": "Other OTC",
"financialCurrency": "USD",
"regularMarketOpen": 4.95,
"averageDailyVolume3Month": 9237,
"averageDailyVolume10Day": 22020,
"fiftyTwoWeekLowChange": 1.2399998,
"fiftyTwoWeekLowChangePercent": 0.33423173,
"fiftyTwoWeekRange": "3.71 - 5.3",
"fiftyTwoWeekHighChange": -0.35000038,
"fiftyTwoWeekHighChangePercent": -0.066037804,
"fiftyTwoWeekLow": 3.71,
"fiftyTwoWeekHigh": 5.3,
"dividendDate": 1407456000,
"earningsTimestamp": 1639991127,
"earningsTimestampStart": 1648065600,
"earningsTimestampEnd": 1648238400,
"trailingAnnualDividendRate": 0,
"trailingPE": 50.510204,
"trailingAnnualDividendYield": 0,
"epsTrailingTwelveMonths": 0.098,
"epsForward": 0.43,
"epsCurrentYear": 0.26,
"priceEpsCurrentYear": 19.038462,
"sharesOutstanding": 8543230,
"bookValue": 6.038,
"fiftyDayAverage": 4.9554,
"fiftyDayAverageChange": -0.005400181,
"fiftyDayAverageChangePercent": -0.0010897568,
"twoHundredDayAverage": 4.89935,
"twoHundredDayAverageChange": 0.050649643,
"twoHundredDayAverageChangePercent": 0.010338033,
"marketCap": 42288988,
"forwardPE": 11.511627,
"priceToBook": 0.8198078,
"sourceInterval": 15,
"exchangeDataDelayedBy": 0,
"averageAnalystRating": "2.0 - Buy",
"firstTradeDateMilliseconds": 749136600000,
"priceHint": 4,
"regularMarketChange": -0.05000019,
"regularMarketChangePercent": -1.0000038,
"regularMarketTime": 1648141256,
"regularMarketPrice": 4.95,
"regularMarketDayHigh": 4.95,
"regularMarketDayRange": "4.95 - 4.95",
"regularMarketDayLow": 4.95,
"regularMarketVolume": 604,
"regularMarketPreviousClose": 5,
"displayName": "Tandy Leather Factory",
"symbol": "TLFA"
},
{
"language": "en-US",
"region": "US",
"quoteType": "EQUITY",
"typeDisp": "Equity",
"quoteSourceName": "Nasdaq Real Time Price",
"triggerable": true,
"customPriceAlertConfidence": "HIGH",
"currency": "USD",
"tradeable": false,
"exchange": "NCM",
"shortName": "ChromaDex Corporation",
"longName": "ChromaDex Corporation",
"messageBoardId": "finmb_5034211",
"exchangeTimezoneName": "America/New_York",
"exchangeTimezoneShortName": "EDT",
"gmtOffSetMilliseconds": -14400000,
"market": "us_market",
"esgPopulated": false,
"marketState": "REGULAR",
"fullExchangeName": "NasdaqCM",
"financialCurrency": "USD",
"regularMarketOpen": 2.52,
"averageDailyVolume3Month": 421352,
"averageDailyVolume10Day": 520790,
"fiftyTwoWeekLowChange": 0.5400001,
"fiftyTwoWeekLowChangePercent": 0.2797928,
"fiftyTwoWeekRange": "1.93 - 11.2",
"fiftyTwoWeekHighChange": -8.73,
"fiftyTwoWeekHighChangePercent": -0.77946424,
"fiftyTwoWeekLow": 1.93,
"fiftyTwoWeekHigh": 11.2,
"earningsTimestamp": 1646841720,
"earningsTimestampStart": 1651694400,
"earningsTimestampEnd": 1652126400,
"trailingAnnualDividendRate": 0,
"trailingAnnualDividendYield": 0,
"epsTrailingTwelveMonths": -0.404,
"epsForward": -0.15,
"epsCurrentYear": -0.29,
"priceEpsCurrentYear": -8.5172415,
"sharesOutstanding": 68126000,
"bookValue": 0.464,
"fiftyDayAverage": 2.664,
"fiftyDayAverageChange": -0.194,
"fiftyDayAverageChangePercent": -0.072822824,
"twoHundredDayAverage": 5.90605,
"twoHundredDayAverageChange": -3.4360502,
"twoHundredDayAverageChangePercent": -0.5817848,
"marketCap": 168724224,
"forwardPE": -16.466665,
"priceToBook": 5.323276,
"sourceInterval": 15,
"exchangeDataDelayedBy": 0,
"pageViewGrowthWeekly": 0.02642558,
"averageAnalystRating": "1.7 - Buy",
"firstTradeDateMilliseconds": 1214400600000,
"priceHint": 4,
"regularMarketChange": -0.03999996,
"regularMarketChangePercent": -1.5936241,
"regularMarketTime": 1648145420,
"regularMarketPrice": 2.47,
"regularMarketDayHigh": 2.99,
"regularMarketDayRange": "2.45 - 2.99",
"regularMarketDayLow": 2.45,
"regularMarketVolume": 221155,
"regularMarketPreviousClose": 2.51,
"bid": 2.46,
"ask": 2.47,
"bidSize": 9,
"askSize": 10,
"displayName": "ChromaDex",
"symbol": "CDXC"
}
],
"error": null
}
}

这是我的VBA代码

Sub RequestURL()
Dim i As Integer
Dim rstring As String
Dim item As Object
Dim lrow As Integer: lrow = Range("A10000").End(xlUp).Row
rstring = "https://yfapi.net/v6/finance/quote?region=US&lang=en&symbols="

For i = 2 To lrow
rstring = rstring & Tsheet.Range("A" & i).Value & "%2C"
Next i
rstring = Left(rstring, Len(rstring) - 3)
Dim request As New WinHttpRequest
Dim key As String
key = "mykey"
request.Open "get", rstring
request.SetRequestHeader "X-API-Key", key
request.Send
If request.Status <> 200 Then
MsgBox request.ResponseText
Else
MsgBox request.ResponseText
End If

' To access Data

Dim reply As Object
Set reply = JsonConverter.ParseJson(request.ResponseText)
Dim price As Collection
Set price = reply("result")
Dim result As Dictionary
For Each result In price
Debug.Print result("regularMarketPreviousClose")
Next
End Sub

Answer 1

JSON使用名称-值对格式。花括号表示对象，方括号表示数组。结果是一个对象本身，其中包含一组对象。由于，它在quoteResponse内部。你必须先处理它，然后再处理里面的结果

Set price = reply("quoteResponse")("result")