homepage

Bank Reconciliation Import and Co-Pilot AI

Concept

  • This document addresses two points to help with Bank Reconciliations. You can just do the file mapping by itself and not use Artificial Intelligence (AI) if you like. However, if you want to use AI, then you must have a file to import.
  • The bank reconciliation in Business Central works best when you import records from the bank account. Unfortunately, setting up the fully integrated import routines is not completely reliable and costs hours and potentially monthly fees to setup. To get around this we are providing a semi-automatic process. This process involves exporting a simple text file from the bank website and formatting an import into Business Central.
  • The matching of imported records works best when used in conjunction with the built-in Artificial Intelligence module “Co-Pilot”.

Video.

Turn on the Artificial Intelligence Settings (Optional)

  • You must be running BC23 or later to use this feature.

  • Turn on Co-Pilot.

  • Search "Copilot & AI Capabilities" to ensure your Bank Account Reconciliation "Capability" is turned on, data flow is enabled, etc.

  • Turn on the “Bank account reconciliation with Copilot” feature in Feature Management.

    • Search "Feature Management" then find the line: "Feature Preview: Bank account reconciliation with Copilot" (if not visible, it is turned on by default in your Business Central version)
      • Ensure the feature is set to Enabled for "All Users"
      • You may have to log off and log back in to fully enable.

Setup the Import of the Bank Statement File

The solution we are proposing is a semi-automatic process:

  • Because a human must log into the bank website to export the simple text file, we avoid all the complexities of having one system talk to another.
  • Because we take the exported file and run it through a (graphical) mapping tool that takes just a few minutes to setup; if there are changes in future exports; we can just re-map the process.
  • If you want a fully automated process, please contact your HomeBuilder consultant, or turn on a service like the “Envestnet Yodlee - Bank Feeds Service”.

Steps:

Export a File from your Bank.

  • Most banking websites provide one or more export types. Once you have logged into your bank account, find a CSV or .txt export if possible. You want the export to be a flat file.
  • Here is a sample layout:

Map the Export in Business Central Using the Assisted Setup Wizard.

  • Search Assisted Setup to open the “Assisted Setup” page in Business Central.
  • Find the line “Set up a bank statement file import format”, click on the line (the Hyperlink), and follow the instructions.

  • On page 1 of the Wizard (called "Bank Statement File Setup) click Next

  • Page 2 – Upload a sample file and allow the system to help define the layout.

    • Using a file from your bank to get the system to define the layout is significantly easier than building the layout yourself.
    • You can override the process manually if you prefer but the automated system works well.

  • Page 3 – confirm the suggested number of header lines the import should ignore, one in our example:

  • Page 4 – Confirm the suggested separator, number of columns, and line separator. The settings shown below worked for our example file.

  • Page 5 – Confirm/tell the import which column is which:

  • Page 6 – Confirm/Tell the import the date format and decimal separator:

  • Page 7 – Click on the Test link to see the Bank Statement Import preview.

    • Ensure that the headers are aligned with the content (E.g. Transaction Amount header contains a payment number from your Bank Statement)
    • If the Headers are misalligned with the content, go back and read the instructional carefully to see the misconfiguration reason.

  • Page 8 – Give your definition a name and attach it to a specific Bank Account that you work with.

    • You should pick something that will explain to various users the content of the file. There is no specific "format" required here.

Running the Bank Reconciliation

  • Create a new Bank Reconciliation as normal. However, instead of running the “Suggest Lines” routine, click on “Reconcile with Copilot” from the List (note that you may not be able to run this from the card).

Note

If you did not turn on the Artificial Intelligence tools, you can bring in the file from the Bank Tab with the button “Import Bank Statement”.

  • Click on the “Import transaction data” link and select the file you exported from the bank.

  • Click on the Generate button after selecting the data file from the bank.
  • You will get a screen reporting on what the system matched; in my case, all lines were matched even though some transactions were out of my typical date tolerance.

  • Review the Bank Reconciliation.
  • Make manual adjustments if necessary.
  • Once satisfied with the results, post the Bank Rec.
Note

You can view your setup in “Data Exchange Definitions”. This is a more advanced part of the system and is not recommended for most users. If your file is not working, it is normally easiest to just rebuild by starting the Assisted Setup Wizard.

Solving for dates errors and making the Amount show credit debit based upon another field for co-pilot bank import.

  1. If you get an error that the date format is not correct or is missing, you may want to use the following two settings (see image below called “Data Exchange Definition”).
  • Date Format = yyyyMMdd
  • Length = 8
  1. B Another Date format is M/d/yyyy rather than M/dd/yyy
  2. If all Amounts are positive from the export out of the bank, they will normally provide another field that indicates whether the Amount is a Credit or a Debit. In our example, field “9” is “0” for credits and “1” for debits. Therefore, we must reverse the Amount sign for credits. (see image below called “Field Mapping”). This is done by:
  • Creating the Column Definition “9” where we enter “0” into the “Negative Sign Indicator” field.
  • We then add a new “Field Mapping” line for “Column No.” “9” that refers to field “7”.