Over the past few weeks our routines have changed somewhat. I’ve made fresh pasta, which resembled something quite unlike any other pasta I’ve tasted. Entirely coincidentally, I’ve discovered why I should not make fresh pasta, and there’s a reason why folks buy ready made pasta. Aside from delving into the world of pseudo-pasta creation, like many of you reading, I’ve been working at home. I’ve also spent time thinking, in part imagining how things will be, once we’re back to “normal”.
Of course, “normal” doesn’t necessarily mean that things won’t be different. Just thinking about my area of work analysing financial markets, there’s many things that can be improved. In particular, we all have those tasks, which are repetitive in markets. These are often necessary but can soak up a lot of time. I dread to think how much time I’ve spent in the past updating Excel spreadsheets. We can learn Python to help automate a lot of these processes. As a bit of a plug here, if you are interested in learning Python, I’ve developed a Python for finance workshop, which I can teach at your firm (via video conference given the current situation) and I also offer consulting services to help you in automating your processes with Python. Whilst Python can be used to do a lot of cool analysis (eg. natural language processing), in practice, the “low hanging” fruit is automating all those manual spreadsheet tasks. Below I’ll go through a few tasks which can be automated using Python.
First, work out what you can automate with Python
Before you even do any coding, you need to understand what regular processes you have. How long do they take to do? What do they involve? Why is the task done? If it’s a task which is done once a year, maybe it won’t be high on the list to automate. However, if the task involves a 30 minute Excel update every day, then perhaps we should add it to a list of tasks to be automated with Python. It can often help to get an independent viewpoint about your processes and make recommendations on how you can go about the automation, and Cuemacro can also help here.
Move data storage from random spreadsheets to a database
Whatever analysis you do you’ll need to have data! Rather than being randomly stored in spreadsheets within a team, try storing it in a centralized way so everyone in your team can access it and it can be properly maintained. A SQL database might be sufficient, or very high frequency data, perhaps Arctic/MongoDB or kdb+/q. Python can then hook into your databases.
Creating market charts in Python
Most times I use Excel it’s to create fairly simple charts eg. AUD/USD vs. S&P 500 etc. It’s not rocket science, but it does take time, in particular trying to format. If there are lots of charts you do regularly, it can end up wasting a lot time, to repeatedly update such charts. We can instead automate the creation of these charts in Python. In my finmarketpy Python library, I’ve created the QuickChart class, which let’s you create such charts with only a few lines of code (example here), and it can be used to fetch data from Bloomberg, Quandl and many other market data sources.
TCA (transaction cost analysis) in Python
In years gone past, I used to do transaction cost analysis using Excel. I looked at the theoretical performance of a trading strategy vs. where traders had manually executed the same trades. I provided them with feedback on this, which was pretty useful for improving execution performance longer term. It was pretty cumbersome using Excel. TCA is something that can be done in Python, and I’ve recently open sourced my TCA Python library tcapy to do exactly this. Download tcapy for free from GitHub here – and it even has an Excel add in to use it via xlwings.
Creating PDF reports and web dashboards
A lot of analysis you do in financial markets, you are likely to want to share. Usually this involved creating PDF reports. There might be several stages, an Excel spreadsheet, and then a Word document and finally a conversion to PDF. With Python we can do this all in one go, automate the creation of the analysis (the Excel bit) and we can do the Word/PDF bit too! We can automate the creation of PDF reports to display our analysis, using Python tools like Jinja2 (to generate HTML pages) and WeasyPrint (to convert HTML to PDF). I’ve got an example of automated PDF here, which I did in Python in my tcapy library.
We can also create nice interactive web dashboard to generate such charts using Dash and Plotly, and again this is something I’ve leveraged in tcapy. If you want to stick to Excel, and still get the benefits of Python, xlwings is a great tool that I’ve used. Basically, use Excel as your GUI and all the number crunching is done in Python via xlwings.
Checking “rules of thumb” discretionary traders use
Whilst systematic traders have specific strategies they follow, it can be the case that very often discretionary traders have “rules of thumb”, which they use to trade. If we can define these rules of thumb, it can make sense to backtest them, to see if they work in practice (just as a systematic trader would do). After all, wouldn’t you want to know if an idea you are following has made money historically? You can backtest trading strategies with Excel. However, using Python can let you make the whole process of backtesting a lot slicker and it can handle intraday data better. I’ve developed an open source finmarketpy library which has lots of “boilerplate” code, leaving users to define the signal.
After automation, what to do next with Python?
Once you’ve automated all those pesky manual Excel spreadsheets, it’s also worth thinking about all the additional things you can do with Python, which can be tricky with Excel. For one, Python has a lot of tools for doing machine learning like TensorFlow, PyTorch and scikit-learn and it can be a good environment to work with alternative data more broadly. If for example, you want to use text based datasets, like machine readable news or analyst reports, Python is ideal for doing NLP (natural language processing), with many libraries like NLTK, spaCy, and Transformers (which has many newer pre-built NLP models like BERT).
Conclusion
It is often necessary to do a lot of regular processes when working in financial markets, which are time consuming but also repetitive. I’ve only written about a small selection of these tasks here. With Python we can automate many of these tasks, so we can concentrate on those types of analysis where we add the most value. Learning Python is also a great way to future proof you skillset. Excel is a key part of financial markets, but I suspect in the next decade, Python will end up being another must have skill for anyone working on a trading floor.