Excel on the trading floor

March 22, 2017

Excel is used heavily on all trading floors for all asset classes, buy and sell side, market making and prop trading, real money and leveraged. At SpreadServe we have first hand experience of working with traders building pricing, risk management and trading systems in Excel including...

  • A market making system for trading LIFFE Exchange Traded Options and Option Strategies on Interest Rate Futures. All option pricing and limit order management was handled in Excel using a multi sheet workbook making heavy use of VBA macros and XLL addins for analytics and market gateway connectivity.
  • A market making system for pricing, risk managing, quoting and trading interest rate based structured notes on Bloomberg and a proprietary web based channel. This was a huge spreadsheet that would lock up for 30 minutes after its main developer and user first hit F9 at the start of the trading day. Over the course of many months, with involvment from at least four developers, the spreadsheet was gradually replaced with C++ server components and GUIs.
  • Connecting a Swaption pricing spreadsheet to Bloomberg to enable quoting and RFQ trading.
  • PnL explain: this one's a frequent assignment for new juniors joining a desk.
  • Strips reconstitution: an XLL addin based sheet to identify arbitrage opportunities that may arrise from reconstituting stripped European government bonds.
  • Accrual calcs: another spreadsheet that is frequently developed on fixed income trading desks.

Traders turn to Excel time and time again because it's the fastest way for them to explore trading ideas and quickly whip up pricing and risk management systems. In larger trading organisations like banks this can lead to a war of attrition between trading desks and the IT departments supporting them. The IT departments provide a mix of in house developed systems and vendorware for pricing, risk management, execution, booking & STP, and aim to make spreadsheets redundant by providing a single well managed, scalable, audit compliant system. However IT departments can never achieve the quick change cycle time that traders demand, so traders continue resorting to spreadsheets. If the spreadsheets can tap the data flowing around the IT department provided system they become even more appealing to the traders! All the problems with spreadsheets on the trading floor continue to recur: multiple tweaked versions of popular spreadsheets in circulation, error prone manual operation including much rekeying of data, little or no testing, and a complete lack of audit trail for spreadsheet based processes.

Often the answer to these recurring spreadsheet problems is the one described above; a lengthy and expensive project to replace a critical spreadsheet with bespoke client and server components. Even when such a project delivers a working system we haven't escaped the downsides. Traders are likely to be frustrated by the lengthy cycle times to get a change into production, compared to a spreadsheet.

SpreadServe enables Excel industrialization, so trading desks can get the best of both worlds. They retain all the agility of trader developed Excel solutions. And they gain all the resilience, automation, scalability and audit compliance of an IT supported platform. Here's how it works with SpreadServe...

  • One or two key tech savvy traders keep developing pricing and risk management spreadsheets in Excel.
  • The spreadsheets get deployed frequently to SpreadServe by trading or IT.
  • The spreadsheets are subjected to scripted automated tests on a test environment deployment of SpreadServe, with the results recorded in a DB.
  • Once testing is passed the sheets are deployed to production. The whole desk can view the live, ticking pricing and risk sheets in their browsers.
  • Only permissioned users can change inputs via their browsers; for instance day count basis for a swap portfolio.
  • Every change can be persisted in a database: that includes loading a spreadsheet, every calculation and all input and outputs, and the identity of any user making permissioned edits.
  • Multiple SpreadServeEngines can apply the same spreadsheet to different books, currencies, tenors, contracts etc.