Excel Migration Template
- 26 Jun 2023
- 13 Minutes To Read
- Print
- DarkLight
- PDF
Excel Migration Template
- Updated On 26 Jun 2023
- 13 Minutes To Read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
This article is a reference guide to the Excel spreadsheet template we provide in the Mambu UI for migrating data from legacy systems into Mambu. For more information on migration, see Data Management Overview.
The migration template is pre-filled with fields corresponding to the information you may import into Mambu.
The spreadsheet includes ten sheets, accessible at the bottom of the screen.
The following sheets are used to hold data for migration: Clients, Groups, Accounts, Schedules, Transactions, and Chart of Accounts.
The following sheets are pre-filled with information that has already been entered in Mambu: Branches, Credit Officers, Loan Products, Deposit Products. These pre-filled sheets are provided as a reference to use when you are adding data for migration. Any data you add to them will not be imported.
Any custom field definitions you have set up for clients and groups will be reflected in the relevant sheets.
General requirements
- Your import spreadsheet must be 5 megabytes or smaller. If you need to import more data than you can fit in a file of that size, you must split the data into multiple submissions and import them separately.
- Your file must have an XLSX file extension to be imported. Be sure the file is not in the XLS file extension.
- When copying data into the spreadsheet, copy only the data. Do not add or include formatting or formulas in any cell.
- Date cells must always use the format dd.MM.yyyy. Any other format will fail validation.
- Input fields have set character limits. Fields that contain Entity IDs have a 32-character limit. Most other text fields have a 255-character limit.
Clients
This sheet is used to submit client data. It includes cells for any custom field definitions that you have previously created in Mambu.
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| Client ID | The alphanumeric code corresponding to that client's existing ID. |
| Group ID | The alphanumeric code for the group the client is a member of. |
| Branch ID | The alphanumeric code for the branch the client is assigned to. |
| Centre ID | The alphanumeric code for the centre the client is assigned to. |
| Credit Officer username | The username of the credit officer with whom the client will be associated. |
| Date Joined (dd.MM.yyyy) | The day, month and year the client joined your organization. |
| First Name | The client's given name. |
| Middle Name | The client's middle name. |
| Last Name | The client's surname. |
| Date of Birth (dd.MM.yyyy) | The day, month and year the client was born. |
| Gender (M/F) | The client's gender, M for male or F for female. |
| Address 1, Address 2, City, Zip, State/Province/Region, Country | Address information used to locate the client. |
| Mobile/Cellphone | The client's mobile phone number. |
| Phone | The client's phone number. |
| 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 | The type of identification document, for example, passport, ID card, or any other official document. |
| ID Number | The unique number of the ID document. |
| ID Authority | The authority that issued the document, such as police or government. |
| ID Valid Until (dd.MM.yyyy) | The expiration date of the document. |
| Custom Fields | The last columns contain any custom field definitions that you previously created in Mambu. If it is a selection field, you must enter one of the required custom field values you specified when setting up the custom field definition, see Selection custom field definition. |
Groups
Group ID and Group Name are mandatory fields.
| Field | Description |
|---|---|
| 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 the group is assigned to. |
| Credit Officer Username | Username of the credit officer the 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. |
| Address 1, Address 2, City, Zip, State/Province/Region, Country | Address 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. |
Loan accounts
Please be Aware
Solidarity or Hybrid Group loan accounts cannot be imported into Mambu - we only support importing Pure Group loan accounts. For more information, see Loans for Groups.
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| 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 | The client type, either C for individual client or G for group. If you leave this empty, it will be individual 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 repayment period is set when you configure your loan products. |
| Repayment Frequency: | How often the installments will be, in the number per repayment period. If, for example, repayment occurs once per month, you would enter 1. If every 2 weeks, you would enter 2 and so on. The repayment period is set when you configure your loan products. |
| 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 institution to borrowers for the use of a loan amount. When entering this information, you should only include the number. For an interest rate of 12%, for instance, enter 12. |
| Loan Amount | The original amount of the loan. |
| Interest and Principal Paid | How much interest and principal has been paid. If nothing has been paid yet, enter 0. |
| Account State | The loan state: Active, Closed, Withdrawn, Rejected, Written Off, Pending Approval and Approved. |
Loan Schedules
You may import schedules for fixed loan accounts, but you cannot import dynamic loan schedules - they are always recalculated based on Mambu logic.
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| 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 ascending order for the same loan. |
| Principal Expected | Amount of principal expected for the installment. If nothing is expected yet, enter 0. |
| Interest Expected | Amount of interest expected for the installment. If nothing is expected yet, enter 0 and the interest will be marked as Grace. |
| Fees Expected | Amount of fees expected for the installment. If nothing is expected yet, enter 0 and the principal will be marked as Grace. |
| Penalty Expected | Amount of penalties expected for the installment. If nothing is expected yet, enter 0. |
Loan schedule notes
- Mambu will use the interest, fees, and penalties specified in the import sheet, even if the product has other interest/fees/penalties settings.
- If an imported loan has a schedule specified in the import sheet, do not generate another schedule. Use the one specified by the user.
- Mambu will update the account dues and balances according to the imported schedule and the repayments states and paid amounts after the loan is imported.
- Payment due fees are not applied on imported schedules.
- Late fees are not applied on the schedule for the installments that are late at the time of import.
Loan Transactions
If you wish to recreate previous loan schedules and full transaction histories, you may import past transactions.
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| Account ID | Alphanumeric code which you can customize and that is unique for each loan account. |
| Transaction Type | Options include 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. |
Loan transaction notes
- 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.
- If any transaction is provided, the Interest and Principal Paid amount from the loan account are ignored.
- 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.
- Fees can be applied regardless of the product's arbitrary fees setup.
- Penalties: The product setup (rate and calculation method) are not considered when imported amounts are applied.
- If taxes are enabled for the product they should be included in the amounts (interest/penalty/fee).
- All transactions require a date. A date validation must 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.
- The total number of imported transactions will be shown in the Import Events table.
- No accounting is logged for imported transactions.
Deposit Accounts
Please be Aware
The dates for last interest calculation, latest interest storage, and latest account appraisal will be set to the time of the migration.
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| 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 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 balance at the moment of the import. This balance will be imported as a Deposit transaction with an Entry Date that is the same as the "Date Approved" in the import file. Please note: 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. |
Chart of Accounts
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| 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: D for detail, or H for 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. |
Please be Aware
If balances are added for the GL sheet, debit amount and credit amount must be equal.
Centres
Mandatory fields are in bold text below.
| Field | Description |
|---|---|
| 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 with. |
| Name | The 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. 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 with the centre. |
Data (for reference only)
The sheets in this section are populated with data that is already stored in Mambu. They are provided as a convenience to make it easier to fill out other sheets.
For example, when you are filling out the Client or Groups sheets, you will need to know the IDs of the branches some of the values are associated with. You can easily find that information in the provided Branches Data sheet.
Any changes you make to these sheets will be ignored during import.
Branches Data
This sheet includes data you have previously entered about your organization's branches.
Branch IDs are automatically generated when you create a new branch in Mambu.
Credit Officers
This sheet includes data you have previously entered about your organization's credit officers.
Credit Officer IDs are automatically generated when you download the template so that you can use it as a reference.
Loan Products
The information displayed in this sheet includes the data you entered in the system when creating your loan products.
Loan product IDs are not automatically generated - they must be defined when creating your loan products.
Deposit Products
The information displayed in this sheet includes the data you entered in the system when creating your deposit products.
Savings product IDs are not generated automatically - they must be defined when creating your deposit products.
ID consistency and validation
In order to pass validation, the information in the sheets must be consistent. The following validations will be performed.
Clients sheet validation
The Group ID for clients who are members of a group, the Branch ID, the Centre ID, and the Credit Officer ID must correspond to the Group, Branch, Centre, and Credit Officer IDs in the following sheets: Groups, Branches, Centres, Credit Officers.
Groups sheet validation
The Branch, Centre, and Credit Officer IDs you provide here must match the corresponding values in the Branches, Centres, and Credit Officers sheets.
Loan Accounts sheet validation
The Client ID and the Product ID must match the corresponding fields in the Clients and Loan Products sheets.
Deposit Accounts sheet validation
The Client ID and the Product ID must match the corresponding fields in the Clients and Deposit Products sheets.
Centres Sheet validation
The Branch ID must match the ID in the Branches sheet.
Please Note
Clients and credit officers do not need to be assigned to the same branch that their group is assigned to.
Custom fields
If you have added custom field definitions to clients, groups, users, or accounts in Mambu, you will find a column for each field in the migration template.
For the custom field definition types Free Text, Number, or Selection, enter the data as it should be displayed in Mambu. For Checkbox custom field definitions, enter True if the box should be checked and False if it should not.
Was this article helpful?
