Support Center

Excel Template

Last Updated: Sep 05, 2017 06:23PM CEST

 

Overview

Clients

Groups

Loan Accounts

Loan Schedules

● ​Loan Transactions

Deposit Accounts

Chart of Accounts

Centres

Reference Tables

ID Associations

Custom Fields

Required Fields

 

------------------------------------------------------------------------------

 

Overview

The Excel template that you download for the migration process has different fields corresponding to the information you are trying to import into Mambu. In the bottom you can see that there are ten different sheets - Clients, Groups, Accounts, Schedules,Transactions, Chart of Accounts,Centres, Branches, Credit Officers, Products - to structure the data you'll enter. This is a quick reference guide that will help you making sure your data is exactly where it should be.

 

 All the custom fields for clients and groups that you've entered in Mambu before downloading this template will be displayed in the correspondent sheets so that you can also fill in that information.

 Important! The format of the data must be kept intact, please paste special with "only data" and have  no Formulas in the cells. For instance, the Date cells should be 03.04.2001 and not =DATE("2001,3,4"). 

 Please note that only XLSX files are accepted for the validation process. Please make sure the file you upload is not the XLS format.

 Import Excel files need to be less than 5mb to be able to be imported successfully, if your file is larger please split it in different 5mb tranches.


------------------------------------------------------------------------------ Back to top

 

 

Clients


Here you'll find all the fields you need to fill in with your clients data, including the custom fields you've created before.

* = Mandatory Field

 

 

Client ID *: is the Alphanumeric code corresponding to that client's existing ID.
 

Group ID: is the Alphanumeric code that corresponds to the group that client is a member of.
 

Branch ID: is the Alphanumeric code corresponding to the branch that client is assigned to.
 

Centre ID: is the Alphanumeric code corresponding to the centre that client is assigned to.
 

Credit Officer username: is the username of the credit officer who the client will be associated with in Mambu.
 

Date Joined (DD.MM.YYYY): The day, month and year the client joined your organization. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
 

First Name *: the given name.
 

Middle Name To be filled if the client has middle names.
 

Last Name *: the surname.
 

Date of Birth (DD.MM.YYYY): The day, month and year the client was born. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
 

Gender (M/F): M-male or F-female.
 

Address 1, Address 2, City, Zip, State/Province/Region, Country: contact information used to locate the client.
 

Mobile/Cellphone: mobile phone number that can be used to contact the client.
 

Phone: the land line phone number that can be used to contact the client.
 

Notes: any additional comment you want to add.
 

Individual Loan Cycle: The number of individual loans the client has already completed.
 

Group Loan Cycle: The number of group loans the client has already completed.
 

ID Type: Examples could be passports, ID card, or any other official document.
 

ID Number: the unique number of the ID document.
 

ID Authority: the authority that issued the document (police, government).
 

ID Valid Until (DD.MM.YYYY): the expiry date of the document.
 

Custom Fields: the last columns will contain the custom information fields you created before in Mambu. If it’s a Selection field you have to write the same values you chose in the Mambu Settings.

 

------------------------------------------------------------------------------ Back to top

 

Groups

 

* = Mandatory Field

 

 

Group ID * : Alphanumeric and unique code used to identify the group.
 

Branch ID: Alphanumeric and unique code of the branch that group is assigned to.
 

Centre ID: Alphanumeric code of the centre that groups are assigned to.
 

Credit Officer Username: Username of the credit officer that group is assigned to.
 

Group Name * : Name which identifies that group.
 

Date Joined (DD.MM.YYYY): The day, month and year the group joined your organization. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
 

Address 1, Address 2, City, Zip, State/Province/Region, Country: contact information used to locate the group.
 

Notes: any additional comment you want to add.
 

Group Loan Cycle: The number of loans the group has already completed.
 

 Currently Mambu only supports the import of groups using the Pure Group method. If your groups will be using the Hybrid Group method, you should create the groups manually in Mambu after importing or creating the individual clients.

 

------------------------------------------------------------------------------ Back to top

 

Loan Accounts

 

* = Mandatory Field

 

 

Account ID *:  Alphanumeric code which you can customize and that is unique for each loan account.
 

Client ID * : Alphanumeric code of the client the account is for.
 

Client Type * : C-Individual Client or G-Group. If you leave this empty, it will be Client by default.
 

Product ID * :The alphanumeric code of the loan product the account is associated with. The product ID is defined when you create the products and you can see it in the Loan Product sheet.
 

Date Applied * : Date when the client applied for the loan.
 

Date Approved * : Date when your organization approved the loan account.
 

Date Disbursed * : Date when the loan account was disbursed.
 

Repayment Start Date * :The day of the first loan repayment.
 

# Grace Installments: the number of grace installments for the loan account, if any.
 

Loan Length (# Installments) * : the number of repayments of the account. The time unit you chose in the Loan Product will be the criteria to be followed (i.e. months, days, weeks...).
 

Repayment Frequency: How often the installments will be. If, for instance the repayments occur once per month, you would enter "1". If every 2 weeks, you would enter 2 and so on.
 

Repayment Period (D/W/M/Y): The unit of time used for the Repayment Frequency. If repayments will be made every 10 days, for instance, then the Repayment Period would be D (D=days, W=weeks, M=months, Y=years).
 

Principal Interval: If the principal amount should be paid in every installment, you would enter 1. If, for instance, principal would only be paid every 6 installments, enter 6.
 

Interest Rate * : The fee in percentage that is charged by your MFI to borrowers for the use of a loan amount. When entering this information, you should only include the number. So for an interest rate of 12%, for instance, you should only enter 12.
 

Loan Amount * : the original amount of the loan.
 

Interest and Principal Paid * : how much of interest and principal has been paid. If nothing has been paid yet, you should enter 0.


Account State: the loan state: Active, Closed, Withdrawn, Rejected, Written Off, Pending Approval and Approved.

 

------------------------------------------------------------------------------ Back to top

Loan Schedules

For organizations that are trying to recreate their loan schedule, it is possible to import for fixed loan accounts .

* = Mandatory Field





Account ID*Alphanumeric code which you can customize and that is unique for each loan account.

Due Date*: The installment due date;must be in an ascending order for the same loan.

Principal Expected*:  how much of principal is expected for the installment. If nothing is expected yet, you should enter 0. 

Interest Expected*: how much of interest is expected for the installment. If nothing is expected yet, you should enter 0 and the interest will be marked as Grace.

Fees Expected: how much in fee is expected for the installment. If nothing is expected yet, you should consider it 0 and 
the principal will be marked as Grace. 

Penalty Expected: how much of penalty is expected for the installment. If nothing is expected yet, you should consider it 0. ​


Note: User cannot import Dynamic Loan schedules ; schedules of dynamic loans will always be recalculated based on Mambu logic.
 

Requirements / Implications

 1. The system will use the interest, fees and penalties specified in the import sheet, even if the product has other interest/fees/penalties settings

 2. If an imported loan has a schedule specified in the import sheet, don't generate another schedule, but use the one specified by the user

 3. Mambu updates the account dues/balances accordingly to the imported schedule and the repayments states/paid amounts after the loan is imported:
  • Payment due fees will not be applied on the imported schedules 
  • ​Late fees will not be applied on the schedule, for the installments that are late at import moment

------------------------------------------------------------------------------ Back to top
 

Loan Transactions

For organizations that are trying to recreate their original loan schedules and full transaction history, it is possible to import past transactions.

* = Mandatory Field

 

Account ID*: Alphanumeric code which you can customize and that is unique for each loan account.


Transaction Type*: One of either DISBURSEMENT, REPAYMENT, FEE or PENALTY.


Date*: Date of the transaction.


Amount*: Depending on the transaction type, how much principal was paid / disbursed / fee applied / penalty applied.



Notes: Any free text notes to be attached to the transaction.


Requirements / Implications

  1. If a disbursement transaction is provided, the disbursement date and loan amount (on the account sheet) are ignored. If there are transactions defined for an account, a Disbursement transaction is mandatory.
     
  2. If any transaction is provided, the Interest and Principal Paid amount from the loan account are ignored.
     
  3. If the account is fixed:
    • Fees will be applied on the first unpaid installment. If the whole schedule is paid, an error message will be shown.
    • Penalties will be applied on the first unpaid installment. If the whole schedule is paid, an error will be shown.
       
  4. Fees: Can be applied regardless of the products' arbitrary fees setup.
     
  5. Penalties: The product setup (rate and calculation method) are not considered when imported amounts are applied.
     
  6. If taxes are enabled for the product they should be included in the amounts (interest/penalty/fee).
     
  7. All transactions must specify a date. A date validation should be performed:
    • Disbursement goes before fee, penalty applied or repayment transactions
    • Transactions should be ordered by their date, in ascending order (older first) - the transactions will be posted on the account on the order from the import table.
    • The transactions must be posted in a sequence for an account (all transactions for account 1, then all transactions for account 2 etc.
    • Creation date will be the approval of import event date.
       
  8. The total number of imported transactions will be shown in the Import Events table.
     
  9. No accounting is logged for imported transactions.
     
  10. It is not possible to reverse imported transactions.

------------------------------------------------------------------------------ Back to top

Deposit Accounts

 

* = Mandatory Field

 

 

Account ID * : Alphanumeric code which you can customize and that is unique for each client.
 

Client ID * : Alphanumeric code of the client the deposit account is for.
 

Product ID * : The alphanumeric code of the deposit product the account is associated with. The product ID is defined when you create the products and you can see the ID in the Deposit Product sheet.
 

Date Applied* : When the account was created.
 

Date Approved* : When the account was approved and activated. If accounts are imported with current balances, these balances will be reflected as deposit transactions with entry date same as the "Date Approved".
 

Overdraft Interest Rate: The interest rate charged on overdraft accounts. Can be zero.
 

Overdraft Limit: The maximum amount that a client can withdraw from the overdraft account.
 

Overdraft Amount Due: How much is due at the date of the import.
 

Overdraft Interest Due* : The amount of interest due at the point of the import.
 

Overdraft Fees Due: The amount of fees due at the moment of the import.
 

Current Balance: Account's balance* at the moment of the import. This balance will be imported as a Deposit transaction with Entry Date same as the "Date Approved" indicated in the import file. 

***(Please note that if you pay interest to deposit accounts and at the moment of the import there is an interest amount accrued and not applied yet, you should include this in the current balance. After the import, Mambu will start accruing interest (if applicable) on the balance that has been imported, based on the product settings)


Notes: any additional comment you want to add.
 

 

 Currently Mambu only supports the import of Current Deposit accounts. Fixed Deposits and Savings Plans will have to be created manually in Mambu.

 

The dates for last interest calculation, latest interest storage and lates account appraisal will be set as of the point of the migration event.

 

------------------------------------------------------------------------------ Back to top

 

Chart of Accounts

 

* = Mandatory Field

 

 

GL Code * : The number used to identify the account in the General Ledger.
 

Account Name * : The name so that you can identify the account.
 

Date: The date AS-OF the GL account's balance in the Balance Column. This is ideally the date of the client`s data cut off, ensuring a clean accounting record in Mambu.


Type (A/L/I/E/Q) * : select if the account is an Asset, Liability, Income, Expense or Equity.


Usage (D/H): the category of the account: Detail or Header.

 

Balance: Balance of existing GL accounts at the moment of the import.
Balances must follow the correct sign according to the type of account, as follows:

- Asset: Debit (+) / Credit (-)
- Expense: Debit (+) / Credit (-)

- Liability: Debit (-) / Credit (+)
- Equity: Debit (-) / Credit (+)
- Income: Debit (-) / Credit (+)

Notes: any additional comment you want to add.

 

        If balances are added for the GL sheet, debit amount and credit amount must be equal.


------------------------------------------------------------------------------ Back to top

 

Centres

 

* = Mandatory Field

 

 

Centre ID * : Alphanumeric code which you can customize and that is unique for each centre.
 

Branch ID * Alphanumeric code of the branch the centre is associated to.
 

NameThe centre's name.
 

Meeting Day: The day of the week when the clients who are associated to this centre meet with the credit officer. Use the initials of the appropriate day as they are shown. So for instance, M for Monday, T for Tuesday and so on.
 

Address 1, address 2, city, zip, state/province/region, country: contact information of the centre.
 

Notes: Any comments you want to have associated to the centre.
 

 

------------------------------------------------------------------------------ Back to top

 

Data for reference only

 

The following sheets contain information to be used only as a reference when you're filling the other sheets. So, for instance, when you're entering information in the Clients' or Groups' sheets, you will need to open the Branches' sheet to see which is the branch's ID that the clients are associated with.


Branches Data

This information corresponds to the data you've entered before about your organization's branches.

The branches IDs are automatically generated when you create a new branch in Mambu.

 

 

------------------------------------------------------------------------------ Back to top


Credit Officers

The information in the Credit Officers' sheet corresponds to the data you entered in the system about them.

The Credit Officers IDs are automatically generated when you download the template so that you can use it as a reference.

 

 

------------------------------------------------------------------------------ Back to top


Loan Products

The information displayed in this sheet corresponds to the data you entered in the system when creating your products.

Loan products' IDs are not automatically generated, so you need to define the ID when creating your loan products.

 

 

------------------------------------------------------------------------------ Back to top


Deposit Products

Just like for the loan products, the information in this sheet corresponds to the one you entered before in the system.

Savings products' ID number is not generated automatically, so you also need to define it when creating your savings products.

 

 

------------------------------------------------------------------------------ Back to top

 

ID Associations

So that the data migration is completely successful, the information in the ten sheets - Clients, Groups, Loan and Savings Accounts, Chart of Accounts, Branches, Credit Officers, Centres and Loan and Savings Products - in the template has to match. You can make sure it does by using the ID numbers, which ensure that the clients and groups will be assigned to the appropriate branch and credit officers in the system.

 

In the Clients sheet:

  • The Group ID (for clients who are members of a group), the Branch ID, the Centre ID and the Credit Officer ID need to correspond to Group, Branch, Centre and Credit Officers IDs in the other sheets - Groups, Branches, Centres, Credit Officers.

 

In the Groups sheet:

  • The Branch, Centre, Credit Officer ID you enter here has to be the same as the one displayed in the Branches, Centres and Credit Officers sheets.

 

In the Loan Accounts sheet:

  • The Client ID and the Product ID must match with the fields in the Clients and Loan Products sheets. 

 

In the Deposit Accounts sheet:

  • Just like for the loan accounts sheet, the Client ID and the Product ID need to match the fields in the Clients and Deposit Products sheets.

 

In the Centres' Sheet:

  • The Branch ID needs to match the ID in the branches' sheet.

 

 Nor clients or credit officers need to necessarily be assigned to the same branch their group is assigned to.

 

------------------------------------------------------------------------------ Back to top

 

Custom Fields

If you added custom fields to clients, groups, users or accounts, you will see that there will be a column for each of them in the excel template.

For any of the Custom Field types Free Text, Number, or Selection you can just enter the information as it should be displayed in Mambu.

The only exception is for custom fields of type Checkbox in which case you will have to enter the word True if the box should be checked and False if it shouldn't.

 

------------------------------------------------------------------------------ Back to top

 

Required Fields

The list below contains all fields that are required on the different sheets of the migration file, for any given row of information on the sheet all required fields must be filled for the file to pass Mambu's validation.

- Required Client Columns
Client Id
Client First Name
Client Last Name

- Required Group Columns
Group Id
Group Name

- Required Loan Columns
Loan Account Id
Loan Product Id
Loan Date Applied
Loan Client Type
Loan Client Id
Loan Date Approval
Loan Date Disbursal
Loan Date Repayment Start
Loan Length
Loan Interest Rate
Loan Amount
Loan Amount Paid

- Required Loan Transaction Columns
Loan Transaction Account Id
Loan Transaction Amount
Loan Transaction Date
Loan Transaction Type

- Required Savings Columns
Savings Account Id
Savings Client Id
Savings Product Id
Savings Date Applied
Savings Date Approval
Savings Current Balance

- Required Gl Account Columns
Gl Account Code
Gl Account Name
Gl Account Type

- Required Centre Columns
Centre Id
Centre Branch Id
Centre Name

 

support@mambu.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete