search

binance / binance-public-data

Details on how to get Binance public data
1.4k stars 444 forks source link

futures 'book ticker' for ETH and BTC are output out of sequence. #305

Closed zeroflaw closed 2 months ago

zeroflaw commented 5 months ago

Hello, since January the ETH and BTC book ticker data files are a bit of a mess. I have to re-sort the file with the following command:

duckdb -c "COPY (SELECT * FROM(SELECT * FROM read_csv('ETHUSDT-bookTicker-2024-01-15.csv', columns = {'update_id': 'UBIGINT', 'best_bid_price': 'VARCHAR', 'best_bid_qty': 'VARCHAR', 'best_ask_price': 'VARCHAR', 'best_ask_qty': 'VARCHAR', 'transaction_time': 'BIGINT', 'event_time': 'BIGINT'}, header = true)) ORDER BY event_time,update_id) TO 'fixed-ETHUSDT-bookTicker-2024-01-15.csv' (HEADER, DELIMITER ',');"

Please take a look at the following sample : https://data.binance.vision/data/futures/um/daily/bookTicker/ETHUSDT/ETHUSDT-bookTicker-2024-01-15.zip

You will see the lines seem to be interleaved out of order like:

3831603310013,2474.30000000,6.00900000,2474.31000000,44.75700000,1705276800000,1705276800005
3835812359996,2535.78000000,57.39300000,2535.79000000,14.66800000,1705350085394,1705350085401
3831603310668,2474.30000000,6.09000000,2474.31000000,44.75700000,1705276800011,1705276800016
3835812361675,2535.78000000,57.38400000,2535.79000000,14.66800000,1705350085422,1705350085427
3831603313269,2474.30000000,6.09000000,2474.31000000,44.41200000,1705276800044,1705276800050
3835812361874,2535.78000000,57.38400000,2535.79000000,14.69100000,1705350085427,1705350085432
3831603314855,2474.30000000,6.09000000,2474.31000000,44.75700000,1705276800070,1705276800074
3835812362559,2535.78000000,57.38400000,2535.79000000,14.29300000,1705350085447,1705350085453
3831603315653,2474.30000000,6.09000000,2474.31000000,42.73700000,1705276800082,1705276800088

It should look like this:

3831603310013,2474.30000000,6.00900000,2474.31000000,44.75700000,1705276800000,1705276800005
3831603310668,2474.30000000,6.09000000,2474.31000000,44.75700000,1705276800011,1705276800016
3831603313269,2474.30000000,6.09000000,2474.31000000,44.41200000,1705276800044,1705276800050
3831603314855,2474.30000000,6.09000000,2474.31000000,44.75700000,1705276800070,1705276800074
3831603315653,2474.30000000,6.09000000,2474.31000000,42.73700000,1705276800082,1705276800088
3831603315896,2474.30000000,6.15000000,2474.31000000,42.73700000,1705276800085,1705276800090
3831603315897,2474.30000000,6.21000000,2474.31000000,42.73700000,1705276800085,1705276800090
3831603315899,2474.30000000,6.27000000,2474.31000000,42.73700000,1705276800085,1705276800090
3831603315902,2474.30000000,6.33000000,2474.31000000,42.73700000,1705276800085,1705276800090