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?

  • 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
0

Somebody can help me I just want to open an order but everytime I get this Error {"code":-1022,"msg":"Signature for this request is not valid."}

Sub Getorder()

  Const apiKey As String = "[insert API key]"
    Const secret As String = "[insert API secret]"

    Dim totalParams As String
    totalParams = "symbol=LTCBTC&side=Sell&type=Market&quantity=10&timeInForce=GTC&recvWindow=500000000000&timestamp=" & BinanceTime()
    Signature = Base64_HMACSHA256(totalParams, secret)

   sUrl = "https://api.binance.com/api/v3/order?" & totalParams & "&signature=" & Signature


    Dim Request As WinHttp.WinHttpRequest
    Dim sResult As String
    On Error GoTo Err_DoSomeJob

    Set Request = New WinHttp.WinHttpRequest
    With Request
        .Open "POST", sUrl, False
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .SetRequestHeader "X-MBX-APIKEY", apikey

        .Send ""
        .WaitForResponse
        sResult = .ResponseText
    End With

    Debug.Print (sResult)
    Debug.Print (sUrl)
    'Set JsonResponse = JsonConverter.ParseJson(sResult)


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
    Range("A1") = sResult
    Range("A2").Calculate
    BinanceTime = Range("A2")
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
  • This is not an answer, but a question in itself. Please ask a question via the the 'Ask question' button in the top right. To answer your question you need to look at what parameters should go into the totalParams variable, for you request it's expecting alot more than just the timestamp. – Sjors Hijgenaar Jan 31 '18 at 13:23
  • so sorry... next time I will do that.. Do you have an excamble for me how I can do that?? I wrote the the other things in directly in the URL . I will pay u 100 TRX on your account TRX account If u give me an working excample – Kerbin West Feb 2 '18 at 17:29
  • I have edited your answer, should be a working example now. – Sjors Hijgenaar Feb 5 '18 at 10:43
  • thx so mutch tell me ur BTC, ETH or TRX account number ... I will pay u .. greetings from Austria – Kerbin West Feb 9 '18 at 13:29
  • I think a payment isn't necessary, nor are monetary rewards very ethical on this website. But just in case I'm gonna leave this ETH address here: 0x201b89333Ed6Cb55DeD5b8A12b92033B1E04320d – Sjors Hijgenaar Feb 13 '18 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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