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