krijnsent / crypto_vba Goto Github PK

I remember reading, somewhere in here, that you planned on some day adding support for pulling data related to open positions. I utilize your program for trading on Binance, but kind of have to drive blind when it comes to the program knowing if I have open positions or not.

If some code for grabbing open position (trades) data were added, that would be an amazing icing on the cake. Thank you for all of your work on this.

Hi Koen,

Thanks a lot for adding Kucoin to the spreadsheet. The import of the balance works, but partially only.
I own a really small amount of a lot of different tokens on that exchange (due to the Kucoin (KCS) token paying to holders a small fraction of the transaction fees of any token traded on the platform). However, when I run the macro, it only pulls the data for 12 tokens, some of which are very random ones with a super small balance, and it is missing a bunch of the more significant ones.

Is that something you can fix?
I understand it might cause some performance issues to pull the data for every single token - in that case it is possible to prioritise the ones with the highest balance value?

Many thanks!

Hi krijnsent,

First of all, a big thanks for all the work you have been doing here, this is super helpful and saving me a lot of time! I have sent you a little contribution to your BTC address as a mark of my gratitude :)

I have been using your spreadsheet/code for a week now and I have a few feature requests I would like to ask. I will create a separate ticket for each, so your GitHub stays clean.

Starting with an easy one: I see you have added the code to import data from Kucoin yesterday, but there is currently no Kucoin tab in the example workbook. Can you please add one tab similar to the other ones (e.g. Bittrex), to make easy it to import private portfolio data from Kucoin?

I hope you will continue to have time to further improve your spreadsheet and that people will keep supporting you!

All the best,
Pierre

I saw you added HitBTC API v1. Do you think you could update it to v2?

https://api.hitbtc.com/

I ran VBA ModExchBinance/Sub TestBinance() but I got error, pls, advice:
{"error_nr":-2147012739,"error_txt":"VBA-WinHttp.WinHttpRequest ?????? ????????? ?????????? ???????
"}

Throwing an error on run re DateToUnixTime function

Hi again,

Here is my last feature request (it was a lot!). Would it be possible to develop some code to import the historical prices and volumes from Kraken for any given pair available on the exchange?
Ideally, the user will have to input three things:

1. The pair he is interested in (e.g. ETH/EUR, XRP/BTC, etc.)
2. The desired time interval between each prices (e.g. 5 minutes, 1 hour, etc.)
3. The desired period to cover (e.g. last 6 months, from 01-Jan-2017 to 31-Dec-2017, etc.)

If you think you could help with that, it would be amazing :)

Dank u wel!
Pierre

Hi, thanks for building this!

I am having trouble getting any of the Private API functions to work. Specifically, excel crashes whenever I call any of the CreateObject("System.Security.Cryptography.SHA256Managed") lines of modHash. I get this error:

Run-time error '-2146232576 (80131700)':
Automation error

Any ideas what I need to do to get it working? I've added mscorlib.dll as a reference (it's v4.0.30319)
If it makes any difference, I'm using Office Pro 2016, and I'm running Windows 10

Zylo

Hi, I tried in the past to use the Cpryptopia API on excel but had no success.
You may have the same issue, and I really want to help to be able to use cryptopia on excel...
The only hint that I found about the error was:

https://social.technet.microsoft.com/Forums/en-US/b10b16d1-8eea-4b52-8aeb-f96ea87135fa/sectionresponseheader-detailcr-must-be-followed-by-lf?forum=powerquery

please, let me know if you were able to pull market data from cryptopia to excel

regards,

Hi and big thanks for this project.

I am an newbie using vba, so I would really appreciate it if you could add a simple instruction how to properly install and use this tool.

Thx

Hi krijnsent,

Since approximately a week, I am getting a run-time error '-2147012746 (80072f76)' every time I try to update my balance for Kucoin.
The error occurs at line 70 of the PrivateKucoin function:

objHTTP.setRequestHeader "KC-API-KEY", apikey

It would be great if you could have a look when you get some time.

Many thanks!

Hello
I would like to cancel a buy or sell order on Binance, but I can not. thank you for giving me a track.
Purchase and sale orders work very well.
My command line
PrivateBinance("order", apikey, secretkey, "symbol=BNBUSDT&orderId=83375426&timestamp=" & Temps)
Error message
{"code":-1101,"msg":"Duplicate values for a parameter detected."}
A thousand excuses for English
Merci

Hi, would it be possible to put the trading options like buy sell cancel pls, I have some issues with Kucoin and Hitbtc
thanks

Hi there,
It's strange. When I try the code after midnight it no longer works and throws this error on any type of API call via VBA.
I can't say till what timestamp this issue occurs, becasuse when I tested this morning it worked again.
For info, I'm on CET+1h
Nice work by the way.
KR;,
Bart

Hey krijnsent,

as I use Bitfinex as exchange amongst others, I have tried to make the connection myself, using your code as example. The Public query works fine, but the Private query doesnt even receive an answer. I suppose its because of the very strange hash of the payload. I followed these two examples to write the vba code:

python code: https://gist.github.com/jordanbaucke/5812039
vb.net code: http://www.vbforums.com/showthread.php?777193-Need-some-help-getting-my-code-to-work-with-the-Bitfinex-API

I tried to implement the vb.net code, but its functions is just too different as that i could adapt it.

my code looks like this (maybe you can see my mistake):

`Sub TestBitfinex()

Dim apikey As String
Dim secretkey As String
Dim Coins As Worksheet
Set Coins = Worksheets("Coins")

apikey = Coins.Range("apikey_bitfinex")
secretkey = Coins.Range("secretkey_bitfinex")

Debug.Print PublicBitfinex("symbols", "")
'["btcusd","ltcusd","ltcbtc","ethusd","ethbtc","etcbtc","etcusd","rrtusd"...
Debug.Print PublicBitfinex("pubticker", "ltcbtc")
'{"mid":"0.0171145","bid":"0.017113","ask":"0.017116","last_price":"0.017105","low":"0.01666","high":"0.01721","volume":"85227.17880718","timestamp":"1515663208.679153"}
Exit Sub

'Unix time period:
t1 = DateToUnixTime("1/1/2014")
t2 = DateToUnixTime("1/1/2018")

'To pass method options: (I couldnt test this yet, because it doesnt work without method options either)

'Dim Dictionary As New Scripting.Dictionary
'Set Dictionary = New Dictionary
'Dim Options As New Collection

'Dictionary(MethodOption1) = "XYZ"
'Dictionary(MethodOption2) = "XYZ"
'Options.Add Dictionary

Debug.Print PrivateBitfinex("account_infos", apikey, secretkey)

End Sub

Function PublicBitfinex(Method As String, Optional MethodOptions As String) As String

'This works
'https://api.bitfinex.com
Dim Url As String
ApiSite = "https://api.bitfinex.com"
urlPath = "/v1/" & "/" & Method & "/" & MethodOptions
Url = ApiSite & urlPath
Debug.Print Url

PublicBitfinex = GetDataFromURL(Url, "GET")

End Function
Function PrivateBitfinex(Method As String, apikey As String, secretkey As String, Optional MethodOptions As Collection)
Dim NonceUnique As String
NonceUnique = DateDiff("s", "1/1/1970", Now)

'see the general Bitfinex documentation here: https://bitfinex.readme.io/v1/docs/rest-general

Dim PayloadDict As Scripting.Dictionary
Set PayloadDict = New Dictionary

'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
PayloadDict("request") = "/api/v1/" & Method
PayloadDict("nonce") = NonceUnique
If Not MethodOptions Is Nothing Then
Set PayloadDict("options") = MethodOptions
End If

Dim Json As String
Json = ConvertToJson(PayloadDict)
payload = Base64Encode(Json)
'Debug.Print Json
'Debug.Print Payload

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

Url = ApiSite & "/" & Method
HTTPMethod = "POST"

' Instantiate a WinHttpRequest object and open it
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 "X-BFX-APIKEY", apikey
objHTTP.setRequestHeader "X-BFX-PAYLOAD", payload
objHTTP.setRequestHeader "X-BFX-SIGNATURE", Signature
objHTTP.Send get_url

objHTTP.WaitForResponse
PrivateBitfinex = objHTTP.ResponseText
Set objHTTP = Nothing

Debug.Print PrivateBitfinex

End Function`

Hey,

have you thought about the API of Bithumb.com?

Bye

Moe

I'm having a hard time to understand how to place "buy" and "sell" orders.
Could you guide me on that?

Thanks!

Hi,

I was trying to port your code to .NET. I started with Binance. I can't get my head around how to make the signature. I always get a message saying Invalid API key. Obviously, I have a api key. the work fine under linux with python. So I know the key works. Can you give me a hand with it?

I don't know if this is a known issue or not, but when pulling historical prices (via C_ARR_OHLCV method) the prices are way off from the actual exchange requested. For example, when I utilize...

C_ARR_OHLCV("H", "RCN", "BTC", "EOHLC", "60", Now, "Binance")

And then I compare the outputted values to the actual charts on Binance or on TradingView (filtered as Binance)...the TradingView and official Binance site's chart values match perfectly to each other, but the values generated by Crypto_vba are often not even in the same candle range. Sometimes I see the High price generated from Crypto_vba is below the entire candle itself (looking at Binance/TradingView)...like I said, way off.

Is all of this data being pulled from CryptoCompare and perhaps they are just ignoring the request for "Binance" or perhaps are they returning the wrong exchanges data?

I was wondering if its possible to get a option to aggregate time ex: 5m,15m,2h,7d
Thank you!!

Hi, have issues manually importing the vba references. Is there a way to do it automatically?

Hi Koen,

Kucoin has released a new version of its API and the macro to pull balance of our tokens is no longer working. Could you have a look and try to fix that in the crypto_vba_example.xlsm spreadsheet when you get a chance?

Many thanks!

Could you please add Coinbase and GDAX?

https://developers.coinbase.com/

https://docs.gdax.com/

Hello. I use Win7 SP1 and Excel 2010.
The solution, that was described in previous ticket is not working.

set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Option(4) = 13056
objHTTP.Open "POST", Url, False

The same error is appeared.

Very Well Done. Thank you very much to your work.
Also cpuld you add Coinex and CEX.IO to this good work
Best Regards.

I've adapted the access code for LocalBitcoins API (https://localbitcoins.com/api-docs/), but I have problems for POST request. The GET request is working fine. I've posted a question on stackoverflow along with the code used: https://stackoverflow.com/questions/54117140/localbitcoins-authenticated-http-post-request-error. Maybe someone more experienced can spare the time and maybe give me a hint on this.

Great work here Koen. Road opener. Thanks!

Hey developer,

Thanks for creating such an useful tool.
I was trying to run the macro in the excel file on Kraken sheet and the vba stops at the line iV=Dictln(k). The error msg shows as
"Run-time error '450':
Wrong number of arguments or invalid property assignment"

Do you know how I should overcome this?
Thanks

Good day. Thank you for great work for vba users!
I have one problem with post order on btsusd pair. I set quantity = 0.01, type = market and get response with error "Quantity is not valid number". If i try to change quantity = 1, i have response "Insufficient funds". and if I post order wiht DOGEBTC for examply where quantity =1000 i have success order. It seems like HitBTC don't alLow me trade with quantity less than 1 BTC. But quantityIncrement ia 0.01 for BTCUSD.
I have general verification account.
Can anybody explain me how solve this problem?

`Sub placeorderHitBTC()
Dim httpObject As Object
Dim nonce As Double
Dim verb, symbol, url, postdata, replytext, nonceStr As String
'âðåìåííî
Set thiswb = ActiveWorkbook
qty = 0.01
side = "sell"

' Set api key and secret
apiKey = ".."
apiSecret = ".."

nonce = CreateNonce(10)
nonceStr = nonce
symbol = "BTCUSD"
url = "/api/2/order"
postdata = "symbol=" & symbol & "&quantity=" & qty & "&type=market" & "&side=" & side

Set httpObject = CreateObject("msxml2.xmlhttp") '
httpObject.Open "POST", "https://api.hitbtc.com" & url, False
httpObject.setRequestHeader "accept", "application/json"
httpObject.setRequestHeader "Authorization", "Basic " & Base64Encode(apiKey & ":" & apiSecret)
httpObject.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpObject.Send postdata

' Catch response
replytext = httpObject.ResponseText
thiswb.Worksheets("Òåðìèíàë").Cells(1, "Q") = replytext

Set httpObject = Nothing
`

Hi again krijnsent,

Here is my second feature request. The Kraken tab in the example workbook is currently importing the deposit history when running the GetMyKrakenData macro. Could you please make it import the latest private portfolio balances instead (e.g. similar to what the Bittrex or Binance tabs do).

Many thanks,
Pierre

Hello Koen,

I saw you updated the following line:
NonceUnique = Trim(Str((Val(NonceUnique) + TimeCorrection)) & Right(Timer * 100, 2) & "0")
However, after midnight, still the same issue occurs relating these invalid caracters.
Any other suggestions?
Thanks.

Kind regards

Hello,

Thank you very much for this excel file. It is very useful and helpful for me.

Could you add Cex.io, please?

Kind Regards,

BM

Hi,

you did a great job! Thanks for this! I just got one problem in function

Function ArrayTable(ArrIn As Variant, Optional ReturnHeader As Boolean = True) As Variant

in module ModJSON.

I receive this JSON:
In the browser it looks like: {"success":false,"message":"APISIGN_NOT_PROVIDED","result":null}
In VBA it looks like: {"success":true,"message":"","result":[]}

In the function ArrayTable() at position:

Idx = Application.Match(ArrIn(3, rw), TblHeaders.keys, 0)
  If ReturnHeader = True Then
  ResArr(Idx, 1) = ArrIn(3, rw)
End If

Idx gets the value "Error 2042" and then ResArr(Idx, 1) = ArrIn(3, rw) does not work. The value of ArrIn(4,4) is 0. Can we use this information to skip "result"?

TblHeaders counts only 2 values "success" and "message". I thought about continue the for-loop, but I am not sure.

Maybe you have an idea.

Bye

Moe

Opening a new tickets for Kraken as the private balance download is not working consistently.
Sometime running the macro works perfectly, and sometime the Kraken Balance table return the following error: "EAPI:Invalid nonce".

I cannot figure out why it is happening, but if you wait and try again a bit later it works again. Not a critical issue therefore, but I wanted to flag it.

Thanks again for sharing your work!

Hi Koen,
Many thanks for writing this code. It has been very helpful for me to learn how to make API calls.
However, I use www.coinspot.com.au and I'm unable to amend your script to work with their requirements.
Would you be able to have a look at creating one please?
They only have a single page on their website that details their API requirements - https://www.coinspot.com.au/api
Thanks, Mark.

Hi, I am trying to cancel active order (i got orderId) on binance and I use:
Debug.Print PrivateBinance("order", apikey, secretkey, "symbol=ICXBTC&orderId=25200510")
but I got error {"code":-1102,"msg":"Mandatory parameter 'side' was not sent, was empty/null, or malformed."}. Pls, help me.
Thanks
Saga

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.

Hi!
I have taken your VBA example (I have made small cleanup to be able to compile it)

I can not get private data from Kraken because I get and error code:

"Excel 2010 VB Run-time Error '-2146232576 (80131700)' Automation Error"

in the line:

Set SHAhasher = CreateObject("System.Security.Cryptography.SHA256Managed")

I am working on Windows 10; I have NET Framework64 mscorlib.dll v4.0.30319.

I was searching for a long time for the solution without success.

Do you have any idea what to do?

Regards
Marko

Can you make some steps on how to get started. It's really not clear please.

thanks for you work.

You have an email at [email protected].

Hi, Excel shows me always a runtime error the channel is not secured if I try to use the Poloniex API. Any ideas?

Hi there,

Here is my third feature request. Would it be possible to develop some code to import the balances of a wallet (ETH and any other token) directly from a public key? Maybe that’s possible using the Etherscan api, or the one of any other blockchain explorer?

Many thanks,
Pierre

I'm getting this error in Excel 2016 for Mac. I try to get information from Binance.

Don't know what is going wrong.

Please assist.

Hi,
When I execute Sub TestBinance() at crypto_vba_example.xlsm, I see an error at line
"Test.IsOk JsonResult("balances").Count > 10" . The pop up says run time error 424 object required.
Thank you.

Hey dude,

I use basically the same code for private Bittrex requests, but two days ago, it just stopped working. The only answer I receive is "Bad Request". I have no idea what the problem might be, since i have not changed the code. Could you have a look if you receive the same error?

First of all thank you for your work. I have been working on your vba in order to get one of my exchange account running. However, there is no luck so far i get lost on how to send POST form data.
I sent the request and get this error.
{"success":false,"error":"You did not set any API key. Make sure you send your request as POST form-data, and not as GET or JSON body request."}
The document is in english and in this link https://bx.in.th/info/api/
If you have a bit time your help would be very appreciate.
Thank you in advance

Below is the code

Function PrivateBX(Method As String, apikey As String, secretkey As String, Optional MethodOptions As Collection)
Dim NonceUnique As String
Dim Json As String
Dim PayloadDict As Scripting.Dictionary
Dim twofa As String
Dim temp As String
Dim strResult As String

twofa = ""

NonceUnique = CreateNonce(15)
temp = apikey + NonceUnique + secretkey
ApiSite = "https://bx.in.th/api/"
Signature = ComputeHash_C("SHA256", temp, "", "byte")
Url = ApiSite + Method + "/"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "POST", 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.send ("apikey=apikey & nonce=NonceUnique & signature=Signature & twofa=twofa")
objHTTP.send (postdata)
objHTTP.WaitForResponse
PrivateBX = objHTTP.responseText
Set objHTTP = Nothing

End Function

Hi krijnsent,

I see in the comment that you added HitBTC v2 in the latest version of of the crypto_vba_example spreadsheet, however I don't see a HitBTC tab. Could you please add it to the spreadsheet when you get sometime, or let me know if I am missing something?

Many thanks

How do I pull all the wallet data from Bittrex including the empty wallets....I believe there are 210 of them. Currently the code is only pulling 83 of them. ADA to XVG only. How Do I fix this?

First of all, tank you all for this awesome project !!

I just tried to used the program and got this error when i click on button which is linked to GetMyBinanceData()

It's quite strange , because i got 32 GB of memory ..

Can somebody help please ?

I've been running into an issue where the app stops pulling price data. Once I'm in this state, when I go to the CryptoCompare api website (https://min-api.cryptocompare.com/)...I'm presented with a message similar to the following, rather than the normal webpage:

{"Response":"Error","Message":"You are over your rate limit please upgrade your account!","HasWarning":false,"Type":99,"RateLimit":{"calls_made":{"second":1,"minute":1,"hour":44,"day":44,"month":2010716,"total_calls":2125796},"max_calls":{"second":50,"minute":1000,"hour":20000,"day":200000,"month":2000000}},"Data":{}}

I eventually went ahead and generated my own API Key from the site (when I could access it again), but I don't know where I would plug my new API Key into the crypto_vba code.

Can you tell me if there is a way around getting stuck in this situation as well as where I might plugin my own API Key for CryptoCompare.?

Could you please add HitBTC and can you give any example to import open orders and cancel / place the order.