MCG Tactical Tools - DTCC Data Quality Metrics Tool

Dodd-Frank Title VII, which includes the reporting provision covering OTC swaps, has given the CFTC enhanced tools for policing potential market manipulation activities by unscrupulous swap market participants.  One of those tools is the DTCC’s Global Trade Repository (GTR), which provides regulators with access to trade data from many of the largest swap dealers, major swap participants, and end users actively trading swaps in the market. 

To aid in comparing, benchmarking, and in some cases shaming the dealer community into improving the quality of their reported data, the DTCC publishes monthly metrics showing the relative data quality among the top dealers across the forty mandatorily required data fields such as trade date, notional, and the counterparty legal entity identifier (LEI).  Month-over-month trending is used to demonstrate which dealers are making a concerted effort to fix bugs in their reporting infrastructure in order to improve the quality of their data. 

Monticello Consulting was called upon by our client—a top swap dealer—to implement a tool to reconstruct the DTCC’s monthly metrics, but to do so on a daily basis.  In order to tackle this challenge, MCG designed a system to leverage the enhanced position reports published daily by the DTCC and store this large volume of data in a custom designed database.  Next, we computed the metrics using complex formulae shared with our development team working closely with the subject matter experts at the DTCC. 

The end result was a streamlined and user-friendly toolset that is leveraged by our client to drill in and examine the quality of their daily swaps trade reporting across all required fields.  If a particular data field on a trade is being reported incorrectly, an analyst using the toolset can quickly raise a defect ticket with little time lag and have IT begin to investigate and resolve the issue intramonth.  The toolset provides the organization with the ability to not only identify data reporting anomalies but also to allocate and assign its staff to remediate data issues on a more timely basis than previously possible.  In a short period of time, this toolset can aid a swap dealer to significantly improve the quality of their reported trade data and streamline their efforts to fix bugs and scrub incorrectly reported data in the GTR.

DTCC Position Report Query Tool

Having seamlessly engineered a daily metrics platform to match the one run monthly by the DTCC, MCG turned its sights on another intriguing opportunity.  Together with the client, the MCG Tactical Tools Team sought to take the vast position reports provided daily by the DTCC and open them up for adhoc querying by analysts on their control team.  Furthermore, the platform envisioned by MCG would allow users to create complex queries—and have unfettered access to the entire dataset—with no prior knowledge of SQL.

The concept of utilizing the gamut of information coming daily from the DTCC was, in itself, nothing new.  The control team had long been importing the reports into Excel in pieces, and filtering on points of interest.  However, the process was cumbersome and prone to errors—inherent limitations of Excel made it an inadequate medium for managing such a large dataset.  Furthermore, the analysts would need to re-run their manual process day after day, or cope with the ever-rising risk of their data becoming obsolete.

The tactical tool developed by MCG alleviated the burden of accessing the DTCC position reports manually.  Daily data refreshes from the DTCC are now loaded automatically to a central data repository, while a user-friendly point-and-click interface allows several analysts to query the data simultaneously from different locations.  And to further maximize usability, the query tool seamlessly integrates with Excel—letting users export their query results to spreadsheets and continue their analysis.

The benefits to the client and their control team have been numerous: while their new query tool undoubtedly saves them considerable time and money, it has also expanded the team’s capabilities.  Meanwhile, the DTCC Position Report Query Tool joined an ever-growing inventory of MCG’s dynamic and easily deployable tactical tools which can be applied to similar problems in the future.

Did You Know?

Data completeness is a measure of whether all the necessary data—as defined by established requirements—is available in the data resource.


Did You Know?

The DTCC Data Quality Metrics Tool has caught occasional mistakes committed by the DTCC itself.


Did You Know?

The latest version of Excel can support up to 1,048,576 rows of data in a single spreadsheet.  Properly working with larger datasets requires more robust data management features offered by databases.