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

Keta District (Volta Region, Ghana) Population

Here it is, the first ever interactive map for the Keta District using Tableau Software (and a mapping tip from Cleary and Simply).  In case you’re not familiar with Lumana, our microfinance operation is based in Atorkor and has officially expanded to the nearby villages of Dzita and Dzita Agbledomi, but also includes clients from Srogboe and Anloga. 

On the left is a map of the district with the size of the circle representing the size of the population.  On the right is a graph depicting population over the last 40 years of the 20th century.  Notice that the village of Keta used to have the highest population in the 60’s but has now fallen severely.  This is mostly due to land loss from erosion.  During my stay in Ghana, I observed that many of the villagers were full of children and grandparents.  What the growth rate doesn’t reveal is how many young adults are leaving rural areas for opportunities in the cities, and sending children to live with the grandparents back in the village.  For that we would need to look at age composition over time, but unfortunately I only have data for the year 2000 as you can see in my last post. 
* Click on any city name to highlight it on the map.  Hover over any data point to see the numbers. 
* Select the “arrow” graphic at the bottom to zoom in on the map and pan around.  

Powered by Tableau

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.