Dynamics GP electronic bank reconciliation, called Electronic Reconcile, imports the bank's cleared-transaction file and marks matching checkbook entries as cleared. Setup has three parts: a checkbook whose bank account number matches the file exactly, a Bank Download ID linking them, and a configurator that maps the bank's file layout. Whatever fails to match is reviewed by hand.
Dynamics GP Electronic Reconcile setup: checkbooks and the download ID
Electronic Reconcile needs each bank account in the import file to belong to exactly one Dynamics GP checkbook, and that checkbook to be attached to the Bank Download ID used for the import. If the account number in the file does not match a checkbook, the line is rejected before any matching happens.
Microsoft's article on modifying the BAI file format in Electronic Reconcile sets out the checks. A setup checklist drawn from it:
- Open the bank file in Notepad and note the account number on each account header line, which in a BAI file begins with 03.
- In Cards, Financial, Checkbook, confirm the Bank Account field on the checkbook matches that number exactly, including leading zeros.
- Make sure no other checkbook carries the same bank account number. Microsoft says the line is not read if two checkbooks share it.
- In Tools, Routines, Financial, Electronic Reconcile, Download Maintenance, add every checkbook the file covers to the Bank Download ID.
When an account number fails to match, the Unprocessed Data Records Report says so. Read that report after every test import.
Mapping the bank file in the Dynamics GP configurator
The Dynamics GP configurator describes the bank file's layout so Electronic Reconcile can read it: which record types exist, how many fields each line holds, and which field is the amount, date or check number. The mapping must match the bank's real file, field for field, or the import fails.
Open it from Tools, Routines, Financial, Electronic Reconcile, Configurator. For a BAI file, choosing BAI as the file format fills in a default layout. Microsoft's article warns that the default detail line has 9 fields while the published Version 2 BAI layout has 7, and that a mismatch in field count can make GP hang or crash. Count the fields on the detail lines, which begin with 16, on both a check and a deposit before you save.
Two more items from the same article:
- Transaction codes. In Codes Entry, every code that appears in the file must sit under a transaction type. Transfer credits and debits have no default codes, so add them if the bank sends transfers. A code placed under Other will need to be reconciled by hand.
- Line length. GP fails on lines longer than 244 characters. Ask the bank to shorten them, or trim a description field for testing.
The configurator's field mapping is also what matching relies on. If the check number is mapped to the wrong field, checks cannot pair with their checkbook entries, whatever the amounts.
What happens to unmatched lines after a Dynamics GP import?
Unmatched lines after a Dynamics GP import stay outside the reconciliation until someone deals with them. Lines GP could not read at all appear on the Unprocessed Data Records Report with a reason. Lines it read but could not pair stay uncleared, and you mark or adjust them yourself.
Work through leftovers in groups:
- Unreadable records. Fix the configurator or the checkbook setup, clear the test download and import again.
- Bank items with no checkbook entry, such as fees and interest. Record them as adjustments when you reconcile. Microsoft's Bank Reconciliation guide for Dynamics GP describes the Reconcile Bank Adjustments window for service charges and interest, and the Cleared Amount Entry window for a check that cleared for a different amount.
- Checkbook entries with no bank line. These are outstanding checks and deposits in transit, or errors. Age them every month.
Treat a recurring unmatched deposit with suspicion. A deposit recorded in the books that never reaches the bank can be a timing item, or it can be cash that was taken before deposit; the guide to skimming fraud explains what that pattern looks like.
Opening a Dynamics GP bank file in Excel before import
Opening a Dynamics GP bank file in Excel and saving it again is the most common way to break an import that worked yesterday. Excel strips the leading zeros from month and day, so 01/05/2015 becomes 1/5/2015 and no longer matches the configurator's date format. Use Notepad for any inspection or edit.
Microsoft's BAI article lists this cause for wrong dates, alongside a date format in the configurator that does not match the file exactly. Blank lines at the end of a file can also make GP hang, and Notepad shows them where Excel hides them.
During testing, clear previous downloads before importing the same file again. Microsoft warns that the Delete All option in the download summary removes history for every checkbook, so use it only before Electronic Reconcile is live anywhere.
When should a Dynamics GP checkbook be reconciled manually?
Reconcile a Dynamics GP checkbook manually when the bank cannot supply a stable file, when the account has few lines a month, or when the first months of Electronic Reconcile are still being proven. The manual route uses Reconcile Bank Statements: enter the ending balance, ending date and cutoff date, mark cleared items and add adjustments.
Microsoft's guide says reconciliation succeeds only when the adjusted checkbook balance and adjusted bank balance differ by zero, and that the Last Reconciled Balance and Date on the checkbook then update. Both routes end in the same place, so running one month both ways is a fair test of a new configurator.
If you run several ledgers, compare the approach elsewhere. Odoo bank reconciliation and its matching models, Zoho Books reconciliation and Sage bank reconciliation in Sage 50 and Intacct all match differently. Whatever the ledger, foot the statement first. PreciseBankFlow checks that each statement's opening balance plus deposits less withdrawals equals its closing balance, and you can request a spot.