Brokerage Payables Remediation Engagement

 Ayrat Safine is a Business Analyst and Project Manager with particular skills in quantitative investigation.  Ayrat specializes in devising technology solutions for tackling his clients’ most critical data challenges.

Ayrat Safine is a Business Analyst and Project Manager with particular skills in quantitative investigation.  Ayrat specializes in devising technology solutions for tackling his clients’ most critical data challenges.

Exhibit A:  F&O Trade Flow and Fee Structure

A top investment banking client recently called on Monticello to help with a process fit for automation.  The engagement called for building a suite of tactical tools to address a particularly thorny reconciliation dilemma.  Acting as the clearing broker in a futures and options give-up scenario, the bank would take on the responsibility of managing fee disbursements between involved parties in a trade (Exhibit A). Over the past seven years, our client would repeat this process for millions of transactions, utilizing GMI-an industry standard system for futures and options back-office processing.


Exhibit B:  Several Factors at Play

For each trade, the bank would receive an invoice from the executing broker-one who had given up a trade to our client.  This would be reconciled to the theoretical fee amount in GMI before payment was sent.  When the bank couldn’t agree with the executing broker on the proper fee amount for the transaction, breaks were created in the process and brokers did not receive payments.  This dilemma spawned from several concurrent factors.

Factor 1: Voice vs. DMA
One of the primary root causes was a fault in the GMI platform itself.  This legacy back-office processing system used by many top broker dealers-including our client-lacks a rudimentary indicator to distinguish between trades booked via voice and those booked through direct market access (DMA).  As a result, there were many instances where the clearing broker’s staff were unable to identify voice trades from DMA trades.  Because voice and DMA trades carry different fees, this led to frequent disagreements in the fee invoicing process between the clearing and executing brokers.

Factor 2: An Industry Problem
Not all broker-dealers use GMI to track their trading activity.  In fact, there are three platforms that have dominant market share in the industry for back-office futures and options processing.  Not surprisingly, these systems lack consistent data standards.  The various nuances at play when comparing these trade repositories require complex mapping routines to translate the data from one system prior to matching it to trades in their native platform (GMI in the case of our client).

Factor 3: Breaks
Upon reconciling invoices with data in GMI, analysts often encountered a range of discrepancies.  Duplicate invoices were frequently received from executing brokers.  Time and again, fees were found grouped together by various categories, and a vast majority of fees were prone to breaks for reasons ranging from precision to timing issues.

Factor 4: A Growing Problem
Finally, this reconciliation process for the brokerage department staff at our client was a painstakingly manual one.  Every day, the staff had to choose which breaks to tackle but had limited ability to prioritize based on hard data.  Without proper data analytics, this often meant reconciling either the oldest items first or prioritizing those brokers who were demanding to be paid on an immediate basis.  As one can imagine, this led to an inefficient state where quick wins and easy matches went unexploited-lost in a sea of ever-growing unpaid invoices.


MCG Auto Recon

For a typical invoice submitted by a broker-dealer, MCG’s tactical recon tool matched 45% of the data outright.  These deals would skip the manual workflow process and be scheduled for automatic payment.  An additional 25% of the deals were matched and categorized under known break types.  These would be prioritized for analysis prior to payment.

The Monticello Solution

To address this problem, the Monticello team implemented a tactical reconciliation tool capable of matching aged transactions en masse.  The technology underlying this tool included a MS SQL Server database capable of processing millions of records loaded daily, along with a user-friendly front-end interface built in MS Access.  The Microsoft tech stack was chosen for this solution because it was supported within our client’s IT organization, and also due to the omnipresent nature of MS Access on the business user’s desktop-facilitating quick and easy deployment to new users.

While the use of a unique identifier (UID) is the preferred method of running a reconciliation of this magnitude, no tracer fields could be utilized for the data in question.  In the absence of a UID, Monticello’s team of analysts performed a judicious data scrubbing exercise on the incoming broker invoices.  Subsequent to the scrubbing, the transaction data was run through a complex hierarchical matching sequence to automatically pair invoiced trades with their respective counterparts in the client’s in-house GMI trade repository.  

In less time than it would take an analyst to manually locate and reconcile a broken fee using the GMI interface, the MCG Auto Recon tool processes millions of transactions and produces an output set of business intelligence (BI) reports to direct analysts toward problem areas in need of attention.  In addition, the tool suite segregates and prepares perfectly matched brokerage fees for prompt payment, thus further alleviating the analysts’ workload and satisfying the executing brokers waiting for their funds.

This fully automated solution allowed our client to make significant strides toward reducing aged payable balances owed to their peer broker-dealers, while also minimizing the time and resources required to reconcile F&O brokerage fees.  Not surprisingly, the MCG tactical recon tool has become an integral component of our client’s back-office futures and options processing, and continues to produce tangible benefits long after our engagement has come to a close.

Did You Know?

Data scrubbing is the process of cleansing data by fixing errors, removing duplicates, and correcting formatting.  A critical step in an analysis effort, data scrubbing can either be done manually using tools like Excel or systematically using special programs and algorithms like SSIS.

Did you Know?

A unique identifier (UID) is a numeric or alphanumeric string assigned to a single entity or trade within a system.  Leveraging unique identifiers allows us to reconcile trades from multiple sources systematically.  Consequently, in their absence, a more cumbersome process is required.