Do you analyse your loan portfolio?

[Guest]

.

[Christopher]

So do you?

Do you wish to be able to analyse your loans further, or are you struggling with the export Kiva provides as you can't add the totals or sort the data?

I was struggling with Kiva's export so wrote a spreadsheet to allow me to carry out further analysis with a couple of clicks of a mouse.  It is available to download and I am more than happy to try and add features to help us all manage our portfolios better.

There is a thread here with more information and comments
Basically you just copy a fresh download of your Kiva Export into a 'My loans' tab in the spreadsheet - it does all the necessary using functions, formulas and calculations.  No macros are used for those worried about security risks.

The page to download an appropriate version is here

[AccountAbility]

We've been analyzing our portfolio almost as soon as the first repayments started to come in.  We've been using a non-standard spreadsheet program and have largely been inputting the data manually.  I've got Open Office on another computer so will be trying out your conversion tool shortly.  It sounds excellent and for those who are just starting to analyze an existing portfolio it will save a ton of work.  Thanks for all your hard work.

It would be nice if Kiva exports were a bit more user friendly.  The alternative of them actually building a selection of "most wanted" reports right on the site would be ideal.

Dan

[Christopher]

Thanks Dan, it has been great fun creating the spreadsheet and pushing my knowledge of Excel and OpenOffice spreadsheet functions and calculations out of my comfort zone.  I started to use a manually updated spreadsheet, but it quickly fell out of date as I found loaning fun and data entry boring.

Let me know how you get on with the Calc version - I am not sure how well it will react to a English(United States) locale - which I presume you have.  It is still in development and suggestions are most welcome - I have been cleaning up some of the calculations today...

I am hoping that this type of discussion / thread might become a stimulant for Kiva to develop some proper interactive portfolio reporting / analysis in the future - though I am realistic and not holding my breath as I am sure they are very busy on other stuff.
Encouragement much appreciated.
Cheers
C

[alanm]

Greetings:

I use a spreadsheet that I built myself. Kiva stats are much better now than a year-or-so ago. The Kiva stats cover all loans, but I'm more interested in my open loans.

I want to do several (often conflicting) things: I want to focus loans in the Health, then Green, Education, Agriculture and finally Food sectors. Next I want to focus loans in Africa, because they have been "beaten up"  by climate, disease, colonialism, external and internal politics and more, for centuries (but I can be tempted by loans in other places... :-) Finally, within the above considerations, I want to spread my loans across counties, both for some loose idea of "fairness" - that's not quite the right concept; and if any one country has some sort of problem, I don't have a "lot of eggs" in that one country.

So my personal stats spreadsheet helps guide my "next" loan.

My final thought, is, if for some reason, a loan is not repaid, it isn't a loss to "my" money, it's just that the non-repaid money can't be loaned to a future Entrepreneur.

Alan (alanm)

[Bennett43]

This is a moment for celebrating a happy milestone in my career as a KIVA supporter. Yesterday an email arrived in the in-box which confirmed my 100th KIVA loan. To mark the occasion, I'd like to share some data from a collection that's been fun for me to make. It may help with answering the question, "Where do KIVA lenders live?"

Those 100 loans were subscribed, collectively, by 2392 lenders. The only common bond they had is that they signed on to the same loans I did. After the first six or seven of those went through, I began noting the home designations as given and keeping them on a chart, backtracking to the first loan in order to make the count complete. Apart from those early exceptions, the record of lenders' locations was made as soon as the loan confirmations came in from KIVA. The range of confirmations examined extends back to December, 2006.

The 2392 supporters break down (for location) as follows:

       858   -   U.S.A. 19 Blue States, plus DC. Supported John Kerry for president in 2004.
       393   -   Europe.
       294   -   U.S.A.  22 Red States. Supported John McCain for president in 2008.
       243   -   U.S.A.  9 Purple States (CO, FL, IA, IN, NC, NM, NV, OH, VA). Supported the winner in
                   both 2004 and 2008 presidential elections.
       220   -   Anonymous by choice, members of KIVA teams otherwise obscure, or lenders simply
                   unknowable.
       158   -   Canada.
       100   -   me
         71   -   Oceania, meaning Australia, New Zealand and odd Pacific islets.
         46   -   Asia.
          9    -   Latin America.

A statistical sample is just a sample, necessarily incomplete. Every reader here will know that this one charts a tiny portion of the KIVA lender base as a whole. The methodology is resolutely unscientific, although every relevant lender has been isolated and assigned a value of 1. These results are offered only to intrigue and amuse the Friends; theories galore may be spun at will to decipher any meaning. Interesting trends have indeed developed over four years of observation, and some of them now look to hold firm. Possibly lessons are here to be drawn by those most interested in KIVA's success. After all, it may help if the KIVA lenders know who they are. Just before I leave all that deep brooding to others, Here are the ten jurisdictions which weighed in most heavily to support the same 100 loans I did, along with their count of supporters:

          1)    California                      277                     6)    Australia                      63
          2)    New York                       95                     7)    Ontario                        59
          3)    Texas                            89                     8 )    Massachusetts             55
          4)    United Kingdom               79                      9)    Florida                         53
          5)    Washington State            75                    10)    Germany                      52

Hooray for KIVA. Prosperity and fulfillment to its borrowers. Now to start in on a second hundred of loans.

[Diane R]

Fascinating, what an interesting look into your first 100 loans and the people who joined you on that walk!  Congratulations on your milestone and thanks for the look into the details.

(As for removing that , it comes from the fact that there is no space between the 8 and the ) in your list, and the resulting combination is a pre-defined smiley meant to look "cool".  If you Modify your post and insert a space, all will be well.)

congrats,
--Diane.

[Bennett43]

Corrected. Thank you very much. I could never have done that on my own.

[Mona]

10)    Germany                      52

Hooray for KIVA. Prosperity and fulfillment to its borrowers. Now to start in on a second hundred of loans.

Really some fascinating statistics! 

I unfortunately forgot the link, but there is also a page somewhere that shows you with which lender you share most of your loans. Not that it really matters, but I would be curious to learn how many of these 52 might be me.

[David2051]

I thought that was one of Ian's spreadsheets.  I played with that some time ago.   

[Bennett43]

Really some fascinating statistics!  

I unfortunately forgot the link, but there is also a page somewhere that shows you with which lender you share most of your loans. Not that it really matters, but I would be curious to learn how many of these 52 might be me.

Regret to say we do not share any loans. Of the 52 German lenders, 4 claimed Berlin as their base, 3 Munich. Other than that, there is a wide spread of lender locations. An erstwhile colleague apparently left Germany and established another base of operations. (Military?) Only one name is a duplicate, supporting two of the relevant loans. The names of German cities and towns on offer suggest to me that, as one might expect, the KIVA lenders remain overwhelmingly concentrated in the old West Germany. But nothing is forever.

I am very honored indeed to answer a question from a senior poster at KIVA Friends. Thank you for posing it. To do so, however, I had to go back to the individual loans and count them by hand. The skills which might speed that process up are beyond me. While I don't grudge the labor in the slightest, this particular window of inquiry is now closed. Once a philosopher, twice .............