Export analysis - making the Kiva Export Button work for you

[Guest]

.

[KivanSteven]

Thanks Christopher, that answered everything for me--and I actually had training in excel three separate semesters...goes to show how hard it is to retain information that you never use.  Thanks again.

[jgbndaudio]

Hey gang,

Does anyone know if it's possible to have excel automatically move paid off loans to a separate paid off loans worksheet? That would make the details page a lot easier to read. Also if this is possible I was thinking it would be really handy to have a four separate worksheets.
One for active loans, one for paid off loans, one for raised and one for loans behind schedule.

Any help appreciated.

Thanks

Scotty

PS. One other thing, Is it possible to have the

[Christopher]

Hi Scotty,

if this is possible I was thinking it would be really handy to have a four separate worksheets.

I am not sure it is possible, doing such operations to the data would really take a database to achieve.

Have you tried using Filter, and in particular Autofilter?  Tools > Filter on the menu.
This way you can view each of the categories in turn - active, raised, refunded etc.
You could set up a filter to show all but Paid Back if you are not concerned about seeing them on a regular basis.
With Autofilter it would also be possible to view the list of loans due on any particular day or month, coming from a particular country - it really is powerful (without going to a database solution).

More drastically you could hide the data, though the rows would appear blank, using conditional formatting - or delete it out of the imported Kiva Export.

PS. One other thing, Is it possible to have the

You seem to have been cut off in your prime?  Did you spot a loan from Haiti

[KivanSteven]

   That would do it.

[jgbndaudio]

Hey Chris,

Thanks for the quick reply, and thanks again for making this spreadsheet!
I'll try playing with the Autofilter since I've never really used that feature.
Also my PS was not distracted by a loan to haiti : ( but by the realization that I no longer had another question.

Scotty

Hi Scotty,I am not sure it is possible, doing such operations to the data would really take a database to achieve.

Have you tried using Filter, and in particular Autofilter?  Tools > Filter on the menu.
This way you can view each of the categories in turn - active, raised, refunded etc.
You could set up a filter to show all but Paid Back if you are not concerned about seeing them on a regular basis.
With Autofilter it would also be possible to view the list of loans due on any particular day or month, coming from a particular country - it really is powerful (without going to a database solution).

More drastically you could hide the data, though the rows would appear blank, using conditional formatting - or delete it out of the imported Kiva Export.
You seem to have been cut off in your prime?  Did you spot a loan from Haiti

[KivanSteven]

Hey Christopher...Im trying to add another stat idea I got from a mention by Peter I believe...adding an "average loan term" stat on the summary page...I am trying to reference the total months of all my loans from the details page, and dividing that by the number of loans I have as listed on the summary page, but I keep getting either a zero value or an error value depending on how I attempt to do it...do you have any suggestions on how I might calculate that figure onto the summary page?

i averaged it out on the details page, average loan duration is about 9 months....if i cant calculate it on the summary page, is there a way to simply automatically copy one cell, when it changes, to another cell on a different tab?  Thanks.

[Christopher]

Hi Steve,
I presume you are trying to average out column D (Loan term (months))??
You could just use AVERAGE() selecting that column.

Let me know if that works or not - if not I will spend some time on my other computer and delve into excel but tonight I have a film to watch...
C

[KivanSteven]

Hey C, thanks...thats somewhat what I was referring to, but I was able to use the average function for column d...that wasnt a problem at all, but what Im having trouble with is putting together a small formula, which isnt working for some reason, so that this average displays on the summary page with all of my other summarized details...in a cell on the summary page I have (the total months of all loans in column D / the total number of loans)  and its just not calculating for some reason.

....no rush though...enjoy your film and get back whenever you feel up to it   Thanks.

[Christopher]

With the ongoing drought I have had time to develop my most sophisticated spreadsheet to date, while (hopefully) making it the easiest to use.

This time I have used Editgrid.com as it can do so much more - including having the redemption schedule of your loans in your calendar and a map without having to input lots of coordinate! 

I have attached some screenshots below.  Further information can be found here: http://cjw.me.uk/kiva/spreadsheets/editgridexport/index.html

Basically all you have to do is paste a copy of your Kiva Export into the 'My Loans' sheet and away you go!

Here is a link to the spreadsheet: http://www.editgrid.com/user/kivaexport/Kiva_Loans
and here is some of what it can do:
Google Maps: http://maps.google.com/maps?q=http://www.editgrid.com/user/kivaexport/Kiva_Loans.kml
Microsoft Virtual Earth: http://maps.live.com/default.aspx?&mapurl=http://www.editgrid.com/user/kivaexport/Kiva_Loans.kml
iCal Calendar feed: http://www.editgrid.com/user/kivaexport/Kiva_Loans.ics

Any questions or problems with it please let me know - feel free to PM me and I will do what I can to help.

[Milford]

The import to Google calendar and especially Google Earth makes this a real gem for me. Well done!