Looking at Defaults and Average Balance from the MIX

*This is from my bi-weekly post on Lumana.org/blog

Lately I've been working with a lot of MIX Market data in order to see trends in the microfinance industry around the world. In today's post, I'm looking at the average write-off percentage in comparison to the average loan size.

By advancing the years on the control next to the map below, you can see that from 1995 to 2009 the average size of loans (denoted by the shade of orange) around the world has increased, and the average default rate has also increased (denoted by the size of the circles). The same information is in the line graph only reveals that, when looking at all MFI's in aggregate, the write off percentage does increase linearly - although it does around that industry touted benchmark of 2%.

So, it appears as though MFIs are providing larger and larger loans every year despite the global recession. Is this due to inflation within each market, or is it merely a result of better reporting? (We can see the number of MFIs reporting balances in 2005 was only 2, compared to 1,146 in 2008.)

What is comforting to see is that in the third picture, the heat-map, I've used Excel and broken average loan size out by $100 and plotted the maximum default rate for each year. As we would hope, it is a boring and predictable chart showing that as average loan size rises, there tends to be a lower default rate.

Powered by Tableau

Excel VB Script For MIX Market

I’ve been working with a lot of microfinance data from the "trends" section of the MIX Market lately and unfortunately the .csv file is not organized in a way that can be easily exported into pivot tables or data visualization software such as Tableau.  So, I’ve written this little macro to compile the data into the necessary columns for working with and made it available to you.  It should work with most aggregate tables you download from the site. The only thing you need to be sure of is that the source worksheet is titled "Sheet1" and the worksheet for compiling the data is titled "Sheet2". It is a super simple macro, but now I’ve written it so you don’t have to.  

Sub Compile() ' Clear Clipboard and Clear Content From Sheet2 Application.CutCopyMode = False Sheets("Sheet2").Select Cells.Select Selection.ClearContents ' Column Headers Sheets("Sheet2").Select Range("A1").Select ActiveCell.FormulaR1C1 = "MFI ID" Range("B1").Select ActiveCell.FormulaR1C1 = "NAME" Range("C1").Select ActiveCell.FormulaR1C1 = "COUNTRY" Range("D1").Select ActiveCell.FormulaR1C1 = "YEAR" Range("E1").Select ActiveCell.FormulaR1C1 = "NUMBER" Range("A2").Select ' Paste MFI ID, NAME, COUNTRY Counter = 15 Do While Counter > 0 Sheets("Sheet1").Select Range("C2").Select Range(Selection, Selection.End(xlToLeft)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Counter = Counter - 1 Loop ' paste date setup Counter2 = 15 Sheets("Sheet2").Select Range("D2").Select Sheets("Sheet1").Select Range("D1").Select ' paste date Do While Counter2 > 0 Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1753, 0).Select ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select ActiveCell.Offset(0, 1).Select Counter2 = Counter2 - 1 Loop 'paste number Sheets("Sheet1").Select Range("D2:D1755").Select Selection.Copy Sheets("Sheet2").Select Range("E2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("E2:E1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("F2:F1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("G2:G1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("H2:H1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("I2:I1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("J2:J1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("K2:K1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("L2:L1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("M2:M1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("N2:N1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("O2:O1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("P2:P1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("Q2:Q1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select Sheets("Sheet1").Select Range("R2:R1755").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste ActiveCell.Offset(1754, 0).Select End Sub

Dashboards

Dashboards are the type of thing I stay up for days on end for and miss meals sometimes because I'm so excited about building one. Most of the time, these dashboards are in MS Excel although recently I had the opportunity to build quite a few in Tableau.

However this post is about Google Wave. I mentioned in my last post that I was looking for a way of tracking my projects. Additionally, my microfinance company, Lumana, uses Google Wave almost exclusivley for document generation and archiving. With at least 10 volunteers at all times creating documents, my inbox is easily flooded.

The simple solution is to create a dashboard that links to all related waves. All you need to drag and drop the wave onto the dashboard wave and it automatically creates a hyperlink. Then, simply store the dashboard wave in a dashboard folder so that it's easy to locate. I also created a wave that acts as my "journal", similar to a journal in MS Outlook. Only, I manually link to all the project waves, emails, and websites that I've been working on.