CTRM Center for CTRM Software and ETRM Software
Blog News Events Publications Directory Community Media ETTCenter

Spreadsheets Revisited

Although it is now a little old, the work that we did back in 2015 on spreadsheets is always worth a revisit and still relevant. With so many new people coming into the business, they may be unaware of some of the historical issues caused by spreadsheet use in the industry. The research and report was sponsored by Gen10. What the research found, I think, would be as valid today as it was back then and that was that the often predicted demise of the Excel spreadsheet in commodity trading has never occurred. At the time, we looked at the reasons why spreadsheets should be used only with caution. The survey asked for examples of the sorts of issues that had arisen with the use of spreadsheets at trading firms and the result was a real horror show of issues like these,

/ A $2 million trading error was traced to the replacement of a cell formula with a fixed value,

/ Numbers were transposed on data entry into a master spreadsheet and downstream spreadsheets had no visibility of that error in the master spreadsheet,

AdvertisingQUOR
AdvertisingComTech book

/ An added row or column didn’t get included in the calculations,

/ Lack of audit controls including time stamp and approvals for the trade ticket,

/ Different Versions of the same Spreadsheet being used simultaneously and no one knew until too late,

/ A bug in VBA code, written internally, led to huge losses,

/ The spreadsheet needed to be tweaked every month-end but when the owner of the spreadsheet was on holiday, reporting had to be suspended – major headache,

/ Incorrect positions as a result of missing certain cells on the spreadsheet,

/ An attempt to sort a column resulted in wrong values and no one noticed. The downstream spreadsheets were all impacted too,

/ Copy and paste errors where formulas got copied rather then numerical values and all the results were wrong,

/ Incorrect position data as some cells were omitted in the final calculation,

/ Very common to find errors in trader spreadsheets being used to determine P&L. Traders will almost always put in some level of “Mark to Intent” numbers in the sheets to support larger bonuses than would be granted using Risk System numbers,

/ Spreadsheet pulling in incomplete data resulting in risk mismanagement errors.

We concluded that spreadsheets served a purpose and would likely have a continued place in trading forms but that they needed to be used with caution. The thing is that Excel is so easy to use, so powerful in presentation and manipulation of data and so widely used that there is no way it won’t be used. The fact that it is also prone to a wide array of serious errors like those above means that it poses a risk that needs to be managed.

Almost all CTRM solutions recognize the role of Excel as a convenient tool in trading firms. Almost all provide automated tools to suck in data from spreadsheets and to push data out to Excel for further analysis. Indeed, as firms have sought to do more data analysis, this feature has been supplemented with thee use of other BI tools. Yet, Excel often proves to have the better combination of functionality, ease of use and presentation capabilities than many of the commonly used BI tools and requires little or no training as everyone is familiar to some degree with it.

Discussing that 2015 report yesterday with Richard Williamson, CEO of Gen10, he remarked that spreadsheets were not the tool for transactional work but it could be a very good BI tool.  He believes that transactional data needs to be entered into a proper solution like a CTRM/CM that has the workflow, processes and controls, but he sees Excel performing as a type of BI tool. We would concur. There is a role for Excel but its use poses a risk and that risk, like all others, needs to be monitored and managed. Using Excel for reports, data analysis and display or even as a source of raw data that can be ‘sucked’ into a CTRM automatically, is likely a low risk approach. Using a spreadsheets to manage the business may prove to be folly. In the former case, controls must still be used even if they are manual processes and controls to avoid the kinds of costly errors our 2015 research found.

What are your thoughts on spreadsheets? We would like to hear from you.

Keep in touch and sign up to our Newsletter