当我尝试通过VBA代码检索excel中的某些数据时遇到问题。我以以下内容为基础:https : //github.com/BitMEX/api-connectors/tree/master/official-http/vba并且它可以正常工作,我能够根据需要进行更新并下订单(测试网)
我现在尝试检索这本书,但始终收到“签名无效”作为回应。您能帮我了解我在做什么错吗?
我要接收的数据如下:https : //testnet.bitmex.com/api/explorer/#!/OrderBook/OrderBook_getL2
作为哈希函数,我使用上面提供的链接中可用的HexHash函数(它适用于“ Post”指令,但不能使其适用于“ GET”指令。
提前致谢
下面是一个工作代码(POST函数):
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()
下面是一个非工作代码(GET函数):
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 ()
在第二部分中,我总是收到错误消息,返回“签名无效”
在GET和POST之间切换不仅需要更改请求中的动词。GET请求需要将数据作为URL字符串的一部分,因此请尝试:
url = url & "?" & getdata
getdata = ""
httpObject.Open "GET", "https://testnet.bitmex.com" & url, False
您还需要从以下位置更改此行:
httpObject.Send (getdata)
至:
httpObject.Send
api-signature
对于此API的GET请求,构造值的方式也有所不同-有关详细信息,请参见此处。我建议的更改应导致生成正确的签名。如果您需要在VBA中对数据进行URL编码,则此答案可能会有所帮助。
其他问题:
Dim a, b As String
等价于Dim a As Variant, b As String
。要声明多个String变量,您需要编写Dim a As String, b As String
CreateObject("MSXML2.XMLHTTP")
访问旧版本的MSXML2 3.0。要访问最新版本6.0,您需要CreateObject("MSXML2.XMLHTTP.6.0")
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句