How to perform bank reconciliation in Dynamics 365 Finance using excel bank statement import?

Export the bank statement entity

We can export the bank statement entities from D365 in Excel. Navigate to: Workspaces > Data management

Click on Export.

Provide the group name and click on “Add entity.”

Select the entity name and the target data format, and click “Add Entity.”

Similarly, add the entity for the bank statement lines.

After adding the entities, click on “Export.”

After completing the export batch job, click on the download file.

After downloading the files, open them in Excel and add the details. Now, we need to prepare the Excel File according to the required bank. First, we will prepare the Bank Statement Header. Below is the Template for importing the bank statement header. · STATEMENTID ENDINGBALANCE · BANKACCOUNT FROMDATE · CURRENCY TODATE · OPENINGBALANCE

Similarly, we also need to prepare the Bank Statement Lines. The template for the bank statement lines is:

However, not all the fields mentioned above are necessary for the Bank Statement lines. We can also keep some of them blank. You can see in the screenshot below that we have kept some of the fields blank.

In the above screenshot, the first two lines are for the unreconciled transactions already in the system. However, the remaining three include customer payment to settle customer invoices, interest expense charged by the bank, and customer payment. After adding the details, we will import the bank statement into the system. For that, navigate to: Workspaces > Data management

Click on “Import.”

Add the group name and click on “Add entity.”

Add the entities and upload the files.

Click on “Import” to import the bank statement into D365.

If the data in the bank statement header and lines is correct, the system will upload the bank statement.

Check the bank statement by navigating to: Modules > Cash and Bank Management > Bank Accounts.

Select the relevant bank account, and under the Reconcile Fast tab, click on Bank Statement.

The bank statement will be available there.

Click on the statement ID to see the details of the lines imported into the system.

There, the details can be seen.

Click on Validate to validate the imported bank statement. If the validation is successful, the system will allow you to proceed.

Click on “Confirm” to confirm the bank statement.

Upon confirmation, the system will display the option to reconcile in the worksheet.

There, the unmatched transactions will be shown.

Click on run matching rules to run the defined matching rules.

Select the matching rule set and click “OK.”

The system will run the matching rules and mark the transactions as “Matched.”

Click on matched transactions to view the details.

Mark the transaction and click on the journal to see the journal posted by the system.

Similarly, we can navigate to the customer transactions to see the posted transactions. The invoice settled against the payment journal can also be seen from the customer transactions. Navigate to: Modules > Accounts Receivable > Customers > All customers

Select the relevant customer and click on transactions.

All the transactions will be displayed there.

Select the invoice and click on View Settlement.

The settlement details will be displayed there.

The bank reconciliation can be viewed within the bank account. To access it, navigate to: Modules > Cash and Bank Management > Bank Accounts

Select the appropriate bank account, navigate to the Reconcile Fast tab, and click on Reconciled Transactions.

The reconciled bank transactions are displayed from the bank master as well.

This functionality can also be extended to vendor payments by configuring matching rules, enabling automated reconciliation, and optimizing financial processes.

Conclusion

Importing bank statements through Excel in Microsoft Dynamics 365 Finance marks a significant step toward financial automation. This feature offers greater flexibility and simplifies the reconciliation process. It enables finance teams to import and process bank statements more efficiently, reducing manual effort and improving the accuracy of financial records. By leveraging this capability, organizations can streamline reconciliation workflows, minimize errors, and strengthen overall financial management. If you’re looking to enable this feature or need support optimizing your Dynamics 365 Finance environment, feel free to contact us at marketing@confiz.com. Our experts are here to help.