System Design and Reconciliation

It's not exciting but...

Even those not working in Finance have probably heard about the intriguing events in a large investment bank and the HUGE losses that have occurred.

The interesting comment from an IT and architecture point of view is the following:

The trader responsible for the fraud had "in-depth knowledge of the control procedures resulting from this former employment in the middle-office", the bank said.

I'm sure the story and our perception of it will change as more facts emerge but we can be certain that the banks internal security and control systems failed.

Many security and control terms such as authentication, authorization and auditing are probably familiar what about reconciliation? Consider a simple stock-taking example. The number of items delivered to a warehouse minus the number of items sold should tell you how many are still there. If it's less, you may have a thief. ( If you've ever been involved in a stock-take you may have seen many missing items - they tend to be small and valuable.) Stock taking is a physical example of reconciliation i.e do all the numbers add up.

Should you be doing this in a system of your own? If you have developed an online store do you periodically make sure that the number of orders to your website equals the number of payments received and items sent? It's very common for a system designer to assume this will be the case, as the data normally flows from one component to the next, but consider what happens if someone sends messages to your delivery system directly. They bypass your ordering and payment systems so there is no matching order or payment. This is likely to be an insider that knows the system well and you can lose a lot of money as you send out goods without being paid. These kind of issues are increasingly likely as designers move away from monolithic systems to SOAs.

Apart from internal checks between your own systems you can reconcile with external ones. In securities trading, we usually get a list of trades from the markets and exchanges at the end of he day and reconcile against the banks internal list of trades. This should show if a trader has been placing trades they shouldn't have (or failing to place those they should).

It's important to make sure that the main system and the reconciliation system are separate otherwise an error or fraud in one could effect the other. (Reconciliation is often simple and can be done in something like a script).

Finally, you should remember that regression testing is actually a form of reconciliation between different versions of systems, so you may be doing some already!

About the author

Robert Annett Robert works in financial services and has spent many years creating and maintaining trading systems. He knows far more about low latency data systems and garbage collection than is good for anyone. He likes to think of himself as a pragmatist who loves technology but uses what's appropriate rather than what's cool.

When not pouring over data connections or tormenting interviewees with circular reference questions, Robert can be found locked in his shed with an impressive collection of woodworking tools.

E-mail : robert.annett at

Re: System Design and Reconciliation

A former employer delivered empty data feeds for several weeks before being told about the issue. A system was hastily concocted to deal with data quality - the type of reconciliation you're talking about.

This tactical solution grew into a mess but was still used daily nonetheless. I'm pleased to hear it's been replaced by a comprehensive MIS which includes similar reporting.

Some notion of reconciliation (or at least validity) was already included in the file format used for their feeds - it included what amounted to a checksum. However, it was not the feed that was in error, it was the data. So I'm definitely keen to see reconciliation based on sensible operating parameters rather than plain calculation. For example, a stock take might need to take into consideration spoiled items if it's to avoid generating too much noise. A shop that reports no business over the course of a week might present a perfectly valid ledger - but that may well be a sign that there's something more seriously wrong than a bit of cash missing: perhaps their IT systems reckon it's 1908 ;)

Add a comment Send a TrackBack