Spreadsheet analysis of Kiva's Export

Description

Using the data from Kiva's export button this file allows you to better analyse the loans that you have made. No macros are used, all data manipulation is carried out using functions, formulas and calculations.
All you have to do is cut and paste the data from Kiva's export into the analysis spreadsheet.
If you have any suggestions for modifications or additions please let me know via Kivafriends.org and I will try an incorporate them.

If you would prefer an online solution, which will also provide you with a map and many other cool features, I suggest you check out the Editgrid version.

Screenshots (from OpenOffice.org version)

Current Version & Requirements

Version 1.5 (Mar 13, 2008) - Fixed summary totals with refunds, and added some stats and extra info for arrears and risk

Microsoft Excel - for calculation of dates the Analysis ToolPak add-in is required (Tools > Add-Ins… > Analysis ToolPak)
OpenOffice.org Calc - no additional requirements

Previous Versions & Requirements

Version 1.4 (Feb 19, 2008) - More stats, fix for change to 'Fundraising' from 'In Need' by Kiva.org
Version 1.3 (Dec 2, 2007) - date / locale issues sorted so now unified Excel version
Version 1.2 (Nov 27, 2007)

Instructions for use

Download and extract the relevant version of the analysis spreadsheet.
Download a copy of your data using Kiva's export button: http://www.kiva.org/app.php?page=account&action=loans&export_excel=1 (will open in your web browser and ask you to log in to Kiva.org if you are not already)
From the newly downloaded file select all data from the 'My loans' tab (using CTRL-A : CTRL-C)
Goto 'My loans' tab in this document and overwrite all existing data (use CTRL-A : CTRL V or Paste Special) the resulting pasted data must remain as text.
Goto 'Summary' tab and refresh the data in the country table by right clicking in the bounding box and selecting refresh (the rest of the data in the file should automatically recalculate and update – depending on your settings) //I think the same effect can be had by closing the file (remember to save changes) and opening

To do

Work out how to deal with Defaulted loan calculations (particularly when summing the summary data. Currently amount loaned does not take into account these types of loan status. //thankfully I don't have any of these
Identify new summary stats to be calculated – suggestions welcome.

Known issues

If you only have a few loans the 6,000 lines of calculations can slow down calculations (and cause bloat) if required delete excessive lines.
Reading of 'My loan' data into 'Details' only runs to line 6,000 ie more than 5,999 loans and errors will start to appear //should not bother most people, enough for Kay for a couple more years perhaps :-)
Calculations in the 'Summary' tab only take into account the first 6,000 lines ie more than 5,999 loans and they will not be included in calculations //should not bother most people, enough for Kay for a couple more years perhaps :-)
Will break horribly if Kiva changes the format of the export file.
Excel versions don't include count of unique countries (as in Openoffice version)

Changelog

1.4   NEW additional stats included
        FIXED issues with Kiva changing to use Fundraising (instead of In Need)

1.3   FIXED issue when it would report a deficit of -$0.00 (all values above 1 cent are now hidden) – thanks Diana
        NEW names of entrepreneurs in column B are now hyperlinks to their Kiva.org page
        FIXED issue with incorrectly reporting number of loans due this month – it was including Paid Back loans
        FIXED hidden columns and cleaned some code in most columns

1.2   FIXED issue with misreporting in column K when Loan Status was Paid Back - thanks Diane
        FIXED issue when updating pivot table so that countries reorder to ascending alphabetical order
        NEW day of the month loan due is now calculated and included in column K, a summary is provided on the Summary sheet
        RESOLVED calculation is now made for Refunded loans, totals per loan status now calculated separately (Defaulted status remains an issue)

1.1   FIXED issue with date format and locale due to differences with US (m/d/y) and UK (d/m/y date formats - thanks Henry

1.0   Initial release

Credits

to copy, distribute, display, and perform the work
to make derivative works

Under the following conditions:

Attribution. You must give the original author credit.
Non-Commercial. You may not use this work for commercial purposes.
Share Alike. If you alter, transform, or build upon this work, you may distribute the resulting work only under a licence identical to this one.

For any reuse or distribution, you must make clear to others the licence terms of this work.
Any of these conditions can be waived if you get permission from the copyright holder.
Nothing in this license impairs or restricts the author's moral rights.
http://creativecommons.org/licenses/by-nc-sa/2.0/uk/