Issue/Symptom/Question
How Do I Create a Bank Reconciliation File to upload bank transactions?
What bank reconciliation file formats are allowed?
What information must be included in the bank reconciliation bank transaction file?
Is there a certain order that bank account information must be input into the file for it to work correctly?
Applies To
PENTA Web > Accounting Module > Bank Reconciliation
Creating a New Bank Reconciliation with a Bank File containing cleared transactions
PENTA Version 21.2 and up
Resolution/Fix/Answer
File Format: Currently, only .csv files are supported for uploading bank transactions into a bank reconciliation statement. When saving an Excel spreadsheet, the user can switch the file format type from .xlsx to .csv
Content and Information: The order in which the columns and data are input into the .csv is crucial to the upload working correctly - any mistakes and the upload will fail.
The columns should be in the following order: "Date", "Description", "Reference", "Withdrawal", "Deposit"
Check with your bank to see if a request is needed for an output file in the format. Some banks are configurable on the output file.
The transactions can be entered in any order, so long as they are complete and abide by the following rules:
- For the "Date" column, all dates MUST be in MM/DD/YYYY format, or if the file converts to M/D/YYYY format for numbers less than 10, that is allowed.
- "Description" is a string, do NOT exceed 50 characters in the string.
- "Reference" is a string, and almost always will be referencing a check # or direct deposit #. Limited to 30 characters in length.
- "Withdrawal" is input as a positive number, with no comma's, can only go to 2 decimal places, and cannot exceed 9,999,999,999.99. ex "1000.75".
- "Deposit" is input as a positive number, with no comma's, can only go to 2 decimal places, and cannot exceed 9,999,999,999.99. ex "1000.75".
NOTE: If you enter in a Withdrawal Amount, do not enter in a Deposit Amount for the same row, or vice versa.
Here is a sample screenshot of a few basic bank transactions in an Excel document - this can be saved as a .csv file and will upload properly:
Cause
Getting an error when trying to upload a Bank Reconciliation bank transaction file