Print Page - [SOLVED] Bittrex API 1.1 calls with Excel

Title: [SOLVED] Bittrex API 1.1 calls with Excel
Post by: JMan7777 on May 28, 2017, 02:29:06 AM

Hi,

I'm trying to get the Bittrex API 1.1 working in Excel but for whatever reason I don't get it right ::).

There seems to be something wrong in they way I calculate the signature.

I'm getting the follwing error:

Code:

{"success":false,"message":"INVALID_SIGNATURE","result":null}

This is my current Macro code:

Code:

Public Sub getopenorders()

Dim apikey As String
Dim apisecret As String
Dim uri As String
Dim sign As String
Dim response As String
Dim params As String

'For Debugging
Range("B3").Value = ""
Range("B4").Value = ""
Range("B5").Value = ""

apikey = "BITTREX API KEY"
apisecret = "BITTREX API SECRET"
uri = "https://bittrex.com/api/v1.1/market/getopenorders"
params = "?" + "apikey=" + apikey + "&nonce=" + getNonce
sign = createSignature(apisecret, uri + params)
response = getResponse(uri, "apisign", sign, params)

Range("B3").Value = response


End Sub

Function getResponse(ByVal pURL As String, sendVarKey As String, sendVarValue As String, params As String) As String
    Dim oRequest As WinHttp.WinHttpRequest
    Set oRequest = GetHttpObj("POST", pURL + params, False, sendVarKey, sendVarValue)
    oRequest.send ""
    getResponse = oRequest.responseText
End Function


Public Function GetHttpObj(httpMethod As String, uri As String, async As Boolean, _
    sendVarKey As String, sendVarValue As String, _
    Optional contentType As String = "application/json") As WinHttp.WinHttpRequest
    Dim httpObj As New WinHttp.WinHttpRequest
    With httpObj
        .Open httpMethod, uri, async
        .setRequestHeader "origin", "pamsXL"
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
        .setRequestHeader "Connection", "keep-alive"
        .setRequestHeader "Content-type", contentType
        .setRequestHeader "cache-control", "no-cache"
    End With

    Range("B4").Value = uri
    Range("B5").Value = sendVarValue
    httpObj.setRequestHeader sendVarKey, sendVarValue
    

    Set GetHttpObj = httpObj
End Function


Private Function createSignature(keyString As String, url As String) As String
    createSignature = sha512(keyString, url)
End Function


Private Function sha512(ByVal keyString As String, ByVal str As String) As String

    Dim encode As Object, encrypt As Object, s As String, _
        t() As Byte, b() As Byte, privateKeyBytes() As Byte
        
    Set encode = CreateObject("System.Text.UTF8Encoding")
    Set encrypt = CreateObject("System.Security.Cryptography.HMACSHA512")
    s = keyString
    privateKeyBytes = decodeBase64(s)

    encrypt.Key = privateKeyBytes
    t = encode.Getbytes_4(str)
    b = encrypt.ComputeHash_2((t))
    
    s = tob64(b)
    sha512 = Replace(s, vbLf, "")
    Set encode = Nothing
    Set encrypt = Nothing

End Function

Private Function tob64(ByRef arrData() As Byte) As String

    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement

    Set objXML = New MSXML2.DOMDocument60

    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    tob64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing

End Function

Private Function decodeBase64(ByVal strData As String) As Byte()
    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement
    
    Set objXML = New MSXML2.DOMDocument60
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strData
    decodeBase64 = objNode.nodeTypedValue
    
    Set objNode = Nothing
    Set objXML = Nothing
End Function


Function getNonce() As String
    getNonce = CStr(DateDiff("S", "1/1/1970", Now()))
End Function

Thanks a lot for your help.

Title: Re: [Need help] Bittrex API 1.1 calls with Excel
Post by: rijnsent on June 14, 2017, 01:44:02 PM

Hi JMAN,
I don't know whether you succeeded, but I have a working sample for Kraken, BTC-e and Poloniex in this project: https://github.com/krijnsent/crypto_vba
Getting a Bittrex account and getting that in that project too is on my ToDo list.
Cheers,
Koen

Title: Re: [Need help] Bittrex API 1.1 calls with Excel
Post by: Pudrim on October 08, 2017, 07:57:08 PM

Quote from: rijnsent on June 14, 2017, 01:44:02 PM

Hi, I'm not compile your Code crypto_vba. Could you help me please?

Title: Re: [Need help] Bittrex API 1.1 calls with Excel
Post by: JMan7777 on October 25, 2017, 02:44:32 PM

Hi,

I got it working long time ago but did not yet much use it ;).

What I finally used was the JSON Converter for VBA module from (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON (https://github.com/VBA-tools/VBA-JSON)
to easy get the JSON data pumped into Excel cells.

My initial problem was with the correct request signing and building the correct http request.
After solving that it was easy.

After importing the JSON Converter for VBA module this is my macro working demo code for Excel itself.
It's raw code without nice formating, etc. but it should help you.

Good luck.

FYI: You need to ensure that in the Excel VBA Editor the following Tool-References are enabled:
- Microsoft Scripting Runtime
- Microsoft WinHTTP Services, version 5.1

Code:

Public Sub getopenorders()

Dim apikey As String
Dim apisecret As String
Dim uri As String
Dim sign As String
Dim response As String
Dim params As String
Dim json As Object
Dim tempString As String
Dim Item As Dictionary
Dim key As Variant
Dim c As Integer
Dim r As Integer
Dim rv As Integer

apikey = "THIS IS YOUR BITTREX API KEY"
apisecret = "THIS IS YOUR BITTREX API SECRET"

uri = "https://bittrex.com/api/v1.1/public/getmarketsummaries"

params = "?" + "apikey=" + apikey + "&nonce=" + getNonce
sign = createSignature(apisecret, uri + params)
response = getResponse(uri, "apisign", sign, params)
tempString = Mid(response, InStr(response, "["), Len(response) - InStr(response, "["))
tempString = Replace(tempString, ":null", ":""null""")

Set json = JsonConverter.ParseJson(tempString)

r = 2
rv = 3
c = 1

Sheets(1).Cells.ClearContents

For Each Item In json
     
    For Each key In Item.keys()
        Sheets(1).Cells(r, c).value = key
        Sheets(1).Cells(rv, c).value = Item(key)
        c = c + 1
    Next
    rv = rv + 1
    c = 1
Next

MsgBox ("Update from Bittrex done.")

End Sub


Function getResponse(ByVal pURL As String, sendVarKey As String, sendVarValue As String, params As String) As String
    Dim oRequest As WinHttp.WinHttpRequest
    Set oRequest = GetHttpObj("POST", pURL + params, False, sendVarKey, sendVarValue)
    oRequest.send ""
    getResponse = oRequest.responseText
End Function


Public Function GetHttpObj(httpMethod As String, uri As String, async As Boolean, _
    sendVarKey As String, sendVarValue As String, _
    Optional contentType As String = "application/json") As WinHttp.WinHttpRequest
    Dim httpObj As New WinHttp.WinHttpRequest
    With httpObj
        .Open httpMethod, uri, async
        .setRequestHeader "origin", "pamsXL"
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
        .setRequestHeader "Connection", "keep-alive"
        .setRequestHeader "Content-type", contentType
        .setRequestHeader "cache-control", "no-cache"
    End With
    httpObj.setRequestHeader sendVarKey, sendVarValue
    Set GetHttpObj = httpObj
End Function


Private Function createSignature(keyString As String, url As String) As String
    createSignature = sha512(keyString, url)
End Function


Private Function sha512(ByVal keyString As String, ByVal str As String) As String

    Dim encode As Object, encrypt As Object, s As String, _
        privateStringBytes() As Byte, b() As Byte, privateKeyBytes() As Byte
        
    Set encode = CreateObject("System.Text.UTF8Encoding")
    Set encrypt = CreateObject("System.Security.Cryptography.HMACSHA512")
    
    privateKeyBytes = encode.Getbytes_4(keyString)
    privateStringBytes = encode.Getbytes_4(str)
    encrypt.key = privateKeyBytes
    
    b = encrypt.ComputeHash_2((privateStringBytes))
    sha512 = ByteArrayToHex(b)

    Set encode = Nothing
    Set encrypt = Nothing

End Function

Private Function ByteArrayToHex(ByRef ByteArray() As Byte) As String
    Dim l As Long, strRet, Val As String
    For l = LBound(ByteArray) To UBound(ByteArray)
        Val = Hex$(ByteArray(l))
        If Len(Val) <> 2 Then
             Val = "0" & Val
        End If
        strRet = strRet & Val
    Next l
    ByteArrayToHex = LCase(strRet)
End Function

Function getNonce() As String
    getNonce = CStr(DateDiff("S", "1/1/1970", Now()))
End Function

Title: Re: [SOLVED] Bittrex API 1.1 calls with Excel
Post by: Coinyguys on November 17, 2017, 05:48:37 PM

Hey Jman777, its wonderfull codes.
I used them for getting other request also.
But during placing Buy / Sell / cancel orders - its getting error. perticulerly after getting response from server.
in the below lines,

tempString = Mid(response, InStr(response, "["), Len(response) - InStr(response, "["))
tempString = Replace(tempString, ":null", ":""null""")

could you help me out please.

Title: Re: [SOLVED] Bittrex API 1.1 calls with Excel
Post by: JMan7777 on November 24, 2017, 02:39:23 PM

Hey Coinyguys ,

After:

Code:

response = getResponse(uri, "apisign", sign, params)

check first what's in the response variable.

The following 2 lines are only added to correct/modify the reponse to be able to be parsed with the JsonConverter.

Code:

tempString = Mid(response, InStr(response, "["), Len(response) - InStr(response, "["))
tempString = Replace(tempString, ":null", ":""null""")

I never tried to place Buy / Sell / Cancel orders.

As such I don't know how the response of such requests looks like.

Best is you just temporary write the context of the variables "response" and "tempString" in 2 seperate Excel cells
and then you see what I do to modify the Bittrex response for the JsonConverter.

Then you can compare the Bittrex response format for normal get* with your Buy / Sell / Cancel orders.

Hope this helps.

Good Luck.

Bitcoin Forum

Economy => Service Discussion => Topic started by: JMan7777 on May 28, 2017, 02:29:06 AM