Code

Automation of FTP process - full implementation

Posted on April 5, 2016April 1, 2016 by Michele Maio

The previously-implemented semi-automated business-used prototype of the daily fund transfer price (FTP) for the mortgage domain was moved to production from business to IT departments of a large Dutch bank and became an IT system under FAM responsibility.

The resulting fully-automated application was built on a consistent technology stack, which includes .NET, Visual Studio, C#, SQL-Server, SSIS package, QRM. The tool is accompanied by the corresponding graphical user interface (GUI) which was developed in ASP.NET.

This project turned out to be very challenging, especially because of: i) the interactions among various bank departments, including IT, FAM, FTP Centre and Mortgage Group; ii) the large technology stack involved. In all these cases we were in the lead.

ETL for Daily Liquidity Monitoring

Posted on April 1, 2016 by Michele Maio

Daily monitoring of liquidity has become a crucial job inside any bank. We implemented the Extract-Load-Transform (ETL) operations of liquidity tools for different trading desks for the modelling and risk-reporting departments in a large Dutch bank, which allows the bank to run its liquidity-monitoring tools daily.

Liquidity input data come from various sources and all have different formats. Some are Excel files, while some others are comma-separated text files. Moreover, date conventions are not standard and depends on external factors, such as Excel settings. In addition, all the different pieces of information have to be adjusted before they can be used. Such adjustments include specific selection and join operations.

Our input tool has been developed in C#. We have created in-memory databases and used LINQ to perform DB operations. The code has been unit-tested. Log text files and Excel output files are created daily. The tool has been accompanied by a self-contained user manual explaining the business logic, configuration setting and command-line arguments, exceptions that may arise during the execution.

Square Law of Spreadsheets

Posted on February 12, 2012April 5, 2016 by Jorrit de Jong

Ella places two printouts on John’s desk and points at the total columns. “Why don’t these numbers match?” she asks. John looks for a second, hoping Ella has made a simple mistake. But she’s right, they should match. John spends the rest of the day crawling through dozens of spreadsheets, attempting to find the source of the discrepancy. Eventually the problem is found, but only after turning up five or six similar issues. The team collectively pauses for breath when they consider the number of decisions made on faulty data. It is another week before John has fixed the problems and can return to his original task.

This is a particularly familiar story to many growing financial companies, which often prompts the question “how did things get like this?”. To find the answer, we need to investigate a misunderstood dynamic in spreadsheet and tool development. Consider the following diagram:

Figure 1 – The Square Law of Spreadsheets Effects Diagram. Continue reading →

Reducing Operation Risk: The First Baby Step

Posted on January 27, 2011April 5, 2016 by Jorrit de Jong

Introduction

In our experience, each client is different, context is always king. However, because many financial institutions start off with end-user programmers, people who program for themselves, usually in Excel with VBA, the organisations tend to follow similar growth paths. In the end, financial institutions end up with a consolidated engineering team, what is often called the ‘Engineering Hypermarket’. The hypermarket is a place where the financial institution can ‘shop’ for:

A bug fix.
A new pricer.
A desk quant who can sit in the trading room assisting with features, analysis or coding.

The hypermarket is characterised by high levels of automation, discipline and rapidity; they do things quickly. Building a hypermarket is a bootstrapping process that starts with the smoke test. Continue reading →

Operational risks in code libraries

Posted on December 21, 2010September 25, 2015 by Jorrit de Jong

Introduction

One of the most popular tools in the finance industry is Microsoft Excel. This versatile tool has proven its worth many times over and is considered the standard for communicating numerical reports. Although Excel is a great tool, it doesn’t do everything out of the box and often needs extending. Extensions can be programmed in Visual Basic for Applications (VBA), e.g. Microsoft’s custom scripting language. VBA is considered to be an easy entry into programming, making it especially attractive for those who are not trained in software development.

There are, however, limitations to working with VBA. These limitations lead to risks that need to be managed. At the root of the problem is a lack of code centralisation, which leads to duplication. This problem quickly becomes unmanageable if there is no automated test facility. This leads to two common questions:

Am I working with the correct version of the spreadsheet?
Is the spreadsheet working correctly?

If these questions can’t be answered quickly, it’s a good indication that you have got a problem The problem might materialise the next time hedge computations are done, resulting in an inappropriate hedge. Do you really want to “hope” the correct spreadsheet is going to be used for the next report? In the remainder of this note it will be explained how centralised, tested code can greatly reduce operational risk while speeding up the development process. This means that we don’t have to hope our sheets are correct. We will know.

Continue reading →

Ugly Duckling

Dedicated to Financial Engineering