|
<< Click to Display Table of Contents >> Navigation: Participation Menu > Fees & Receipting > Bank Data Import |
Bank data import is the process of importing bank cash receipt details and assigning those receipts to the members that paid them. Importantly, bank data import is only required for those members that paid their fees via their banking software, cash or cheque. It is not required for those members who paid their fees via the Eway online payments system. Any receipts paid via Eway must be ignored in this procedure.
To use this procedure you must have access to the U3A's banking software or be provided with a file by somebody that does. The file required is known as an OFX or QIF file. These files a very specific file format and is supported by all major Australian financial institutions.
Normally, when you request this file you will be given the ability to enter a date range. We suggest the start date be one day after the last file import and the end date be no later than yesterday's date.
Important
During data import, the software will attempt to match receipts to participants (members). This is done by extracting the member's name from the transaction's description. At best, this procedure is problematic. Therefore,
All receipt transactions & their assignment must be checked carefully for accuracy.
You can import bank data files with file extensions of OFX or QIF...
1.OFX is a Financial Data Exchange formatted file.
2.QIF is a file formatted in the Quciken Interchange Format.
Why multiple formats?
For many years QIF was the de facto standard for the electronic transfer of financial data.
OFX is the new kid on the block & is a far more sophisticated specification.However, OFX files require a very strict adherence to specification. We have noticed some banking software does not conform and therefore creates errors on import. In addition some banking software simply does not support the OFX format.
Which format should I use?
If OFX is available then use it. If it causes errors on import then switch to QIF.
Note that some banking software will give you a choice of QIF formats. For example, the Commonwealth Bank’s Netbank software provides…
1.QIF (US)
2.QIF (AUS)
3.QIF (MYOB)
If given a choice, use the Australian format.
The procedure's user interface consists of three sections...
1. On the top-left corner is the file upload dialogue. Use this dialogue to select the file to be imported into the system.
2. On the top-right corner is the procedure's command buttons. These give you access to the actions performed by the procedure.
3. Underneath items 1 and 2, is the display grid. This displays the transactions entered and provides the ability to manually assign a receipt to a member.
The current financial year will be displayed in the command button section. Always check to ensure you are working in the correct financial year. if it is incorrect, click the Change Year button and select the year required.
The other buttons available are...
1.Apply Payments to Participant records. When you are satisfied that all imported transactions are correctly assigned to participant records, click this button to apply the transactions and finalise the procedure.
2.Show all bank transactions. Normally, only receipt transactions are displayed. Click this button to show all transactions in the import file.
3.Clear bank import file. This option will delete the OFX/QIF file from the system. It is intended to be used if a mistake was made when importing the file. An incorrect date range, for example. The original bank import file will remain intact on your client PC.
The display grid has the following columns...
Column |
Editable |
Description |
|---|---|---|
On File? |
No |
A checkbox to indicate the receipt has already been processed by the system in a previous import. This will occur if your OFX/QIF file's date range overlaps with a previous import. |
Date |
No |
The date received/processed by the U3A's financial institution. |
Name |
Yes |
The participant (member) to whom the receipt has or will be assigned. |
Amount |
Yes |
The amount received. |
Financial To |
Yes |
The member's new Financial To year. This field can be set to the previous financial year, meaning the member is not financial or the current financial year, meaning the member is financial. |
Terms Paid |
Yes |
The term to which the member is financial. If left blank, the member is financial for the full year. If set to a value, the member is financial to that term. Thus, if set to 2 the member is financial in terms 1 and 2 of the financial year but not terms 3 and 4. Note that this column is only displayed when Member Fee Payment Type is not set to Full Year Only in Membership Fees. |
Description |
Yes |
The description of the transaction. Most often this is text entered by the participant when the payment was made. |
Identifier |
Yes |
Most often not used. |
Note: Always choose the Australian file version if you are importing a QIF file and your banking software provides a choice of files.
1.Using your U3A's banking software, download the OFX or QIF onto your PC. It will normally be placed in your Downloads folder.
2.Using the file upload dialogue, import the file into the system. You can do this in one of two ways...
a.Drag the file from your operating system's file manager (File Explorer, for example) and drop onto the file upload dialogue, or
b.Click the Select File button to open a standard operating system file open dialogue, navigate to and select the file required.
Once selected, the system will upload the OFX or QIF file onto the U3Admin server and process it. The results will be output into the display grid.
The import process will make a "best guess" at assigning receipts to participants. The process is not perfect and quite often you will need to correct the automatic assignment.
Click the edit (pencil) button on the grid row you wish to change. Select the required member from the drop-down list on the Name column. When you are satisfied that all is correct, click the Save (disk) icon on the grid row. Your changes are now saved. Repeat for all receipts to be assigned.
When you are satisfied that all rows are correct (or, correct as can be), click the button, Apply Payments to Participant records. This will finalise the procedure and return you to the main menu.
It is safe to exit the procedure without applying the payments to participant records. When you re-enter the procedure all transactions will be displayed in the grid exactly as you have left them.