Systems and process

Money market fund reconciliation, rebuilt

A daily control check that took about an hour now takes six minutes, with no loss of control coverage.

Two clock faces on a warm dark floor. In front, a crisp white clock reads “6 minutes”, with a short ochre arc from twelve o’clock marking six minutes. Behind it, dim and out of focus, a clock reads “60 minutes”, its ring filled all the way round.

At a glance

Role
Custody Operations Specialist, U.S. Bank Global Fund Services
Team
Custody operations in Dublin
Tools
Excel, VBA
Timeline
May – October 2025
Status
Handed over to the team, with training and a handover process

What it shows

  • Root cause analysis
  • Reverse-engineering a legacy macro
  • Parallel testing, with no loss of control coverage
  • Bringing many people along on a high-volume change

Discover

Every day, the custody team reconciled its money market fund trades: a control check that compares the bank’s internal records against the market’s settlement records, so any difference is caught. It ran on a macro that took about an hour, every day, and several colleagues had already tried to speed it up, without success.

How it came together

6 steps · 1 milestone

  • Step
  • Milestone
  1. 1Discover1 step
  2. 2Design2 steps
  3. 3Deliver3 steps · 1 milestone

  1. 1Discover

    1. Step 01

      Finding the root cause

      The macro ran inside the trade system and reached out to Excel: slower processing and older methods.

  2. 2Design

    1. Step 02

      Reverse-engineering the macro

      Every step rebuilt to run from Excel and reach into the trade system.

    2. Step 03

      A prototype

      The new version, ready to test against the old one.

  3. 3Deliver

    1. Step 04

      Months of parallel testing

      Old and new side by side, with every step towards full automation checked by hand.

    2. Step 05

      Reviewed by many people

      It touched far more trades than settlements, so it had a lot of oversight and input.

    3. Step 06

      Training and handover

      I trained the team, and wrote a handover and training process when I moved role.

    4. Milestone 07

      Gold Shield Award

      For the rebuilt reconciliation.

Define

The root cause

The problem wasn’t the logic: it was where the macro lived. It had been built inside the bank’s trade system and reached out to Excel for its work, so it ran on the trade system’s slower processing, using older methods.

Compared Before After
Where the macro runs Inside the trade system, reaching out to Excel In Excel, reaching into the trade system
Processing The trade system’s, which was slower Excel’s, on the analyst’s computer
Time About an hour, every day Six minutes
Controls Every check the reconciliation needs The same checks: no loss of coverage

Design

Turning the macro around

Compared with the settlements workbook, the solution was simple. I reverse-engineered the old macro, step by step, to understand everything it did. Then I rebuilt it to run from Excel and reach into the trade system, instead of running inside the trade system and reaching out to Excel.

Report in

  • Today’s money market fundsA report of every money market fund trade for the day

Excel

  • The rebuilt macroRuns the reconciliation, step by step, as before

Records compared

  • The bank’s trade systemThe internal record of each trade
  • The marketWhat actually settled

Reports out

  • Three result reportsGenerated, filled in and saved to their folders automatically

How it flows

  1. The day starts with a report of every money market fund trade.
  2. The macro runs in Excel, so the work happens on the computer’s own processing.
  3. It reaches into the trade system for the bank’s internal record of each trade, and compares it against the market’s settlement records, with the same checks as the old macro, so no control is lost.
  4. The results are split into three reports, which are generated, filled in and saved into their folders automatically.

Deliver

The fix was simple, but the reconciliation touches far more trades than settlements, so the change had a lot of oversight and input from many people before it replaced the old macro. That scrutiny is what made it a good piece of work. I built a prototype, then tested it in parallel with the old version for months, to prove it kept every control. The change was made a step at a time: each new feature, and each step towards full automation, was checked by hand to confirm everything was accurate before the next. Once it was in use, I trained the team on it, and when I moved role I wrote a detailed handover and training process so it kept running without me.

Outcome

  • 60 → 6Minutes for the daily reconciliation
  • 10×Faster, every day

The reconciliation now takes six minutes instead of about an hour, with no loss of control coverage, where earlier attempts hadn’t worked. It won a Gold Shield Award, one of U.S. Bank’s highest internal awards, with recognition from senior leaders.

What’s next?

I’ve since left U.S. Bank. If I were carrying the work on, these would be my next steps:

  • Look for the same trap elsewhere. Any other process built inside the trade system could be checked for the same problem, and turned around the same way.
  • Write the pattern down. A short guide for the team on building automation that runs from Excel and reaches into the trade system, so new tools start the right way round.