VBA issue with JSON body send

[HansSheller]

Hi,

I am building a program in Excel/VBA to execute operations in the trading platform CEX.io. I am using their API and it is working perfectly for any public call. However, when I need to use Private calls, I cannot manage to correctly send the parameters requested for the authorization. Every time I get the following response : "API key is missing.". Could anyone advise what I am doing wrong?

I thought it would be something about the JSON format in the body, but after trying many combinations I really have no idea what to do. I have succesfully created and activated the API key. The signature is hashed correctly, but it seems that I am not being able to even send any information in the body along with the call.

Thank you very much for the support.

Hans



Here Cex's API calls: https://cex.io/rest-api

Below my code:

---------------------------------------------------------

Sub get_balance()


    Dim cexresponse As Object
    Dim scriptControl As Object
    Dim api_key, api_signature, api_secret, api_body As String
    Dim api_nonce As Double
    
    api_key = "myprivatkey" 'replace for real key
    api_secret = "mysecret" 'replace for real secret
    api_nonce = DateDiff("s", "01/01/1970 00:00:00", Now())
    api_signature = HMAC_encode(api_nonce & api_key, api_secret)
    api_body = "{""key"": " & api_key & ",""signature"":" & api_signature & ",""nonce"":" & api_nonce & "}"
    
    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"


    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "https://cex.io/api/balance/", False
        .Send (api_body)
        Set cexresponse = scriptControl.Eval("(" + .ResponseText + ")")
        .abort
    End With


End Sub

 

[CEX_IO]

Hi,

I am building a program in Excel/VBA to execute operations in the trading platform CEX.io. I am using their API and it is working perfectly for any public call. However, when I need to use Private calls, I cannot manage to correctly send the parameters requested for the authorization. Every time I get the following response : "API key is missing.". Could anyone advise what I am doing wrong?

I thought it would be something about the JSON format in the body, but after trying many combinations I really have no idea what to do. I have succesfully created and activated the API key. The signature is hashed correctly, but it seems that I am not being able to even send any information in the body along with the call.

Thank you very much for the support.

Hans



Here Cex's API calls: https://cex.io/rest-api

Below my code:

---------------------------------------------------------

Sub get_balance()


    Dim cexresponse As Object
    Dim scriptControl As Object
    Dim api_key, api_signature, api_secret, api_body As String
    Dim api_nonce As Double
    
    api_key = "myprivatkey" 'replace for real key
    api_secret = "mysecret" 'replace for real secret
    api_nonce = DateDiff("s", "01/01/1970 00:00:00", Now())
    api_signature = HMAC_encode(api_nonce & api_key, api_secret)
    api_body = "{""key"": " & api_key & ",""signature"":" & api_signature & ",""nonce"":" & api_nonce & "}"
    
    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"


    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "https://cex.io/api/balance/", False
        .Send (api_body)
        Set cexresponse = scriptControl.Eval("(" + .ResponseText + ")")
        .abort
    End With


End Sub

Dear HansSheller,


Thank you for your post.


With regard to your issue, we kindly advise you to create an official request to our Support Team via the following link: https://support.cex.io/hc/en-us/requests/new

Since all such requests should be forwarded via the main communication channel to our Tech department, so they can thoroughly investigate the case and find a solution.
 

Please do not hesitate to contact us 24/7.

 

[John_w]

I actually registered on CEX.IO to look at this issue and after trawling the various pages managed to get the VBA code for the REST API working. I found the answer at https://cex.io/websocket-api (bottom of page) where it links to the Python 3.5 client, specifically https://github.com/cexioltd/CEX.IO-Client-Python3.5/blob/master/cexio/rest_client.py. The Python code shows that the signature is a concatenation of timestamp, user ID and API key. Also important is setting the following header in the POST request:

"Content-Type", "application/json"

Another thing wrong with your code is that the key, signature and nonce parameter values (not just those parameter names) must be in double quotes in the API body string, as shown in the example at https://cex.io/rest-api#account-balance.

Here is the complete code:

Public Sub get_balance2()

    Dim cexResponse As Object
    Dim ScriptControl As Object
    Dim api_key As String, api_signature As String, api_secret As String, api_body As String
    Dim api_nonce As String
    Dim userID As String
    
    userID = "Your User ID" 'replace with real user ID
    api_key = "Your API key" 'replace with real key
    api_secret = "Your API secret" 'replace with real secret
    
    api_nonce = DateDiff("s", "01/01/1970 00:00:00", Now)
    
    'https://cex.io/rest-api
    'signature
    'An HMAC-SHA256 encoded message containing - a nonce, user ID and API key. The HMAC-SHA256 code must be generated using a secret key
    'that was generated with your API key. This code must be converted to its hexadecimal representation (64 uppercase characters).

    api_signature = UCase(Hex_HMACSHA256(api_nonce & userID & api_key, api_secret))
    
    api_body = "{" & JParam("key", api_key) & "," & JParam("signature", api_signature) & "," & JParam("nonce", api_nonce) & "}"
                
    Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
    ScriptControl.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "https://cex.io/api/balance/", False
        .setRequestHeader "Content-Type", "application/json"
        .send (api_body)
        Debug.Print .statusText
        Debug.Print .responseText
        Set cexResponse = ScriptControl.Eval("(" & .responseText & ")")
        .abort
    End With

End Sub


Private Function Hex_HMACSHA256(ByVal sTextToHash As String, ByVal sSharedSecretKey As String) As String
    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA256")
    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sSharedSecretKey)
    enc.key = SharedSecretKey
    Dim bytes() As Byte
    bytes = enc.ComputeHash_2((TextToHash))
    Hex_HMACSHA256 = EncodeHex(bytes)
    Set asc = Nothing
    Set enc = Nothing
End Function


Private Function EncodeHex(ByRef arrData() As Byte) As String
    Dim objXML As Object
    Dim objNode As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    ' byte array to hexadecimal
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.Hex"
    objNode.nodeTypedValue = arrData
    EncodeHex = Replace(objNode.text, vbLf, "")
    Set objNode = Nothing
    Set objXML = Nothing
End Function


Private Function JParam(parameterName As String, parameterValue As String) As String
    JParam = Chr(34) & parameterName & Chr(34) & ": " & Chr(34) & parameterValue & Chr(34)
End Function

 

[HansSheller]

Dear John,

That works so well, thanks a lot :D

Now I see that my JS format was wrong. It is quite tricky one though. And you are also right about the signature, I did not include the user in the hashing.

I executed your code with success, so now I can continue working with my project. Really appreciate your support. Wish you a great day!

I actually registered on CEX.IO to look at this issue and after trawling the various pages managed to get the VBA code for the REST API working. I found the answer at https://cex.io/websocket-api (bottom of page) where it links to the Python 3.5 client, specifically https://github.com/cexioltd/CEX.IO-Client-Python3.5/blob/master/cexio/rest_client.py. The Python code shows that the signature is a concatenation of timestamp, user ID and API key. Also important is setting the following header in the POST request:

"Content-Type", "application/json"

Another thing wrong with your code is that the key, signature and nonce parameter values (not just those parameter names) must be in double quotes in the API body string, as shown in the example at https://cex.io/rest-api#account-balance.

Here is the complete code:

Public Sub get_balance2()

    Dim cexResponse As Object
    Dim ScriptControl As Object
    Dim api_key As String, api_signature As String, api_secret As String, api_body As String
    Dim api_nonce As String
    Dim userID As String
    
    userID = "Your User ID" 'replace with real user ID
    api_key = "Your API key" 'replace with real key
    api_secret = "Your API secret" 'replace with real secret
    
    api_nonce = DateDiff("s", "01/01/1970 00:00:00", Now)
    
    'https://cex.io/rest-api
    'signature
    'An HMAC-SHA256 encoded message containing - a nonce, user ID and API key. The HMAC-SHA256 code must be generated using a secret key
    'that was generated with your API key. This code must be converted to its hexadecimal representation (64 uppercase characters).

    api_signature = UCase(Hex_HMACSHA256(api_nonce & userID & api_key, api_secret))
    
    api_body = "{" & JParam("key", api_key) & "," & JParam("signature", api_signature) & "," & JParam("nonce", api_nonce) & "}"
                
    Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
    ScriptControl.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "https://cex.io/api/balance/", False
        .setRequestHeader "Content-Type", "application/json"
        .send (api_body)
        Debug.Print .statusText
        Debug.Print .responseText
        Set cexResponse = ScriptControl.Eval("(" & .responseText & ")")
        .abort
    End With

End Sub


Private Function Hex_HMACSHA256(ByVal sTextToHash As String, ByVal sSharedSecretKey As String) As String
    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA256")
    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sSharedSecretKey)
    enc.key = SharedSecretKey
    Dim bytes() As Byte
    bytes = enc.ComputeHash_2((TextToHash))
    Hex_HMACSHA256 = EncodeHex(bytes)
    Set asc = Nothing
    Set enc = Nothing
End Function


Private Function EncodeHex(ByRef arrData() As Byte) As String
    Dim objXML As Object
    Dim objNode As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    ' byte array to hexadecimal
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.Hex"
    objNode.nodeTypedValue = arrData
    EncodeHex = Replace(objNode.text, vbLf, "")
    Set objNode = Nothing
    Set objXML = Nothing
End Function


Private Function JParam(parameterName As String, parameterValue As String) As String
    JParam = Chr(34) & parameterName & Chr(34) & ": " & Chr(34) & parameterValue & Chr(34)
End Function