Auto-matching Downloaded Bank Transactions
Many banks allow banking transactions to be downloaded from their website to a file. New or Open Reconciliations will show the controls that allow those bank files to be imported into Workamajig.
Workamajig supports three different file formats: OFX, CSV and Fixed Width. OFX is a banking industry standard and is the easiest and most stable format to import. If your bank supports this file format, this is the recommended option. If OFX is not supported by your banking institution, most others that allow exporting will support CSV or Fixed Width formats. These formats require addition setup information in Workamajig.
Uploading Bank Files
Step 1: Select a File Format from the menu
If you select File Format: OFX/QFX: no additional setup is needed, skip to Step 7.
If you select File Format: CSV or Fixed Width:
Workamajig will prompt you for additional setup information. Your banking institution should provide an explanation of these file types, although it may be possible to inspect a downloaded file and make a determination about what the settings should be.
For CSV format, go to Step 2a.
For Fixed Width format, go to Step 2b.
Step 2a: Setup for columns in CSV files
CSV format stands for "Comma Separated Values." In this type of plain text file, each row or line of information is a new record and each data value, separated by a comma, is a "column" or field. Many computers will automatically open this type of file in a spreadsheet such as Excel for easy editing.
No matter how many data values your CSV file has, only two are absolutely required, the Amount and the Date of the transaction. If other values are provided, it is recommended that you specify them so Workamajig will be able to read and display them. This can be helpful later if Workamajig is not able to find a matching transaction in its system.
In the example above, the first column would be the Date, followed by the Amount, Check Number and Payee Info. If this file were opened in a spreadsheet, they would be first, second, third and fifth columns. In this example the Transaction ID and Memo values are either not present in the file or not desired for display, so they are set to 0. Additionally, it can be inferred that there is a value in column 4 that is not supported by Workamajig or not desired for display since it is not set. Go to Step 3.
Step 2b: Setup for columns in Fixed Width files
The Fixed Width format is a type of plain text file where each row or line of information is a new record and there are a predetermined number of characters in each row. Each character is a "column." In every row, a data value begins at the the same character column index and contains the same number of characters. There is no delimiter, such as a comma, that denotes where one data value ends and the next begins. Each value simply has a fixed character width. Many spreadsheet programs such as Excel have an import feature to open this type of file for easy editing.
No matter how many data values your Fixed Width file has, only two are absolutely required, the Amount and the Date of the transaction. If other values are provided, it is recommended that you specify them so Workamajig will be able to read and display them. This can be helpful later if Workamajig is not able to find a matching transaction in its system.
In the example above, the first four character columns define the Transaction ID data value. So the Transaction ID column is set to 1, where the data begins, and the Transaction ID Column Size is 4. The Date begins in column 5 and is 10 characters in length. Any data value that is not supplied by the banking institution or is not desired to be displayed in Workamajig should have both its Column and Size set to 0 as Memo in the example above.
Step 3: Amount Contains Decimal
Depending on your banking institution's standards, the transaction Amount will either include decimals or not. If they do, a transaction for $10 would show in the file as "10.00". If they do not, it will show as "1000". The Amount Contains Decimal checkbox must be set correctly in order for Workamajig to read the file correctly.
Step 4: File Contents
Some banking institutions provide a single file with both Deposits and Withdrawals. When that is the case, the Amount value will be positive or negative to denote a Deposit or Withdrawal respectively.
Even if the file contains only Withdrawal transactions, if those transactions are negative, the File Contents should still be set to Deposits and Withdrawals. Also, if the file contains only Deposits, this would still be the correct setting. Only if the file contains ONLY Withdrawals AND the Amount values are positive should the setting be changed to Withdrawals Only - Amounts Positive.
Step 5: Number of Header/Trailer Records
In some cases, the file provided by the banking institution will contain 1 or more rows of data that are not banking transactions. For a Header this could be one row of field names. Other possible cases for either Header or Trailer Records may be rows that summarize the data or provide data about the account being queried such as the bank account number. These rows may not even conform to the standards in the previous steps. In any event, this information is not read or used by Workamajig. It is, however, imperative to set these values correctly to let Workamajig know which rows to ignore and prevent an error reading or displaying the file.
Step 6: Editing the File Format
After completing the previous steps, you will not be prompted to do so again for this Account. Workamajig will remember the File Format you select and the specifications you input. Future Bank Reconciliations will automatically use these settings when a file is uploaded. It may, however, become necessary to change the specifications - either because they contain an error or if the bank changes its specification for the file. In such cases, simply click the File Format button to display the current changes and make any changes necessary.
Step 7: Upload Transaction File
Once you have downloaded and save a file from your banking institution, click the Upload Transaction File and select the saved file from the location in your hard drive. Once it is uploaded successfully, transactions that match what is in Workamajig will be cleared automatically. The Selected Increases/Decreases and Selected Balance/Statement Difference calculations will automatically update.
Step 8: Reviewing Matched Transactions
In the Example below, transactions that match what is in Workamajig will be cleared automatically. The Selected Increases/Decreases and Selected Balance/Statement Difference calculations will automatically update.
Step 9: Reviewing and Matching Unmatched Transactions
Unmatched transactions are bank transactions that could not be paired to a transaction found in Workamajig. These transactions should be reviewed.
In the example below, a Deposit in Workamajig is found with the same Amount as an Unmatched bank transaction. Since the Dates did not match, Workamajig could not be sure this transaction should be matched and requires user intervention.
Unmatched Transactions that are determined to match to an existing transaction in Workamajig, can be drag/dropped to the transaction in the reconciliation. This will manually match and clear the transaction.
Selecting to Match to an existing item will remove the Unmatched item from the grid and mark the selected Increase item as Cleared and adjust the balance calculations. NO OTHER DATA IN WORKAMAJIG will be changed by this action.
Step 10: Creating missing Workamajig data based upon Unmatched Transactions
If no matching transaction in Workamajig is found, an item can be created to match the transaction found in the bank statement by clicking the icon of the Unmatched transaction.
On the Increases side, this will create a receipt
On the Decreases side, this will create a payment.
Other items may be cleared manually, if necessary once the Statement Difference is 0.00 and there are no Unmatched transactions that need to be matched or created, the Account Reconciliation is complete and should be Finalized.