|
Schroders analysts report that this task, which used to take an employee a month to complete manually, can now be done by an employee in one day.
When Schroder Investment Management North America Inc. acquired a smaller company, STW Fixed Income Management, they inherited multiple Excel spreadsheets and Origin Project files used to track client investments. Each client had a separate spreadsheet into which data was input on a quarterly basis via an Excel macro. Spreadsheet data were then imported into Origin. There, the Line plot and Fill Area plot graph templates shown in Figure 1 were used to manually create graphs depicting the Account Yield and Allocation over Time.
Two financial analysts at Schroders were charged with the task of streamlining this time-consuming process. They contacted the Origin Support team for assistance. Schroders’ goal was to automate the process so that Origin graphs were effectively updated in real time, as data were entered into the Excel spreadsheets. Since the graphs were intended to depict change with time, automating the graphing component required plotting quarterly updates against a moving time window. The Origin Support team furnished Schroders’ analysts with an Excel macro that could handle this. The technical team at Schroders then further customized the macro with the help of the Origin Support team.
At this point, Schroders encountered another obstacle; there was a separate Origin project file for each client’s graphs. To avoid having to run the macro repeatedly to update each client’s data, the team at Schroders decided to set up an automation server using COM. Once again the Origin Support team provided the sample COM script that Schroders then reworked. The script was designed to open each Origin Project file, extract a graph, update the graph with new data, and then resave the graph with an updated time scale.
Numerous other issues were worked out by collaborating with the Origin Support team. For example there were text and arrow objects on the graphs that indicated important events. Initially, the text labels had to be manually shifted along the graph as data were added. The solution was provided by an Origin Support Engineer who wrote a LabTalk script that calculated the amount the data plot would move along the x axis with each update. The script added this quantity to the x coordinate of each text label so that the text labels “tracked” their proper positions on the plot without manual intervention.
| |
| Organization Schroder Investment Management North America Inc. |
| Problem Automating quarterly report generation to minimize the time and manual labor required to input data into Origin, add it to each clients' existing graphs and adjust the graph size, labels and structure accordingly.
|
| Solution Created Excel macro that inputs client data from spreadsheets into Origin, where the data is added to existing graphs and then output as GIF images in a centralized location. COM scripts were also used to run the macro over each client's opj file.
| | Key Features
|
In another instance, the date format needed to be changed to comply with the format used in a financial report. Origin’s new Axis Dialog, updated for version 9.1, was handy for this as it allowed for greater customization and included support for more data formats.
Since the COM script only has to run once, this drastically reduces the time needed for the graph-update process. The script also automatically exports the graphs as GIF images to a common location where they are output to PowerPoint presentations. With each fiscal quarter, a financial report and a PowerPoint presentation are generated for each client. This process is now being used to produce up to 130 client presentations and the only manual intervention required is the starting of the script and checking to ensure that the output is correct.
The seamless collaboration between the Origin Support team and Schroders’ technical team allowed for the creation of the needed scripts and macros within three months. The process was put into practice for the first time in April 2014. Schroders analysts report that this task, which used to take an employee a month to complete manually, can now be done by an employee in one day.
|