MSFT PowerPivot

For the past few months I've been using Tableau to look at microfinance data and compare it to our non-profit MFI Lumana.  Recently though, a colleague from my job at Hitachi Consulting demoed the new Microsoft PowerPivot (watch his video).  This is a free add-in for Excel 2010 that leverages the capability of pivot tables and is pretty slick.  There are a lot of advantages including a limitless row count (well, our testing so far hasn't been able to max it out), establishing a connection to multiple data sources at the same time from a wide variety of databases (Oracle, Teradata, Informix, and even Atom feeds), and of course the best part is being able to create relationships between tables.

The first picture shows the interface for viewing the tables and check the formatting, create calculated fields (measures), and manage relationships between tables (picture 2).  The final picture shows your normal Pivot Table window with the slicers (left) that allow for dynamic filtering and can be global or specific to a table or graph.  After completing your dashboard you can publish it to a SharePoint site and retain the active filtering and daily refreshing of data for others in your company.

When comparing the features of Tableau and PowerPivot I don't think one is better than the other, it just depends on your needs.  Tableau has a very beautiful interface that is incredibly easy for anyone to use regardless of comfort levels with software while PowerPivot retains the usual complexity and busy layout of MSFT software.  However, PowerPivot is a free add-in that builds on the robust capabilities of Excel which most business users already have and use daily.  

Additionally, Tableau dashboards can be published to managed servers and be viewed by anyone in any browser (or even embedded like in my previous blog posts).  In order to get the dynamic functionality of PowerPivot you have to have SharePoint which is about twice as expensive (for the basic version) per license as Tableau, but SharePoint has a tremendous amount of additional business management capabilities so you're definitely getting your money's worth.  

Finally, Tableau offers an array of useful tools such as maps, customized graphs, and allows you to display multiple measures in a single graph while PowerPivot only uses the same simple graphs Excel has always had, yet for the majority of businesses out there this is more than enough.  

So, as I said before, neither is better it just depends on your needs, but it's great to see that we all are finally getting choices on how to visualize data.

UPDATE 7/27/2010: I just read this Tableau update and it seems v 5.2 is able to connect to PowerPivot.  It also seems as though the PowerPivot sharepoint upload max size is 2GB but PowerPivotGeek.com has run files locally in PowerPivot that are 15GB - 20GB. 

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