Macro Cash Flow Hedging application for ABN AMRO

History

My first self employed assignment, July 2004, was creating a replacement tool for a collection of MS Excel spreadsheets which had grown unmanageable.

A company (called QRM) had spent 1.5 years creating an application to perform 'macro cash flow hedging' (according to the IAS 39 standard) on the swap portfolio of ABN AMRO (WCS, Amsterdam) - but it still wasn't ready and WCS could not wait anymore. So my job was to make the collection of spreadsheets into a manageable and fault-less system until QRM was ready.

This diverged into 8 months of programming an application in Microsoft Access, and then a year of incedental refinings (while I did other stuff for WCS at the same time). The end result was a rock solid application containing more functionality than QRM's, happy users, the decision to scrap QRM's application (which was still in test phase) altogether, and approval by external auditors to keep using my application with only minor comments.

Yes, I'm proud of this baby ;-)
There is something nice about taking a programming platform in which you can quickly hack together absolutely ugly solutions (like MS Access), and then making a nice application in it which reliably hedges swaps with notional amounts of about a hundred billion each month, divided over nine currencies, using two different methods :-)

What does it do?

'Macro cashflow hedging' is a specific financial process which is totally useless to anyone who is not working with financial instruments - or more specific, swaps. (And I'm not working with them daily, so I won't get all the terms fully correct.)

The aim of this process is to minimise effects of the swaps' changing market value on the P&L which needs to be reported periodically. Ideally, the effect on P&L should always be 0.
(This can be achieved if you have enough cash flows in stock of a certain type which 'match' your swaps. If you have cash flows of slightly different types, this might be (partly) possible too, but all kind of accounting rules come into play.)

The application takes as input:

• the swaps which need to be hedged if possible
• the cash flows (some of) which can be used for the hedging relationships
• information needed to establish hedging relationships with 'different type' cash flows
• its full history of P&L calculations from previous periods

and then calculates the hedging relationships to minimise P&L. It also amortises P&L over longer periods of time where that is allowed by IAS 39, so as to minimise 'jumps'.

From a programmer's perspective it's the same always: you take several inputs, do some calculations on the combination of those inputs, and report the results to the end user.
The complications here are:

• the history of earlier periods affect the reported figures in the current period
• it needs to be a closed system; it must be proven as clearly as possible that input and other intermediate data is not changed by the user
• still, the user must be able to alter data in specific cases - which must then be clearly marked, for auditing purposes
• the application enables the user to also make changes in past periods which have already been reported. These changes are always noticed, the cumulative effect on P&L in already reported periods is calculated and reported in the next period.
• past reported figures (with and without later changes) are always available

the main screen of the application, with input/import functions at the top, controlled edit possibilities for users on the right, and report functions in the larger area.

Reports are generated in Excel. Most of them are based on simple tables of data. There is one report that needed to be 'hand crafted' to present the financial figures in 'T-accounts' format, and have details on the calculations which looked like the original Excel sheets - so that all figures can be traced back to their detailed calculations (at least by an experienced user).

T-accounts (balance + P&L) for May 2004, based on a set of test data. (The image links to an Excel file including details on the calculated figures.)

Code specifics

P&L calculations are not purely programmed in VB; they are all expressed as SQL commands (using temporary tables where necessary). The SQL statements themselves are mostly defined in VB code (as opposed to standard MS Access queries), though - since they often have different arguments that slightly change the queries.

The routine which generates the 'T-accounts sheet' in Excel is general. All T-accounts with names, plus all 'categories' with their bindings to exactly 2 T-accounts (one on the left side and one on the right) are defined in a table structure inside the application. The code building the page takes as input a recordset with financial figures + categories, and then dynamically builds all T-accounts and decides on their size, and displays only the relevant categories in the legend (the green block).

I believe that all mechanisms I ever used to make a solid, maintainable and portable MS Access application -assuming one is a good VB(A) programmer and knows the code-, have been used here.

• The application uses MS Access' standard security mechanism. You need to log in to use it; three groups are defined with different rights to viewing / changing data.
• The application itself uses data in three distinct locations:
  • non-temporary data (all imported data, results of calculations which should be kept, and user changes) are stored in a 'data mdb', which can reside on a network drive
  • temporary tables are stored in an mdb file which is created in 'the user temporary directory' upon application startup
  • definitions for edit screens, file import fields, some queries and the data model of the 'temporary mdb' are in the application (mde file) itself

  This way (because all volatile data is stored outside the application file) the application can actually reside on a network drive and started concurrently by different users. (The application keeps separate application settings for each user.) This means that there is no hassle upon upgrades (like 'all users need to copy the new application to their local drive'). Also, neither the application nor the data MDB grows in size at the speed that mdb files often do.

• No ODBC table links are present the application (except for one specific case), and no queries. The application uses direct (DAO) connections to the data and temporary mdb files. (ODBC table links to the 'data mdb' are created inside the 'temporary mdb', to facilitate joins and updates from temporary data into the general data store.)
• The 'data connection code' is general so that the application is almost ready to use ADODB instead of DAO / a data storage on e.g. Oracle instead of a 'data mdb' on a network drive. (The one reason why this is not immediately possible, is that some SQL commands use Jet/VBA specific functions.)
• The application started out on MS Access 97 and was converted to MS Access XP, but could still run on 97 (except that the temporary data would then be stored inside the application - which then needs to reside on a local drive instead of in a separate temp mdb). Handy things which do not work in Access 97 VBA (such as Enums) are defined inside '#If' statements so the code still compiles and works on Access 97. The general 'SQL Builder code' (see SQLConvert) takes care that in Access 97, subqueries are created and used as separate querydefs (when they would be one SQL statement when executed in Access XP).
• For common Excel reports, a class is used which makes MS Excel invisible upon initialisation and visible again on termination of the class, for speed reasons and to prevent users from inadvertantly meddling with a report while it's being generated. Some other Excel properties are set too, to optimise speed of report generation. (NB: newer versions of the classes have in the meantime been optimised to be much faster :-) )
• The application uses general forms/code for most data edit/list screens, so these edit screens are defined inside tables and dynamically built. Custom behaviour of edit screens is split out in 'hooks' defined in custom modules. (I had this code already and just needed to refine it in places.)

Standard look of edit screens (list and details) - simple but effective. Note the application is locking the edit screen in this specific case, since the record has been used for reporting already.

N.B.: No source code is available for download for this application. If you want to view code that implements some of the above, have a look at the Workflow application.

Copyright issues

The copyright on the Macro Cash Flow Hedging application resides with ABN AMRO. Some details in the implementation of the 'hedge accounting rules' are probably specific to ABN AMRO's situation, so noone would be able to use the application 'as is' anyway. I put the application up for download to demonstrate the general workings of the code, as an 'mde' file which cannot be changed. I will NOT give out any source code related to the business logic.

Ofcourse the general VB(A) code libraries (which I already had before starting work at ABN AMRO, and built upon/refined) are still free for me to do with as I please.

(And you are still free to hire me for what is inside my head - i.e. my expertise on implementing IAS39 rules :-) )