Upload bank transactions [in-depth guide]
Overview
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 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 your banking institution does not support OFX, most others that allow exporting will support CSV or Fixed-width formats. These formats require additional setup information in Workamajig.
Uploading bank files
Guide contents: Select a file format | Setup for columns in CSV files | Setup for columns in Fixed Width files | Amount contains decimal | File contents | Number of header/trailer records | Editing the file format | Upload transaction file | Reviewing matched transactions | Reviewing & matching unmatched transactions | Creating missing Workamajig data based on unmatched transactions
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. However, it may be possible to inspect a downloaded file and decide 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 transaction 'amount' and 'date'. If other values are provided, it is recommended that you specify them so Workamajig will be able to read & display them. This can be helpful later if Workamajig cannot 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 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 transaction 'amount' and 'date'. If other values are provided, it is recommended that you specify them so Workamajig can read & display them. This can be helpful later if Workamajig cannot 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 for Workamajig to read the file correctly.
Step 4: File contents
Some banking institutions provide a single file with both deposits & 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 & 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 settings and make necessary changes.
Step 7: Upload transaction file
Once you have downloaded and saved a file from your banking institution, click the Upload transaction file and select the saved file from the location on your hard drive. Once it is uploaded successfully, transactions that match what is in Workamajig will be cleared automatically. The Selected increases/decreases & 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 & Selected balance/statement difference calculations will automatically update.
Step 9: Reviewing & matching unmatched transactions
Unmatched transactions are bank transactions that cannot be paired with 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 required user intervention.
Unmatched transactions that are determined to match an existing transaction in Workamajig can be dragged/dropped to the transaction in the reconciliation. This will manually match & clear the transaction.
Selecting 'Match to an existing item' will remove the 'unmatched' item from the grid & 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 on 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.