"Inversing" the output of a JSON-request

HJA14

Board Regular
Joined
Apr 12, 2016
Messages
60
Hi all,

I would welcome some help with the following macro:

Code:
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


An example of such a JSON-request is the following output Example
The macro obtains the data in such a way that the data of Today is located in LastColumn. The problem with my database in Excel is that all the complimentary data is storaged in the opposite way, where Today can be found in Column A. I need to align the data. Due to the size of the file, I, ideally, don't want to use MATCH- and INDEX-formula's. How can my macro be re-written in such a way that the data is produced from recent --> old instead of old --> recent?
Thanks in advance,
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top