Excel spreadsheets - Would you use one if it used macros to do the work

[Guest]

.

[YowieFreak]

OK - the Kiva Developer Program is coming, and that means that we soon will be able to write code to do all sorts of nice things based on the data that Kiva makes available.

But, in the meantime, people are asking for things (especially with the Summary spreadsheet originally developed by Christopher) that would be so much easier to implement if I used some code (i.e. Excel macros) instead of just normal Excel formulae.

As an example, I could adjust the pivot tables currently in the spreadsheet to give summaries showing outstanding balance as well as counts, but it would be easier for me to do it using macros.

Also, using macros I could make the determination of when payments are expected to be made much more "intelligent" than is possible using just Excel formulae.

Is it worth me doing this though, or will everyone just refuse to use what I develop because of the fact that the spreadsheet will contain macros?


Ian

[Unilove]

Hello Ian:

Perhaps, for the less computer-literate among us, you could explain the hesistancy of employing macros?  If I am not mistaken, macros in Excel causes concerns because the possibility exists of the macros possibly running malicious instructions.  Please note, I am NOT saying that would be the case, and especially NOT saying it in regards to you, but that malicious coding is the typical concern, yes?

If I failed to explain it properly, please clarify.

Personally, I would enjoy having better tools for KIVA details, and would appreciate the time and efforts you or someone would put into tools to help us lenders.

Best regards,

Unilove

[YowieFreak]

Perhaps, for the less computer-literate among us, you could explain the hesistancy of employing macros?  If I am not mistaken, macros in Excel causes concerns because the possibility exists of the macros possibly running malicious instructions.  Please note, I am NOT saying that would be the case, and especially NOT saying it in regards to you, but that malicious coding is the typical concern, yes?

If I failed to explain it properly, please clarify.

Unilove,

Yes - your explanation is spot on.  Macros can be developed that are nasty little beasts (worse than bugs), and can even have viruses or spyware embedded within them.

Personally, I would never run a macro that was written by someone else without first having at least a quick look through the code to make sure it wasn't accessing anything other than cells within the current spreadsheet.  (Which is why I included an option in the poll saying "only if the code was available" )

Having said that, I must admit that I also make use of the Kiva toolbar, despite the fact that the code for it is not available.  I am just trusting that Joe did not incorporate anything malicious in it (I obviously doubt that he did, or else I would not have downloaded it), but am also having to trust that the "Conduit Toolbar" people don't have any malicious code in the platform that Joe used to develop it.

So, whether it is in toolbars, Excel macros, or anything developed using the Kiva Developer Tools when they come out, every time we use them we are having to place our trust in the developers that they have not included any "nasties".

My poll basically boils down to "If I (or anyone else) develops code to make your life easier, will you be too scared to use it because you don't trust it to only do what it says it will do".  And, although I have made this poll specific to Excel spreadsheets for the time being, a similar question will need to be raised once the Developer Tools are made available.

And, before anyone starts getting worried and thinks "If I vote 'No' then Ian will be insulted because he will think I don't trust him", I assure you I won't think that way.  I have only been a member of this Forum for two weeks, so you have absolutely no reason to trust me yet.  I would hope though that, once I do develop something, some other members of the Forum will have a look at what I did and will tell the rest of the members that the code is "benign".  Only then would I expect people to actually use whatever I have written.

Ian

[wthepoo]

Ian,
I would be delighted to use your spreadsheet - macros or not.

Best wishes,
Wolfgang.

[RichardF]

I would go for a "certified safe" copy. 

By the way, how do I know if something is certified to be safe?!  

If the canary can run it without keeling over? 

If nothing I don't expect doesn't jump out and grab me? 

If a properly nerdly KFer gives it the 'ol thumbs up? 

But seriously folks... 

Is there an external parking spot that can be used to get reviews & checks of third-party applications like this that are about to be unleashed?   

[JoanW]

I voted "Yes, if code was available".  What I really mean is "Yes, after a bunch of techs d/l it and are not screaming about icky things being hidden" I would not understand much of the code, nor know what I am looking for...but I have every confidence that if something were off - there are those here who would know and would be telling the rest of us.

Macros/scripts can do evil things, just like software. I will use either of them as long as I can trust the source or trust the site I got it from to check it out first.

Richard - would it have to be off-site? What about the Downloads folder?  At least until/unless Kiva wants to make an official d/l page

[RichardF]

Joan, I just mentioned "off site" because I know they are out there, but I really don't pay attention to where all the techies put there stuff.  I'm just thinking developer tools in general might have a wider access to lenders if they were posted outside of Kiva Friends.  There also are some file size limitations here that might be problematic for the fancier tools.

[YowieFreak]

BETA TESTERS WANTED

I have created a very, very, early version of a spreadsheet which uses macros to do most of the work of summarising the Export files from Kiva, and I need someone (or even something if anyone has some trained monkeys laying about) to do some beta-testing for me.

Using this spreadsheet is similar to the existing spreadsheets in so far as you get a Loan export and a Transaction export from Kiva, and paste them into the relevant tabs of this spreadsheet.

Using this spreadsheet is different to previous versions in that:

• you need to have your Excel settings set so that macros can be run
• you need to press a "Press Me" button on the "Instructions" tab before anything will be calculated
• there is a tab ("My extra info") where you can (i.e. it's optional) advise date of disbursement for each loan, and the actual repayment schedule
• it doesn't produce a month-by-month summary of expected repayments on the Summary page, but they are summarised on the Loans page so I am hoping that is sufficient.  (If people complain, I will add it into v2.)
  

I have left (some) of my export info in the copy of the spreadsheet I am attaching here.  It will give you some idea of the info that can be put into the "My extra info" tab.  As long as you replace my loan and transaction export details with your own loan and transaction export details, you won't need to do anything else to get it to ignore my details when you are looking at your details (i.e. you don't have to delete my info out of the "My extra info" tab, etc).

Ian

P.S.  "very, very, early version" = "I think it is working OK, but if anyone finds bugs I will just say that I haven't gotten around to that bit yet"

[JoanW]

I like it Ian.  I like it a lot!

Good -

• Instructions easy to follow - even for me
• I love the summary page. Neat, clean.
• Easy to follow all the information - well organized
• work in Excel 2003

Questions -
1) I assume on the summary page the "deposited" is the total money I have spent out of pocket.
2) Still on Summary - Delinquent section - Outstanding Total column is blank?

I would like to see -

• A summary of when payments can be expected
• Something for late loans on Summary tab
• Add a 'fun stats' tab (I could add it myself, but don't know how to hook it into the Press Me button) -
  - Summary of top 3-5 MFI's - so I can know who I already have too much in and can diversify more.
  - Biggest/smallest loan amounts
  - Shortest/longest Terms
  - 3-5 Top countries
  - Other things that are fun to know but not required.
  
  
  Again - first glance it's looking good Ian

[YowieFreak]

Good -

• works in Excel 2003

It had better work in Excel 2003 - that's the version I have myself ! 

Questions -
1) I assume on the summary page the "deposited" is the total money I have spent out of pocket.
2) Still on Summary - Delinquent section - Outstanding Total column is blank?

1) I would say "yes" - as it represents deposits from PayPal.  But it could be argued that your out-of-pocket spend is the deposits less the withdrawals.
2) Hmmm - my one delinquent loan shows up with an outstanding balance.  Can you give me the business ID of one of your delinquent loans, and I will dummy up some data to test it.

EDIT:  I may have worked out what is happening there.  Do you only have one loan showing as delinquent in the spreadsheet?  And is it business ID 32777?  If that is the case then the reason that you aren't seeing an outstanding balance is that that loan has no outstanding balance anymore - it has defaulted.  I therefore shouldn't be showing it with the "delinquent" loans.  I will fix that the next time I upload a new version.

I would like to see -

• A summary of when payments can be expected
• Something for late loans on Summary tab
• Add a 'fun stats' tab (I could add it myself, but don't know how to hook it into the Press Me button) -
  - Summary of top 3-5 MFI's - so I can know who I already have too much in and can diversify more.
  - Biggest/smallest loan amounts
  - Shortest/longest Terms
  - 3-5 Top countries
  - Other things that are fun to know but not required.

Expected payments - in the post-PA2 world, we can realistically only "expect" payments on the 15th of each month.  (Anything not paid then will just get paid sometime over the next 30 days, whenever the MFI wires the outstanding amount to Kiva, but we have no idea when that will be.)  If you just want the info from cells AA2:BD2 on the Loans tab to be replicated on the Summary tab, then I might consider doing that.  (You asked nicely, so how can I refuse.)

Late loans - By this do you mean loans that aren't just delinquent, but actually delinquent and past the final due date?

Fun stats - most of the stats you are looking at there could be fairly easily generated.  The only one that might give trouble is the "smallest loan amounts".  I would guess that in most people's portfolios there are going to be a heap of $25 loans, so the macro is going to have a hard time choosing which ones to show.  Maybe, for all of these fun stats, when two or more items are equally eligible to fit within the "top 5" (I like 5 more than 3), the macro could just pick one at random.  So, if your smallest 6 loans were made up of 3 x $25 and 3 x $50, the three $25s would always show, but two of the $50 loans would be picked for inclusion at random and the third one would miss out.

EDIT:  We would also need to determine what criteria to use to pick things like "top 5 countries" - is it the 5 with the highest count? (active? total?)  Or the 5 with the highest original loan value?  (active loans only?)  Or the 5 with the highest outstanding value?  I personally prefer looking at outstanding value as, if you are looking at spreading exposure, it's the outstanding value which is probably the main thing that you want to spread.

Ian