Automating FATCA and CRS Tax Reporting Submissions and Resubmissions

FATCA and CRS reporting must be submitted in an XML format which can be challenging to build due to wide ranges of source data, complex output schema and constantly evolving regulatory requirements.

Building bespoke code to tackle these regulatory reporting issues leads to long turnaround times and requires expensive third-party developers.

Here at Continuum, we work with clients to replace their high maintenance bespoke code with intuitive, internally maintained Alteryx workflows.


  • Data is queried from a wide range of source databases and supplemented with additional excel files.

  • Account data is cleansed and summarised.

  • Up-to-date regulatory rules are introduced to remove non-reportable individuals or organisations.

  • Error checks are implemented to validate the quality of the source data, and exception reports for review are produced where the validation is failed.

  • XML outputs are built to regulatory standards from the remaining reportable accounts.

  • XML files are validated against the current XSD format.

  • XML files are reconciled to the previous period’s XML files, with any differences given to the business to confirm as expected.

  • Only when the format and contents on the report have been validated and agreed by the business are the files submitted to the regulator.

FATCA Diagram 2.0 (1).png
Copy of FATCA year rec.png

Our approach by using Alteryx to solve this complex and prevalent issue gives a clear advantage in adaptability and repeatability:

  • Re-runnable, fully transparent, end to end process showing clear data lineage, rule application and exceptions.

  • When regulatory standards change and evolve, rules can be introduced, removed, or edited in the workflows to alter which accounts are reported.

  • When reporting formats change, only edits to the XML building aspects of the workflow are needed.

  • When the list of reportable countries is revised, only an updated country list is required to include newly reportable accounts.

When faced with new requirements for the next reporting period, these amendments can be made seamlessly without requiring the modification of complex and bespoke code. The use of Alteryx transforms a currently expensive and arduous process into an internally maintained workflow, saving valuable time and resources and providing reassurance that the business has reported their client data to the best of their ability and knowledge.


Case Study - 2018/19 CRS and FATCA Resubmissions

Summary

The process of separating accounts into their different Reporting Entities, identifying previous errors in reporting, and generating multiple new XML files of different formats is extremely time and resource consuming for IT departments to execute, and almost impossible to accomplish with the use of traditional tools such as Excel, SQL Server or 3rd party submission platforms.

By using Alteryx, Continuum and the business were able to carry out this process transparently, providing step-by-step reassurance to the business that the new submission was completed to the highest degree. The project sign-off was made easy and simple as the business had a wide overview of the data at all stages through the power of Alteryx data visualisation.


A client had their 2018 and 2019 CRS/FATCA submissions rejected by the tax authorities in late 2019 and were given an extremely tight deadline for resubmitting their returns.
Their several thousand international clients had assets and income across a range of holdings with multiple entities, adding significant complexity to the CRS/FATCA reporting requirements.

Repeatability and transparency were vital since the new reports would be under enhanced scrutiny, requiring an audit trail of how the reports were produced.

Working alongside the client’s team, Continuum hit the deadlines by managing the project and performing the work in Alteryx.

The key stages of the project were:

FATCA_CRS Case Study.png
  • Cleanse and compile data for all accounts deemed reportable by the business and the CRS/FATCA reporting guidelines.

  • Split out the accounts by the Financial Institution they are reportable under.

  • Repeatably validate data quality and provide errors and omissions for the business to correct, for instance invalid City names and address formats which now require increased quality for 2019 reporting onwards.

  • Sharing approach and data summary with a Tax Consultancy in a workshop for their input and approval.

  • Analyse the previous CRS/FATCA XML reports, comparing with actual source data to identify previous reporting errors, and identify the 3 record types:

    • Corrections - Accounts reported under the correct institution but with incorrect data that requires correction.

    • Deletions - Accounts incorrectly included in the previous submission, and should be omitted from reporting.

    • Creations - Accounts incorrectly omitted from reporting, and should be included in the new submission.

  • Reconcile the new 2018 and 2019 resubmissions against the previous reports.

  • Receive sign off from the business to proceed.

  • Create the new XML submission files.

  • Verify all files on tax authority portal and submit.