Bitfinex Private API

[RH4]

Hi Everyone,

Hope you're all well. @balin77 had closed a previous issue about problems with Bitfinex Private API connection. Unfortunately the solution was not provided to the community.

Since Bitfinex is the most liquid market for bitcoin trading, has anyone managed to query it through the Private API key and secret in VBA? Would greatly appreciate you sharing the solution here.

Many thanks.

[balin77]

Hi, actually you solved the problem yourself. After the corrections you made, the API connection worked. I adapted the connection a little bit since then.

Modifications:

-PublicBitfinex can access API v1 and v2 -PrivateBitfinex can return the json object. This is required in case of complicated Json files, because sometimes your function "JsonToArray" doesnt work. -My API Key access is different to yours

And I have added several API functions. Following are tested (except two of them)

-AccountInfosBitfinex (returns array of Account Infos) -GetSymbolsBitfinex (returns array of all available trades) -GetTickerBitfinex (returns collection of Bid, Ask, Last, Mid) -GetCandlesBitfinex (returns array of candles) -GetAllBalancesBitfinex (returns array of all balances) -AccountFeesBitfinex (returns a double? of the fees) -AccountSummaryBitfinex (returns array of the account summary) (dont remember the difference to AccountInfosBitfinex
-DepositAddressesBitfinex (returns array of all deposit addresses -NewOrderBitfinex (creates a new order and returns the answer as array) -ReplaceOrderBitfinex (replaces an order UNTESTED) -OrderStatusBitfinex (returns the order status UNTESTED) -CancelOrderBitfinex (cancel an order and returns the answer as array) -CancelAllOrderBitfinex (cancel all orders and returns the answer as array) -OpenOrdersBitfinex (returns a dictionary of all open orders)

In addition to this, there are several functions, that may be helpful.

-SymbolBitfinex (Input: Coin, MarketCoin / Output: Symbol Bitfinex) -GetMarketCoinsBitfinex (returns a collection of all MarketCoins (example: BTC, ETH, USD, EUR)) -GetDividedSymbolBitfinex (Input: Symbol Bitfinex / Output: Coin, MarketCoin (as a Public Type))

And I wrote some more functions, that help to interpret the Symbol of Bitfinex. The problem of this Symbol is, that it doesnt use the usual shorts (BTC, MIOTA, YOYOW). Instead it is composed by shorts of just three characters. The Symbol of MIOTA-BTC would be written like: iotbtc. Unfortunately this is not compatible with other exchanges or the data of CoinMarketCap (which I import to get the momentary average price) thus, I had to invent something new. And what I created, was an globally accessable dictionary with the correct translation:

-gTranslateDictBitfinex ( Global dictionary) which is set up by the procedure

-UpdTranslateDictBitfinex (fills the globally accessable dictionary gTranslateDictBitfinex)

the translation can return this procedure:

-GetTranslationBitfinex

Unfortunately, every time Bitfinex adds a new currency, the dictionary has to be updated manually. To make this more userfriendly, I created:

-AddSymbolToTranslateDict

This procedure asks the user to search the correct translation in Internet (ex. YOYOW instead of yyw) and adds it to the code. It is a little bit annoying, but the only practical way I could find.

There are two more globally accessable Dictionaries:

-gMarketDictBitfinex (Global dictionary that cointains all possible trades) which is set up by the procedure:

-UpdMarketDictBitfinex

-gCandleIntervalBitfinex (Global dictionary that cointains all possible candles) which is set up by the procedure:

-UpdCandleIntervalBitfinex

You can download the .bas file here: https://www.dropbox.com/s/vwvvcyj283qzlr7/ModExchBitfinex.bas?dl=0

You may make use of it as you wish, as long as I may make use of your work. Have a nice day

[RH4]

Thanks a lot @balin77 . Can you also post the GetKeys() function which you are calling inside function PrivateBitfinex? Thanks.

[RH4]

I managed to make it work. Thanks a lot for your help @balin77

[balin77]

You are welcome. As you noticed you can substitute GetKeys() with hardcoded API Keys. But I forgot to give you the functions for printing.

Printarray:

Sub PrintArray(Data, SheetName, startRow, startCol)

Dim rng As Range

With Sheets(SheetName)
    Set rng = .Range(.Cells(startRow, startCol), _
        .Cells(UBound(Data, 1) - LBound(Data, 1) + startRow, _
        UBound(Data, 2) - LBound(Data, 2) + startCol))
End With
rng.Value2 = Data

End Sub

PrintCollection:

Sub PrintCollection(Data, Worksheet, startRow, startCol)

Dim Row As Double
Dim Col As Double
Dim i As Double

Row = startCol
Col = startRow

For i = 1 To Data.Count
        Worksheet.Cells(Col, Row).Value = Data(i)
        Col = Col + 1
Next i

End Sub

PrintDictionary:

Sub PrintDictionary(ByVal d As Dictionary, WSname As String, startRow, startCol)
'The symbols ":", "[", "{" are just added for readability. They are not included in the dictionary

pStartRow = startRow
pStartColumn = startCol

Dim i As Long

For i = 0 To d.Count - 1
Key = d.Keys(i)
'Add { at the beginning of the dictionary
If i = 0 Then
    Worksheets(WSname).Cells(pStartRow, pStartColumn) = "{" & Key & ":"
Else
    Worksheets(WSname).Cells(pStartRow, pStartColumn) = Key & ":"
End If
If TypeName(d(Key)) = "Collection" Then
    pStartColumn = pStartColumn + 1
    Call PrintDictionaryCol(d(Key), WSname, pStartRow, pStartColumn)
ElseIf TypeName(d(Key)) = "Dictionary" Then
    Call PrintDictionary(d(Key), WSname, pStartRow, pStartColumn)
ElseIf TypeName(d(Key)) = "Array" Then
    MsgBox "Function not programmed for arrays!"
Else
    Worksheets(WSname).Cells(pStartRow, pStartColumn + 1) = d(Key)
    If pCallByCol = True Then
        Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn + 1)
    Else
        Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn + 1)
    End If
    pStartRow = pStartRow + 1
End If
'Add } at the end of the dictionary
If i = d.Count - 1 Then
    pLastRange.Value = pLastRange & "}"
End If
Next

If pCallByCol = True Then
pStartRow = pStartRow - d.Count
pStartColumn = pStartColumn + 2
End If

End Sub
Sub PrintDictionaryCol(ByVal Coll As Collection, WSname As String, startRow, startCol)
'Only in combination with PrintDictionary

If Coll.Count = 0 Then
Worksheets(WSname).Cells(pStartRow, pStartColumn) = "EMPTY"
Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn)
AddOne = True
End If

OriginRow = startRow
OriginColumn = startCol
DictCounter = 0

pStartRow = startRow
pStartColumn = startCol

Dim i As Long

For i = 1 To Coll.Count
If TypeName(Coll(i)) = "Collection" Then
    Call PrintDictionaryCol(Coll(i), "Test", pStartRow, pStartColumn)
ElseIf TypeName(Coll(i)) = "Dictionary" Then
    pCallByCol = True
    Call PrintDictionary(Coll(i), "Test", pStartRow, pStartColumn)
    pCallByCol = False
    If Counter < Coll(i).Count Then
        Counter = Coll(i).Count
    End If
Else
    AddOne = True
    Worksheets(WSname).Cells(pStartRow, pStartColumn) = Coll(i)
    Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn)
    pStartColumn = pStartColumn + 1
End If
If i = 1 Then
    Worksheets(WSname).Cells(OriginRow, OriginColumn).Value = "[" & 
Worksheets(WSname).Cells(OriginRow, OriginColumn).Value
ElseIf i = Coll.Count Then
    pLastRange.Value = pLastRange.Value & "]"
End If
Next i

Jumpover:

If AddOne = True Then
Counter = Counter + 1
End If

pStartRow = OriginRow + Counter
pStartColumn = OriginColumn - 1

End Sub

[balin77]

In my code it prints to an empty sheet called "Test". Its thought to be used for controlling purposes only

[krijnsent]

Thanks for the interaction, will include the code when I am near a pc (not this month).

[ghost]

Does anyone have a working Spreadsheet example of Bitfinex which I can download?

[balin77]

Yes and no. Im building a trading platform for various exchanges for Excel. It is working quite well already, but it still has a lot of mistakes. As soon as its done, im going to let you know

[Pi-Rr]

That would be amazing, looking forward to see it! Thanks :)

On Thu, 4 Oct 2018, 07:19 balin77, notifications@github.com wrote:

Yes and no. Im building a trading platform for various exchanges for Excel. It is working quite well already, but it still has a lot of mistakes. As soon as its done, im going to let you know

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/krijnsent/crypto_vba/issues/38#issuecomment-426898562, or mute the thread https://github.com/notifications/unsubscribe-auth/AiBY5pm3O4xg6mJvJEQxvcbu_yWqzQlWks5uhaiJgaJpZM4T2O_b .

[krijnsent]

Sounds cool, I'm also trying to move this code into a tool that is easier to use for end-users. @balin77 , could you send me an email to see if we can e.g. co-develop?

P.S. My silence over the past months was due to a small sabbatical without a laptop, am now putting some more time into this project again.

[balin77]

shure. Should I use the email address mentioned on your website?

[ghost]

I just want to have a button in my Excel sheet to press and retrieve my Bitfinex wallet balances to a cell. Does anyone have the code for this? Im just a basic Excel user and have set up the API links for all the public API data I need. But Im completely lost with Authenticated API.

Edit : I mean i need he code for the data connection to set up and retrieve this information, I can set up the button

[balin77]

Well actually, this is quite difficult. Data has to be fetched for three wallet types: Trading, Margin and Funding, as Bitfinex differentiates them. Then it has to be processed and finally printed to the worksheet in a readable form.

I can give you the Trading and the Margin connection, but the Funding connection is not written yet. Copy this code in a normal Module and execute the function "GetAllBalancesBitfinex". Dont forget to insert the Public and Private key in between the "" instead of YOUR_PUBLIC_KEY or YOUR_PRIVATE_KEY. This is not going to look very pretty but it should work. Otherwise let me know

Private pPrintDictDictionaries As Long
Private pStartRow As Long
Private pStartColumn As Long
Private pCallByCol As Boolean
Private pLastRange As Range

Private Function PrivateBitfinex(Method As String, Optional MethodOptions As Scripting.Dictionary, Optional GetJson = False, Optional V2 As Boolean = False)

Dim NonceUnique As String
Dim JSONResp As String
Dim JSON As String
Dim Json2 As Object
Dim PayloadDict As Scripting.Dictionary

PublicKey = "YOUR_PUBLIC_KEY"
PrivateKey = "YOUR_PRIVATE_KEY"

NonceUnique = DateDiff("s", "1/1/1970", Now) & "000"
If NonceUnique <= pNonceBitfinex Then
    NonceUnique = pNonceBitfinex + 1
End If
pNonceBitfinex = NonceUnique

'the payload has to look like this: payload = parameters-object -> JSON encode -> base64
'see the authenticated endpoints documentation here: https://bitfinex.readme.io/v1/docs/rest-auth
Set PayloadDict = New Dictionary

If Not V2 Then
    PayloadDict("request") = "/v1/" & Method
    PayloadDict("nonce") = NonceUnique

    If Not MethodOptions Is Nothing Then
        For Each key In MethodOptions.keys
            PayloadDict(key) = MethodOptions(key)
        Next key
    End If

    JSON = Replace(ConvertToJson(PayloadDict), "/", "\/")
    'Debug.Print json
    payload = Base64Encode(JSON)

    'signature = HMAC-SHA384(payload, api-secret).digest('hex')
    ApiSite = "https://api.bitfinex.com"
    Signature = ComputeHash_C("SHA384", payload, PrivateKey, "STRHEX")

    Url = ApiSite & "/v1/" & Method
    'Debug.Print Url

    'Headers
    Header1 = "X-BFX-APIKEY"
    Header2 = "X-BFX-PAYLOAD"
    Header3 = "X-BFX-SIGNATURE"
    HeaderContent1 = PublicKey
    HeaderContent2 = payload
    HeaderContent3 = Signature
Else
    apiPath = "v2/auth/r/" & Method
    Set body = MethodOptions
    rawBody = Replace(ConvertToJson(MethodOptions), "/", "\/")
    PrepSignature = "/api/" & apiPath & NonceUnique & rawBody
    Signature = ComputeHash_C("SHA384", PrepSignature, PrivateKey, "STRHEX")
    Url = "https://api.bitfinex.com/" & apiPath
    'Headers
    Header1 = "bfx-nonce"
    Header2 = "bfx-apikey"
    Header3 = "bfx-signature"
    HeaderContent1 = NonceUnique
    HeaderContent2 = PublicKey
    HeaderContent3 = Signature
End If

'Instantiate a WinHttpRequest object and open it
HTTPMethod = "POST"
Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
objhttp.Open HTTPMethod, Url, False
objhttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objhttp.setRequestHeader Header1, HeaderContent1
objhttp.setRequestHeader Header2, HeaderContent2
objhttp.setRequestHeader Header3, HeaderContent3
objhttp.Send get_url

objhttp.WaitForResponse
JSONResp = objhttp.responseText
Set objhttp = Nothing
Set Json2 = JsonConverter.ParseJson(JSONResp)

Call ErrorHandlerBitfinex(Json2)

If GetJson Then
    Set PrivateBitfinex = Json2
Else
    ResArr = JsonToArray(Json2)
    PrivateBitfinex = ArrayTable(ResArr)
End If

End Function
Function GetAllBalancesBitfinex() As Scripting.Dictionary

Dim TradingType As String, Coin As String
Dim Balance As Double, Available As Double, Pending As Double

Dim PivotDict As Scripting.Dictionary
Dim TradingDict As New Scripting.Dictionary
Dim MarginDict As New Scripting.Dictionary

Set GetAllBalancesBitfinex = New Scripting.Dictionary

Dim JSONCol As Collection
Set JSONCol = PrivateBitfinex("balances", , True)
For Each CoinDict In JSONCol
    TradingType = CoinDict("type")
    Coin = CoinDict("currency")
    If Coin = vbNullString Then GoTo NextIteration
    Balance = CoinDict("amount")
    Available = CoinDict("available")
    Pending = Balance - Available
    'Add to PivotDict
    Set PivotDict = New Scripting.Dictionary
    PivotDict.Add "Balance", Balance
    PivotDict.Add "Available", Available
    PivotDict.Add "Pending", Pending
'    Add to Trading or MarginDict
    If TradingType = "exchange" Then
        TradingDict.Add Coin, PivotDict
    ElseIf TradingType = "trading" Then
        MarginDict.Add Coin, PivotDict
    End If
NextIteration:
Next

GetAllBalancesBitfinex.Add "TRADING", TradingDict
GetAllBalancesBitfinex.Add "MARGIN", MarginDict

Call PrintDictionary(GetAllBalancesBitfinex, "Test", 1, 1)

end function

Sub PrintDictionary(ByVal d As Dictionary, WSname As String, startRow, startCol, Optional RecursiveCall As Boolean = False)
'The symbols ":", "[", "{" are just added for readability. They are not included in the dictionary

pStartRow = startRow
pStartColumn = startCol

If RecursiveCall Then
    pStartColumn = pPrintDictDictionaries + 1
Else
    pPrintDictDictionaries = 0
End If

Dim i As Long

For i = 0 To d.Count - 1
    key = d.keys(i)
    'Add { at the beginning of the dictionary
    If i = 0 Then
        Worksheets(WSname).Cells(pStartRow, pStartColumn) = "{" & key & ":"
    Else
        Worksheets(WSname).Cells(pStartRow, pStartColumn) = key & ":"
    End If
    If TypeName(d(key)) = "Collection" Then
        pStartColumn = pStartColumn + 1
        Call PrintDictionaryCol(d(key), WSname, pStartRow, pStartColumn)
    ElseIf TypeName(d(key)) = "Dictionary" Then
        pPrintDictDictionaries = pPrintDictDictionaries + 1
        Call PrintDictionary(d(key), WSname, pStartRow, pStartColumn, True)
    ElseIf TypeName(d(key)) = "Array" Then
        MsgBox "Function not programmed for arrays!", vbCritical
    Else
        Worksheets(WSname).Cells(pStartRow, pStartColumn + 1) = d(key)
        If pCallByCol = True Then
            Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn + 1)
        Else
            Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn + 1)
        End If
        pStartRow = pStartRow + 1
    End If
    'Add } at the end of the dictionary
    If i = d.Count - 1 Then
        pLastRange.Value = pLastRange & "}"
        If pPrintDictDictionaries <> 0 Then
            pPrintDictDictionaries = pPrintDictDictionaries - 1
            pStartColumn = pPrintDictDictionaries + 1
        End If
    End If
Next

If pCallByCol = True Then
    pStartRow = pStartRow - d.Count
    pStartColumn = pStartColumn + 2
End If

End Sub
Sub PrintDictionaryCol(ByVal Coll As Collection, WSname As String, startRow, startCol)
'Only in combination with PrintDictionary

If Coll.Count = 0 Then
    Worksheets(WSname).Cells(pStartRow, pStartColumn) = "[EMPTY]"
    Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn)
    AddOne = True
    pStartColumn = pStartColumn + 1
End If

OriginRow = startRow
OriginColumn = startCol
Counter = 0
CollCounter = 0

pStartRow = startRow
pStartColumn = startCol

Dim i As Long

For i = 1 To Coll.Count
    If TypeName(Coll(i)) = "Collection" Then
        Call PrintDictionaryCol(Coll(i), "Test", pStartRow, pStartColumn)
        pStartColumn = pStartColumn + 1
        CollCounter = CollCounter + 1
    ElseIf TypeName(Coll(i)) = "Dictionary" Then
        pCallByCol = True
        Call PrintDictionary(Coll(i), "Test", pStartRow, pStartColumn)
        pCallByCol = False
        If Counter < Coll(i).Count Then
            Counter = Coll(i).Count
        End If
    Else
        AddOne = True
        Worksheets(WSname).Cells(pStartRow, pStartColumn) = Coll(i)
        Set pLastRange = Worksheets(WSname).Cells(pStartRow, pStartColumn)
        pStartColumn = pStartColumn + 1
    End If
    If i = 1 Then
        Worksheets(WSname).Cells(OriginRow, OriginColumn).Value = "[" & Worksheets(WSname).Cells(OriginRow, OriginColumn).Value
    ElseIf i = Coll.Count Then
        pLastRange.Value = pLastRange.Value & "]"
    End If
Next i

If CollCounter = 0 Then
    If AddOne = True Then
        Counter = Counter + 1
    End If
Else
    Counter = Counter + CollCounter
End If

pStartRow = OriginRow + Counter
pStartColumn = OriginColumn - 1

End Sub

[balin77]

I forgot to mention: you need a worksheet called "Test". otherwise it wont work. And you may connect your button with the function "GetAllBalancesBitfinex". Then it starts the macro every time you press the button

[krijnsent]

Finally got an account to test, built the v1 and v2 with tests, please test & close @RH4 @balin77 Will include Bitfinex in the example file soonish ;)

[krijnsent]

Did anyone get the chance to test this? I'd like to close the issue :).

[balin77]

Hey Koen,

I actually stopped working on the Excel some time ago. It started to become too complicated as the API of Coinmarketcap.com changed to a paid model. The general coin data and its naming that was fetched there was the basis for every other exchange. I tried to migrate to Coingecko but that didnt work out very well. The data structure is organized very differently. I tried to program some kind of javascript server to fetch the data periodically and store it in a mysql database but eventually I just gave up...

So I've actually never tested it, I'm afraid.

Greetings

Raphael

Koen Rijnsent notifications@github.com schrieb am Do., 23. Jan. 2020, 11:59:

Did anyone get the chance to test this? I'd like to close the issue :).

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/krijnsent/crypto_vba/issues/38?email_source=notifications&email_token=AINRI7ZXY2SHRQL3OLHQZTLQ7FZ7ZA5CNFSM4E6Y57N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJW7MTQ#issuecomment-577631822, or unsubscribe https://github.com/notifications/unsubscribe-auth/AINRI73HHF25E3FH2ZE7ELDQ7FZ7ZANCNFSM4E6Y57NQ .

[krijnsent]

Hey Koen, I actually stopped working on the Excel some time ago. It started to become too complicated as the API of Coinmarketcap.com changed to a paid model. The general coin data and its naming that was fetched there was the basis for every other exchange. I tried to migrate to Coingecko but that didnt work out very well. The data structure is organized very differently. I tried to program some kind of javascript server to fetch the data periodically and store it in a mysql database but eventually I just gave up... So I've actually never tested it, I'm afraid. Greetings Raphael Koen Rijnsent notifications@github.com schrieb am Do., 23. Jan. 2020, 11:59: … Did anyone get the chance to test this? I'd like to close the issue :). — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#38?email_source=notifications&email_token=AINRI7ZXY2SHRQL3OLHQZTLQ7FZ7ZA5CNFSM4E6Y57N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJW7MTQ#issuecomment-577631822>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AINRI73HHF25E3FH2ZE7ELDQ7FZ7ZANCNFSM4E6Y57NQ .

Thanks for the update.