Hi all,
I would welcome some help with the following macro:
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,
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,