Binance/Poloniex Historical Data Inaccurate (possibly all exchanges)

[ITJusus]

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?

[ITJusus]

I know this is a free offering / side project, so I don't expect immediate responses of course. If someone could at least confirm for me, in the community or the author, that this issue is indeed a problem on the "CryptoCompare" side of things...I can then go raise this issue with them that they are pushing out inaccurate Binance historical price info.

[krijnsent]

Hi @ITJusus , that sounds weird. I'll try to get the same data from the Binance API, but it does indeed sound like a CryptoCompare issue. What you could do is debug.print the raw command that's used and copy-paste that in the browser, that should give you the exact JSON to compare with Binance.

[ITJusus]

Thank you. I emailed info@cryptocompare.com and I also submitted a ticket to what appears to be the CryptoCompare API issue tracking site...

https://github.com/ccxt/ccxt/issues/2651

Please let us know if anyone on here figures out anything new about this and I'll let you know if CryptoCompare responds with anything meaningful as well.

[ITJusus]

They basically just said that the Binance historical values are pulling accurately for them and to ask the author of Crypto_VBA instead. Full circle.

Would you be able to test getting the Binance historical prices for a coin (for instance ZRXBTC) to ensure I'm not missing something? I see no pattern with the Binance prices that come back other than they are wrong (sometimes above and sometimes below what Binance shows), but always off. Again, its as if it is the wrong exchange's data is being pulled.

I was thinking that maybe Crypto_VBA is pulling the Ask price when it should be the Bid price, but that doesn't make sense if the price is sometimes lower. Hmm.

[ITJusus]

I just tested using Crypto_VBA to get Poloniex based historical prices as well. These numbers are not accurate to the TradingViewer/Poloniex charting numbers either. So, this seems to be a wider issue with the historical data that is returned for multiple exchanges via CrypoCompare to Crypto_VBA. Is this just me or are you getting the same results out there?

Call C_ARR_OHLCV("H", "ZRX", "BTC", "EOHLC", "60", Now, "Binance") Call C_ARR_OHLCV("H", "ZRX", "BTC", "EOHLC", "60", Now, "Poloniex")

[krijnsent]

Hi @ITJusus Try e.g. the code below. I've used both the Binance API (klines command) and the CryptoCompare API to pull in more or less the same data. You'd need a sheet called "TEST" for this code to work. The Binance data looks a bit wonky, but you could check out https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md (kline command) for more details on the columns. For clarity, add a formula like =UnixTimeToDate(C5/1000) in column O If you compare that data with the one of CryptoCompare, there is quite a good match. So I'm not sure where your comparison goes wrong, but this seems to work quite okay to me. Cheers, Koen

`Sub TestBinance()

'klines Dim JsonResponse As String Dim Json As Object Set Sht = Worksheets("TEST")

JsonResponse = PublicBinance("klines", "?symbol=ZRXBTC&interval=1d") Set Json = JsonConverter.ParseJson(JsonResponse) ResArr = JsonToArray(Json) tbl = ArrayTable(ResArr, True) Sht.Range("B4").Resize(UBound(tbl, 2), UBound(tbl, 1)) = TransposeArr(tbl)

Dim DHM As String, Buy As String, Sell As String, Cols As String, NrL As Long, MTD As Date, Exch As String

DHM = "1D" Buy = "ZRX" Sell = "BTC" Cols = "TEOHLCVF" Exch = "Binance"

ResTbl = C_ARR_OHLCV(DHM, Buy, Sell, Cols, NrL, MTD, Exch) Sht.Range("X4").Resize(UBound(ResTbl, 1), UBound(ResTbl, 2)) = ResTbl

End Sub`

[krijnsent]

A brainfart: could it be that there is just a shift in the data (some hours different, maybe caused by timezones)?

[Pi-Rr]

Hello,

I have run some tests using the C_ARR_OHLCV (e.g. C_ARR_OHLCV("1H","RCN","BTC","TEOHLCFV",48,NOW(),"Binance") and compared to Binance charts and it all mach perfectly for me (except for the time which is 1h behind, but that's probably a local settings issue).

I also cross-checked with the "RATES_MACRO" tab that is available in the crypto_vba_example.xlsm file, and it matches too.

krijnsent, do you have the issue too?

[ITJusus]

The Test Binance sub you provided above matches Binance, once I align the timezone to UTC. And then I started tracking back the results when I use...

C_ARR_OHLCV("1H","ZRX","BTC","EOHLCFV",60,NOW(),"Binance")

I found the pattern. The results are exactly -4 hours from my own timezone (UTC-4 NY). I'm going to try to chase down that path in the code to make sure I didn't do anything to mess up the time zone in your code.

[ITJusus]

Am I supposed to modify the code somewhere to accommodate for my own timezone? It's giving me results that are -4 hours off. For instance, the date/time stamp shows 21:00, but the values match 17:00 prices.

[ITJusus]

Get this, when I change my computer's system time zone from New York (UTC -4/5) to London (UTC)...now Crypto_vba at least does pull the real latest prices (up to Now), but the prices labled 01:00 in the results are actually a perfect match for 21:00 in my real time zone. It's like Crypto_vba is seeing my system time zone is UTC -4 and then it is somehow grabbing data that's 4 hours old and only up to 4 hours old (completely missing the last 4 hours of data in the results).

I'm using VB v6.5 (Excel 2007)...do you think this is causing the issue or is there something I can fix in the code to force Crypto_vba to pull the latest historical price data without being -4 hours behind?

[ITJusus]

I ruled out it being an issue with VB v6.5 (Excel 2007). I ran the same code in VB v7.1 (Excel 2016) and it still outputs results for historical prices that are 4 hours old (missing the last 4 hours of historical prices and showing for the last hour what was actually 4 hours ago).

[ITJusus]

I was able to modify the Crypto_vba code's C_ARR_OHLCV function, adding the following line...

If MaxTimeDate > DateSerial(2000, 1, 1) Then MaxTimeDate = DateAdd("h", 4, MaxTimeDate) 'Custom TimeZone Change dt = DateToUnixTime(MaxTimeDate) TimeTxt = "&toTs=" & dt

Now I actually do get the last 4 hours of historical price data in my results, but the dates themselves are 4 hours in the future. Trying to figure out how I can balance out this problem without having to add a lot of extra time to the processes having to re-update the date of each row in Excel each time I have to pull this data. The best approach would be for Crypto_vba to get the correct data, aligned with the correct date, from the get go.

[krijnsent]

I think i made the time variable in that function optional, so you can leave it out all together. All other times are interpreted as being UTC. I didn't build anything to deal with timezones, so that's up to you. If you have some general code that could help all users, please let me know so I can include it in this project.