Importing tenant receipts from a bank statement/CSV

Rather than manually recording tenant receipts, you can bulk import them from a bank statement or CSV download of transactions.

On the recent transactions page within your online banking account, you should see a button to Export/Download the transactions for a selected date range - you can normally export them into a variety of formats, including CSV and OFX.  When downloading the transactions, care should be taken not to include previously downloaded transactions, as otherwise you could record duplicate payments in the system (although it will warn you of this).

Having downloaded the transactions, you can upload the file into the system in the following location:

Home -> Finance -> Tenant Receipts Import -> Import from a file:

 

mceclip0.png

 

The system will check that the file hasn't been used before, and then read each inbound (paid in) row/transaction in the file, and based on the payee/reference/description details included in that row, try to find a matching tenant in the system.  You will then be taken to a summary screen showing the match results, and the list of transactions in the file.

The summary shows the status of the transactions in the uploaded file:

mceclip2.png

The available status values for any transaction are:

  • Imported: a tenant receipt has been created from the uploaded and matched transaction (receipts only get created in the next step - see "Importing matches" below)
  • Matched: the transaction has been successfully uploaded and matched to a tenant in the system, but no receipt has been created yet
  • Unmatched: the system cannot find a matching tenant
  • Duplicate: the transaction has been uploaded before (i.e. there is another imported transaction with the same external transaction ID - note, external IDs are not provided on most bank formats)
  • Ignored: a user has marked this transaction as to be ignored
  • Invalid: this transaction is missing key/required data, or in an invalid format

The second half of the page shows each transaction in turn, together with the match (if a matching tenant can be found):

mceclip1.png

 

You can edit a match if it isn't correct, by clicking the edit (pen) link on the right-hand side.  Click the ignore button to ignore the transaction altogether.

 

Updating a match

When updating a match (or manually finding a match for an unmatched transaction), the system gives you the option of saving down the match text for future import runs:

mceclip3.png

The match text is the payee/reference/description on the transaction, and can be stored against the tenant in the system, and used for future imports - removing the need to manually find the match again.

 

Importing matches

Once you have reviewed the matches and are happy with them, the final step is to create the tenant receipts from them.  To do this, click the "Import Matches" button at the top of the page.

The system will warn for possible duplicates at this stage - i.e. if there has been a receipt for the same amount and date already recorded for this tenant - plus warn for other issues - e.g. that the tenant isn't due to pay anything or is fully paid up already.  Any matches that you don't want to import at this stage can be deselected.

Once the receipts have been imported, you will be taken to a results page where you can bulk send out a receipt confirmation email to each tenant via the "Email Receipts" button at the top.

 

Generic CSV format

This is a generic 10ninety format that can be used for files in a format not supported in the banks dropdown list (for example, housing benefit payments).  It supports the following column headings:

Date, Payee, Reference, Description, Amount, External Transaction ID

Date and Amount are required, as is at least one of Payee, Reference or Description.

We can configure a saved mapping to this format if the format you are using has different column headings - saving you having to rename them with each upload.  Please contact Support who can assist with this.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.