Looping and Parsing a JSON-request

[HJA14]

Dear all,

I have an excel-file with in cells B1, C1, ... , LastColumn1 the name of certain stocks.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Details[/TD]
[TD]BTC[/TD]
[TD]ETH[/TD]
[TD]NEO[/TD]
[TD]LTC[/TD]
[TD]...[/TD]
[TD]"LastColum"[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Next, I want to send a JSON request to get the Open, High, Low and Close price of these assets. An example can be found here:

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

With the following output:

[COLOR=#000000]{"Response":"Success","Type":100,"Aggregated":true,"Data":[{"time":1493769600,"close":1507.77,"high":1609.84,"low":1424.05,"open":1445.93,"volumefrom":338807.89999999997,"volumeto":523652428.9200001},[/COLOR]

However, I only want the "Close" variable displayed in row 2 and below. Next, I wrote the following macro:

Option Explicit

Sub getClose()




Dim strURL As String, strJSON As String, strTicker As String, strCurrency As String, strLength As String
Dim i As Integer
Dim http As Object
Dim JSON As Dictionary, Item As Dictionary
Dim LastColumn As Long
With ActiveSheet
    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With


For i = 2 To LastColumn




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


Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strJSON = http.responsetext
Set JSON = JsonConverter.ParseJson(strJSON)
For Each Item In JSON("Data")
.Cells(i, 2).Value = Item("close")
Next


Next


End Sub

However, the macro gives an error in the following line:

.Cells(i, 2).Value = Item("close")

JsonConverter is not part of native VBA and I downloaded / imported it from github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas. In addition, I enabled Microsoft Scripting Runtime in the Preferences.

Any idea why the code is not showing any output? Thank you in advance!!

 

[John_w]

Move the strURL = line below the strLength = line. I also think you need a separate row index/counter variable in the For Each Item loop instead of the i loop variable.

 

[actorcat2000]

Dear all,

I have an excel-file with in cells B1, C1, ... , LastColumn1 the name of certain stocks.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Details[/TD]
[TD]BTC[/TD]
[TD]ETH[/TD]
[TD]NEO[/TD]
[TD]LTC[/TD]
[TD]...[/TD]
[TD]"LastColum"[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Next, I want to send a JSON request to get the Open, High, Low and Close price of these assets. An example can be found here:

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

With the following output:

[COLOR=#000000]{"Response":"Success","Type":100,"Aggregated":true,"Data":[{"time":1493769600,"close":1507.77,"high":1609.84,"low":1424.05,"open":1445.93,"volumefrom":338807.89999999997,"volumeto":523652428.9200001},[/COLOR]

However, I only want the "Close" variable displayed in row 2 and below. Next, I wrote the following macro:

Option Explicit

Sub getClose()




Dim strURL As String, strJSON As String, strTicker As String, strCurrency As String, strLength As String
Dim i As Integer
Dim http As Object
Dim JSON As Dictionary, Item As Dictionary
Dim LastColumn As Long
With ActiveSheet
    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With


For i = 2 To LastColumn




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


Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strJSON = http.responsetext
Set JSON = JsonConverter.ParseJson(strJSON)
For Each Item In JSON("Data")
.Cells(i, 2).Value = Item("close")
Next


Next


End Sub

However, the macro gives an error in the following line:

.Cells(i, 2).Value = Item("close")

JsonConverter is not part of native VBA and I downloaded / imported it from github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas. In addition, I enabled Microsoft Scripting Runtime in the Preferences.

Any idea why the code is not showing any output? Thank you in advance!!

took me all day, but i finally got it working,,, had to put strLength in a1 because i couldn't find any other place to put it... now if i could just get the one i was working on before to work...



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(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
End With




For i = 2 To lastrow

For i2 = 2 To LastColumn

strTicker = Cells(1, i2).Value
strCurrency = Cells(i, 1).Value
strLength = Cells(1, 1).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


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

Next i2


Next i