Purpose

Bank Auto Reconcile takes an CSV file containing a list of financial transactions and automatically reconciles it against the corresponding FrontAccounting (FA) bank account.

Prerequisite

FA does not have a field to contain the check number. In order for Bank Auto Reconcile to work for CSV formats with check numbers, you must enter the check number for each checking transaction in the memo field.

Bank Auto Reconcile also handles transactions without check numbers (electronic deduction, ACH, etc), by matching the amount and date (7 day window).

Procedure

You will need to download the financial transactions in a CSV format file. Most financial institutions offer this option.

For example, for Wells Fargo, you would:

  1. Login
  2. Click on the bank account
  3. Select download account activity
  4. Select the dates (i.e. dec 1 through dec 31)
  5. Select the comma separated format.
  6. Download

For example, for Chase Credit Cards, you would:

  1. Login
  2. Click on Credit Cards
  3. Click on See Activity
  4. Select the Statement Period
  5. Select Download
  6. Select the comma separated format.

In Bank Account Auto Reconcile:

  1. Select the bank account you want to reconcile
  2. Trial Run. Always keep this checked until you verify that the displayed output is correct.
  3. Reconciliation Date. This should be entered as the end date of the statement.
  4. CSV Format. Two formats are supported, but you can easily add more by changing the code (see below).
  5. Select the CSV file you downloaded.
  6. click Auto Reconcile.

Bank Auto Reconcile will attempt to match the bank statement transactions in the FA bank account. Any missing transactions will be displayed.

If all transactions are found (or displayed as missing, but are marked as automatically created using the G/L account from a prior matching bank transaction, i.e. recurrent transactions) then you can unclick the Trial Run box and run Auto Reconcile again to automatically reconcile the statement.

Tips and Tricks

After performing an automatic reconcile, use the manual reconciliation page (Banking and General Ledger->Reconcile Bank Account) to help identify any double-entered transactions. Using "New" in the drop-down menu will show all the unreconciled transactions. If there are transactions in this list that you believe should have been reconciled, they may need to be deleted.

How To Add/Modify CSV Formats

The csv formats are contained in the items[] array:

$items = array();
$items[] = array("Wells", array( "date", "amount", "", "checkno", "comment" ));
$items[] = array("United", array( "card", "date", "postdate", "comment", "category", "type", "amount", "memo"));

To add a new format, create an array element with fields in desired positions.

The first field is the bank account name up to the first whitespace. For example, if you have a bank account "United Credit Card", set the first field to "United" so that when this bank account is selected for reconciliation, the correct csv format (i.e. "United") will also be selected.