Export analysis - making the Kiva Export Button work for you

[Guest]

.

[Diane R]

Eli, didn't I *TELL* you it was fabulous???  And there are some further clean-ups coming soon: it's really wonderful, everyone should try it.  (Just remember that you have to export the Kiva data and copy/special-paste it in daily or at least regularly if you want the latest info in the stats.)

Yay Christopher!

--Diane.

[KivanSteven]

Hey Christopher...I wasnt all that interested in tracking my loans through this or any other way because more or less, I was content doing it through Kiva, as tedious as it could be at times...I also have a very detailed google map...but after reading the posts I thought it was definitely worth checking out...I have taken numerous excel courses so if it worked well I knew what it could be capable of...I must say you did a superb job and it is quite impressive...thanks a ton, and Ill definitely be a user of your creation...Ill try to think up some other ways to use the statistics as well...however Im just having one problem...Im not familiar with the analysis tookpak and I dont have the installation disc at my residence at the moment so I cant determine if not having that tookpak installed is my problem, but I have included a screen shot of the issue...the only problem is under the "details" tab, and as you see I have many columns that show "#NAME?...rather than messing with it myself I thought Id ask you since you could probably clear it up a bit quicker than myself...any suggestions?

[Christopher]

Thanks to everyone for their support and suggestions - we are now up to version 1.3 for both Microsoft Excel and OpenOffice.org Calc.

The Excel versions are now unified, as I believe I have managed to stamp on the little date locale bug - and there are a whole heap of code cleanup and removal of the hidden columns.  Hyperlinks have been added so that you can easily check an entrepreneurs page on kiva.org.

The files can be downloaded from here.  Any problems with it you know where I am...

<sneakpeak>
For the last couple of days I have been working on a Google Docs Spreadsheet version, with the added benefit of a live feed to support an interactive sortable, filterable web page with just one simple html file using Exhibit 2.0 from MIT (a Javascript web application).
You can check it out here.
It is very simple to set up and use (having just walked Henry through it for potential use on Kiva Shopping Club) but it might take a little time to write the documentation.
</sneakpeak>

[KivanSteven]

Wow this is all great...Christopher when this is all refined and perfected Id drop Kiva a line and see if they are willing to use it or offer it on their site...I mean both of these could be something that thousands of Kiva users might use and it definitely doesnt help our addictions, which is just how it should be!!   

[Christopher]

Thanks Steve, and I am glad you have got the Excel version working.

Fingers crossed Kiva will think harder about how we try and use the data, and make it available direct one day.  I am not going to hold my breath over it - they have far more pressing needs to deal with.
Kind regards
Christopher

[wind5001]

THanks, Christopher,

I downloaded it and will install it shortly and see how it works...from what I've seen and read here, it does sound very good!!

Oli

[Christopher]

<sneakpeak>
For the last couple of days I have been working on a Google Docs Spreadsheet version, with the added benefit of a live feed to support an interactive sortable, filterable web page with just one simple html file using Exhibit 2.0 from MIT (a Javascript web application).
You can check it out here.
It is very simple to set up and use (having just walked Henry through it for potential use on Kiva Shopping Club) but it might take a little time to write the documentation.
</sneakpeak>

The files and instructions are now available from a special page on my website

They are fairly long, but hopefully not too involved.
If you need some help with it let me know as I could try and deliver to you working files with your data - with just a modicum of info from you (I work to loan ).

I have now swallowed my own medicine and put it live on my own blog, check it out @ http://cjw.me.uk/?page_id=94
C

[KivanSteven]

Hey Christopher...in regards to the excel file...Im still having so much fun with it...just wanted to post a few extras I added...nothing technical really at all like you were doing...quite simple actually... and if you think they might enhance your project then feel free to use the ideas...some people have more of a use for certain features and stats than others...

1.   On the "Summary" tab I included a few %s...next to the Pivot Table I think its called, on the right hand side of it, next to "Grand Total" I added another columnn with the formula for the % that each country represents of our total loans...I didnt know how to add to the Pivot table though so although these will update when the loan info is uploaded and copied over from Kiva, if a new country is added to the list of countries loaned to, Im not sure if the %s will stay aligned to the corresponding country...its not much of a problem for me if they dont because unless Kiva adds a bunch of countries in the short term, I dont anticipate more than one or two newly loaned to countries, and fixing things manually takes none more than a minute...I was thinking the % column possibly wont stay properly aligned with the countries if one is added because kiva doesnt provide those %s on the export and also because this column is not embedded into the pivot table...i made a first time loan to azerbaijan, updated, and it was as i suspected...after simply dragging my %s down a row manually it was all fine and so its no big deal if this is impossible, but is there any way you could add the % column to the pivot table so everything meshes properly when a loan to a new country is added? 

2.  On the summary tab upper left, I added a cell that displays my personal "Default/refunded" %...I know you said you were having problems with the default figurings, not sure if those were worked out, so i just threw the two categories together for now...

3.  Again on the upper left of the "summary" tab I added "delinquencies", and "delinquency %" of my personal loans.

4.  I also added in this same section something similar to "amount paid back".  I just called it "Partially Paid Back %"  and what it calculates is the % of currently loaned money that has thus far been paid back...gives you a real good and clear idea of just how much of your money you are waiting to be repaid as it compares to what has already been repaid on active loans...This could also be an awesome little statistic for people that want to structure their lending around not dipping below or flowing over a certain %....for example, someone who follows things strictly or for comfort reasons, might not want to loan if this % falls below 20...when they receive a few more payments and surpass that 20% mark, they can easily see that and automatically know that they are free to make another loan or two if they so choose.  BtW. I forgot to mention, in addition to the "partially paid back %", I also included "amount partially paid back" for the actual amount in number...Its the figure we see on our loan portfolio page.

5.   Included a hyperlink on the "details" tab as "partners" for quick access to the MFI list/info page on kiva.org.  I was going to hyperlink directly to each one but what is one extra page.

These were just a few minor additions that help me out personally, so just wanted to mention them in case you wanted to use some as well.  I had a couple quick questions to...things I wasnt too sure of one way or the other..

1.  Is there any way to link the lender pics up with their loan info on excel?  Maybe have a thumbnail come up when you mouse over their name?  The only thing I could think of was to create a tab or section for all your pics and link to them, like directly to the actual cell where the pic is located, from within the excel document.  Would 500 thumbnail sized pics cause any problem with the file as far as size and speed is concerned?  This would all be a little quicker than accessing Kiva through each lender link, and be a great benefit if internet access wasnt available.  If the only option is to link to pics placed in the document, from within the document itself, it would be a little tedious to save and link up the pics of all the people youve loaned to.
2.  On the "summary" tab, is there any way that a list of how many loans are due to be completed for each of the next 12 months can be listed?  That way we can better, or more easily know how many potential re-loans will or will not be coming up without having to work through the "details" tab.
3.  Finally...I put together some, cut and pasted others...little graphs and charts of various info...some update automatically when we paste the exported loan info from kiva, some need minor end of the month input...Im a numbers person...didnt say i was good at them, just interested in them....anyways, I have a line graph of my personal lending progress from beginning to present...Im wondering if that is something that could be automatically updated when i paste the info from the loan export file without me having to manually plug in the number of loans I made and accumulated at the end of each month.

Thats about it....sorry it was so lengthy...thought it was better putting it in one message than in many....i was going to PM, but I thought it might get others thinking about some additional options as well if I posted.  If I was mistaken in anything, please correct.  I included screen shots for reference in a word file in case I was confusing...Thanks C.

[Christopher]

These were just a few minor additions that help me out personally, so just wanted to mention them in case you wanted to use some as well.  I had a couple quick questions to...things I wasnt too sure of one way or the other..

1.  Is there any way to link the lender pics up with their loan info on excel?

2.  On the "summary" tab, is there any way that a list of how many loans are due to be completed for each of the next 12 months can be listed? 

3.  I have a line graph of my personal lending progress from beginning to present...Im wondering if that is something that could be automatically updated when i paste the info from the loan export file without me having to manually plug in the number of loans I made and accumulated at the end of each month.

Thanks Steve, you have made some nice additions, and the colours really enhance the readability.
1. Probably not a way to do it automatically, as the image url doesn't bear any relationship to business id.  Elsewhere I have been able to scrape that info to store in a database but can't do similar for Excel.  If you entered the url manually it might - but I am not sure how to do it - but then every time you opened the file it would scrape lots of images from Kiva and put further strain on the Kiva servers and hosting costs.

2. Isn't that already there? Look at Cells A26-A41 on your revised Summary sheet??

3. The way I would do this graph for it to be automatic would be to create a new column in your details tab and use a revised start date (YYYY-MM) and plot the count of these entries in a bar chart.  It would lag a little as it would have to wait for loans to be active, but should not require any intervention.
Hope this helps
C

[KivanSteven]

Thanks Christopher...I dont know how I missed the loan due (month) section on the summary tab...for some reason I was thinking that thats how many loans were due to be paid each month, but they are all due every month, so that wouldnt make sense--I didnt look at it closely enough...as for the pics Ill probably just link to them within the document on a separate tab...seems the only way to do it on excel...for me the pics are necessary because thats how I best remember my loan and the details--im awful with recalling names and associating them with the persons situation or business endeavor, especially ones Im hardly familiar with.  Thanks for the response...Ill let you know if I have any other ideas or questions.