I have an issue when I try to retrieve some data in excel through VBA code. I used the following as basis: https://github.com/BitMEX/api-connectors/tree/master/official-http/vba and it works, I was able to update it according to my need and place an order (testnet)
I now try to retrieve the book but I always get "Signature not valid" as response. Can you please help understand what's I am doing wrong?
The data I want to receive is the following: https://testnet.bitmex.com/api/explorer/#!/OrderBook/OrderBook_getL2
As Hash function, I use the HexHash function available in the link provided above (and it works for the 'Post' instruction but can't make it works for the "GET" instruction.
Thanks in advance
Bellow a working code (POST function):
Sub placeorder()
Dim Json, httpObject As Object
Dim nonce As Double
Dim verb, apiKey, apiSecret, signature, symbol, price, qty, url, postdata, replytext, nonceStr As String
' Set monotonically (w time) increasing nonce
nonce = DateDiff("s", "1/1/1970", Now)
' Set api key and secret
apiKey = "aaa"
apiSecret = "bbb"
' Build query
symbol = "XBTUSD"
price = 8000
qty = 1
verb = "POST"
url = "/api/v1/order"
postdata = "symbol=" & symbol & "&price=" & price & "&quantity=" & qty
' Stringize nonce
nonceStr = nonce
' Compute signature using hexhash script
signature = HexHash.HexHash(verb + url + nonceStr + postdata, apiSecret, "SHA256")
' Set up HTTP req with headers
Set httpObject = CreateObject("MSXML2.XMLHTTP")
httpObject.Open "POST", "https://testnet.bitmex.com" & url, False
httpObject.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpObject.setRequestHeader "api-nonce", nonceStr
httpObject.setRequestHeader "api-key", apiKey
httpObject.setRequestHeader "api-signature", signature
httpObject.Send (postdata)
' Catch response
replytext = httpObject.ResponseText
end sub()
Bellow a NON-working code (GET function):
Sub getorderbook2()
Dim Json, httpObject As Object
Dim nonce As Double
Dim verb, apiKey, apiSecret, signature, symbol, url, getdata, replytext,
depth As String
Dim nonceStr As String
' Set monotonically (w time) increasing nonce
nonce = DateDiff("s", "1/1/1970", Now)
' Set api key and secret
apiKey = "aaa"
apiSecret = "bbb"
' Build query
symbol = "XBTUSD"
depth = 3
verb = "GET"
url = "/api/v1/orderBook/L2"
getdata = "symbol=" & symbol & "&depth=" & depth
' Stringize nonce
nonceStr = nonce
' Compute signature using hexhash script
signature = HexHash.HexHash(verb + url + nonceStr + getdata, apiSecret, "SHA256")
' Set up HTTP req with headers
Set httpObject = CreateObject("MSXML2.XMLHTTP")
httpObject.Open "GET", "https://testnet.bitmex.com" & url, False
httpObject.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpObject.setRequestHeader "api-nonce", nonceStr
httpObject.setRequestHeader "api-key", apiKey
httpObject.setRequestHeader "api-signature", signature
httpObject.Send (getdata)
' Catch response
replytext = httpObject.ResponseText
end sub ()
In the second section, I always get an error message in return "Signature not valid"
Switching between GET and POST requires more than just changing the verb in the request. A GET request needs to have the data as part of the URL string so try:
url = url & "?" & getdata
getdata = ""
httpObject.Open "GET", "https://testnet.bitmex.com" & url, False
You also need to change this line from:
httpObject.Send (getdata)
to:
httpObject.Send
The way in which you construct the api-signature value is also different for GET requests to this API - see here for details. The changes I have suggested should lead to the correct signature being generated. If you need to URL encode data in VBA then this answer might help.
Other issues:
Dim a, b As String is equivalent to Dim a As Variant, b As String. To declare multiple String variables you need to write Dim a As String, b As StringCreateObject("MSXML2.XMLHTTP") accesses the older version 3.0 of MSXML2. To access the most recent version 6.0, you need CreateObject("MSXML2.XMLHTTP.6.0")