Spreadsheet file formatting for CR Reconciliation Center

Ensure your bank's statements can be downloaded to a Microsoft Excel spreadsheet file. The file(s) must be downloaded and available in a local directory or a shared file system. The files must be formatted specifically to be imported into the CR Reconciliation Center. If your bank provides a CSV file, that file must converted to an Excel spreadsheet. Files must be correctly formatted before importing. The formatting of the spreadsheet that is imported for each reconciliation affects the success of the rule matching. Consider these points when formatting the Excel spreadsheet:

  • Columns may occur in any order.
  • Valid column labels are: Date, Amount, Description, Check Number, Reference. Columns with invalid labels are ignored during import.
    Date
    Must be a valid date. The separators must be slashes. For example, MM/DD/YY , DD/MM/YY.
    Check Number
    Check numbers are pulled from this column. Rule definition can be configured to match by check number. During reconciliation, records can be filtered by check number.
    Amount
    Amounts should be formatted without currency symbols, or commas to separate thousands. Negative amounts should not be in parenthesis and should be preceded with a minus sign. For example, "-12345.67" not "($12,345.67)".
    Description
    Typically, the transaction description or additional details about the transaction are contained in this column. The Match Text fields in a rule always pulls from values in the Description column.
    Reference
    This is an optional, additional column that can be imported. The column can accommodate, for example, a transaction ID.
  • Any records with an amount of zero are skipped during the import. You can use this skipping action to fix errors to the Statement Date or Ending Balance. For example, create a file with one record with a zero amount. When entering the bank to import, correct the Statement Date and Ending Balance and import the singe record with a zero amount. The record is not imported, but the Date and Balance are updated.