Hello,
So I found some way to do it, with the Restlibrary.
All credit should go to the guys that did this : http://ramblings.mcpher.com/Home/excelquirks/json
You can download their complete excel file with all the examples and the Restlibrary here:
http://ramblings.mcpher.com/Home/excelquirks/downlable-items/cDataSet.xlsm
Here is an example on how to use it with a macro that will get me the current opened positions (command requestTrades in the Signal API v2).
Of course you need to replace “XXX” and “YYY” by your own apiKey and systemId
Sub PopulateC2RequestTradesOpened()
Dim sURL As String
Dim sJson As String
Dim xmlDoc As New DOMDocument
Dim sEnvlength As Integer
Dim responseText As String
Dim sC2RequestTrades As String
Set objHTTP = New MSXML2.XMLHTTP
’ URL for requestTrades command
sURL = “https://collective2.com/world/apiv2/requestTrades”
'JSON
sJson = sJson & "{"
sJson = sJson & " ““apikey””: ““XXX””,"
sJson = sJson & " ““systemid””: ““YYY””“
sJson = sJson & " }”
objHTTP.Open “POST”, sURL, False
objHTTP.SetRequestHeader “Content-Type”, "application/json"
objHTTP.Send (sJson)
xmlDoc.LoadXML (objHTTP.responseText)
sC2RequestTrades = objHTTP.responseText
’ Change the quotes format
sC2RequestTrades = Replace(sC2RequestTrades, “”"", “’”)
'Display the info retrieved from C2
MsgBox sC2RequestTrades
'Now use Restlibrary, then we can parse the JSON
Dim cj As cJobject, rout As Range, ds As cDataSet, job As cJobject
’ C2RequestTrades must be defined in the Restlibray Module in Function createRestLibrary()
’ restQuery will not do the query to C2 here, it has been done before, we only use it to create the JSON objects
With restQuery(“C2”, “C2RequestTrades”, , , , , , False, , , True, sC2RequestTrades)
’ clear the sheet
wholeSheet(“C2”).Cells.ClearContents
’ add the headings by getting every single key in the top level array
' For Each job In .datajObject.children
' For Each cj In job.children
' if we dont already have this heading then add it
' If rout Is Nothing Then
' Set rout = firstCell(wholeSheet("C2"))
' rout.Value = cj.key
' Else
' If (cleanFind(makeKey(cj.key), rout) Is Nothing) Then
' Set rout = rout.Resize(, rout.columns.count + 1)
' lastCell(rout).Value = cj.key
' End If
' End If
' Next cj
'Next job
’ Or just Add the headings we want to be displayed
Set rout = firstCell(wholeSheet(“C2”))
rout.Value = “symbol”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “trade_id”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “open_or_closed”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “openedWhen”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “quant_opened”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “opening_price_VWAP”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “closedWhen”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “closing_price_VWAP”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “PL”
’ now make a dataset of the headings
Set ds = New cDataSet
ds.populateData rout
’ Use some date to filter the positions if needed
Dim sYesterday, sToday As String
sToday = Format(Date, “yyyy-MM-dd”)
sYesterday = Format(DateAdd(“d”, -1, Date), “yyyy-MM-dd”)
'add the data values
For Each job In .datajObject.children
' Here we only keep "Open" positions and discard the other ones
If job.cValue("open_or_closed") = "closed" Then 'Or left(job.cValue("openedWhen"), 10) <> sToday Then ' Just keep positions opened today
job.remove
Else
For Each cj In job.children
If cj.key = "symbol" Or cj.key = "trade_id" Or cj.key = "open_or_closed" Or cj.key = "openedWhen" Or cj.key = "quant_opened" Or cj.key = "opening_price_VWAP" Or cj.key = "closedWhen" Or cj.key = "closing_price_VWAP" Or cj.key = "PL" Then
With ds.headingRow.exists(cj.key)
.where.Offset(job.childIndex).Value = cj.Value
End With
End If
Next cj
End If
Next job
’ clear up dataset
ds.tearDown
’ clear up restquery
.tearDown
End With
End Sub
