FundraisingLoans spreadsheet

[Guest]

.

[YowieFreak]

This spreadsheet gets details of all loans which are currently in Fundraising status on Kiva.

The user has a choice of asking for a "simple" listing or a "detailed" listing.  The "simple" listing will show for each loan:

• Business ID
• Name
• Date Posted
• Country
• Field Partner
• Field Partner Rating
• Loan Amount
• Amount Checked Out
• Amount in Baskets
• Amount Left to Fund
• Sector - e.g. Retail, Food, Agriculture
• Activity - e.g. Cosmetic Sales, Bakery, Animal Sales
• Use - i.e. a short description of what the loan will be used for - e.g. "To purchase shoe-making supplies"

The "detailed" listing, which takes longer to obtain all the data from Kiva, shows all the above plus:

• Female % - i.e. what percentage of the entrepreneurs on the loan are female - 100% for an individual female, 0% for an individual male, somewhere between the two extremes for a group loan
• Non-Payment Risk - I believe this is whether it is the lender or the partner who takes on the risk of defaults
• Currency Risk - i.e. whether the currency risk is "Covered" by the Partner, "Shared" by the Partner and the lender, or "N/A" because the loan is in USD$
• Loan months (borrower) - i.e. period from when the entrepreneur gets their money and when they are due to make the last repayment
• Loan months (lender) - i.e. period from now until when the lender is expected to receive back their last credit
• Description - the full English description as shown on the Kiva website
• Disbursal Date
• Date First Repayment Due
• list of Repayment amounts

Once details are loaded, the user can place an amount against each loan they wish to fund and then press an "Add To Basket" button, which will open up a new browser window with a pre-populated basket.

Obviously (or maybe not) the details can be sorted, filtered, etc once they have been loaded into the Excel spreadsheet.


Attached are two versions of the spreadsheet - v1.02 uses msxml.dll, and v1.02.6 uses msxml6.dll.

I recommend people use v1.02.6 unless they don't have msmxl6.dll installed on their computer and aren't willing to install it (e.g. on company computers, etc).

[YowieFreak]

I have ripped the fundraising loan stuff out of the Summary spreadsheet and put it into a separate one.

I have also added an extra column to the sheet, showing the Posted Date for each loan.  This allows me to create a summary showing how much is left to be funded before various expiry dates (and graph that amount too).

Hopefully that will allow people to keep track of whether loans are likely to go unfunded or not.


I have just uploaded v0.01 (obviously a "beta" version) to see if anyone wants anything changed prior to v1.00 being released.

P.S.  I intend to keep the most up-to-date version of the spreadsheet attached to the first message in this thread.

[Unilove]

Thank you.  You are, simply, amazing.

[Kathy]

Holy Hannah! You're amazing. This is totaly cool. Thanks!

[Kathy]

Just want to say  again!

I actually used the spreadsheet to make 4 loans (on the four oldest loans) and matched with donation this afternoon. It made it really simple and easy. I've bookmarked it and will be using it again and again and again (well, when I've got more money that is). I like how you can change the filters to be what you're looking for. It's way quicker than the site and since I don't really read the 'stories' (gasp! I know, I know how odd) it makes the entire process that much simpler.

[YowieFreak]

Version 1.00

I have just updated the spreadsheet and attached it to the first post in this thread.


This version will show, on the "Kiva partner data" tab, which partners have loans currently fundraising in each of the three possible currency loss categories - "possible", "covered" and "n/a".

Edit:  This feature only works when you use the "Detailed" button, not the "Simple" button.  That's because the currency loss details are only accessible from the "detailed" loan call to the API.

(I have also started calling it "possible" currency loss instead of "shared" currency loss, to make it consistent with the Kiva website.)


This version also directly writes the current time (PST) into cell B1 of the "Summary" tab, so that the date/times on the summary page always match up with when the macros last refreshed the loan details.

That is the only cell on that tab which is used by the macros so, if you want to, you can safely add extra rows into the table on that tab, or extra columns, etc, etc.

[YowieFreak]

Version 1.01

I have just updated the spreadsheet and attached it to the first post in this thread.


Loans that were disbursed in USD$ (and therefore had no currency risk to lenders), but which were stored in Kiva's data as "shared" currency risk, were incorrectly being shown as "Shared" currency risk in the spreadsheet.

This has now been changed so that they will show as "N/A" (i.e. to be the same as the other USD$ loans where the lender has no potential currency risk).

[RichardF]

Hi Ian,

Would you be willing to add a "See all fundraising loans from this field partner" type of column?  The current links on the Kiva partner data tab go to their profile pages, which is fine, but having a link to all the partner's Fundraising loans would be a nice addition here.  And as long as I'm asking, sorting by Repayment Term would be nice too.
 

An example of how this would work for some partners is at the list:

Fundraising Loans NOT Sharing Currency Exchange Loss

[YowieFreak]

Would you be willing to add a "See all fundraising loans from this field partner" type of column?  The current links on the Kiva partner data tab go to their profile pages, which is fine, but having a link to all the partner's Fundraising loans would be a nice addition here.  And as long as I'm asking, sorting by Repayment Term would be nice too.

I could ask why, instead of going to each individual partner's page (one by one) to see their fundraising loans, you just don't sort the "Fundraising Loans" tab into repayment term order and then use a filter on the Partner column to look at the Partners you are interested in (one by one).  Or, even better, sort on the repayment term and then filter on the currency risk column to see the non-shared currency risk loans from all Partners all at once.

P.S.  I'm thinking of changing the spreadsheet (sometime in the next month or two [perhaps]) to allow people to specify that they want to exclude certain MFIs, certain currency-risk types, certain terms, etc prior to it populating the data.  The ability to exclude certain MFIs would speed up the time taken in obtaining the data when using the "detailed" button.

[RichardF]

My particular "why" is that, even though I find your spreadsheets very useful for various purposes, e.g., finding duplicate listings, I still prefer the image-oriented Kiva Lend Page for browsing selected subsets of listings.  The operational problem is that Kiva's filters and sorts are inadequate for many lenders.  I agree no new functionality would be introduced here, just convenience of a sort.  I already have my own browser folders set up with my favorite partners, and I just open them all in different tabs when the mood calls.  If you haven't already received 500 PMs requesting something like this, I guess it's really no big deal.

[YowieFreak]

My particular "why" is that, even though I find your spreadsheets very useful for various purposes, e.g., finding duplicate listings, I still prefer the image-oriented Kiva Lend Page for browsing selected subsets of listings.

  I must admit that, when I asked the question, I guessed that would be the answer.

How about I change the Partner tab so that the "Partner ID" becomes the link to the normal partner page, and the "Name" becomes a link to the current fundraising loans for that partner (sorted by repayment term just to keep you happy - or perhaps by old to new to keep others happy - hmmm - major dilemma there).

Anyone else want to put up their hand and say that this change is not a good idea?

[RichardF]

Yeah, sure, that sounds like a great idea!  Whatever you decide is fine with me.   

[YowieFreak]

Version 1.02

I have just updated the spreadsheet and attached it to the first post in this thread.


This version is a very minor change to v1.01.  The partner page now uses the Partner Name to link to the currently fundraising loans for that partner on Kiva's website (for those people who can't read a book without pictures ).  And the Partner ID now contains the link to the applicable partner page on Kiva.

[RichardF]

 

[YowieFreak]

Oops - there is a "bug" in the version I uploaded.  It won't update the Partner data until after October 11 at 1pm (your local time).  It's not something wrong in the macros per se - just the data in cell A1 of the Partner tab as I uploaded it.

Partner info is only updated if it is more than 12 hours old, and I left a time last updated in that cell which was too recent.  (Normally I manually edit it so that it is several days old before I upload it.)

If you wait until after 1pm on October 11, everything will work again normally.  Or manually change cell A1 if you are impatient.

[YowieFreak]

I just saw a tweet saying "buildkiva: Nuclear Spike launches the first Kiva app that lets you shop offsite of kiva.org! http://bit.ly/ruWG2".

I guess, once again, the Fundraising Loans spreadsheet doesn't rank as far as Kiva is concerned.

[JohnR]

Ian, thank you for your wonderful spreadsheet.  I've used it several times and it helped me locate a loan I wanted and would have otherwise missed.

I do have a request, though.  (Did you see that one coming?  )  I use it because I don't have access to Firefox (I used a company laptop and they won't allow it) so it is the only way I can sort by currency risk.  Could you put out a stripped-down currency risk spread sheet?  For me, it would only need to have the link to the borrower's page, the country, the MFI, and the currency risk status.  I only use your spreadsheet to filter out loans that have currency risk and then to look at them by country.  I don't mind clicking to the borrower's page to get the dirty details.

If you could do this, then it would load much faster, I think, which is the goal behind my request.

Thanks for considering this.

John

[YowieFreak]

If you could do this, then it would load much faster, I think, which is the goal behind my request.

The problem is, to obtain the currency risk indicator the macros have to make a call to the API which also returns all the other "detailed" info.  And it is the call to the API, and the return of the data, that takes about 99% of the time.

[RichardF]

John, you always can just upload the browser bookmarks for the Fundraising Loans NOT Sharing Currency Exchange Loss.  That way, you get pictures too! 

[JohnR]

Ian, thanks for the explanation.  The wait is still worth it.

Richard, I didn't completely follow your instructions; I'll have to come back to it later in the day when my brain is more active.

John