If you have ever worked for financial services to develop systems for their front office, you know that traders love MS Excel. There are a lot of reasons to use Excel as the viewer; manipulating and visualizing data in a grid fashion are both sound and logical. Still, computations that require more than 1 computer could handle are better to offload to a grid computing platform and that's what we have done for one of the largest banks in China.
Today, I'm not going to talk about the grid computing platform we've modernized for the bank, instead I will focus on version control for MS Excel. You might think that is easy because you can use Sharepoint. You are absolutely right and that's what we have selected as a technology to manage different version of the Excel. However, this is only part of the story. There is another part of the story which is constantly being overlooked and that is to manage the different version of "the data" in the Excel.
Version control of data in the Excel is a problem that even Microsoft leaves the door opened for others to contribute because it requires the domain knowledge of the field (in this case, the financial services) in order to understand what the data is about to version control. In the financial services, the quants build their pricing models into the Excel spreadsheet. The pricing models are mathematical models that required model parameters and some predefined inputs in order to calculate the outputs for the pricing models. Once the quants have validated the models, the Excel spreadsheet is hands-off to the traders and the sales for perform their daily operations. Problem is that when the quants update the pricing models with new parameters and new inputs, all of a sudden the traders and the sales are facing a difficult problem; all the current deals that are made in the past using the old models are needed to port to the new version of the Excel spreadsheet. This porting activity include the inputs, the model parameters and the outputs of the model, PLUS all the information about the deal. They need a flexible way such that they can use the new Excel spreadsheet with the data that is in the old version of the Excel. The solution we have proposed and implemented uses RESTFul services to facilitate the version control and migration of data in the Excel spreadsheet.
RESTful services are great for system integration. As discussed in this presentation, it allows each system to upgrade at its own pace without concerning the other depending and dependent systems as long as the upgraded system maintains the older versions of the services. In our case, the data in the Excel is stored in GigaSpaces which provides real-time risk hedging functionalities based on the market changes. We developed a XML schema for the data and represented the data in XML format so that all client applications know exactly what is in the XML and how to use it (we build a library that uses XPath to get the data we need from the XML and populate the Excel spreadsheet on-the-fly) . If the new version of the Excel spreadsheet does not required new inputs and model parameters, the traders and the sales can benefit from it immediately. If the new version of the Excel spreadsheet requires new inputs and new parameters, the quants will need to add new entries in the XML to describe the new data. When the traders and the sales open the new spreadsheet, the data in the old version of the Excel will be filled-in to the new version of the Excel spreadsheet. After the traders and the sales have filled-in the new data and submit the new version of the data back to the data grid, everything is up and running again. This is just one of the use cases for the RESTful Excel but you can imagine that there are other use cases that can make good use of this technology.
Bear in mind that system integration is crucial in financial services and therefore, having a good technology that can facilitate system integration will allow the banks to adopt new technologies much faster which might improve their system reliability and performance. It has a direct impact to everyone's life (assuming you also put your money in a bank).
