Saturday, 12 March 2016

Excelling with & without Excel

14:18 Posted by The Thalesians (@thalesians) 2 comments

Travel does many things. The cliche tells us that it broadens the mind. Holidays are the tonic for the whispering monotony of the routine. Yet a holiday is never purely a matter of joy. Within the idea of holiday are bound up the logistics of transportation. None of us book a holiday for the experience of being squeezed on to a flight for hours, at the mercy of delays and heavily processed airline food. However, being able to get to our destination, is a necessary part of any holiday to far away places. Of course we could forgo that long airline journey, but then we'd forgo those sandy beaches, beneath the sunny rays of heat, and instead settle for staying at home.

When trying to analyse markets, we are in a way faced with similar conundrum. We want to find some novel and interesting way to look at market data. However, very often this route is laced with an Excel spreadsheet! Sometimes this approach can work very well, in particular given Excel spreadsheets are the preferred way to share ideas with traders.. but as soon as you have just a bit more data there, a spreadsheet can become unbearably slow to recalculate. Furthermore, when the complexity of your analysis rises, a spreadsheet can become increasingly unstable and easy to break. 

In a sense, there are only certainties in life for a quant researcher: having to use Excel and moaning about having to use Excel. I have to say that I have at times, used Excel far more than perhaps I should have done. One reason is that it makes it easy to explore simple ideas. The difficulty is that very often simple ideas, become very complicated very quickly! Whilst it might save time to use Excel initially, it makes it more difficult to reuse analysis and cost you in the longer term. As for using big datasets like news data, Excel is basically unworkable. Hence, it limits the type of analysis you can do.

To try to get past the issues with Excel, I started writing PyThalesians, an open source Python financial library over a year ago. I wanted to streamline the bulk of my analysis. Rather than reinventing the wheel every single time each time I want to do a new sort of analysis, I've tried to write functions of commonly used financial analysis. These can easily be used again and again, with small modifications. I've now written functions to do basic backtesting of trading strategies, plotting, and market data downloading from many sources including Bloomberg. In particular, I've tried to focus on making the various components interchangeable. Want to change the data source you're using? Just a few lines of code need to be changed, not the the details in your trading algorithm. Want to change how you plot your results? Again, just change a keyword in your code. I've designed the code from the perspective of my experience as a quant researcher and someone who enjoys coding, as opposed to purely looking at it as an IT problem to be solved.

Along the way I've utilised many great open source libraries such as pandas (time series), NumPy (mathematical computations), matplotlib, bokeh and plotly (plotting). Since it's open source, I'm hoping over time, that if users find it useful, they can contribute their own improvements to the library too. We can also blend the two solutions using libraries such as xlwings, which allow us to call complicated calculations in Python and display the results in Excel. In this way, traders who love Excel can still create funky models in Python, but fall back in the familiarity of Excel.

So ok, we often moan about having to use Excel (including me!), but sometimes we can excel both with and without it, if we use a bit of Python (and hopefully PyThalesians!)

Like my writing? Have a look at my book Trading Thalesians - What the ancient world can teach us about trading today is on Palgrave Macmillan. You can order the book on Amazon. Drop me a message if you're interested in me writing something for you or creating a systematic trading strategy for you! Please also come to our regular finance talks in London, New York, Budapest, Prague, Frankfurt, Zurich & San Francisco - join our group for more details here (Thalesians calendar below)

14 Mar - San Francisco - Quant Fintech Mixer Event
15 Mar - New York - Thalesians/IAQF - Alex Lipton - Modern Monetary Circuit Theory
21 Mar - London - Robin Hanson - Robin Hanson, Economics when robots rule the Earth
20 Apr - London - Oskar Mencer - FRTB, RWA, XVA, Scenarios, MiFiD II, fast?
13 May - Budapest - Saeed Amen/Paul Bilokon - Thalesians workshop on algo trading at Global Derivatives
20 May - London - Martin Bridson - Knots and what not


  1. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.
    Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.

  2. There are some really great point of learning that you have showed in your blog. Learning is excelling, and I believed that this is one good example that you have posted in your blog. This is ideally the same concept to the SaaS Financial Model that also gives a lot of perspectives in learning the financial problems.