Who's helping you to fund that loan?

[Guest]

.

[YowieFreak]

I was asked last night if I could possibly develop something so that people could determine what loans they have in common with other lenders.

To that end I have created another spreadsheet (that seemed to be the easiest way to do this sort of thing) which will load up details of all your loans, and then analyse which lenders are on those loans, to give you a summary of how many loans you have in common with each other lender.

You, naturally enough, have to specify your lender id on the "Instructions" tab so that it knows who you are.  (You can also pretend to be someone else if you like.)

There is also one other input which should be specified on the "Instructions" tab.  That is set to a "Y" if you want all the lenders to be listed in a single column (this should be the normal thing, as the info produced will then be much more informative as you will be able to easily sort the output by, for instance, the number of loans in common), but you can set it to "N" if you would prefer output to be shown in separate columns based on the first letter of the person's lender id.  I added this feature primarily for people with huge portfolios, as the macros will crash if they find more than 65534 lenders to be placed in a single column.

Enjoy.  (And, as always, feedback [including complaints] is welcome.)

[Diane R]

Looks like fun!  But....


Compile error: can't find project or library.

Function XMLParsePartners(xml As MSXML2.IXMLDOMNode) As kkPartners



(I put N in the appropriate slot )

--Diane.

[YowieFreak]

Compile error: can't find project or library.

Function XMLParsePartners(xml As MSXML2.IXMLDOMNode) As kkPartners

Sigh - I guess that means you can't use my summary spreadsheet either yet.

That problem may mean that you need to download the latest version of MSXML from Microsoft.  I think http://www.microsoft.com/downloads/details.aspx?familyid=d21c292c-368b-4ce1-9dab-3e9827b70604&displaylang=en is probably the best place to get it from if you want to try it.

[YowieFreak]

(I put N in the appropriate slot )

You may also need to let it run overnight too!!

It probably takes a few seconds per loan to download details from Kiva.  It might be quicker for you because you don't have to use a trans-Pacific cable to get to Kiva's servers, but I think it may still take a few hours to load your whole portfolio!

[Diane R]

Correct, I have not yet been able to get your summary spreadsheet to work, sadly.  (But I'm stuck on the step of trying to get my transaction history downloaded which Ain't Happenin', so that's a Kiva problem we determined.)

I may try to get the code you suggest, but I'll have to see if it conflicts with anything I'm required to keep on this laptop for work first.

looks cool, though...
--Diane.
EDIT:  No, I got that error message almost immediately. No sense of letting it sit and wait, it blorted at me right off the bat. 

[Unilove]

blorted?

I gotta write that one down...

[YowieFreak]

Version 1.2

As always, v1.0 had some bugs in it.  Hopefully most of the ones I know about have been fixed in v1.2.

The ones I know about that I haven't been able to fix (and probably won't ever be able to fix) are:

• People with no lender id are lumped together on the "Lenders" tab.  (How people managed to create accounts with no lender id, and yet not be "anonymous", is still a mystery!)
• People with Japanese or Chinese characters in their locations cause the whole "page" of lender details to fail to be accessed.  This can cause either none of the lenders for a loan to appear (if that lender is one of the first 50 lenders on the loan) or for a block of up to 50 lenders to be ignored (if that lender is not one of the first 50 lenders).

This version should be somewhat faster than the earlier version as well.  It took me 47 minutes to process a portfolio of about 2,500 loans.  This should (possibly) be compared with the 9 hours it took for Wolfgang to run that same person's portfolio through v1.0.

Ian

P.S.  Wolfgang, note that this is v1.2, not v1.1.  Do you want to play "spot the difference"?

[wthepoo]

Ian,

thank you very much indeed - I haven't had a chance to play "spot the difference" yet, but I might try later. 

What a great achievement... with this and your other tools (the summary in particular, of course) you have hugely improved my lending experience - and that of many other lenders, too, I am sure. Do the folks at Kiva know about your tools?

Best wishes, thanks again, and take care,
Wolfgang.

[Diane R]

Ian, do you have a suggestion about what those of us with larger portfolios might be able to do to overcome the time-out problems so we, too, can participate in using the tools you've created, which sound super?

--Diane.

[YowieFreak]

Ian, do you have a suggestion about what those of us with larger portfolios might be able to do to overcome the time-out problems so we, too, can participate in using the tools you've created, which sound super?

Well, firstly, the timeout problem you have with getting an export file out of Kiva won't be an issue with the spreadsheet to find common lenders.  This one gets all the information it needs about your portfolio directly from data that is publicly accessible via Kiva's API, so no export files are required.

The Summary spreadsheet needs the Loan export file because it is the only place to obtain information re the lending team applicable to each of your loans, and obviously the Transaction export file as it is the only way to obtain information about your actual transactions.

There is talk that, at some stage, the API will be modified so that it can access private information (after proper user authentication obviously) and, once it is modified in that way, I will be able to modify the Summary spreadsheet to no longer need the export files too.

As far as the export file problems go, if the error message you get is the "oops" message that most of the rest of us get then I would suggest just to continue to persevere and hope that it works finally.  I think it is related to the number of other people who are trying to get an export file created at the same time, so perhaps try late at night or very early in the morning.

If you are instead getting an actual "timeout" message of some sort, that is going to be different to the rest of us, and it won't matter what time of day you try - it will be related simply to the time that it takes Kiva's servers to extract the information for your portfolio from the databases and then format it into an Excel file.  The only thing I can suggest you could do in that situation is ask Kiva to include a facility to supply a simple CSV file, rather than the actual XLS file that they normally provide.  (I'm not sure why they even go to the trouble of actually providing an XLS file.)  A CSV file should be faster to produce than an XLS file, so should mean less chance of it taking longer than the "timeout" period.

[Diane R]

Ian, thanks for the reply.  Another of our very helpful KivaFriends (*cough*Wolfgang*cough*) has provided me with a copy of the "fellow lenders" spreadsheet for my portfolio, so now all I need is to get Kiva to help me get the transaction history download to work and I'll be golden!  Thanks again so much.

--Diane.

[YowieFreak]

... all I need is to get Kiva to help me get the transaction history download to work and I'll be golden! 

If you want to be able to use the Summary spreadsheet, it will need the transaction export file and the loan export file.  (Although, if they get the transaction history download to work for your portfolio, the loan export should also work.)

[A Nonny Mouse]

Ian, I finally got a chance to run your Common Lenders tool, and I just wanted to say WOW...I love Excel, and I love watching it at work crunching numbers.  That was great fun!

Thanks for all you do for us, Ian, even if Kiva HQ doesn't get it   

Nonny

[AccountAbility]

If you are instead getting an actual "timeout" message of some sort, that is going to be different to the rest of us, and it won't matter what time of day you try - it will be related simply to the time that it takes Kiva's servers to extract the information for your portfolio from the databases and then format it into an Excel file.

Just to be completely clear, the Transactions page does not load, period.  There is no time-out message-- it just waits for a few minutes and then marks it as "done".
Dan

P.S. Yes, a CSV file export would make so much more sense than an Excel format.

[damienlanfrey]

Ian,

A bit later, as now am catching up with some older posting, but I also downloaded and tried your tool.   
Just amazing, and, I love to think about the potentialities of it.

As a PhD student particularly interested in social networks and hyperlink networks research attached to platforms for social change like Kiva (as my last survey which has been running since two days ago clarifies (waiting for even more contributors), as you can see from my hopefully not too annoying signature or here http://www.kivafriends.org/index.php/topic,2964.msg60646.html#new ), I'd love to see or work on something that explains a bit of the wealth of connections developed around Kivans, KivaFriends, Kiva teams, even attached to other statistics (E.g. number of loans, background, similarities and differences among lenders).

Three questions: 
- What does "Kiva HQ doesn't get it" mean?
- Thought about extending this work to other data?
- Why not Web-based, with all the benefits for rich visualization modes?

In any case,
Thank you very much,

Damien

[YowieFreak]

Three questions: 
- What does "Kiva HQ doesn't get it" mean?

I think Nonny's remark was in regard to the fact that Kiva ignored my request to include the spreadsheets I had developed as part of their "which is the best Kiva app" survey.  We assume they don't understand that Microsoft Office applications are just as valid as any other development tool.

- Thought about extending this work to other data?

Yes, but I don't have the imagination to decide what other data.  Feel free to make suggestions.

- Why not Web-based, with all the benefits for rich visualization modes?

a) Web-based requires a web-server.  I have one, but the band-width available on my plan would not support me putting such an app on it.  (Not without costing me more than I am willing to spend on it - I'd rather use my money to fund a Kiva loan instead! )

b) I am not a "graphical" person, I am a "number" person.  So I find spreadsheet tables much easier to interpret than pretty maps, etc.  And that means I don't have the imagination to think how other people would like to see the output displayed graphically.

[damienlanfrey]

Ok, got it. I guess Kiva HQ they had in mind Web application to sustain the whole Open API idea.
At the same time, I think your application is for the moment the one with the highest qualitative value.
Most applications developed are about displaying, not revealing semantic patterns.

If extended it could reveal very interesting lending patters, which might make things a bit too "transparent" (but what is not here) but very useful to Kiva itself for understanding how networks (and teams) grow and cooperate (or not) and which ones do this in an interesting way, or finally whether natural groups form outside teams or communities.
It would give another layer of understanding on how we interact around Kiva.

The "team" data is the first one I'd  think about then. (to know to which team - KivaFriends apart - we are closer). Attached to this, country data.
Then the information about overlaps can be ordered and values higher than a certain amount, like 5, could be nicely mapped to see whether any surprising (or not really) relation among teams (E.g. Kiva Christians and Agnostics)  or countries emerge.

The other way around is of course very interesting. The most interesting thing I'd love to know in the future would be about who funds American loans, on aggregate. But that might be way another thing.

Also, mapping the information of the last, for example 200 loans of a Team and then compare team with each other.

Finally, Web Server: of course totally understand you. (Kiva - or a nice Web hosting company or else should provide for that)
Maps: I totally agree on that; as I said, many applications on mapping around, but little use apart from visualization. The graphical side could be left to somebody else to complete the work, I'd just love to see the quality of your work displayed in a way to be easily grasped by everybody.

Thank you,   
Damien

[A Nonny Mouse]

- What does "Kiva HQ doesn't get it" mean?

Sorry I couldn't get your question answered in quick time, Damian...Ian did guess the meaning correctly.   

Nonny