Selenium Scripts for Data Migration

The following consists of tips and guidance for migrating historical data into Mifos via Selenium scripts that are based off of the approach outlined by Sam Birney in this mailing list post. He used this technique while migrating data at Al Majmoua in Lebanon. This approach was utilized by Natu Launchande while deploying Mifos at Associacao Progresso in Mozambique.

Migrating Data via Selenium

First of all, watch this two minute intro at the top of this web-page: http://seleniumhq.org/

The basic idea is pretty simple:

Do your base mifos configuration and export a clean database.
Export data from the legacy MIS into an excel spreadsheet. Save the spreadsheet as a tab-delimited unicode text file (in our case), but there are many different ways to kill this rat (thanks for the colorful expression).
Use Selenium IDE to record the Selenese code for creating a new client, approving him/her, creating a loan, approving the loan, disbursing the loan.
Export this Selenese into the programming language of your choice, whatever is easiest for you to work with. I chose python because it is very good at parsing text files and manipulating strings and lists, also, because I am reasonably experienced with it.
Edit the exported code to basically do a for loop that reads each line of your exported data file and then performs the actions you recorded in the UI, passing in variables from the fields you read from the file.
Run the script, test, make changes, revert to your exported database, run again, etc., repeat as needed, voila, finito!

Sample Scripts to Download

Scripts used for the Al Majmoua deployment can be downloaded here. (Note: these are very specific to one MFI (because of configuration, custom
fields, business rules, etc.) so I think it is better to record and write your own selenium scripts for other MFIs).

Tips and Advice

What data migration templates did you use? (What information was being captured in the templates?)

We exported everything we could from the old MIS and put it into either Mifos standard fields or custom fields: i.e. client name, birthdate, loan officer, gender, etc.

What issues were encountered?

No major issues came up as problems, but there have been tons of little details...things like fields we don't want to be mandatory that currently are, not being able to disburse loans for a future date, meeting schedules, etc, but we found a solution for all of them,one by one.

How long did the approach take?

It only took me a couple days to get the basic client data migration automated - a few more days for individual loans and a few more days for group loans (which were a little trickier, but by then I had a good feel for Selenium). In fact the basic idea was working within a few hours of starting, all those days were just for the various details of this or that field or for Mifos quirks.

Unfortunately it is not a general re-usable tool at this point because it depends a lot on the specific configuration. For example, we are using Group Lending with Individual Monitoring, Loan Schedule Independent of Meeting Schedule, no Centers, etc., which all affect the UI and therefore the scripts.

Did you do the open balances automatically? If yes, what was your approach?

We exported the principal remaining on the loan and the interest remaining on the loan as two separate values. Then the script entered the remaining principal as the loan amount in mifos and used a formula to calculate the effective interest rate for the remaining loan. The formula, for flat interest for monthly loans, was:

(remaining interest) / (remaining principal) * 12 * 100) / (installments left)

So, for example, let's say there was a 12 month loan of $1200 with 10% flat yearly interest. So each payment should be $100 principal + $10 interest. Let's say the client has already made 3 repayments.

So the script would enter a 9-month loan in Mifos with an amount of $900 and use this formula to enter a calculated interest rate of 13.3333333333%. This should result in a schedule that correctly has 9 payments left each with $100 principal and $10 interest.

This is the basic idea. We enhanced it to handle clients who were overdue by using the formula above on the scheduled amounts remaining, and then adding fees for any interest and principal that was already overdue.

So, in the example above, let's say the client had only made 2 payments out of the 3 that were due. We would still calculate the migrated loan as if 3 payments had been made, so that the schedule matches the old schedule, then we would add a $100 "overdue principal" fee and a $10 "overdue interest" fee as the script enters the loan.

Similarly, some clients were already ahead on their future payments. We still calculated the migrated loan in the same way, based on scheduled amounts, but then the script applied a payment to the loan, after approving it and disbursing it, to reflect what had already been paid in advance. So if the client above had made 4 payments even though only 3 were due, the loan was the same, but then the script applied a payment of $110.

So, to do this approach you need to export these fields from the existing data:

scheduled principal remaining
scheduled interest remaining
total principal remaining
total interest remaining
number of installments remaining

And if you have any clients with outstanding fee or penalty amounts, you can also export these fields and have your script apply them to the migrated loan.