The JP Morgan Whale episode is a much referenced spreadsheet horror story. JPMC's own Task Force Report provides great detail. Baseline Scenario's Importance of Excel blog got a lot of attention on Hacker News and FT Alphaville produced a thoughtful commentary. In my Excel on the Trading Floor blog entry I explored how trader developed spreadsheets are the paradigm use case for SpreadServe: how replacement with bespoke in house code is expensive, time consuming and produces an inflexible result. And how important it is to retain the time to market and agility of spreadsheets while mitigating their operational issues by automating, integrating and serverizing. The Whale episode is a perfect illustration of those issues. The report gives us plenty of detail on the spreadsheet models in play on pages 104 to 108, and in Appendix A. SpreadServe enables the serverization and automation of unmodified calc heavy Excel spreadsheets via its Python, Rest and C++ APIs, and the injection and capture of inputs and outputs to and from database using its connectors. So let's drill into how SpreadServe would have improved things. Here are the issues identified in the report...
SpreadServe fix: SpreadServe runs unmodified calc heavy spreadsheets on a server as genuine headless server processes emulating Excel without any in process GUI. It's Python, C++ and Rest APIs and infrastructure connectors enable complete automation of input injection from databases and message buses, and results capture and broadcast to the same for logging, audit and broadcast purposes. All manual operation can be eliminated, removing a whole class of errors and saving hours of skilled staff time.
SpreadServe fix: SpreadServe's Python, C++ and Rest APIs and infrastructure connectors make it easy to build and operate extensive test suites. Back testing suites to pull inputs from market data history DBs, push them through the spreadsheets and capture results back to DBs are straightforward to build.
SpreadServe fix: the Gaussian Copula vs Uniform Rate toggle could have been made into an explicit input with SpreadServe, an the value of that input captured, instead of being allowed to default. SpreadServe's fingerprinting feature computes a hash from the aggregate of all the formulae in a spreadsheet. This can be captured in a DB, flagging when code changes are introduced.
Conclusion: a SpreadServe deployment would have automated the VaR model, as well as enabling extensive testing, tracking of code changes and explicit specification of key inputs.