What is a QIF File?
Most banks can provide your online trust account statement in a variety of file formats. Gateway can read these files and automatically receipt the money for you, saving you time and effort. One of these formats is the Quicken Interchange Format, or QIF. Each bank has their own method of creating the file, but most have a section on your statement where you may choose to export a QIF file to a location of your choice.
You can open QIF files with any word processor programme; Notepad works well. This is how they will look when converted to a QIF file.
PD/C FROM Franklin Smith
MRent Frank Smith
PD/C FROM James Smith
MPart=J Smith Code=RENT Ref=10664
PD/C FROM Nigel Anderson
M10653 N Anderson
D is the date of the Transaction
T is the Transaction amount
N is the Cheque or Receipt reference number
P is the payee of the Cheque or recipient of the Receipt
M is the memo details of the Transaction. This line may include Ref= for a reference, Part= for particulars and Code= for a code, depending on the bank that created the file.
How does Banklink work?
The Banklink Wizard compares tenants in your database with transactions in the bank file. In order for a tenant to have the correct funds receipted against them they need to have a Tenant Banklink Reference Code entered on their file and they must use this Reference on the transaction which will appear in the QIF file.
Gateway looks at the details of the transaction in the QIF file and then checks every Tenant Banklink Reference Code to see if that code is contained anywhere within the transaction.
- If there are no codes that are contained within the transaction then it will not match.
- If there is one and only one code that is contained within the transaction then Gateway will create a match.
- If there are two or more tenant codes that are contained within the transaction then it will not match.
Example 1: Creating a match
A reference has been entered below for James Smith. It is 10664. James has included his Banklink Reference in his online payment and it appears in the M line of the transaction below.
PD/C FROM James Smith
MPart=J Smith Code=RENT Ref=10664
Select Banklink Wizard from the Tools menu then follow the prompts to import your QIF file.
Once you have browsed for and selected your file click Next. At this point Gateway will try to match every transaction in the QIF file with every tenant in your database. Where a match is successful Gateway will display the transaction in black. If no match is successful Banklink will display the transaction in red.
Frank’s rent payment has failed to match above because he has not entered his unique tenant number into his online payment. Gateway 2007 will not receipt his rent. A temporary solution here would be to change the reference in the tenant file so that it matches some part of the transaction in the QIF file. The ‘M’ line in Frank’s transaction is as follows:
Rent Frank Smith
In this case it would be best to enter the entire line to ensure that there is an exact match. If the word ‘Rent’ was chosen then there could be any number of other transactions that contain the word rent, and any clash would prevent a match. Alternatively ‘Smith’ would not be a satisfactory choice due to another tenant having the word Smith in their transaction line. While ideally all tenants would match with a unique reference number, using “Rent Frank Smith” as the Banklink reference will work fine, providing that these exact words in that exact order are not found elsewhere in the QIF file.
Gateway will not automatically receipt to the tenants that match. You have control over which ones are selected. Tick the transactions you want to be processed or choose Select All and continue through the Banklink Wizard by clicking Next.
Selecting your banklink reference numbers
The Banklink Reference numbers that you choose for each tenant need to be completely unique, and unlikely to appear in any other transaction. The reference may contain numbers, letters or spaces. The longer the reference, the less likely it is that it will clash with or be contained in another transaction.
Some strong reference numbers would be:
1827354 < This reference would not be duplicated in any other file
SMITH0001 < Although Smith is a common name, the numbers will prevent a clash
A3H8G5F65J < Mixed letters and numbers will not be accidentally duplicated
Some weak reference numbers would be:
11 < This could appear in a file as a street address number
Smith < Smith is a common name and is likely to occur again at some point
Rent < Rent might be the only info in the transaction line, but it is not enough
243 < This number could easily be contained within another file
Below is a specific scenario of three different reference numbers in a database and how they may clash with each other.
Example 2: Similar reference code issues
The above reference codes seem different at first, but if these three were in the same database only the last code would match.
- When Banklink comes to process Anne’s transaction within the QIF file it will check the reference code of all tenant files.
- It will check Anne’s file and see that her reference code is contained within the transaction as planned.
- It will then check Barry’s file to see if his reference code is contained within the transaction. It will see that Jones1 does exist within the transaction. This is a second time a reference code has been contained within this file.
- Banklink will then check Charlie’s file to see if his reference code is contained within the transaction. It will see that Jones does exist there. This is the third time a reference code has been contained within this file.
- Banklink has found 3 reference codes that are contained within the file and will therefore not create a match.
When Barry’s transaction is processed Gateway will find two reference codes contained within the transaction (Barry’s and Charlie’s) and will also not create a match.
Only Charlie’s transaction will appear in black (matching) because one code and one code only will be contained within the transaction. Although Charlie’s reference code is the ‘weakest’ the other codes (Jones1 and Jones100) will not be found in his transaction file.
Reference codes should be unique to avoid any potential clashes.
Banklink Basics – Setting up your office with Banklink
Here are some tips to ensure that you get the most out of Banklink.
Ensure that new tenants that sign up with your agency have a unique Banklink reference code entered into their payment in either the Particulars, Code or Reference section. Some Gateway users prefer to fill out the form on behalf of the tenant. This way you can be sure that the correct reference number is added and avoid having to spend time chasing up tenants to ensure that they have their payment set up correctly.
Send an email or letter to your tenants with their new Banklink code asking them to implement it in their future payments. You can determine how you want to set up your reference codes and enter them into Gateway in the tenant file first, and then use the banklink reference merge field in an email or letter to make it even easier. Over the following weeks as those codes are added to payments you will be able to tick more and more matching black transactions in the Banklink Wizard.
Remember that although a unique code is preferable you can use any data from the transaction provided that no other reference numbers will be contained within it. This means that you can match up tenants now if you think it is unlikely that they will change their online payment method. Simply copying the entire ‘M’ line (excluding the M at the start) will in most cases be sufficient. If the only item in the ‘M’ line is ‘Rent’ then that will not be unique in which case copying the entire ‘P’ line might be a better option.
While the Banklink wizard will greatly reduce the time spent on receipting in your office each morning it is likely that there will always be some tenants who do not enter their reference number or include sufficient details in their payment for Banklink to create a match. These rent amounts will need to be receipted manually.