"Signature not valid" when getting API data (VBA-Excel)

Question

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"

I'm not quite sure about the code to be honest... is difficult to test without having all the facts. However, putting this codes side by side, `"application/application/x-www-form-urlencoded"` doesn't seem right in the second one... should have been `"application/x-www-form-urlencoded"` ? — FAB, Jun 01 '19 at 09:45
Thanks, it is a copy past isssue when creating the message on my side. The original code was correct: "application/x-www-form-urlencoded" and it still have the "Signature no valid" with this. When you say "without having all the facts"', can you let me know what additional information you need? I can load the excel file directly if necessary :) — Bounty25, Jun 01 '19 at 10:02
By all facts I mean all the other details you can't post for security reasons, i.e.: key, secret, etc. I didn't mean you haven't added as much as possible already. Plus, debugging API is not exactly my strong suit, just thought to comment on the bit I was able to test easily. — FAB, Jun 01 '19 at 10:45
@Bounty25 So if I understand correctly there's no problem with the code itself. It runs just fine but if you print `replytext` you get "Signature not valid" instead of an HTML response you would normally expect to get? If that's the case, maybe there's a problem in the way/logic with which you compute the `signature`. — Stavros Jon, Jun 01 '19 at 14:05
Yes you’re right, I get « Signature not valid ». And it makes me crazy as I use the same method to comput the signature in the first code and it works so I don’t understand what I am doing wrong in the second one. — Bounty25, Jun 01 '19 at 14:57

score 1 · Accepted Answer · answered Jun 02 '19 at 01:48

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 String
CreateObject("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")

Many thanks for this answer. I’m not able to implement it today but will try tomorrow and let you know. — Bounty25, Jun 02 '19 at 09:34

"Signature not valid" when getting API data (VBA-Excel)

1 Answers1