際際滷

際際滷Share a Scribd company logo
Reconciliations Design Considerations for a Robust Reconciliation Process uatro Solutions Prepared by Quatro Solutions for HSBC
Context Information flows imperfectly though our diverse systems Need to ensure data is checked & corrected Reconciliations are key Operations & Product Control processes LIVE UAT!
Many Possible Reconciliations
What Ones Should We Do?
Product Control Reconciliations? Assuring the GL Balances Cash, MtM & Notional Common Theme: Two part: Trade FO-GL Postings Rec  Posting =   Balances
Reconciliations Principals Reconciliation is not the process if identify items that match  its the process of identifying  and resolving  items that dont match. Robust Repeatability Fix-to-Clear Aged Responsibility
Robust Repeatability Our environments are often not robust such that systems, feeds and databases are not available on a given day. It should be possible to run an automated rec the next day without losing any breaks. YTD or MTD is therefore preferred over daily movements. Inception to date is better but sometimes impracticable.
Fix to Clear Fixes to underlying system [Trade Amends, Clearing items from NACK queue ] should clear breaks the following day. Conversely, if fixes are not applied, the breaks should remain outstanding. Marking breaks as cleared or attaching explanations waste effort. Again ITD, YTD or MTD recs are preferred.
Aged Responsibility  A controlled way to reduce breaks is to make someone responsible for clearing each break and to highlight where such breaks have not been fixed. This means aging breaks by recording the first instance of a break and mapping breaks to a  person  by some data-based rules. Is it PCs job?
油
Reconciliation Tool
Two Table Approach Load Raw Data, Map and Net Queries to Identify Breaks Abs(A-B) > 0.05  for Numerics  Big Union Query
油
油
油
SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, FO_Feed.DailyCash AS DiffVal, &quot;Different Cash&quot; AS Comment FROM BO_Feed INNER JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((Abs([BO_Feed].[DailyCash]-[FO_Feed].[DailyCash]))>0.01)) UNION SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, FO_Feed.Book AS DiffVal, &quot;Book Different&quot; AS Comment FROM BO_Feed INNER JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((FO_Feed.Book)<>[BO_Feed].[Book])) UNION SELECT FO_Feed.TradeId, FO_Feed.Mtm, FO_Feed.DailyCash, FO_Feed.Book, &quot;&quot; AS DiffVal, &quot;FO Not BO&quot; AS Comment FROM FO_Feed LEFT JOIN BO_Feed ON FO_Feed.TradeId = BO_Feed.ExtId WHERE (((BO_Feed.ExtId) Is Null)) UNION SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, &quot;&quot; AS DiffVal, &quot;BO Not FO&quot; AS Comment FROM BO_Feed LEFT JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((FO_Feed.TradeId) Is Null));
Or  Union, Sum & Diffs
SELECT qryAll.TradeID, qryAll.Book, Sum(qryAll.MtM) AS MtMDiff, Sum(qryAll.DailyCash) AS CashDiff,  Nz(First(tblBookMappings.Owner),&quot;Unmapped&quot;) as Resp FROM (SELECT TradeId, Book, Mtm, DailyCash FROM FO_Feed UNION SELECT ExtId, Book, -[Mtm], -[DailyCash] FROM BO_Feed) AS qryAll  LEFT JOIN tblBookMappings on qryAll.Book = tblBookMappings.FO_Book GROUP BY qryAll.TradeID, qryAll.Book HAVING (Abs(Sum(qryAll.MtM)) >0.05) OR (Abs(Sum(qryAll.DailyCash)) >0.05);
Single Table Approach Load Raw Data, Map and Net Amend Queries to Mark Breaks Single source for MI Reconciliation Run Once
油
油
油
Some Other Considerations [1]  Real Time Data Feeds Complex Transform: Simple Rec Mappings & Maintenance Impractical
Some Other Considerations [2] XML v CSV Re-create Trade-level from Accounting Month-End 60-40 Development Rule CSV!
Some Other Considerations [3] Duplicates Issue MI: Management Information Key-Field v Trade Detail Matching Testing
Some Other Considerations [4] Proposed and Partial Matches Simplicity v Complexity Single Rec: Multiple Reports Tolerances
Some Other Considerations [5] Inadequate Source Data Single Excel Breaks Report Pivot Tables Auto Filter Open, Cancelled and Matured Trades
Some Other Considerations [6] Reconcile Primary Sources for Other Uses FO-FO Recs Duplication of Effort [Inter and Intra-Team]
Ad

Recommended

BSD Presentation v1.1
BSD Presentation v1.1
Sylverflash
Brochure V3.2 B3
Brochure V3.2 B3
Sylverflash
Business Case Presentation V0.9
Business Case Presentation V0.9
Sylverflash
Five finger audit
Five finger audit
Bertil Hatt
Account Reconciliation Why is Matching Your Records Crucial.pdf
Account Reconciliation Why is Matching Your Records Crucial.pdf
KaranBhalla36
The Essential Guide to Account Reconciliation: Accuracy Matters
The Essential Guide to Account Reconciliation: Accuracy Matters
Outbooks Australia
DDMA / Hyperion: Datakwaliteit
DDMA / Hyperion: Datakwaliteit
DDMA
Integrated Reconciliation A Process View
Integrated Reconciliation A Process View
David A.A. Ross
Oracle tips and tricks
Oracle tips and tricks
Yanli Liu
AFS ERP Financial Reconciliations
AFS ERP Financial Reconciliations
Global Creative Group, Inc
Recs-Day1-updated.pptRecs-Day1-updated.ppt
Recs-Day1-updated.pptRecs-Day1-updated.ppt
ssuserad3af4
Here is How the B2B Payment Process Can Help Streamline Processes!
Here is How the B2B Payment Process Can Help Streamline Processes!
Account Mein Fintech Solutions Pvt. Ltd.
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
kets8
optimus fintech -What is Payment Reconciliation?
optimus fintech -What is Payment Reconciliation?
Optimus Fintech
Sap hana retail sales reporting innojam21 final
Sap hana retail sales reporting innojam21 final
Kumud Singh
Top 4 Accounting Mistakes and How to Stop Them.pdf
Top 4 Accounting Mistakes and How to Stop Them.pdf
Finance Course Ahmedabad
Introduction.business scurecfo stratgy .
Introduction.business scurecfo stratgy .
patrickjenkins862
Collaborate 2012-critical success factors for data quality management - ppt
Collaborate 2012-critical success factors for data quality management - ppt
Chain Sys Corporation
Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
ICT Frame Magazine Pvt. Ltd.
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
Muhammad Rizwan Akram
Connecting Data and Intelligence: The Role of FME in Machine Learning
Connecting Data and Intelligence: The Role of FME in Machine Learning
Safe Software
Enabling BIM / GIS integrations with Other Systems with FME
Enabling BIM / GIS integrations with Other Systems with FME
Safe Software
AI vs Human Writing: Can You Tell the Difference?
AI vs Human Writing: Can You Tell the Difference?
Shashi Sathyanarayana, Ph.D
Cyber Defense Matrix Workshop - RSA Conference
Cyber Defense Matrix Workshop - RSA Conference
Priyanka Aash
OWASP Barcelona 2025 Threat Model Library
OWASP Barcelona 2025 Threat Model Library
PetraVukmirovic
MuleSoft for AgentForce : Topic Center and API Catalog
MuleSoft for AgentForce : Topic Center and API Catalog
shyamraj55
"Database isolation: how we deal with hundreds of direct connections to the d...
"Database isolation: how we deal with hundreds of direct connections to the d...
Fwdays
AI VIDEO MAGAZINE - June 2025 - r/aivideo
AI VIDEO MAGAZINE - June 2025 - r/aivideo
1pcity Studios, Inc
From Manual to Auto Searching- FME in the Driver's Seat
From Manual to Auto Searching- FME in the Driver's Seat
Safe Software
Python Conference Singapore - 19 Jun 2025
Python Conference Singapore - 19 Jun 2025
ninefyi

More Related Content

Similar to Recs Presentation (10)

Oracle tips and tricks
Oracle tips and tricks
Yanli Liu
AFS ERP Financial Reconciliations
AFS ERP Financial Reconciliations
Global Creative Group, Inc
Recs-Day1-updated.pptRecs-Day1-updated.ppt
Recs-Day1-updated.pptRecs-Day1-updated.ppt
ssuserad3af4
Here is How the B2B Payment Process Can Help Streamline Processes!
Here is How the B2B Payment Process Can Help Streamline Processes!
Account Mein Fintech Solutions Pvt. Ltd.
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
kets8
optimus fintech -What is Payment Reconciliation?
optimus fintech -What is Payment Reconciliation?
Optimus Fintech
Sap hana retail sales reporting innojam21 final
Sap hana retail sales reporting innojam21 final
Kumud Singh
Top 4 Accounting Mistakes and How to Stop Them.pdf
Top 4 Accounting Mistakes and How to Stop Them.pdf
Finance Course Ahmedabad
Introduction.business scurecfo stratgy .
Introduction.business scurecfo stratgy .
patrickjenkins862
Collaborate 2012-critical success factors for data quality management - ppt
Collaborate 2012-critical success factors for data quality management - ppt
Chain Sys Corporation
Oracle tips and tricks
Oracle tips and tricks
Yanli Liu
Recs-Day1-updated.pptRecs-Day1-updated.ppt
Recs-Day1-updated.pptRecs-Day1-updated.ppt
ssuserad3af4
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
Best_Practices_FICA_Apollo_Use_Case_Tyres_LTD
kets8
optimus fintech -What is Payment Reconciliation?
optimus fintech -What is Payment Reconciliation?
Optimus Fintech
Sap hana retail sales reporting innojam21 final
Sap hana retail sales reporting innojam21 final
Kumud Singh
Top 4 Accounting Mistakes and How to Stop Them.pdf
Top 4 Accounting Mistakes and How to Stop Them.pdf
Finance Course Ahmedabad
Introduction.business scurecfo stratgy .
Introduction.business scurecfo stratgy .
patrickjenkins862
Collaborate 2012-critical success factors for data quality management - ppt
Collaborate 2012-critical success factors for data quality management - ppt
Chain Sys Corporation

Recently uploaded (20)

Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
ICT Frame Magazine Pvt. Ltd.
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
Muhammad Rizwan Akram
Connecting Data and Intelligence: The Role of FME in Machine Learning
Connecting Data and Intelligence: The Role of FME in Machine Learning
Safe Software
Enabling BIM / GIS integrations with Other Systems with FME
Enabling BIM / GIS integrations with Other Systems with FME
Safe Software
AI vs Human Writing: Can You Tell the Difference?
AI vs Human Writing: Can You Tell the Difference?
Shashi Sathyanarayana, Ph.D
Cyber Defense Matrix Workshop - RSA Conference
Cyber Defense Matrix Workshop - RSA Conference
Priyanka Aash
OWASP Barcelona 2025 Threat Model Library
OWASP Barcelona 2025 Threat Model Library
PetraVukmirovic
MuleSoft for AgentForce : Topic Center and API Catalog
MuleSoft for AgentForce : Topic Center and API Catalog
shyamraj55
"Database isolation: how we deal with hundreds of direct connections to the d...
"Database isolation: how we deal with hundreds of direct connections to the d...
Fwdays
AI VIDEO MAGAZINE - June 2025 - r/aivideo
AI VIDEO MAGAZINE - June 2025 - r/aivideo
1pcity Studios, Inc
From Manual to Auto Searching- FME in the Driver's Seat
From Manual to Auto Searching- FME in the Driver's Seat
Safe Software
Python Conference Singapore - 19 Jun 2025
Python Conference Singapore - 19 Jun 2025
ninefyi
cnc-processing-centers-centateq-p-110-en.pdf
cnc-processing-centers-centateq-p-110-en.pdf
AmirStern2
Viral>Wondershare Filmora 14.5.18.12900 Crack Free Download
Viral>Wondershare Filmora 14.5.18.12900 Crack Free Download
Puppy jhon
9-1-1 Addressing: End-to-End Automation Using FME
9-1-1 Addressing: End-to-End Automation Using FME
Safe Software
OpenPOWER Foundation & Open-Source Core Innovations
OpenPOWER Foundation & Open-Source Core Innovations
IBM
The Future of Technology: 2025-2125 by Saikat Basu.pdf
The Future of Technology: 2025-2125 by Saikat Basu.pdf
Saikat Basu
Crypto Super 500 - 14th Report - June2025.pdf
Crypto Super 500 - 14th Report - June2025.pdf
Stephen Perrenod
June Patch Tuesday
June Patch Tuesday
Ivanti
Enhance GitHub Copilot using MCP - Enterprise version.pdf
Enhance GitHub Copilot using MCP - Enterprise version.pdf
Nilesh Gule
Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
Information Security Response Team Nepal_npCERT_Vice_President_Sudan_Jha.pdf
ICT Frame Magazine Pvt. Ltd.
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
ENERGY CONSUMPTION CALCULATION IN ENERGY-EFFICIENT AIR CONDITIONER.pdf
Muhammad Rizwan Akram
Connecting Data and Intelligence: The Role of FME in Machine Learning
Connecting Data and Intelligence: The Role of FME in Machine Learning
Safe Software
Enabling BIM / GIS integrations with Other Systems with FME
Enabling BIM / GIS integrations with Other Systems with FME
Safe Software
AI vs Human Writing: Can You Tell the Difference?
AI vs Human Writing: Can You Tell the Difference?
Shashi Sathyanarayana, Ph.D
Cyber Defense Matrix Workshop - RSA Conference
Cyber Defense Matrix Workshop - RSA Conference
Priyanka Aash
OWASP Barcelona 2025 Threat Model Library
OWASP Barcelona 2025 Threat Model Library
PetraVukmirovic
MuleSoft for AgentForce : Topic Center and API Catalog
MuleSoft for AgentForce : Topic Center and API Catalog
shyamraj55
"Database isolation: how we deal with hundreds of direct connections to the d...
"Database isolation: how we deal with hundreds of direct connections to the d...
Fwdays
AI VIDEO MAGAZINE - June 2025 - r/aivideo
AI VIDEO MAGAZINE - June 2025 - r/aivideo
1pcity Studios, Inc
From Manual to Auto Searching- FME in the Driver's Seat
From Manual to Auto Searching- FME in the Driver's Seat
Safe Software
Python Conference Singapore - 19 Jun 2025
Python Conference Singapore - 19 Jun 2025
ninefyi
cnc-processing-centers-centateq-p-110-en.pdf
cnc-processing-centers-centateq-p-110-en.pdf
AmirStern2
Viral>Wondershare Filmora 14.5.18.12900 Crack Free Download
Viral>Wondershare Filmora 14.5.18.12900 Crack Free Download
Puppy jhon
9-1-1 Addressing: End-to-End Automation Using FME
9-1-1 Addressing: End-to-End Automation Using FME
Safe Software
OpenPOWER Foundation & Open-Source Core Innovations
OpenPOWER Foundation & Open-Source Core Innovations
IBM
The Future of Technology: 2025-2125 by Saikat Basu.pdf
The Future of Technology: 2025-2125 by Saikat Basu.pdf
Saikat Basu
Crypto Super 500 - 14th Report - June2025.pdf
Crypto Super 500 - 14th Report - June2025.pdf
Stephen Perrenod
June Patch Tuesday
June Patch Tuesday
Ivanti
Enhance GitHub Copilot using MCP - Enterprise version.pdf
Enhance GitHub Copilot using MCP - Enterprise version.pdf
Nilesh Gule
Ad

Recs Presentation

  • 1. Reconciliations Design Considerations for a Robust Reconciliation Process uatro Solutions Prepared by Quatro Solutions for HSBC
  • 2. Context Information flows imperfectly though our diverse systems Need to ensure data is checked & corrected Reconciliations are key Operations & Product Control processes LIVE UAT!
  • 5. Product Control Reconciliations? Assuring the GL Balances Cash, MtM & Notional Common Theme: Two part: Trade FO-GL Postings Rec Posting = Balances
  • 6. Reconciliations Principals Reconciliation is not the process if identify items that match its the process of identifying and resolving items that dont match. Robust Repeatability Fix-to-Clear Aged Responsibility
  • 7. Robust Repeatability Our environments are often not robust such that systems, feeds and databases are not available on a given day. It should be possible to run an automated rec the next day without losing any breaks. YTD or MTD is therefore preferred over daily movements. Inception to date is better but sometimes impracticable.
  • 8. Fix to Clear Fixes to underlying system [Trade Amends, Clearing items from NACK queue ] should clear breaks the following day. Conversely, if fixes are not applied, the breaks should remain outstanding. Marking breaks as cleared or attaching explanations waste effort. Again ITD, YTD or MTD recs are preferred.
  • 9. Aged Responsibility A controlled way to reduce breaks is to make someone responsible for clearing each break and to highlight where such breaks have not been fixed. This means aging breaks by recording the first instance of a break and mapping breaks to a person by some data-based rules. Is it PCs job?
  • 10.
  • 12. Two Table Approach Load Raw Data, Map and Net Queries to Identify Breaks Abs(A-B) > 0.05 for Numerics Big Union Query
  • 13.
  • 14.
  • 15.
  • 16. SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, FO_Feed.DailyCash AS DiffVal, &quot;Different Cash&quot; AS Comment FROM BO_Feed INNER JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((Abs([BO_Feed].[DailyCash]-[FO_Feed].[DailyCash]))>0.01)) UNION SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, FO_Feed.Book AS DiffVal, &quot;Book Different&quot; AS Comment FROM BO_Feed INNER JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((FO_Feed.Book)<>[BO_Feed].[Book])) UNION SELECT FO_Feed.TradeId, FO_Feed.Mtm, FO_Feed.DailyCash, FO_Feed.Book, &quot;&quot; AS DiffVal, &quot;FO Not BO&quot; AS Comment FROM FO_Feed LEFT JOIN BO_Feed ON FO_Feed.TradeId = BO_Feed.ExtId WHERE (((BO_Feed.ExtId) Is Null)) UNION SELECT BO_Feed.ExtId, BO_Feed.Mtm, BO_Feed.DailyCash, BO_Feed.Book, &quot;&quot; AS DiffVal, &quot;BO Not FO&quot; AS Comment FROM BO_Feed LEFT JOIN FO_Feed ON BO_Feed.ExtId = FO_Feed.TradeId WHERE (((FO_Feed.TradeId) Is Null));
  • 17. Or Union, Sum & Diffs
  • 18. SELECT qryAll.TradeID, qryAll.Book, Sum(qryAll.MtM) AS MtMDiff, Sum(qryAll.DailyCash) AS CashDiff, Nz(First(tblBookMappings.Owner),&quot;Unmapped&quot;) as Resp FROM (SELECT TradeId, Book, Mtm, DailyCash FROM FO_Feed UNION SELECT ExtId, Book, -[Mtm], -[DailyCash] FROM BO_Feed) AS qryAll LEFT JOIN tblBookMappings on qryAll.Book = tblBookMappings.FO_Book GROUP BY qryAll.TradeID, qryAll.Book HAVING (Abs(Sum(qryAll.MtM)) >0.05) OR (Abs(Sum(qryAll.DailyCash)) >0.05);
  • 19. Single Table Approach Load Raw Data, Map and Net Amend Queries to Mark Breaks Single source for MI Reconciliation Run Once
  • 20.
  • 21.
  • 22.
  • 23. Some Other Considerations [1] Real Time Data Feeds Complex Transform: Simple Rec Mappings & Maintenance Impractical
  • 24. Some Other Considerations [2] XML v CSV Re-create Trade-level from Accounting Month-End 60-40 Development Rule CSV!
  • 25. Some Other Considerations [3] Duplicates Issue MI: Management Information Key-Field v Trade Detail Matching Testing
  • 26. Some Other Considerations [4] Proposed and Partial Matches Simplicity v Complexity Single Rec: Multiple Reports Tolerances
  • 27. Some Other Considerations [5] Inadequate Source Data Single Excel Breaks Report Pivot Tables Auto Filter Open, Cancelled and Matured Trades
  • 28. Some Other Considerations [6] Reconcile Primary Sources for Other Uses FO-FO Recs Duplication of Effort [Inter and Intra-Team]