Imported Statement Reconciliation

You are able to import your bank statements directly into Expert Agent, in the commonly available CSV format. This is the most commonly provided format for export from the major banks.

Having imported your statements, you are then required to match transactions to records that you have already on Expert Agent so that EA can process the payment or receipt on to the relevant record.  Please be aware that if you are choosing to reconcile your receipts in this way, the amounts selected will appear in the relevant Landlord/Tenancy records on Expert Agent ready to be allocated to their corresponding transactions, Expert Agent will not allocate the received funds against any outstanding transaction. Once you have completed your Bank Reconciliation, you will need to go into the tenancies and allocate the funds or process through the Unallocated Funds reports (Unallocated Funds - Tenancies or Unallocated Funds - Landlords). It is very important that if you decide to reconcile in this way that you do not also receipt your rents in manually via the Outstanding Transactions or Rent Arrears reports as this will duplicate the receipts.

Before starting the process on Expert Agent there are some key points that need to be clear on the statement that you have downloaded from your bank:

1. The bank statement must be saved as a .csv format.
2. There must not be any symbols in the file name as this will stop the upload.
3. When looking at the data, you must make sure that there are no symbols in the notes or reference field of the statement. 
4. Your credits and debits must show in two separate columns for Expert Agent to be able to process the information.
5. The date needs to be in the correct format - DD/MM/YYYY.

The Imported Statement Reconciliation function is accessed from the Accounts sub menu, from the top Finance drop menu:

You will be prompted with the following screen to upload new statements, you are able to upload multiple files at once:

1. Select the bank account the statement entries relate to.
2. Click into the next field to locate and upload the .csv file. Find the .csv file on your computer or network, select it so that its name appears in the file name box, and click ‘Open’. This will take you back to the main screen with the file name now showing in the upload box.

Press 'Upload Statement', and the system will confirm that the upload was successful:

When you upload a statement, if it has been previously imported or if you have saved the file with a file name previously used, an error message will display; you will need to rename the file and try uploading again. 

If for any reason the statement is incorrect, for example if you have assigned it to the wrong account, you will be able to delete it from this screen. You are also able to add another file if necessary.

1. Follow the process above to upload another statement.
2. Tick the statement you would like to delete.

When you arrive at the ‘Uploaded Statements Awaiting Mapping’ tab, you will need to click into the statement you would like to work with by clicking the row in the grid that details the statement. One of the most important stages of this process is making sure that you tell Expert Agent what information is in the columns of the statement that you have imported - this is called Mapping. 

You will be presented with a drop down of all of the possible fields in your upload and you will simply select the matching field:

If you have already saved the mapping for a statement you will simply select the saved mapping in the ‘Use Saved Mapping’ drop down at the top:

If you are mapping your statement for the first time, you will need to match the columns heading on the bank statement to the relevant headings in Expert Agent. 

1. Select the correct field name from the drop down for each of the relevant columns.
2. Enter a name e.g. ‘Natwest statement’ or similar and save the mapping, if you will need to use it again – this means you don’t have to select the correct fields each time.
3. Click on the save icon.

You will then need to move to the ‘Mapped Statement Awaiting Reconciling’ tab, where your statement will show alongside any other statements awaiting processing. From this screen you are also able to delete any statements as long as no transactions have been marked as reconciled.

You will need to click on the statement you want to reconcile, this will open up to show the individual entries contained within the statement. You will need to click on each transaction in turn to reconcile it.

Reconciling Debits

Clicking on a debit entry will load the below screen. Expert Agent will first look for any payments that match the exact amount of the transaction you are working on to allow you to reconcile against it. If there are no such transactions in Expert Agent, all payments of a lesser will load. This is particularly useful when one record on your bank statement is made up of a number of transactions on Expert Agent, such as agency payments. 

1. Select whether you wish transactions that are an exact match to the amount to appear or all credits.
2. You can then select the amounts which total the payment that shows on the bank statement. Please note that as you select credits that transaction will move automatically to the top of the list rather than staying in their original position.
3. The totals at the bottom will show how much still needs to be allocated.  Only when you have fully allocated the total amount will a "save" icon appear in the bottom right hand corner of the window. On clicking save, the transaction will turn green and be marked as reconciled.

Reconciling Credits

Clicking on a credit entry will load the following screen. Expert Agent will try to match the receipt to a tenancy record for you based on the reference used by the tenant on their payment. This is why it is very important the tenant use the same reference for all payments. If Expert Agent is successful in finding a match, that tenancy will be automatically matched. If not you will simply assign the relevant record using the plus symbol. On clicking save, the transaction will turn green and be marked as reconciled.

 Should you wish to ignore a statement entry (for example if there were to be an entry showing bank charges or similar) then ticking that item in the ‘Ignore’ column will automatically reconcile the entry and update the grid.

 The grid at the bottom of the page shows the total debits and credits as well as the amounts reconciled and unreconciled.

On returning to the ‘Mapped Statements Awaiting Reconciling’ tab, the grid will now be updated with the total reconciled entries.

If you ever find you have processed an entry against the wrong contact, you will need to cancel the transaction from the particular contact's record and selecting ‘Cancel Transactions’ from the financial drop down. If you cancel an item then the system will automatically return the entry in the statement to being unprocessed.

Once you have finished reconciling a statement you will need to mark it as complete from the main ‘Mapped Statements Awaiting Reconciling’ screen (where the statements are listed as opposed to the items on a particular statement).

Tick alongside the statement in the ‘Complete’ column.

If there are entries that have yet to be reconciled, you will be alerted to how many there are and asked if you want to mark all these entries as ‘Ignored’. You’re unable to complete a statement as being reconciled whilst there are items that are not reconciled.

Clicking ‘Yes’ on the message that appears will mark the entries as ‘Ignored’, clicking ‘No’ will allow you to return to the previous screen from which you can go into the statement and reconcile the items manually.

The completed statement will now appear on the ‘Reconciled Statements’ tab:

Clicking on a completed statement will show you the statement details, which entries were reconciled and to which record(s), and which entries, if any, have been ignored.

It is very important that once you have finished reconciling through this process to make sure that you go to the Unallocated Funds - Tenancies report so that you are able to allocate the funds to outstanding transactions.