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.