json - 反转 JSON 请求的输出

Question

我编写了一个发送 JSON 请求的宏

Sub getPrices()

Dim strURL As String, strJSON As String, strTicker As String, strCurrency As String, strLength As String
Dim i As Integer
Dim i2 As Integer
Dim http As Object
Dim JSON As Object, Item As Object
Dim LastColumn As Long
Dim lastrow As Long
With ActiveSheet
    LastColumn = .Cells(9, .Columns.Count).End(xlToLeft).Column
    lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
End With


For x = 10 To lastrow

strTicker = Cells(x, 2).Value
strCurrency = Cells(6, 2).Value
strLength = Cells(5, 2).Value
strURL = "https://min-api.cryptocompare.com/data/histoday?fsym=" & strTicker & "&tsym=" & strCurrency & "&limit=" & strLength & "&aggregate=3&e=CCCAGG"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strJSON = http.ResponseText
i = 3

Set JSON = JsonConverter.ParseJson(strJSON)
For Each Item In JSON("Data")
Cells(x, i).Value = Item("close")
i = i + 1

Next
Next

End Sub

这种 JSON 请求的一个示例是以下输出示例

宏以Today的数据位于LastColumn的方式获取数据。我在 Excel 中的数据库的问题是所有免费数据都以相反的方式存储，今天可以在A 列中找到。我需要对齐数据。由于文件的大小，理想情况下，我不想使用MATCH 和 INDEX公式。如何重写我的宏，使数据是从最近的 --> 旧的而不是旧的 --> 最近的？

提前致谢，

标签： jsonexcelvbaweb-scraping

Answer 1

JSON("Data")您可以向后循环集合 , 。在这个简化的例子中：

代码：

Option Explicit

Public Sub getPrices()

    Dim strURL As String, strJSON As String, http As Object, JSON As Object, item As Long

    strURL = "https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strJSON = http.ResponseText

    Set JSON = JsonConverter.ParseJson(strJSON)

    For item = JSON("Data").Count To 1 Step -1   'JSON("Data")(item) <== dictionary
        Debug.Print JSON("Data")(item)("close")
    Next item

End Sub

示例输出：

原始回复顺序：