Case Contingent Coupon CD Reporting Suite
The Front Office sales team was offering Certificates of Deposit (CDs) linked to a Single Reference Stock or a Basket of Reference Stocks. These CDs would pay a monthly coupon contingent on previously agreed-upon factors. Each month the team was responsible to produce a set of reports about the holdings and performance by CUSIP and distribute to their external clients. The detailed report contained several sections and data was scattered in various offline spreadsheets. When the time came to produce the reports it would take 5-7 business days to prepare a spreadsheet with hundreds of CUSIPs and a main Summary Report. Due to the large amount of charts the spreadsheet would sometimes crash and the team would have to split it and send separate reports.
The Front Office sales team expressed an interest in creating a fully-automated that could be run once a month and complete all reports at once. The team also mentioned that a competitor was producing reports that looked very professional and they looked to see if that could be incorporated.
The developer engaged a technology team to get access to the relevant data. He also reached out to an internal group responsible for creating brands and styles to get approved pictures to use in the templates. The application’s main function was to load all data and then populate predefined individual and summary templates created in Excel and multiply them as many times as necessary to create a deck of professional looking reports to be sent to clients. These reports were converted to PDF format. Each Excel template contained “smart” sections about CUSIP details, economic details, coupon payment history, stock performance, a chart placeholder, disclaimers and these sections were updated automatically as each report was being created.