Json.count

[actorcat2000]

i'm having trouble with Json.count,,, heres my sub:

Sub chart()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

    If Worksheets("chart").ChartObjects.Count > 0 Then
        Worksheets("chart").ChartObjects.Delete
    End If
    
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim symbols As String
Dim symbol As String
Dim quotes As String
Dim quoten As String
Dim quotex As String
Dim n As Integer
Dim n2 As Integer
Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer
Dim i2 As Integer

Set wb = ActiveWorkbook
Set ws = Sheets("chart")
ws.Activate

lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
'ws.Columns("B:Z").AutoFit
ws.Range("B3:ZZ100000").ClearContents
'Debug.Print lastcolumn

For i = 3 To 4

    symbols = symbols & ws.Range("A" & i).Value & ","

Next i

symbols = Left(symbols, Len(symbols) - 1)



Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & symbols & "&types=quote,chart&range=1y&last=5"

    myrequest.Send
        
        Dim Json As Object
        Dim item As Object
        Dim z As Integer
        
        
'*************        
Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
        
Set item = Json("SPY")(Json.Count)("close")
       
 'z = Json("SPY")("chart")(Json.Count)("close")
        
Debug.Print item
        
Stop
'**************


For n = 3 To 4
        
        symbol = Cells(n, 1).Value
        symbol = UCase(symbol)
        
        If n = 3 Then Cells(2, 2).Value = Json(symbol)("quote")("companyName")
        If n = 4 Then Cells(2, 3).Value = Json(symbol)("quote")("companyName")
        
        If n = 3 Then Cells(3, 13).Value = Json(symbol)("quote")("latestPrice")
        If n = 4 Then Cells(4, 13).Value = Json(symbol)("quote")("latestPrice")

        For x = 1 To z
        
            y = Json(symbol)("chart")(x)("close")
            Cells(x + 2, n - 1).Value = y
            y = ""
      
    
        Next x
        
  Stop

Next n
            
        For x = 1 To z
            
            y = Json(symbol)("chart")(x)("date")
            Cells(x + 2, 4).Value = y
            y = ""
 
        
        Next x

        
Macro2 'module 1
        
'ws.Columns("B:Z").AutoFit

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Sub Macro2()
'
' Macro2 Macro
'

'



Dim i As Integer
Dim x As Integer

For i = 3 To 253

    For x = 5 To 6
    
    If x = 5 Then Cells(i, x).Value = Cells(i, 2) / Cells(i, 3)
    If x = 6 Then Cells(i, x).Value = Cells(i, 3) / Cells(i, 2)
    
    Next x
    
Next i

Cells(3, 7).Value = Cells(3, 5) / Cells(3, 6)

    
    Range("G3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
    Range("G3").Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
For i = 3 To 253
Cells(i, 6).Value = Cells(i, 6) * Cells(1, 6)
Next i

Dim rng As Range
Dim cht As Object


'Your data range for the chart
  Set rng = ActiveSheet.Range("D3:F253")

'Create a chart
  Set cht = ActiveSheet.Shapes.AddChart

'Give chart some data
  cht.chart.SetSourceData Source:=rng

'Determine the chart type
  cht.chart.ChartType = xlLine

Range("A1").Select
End Sub

heres the hyperlink:

https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,fb&types=chart&range=1y&last=5

i have QQQ and SPY on lines A3 and A4 of sheet

i have my for next loops numbered 3 to 253, but that number changes from day to day, and i can't figure out how to get Json.count to work...

Json.count wont give my the number of "close" items though,
i try Set item = Json("SPY")(Json.Count)("close") and i get a 'type mismatch' or an object required,
if i try the z = Json("SPY")("chart")(Json.Count)("close") then i get the first price of the spiders close...

any help would be greatly appreciated...

or you can download it at my dropbox...

https://www.dropbox.com/s/k99ud16zmzhmc2r/Book2.xlsm?dl=0

 

[John_w]

Try one of these. Both return 253.

    Set item = Json("SPY")("chart")
    Debug.Print item.Count

    Debug.Print Json("SPY")("chart").Count

 

[actorcat2000]

Try one of these. Both return 253.

    Set item = Json("SPY")("chart")
    Debug.Print item.Count

    Debug.Print Json("SPY")("chart").Count

oh, halelujah, tyvm...
that worked...