93 replies to this topic

[grogged]

MikeS1531, on 01 May 2012 - 22:44, said:

I wouldn't go more recent than 2007.  Someone already has expressed doubt that their office package can understand the Excel 2005 functions.  Using the most recent -- 2010? -- version likely would mean many more users -- especially those using 'compatible' software -- would be unable to use the new capabilities.  And even among those people using genuine Micro$oft packages there will be many who haven't upgraded to the very latest version, so they'd lose out as well.

Perhaps it could be developed in the latest "compatible" package (Open or Libre Office I assume) to sidestep this issue?

[PoohBah]

grogged, on 02 May 2012 - 08:54, said:

Perhaps it could be developed in the latest "compatible" package (Open or Libre Office I assume) to sidestep this issue?

I believe Geoff wanted to use Excel-specific functions as they can carry out certain tasks much more efficiently and with less complicated coding.  It's also conceivable that using LibreOffice might introduce quirks that could cause issues for Excel users.

[figures18]

Geoffj, on 30 April 2012 - 19:21, said:

If I use these functions then users will moan that it is no longer compatible with Excel 2003.

Changing to 2005 or more recent would put me out of the loop but that's fine by me and I can catch up as and when. I won't be moaning you'll be pleased to hear.

I agree with MikeS that the new release shouldn't be any more recent than Excel 2007 but I'd plump for Excel 2005 simply because you'll catch a larger group of Lenders that way. I don't think it'd do any harm to run a poll to identify who's using what as far as Excel and other spreadsheets are concerned.

If it's decided to go with a new release to include the Bad Debt Calculation I'd like to see changes to the FAQ.

The current (non Bad Debt) release to stay and to state what software it's compatible with
The intended Bad Debt release to run alongside the old one and again state what software it's compatible with.

For newer lenders and those with old versions of Excel what we've got now is fine and it's perfect to cut your teeth on.
For old hands, (those with HUGE loan books), Excel whizz kids and/or those with newer (post 2005) Excel they'd have the option of having the Bad Debt Analysis to play with.

So I'd go for option 4.
4) Build bad debt analysis using Excel 2005 syntax and provide links to that and the current Magic Spreadsheet in the present FAQ.

Edited by figures18, 02 May 2012 - 12:34.

[MikeS1531]

figures18, on 02 May 2012 - 12:21, said:

The current (non Bad Debt) release to stay and to state what software it's compatible with

That would be fine as long as the current version doesn't require tweaking to cope with Shorter/Longer markets and any other bug fixes or improvements Geoffj has in mind.  

If changes to the existing version are required, then perhaps those should be done first and the result released as the final Excel 2003 version.  After that, the Bad Debt analysis could be added in and all future updates would be applied to that version.

No doubt Geoffj can -- and will -- tell us how easy/difficult such an approach would be.

[figures18]

MikeS1531, on 02 May 2012 - 13:44, said:

That would be fine as long as the current version doesn't require tweaking to cope with Shorter/Longer markets and any other bug fixes or improvements Geoffj has in mind.  

I don't see it's a problem as Geoffj said the following:

Geoffj, on 24 April 2012 - 10:46, said:

So we are going to have 24 and 48 month loans to add to the 36 and 60 month options we have today. So this should just slot into the functionality that remains from the last time these durations were available. Nice really....  User simply need to set the flag on the variables tab to get MI_zopa working properly.

and 24/48's work fine for me with Listings so Markets should be the same.  As for bugs I thought we'd ironed those out ages ago.

I certainly wouldn't expect Geoffj to update both the old non Bad Debt and the new Bad Debt version but for now I don't see why the current s'sheet can't be left as an option in the FAQ.  In time its use will fall off and the majority will want and be able to use the Bad Debt version.

Edited by figures18, 02 May 2012 - 14:05.

[sl75]

I think the recent comments have understood what GeoffJ was saying differently to how I'd understood it.

I'd anticipated that the "option 3" would result in a spreadsheet that had all the same functionality as today for Excel 2003 users, but a bunch of extra stuff that simply wouldn't work on this older software (and "compatible" software lacking the relevant functions). Where the function is missing, the relevant calculations would just appear as "#NAME?" on Excel (and equivalent errors on "compatible software").

The tab containing this extra functionality might have something like the following formula, ready to be highlighted in 20 point red text if applicable:

=IF(ISERROR(SUMIFS(A1,A1,A1)),"The functions used to perform these calculations are not compatible with your spreadsheet program. This entire tab may be safely deleted","")


IOW a version of the spreadsheet that can be used on Excel 2003 etc. would still be available, but the new functionality wouldn't work on those programs.


Converting the *existing* functionality to a much more compact form (and perhaps an easier-to-follow "demonstration") using the newer Excel functions, in order to create a version which cannot be used at all on Excel 2003 etc. would be an entirely different step AIUI.

Edited by sl75, 02 May 2012 - 15:00.

[figures18]

sl75, on 02 May 2012 - 14:55, said:

IOW a version of the spreadsheet that can be used on Excel 2003 etc. would still be available, but the new functionality wouldn't work on those programs.

I hear what you say but I was hoping to suggest options that didn't involve ripping bits out for those that didn't want or couldn't use them.  

Geoffj, on 30 April 2012 - 19:21, said:

So the options are:-
1) Do nothing - do not build bad debt analysis at this time
2) Build bad debt analysis using Excel 2003 syntax - those that find it too slow can rip it out
3) Build bad debt analysis using Excel 2005 syntax - those that can't run 2005 can rip it out.

We'll have to wait and see what Geoffj thinks.  I'm sure he'll put me straight!

[sl75]

figures18, on 02 May 2012 - 15:06, said:

I hear what you say but I was hoping to suggest options that didn't involve ripping bits out for those that didn't want or couldn't use them.

Sure, just leave them in rather than ripping them out, and put up with one of the tabs that you can't use having a page-full of errors

[figures18]

sl75, on 02 May 2012 - 15:11, said:

Sure, just leave them in rather than ripping them out, and put up with one of the tabs that you can't use having a page-full of errors

Why put in extra stuff that either needs ripping out or if it's left in it makes the spreadsheet heavy and slow.  

Geoffj, on 30 April 2012 - 19:21, said:

   i) Carry on using Excel 2003 syntax and have lots of columns for the filters.  This will grow the size of the spreadsheet by a large amount, and will cause more issues with speed of execution.

Get off my back whilst we wait to see what the man who's doing the work says.

[Gorgeous George]

I think that since the spreadsheet is provided free of charge then it is reasonable to expect users to upgrade to a relatively recent software package if they wish to  use it. We have to acknowledge that the tool is supported by a Zopa member and not by Zopa itself. Perhaps there ought to be a 'donate' button so that users could, if they so wished, buy Geoff a drink to thank him for his work. We shouldn't underestimate the hours that he has spent developing and testing the spreadsheet).

And I say no reason why the old version couldn't be made available with a 'I AM NOT SUPPORTED BY GEOFF' label.

GG

[figures18]

Gorgeous George, on 02 May 2012 - 16:29, said:

And I say no reason why the old version couldn't be made available with a 'I AM NOT SUPPORTED BY GEOFF' label.

All good points and I second the 'I am not supported by Geoffj' Label idea  

[Geoffj]

MikeS1531, on 02 May 2012 - 13:44, said:

That would be fine as long as the current version doesn't require tweaking to cope with Shorter/Longer markets and any other bug fixes or improvements Geoffj has in mind.  

If changes to the existing version are required, then perhaps those should be done first and the result released as the final Excel 2003 version.  After that, the Bad Debt analysis could be added in and all future updates would be applied to that version.

No doubt Geoffj can -- and will -- tell us how easy/difficult such an approach would be.

I think MikeS15310 has proposed the best approach for the immediate issue. I will cut a version that is fully in Excel 2003 syntax.  It will support the markets and provide a few other tweaks and a bug fix too.  But no bad debt handling.

sl75, on 02 May 2012 - 14:55, said:

I'd anticipated that the "option 3" would result in a spreadsheet that had all the same functionality as today for Excel 2003 users, but a bunch of extra stuff that simply wouldn't work on this older software (and "compatible" software lacking the relevant functions). Where the function is missing, the relevant calculations would just appear as "#NAME?" on Excel (and equivalent errors on "compatible software").

The tab containing this extra functionality might have something like the following formula, ready to be highlighted in 20 point red text if applicable:

=IF(ISERROR(SUMIFS(A1,A1,A1)),"The functions used to perform these calculations are not compatible with your spreadsheet program. This entire tab may be safely deleted","")


IOW a version of the spreadsheet that can be used on Excel 2003 etc. would still be available, but the new functionality wouldn't work on those programs.

Converting the *existing* functionality to a much more compact form (and perhaps an easier-to-follow "demonstration") using the newer Excel functions, in order to create a version which cannot be used at all on Excel 2003 etc. would be an entirely different step AIUI.

SL75 has described how I envisage option 3.  Basically the spreadsheet would work in Excel 2003, but the bad-debt tab will be full of errors and the only options for the user will be to ignore the errors, delete the tab or upgrade to Excel 2007 (or compatible).  This way  maintains the highest level of compatibility and we only need one "released" version.

This Excel 2007 version would be a later release. If you are an Excel 2003 (or compatible) user then for a time, it won't matter which of the 2 versions you use.  As time progresses there will be increasing pressure to convert to Excel 2007 as there will be smart stuff that is not available to an Excel 2003 user (should I buy shares in Microsoft now?)

I hope this helps?

Regards
Geoff

Edited by Geoffj, 02 May 2012 - 17:46.

[figures18]

Suits me, as I said I'll stick to the current version until I upgrade my Excel software.  I might have got round to that by the time everyone's screaming for the 2007 release  

[fuzzyiceberg]

MikeS1531, on 01 May 2012 - 22:44, said:

I wouldn't go more recent than 2007.  Someone already has expressed doubt that their office package can understand the Excel 2005 functions.  Using the most recent -- 2010? -- version likely would mean many more users -- especially those using 'compatible' software -- would be unable to use the new capabilities.  And even among those people using genuine Micro$oft packages there will be many who haven't upgraded to the very latest version, so they'd lose out as well.

It doesn't really matter, but when I said 'most currrent' I meant 2007, not 2010.  I expect there are some changes between Excel 2007 and 2010 but the big differences AIUI are between 2003 and 2007.

[Geoffj]

Hi

fuzzyiceberg, on 03 May 2012 - 12:30, said:

It doesn't really matter, but when I said 'most currrent' I meant 2007, not 2010.  I expect there are some changes between Excel 2007 and 2010 but the big differences AIUI are between 2003 and 2007.

I agree with that.  It is 2007 that should be the compatibility level.  Although the differences if there are any are minor.
Regards
Geoffj

[Geoffj]

I have asked LJ to make version 2.11Excel2003 available in the FAQ area
Regards
Geoff

[elljay]

'tis done!

[Geoffj]

Hi

Well I have added in a tab bad debt to my development version as requested. It enables all manner of analysis to be completed.  

Now, I have a lot of bad debt and it gives me some nice quants. But I still can't see that it enables me to make any useful decisions.  :-)  I am interested in other views once you can see the data from your MLB.

I have a list of pilot testers that have kindly assisted with the testing previously.  Is there anyone else reading this thread that has a strong view on bad debt and would like to help test this thing

To give you a taste of what you do and what it will show.  
Select the markets and durations that you want to include in the analysis
Select 3 time periods - the impaired debt will be presented for all 3 time periods side by side
statistics are then shown for the following
   Total loans written
   Total loans paid off immediately
   Amounts in the various loan-states from active to written off
   Total impaired debt
   Weights average value of impaired debt
   ratios of bad loans to the total loans written(adjusted for paid early)
Instant recalc if you change the selection criteria

One minor note -  I have used Excel 2007 syntax on the impaired loan tab.  This has saved having to implement  27 (ie 3 * 9) additional columns compared to how it would be if compatibility with Excel 2003 was maintained.  Not impossible, but quite heavy on resources.

Regards
GeoffJ

[Geoffj]

I have had a play with this new bad debt tool to see if I can learn anything.

I have taken all my loans that from Feb 2009 to Jul 2011.  This avoids any new loans being included.

My MLB has 518 unique loans in this range.  My investment strategy has been driven my tax impacts - So have focused on A*, A, across 36 and 60 months. Nothing in Y, and only tokens in B and C.  I also played in Listings with 43 Listings loans written.

The conclusions I reach are:-
- The performance on Listings was dire - I have written off 13.69% of everything I invested in listings. 8 (19%) of the 43 loans written in Listings have been written off.  Another 2 are in arrangement
- Once Listings are removed, I have suffered some losses (4 loans written off, and 15 more are distressed).  This is a very small amount when compared to the number of loans written.
- The occurrence of the impaired loans is quite random and not too far off indicated bad debt rates. A few markets are showing 0 as losses (A*60 and B36).
- All my written off amounts are significantly UNDER the indicated Zopa bad debt amounts - but there is still some time for the fat lady to sing as 272 loans are active.
- If I view bad debt using a weighted average allowance for impaired loans then A36, A60 and B60 are slightly greater than indicated by Zopa.  But this is a a very theoretical comparison and should be treated with great care.

So this has given predictable statements about listings. Outside of Listings there is not much to learn.  Added to this - you need to wait until the loans have aged before the stats have any value - so there will be a big delay in applying any learning that was gleaned.


Regards
GeoffJ

Edited by Geoffj, 06 May 2012 - 10:18.

[CKemahli]

Afraid I couldn't locate the section where we had discussed the formula for the inclusion of a Bad Debt Tab...looking at what I had copied into my MLB xls it seems to exclude closed loans...what would be the formula for including closed loans in the calculation of bad debt...as of course it has already avoided the risk of going bad by being closed...

I'm probably not making much sense, but hopefully someone with some XL skills could help me out. Would give a clearer picture if :

Market Fees On Time Closed Processing Written Off WO% Late Late % All % Zopa Est
S36
A36 1.00%
B36 3%
C36          5.20%
Y36 5.00%
S60 0.40%
A60 0.80%
B60 2.30%
C60 4%
Y60 3%

Included closed as well.

This is the equation I would think needed modifying...=SUMIF(WSSelect!$HM:$HM,$A2&C$1,WSSelect!$AB:$AB)

Thanks in advance