目前开发数字货币量化策略主流采用的语言主要有Python，javascrip，C#，C等，这些语言对小白来说门槛较高，均需要安装运行环境，很多小白连安装都无法搞定，笔者因为工作需要，对excel VBA有深入研究，发现VBA不仅对提升工作效率有极大的帮助，还可以开发量化策略，VBA依托于excel（99%的办公电脑都有），完全不需要安装其他软件，相比于其他语言具有无可比拟的优势，当然VBA运行效率是比较低的，然而只要不是太高频的策略，VBA的速度足够用了，即使主流语言开发高频策略也一样受限于交易所服务器的限制。因此笔者认为，VBA是上班族量化研究的一款入门级语言，即使没搞定量化，对工作也是有帮助的。
下面以BitMEX为例，讲解一下如何连接BitMEX帐号及获取账户余额，虽然是VBA，其他语言也应该是类似的:
1、签名
首先需要签名，签名信息一般通过包括以下信息进行加密运算后得到的字符串：
请求方式、URL地址（相对地址）、时间戳、请求数据、apisecret
这里的签名函数各个交易所要求有所不同，BitMEX的VBA签名函数可以参考一下代码：
Function HexHash(ByVal clearText As String, ByVal key As String, Meth As String) As String
Dim hashedBytes
Dim i As Integer
hashedBytes = computeHash(clearText, key, Meth)
HexHash = ""
For i = 1 To LenB(hashedBytes)
' Incredibly important that the LCase() function calls this way, otherwise code won't always work
HexHash = HexHash & LCase(Right("0" & hex(AscB(MidB(hashedBytes, i, 1))), 2))
Next
End Function
Function computeHash(ByVal clearText As String, ByVal key As String, Meth As String) As Byte()
Dim BKey() As Byte
Dim BTxt() As Byte
BTxt = StrConv(clearText, vbFromUnicode)
BKey = StrConv(key, vbFromUnicode)
If Meth = "SHA512" Then
Set SHAhasher = CreateObject("System.Security.Cryptography.HMACSHA512")
ElseIf Meth = "SHA256" Then
Set SHAhasher = CreateObject("System.Security.Cryptography.HMACSHA256")
Else
Set SHAhasher = CreateObject("System.Security.Cryptography.HMACSHA1")
End If
If key <> "" Then
SHAhasher.key = BKey
Else
End If
computeHash = SHAhasher.ComputeHash_2(BTxt)
Set SHAhaser = Nothing
End Function2、http请求
连接数字货币服务器帐号，都是通过http请求来实现的，请求信息一般包括：
请求方式，头部信息，请求数据
Sub Bitmexaccount()
With CreateObject("msscriptcontrol.scriptcontrol")
.Language = "JavaScript"
nonceStr = CStr(.eval("new Date().getTime();"))
End With
apiKey = ActiveSheet.Range("B15")
apiSecret = ActiveSheet.Range("B16")
Verb = "GET"
URL = "/api/v1/user/margin?currency=XBt" & "&" & nonceStr
Data = ""
Signature = HexHash(Verb + URL + nonceStr + Data, apiSecret, "SHA256")
Set httpObject = CreateObject("msxml2.xmlhttp")
httpObject.Open "GET", "https://www.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
res = httpObject.responseText
ActiveSheet.Range("B18") = getjson(res, "marginBalance") / 10 ^ 8
End Sub
3、json数据处理
一般通过第二步获取到的数据为json格式的字符串，这里需要一个函数快速获取到我们需要的字段值，下面是笔者写的一个简单的json函数：
Function getjson(jsdata, zhi)
On Error Resume Next
jsdata = Replace(jsdata, "}", "")
jsdata = Replace(jsdata, "]", "")
raw = Split(jsdata, """")
hh = WorksheetFunction.Match(zhi, raw, 0)
If raw(hh) = ":" Then
If hh = "" Then
getjson = "kongzhi"
Else
getjson = raw(hh + 1)
End If
Else
If hh = "" Then
getjson = "kongzhi"
Else
getjson = Replace(raw(hh), ":", "")
getjson = Replace(getjson, ",", "")
End If
End If
End Function
比如根据第二步中返回值，我们需要获取保证金余额;
则调用
getjson(res, "marginBalance")下面是Okex和BitMEX获取账户余额的源代码
以上就是通过BitMEX API 获取账户保证金余额或钱包余额的方法
BitMEX & Okex VBA connector.zip