Download the Kiva toolbar! - (what's this?)

April 23, 2014, 07:32:15 AM *
Welcome, Guest. Please login or register (it's quick and free!) for full access to all community features and functions, including instant messaging and message viewing preferences.

Login with username, password and session length

Cool Forum Options
: Not available. Login or register :)
: Popular Topics on Kiva Friends

Kivapedia
: View recent changes on Kivapedia
: Online shopping that helps support Kiva
: List of Kiva microfinance institutions
: List of Kiva group lenders
: Kiva Timeline : More...


.
Welcome to Kiva Friends, an active community for Kiva users, staff and supporters. Don't know what Kiva is? Read this!
   
   Home   Search Calendar Help Tags Login Register  

Pages: [1] 2 3 ... 7   Go Down
  Bookmark This  |  E-Mail This  |  Print It  
Author Topic: Summary spreadsheet  (Read 27403 times)
0 Members and 2 Guests were last seen viewing this topic.
YowieFreak
Kiva Supporter
*****
Posts: 1563



View Profile
« on: August 06, 2009, 03:56:57 AM »

This spreadsheet allows you to easily manage and make sense of your Kiva portfolio, whatever its size.



Attached are two versions of the spreadsheet - v2.05 uses msxml.dll, and v2.05.6 uses msxml6.dll.

I recommend people use v2.05.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).

* Kiva_Summary_v2.05.xls (300.5 KB - downloaded 389 times.)
* Kiva_Summary_v2.05.6.xls (301.5 KB - downloaded 605 times.)
« Last Edit: October 18, 2009, 02:28:15 PM by YowieFreak » Logged
YowieFreak
Kiva Supporter
*****
Posts: 1563



View Profile
« Reply To This #1 on: August 06, 2009, 03:59:55 AM »

I have moved the summary spreadsheet into a new topic, to make it easier to link to from Kiva (and anywhere else).

Version 2.00(.6) has the following modifications from what was in v1.99(.6):
  • Statistics based on currency risk status are now shown on the Stats tab.  (So you can see how much of your portfolio is at risk.)
  • When determining when repayments are expected the macros will now correctly handle loans which aren't funded until after the MFI would normally have reported to Kiva the first set of repayments from the entrepreneur.  (After I made this code change, Kiva also changed their repayment schedule information to cover this situation, so my changes are probably pointless now.)

P.S.  I intend to keep the most up-to-date version of the spreadsheet attached to the first message in this thread.
« Last Edit: August 06, 2009, 04:04:29 AM by YowieFreak » Logged
JoanW
Kiva Supporter
Portland, Oregon
*****
Gender: Female
Posts: 309


One loan at a time...

View Profile
« Reply To This #2 on: August 06, 2009, 09:23:47 AM »

I think this is a good way for others to find it.  Could you put a line in that first email with a "date of last update" somewhere? I may/may not remember the version number, but I would know by the date.

And if I haven't said it recently - THANK YOU for the spreadsheet.
Logged

"Do not wait for leaders; do it alone, person to person."
~ Mother Teresa
wthepoo
Kiva Supporter
Berlin
*****
Gender: Male
Posts: 2441



View Profile
« Reply To This #3 on: August 06, 2009, 07:34:00 PM »

Ian,

noticing some inconsistencies between my "Summary" and Kiva's database, I looked a little closer and noticed three things:

* "Kiva loan data" - Kiva's API seems to return the 16th of a month instead of the 1st as "Date First Repayment Due". Is there any apparent reason for this or do the engineers simply don't know or care that the Due Date has been shifted forward?

* "Loans" - The "Expected Paid Back" today seems to already include what is expected to be returned on the 15th of this month (or am I imagining things?). Is there any reason for this?

* "Loans" - This is just in case you want to get your spreadsheet's results regarding delinquencies completely in synch with Kiva's database: Kiva uses some sort of minimal rule for determining whether a loan is delinquent (IIRC we asked them to implement this to avoid penny delinquencies boosting the delinquency rate and causing concern), your summary spreadsheet does not (which is OK, I just noticed it today). I currently have 7 such "false positives" (delinquent loans that are not marked "delinquent" by Kiva), in 6 cases due to small amounts underpaid in general (between $0.01 and $0.14, based on the total repayments, between $0.01 and $0.03 regarding my $25-share), in 1 case due to a "penny error in the making" (loan export reports correct repayments, transaction export reports $0.03 missing).

Just in case you are interested - the first 6 loans are: 91319, 102430, 91122, 89423, 89294, 84021; the 7th loan is: 62065


As always: many thanks, and best wishes,
Wolfgang.
Logged
tomviolence
Kiva Supporter
Movin' to Florida
*****
Gender: Male
Posts: 344


A mile and a half on a bus takes a long time

View Profile
« Reply To This #4 on: August 06, 2009, 08:35:16 PM »

hi

tried out new version. thanks again for all your work.

I noticed >> PROI = ( R - NI ) / NI 205.94% which is a bit funny.

On 7-8 when I ran it I got PROI = ( R - NI ) / N 110.40%   

I think the gift certificates I bought in the interveneing time make the numbers go funny.

Since they subtracted from deposits, you could end up with a zero denominator.

Possible ideas for the "Fun Stuff" page -  Loaned / Deposited , this will show your multipleir value (How many times your $1 has been reused )
Logged

"Famines will be famines, banquets will be banquets
Some spend winter in a palace, some spend it in blankets
Dont wag your fingers at them and turn to walk away
Dont shoot someone tomorrow that you can shoot today
Time to end the praying
Listen what they're saying"

The Housemartins - "Get up off your knees" - from "London 0 Hull 4"
YowieFreak
Kiva Supporter
*****
Posts: 1563



View Profile
« Reply To This #5 on: August 06, 2009, 10:22:54 PM »

I noticed >> PROI = ( R - NI ) / NI 205.94% which is a bit funny.

On 7-8 when I ran it I got PROI = ( R - NI ) / N 110.40%   

I think the gift certificates I bought in the interveneing time make the numbers go funny.

Since they subtracted from deposits, you could end up with a zero denominator.

You should only end up with a zero denominator if you have managed to use all the money you have ever invested into Kiva in order to buy GCs.  (Possibly after having put it through a few loan cycles first.)  That would mean that you have no funds left in Kiva credits and no outstanding loans, which probably means you aren't going to be using the spreadsheet.

(I think that's true anyway - unless I am missing something.)
Logged
YowieFreak
Kiva Supporter
*****
Posts: 1563



View Profile
« Reply To This #6 on: August 06, 2009, 10:38:57 PM »

* "Kiva loan data" - Kiva's API seems to return the 16th of a month instead of the 1st as "Date First Repayment Due". Is there any apparent reason for this or do the engineers simply don't know or care that the Due Date has been shifted forward?

Is that happening on all your loans, or only on the ones prior to Kiva changing to a 1st of the month due date?

Quote
* "Loans" - The "Expected Paid Back" today seems to already include what is expected to be returned on the 15th of this month (or am I imagining things?). Is there any reason for this?

It looks like I haven't correctly handled the fact that there is now a period between the previous due date and the next "expected" repayment date.

In the old days we would expect the repayments on the 15th of one month for amounts due on the 15th of the next month, so it was just a case of saying that everything that was due up to the next due date was currently "expected".  Now it appears that I should be saying that that is only the case between the 15th and the end of the month.  Between the 1st of the month up until the 14th only the amounts due up to the previous due date are "expected".

Looks like v2.01 will have to come out soon.  Sad

Quote
* "Loans" - This is just in case you want to get your spreadsheet's results regarding delinquencies completely in synch with Kiva's database: Kiva uses some sort of minimal rule for determining whether a loan is delinquent (IIRC we asked them to implement this to avoid penny delinquencies boosting the delinquency rate and causing concern), your summary spreadsheet does not (which is OK, I just noticed it today). I currently have 7 such "false positives" (delinquent loans that are not marked "delinquent" by Kiva), in 6 cases due to small amounts underpaid in general (between $0.01 and $0.14, based on the total repayments, between $0.01 and $0.03 regarding my $25-share), in 1 case due to a "penny error in the making" (loan export reports correct repayments, transaction export reports $0.03 missing).

I think it will be almost impossible to match exactly what Kiva does.  For instance, if they say that a loan is not delinquent if the repayments are no more than $1.00 behind and a particular loan is $1.01 behind, then some lenders (if there are more than 100 lenders on the loan) may have received 100% of their expected repayments even though Kiva would be showing that the loan is delinquent.  And if the loan was only $0.98 behind, but there were only 2 lenders, then each lender could be missing $0.49 while Kiva would be showing the loan isn't delinquent.

Very complicated - so I think I will leave it until we find out exactly how Kiva determines whether the loan is delinquent.  (Which will probably be never Sad)
Logged
wthepoo
Kiva Supporter
Berlin
*****
Gender: Male
Posts: 2441



View Profile
« Reply To This #7 on: August 07, 2009, 05:57:34 AM »

Is that happening on all your loans, or only on the ones prior to Kiva changing to a 1st of the month due date?

My mistake - sorry... wasn't paying enough attention. (Though it still seems to be strange that it's the 16th instead of the 15th IMHO.)

Quote
It looks like I haven't correctly handled the fact that there is now a period between the previous due date and the next "expected" repayment date.

In the old days we would expect the repayments on the 15th of one month for amounts due on the 15th of the next month, so it was just a case of saying that everything that was due up to the next due date was currently "expected".  Now it appears that I should be saying that that is only the case between the 15th and the end of the month.  Between the 1st of the month up until the 14th only the amounts due up to the previous due date are "expected".

I agree.

Quote
Looks like v2.01 will have to come out soon.  Sad

Sorry.  Empathy

Quote
I think it will be almost impossible to match exactly what Kiva does.  For instance, if they say that a loan is not delinquent if the repayments are no more than $1.00 behind and a particular loan is $1.01 behind, then some lenders (if there are more than 100 lenders on the loan) may have received 100% of their expected repayments even though Kiva would be showing that the loan is delinquent.  And if the loan was only $0.98 behind, but there were only 2 lenders, then each lender could be missing $0.49 while Kiva would be showing the loan isn't delinquent.

Very complicated - so I think I will leave it until we find out exactly how Kiva determines whether the loan is delinquent.  (Which will probably be never Sad)

I agree, just wanted to mention it at least. It might still be a thought, though - for version 3.0 or later -, to read out Kiva's delinquency status and go for a different colour-coding (red for loans that are delinquent for you and for Kiva, purple for loans that are delinquent only according to Kiva but not according to your spreadsheet [seems unlikely to me unless in rare cases of reverse penny errors on loans which just reach Kiva's delinquency threshold], orange for loans that are delinquent only according to your spreadsheet but not according to Kiva... or any other colour combination or none of the above).

As always, many thanks for the spreadsheet(s) and all your work, best wishes,
Wolfgang.
Logged
YowieFreak
Kiva Supporter
*****
Posts: 1563



View Profile
« Reply To This #8 on: August 07, 2009, 06:13:32 AM »

My mistake - sorry... wasn't paying enough attention. (Though it still seems to be strange that it's the 16th instead of the 15th IMHO.)

It actually shows via the API as 8am on the 16th UTC (or 7am on the 16th UTC when California is on PDT time) - so that makes it the very end of the 15th Kiva-time - which is what I had always understood the due date/time to be based on when I had historically seen loans becoming delinquent.

I have also always considered it weird that the due date was changed to be the 1st - I would have expected that Kiva would have given MFIs to "the end of the month" to make repayments but they have actually given them to "the end of the 1st day of the following month".  Just doesn't seem "symetrical" to me.
Logged
wthepoo
Kiva Supporter
Berlin
*****
Gender: Male
Posts: 2441



View Profile
« Reply To This #9 on: August 07, 2009, 06:16:06 AM »

Thanks for the clarification, Ian - very interesting.

Best wishes,
Wolfgang.
Logged
Pages: [1] 2 3 ... 7   Go Up
  Bookmark This  |  E-Mail This  |  Print It  
 
Jump to:  

 
Powered by MySQL Powered by PHP Powered by SMF 1.1.11 | SMF © 2006-2009, Simple Machines LLC
Thanks to PixelSlot
Valid XHTML 1.0! Valid CSS!
Page created in 0.248 seconds with 23 queries.