Case Study: Saving employee time by migrating error prone Excel spreadsheets to custom software

Before the financial crash in 2008, I was working with a commodity brokerage to develop a suite of internal compliance and automation software products for their back office. This company utilized a large number of spreadsheets in carrying out the trade settlement process. In this case, they were creating a separate spreadsheet for each trade. We’re talking hundreds of trades each month. These trades were structured so that if the market price moved above or below certain levels, the behavior of the trade would change. At month end, an employee would open each spreadsheet and enter each day’s market price and range for the month. The final settlement quantity and prices would be copied from the spreadsheet back to another trade management system. This was a time consuming process, taking approximately 80 hours per month. Due to the time constraints and lack of understanding of the complex math behind the spreadsheets, nobody in the office was routinely auditing the process.

In talking with the back office employees, we learned that this was one of the most dreaded tasks and it kept the entire team in the office for several evenings each month-end. They had developed several techniques for splitting up the task to optimize copy-pasting settlement data into each spreadsheet.

The solution my team built was an application which imported the active trades from the other system each day, listened to live market data, and posted periodic updates back to the other system. Whenever a critical event occurred, we could now alert customers in real time. Trades which had unusual behavior or could not be reconciled with the other system would be summarized in a report to senior back office personnel. The dreaded 80 hour a month process was converted into a task which only required the attention of a manager for a few hours to resolve the mismatched trades over the course of each month.

During the rollout of this system, we backtested it on previous data. To the surprise of everyone, we discovered six figures worth of over payments to customers. The copious copy-pasting and lack of auditing had let human error accumulate. We now know this won’t be an issue going forward and is an additional huge cost savings to the firm.

I’m very proud of this project. It involved learning trade pricing rules which really clicked with my inner physicist geek. Beyond the original time savings from the project, it was also a huge morale boost to the employees who no longer had to sacrifice their evenings to reconciling spreadsheets. Due to the success of our project, my team became the go-to consultants for several more automation projects within this office.