The purpose of the Bank Activity screen to make it simple to match your bank statement to transactions already in the system electronically. It also allows easy creation of new deposits and withdrawals based on data from your bank that isn’t in the system yet. Using the Bank Activity feature will make your reconciling a bank statement much quicker.
Once your bank accounts are setup (click here to learn more about setting up bank accounts), then the bank activity process consists of 3 steps:
- Download activity from your bank
- Upload the file in the bank activity form (this will attempt to automatch with transactions that already exist)
- Manually match/create/exclude entries that didn’t auto match
These steps will be described in detail below, but first, the bank activity screen consists of 4 tabs and the terminology of those tabs is important so those will be described here first:
- Unmatched – this tab shows all the remaining bank records that did not auto match to existing transactions or match any bank rules you have created.
- Rule Applied – this tab shows all bank records that matched the logic of bank rules you have created. The system tries to match records to bank rules during initial upload of bank activity as well as each time a new rule is created or a rule is modified.
- Matched/Excluded – this tab shows all bank records that have been matched or excluded. Matching means a record downloaded from your bank has a corresponding transaction in the system for the same amount and same bank account. Records are matched and moved to this tab during initial upload of bank activity (the system applies a series of auto-matching logic as described at the bottom of this training article). They can also be matched on the Unmatched tab manually by the user and created as new transactions from the Unmatched tab or the Rule Applied tab. Creating a new transaction on those tabs will also automatically match that record to the transaction and move it to the matched tab. On the Unmatched tab, records can also be Excluded which will move them to this tab. Excluding is described in more detail later in this training. If something is on this tab that should not be matched or excluded then press the button on that row that says Unmatch or Include respectively.
- Bank Rule – this tab shows all bank rules you have previously created. Think of a bank rule as a transaction you want to create the same as a transaction you’ve created on bank activity in the past. For example you can set a rule so any bank record uploaded where the name is “Sprint” should create a transaction for vendor “Sprint Wireless” and post to the “Telephone Expense” account. These rules are on the Bank Rule tab and can be modified and deleted there. This tab is described in more detail further down in the training.
Log into your bank website and choose to download your bank transaction history for your desired date range. You need to download the information into a QFX or QBO file format – typically choosing the QuickBooks or Quicken format option will provide you with the required QFX or QBO file format.
Converting CSV to QFX
If your banking institution does not provide bank activity in the above formats, the CSV (Comma Delimited) file can be obtained and converted to QFX via a third party application. We recommend csv2qfx Convert from MoneyThumb. Click here for more information on csv2qfx Convert
Upon clicking ‘Download’ the file should save to your computer.
- Open the bank activity window from the top ribbon under Banking – Get Bank Activity
- Select your desired bank account in the Bank Accounts dropdown
- Check or uncheck the Match Undeposited Funds check box based on whether you want the system to match these or not. If checked then the auto-matching logic will first try to match transactions in the system for this bank account and if it doesn’t find a match it will then try to match this record to transactions in the system that were posted to the Undeposited Funds account. If it does find a match in undeposited funds, it will change the account from undeposited funds to the bank account and then perform the auto-match. It will not match to undeposited funds in a closed period though, only in open periods. If you don’t want this undeposited funds matching to occur then uncheck that box before uploading the file.
- Click Upload Bank Activity, browse out to the file you downloaded from the bank and then upload. As part of the upload process the system tries to match and apply rules as described in the following steps
- The system applies auto-matching logic to try and match each activity record with a transaction in the system (auto-matching logic is outlined at the bottom of this training article). Records that auto-match will be put on the Matched/Excluded tab
- Next, on the records that did not auto match, the process will then try to apply any bank rules to help quickly create transactions that do not yet exist but follow the same rules as transactions created during previous bank activity sessions (the Bank Rules feature is described in detail elsewhere in this training article). Records that match to bank rules will be put on the Rule Applied tab
- All remaining records will be shown on the Unmatched tab and are ready to be processed
Step 3 – Manually match/create/exclude entries that didn’t auto match
Most of the time spent on this screen will be on the Unmatched tab during this step, but a bit of time will likely be spent on the Rule Applied tab.
If there is a possible match in the system but the match wasn’t close enough that the system could auto match, it will indicate there’s a possible match with an M on the row in the 9th column (highlighted in red in image below). Reasons for possible matches might be if there are multiple transactions in the system for the same amount so it doesn’t know which to match, or if there are multiple bank activity records imported with the same amount and only 1 transaction for that amount in the system so it doesn’t know which to match to. To see the possible matches, click the arrow on the far left of the row (highlighted in orange in image below) and this will dropdown a list of possible matches for that record (highlighted in green in image below). Click the Match button on the far right of the row that is the correct match and this record will be matched and moved to the Matched/Excluded tab. If you’ve opened the matched dropdown and want to close it, click the arrow button again on that row (highlighted in orange below).
It’s possible that you imported some records from your bank that you didn’t intend to or that you’ve already reconciled manually so you want them removed from the bank activity Unmatched tab. To remove these rows, click the check box on the far left of the rows you want to exclude and then click the Select Command dropdown in the upper right above the grid and select Exclude Selected from the dropdown (red arrow in image below). This will move those rows to the Matched/Excluded tab. If this is done by mistake then on the Matched/Excluded tab, locate that row and press the Include button to move it back to the Unmatched tab again.
Some companies don’t enter all transactions in the system ahead of time and use the bank activity window to quickly create them and match to the bank all in one step. If the transaction you want to add hits only 1 GL account and location then it can be added directly from the row on the Unmatched grid. Follow these steps to add the transaction:
- Populate the Vendor on that row if desired – this isn’t a required field and only applies to withdrawals, not deposits. If you select a vendor then the Account column will automatically be populated with the Default Expense Account on that vendor.
- Set or change the Account on that row – this is required and must be populated on both withdrawals and deposits before they can be created.
- Set or change the Location – this will default to the location on the bank account and is a required field.
- Check the Add Rule check box if you want a bank rule created. By default Add rule is not checked which means a new bank rule will not be created unless you check that box. Checking the box to add the rule will make it so that any other bank record with this same name will be recommended to hit this same vendor/account/location and be moved to the Rule Applied tab. Leaving the Add Rule box unchecked means that when this transaction is added, no bank rule will be created. More details on bank rules are provided later in this training article.
- Either click Add on the row or wait and add in bulk. If you want to create the transaction (and possible bank rule) now, click Add on the row and this transaction will be created and the record will be moved to the Matched/Excluded tab as matched. If you want to set a lot of rows first and then add all the transactions at once in bulk, then click the check box on the far left of each row you want to add and then click the Select Command dropdown in the upper right above the grid and select Add Selected from the dropdown (red arrow in image below).
If you need to create a transaction that hits more than 1 GL account or location then instead of populating Vendor/Account/Location on the row, click the Withdrawal or Deposit button on that row and a new window will open with the normal Withrawal or Deposit form where you can create a transaction with as many details as desired. Once finished entering the transaction, click Create Withdrawal or Create Deposit in the upper left corner of the pop up window and this will create the transaction and move that row to the Matched/Excluded tab as matched.
Clearing Unmatched Tab
Once you’ve finished creating and matching all the desired transactions in the Unmatched tab, if any bank records remain in this list and you want to just remove them because you don’t intend to use them later, click the ‘Remove All Unmatched’ button near the top of the Bank Activity window and all unmatched items will be moved to the Matched/Excluded tab that you can then match at a later date.
Rule Applied Tab
If you have bank rules setup and you’ve uploaded transactions that match those rules, the system will recommend the rules be applied to those records. It will show those records on the Rule Applied tab which looks and functions like the Unmatched tab. The benefit of this tab is it shows you records matched your existing bank rules and it shows what Vendor/Account/Location will be assigned if you choose to add those transactions.
Review the records on this tab, make changes to the Vendor/Account/Location fields on any row that you’d like to change and then just as on the Unmatched tab, you can either add 1 at a time with the Add button on each row, or add in bulk. Adding in bulk is usually best and the quickest way to do it is to click the Select all check box at the top of the far left column (circled in orange in image below) to select all rows and then click Select Command dropdown in the upper right above the grid and select Exclude Selected from the dropdown (red arrow in image below). This will create those transactions and these rows will move to the Matched/Exlcluded tab as matched.
- Name – this is the name of the bank rule and can be named whatever will help you know this rule. By default the rule will have the same name as the Name on the bank record used to create this rule, but it can be modified on the Bank Rule tab.
- Type – this defines what type of transaction will get created using this rule (withdrawal or deposit) and will default to the same type as the record used to create the rule.
- Bank Account – this defines which bank accounts this rule applies to. You can either select the word All and it will apply to all bank accounts or select 1 specific bank account. If you want the same rule to apply to multiple bank accounts but not to all then you will need to create that rule for each bank account you wish to apply it to.
- Condition – this can either be “equal” or “contain”. Since the bank rule looks at the name on the bank activity record to see if a rule exists matching that name, you can either say this rule must match the name exactly by choosing “equal” or you can choose “contain” and it will try to apply the bank rule to any record where the name contains the value you type, but doesn’t have to equal it exactly. The default setting on bank rules is “equal”.
- Value – this is the value that the bank rule will try to match to the Name field on the bank records. You can simplify the value so instead of an exact name which might say “Sprint #05918” you can set the value to just say “sprint” and could set the condition as “contain” and then during bank activity upload, any bank record that’s name contains the word “sprint” will have this bank rule applied to it. This value defaults as the Name on the bank record used to create it initially.
- Vendor – this is the vendor that will be defaulted on bank withdrawals for records that match this rule
- Account – this is the GL account that will be defaulted on withdrawals or deposits for records that match this rule
- Location – this is the location that will be defaulted on withdrawals or deposits for records that match this rule
Bank rules get created by having the Add Rule check box checked on a row where you populate the Vendor/Account/Location fields and click Add (or bulk add). Make desired changes to bank rules on this tab and then click “Save changes” button in the upper right hand side above the grid (red arrow in image below). To delete a rule hit the delete trashcan icon on the far right of the row.
Use the + Size and – Size buttons above the grid to make the work area of this screen larger.
Best practice: do bank activity every week to keep the list of records from getting too large and harder to manage at once.
Ever wonder what matching logic is used for auto-matching? The matching process goes through the following sequence to find matches and potential matches:
1. Check number (Number in the Memo Field to the Check Number on the AP Payment record)
2. Amounts match and Unique (only 1) and Dates match – Mark as Match
3. Amounts match and unique (only 1) and with Date Range of -4 days – Mark as Match
4. Amounts match and unique (only 1) and with Date Range of -7 days – Mark as Match
5. Amounts match and unique (only 1) and with Date Range of +2 days – Mark as Match
6. Amounts match and unique (only 1) and with Date Range of all past days – Mark as Match
7. Amounts match multiple records – Mark as a potential Match
8. Next it does the same for Undeposited Funds account entries where the date is not closed on the legal entity