Creating JSON and extracting data

VBA developers are used to working with xlsx and csv data formats, but JSON, which was born for exchanging data over the internet, is a new format to them. In order to work with RESTful APIs through VBA, you need the ability to handle JSON formats.

The VBA-JSON library makes it easy for VBA developers to handle JSON, but the VBA-JSON GitHub lacks particularly friendly code examples, so I created some test code while creating a trading program.

Let's start by creating the JSON.

Sub ConvertingJson()
  Dim dic As New Dictionary
  Dim Json As New Collection
  
  dic.Add "BS", "BUY"
  dic.Add "DATE", "20190515"
  dic.Add "price", 57800
  dic.Add "RSI", 0.5219
  dic.Add "SIGNAL", 0.3978
  Json.Add dic
  Set dic = Nothing
  
  dic.Add "BS", "SELL"
  dic.Add "DATE", "20190519"
  dic.Add "price", 59800
  dic.Add "RSI", 0.5219
  dic.Add "SIGNAL", 0.3978
  Json.Add dic
  
  Debug.Print JsonConverter.ConvertToJson(Json, 1, 0)
  
End Sub

When you run the above procedure, it creates the following JSON data.

[
 {
  "BS": "BUY",
  "DATE": "20190515",
  "price": 57800,
  "RSI": 0.5219,
  "SIGNAL": 0.3978
 },
 {
  "BS": "SELL",
  "DATE": "20190519",
  "price": 59800,
  "RSI": 0.5219,
  "SIGNAL": 0.3978
 }
]

This is an example of reading JSON and looping through it, or reading a specific item.

Sub ParsingJson()
  Dim szJson As String
  Dim Json As Collection
  Dim jsonItem As Dictionary
  Dim i As Long
  
  szJson = "[ _
    {""BS"":""BUY_"",""DATE"":""20190814"",""PRICE"":39950,""RSI"":0.3658,""SIGNAL"":0.3183}, 
    {""BS"":""SELL"",""DATE"":""20190826"",""PRICE"":40750,""RSI"":0.4214,""SIGNAL"":0.4512}, 
    {""BS"":""BUY_"",""DATE"":""20200317"",""PRICE"":41200,""RSI"":0.4206,""SIGNAL"":0.4142}, 
    {""BS"":""SELL"",""DATE"":""20200318"",""PRICE"":38100,""RSI"":0.3722,""SIGNAL"":0.4058}, 
    {""BS"":""LAST"",""DATE"":""20201028"",""PRICE"":54100,""RSI"":0.4589,""SIGNAL"":0.4687} 
  ]"
  Set Json = JsonConverter.ParseJson(szJson)

' 항목을 루프돈다
  For i = 1 To Json.Count
    Set jsonItem = Json.Item(i)
    Debug.Print jsonItem("BS"), jsonItem("DATE"), jsonItem("PRICE"), jsonItem("RSI"), jsonItem("SIGNAL")
  Next

' JSON의 마지막 항목을 읽는다
  Debug.Print Json(Json.Count)("BS"), Json(Json.Count)("DATE"), Json(Json.Count)("PRICE"), _
 Json(Json.Count)("RSI"), Json(Json.Count)("SIGNAL")
End Sub