Import Bank Transactions

Introduction

Al Majmoua currently handles all repayments of loans by clients by having clients deposit their repayments directly at a bank, and then Al Majmoua is able to download online a file of these recorded transactions to import their MIS.  They need the ability to do this in Mifos as well.  See related Business requirements for more detail.

User Stories (Epics)

PriorityUser StorySection in FR
1As a user (accountant) at the HO, I want to be able to import a XLS of bank transactions from Audi Bank into Mifos so that all details for our clients' payments are accurately recorded in Mifos 
1As a loan officer at a BO, I want to be able to see the clients at branch have paid their loans in Mifos 
2As a user (accountant) at the HO, I want to be able to import a XLS of bank transactions from any bank into Mifos so that all details for our clients' payments are accurately recorded in Mifos 

Goals

  • Ability to import file of loan repayment transaction details into Mifos
  • Transaction History in Mifos accurately shows loan payments.

Non-Goals

The following items will not be addressed in this release:

  • Importing bank transactions from any bank other than Audi Bank
  • No import of attendance, savings, fees, or loan disbursal information - only loan repayments are imported.
  • Additional logging of import than what we do already in Mifos for logging

Definitions and Terminology

  • Mandatory fields will be preceded by *
  • Links are italicized
  • Buttons are Button

Related Documents

  • Business Requirements

Import Bank Transactions

This feature is in the Admin section and will allow the User to import bank transactions

Use Cases

Administrator assigns permissions to accountant to import bank transactions

Actors

  •  Administrator

Preconditions

  •  None

Basic Flow

  1. Administrator logs onto Mifos and navigates to the Admin section. Administrator selects Manage Roles and Permissions. Administrator selects the role for accountants.
  2. Mifos displays all possible permissions for the role and Administrator scrolls down to "Bulk". Administrator confirms that "Can import transactions" is checked.

Post-conditions

  • Accountant role has permission to import bank transactions

Alternative Flows

  • None

Validations

  • None

Accountant imports transactions (Audi Bank) - file with no errors

Actors

  • Accountant

Preconditions

  •  Accountant has permissions to import transactions
  • Accountant has file in xls (Excel 97 format) to import

Basic Flow

  1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import transactions.
  2. Mifos displays new screen for Import Transactions.
  3. Accountant selects Audi Bank Excel 97 or Audi Bank TSV for bank, and selects file from their computer for import. Accountant clicks on Continue.
  4. Mifos imports file and checks for any errors (see Validations below). If there are no errors, Mifos displays Review & Submit screen with "There are no errors found. Click Submit to continue with import.".
  5. Accountant clicks on Submit. Mifos displays confirmation screen that import was successful.

Post-conditions

  • All data available in the file has been imported and all the correct tables and loan account data correctly populated.

Validations

  • If a row has a cell that's empty in a required column is empty, that row is rejected and appropriate error message displayed.
  • No bad data - ie, no numbers in columns that require text and vice versa. Date of Transaction is in date format YYYY/MM/DD if it's a TSV file, or does not follow the expected format of that column. See FR's below.

Alternative Flows

Accountant cancels Import

  1. At step 4, Accountant clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

  • No data has been imported.

Import has errors and Accountant chooses to continue

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to continue with import of the valid rows and clicks on Submit.   Mifos displays confirmation screen that import was successful.

Post-conditions

  • Valid rows are imported and invalid rows are rejected.

Import has errors and Accountant chooses to Cancel

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to cancel and clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

  • No data has been imported.

Import has errors and Accountant chooses to import a different file

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to import a different file and clicks on Edit Import Information instead. Mifos returns User to the previous screen.  Return to Step 2 of Basic Flow.

Post-conditions

  • No data has been imported.

Loan Officer checks data has been updated

Actors

  • Loan Officer

Preconditions

  • Loan Officer has permissions to view clients loan data
  • Administrator has already imported data for that date.

Basic Flow

  1. Loan Officer logs onto Mifos and navigates to client A's loan account.
  2. Mifos displays Transaction History. Loan Officer confirms that a repayment has been updated correctly.

Post-conditions

  •  None

Alternative Flows

Loan Officer doesn't see the data updated

  1. Loan Officer selects a client who did not pay for the date of transaction the Administrator had imported.
  2. Loan Officer goes to view that client's transaction history and sees that no transaction has been made.

Accountant imports bank transactions (generic) - P2

Actors

  • Accountant

Preconditions

  •  Accountant has permissions to import bank transactions

Basic Flow

  1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import bank Transactions.
    1. OR Accountant clicks on link in Quick Start - Import bank Transactions
  2. Mifos displays new screen for Import Bank Transactions.
  3. Accountant enters date of transaction (defaulted to today). and selects file from their computer for import. File follows generic format set by Mifos.
    1. Accountant can select up to 5 custom fields they want data imported from the file to map to.  Ie - they select custom Field "Serial" and enter in column name "Serial" where the import will map to that column.  No validations are done on custom field columns.
    2. Accountant clicks on Continue.
  4. Mifos imports file and checks for any errors (see Validations below). If there are no errors, Mifos displays Review & Submit screen with "There are no errors found. Click Submit to continue with import.".
  5. Accountant clicks on Submit. Mifos returns the Accountant to the Admin screen.

Post-conditions

  • All data available in the file has been imported and all the correct tables and loan account data correctly populated.

Validations

  • If any cell in a required column is empty  entire import is rejected with appropriate error message.
  • No bad data - ie, no numbers in columns that require text and vice versa. Date of Transaction is in date format MM/DD/YYYY or does not follow the expected format of that column. See FR's below.  This is only for columns required in generic format - there is no validation done on Custom fields.

Alternative Flows

User Stories

PrioritySizeUser StoriesMingle card #
1X-SmallAs a User, I can give permission to import bank transactions. 
1SmallAs a User, I can click on Import Bank Transactions and see options to import my file (Audi Bank). 
1SmallAs a User, I can confirm that I want to import the data into Mifos. 
1MediumPopulate all data from file import in Mifos 
2MediumAs a User, I can click on Import Bank Transactions and see options to import my file (generic) 

Import Bank Transactions (Audi Bank) Functional Requirements

Add new permission

FR#PriDescriptionComments / Mockups
1.1P1Add new permission "Can import transactions" under the Bulk subsection in Permissions. The ADMIN role should by default have this permission checked. 
1.2P1Assumption is the only check when the user imports transactions is if they have this permission checked in their role. There is no checking of their data scope and hierarchy. Ie: If the user is a Loan Officer in BO1, and has this permission checked in their role, then they are allowed to import any bank file, and there is no check that the data they are importing is only for their branch office. 
1.3P2If one day we implement importing transactions per branch, then permission should also check for data scope.  Ie, can only import data for a particular branch. 

Add new screen for Import Bank Transactions (Audi Bank)

FR#PriDescriptionComments / Mockups
2.1P1Add Manage Imports section with Import Transactions link in Admin section 
2.2P1New Import Data Transactions Screen with following fields:

* Import format (dropdown)

* Select import file (browse for select file)
 
2.3P1Import format Field will have two options: Audi Bank (tab delimited) and Audi Bank Excel 97 formats 
2.4P1Screen has buttons Continue and Cancel.  Clicking on Continue imports the file selected and Mifos does validations and brings User to Review & Submit screen.  Clicking on Cancel returns User to Admin screen. 
2.5P1If there is no import plugin for the MFI, then the Import Format options will be blank and if the User tries to import a file and Continue, the following error message will be displayed at the top.  Also if the User does not select a plugin even if there are options then the following error message will be displayed.



Please select the import type.
 
2.6P1If there is no Mode of Payment configured in Mifos to be the same as the first cell of that file (in this case, should always be Bank Audi sal), then an error message will be thrown on the Import screen:



No payment type found named <bank>

where <bank> is the first cell of that file.
 

Import File Details and Validations (Audi)

FR#PriDescriptionComments / Mockups
3.1P1Audi Bank import file is currently in Excel.  Al Majmoua will save these as a tab-delimited file first before importing into Mifos. 
3.2P1Audi Bank import file first few lines contain description of file.  These are to be ignored.  Only data after row of column headings will be imported. 
3.3P1Import file will have columns in below table 
3.4P1There will be bare basic error checking - to successfully import the data. 
3.5P1If a row with C (for Credit in D/C column) contains a cell that's missing a required field, Mifos displays an error message for each row this occurs.

Row <#> is missing data.

where the row # is the original row # of the import file.
 
3.6P1If any value under Trans.Date column is not in format of YYYY/MM/DD if User is using TSV importer, then Mifos displays an error message for each row this occurs.

Transaction date value in Row <#> does not follow expected format (YYYY/MM/DD).

where the row # is the original row # of the import file. 

No error message will be thrown to validate date format when using Excel importer since the importer will check for correct date format automatically.
 
3.7P1If any value under the Serial column is not numeric (ie text or special character) then Mifos displays an error message for each row this occurs.

Serial value in Row <#> does not follow expected format.

where the row # is the original row # of the import file. 
 
3.8P1If any value under the Description column does not follow the format PMTMAJ<2 letter code><5 digit loan id for external id> or <7 digit account id> or <15 digit loan id for Mifos loan id> then Mifos displays an error message for each row this occurs. Mifos should throw an error message when:
  1. 1. PMTMAJ<two-letter-code> does not precede some numbers. If the numbers that follows is not 5 digits. 2. If number of digits without <two-letter-code> is not 7 digits or 15 digits.
  2. If the numbers that follows is not 5 digits or 15 digits. 



    The following error message should be displayed.

    Loan ID could not be extracted from Row <#> where the row # is the original row # of the import file.
 
3.9P1Check if the same file name has been imported.  If so, then throw an error message and reject the whole import.



Same file name has been imported.  Please import a different file.
 
3.10P1After clicking on Continue, Mifos will display the Review & Submit screen with the following:

Review the information below.  Click Submit if you want to continue with import or click Edit to make changes. Click Cancel to return to Admin page without submitting information.



Import information



Import file name: <name of file>

Import Status: <#> rows contained no errors and will be imported.



where <#> is the number of valid rows being imported. 

See mockup.
 
3.11P1If any error messages apply, then Mifos adds the messages line by line.



The following rows contained errors and will not be imported:
  • Row <x> is missing data.
  • Serial value in Row <x> does not follow expected format. and then the error messages are displayed one per line.
 
3.12P1User can then either
  1. Click on Edit import information to go back to previous screen and upload new file.
  2. Continue with import and Submit.
  3. Cancel out of the workflow (returning to Admin screen).
 
3.13P1If User clicks on Submit, Mifos imports the file and displays confirmation screen - see mockup -> 
3.14P1There is no option to revert a file upload once it has been submitted. 
3.15P1There is no checking of duplicate rows (see 3.9) 
3.16P1Currency of values imported is directly inherited from the loan product that the loan account is mapped to. 
3.17P2If there are no rows found with import data, the following error message should be thrown:



No rows found with import data.
 

 

Audi Bank Import Columns and Description

Column NameRequiredDescriptionCommentsValidationsRangeExampleMaps to Mifos
Trans.DateYesPayment date Validate date is in range (see above)YYYY/MM/DD (for tab-delimited files)2009/09/01Transaction Date
SerialYesreference number for the transaction Only validate value is numeric (see above) 1234567Serial Number (internal field in DB)
Value DateNo Ignore this column    
ReferenceNo Ignore this column     
D/CYesDebit or CreditOnly import rows with CNo validation - only import rows with CD or CCNone
AmountYestransaction amountcan be in USD or LBP, currency is not indicatedOnly validate amount is a numbernumeric number2577Transaction amount
BalanceNoignoreIgnore this column    
DescriptionYescontains the loan idOnly import rows with A, Z, or C in the 2nd letter of <two-letter-code>



Need to extract the loan id from this row

loan id is 5 digits or 15 digits
Only validate if the loan id can be extracted for rows with A, Z, or C - so if format is incorrect and loan id cannot be extracted, display error message (see above)PMTMAJ <two-letter-code><5 digit external loan id> or <7 digits account id> or <15 digit mifos loan id><space><two-digit LA code> <client name>PMTMAJ EC12345 82 Joe Smith

PMTMAJ 1234567 82 Joe Smith

PMTMAJ123456789012345682 Joe Smith

External ID

Loan Repayment Details

FR#PriDescriptionComments / Mockups
4.1P1Assumption: All transaction detail is for loan repayments (loan fees, interest, principal). There is no data in file for loan disbursals, savings withdrawals or deposits, client, group or center fees, or client attendance. 
4.2P1Trans.Date is actual repayment date recorded 
4.3P1Amount that is being paid - pre-payments and partial payments are allowed.  Amount in file is applied in the following order - Penalties, Fees, Interest, Principal. 
4.4P1Trans. Date cannot be after today in Mifos 
4.5P1Mode of Payment must be configured in Mifos to have Bank Audi sal, which will be the mode of payment used for all transactions in that import. 
4.6P1If there is an overpayment, Mifos should thrown an error. 

Other Assumptions

LSIM

  • LSIM will be on - but should work on or off

QA Considerations

  • Performance History - PAR, other values calculated correctly
  • View Loan Account Details - correct dates and values imported
  • Transaction History - each transaction should still be logged, with the user who did the import

Standard Considerations

Security (Permissions, Roles, and Data Scope)

Does the user need to be in a particular user hierarchy to use this feature?No
Does the office hierarchy affect use of this feature?No
Are you using any existing permissions to control this feature?No
Are you adding any new permissions or changing existing permission to control this feature?Yes - adding new permission Can import bank transactions
Are you using any existing activities to control this feature?No
Are you adding any new activities or changing existing activities to control this feature?No
Are there any special considerations for upgrade scenarios?  What will be the default value for new permissions?No special considerations.  Default value is checked in ADMIN role and unchecked for all other roles.
What will be the default values for default roles in a new installation? 

Impacts to System

Does this feature affect Bulk Loan Creation?  How?No
Does this feature affect Collection Sheet Entry?  How?No
Does this feature affect Redo Loans?No
Does this feature affect Undo Loans?No
  

Globalization/Localization

Will this feature support users localizing data that they enter? 
Does this feature involve any date/time related data, and if so how should conversions be handled? 
Is there currency or other numeric data ? If so does it require any special handling or validation? 

Logging

Change Log

Do changes to the data that is collected or stored by the new feature have to be fully logged by the system?Yes
Does the administrator configuring the system need the ability to turn on or off logging for this feature?No
Is the feature currently logged but the structure of the logged records changing?No

 

Reporting

Provide any relevant information about reporting requirements for the new features and answer the questions below, providing detail to explain any particular area when necessary.

Does the feature affect any existing reports?No
Does the feature require adding any new reports?No

Performance

Will the feature be a high use-case scenario?No
Will the feature have potential for high concurrency?No
Does the feature include complex UI or data gathering logic that will be used by a significant portion of the user base?No
Does the feature contain risks of database connection timeout or ASP page timeout? 
Will the feature contain any bulk insert/update/delete transactions? 
Will the feature contain any caching mechanisms or cache refreshing mechanisms?Unclear
Could the feature result in a large amount of data being sent to the client or between the database and web server?Yes
Would users on a low bandwidth connection likely face issues with a part of this feature?Depends on size of file
Does the feature affect existing batch jobs or require adding any new batch jobs?Unclear

Setup and Installation

New Installations

Will the feature include demo data?No
Does the feature require any data to be gathered at setup runtime?No

Backward Compatibility and Upgrades

Is there any data conversion that needs to be done as part of an upgrade? 
Will customers lose data or will the way existing data is stored change significantly?No
Will another feature, workflow or portion of the data model be deprecated as a result of this new feature?No
Will existing role permissions be changed or impacted by this feature? If so provide details in the security section.ADMIN role should be updated to have this permission checked.
Will existing customers need to learn a new UI process or change the way they use the system as a result of this new feature?No

Hosting Support

If different user groups are using the same database, are there concerns over the sharing of data related to the feature? 
Are there expected to be performance related issues with having many customers sharing the same hardware in support of this feature? 

Configuration

Does this feature require changes to configuration files?No
If so, is this feature enabled or disabled by default?N/A

Open Questions and Notes

Answered

  • we need a sample of what data can be dumped from the banking system
  • Is it one file for the whole MFI or one file per branch office?
    • one file per currency per bank (there are currently 5 banks) - SB
  • Is this one person w/ an Administrator role doing this at the HO
    • It will be more than one user in the HO accounting department, not all of which should be admins
  • What is the name of their bank?
    • Audi Bank is the main one
  • Is it only Audi Bank or BSL too?  Both are referenced in some of the sentences
  • How important is it to share what type of error it is and at what level does it need to be at if it's necessary - ie do we need to tell what row the error occurs on or can we just say generically what the error is?
    • Medium important, for each row that has an error, an accountant is going to have to examine it and manually correct it, possibly after calling a bank or branch. So, it would be better to have Mifos report which rows have errors, otherwise the accountant will have to manually scan the import files and keep trying to import until she finds all the errors.
  • Does Excel format mean it won't accept Unicode (Arabic) characters?
    • Not sure, but it doesn't matter. We only need to import (loan id, bank ref #, trans date, amount), none of which will have Arabic characters.
  • Typical size of file - will this have impact on performance? - Load of transactions - 1500 transactions/file?
    • please see samples attached to MAJ:Bank Imports , these are representative of a typical day. Keep in mind though, the growth projections for MAJ:Al Majmoua
  • Can we get access to the bank's website?
    • they probably won't give us their online banking password, but just let me know what you need and I will ask their accounting department.
  • I deleted the spec I had in progress from mifos.org in lieu of what you said (Sam) about Al M's desire to keep information away from competitors.  We can revisit later what we can and cannot put on mifos.org
    • thank you!!!
  • Do we need to have the Excel format converted first to a different format for easier/quicker development of this feature?
    • whatever is best for feature development.
  • Current spec is assuming option 2 of multicurrency (products have been created and entered in respective currency)
    • interesting... let's catch up on multi-currency then, I thought option 2 was a no-go when we discussed it Monday

can we get the bank to only put loan id in the description field? No

Unanswered

  • credit rows in Audi with no loan id - are these reversals, or missing loan ids?
  • look at Audi USD line 202
Audi Bank Generic Import 
ProsConsProsCons
FasterRestricts us to adding one bank at a time in the futureCan accommodate all 5 of Al Majmoua's banksWill take longer to implement
    

Review and Approvals

 

DateNameRoleStatus
  PM 
  Dev 
  QA