Still Using Spreadsheets to Manage Risk or Create Algos? Time to Reconsider.

I’ve been building technology for trading firms for over a decade. From proprietary desks with in-house development and security so tight that you have to check your cellphones at the door, to shared office spaces where people openly collaborate about their strategies; I’ve seen it all. I’d say that one of the most common tools that I’ve seen on trading desks is Microsoft Excel.

Excel is popular because you can combine realtime data feeds from a multitude of sources with complicated mathematical formulas to do incredibly complex things incredibly quickly from a time-to-delivery standpoint. I’ve seen excel sheets used for everything from position analytics and reporting, to even realtime execution algorithms and complex order types. As a developer, I use Excel sheets to build proofs of concept constantly. Gatekeeper’s Formula Strategy, Spread Bandit, and even the Position Manager in Gatekeeper found their genesis in spreadsheets. It’s easy to use and anyone with a basic understanding of math and logic can become a programmer, well, almost.

Like just about anything that seems too good to be true, it is. All of the things that make Excel powerful can also make it dangerous. Being able to link cells together to form incredibly complex strings of logic without any kind of compile checks or design best practice enforcement means that everyone who “knows enough to be dangerous,” is. According to a recent Marketwatch Article, 88% of excel sheets contain errors! This is not surprising considering that a research survey of senior executives conducted by Vision Critical, only one fifth of companies have control policies for spreadsheets and even in companies that have spreadsheet policies, it is not always applied one third of the time!

One thing that separates traders from programmers, however, is that programmers have bug elimination built into their trade. They use strongly typed languages, compile time checks, best practices policies built into their development methodologies, and post-development quality assurance processes before their code ever makes it into production. Software engineers have had a lifetime of hard knocks and painful experiences stemming from bugs to know that you can’t put a prototype into production, and the same should apply to spreadsheets.

So as a trader, what can you do to protect yourself from spreadsheet errors?

The first thing to do is to find places where your spreadsheets are either error-prone, rely on third party data sources, or rely on bad or changing assumptions. In those places, you need to add checks for data validity and create alerts for when it’s not.

If your spreadsheets are used to enter orders into the market and/or display data that might cause a trading decision, it’s time to contract a developer to build you a custom tool, or evaluate off-the-shelf solutions. I’ve seen position greeks be completely wrong because someone didn’t update an expiration date after a contract roll or realtime price valuations become slightly off because interest rates changed and weren’t updated (okay that hasn’t happened recently, but you get the idea).

If hiring a software company sounds too expensive, remember that it’s cheaper than trading errors. Just ask JP Morgan who lost $2 billion due to spreadsheet errors! At the very least, give this article a read about best practices, or check out the European Spreadsheet Risks Interest Groups to fully understand the risks.