Upload Bank Transactions

Auto-matching Downloaded Bank Transactions [Labs]

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. You may skip to step 4. 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, however.

---

Uploading Bank Files

Step 1: Select a File Format from the menu

If you select the OFX format, no additional setup is needed, skip to Step 7. When you change the File Format to anything other than OFX, 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 Fixed Width format, skip 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. Skip 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.

In the example above, the Account Reconciliation has a Statement Difference of 0.00. However, there are also four unmatched transactions - two Increases and two decreases. It would be a mistake to Finalize this transaction before examining the Unmatched transactions.

Step 8: 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.

Clicking the Unmatched button above the Decreases grid will hide the Increases grid and reveal the Unmatched Bank Decreases on the left. In the example above, a Payment is found in Workamajig for the same Amount and Date as an Unmatched transaction from the bank. Since the Check/Reference number did not match, Workamajig could not be sure this transaction should be matched and requires user intervention. If it is believed these two items should be matched, select each row (DO NOT use the checkbox as this marks the item as Cleared - simply select somewhere in the row) so both are highlighted, then click the Edit icon on the Unmatched item.

At the bottom of the Edit form is the data found in the selected Decrease item; at the top are two options. Selecting the Match to selected item option will remove the Unmatched item from the grid and mark the selected Decrease item as Cleared and adjust the balance calculations. NO OTHER DATA IN WORKAMAJIG will be changed by this action.

Clicking Done over the Unmatched Bank Decreases grid will hide that grid and clicking the Unmatched button about the Increases grid will hide the Decreases grid and reveal the Unmatched Bank Increases grid.

In the example above, 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. If it is believed these two items should be matched, select each row (DO NOT use the checkbox as this marks the item as Cleared - simply select somewhere in the row) so both are highlighted, then click the Edit icon on the Unmatched item.

At the bottom of the Edit form is the data found in the selected Increase item; at the top are two options. Selecting the Match to selected item option 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 9: Creating missing Workamajig data based on Unmatched transactions

When the Unmatched transactions are revealed, if no matching Workamajig items can be found, an item can be created to match the transaction found in the bank statement by clicking the Edit icon of the Unmatched transaction.

This will likely show one or more warnings in the Edit form. THIS IS TO BE EXPECTED. This is because the item selected in the corresponding Increases/Decreases grid DOES NOT match the item you have selected in the Unmatched grid. This warns against choosing the Match to selected item option when either the Amount does not correspond to the bank transaction or the item has already been cleared. However, choosing to Add New Receipt/Payment and match may be appropriate.

After the desired items have been created, refreshing the open Account Reconciliation will bring them into the Increases/Decreases grid.

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 should be Finalized.