Appending dataframe to excel in python

Question

Sorry I cannot give all the code. Basically, I am retrieving the OrderBook of a few hundred cryptocurrencies every second. Every time I retrieve it I need to add it into an excel readable file for another department. Every excel will save all the data in one day. The retrieved order book record will be something that looks like this.

time | exchange | price | quantity | side

unit time| BINANCE | 1.00 | 90925 | ask

I have tried pd.excelwriter append, read_csv combine write_csv and appending to pre-saved pandaframe.

However, all these options are too slow to get under 1 second even I boosted the code with cython. Websocket with thread only takes 0.2s to retrieve the data so we have 0.8s to append. Something is fixed: Python should be the language, my co-worker only knows python, the saving format must be any file extension that excel can read as the other department doesn't know coding.

Can someone help me with some ideas? I can do the implementation myself. Just want some idea.

Answer 1

A slightly different approach could be to write an Excel add-in in Python to get the data into Excel. That should be much faster than writing out a workbook each time the data changes.

You can do that using PyXLL (https://www.pyxll.com), which is a commercial (paid for) product. Using PyXLL you could write RTD (real time data) functions to stream real time data directly into Excel.

Your colleagues would also need the PyXLL add-in installed, configured to load your Python code - then they would be able to access your functions and macros etc to get the real time data. PyXLL is commonly used in finance for this type of application so it might just be what you're looking for...

This post shows how to get real time prices in Excel from BitMEX. I know you're using Binance but it might be interesting for you anyway :) https://towardsdatascience.com/live-streaming-crypto-prices-in-excel-aaa41628bc53