Please add ByBit Exchange
Closed this issue · 15 comments
Would you consider adding ByBit Exchange support to crypto_vba?
If you aren't aware, it's a great futures exchange that is gaining more and more popularity.
I've tried adapting what you already coded for other exchanges to work with ByBit's API, but have run into a wall on a couple of fronts.
While attempting the following code path...
PrTxt = PublicByBitv2("tickers", "GET") > Function PublicByBitv2 > PublicByBitv2 = WebRequestURL(Url, ReqType) > Function WebRequestURL > objHTTP.send
...I invariably get the following error:
"The data necessary to complete this operation is not yet available"
I don't know if it's because they're not responding fast enough or what.
I was hoping, with your VBA experience and already having built a template for so many other sites...you could probably whip this out in just a few hours of effort, whereas my VBA-fu is much weaker and I might spend my life trying to fix this until my wife leaves me.
If you happen to know what this issue is, maybe you can give me a nudge over this hump with some advice. Otherwise, if you have some time you'd be willing to work on this to add full blown support for ByBit (similar to how you have done for other exchanges)...I wouldn't have a problem sending $100 worth of crypto to an address of your choice, for the effort of putting it all together, and then another $100 once I've successfully tested it with ByBit.
If you're interested, here is their API docs page...
https://github.com/bybit-exchange/bybit-official-api-docs/tree/master/en
Additional Note: I would be interested in you adding ByBit in the same format (tab/module) you normally do with other exchanges...but, to save myself headaches of figuring out things with my more limited VBA experience, I would like an example provided in the "Test" sub for all of the items covered in the following page:
https://github.com/bybit-exchange/bybit-official-api-docs/blob/master/en/rest_api.md
Things like Query historical Kline, Latest Information for Symbol, Place active order, and so forth.
It can be in the same format you usually make them, just with all of the examples instead of just a sampling.
Thank you and please let me know if you're interested in helping.
Hi @ITJusus
will have a look at it one of these days, am working on some other exchanges (on request), so will definitely consider yours.
Cheers,
Koen
Appreciate it. I'll let you know if I make progress as well. Thank you.
Hi @ITJusus , could you check the code I just added? I think I have the public API of Bybit covered and created the first two working examples of the private API. Looking forward to your feedback.
Will do. Thank you for working on this.
When I ran my sub to get the symbols data, from ByBit, I got the following error:
"Invalid procedure call or argument"
When I used the Immediate window to Call TestBybit, I got the following error:
"Run-time error '5': Invalid procedure call or argument"
When I Debugged, it showed the following line as the culprit (in bold):
'WinHttpRequestOption_SecureProtocols - 512 = TLS 1.1, 2048 for TLS 1.2
objHTTP.Option(9) = 2048
I modified Windows 7 to utilize TLS 1.1 and TLS 1.2 by default and then commented out this line above. That seemed to get me to the next step, but then I got a "Run-time error '424': Object required" error at the following line (in bold):
'GET with parameter for orderBook
...
Set JsonResult = JsonConverter.ParseJson(TestResult)
Test.IsOk JsonResult("result").Count > 0, "orderbook 2 failed, result: ${1}"
Test.IsEqual JsonResult("result")(1)("symbol"), "BTCUSD", "orderbook 3 failed, result: ${1}"
Test.Includes Array("Buy", "Sell"), JsonResult("result")(1)("side"), "orderbook 4 failed, result: ${1}"
I got similar error in my code for getting symbols data with my sub.
I then commented out all lines related to this section, as suggested in your code below, and UNCOMMENTED out the last line (in bold below)...
'DEFAULT: WinHttp.WinHttpRequest.5.1
'x'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
'HTTP options, can be outcommented if needed
'WinHttpRequestOption_SslErrorIgnoreFlags - 13056: ignore all err, 0: accept no err
'x'objHTTP.Option(4) = 13056
'WinHttpRequestOption_SecureProtocols - 512 = TLS 1.1, 2048 for TLS 1.2
'x'objHTTP.Option(9) = 2048
'BACKUP (OUTCOMMENT THE 3 LINES ABOVE)
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Then, I was able to get results back. Awesome...but it seems to be missing the top level of results from the JSON nested arrays returned. I suspect this has something to do with the JsonConverter.ParseJson or the JsonToArray or ArrayTable methods used here.
This is how the returned data appears normally in a browser for such a call...
I'm able get data returned for the lower level arrays, like "leverage_filter":{..." but not for the top level items like "maker_fee".
Here is the code I'm using to view the results returned, piece by piece:
Sub ByBitSymbolData_MultiCall()
Dim PrTxt As String
Dim json As Object
Dim TempArr As Variant
Dim TempArrStrings As Variant
Dim QuantityIncrementValue As String
Dim TickSizeValue As String
Dim TakeLiquidityRateValue As String
Dim ProvideLiquidityValue As String
Dim rowString As String
Dim iSubA As Long
Dim jSubA As Long
Application.VolatileDim Params2 As New Dictionary
PrTxt = PublicBybit("symbols", "GET")
Set json = JsonConverter.ParseJson(PrTxt)If InStr(PrTxt, "error") > 0 Then
'Error
ReDim TempArr(1 To 1, 1 To 1)
TempArr(1, 1) = "ERROR (" & json("error_nr") & "): " & json("error_txt")
Blad5.Cells(3, "K").Value = TempArr
Else
ResArr = JsonToArray(json)
ResTbl = ArrayTable(ResArr, True)rowString = ""
For iSubA = 1 To UBound(ResTbl, 1)
rowString = ResTbl(iSubA, 1)
For jSubA = 2 To UBound(ResTbl, 2)
rowString = rowString & "," & ResTbl(iSubA, jSubA)
Next jSubAIf InStrB(1, rowString, "qty_step", vbBinaryCompare) <> 0 Then QuantityIncrementValue = Split(rowString, ",")(1) MsgBox ("Qty Increment: " & QuantityIncrementValue) End If If InStrB(1, rowString, "tick_size", vbBinaryCompare) <> 0 Then TickSizeValue = Split(rowString, ",")(1) MsgBox ("Tick Size: " & TickSizeValue) End If If InStrB(1, rowString, "taker_fee", vbBinaryCompare) <> 0 Then TakeLiquidityRateValue = Split(rowString, ",")(1) MsgBox ("Taker Fee: " & TakeLiquidityRateValue) End If If InStrB(1, rowString, "maker_fee", vbBinaryCompare) <> 0 Then ProvideLiquidityValue = Split(rowString, ",")(1) MsgBox ("Maker Fee: " & ProvideLiquidityValue) End If MsgBox (rowString)
Next iSubA
End If
Set json = Nothing
Set Params2 = Nothing
PrTxt = ""QuantityIncrementValue = ""
TickSizeValue = ""
TakeLiquidityRateValue = ""
ProvideLiquidityValue = ""End Sub
Any ideas what may be wrong at this point?
Hi @ITJusus
Try something like this (first add a sheet named SHT, copy-paste this before the line:
rowString = ""
It should give you a good idea of happens. Basically: the ArrayTable function assumes that the "deepest" level in your data is the one that should form the table rows, but in this specific JSON it's the level before. I'll see if I can add a variable to my ArrayTable function to return what ResTblB below returns.
Worksheets("SHT").Range("B2").Resize(UBound(ResArr, 2), UBound(ResArr, 1)) = WorksheetFunction.Transpose(ResArr)
Worksheets("SHT").Range("J2").Resize(UBound(ResTbl, 2), UBound(ResTbl, 1)) = WorksheetFunction.Transpose(ResTbl)
NrResults = 1
j = 0
Dim ResTblB() As Variant
For i = 2 To UBound(ResArr, 2)
If ResArr(1, i) = 2 Then
'New line
j = 1
NrResults = NrResults + 1
ReDim Preserve ResTblB(1 To 15, 1 To NrResults)
ElseIf ResArr(1, i) > 2 Then
If ResArr(5, i) = "VAL" Then
'Header
If ResArr(1, i) = 3 Then
ResTblB(j, 1) = ResArr(3, i)
ElseIf ResArr(1, i) = 4 Then
ResTblB(j, 1) = ResArr(2, i) & "/" & ResArr(3, i)
End If
'Value
ResTblB(j, NrResults) = ResArr(4, i)
j = j + 1
End If
End If
Next i
Worksheets("SHT").Range("J20").Resize(UBound(ResTblB, 2), UBound(ResTblB, 1)) = WorksheetFunction.Transpose(ResTblB)
That helped out immensely. I was able to adapt this to get symbol and tickers data back successfully. Next, I'll be working on the Candle ("Klines") sticks.
One thing I did notice is the new ModWeb content caused some of my HitBTC related candle calls to start acting oddly, so I just created a separate ModWebByBit module and added _ByBit to the WebRequestURL references separately for ByBit calls related to that new module.
At any rate, it works for both ByBit and HitBTC now. Once I'm able to get ByBit candle data, I'll move on to testing the public calls with API keys, etc. Thanks and will let you know how it goes.
I'm stuck. Probably something simple, but I'm not skilled enough in VBA or with these exchanges to figure it out. For the "from" required parameter...I don't have a clear understanding of what they are even asking for here.
The only values that seem to work at all are integers like 1 or converting Now to an integer, but the results are the same...it is providing results from way back in 11/14/18. If I increase the "limit" from 2 to 199...the values returned at least eventually reach 11/20/18, but that's it.
It seems they are returning the data backward (oldest first rather than newest first).
Here is the section of code that applies to this call:
Dim Params2 As New Dictionary
Params2.Add "symbol", "BTCUSD"
Params2.Add "interval", 60 'TimeFrame
Params2.Add "from", Int(CDbl(Now())) 'CDbl(Now()) '1 '1539778407.21086
Params2.Add "limit", 2
PrTxt = PublicBybit("kline/list", "GET", Params2)
Set json = JsonConverter.ParseJson(PrTxt)
Here is the offical ByBit Github page I'm following for this ("kline" section):
https://bybit-exchange.github.io/bybit-official-api-docs/en/index.html#operation/query_symbol
I was hoping you might be able to pull me out of this pit of programming despair.
Hi @ITJusus
for this kind of questions: please do send me an email (see my profile, at the bottom of my linked website) so we can connect on Telegram or WhatsApp, that makes life much easier and/or prevent frustration. For now: the main issue is the "from" parameter. The kline call takes a unixtime of 10 digits (unixtime: nr of seconds from 1-1-1970). So if you send "now()", that should always give an empty result. In your example you get the hourly klines data, so you'd need to deduct 2 hours from that: 2 hours * 60 minutes * 60 seconds = 7200 seconds.
I'll add this to my code example.
Dim Params1a As New Dictionary
Dim LimitTime As Double
Dim ResTime As Long
Params1a.Add "symbol", "BTCUSD"
Params1a.Add "interval", 60 'TimeFrame in minutes
Params1a.Add "limit", 2
LimitTime = (GetBybitTime() / 1000) - 60 * 60 * 2
'GetByBitTime returns time in ms (microseconds, 13 digits), and this function takes seconds (10 digits)
'In order to get the past 2 hours, deduct that time in seconds: interval*limit*60
Params1a.Add "from", LimitTime
TestResult = PublicBybit("kline/list", "GET", Params1a)
Set JsonResult = JsonConverter.ParseJson(TestResult)
Debug.Print JsonResult("result").Count
Debug.Print JsonResult("result")(1)("symbol")
Debug.Print Val(JsonResult("result")(1)("high"))
ResTime = JsonResult("result")(1)("open_time")
Debug.Print ResTime, UnixTimeToDate(ResTime)
ResTime = JsonResult("result")(2)("open_time")
Debug.Print ResTime, UnixTimeToDate(ResTime)
Thanks for that. Oddly though, the LimitTime does correctly produce a value such as "1565256.622", but as soon as I use it as the "from"...I just get an error. Can't yet see the content of the error. Will have to work on that later today.
But, if I force the value back to an integer, like 1 the error does not occur and I'm back at the 2018 records. So it seems it doesn't like something about the format of the unixtime.
Sorry to bring this github thread down into the weeds. I'm on lunch, but will switch to email (as you recommended) with future technical conversations like this.
I went ahead and sent you an email. Much appreciated.
@ITJusus Does the current code work for you? (Checking if all issues are still relevant.)
@krijnsent Thank you for checking in. I've only recently (in the last week or so) started back testing the code again. Currently I'm only making GET requests for price data and that is working perfectly. Once I'm ready to test live (with a few dollars in play money), I'll be able to let you know how the live POST requests go. They seemed to go well in isolated tests I performed last year, but we'll see how it all harmonizes together.
By the way, maybe I missed it somewhere on here...but, how does one go about tipping you for all the work you put into helping us with these projects? I know you have BAT tipping enabled, but is that you're preferred method/address/currency?
Thanks again.