Excel spreadsheets - Would you use one if it used macros to do the work

[Guest]

.

[wthepoo]

Wolfgang - can you explain what this tab might tell me? why I would want that information?

Joan,
right now, Ian's spreadsheet has to work with certain assumptions, namely:

1. Disbursal Date = 2 weeks after Start Date (see my post: should IMHO be Disbursal Date = Start Date) - this is wrong with pre-disbursed loans;
2. monthly linear repayments starting 1 month after disbursal - this is wrong (1) with loans repaid in a lump sum at the end of the term, (2) with loans that start repaying later (like most[?] GHAPE loans that start repaying after 4 months) and (3) with loans with progressive principal repayment (like many loans with Aqroinvest where the borrower repays a fixed monthly sum that contains a higher proportion of [Kiva] principal from month to month).

These assumptions lead to certain inevitable inaccuracies -
# 1 in form of a delayed repayment schedule - (e.g. loans that are really delinquent may be shown as "on time"),
# 2 rather does the opposite, showing loans as delinquent that are not.

The "extra info" - entered either manually like now or (semi-)automatically like I hope it will be possible at some stage - thus makes the spreadsheet more accurate because it supersedes these assumptions.

Best wishes,
Wolfgang.

[YowieFreak]

I'm looking at the Stats page this morning - comments and what the information tells me:

• Looking at it by country - I'm seeing a number of countries that I do not currently have loans in, so I will be looking for those in the future. It would be nice if I could get the Count of Outstanding Loans to go with the total Count
• Teams - perhaps label A72 as "No Team"?
• Months remaining - does the '0' mean 'paid off'?  I am hoping I can use this to once again balance otu when loans are coming due to try and keep it consistent across time.

Count of outstanding loans - You should (hopefully) have a "+" just above column H in the Stats page.  Click that once and it will "ungroup" columns B to H so that you can see counts by status.  Similarly for columns Q and Z.  Clicking the small "2" above the row numbers on the left-hand side of the spreadsheet is the equivalent of clicking all three "+" signs.

Teams - will try to change it - shouldn't be hard.  Will get confusing if someone creates a team called "No Team" though.  (Heh, if I can belong to a team called "none of the above", it's not too much of a stretch of the imagination to think that someone might create a "No Team" team )

Months remaining - At the moment, zero would indicate no payments due after 15 January, 1 would indicate last payment is due by 15 February, 2 would indicate last payment is due by 15 March, etc.  So zero would represent anything where the last due date is in the past, or has been "finished" even before the last due date (i.e. statuses of "Paid Off", "Defaulted", "Refunded" as well as "Active, but delinquent, and last payment is overdue").  Again, the "ungrouping" feature of Excel should make more sense of the numbers - you will be able to see the counts in their different status categories.

Over all, is this going to be a stable spreadsheet or one that will have somewhat frequent updates (out of beta of course)? I ask because there are little tweaks that I would make, but if this is going to get updated, I can live with out them. Again - I'm talking about out of beta.

I certainly hope that it will be relatively stable - I don't want this to become my life's work - but it could be several versions down the track before it is.

Even once it is "stable", you will need to be aware that pressing the "Press Me" button goes and deletes whole chuncks of the spreadsheet as the very first step, so you will need to be careful where in the spreadsheet you place your "tweaks".  (Once it is a bit more stable, I'll make a list of exactly which cells on which pages get zapped, so that you can avoid placing anything in those cells.)


Ian

[YowieFreak]

1. You are assuming a disbursal date that is 2 weeks after the start date - this doesn't make sense, IMHO. The "Start Date" from the export file must always be on or after the disbursal date* (it is, I believe, the date on which Kiva marks a loan as disbursed and sends out disbursal notifications) - so the best assumption would probably be to set Start Date = Disbursal Date.

EDIT: * With one exception, I guess: With some old loans, during the PA-2-transition the disbursal date was pushed back by up to 2 weeks. For these old loans with pre-disbursing MFIs the assumption can make sense - until this bug will hopefully be corrected.

I have changed it to assume Disbursal Date = Start Date if no Disbursal Date advised on the "My extra info" tab.

2. I enjoyed having hyper-links to the loans in Christopher's and Richard's tools - I don't know if this would cause much work or you threw them out for any particular reason, but they are a kind of comfort feature.

"Name" is now a hyperlink again.

3. I notice that your analysis solely relies on the transaction data; I agree that this seems to be the safer bet and probably wouldn't change anything about it. You could, though, add a column showing the differences to the loan data (take my first loan ID=35963 for example: transaction export shows amount paid back is $10.41; loan export shows amount paid back is $10.42) - not necessary but maybe interesting to some.

Done.

4. Your spreadsheet benefits greatly from the chance to enter "extra info" - I tried it with some loans (the ones that were shown as "delinquent" after the first run) and it works like a charm. It's just rather time consuming when you have a lot of loans - so I do hope that at some stages Kiva will make this data accessible and you (or another computer genius) will find a way to import it into your spreadsheet.

I should be able to automate this, but I will make it so that you have to manually initiate it for each loan that you want the info retrieved for.  If I don't do it that way, it could lock up your computer for several hours while it goes and downloads the webpage for every loan in your portfolio.

I'm going to post an updated version of the spreadsheet now with all the other fixes I have done, and then I will work on this "automated" bit over the next couple of days.

[wthepoo]

Ian,
that's great, great news! Thank you ever so much!

Best wishes, take care and have a great start to the week,
Wolfgang.

[YowieFreak]

Beta3 of v1 attached.

Refer to my replies to Joan and Wolfgang's posts to see what has been changed.

Ian

[RichardF]

Hi Ian,

Very nice!   

I have three suggestions for improvements (without reading those from others - to be more "impartial").

• It would be nice if anything that includes macros were digitally signed, even if it were self-created.
• The file download links in steps 1 & 3 are busted, even though they look okay in the edit box.  I went to the "edit hyperlink" box to redo them and they worked fine after that.
• The "Loan Term/Start Date" field in the "My loans" file contains what I still contend is the "Disbursal Date" in the "My extra info" tab.  The "Date First Repayement Due" can be calculated from the definitions you use.  Subsequent repayments are included in the "My transactions" download.  There's no need for the user to enter any information at this tab.

Other than that, woo-hoo!!! 

[wthepoo]

Thanks, Ian!

Beta3 works flawlessly here. Great improvement!

I have changed it to assume Disbursal Date = Start Date if no Disbursal Date advised on the "My extra info" tab.

I like this a lot - it turned up a few "false positives" when I first ran it (the loans where PA2 pushed back disbursal but not start date) - basically half of my loans with Zene za Zene and AMK, and some more will show should netting not work out for my IMON, Hope Nadiya and MLF Microinvest loans on 15 February. But (1) better this way than the other, I believe, (for some reasons) and (2) it's fixed rather easily with manually entering the disbursal date. On the other hand, it also showed for the first time some "true" delinquincies, hidden before in the 14-day-grey-area. Some more delinquincies might of course hide among the pre-disbursed loans since October.
 

Take care, and thanks again!
Best wishes,
Wolfgang.

[wthepoo]

The file download links in steps 1 & 3 are busted, even though they look okay in the edit box.  I went to the "edit hyperlink" box to redo them and they worked fine after that.

Same here - sorry I forgot to mention it.

The "Loan Term/Start Date" field in the "My loans" file contains what I still contend is the "Disbursal Date" in the "My extra info" tab.  The "Date First Repayement Due" can be calculated from the definitions you use.  Subsequent repayments are included in the "My transactions" download.  There's no need for the user to enter any information at this tab.

Richard,
IMHO that's wrong, though, on both counts.

1. The "Start Date" is neither always the disbursal date as it "should be", not is it always the disbursal date recognized by Kiva's system. It is "just" the date on which Kiva sent out the e-mail notification of disbursal.

This means with pre-disbursed loans under the regime of PA2, the "Start Date" is way after both the "real" and the "system" disbursal date, and with pre-disbursed loans before PA2 (those that experienced the push-back) it is a date 2 weeks before the "system" disbursal date.

2. Entering additional info on first repayment date and on repayment schedule is important with loans that have an "irregular" repayment schedule (like GHAPE loans that start to repay after 4 months or Caurie loans that are repaid in one lump sum at the end of the loan term). Your and Christopher's spreadsheets inevitably show these loans as delinquent even though they are not.


Best wishes,
Wolfgang.

EDIT:
Two examples from your portfolio, Richard, regarding point 1., so that you can easily check:
a) http://www.kiva.org/app.php?page=businesses&action=about&id=84003 - Disbursal Date according to loan page: Dec 16, 2008 - I guess "Start Date" in your loan export will be Jan 17, 2009 (?).

b) http://www.kiva.org/app.php?page=businesses&action=about&id=66451 - Disbursal Date according to loan page: Oct 6, 2008 - I guess "Start Date" in your loan export will be Sep 25, 2008 (?).

[RichardF]

Are your contentions with my/Kiva's definitions or Kiva's implementation of PA2?  The exceptions, IMHO, are Kiva's inadequate implementation of their own definitions.  If you want to fudge/compensate with a manual spreadsheet entry, go for it.

[wthepoo]

Are your contentions with my/Kiva's definitions or Kiva's implementation of PA2?  The exceptions, IMHO, are Kiva's inadequate implementation of their own definitions.  If you want to fudge/compensate with a manual spreadsheet entry, go for it.

(EDIT: Reworded.)

Richard,
I am referring to the fact that the loan export contains "Start Dates" that are different from the "Disbursal Dates" shown on the loan pages and used for all schedule and term calculations. I agree that the exports should contain the disbursal dates, but they don't.

I use spreadsheets because I want to be able to find out when a loan is due, when I can expect a repayment and which loans are delinquent. With the "Start Date" from Kiva's loan export this won't work in about 25-50% of the cases (namely all pre-disbursed loans).

I agree that this is due to a mistake made by Kiva (it's on the bugs list, too). But this knowledge does not help me when I am using a spreadsheet.

Best wishes,
Wolfgang.