Edit

Share via

Bank statement file import troubleshooting

  • Article

Note

This functionality will be deprecated September 2022, new users should use electronic reporting.

It's important that the bank statement file from the bank matches the layout that Microsoft Dynamics 365 Finance supports. Because of strict standards for bank statements, most integrations will work correctly. However, sometimes the statement file can't be imported or has incorrect results. Typically, these issues are caused by small differences in the bank statement file. This article explains how to fix these differences and resolve the issues.

What is the error?

After you try to import a bank statement file, go to the Data management job history and its execution details to find the error. The error can help by pointing to the statement, balance, or statement line. However, it's unlikely to provide enough information to help you identify the field or element that is causing the issue.

Note

Imported bank statements can overlap only for single a point in time. For example, if a statement ends at 12:00 AM on January 1, 2021, then beginning date for the next statement can be 12:00 AM on Jarnuary 1, 2021 12:00:00 AM.

What are the differences?

Compare the bank file layout definition to the Finance import definition, and note any differences in the fields and elements. Compare the bank statement file to the related sample Finance file. In the ISO20022 files, any differences should be easy to see.

Time zone differences on imported bank statements

The date-time values in the import file can differ from the date-time values that are shown in finance and operations. To prevent this discrepancy, enter a time zone preference on the Configure data sources page. For more information about entering a time zone preference, see Set up the advanced bank reconciliation import process.

Transformations

Typically, the change must be made in one of three transformations. Each transformation is written for a specific standard.

Resource name File name
BankStmtImport_BAI2CSV_to_BAI2XML_xslt BAI2CSV-to-BAI2XML.xslt
BankStmtImport_ISO20022XML_to_Reconciliation_xslt ISO20022XML-to-Reconciliation.xslt
BankStmtImport_MT940TXT_to_MT940XML_xslt MT940TXT-to-MT940XML.xslt

Debugging transformations

Adjust the BAI2 and MT940 files

The BAI2 and MT940 files are text-based files and require an adjustment to enable Extensible Stylesheet Language Transformations (XSLT) debugging. The program makes this adjustment when a file is imported.

  1. Create an XML file, and copy the following text into it.

    <Batch><![CDATA[PASTESTATEMENTFILEHERE
    ]]></Batch>

  2. Copy the contents of the bank statement file, and paste them into the XML file so that they replace PASTESTATEMENTFILEHERE.

Debug the XSLT

For more information, see https://msdn.microsoft.com/library/ms255605.aspx.

  1. Start Microsoft Visual Studio.
  2. Create a console application.
  3. Open the appropriate XSLT.
  4. Click the XLST and its properties page.
  5. Set the input to the location of the bank statement file.
  6. Define a location and file name for the output.
  7. Set the required break points.
  8. On the menu, click XML > Start XSLT Debugging.

Format the XSLT output

When the transformation runs, it creates an output file that you can view in Visual Studio. Use Ctrl+A, Ctrl+K, and Ctrl+D to quickly format the output file.

Adjust the transformation

Adjust the transformation to get the appropriate field or element in the bank statement file. Then map that field or element to the appropriate Finance element.

Debit/credit indicator

Sometimes, debits might be imported as credits, and credits might be imported as debits. To resolve this issue, you must change the appropriate XSLT. If bank statements come from multiple banks, make sure that they all use the same debit/credit approach, or create separate transformations.

  • BAI2XML-to-Reconciliation.xlst GetAmountCreditDebitIndicator template
  • ISO20022XML-to-Reconcilation.xslt GetCreditDebit template
  • MT940XML-to-Reconcilation.xslt GetCreditDebitIndicator template

Examples of bank statement formats and technical layouts

The following table lists examples of the technical layout definitions for advanced bank reconciliation import files and three related bank statement example files. You can download the example files and technical layouts here: Import file examples

Technical layout definition Bank statement example file
DynamicsAXMT940Layout MT940StatementExample
DynamicsAXISO20022Layout ISO20022StatementExample
DynamicsAXBAI2Layout BAI2StatementExample