Making the (partial) leap from Excel to Python

20170311 Coins

Next week, I’m going to speak at TradeTech FX Asia on the following subject “Life after Excel, using open source Python to analyse FX markets and develop FX trading strategies”. I am not sure why I chose such a long title, but anyway, it’s done now, and I’ve got to think of something useful to say now on the subject, aside from bemoaning its length, and my inability to find a short snappy title. On the subject of Excel, I’ve been in the industry for a while now, and whilst many things have changed, the seemingly invisible umbilical cord between trading desks and Excel is something that has persisted over time. Why is this the case? Well, Excel is relatively intuitive to use. You can see all your data, you can see all the formulae and you plot your data with a few clicks. If your spreadsheets are small, then everything is as tasty as a burger (that idiom might not work so well, if you don’t like burgers however).


The difficulty is of course when your spreadsheets balloon in size. I’ve been there. Done that. Linking together multiple spreadsheets each sized over 100 MB. Waiting for the hourglass to fade, whilst it calculates, peppered by the random act of crashing, cast in the occasional shadow of a Windows blue screen of death. I’ve often written about the benefits of using Python before, but even I have to admit there’s a learning curve to getting acquainted with Python code, which is somewhat steeper than understanding the basics of Excel. So what can I say in my talk, which will persuade people to discard Excel and move to Python? I can’t think of any reason, so instead, I’ve come to the conclusion that it’s best to stick with Excel and start using Python at the same time! The idea is to take the best bits of Excel, the simplicity of the interface and combine it with Python for doing complicated back end computations. This way we can keep the simplicity of Excel and the fact that users are already familiarity with it, but at the same time dispense with the problems of doing heavy duty computations in Excel, and those infamous never ending calculations that plague big spreadsheets.


Furthermore, the great thing about using Python to do all the heavy duty computation is that we can make our code more reusable and we also have the choice of using multiple front ends for users. If we want to use Excel for users to access our Python code, we can use a great library like xlwings. xlwings makes it very easy to call Python in Excel. Using Excel as a front end also means we don’t have to spend time writing one in Python (admittedly there are many libraries for doing GUIs in Python). Alternatively, if we want to use a web based interface, we can use the Flask Python library to create a mini web server in Python to call our computations. If we do everything in Excel, it’s somewhat more tricky to exposure our calculation engine to the web. We can also  farm out our Python computation to another machine, say a powerful server on our network, or even on the cloud, rather than having to rely on the computational capacity of our desktop machine.


So there you have it, even if you are not ready to ditch Excel, but want to take advantage of computation in Python, there is a way! If you are attending TradeTech FX Asia in Singapore between 21-23 March let me know and hopefully, see you at my presentation.