0

I am trying to load my crypto portfolio into Excel so I can use its easy analysis functionality on my trades. However, I'm having trouble connecting to the API using VBA's WinHttpRequest.

I use the following code to try to GET account info (balances) /api/v3/account:

Sub GetBalances()
Dim tNow As String
tNow = CStr(Date2Long(Now()))
Dim sUrl As String
sUrl = "https://api.binance.com/api/v3/account?timestamp=" & tNow & "&signature=[signature here]"

Dim oRequest As WinHttp.WinHttpRequest
Dim sResult As String

On Error GoTo Err_DoSomeJob

Set oRequest = New WinHttp.WinHttpRequest
With oRequest
    .Open "GET", sUrl, True
    .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
    .SetRequestHeader "X-MBX-APIKEY", "[API key here]"
    .Send "{}"
    .WaitForResponse
    sResult = .ResponseText
    Debug.Print sResult
    sResult = oRequest.Status
    Debug.Print sResult
End With


Exit_DoSomeJob:
    On Error Resume Next
    Set oRequest = Nothing
    Exit Sub

Err_DoSomeJob:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_DoSomeJob


End Sub

Private Function Date2Long(dtmDate As Date) As Long
    Date2Long = (dtmDate - #1/1/1970#) * 86400
    Exit Function
End Function

However it gives the following error message:

{"code":-1100,"msg":"Illegal characters found in parameter 'signature'; legal range is '^[A-Fa-f0-9]{64}$'."}

Which seems strange as the secret key's character range is only A-F, while the secret provided seems to contain A-Z. Anyone else know how to solve this problem? Might it be a problem caused by VBA? Or might the problem lie somewhere else?

3
  • Are you signing the signature? for example in PHP: $signature = hash_hmac('sha256', $query, $this->api_secret); – Marc Alexander Jan 3 '18 at 22:22
  • No I don't, so that will probably be the problem. What is $query in your example? – Sjors Hijgenaar Jan 4 '18 at 9:00
  • @MarcAlexander I presume that $query is your request string, do you have an example in code that you could share? I'm struggling what to include... – Sjors Hijgenaar Jan 5 '18 at 9:25
0

As per @Marc Alexander's comment I have to sign (HMAC SHA256) the query parameters (in my case timestamp=##################) and add that as signature parameter in the request. It works using the following code:

Sub GetBalances()
    Const apiKey As String = "[insert API key]"
    Const secret As String = "[insert API secret]"

    Dim totalParams As String
    totalParams = "timestamp=" & BinanceTime()
    Signature = Base64_HMACSHA256(totalParams, secret)
    sUrl = "https://api.binance.com/api/v3/account?" & totalParams & "&signature=" & Signature
    Dim oRequest As WinHttp.WinHttpRequest
    Dim sResult As String

    On Error GoTo Err_DoSomeJob

    Set oRequest = New WinHttp.WinHttpRequest
    With oRequest
        .Open "GET", sUrl, True
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .SetRequestHeader "X-MBX-APIKEY", apiKey
        .Send "{}"
        .WaitForResponse
        sResult = .ResponseText
    End With
    Set JsonResponse = JsonConverter.ParseJson(sResult)
    Debug.Print(

Exit_DoSomeJob:
    On Error Resume Next
    Set oRequest = Nothing
    Exit Sub

Err_DoSomeJob:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_DoSomeJob


End Sub

Public Function BinanceTime() As String
    Dim oRequest As WinHttp.WinHttpRequest
    Dim sResult As String

    Set oRequest = New WinHttp.WinHttpRequest
    With oRequest
        .Open "GET", "https://api.binance.com/api/v1/time", True
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .Send "{}"
        .WaitForResponse
        sResult = CStr(.ResponseText)
    End With
    Set Json = JsonConverter.ParseJson(sResult)
    BinanceTime = Json("serverTime")
End Function

Public Function Base64_HMACSHA256(ByVal sTextToHash As String, ByVal sSharedSecretKey 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)
    Base64_HMACSHA256 = ByteArrayToHex(Bytes)
    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function ByteArrayToHex(ByRef ByteArray() As Byte) As String
    Dim l As Long, strRet As String

    For l = LBound(ByteArray) To UBound(ByteArray)
        'strRet = strRet & Hex$(ByteArray(l))
        strRet = strRet & WorksheetFunction.Dec2Hex(ByteArray(l), 2)
        Debug.Assert l = Len(strRet) / 2 - 1
    Next l
    ByteArrayToHex = strRet
End Function

Not the answer you're looking for? Browse other questions tagged or ask your own question.