[Mifos-users] Data Migration templates

[Maina Wachira]

Hi Everyone,

 

It is my hope that this finds you well wherever you are. I had a couple of questions on the data migration.

I wanted to find out if there is anyone who has been able to migrate data into Mifos (excel spread sheets) and the problems they may have encountered and how they went about it.

I have divided the questions as below,

1. What data migration templates did you use? (What information was being captured in the templates?)
2. What are the issues you may have encountered?
3. How long did it take?

I got some information from this page but I wanted to find out from the user list who has been able to go about it and the challenges they may have faced.

If I come up with other questions I will share them ASAP. I need to deploy Mifos at a client’s by the end of March and they have about 16,000 clients.

Thanks for any and every assistance.

 

Kind Regards,

Maina Wachira.

 

[Sam Birney]

Hi Marina,

great to hear of another Mifos deployment in the works. As Ryan
alluded to
in another message, I have recently done some data migration at Al
Majmoua.

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

The basic idea is pretty simple:

0. do you base mifos configuration and export a clean database.

1. 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).

2. 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.

3. 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 and because i am
reasonably
practiced at it.

4. 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.

5. run the script, test, make changes, revert to your exported
database, run again,
etc., repeat as needed, voila, finito!

to answer your questions,

1. 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.

2. 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 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.

3. 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 Mifos quirks.

I'm happy to share some of the scripts with you as a reference, but it
might be better
to just try writing your own than reading mine. I can also give a
little advice along the way
if you like.

Unfortunately it is not a generally reusable 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.

hope this helps,
Sam

On Nov 9, 2:37 pm, "Maina Wachira" <ma...@adeptsystems.co.ke> wrote:
> Hi Everyone,
>
> It is my hope that this finds you well wherever you are. I had a couple of
> questions on the data migration.
>
> I wanted to find out if there is anyone who has been able to migrate data
> into Mifos (excel spread sheets) and the problems they may have encountered
> and how they went about it.
>
> I have divided the questions as below,
>

> 1.      What data migration templates did you use? (What information was

> being captured in the templates?)

> 2.      What are the issues you may have encountered?
> 3.      How long did it take?

>
> I got some information from this page but I wanted to find out from the user
> list who has been able to go about it and the challenges they may have
> faced.
>
> If I come up with other questions I will share them ASAP. I need to deploy
> Mifos at a client's by the end of March and they have about 16,000 clients.
>
> Thanks for any and every assistance.
>
> Kind Regards,
>
> Maina Wachira.
>

> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>
> _______________________________________________
> Mifos-users mailing list
> Mifos-us...@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/mifos-users

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Mifos-users mailing list
Mifos...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users

[Sam Birney]

Hi Natu, 

(sharing with the mifos-users list in case it helps others or anyone else has suggestions)

>Did you do the open balances automatically  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.

Sam

On Thu, Jul 8, 2010 at 6:02 PM, Natu Lauchande <nlauc...@gmail.com> wrote:

Hi Sam,

Thank you very much for your answers. I still have some questions on the answers that you gave.

On Thu, Jul 8, 2010 at 10:01 AM, Sam Birney <sam.b...@gmail.com> wrote:

Hi Natu,

it was successful using selenium for data migration.  For your questions-

* backdate payments -- the approach we chose was to only migrate opening balances (no historical payments).  So, if the loan was $1000 and $300 was already paid, then we created a migrated loan for $700, and we marked in the "historical data" page that the original loan was actually $1000.

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

* meeting schedules -- we used "Loan Schedule Independent of Meeting Schedule" (LSIM) configuration

* mandatory fields -- when we did not have data to migrate for some fields, we tried to make them non-mandatory for migration (and then set them back to mandatory for data entry of new loans where desired).  Where that was not possible, we just had the selenium script enter "N/A" or "X".

hope this helps,

Sam

Regards,
Natu

[Gayl Kennedy]

That is very interesting. Did you also have a script to load the
groups / clients data or was that done manually?
Are the template available?

Thanks for sharing,
Gayl

--
Regards
Gayl

------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first

[Natu Lauchande]

Hi Sam,

Your approach with sellenium works really very well at least for clients and groups, thank you very much, is actually safer and more easy than playing around with sql inserts.


Regards,
Natu

[Sam Birney]

Gayl, Natu,

congrats on getting it working Natu!

there is a version of the data migration templates I worked on in the
Mifos source control repository:

/documents/deployment/AlMajmoua/migrator

these are very specific to one MFI (because of configuration, custom
fields, business rules, etc.) so I think it is better to record &
write your own selenium scripts for other MFIs.

Sam

> > On Thu, Jul 8, 2010 at 6:02 PM, Natu Lauchande <nlaucha...@gmail.com>wrote:
>
> >> Hi Sam,
>
> >> Thank you very much for your answers. I still have some questions on the
> >> answers that you gave.
>

> >> On Thu, Jul 8, 2010 at 10:01 AM, Sam Birney <sam.bir...@gmail.com> wrote:
>
> >>> Hi Natu,
>
> >>> it was successful using selenium for data migration.  For your questions-
>
> >>> * backdate payments -- the approach we chose was to only migrate opening
> >>> balances (no historical payments).  So, if the loan was $1000 and $300 was
> >>> already paid, then we created a migrated loan for $700, and we marked in the
> >>> "historical data" page that the original loan was actually $1000.
>
> >>> Did you do the open balances automatically  automatically ? If yes what
> >> was your approach.
>
> >>> * meeting schedules -- we used "Loan Schedule Independent of Meeting
> >>> Schedule" (LSIM) configuration
>
> >>> * mandatory fields -- when we did not have data to migrate for some
> >>> fields, we tried to make them non-mandatory for migration (and then set them
> >>> back to mandatory for data entry of new loans where desired).  Where that
> >>> was not possible, we just had the selenium script enter "N/A" or "X".
>
> >>> hope this helps,
> >>> Sam
>
> >>> Regards,
> >> Natu
>
>
>

> --------------------------------------------------------------------------- ---

> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first --http://p.sf.net/sfu/sprint-com-first
>

> _______________________________________________
> Mifos-users mailing list
> Mifos-us...@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/mifos-users

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

This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first