Wilmott Forums - Spreadsheet Errors

Forum Navigation:

new topic

FORUMS > General Forum

< refresh >

Topic Title: Spreadsheet Errors
Created On Mon Aug 16, 04 03:55 PM

Topic View:


	Pages: [ << 1 2 3 Previous ]

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Fri Sep 10, 04 12:22 PM

What sort of % of people around here have Excel 2003 ?

-------------------------
Discussion on the new regulations on bonuses here.

Quote

Top

Bottom

sm345
Junior Member

Posts: 7
Joined: Jul 2004

Fri Sep 10, 04 01:48 PM

Hi DrBen:

I don't understand your question regarding:

Quote
Office 2003 has XML as a native format and I believe Office XP internally has XML.
Anyway, the real hassle in getting Excel --> Function (in Java, C# or whatever) is
getting this XML file.

Excel 2003 can save to XML using SaveAs. It can also save to HTML and .mht/.mhtl
.mht is pretty cool. You can publish your spreadsheet on the web, and have an interactive spreadsheet. However, (there is always a gotcha) You need to use 1. IE client 2. Client must have Office 2003 Web components installed, and 3. Licensing issues

Sanjeev

Posts: 147
Joined: Jul 2002

Fri Sep 10, 04 01:52 PM

Quote
Originally posted by: DrBen
...unless some one works out and represents state in Excel.

What exactly do you mean by "state"?

Quote

Top

Bottom

DrBen
Senior Member

Posts: 494
Joined: Feb 2003

Fri Sep 10, 04 02:24 PM

Quote
Originally posted by: Boofta
Quote
Originally posted by: DrBen
...unless some one works out and represents state in Excel.

What exactly do you mean by "state"?

At present you have state through custom interfaces like Solver.
In solver you "set" the fields within the custom interface like "Are you
solving an integer optimization problem or not" and so on. State within
business classes can be thought of as classes where the order in
which the methods are callled effects the result. A good example is in
an implementations of Portfolio theory, where a client application of a
Portfolio components implementation will generally look something like:

1) Create Portfolio Instance
2) Set asset weight constraints, i.e. change some provate field
containing the asset weights.
3) Evaluate the Efficient Frontier.
4) Get optimal portfolio for a given value of the expected return (and maybe print out result)

The point is that the methods must be called in this order, meaning that
the information from one invocation is used in another. Such classes I
think as being very stateful. If you map such an implementation to a stateless
version then its becomes unfriendly, that is such functionality is most naturally
expressed using the notion of state.

Another example in for pricing frameworks, in which you may have the
following method called to price a contract.

1) Set Constract
2) Set Vol model
3) Set rate model
4) Set price model.
5) Run MC simulations
5) Get Price
6) Get Delta
7) Get confidence interval
and so on....

This example does require state but the method (1)-(4) can be called in
any order, then (5) should be called before (5)-(7) can be called
in any order. Therefore, though this class requires state in my language I
think of it as being "less stateful" than the instance given above.

In Excel there is no notion of state but you can map a general pricing framework
into methods of the following type which do not require state (i.e. stateless):

getPrice('contract parameters', 'vol model parameters', 'rate model parameters', 'price model parameters', ;MC simulation parameters')

In fact this is exactly what people do with Excel. Unfortunately, if you have
10 contracts, priced according to 10, vol/rate/price models; then you are going
to need 10,000 methods which make the tool unusable. Hence the need for
state where you have 5 methods which are called one at a time.

As a side note, I have the blue prints to enable state in Excel and I just need
to find the time to do it.

Going back to the Office XP XML question? I think it is not possible, and everything
will need to be reverse engineered from the xls format. What a nightmare?

-------------------------
My software site: here

Edited: Fri Sep 10, 04 at 02:27 PM by DrBen

Quote

Top

Bottom

DrBen
Senior Member

Posts: 494
Joined: Feb 2003

Fri Sep 10, 04 04:23 PM

Quote
Originally posted by: DCFC
What sort of % of people around here have Excel 2003 ?

I have Excel 2003, and in terms of software development only intend to support Office XP/2003
or later going forward.

-------------------------
My software site: here

Quote

Top

Bottom

popx
Junior Member

Posts: 2
Joined: Sep 2004

Fri Sep 10, 04 05:25 PM

Quote
Originally posted by: DrBen
Quote
Originally posted by: Boofta
Quote
Originally posted by: DrBen
...unless some one works out and represents state in Excel.

What exactly do you mean by "state"?

At present you have state through custom interfaces like Solver.
In solver you "set" the fields within the custom interface like "Are you
solving an integer optimization problem or not" and so on. State within
business classes can be thought of as classes where the order in
which the methods are callled effects the result. A good example is in
an implementations of Portfolio theory, where a client application of a
Portfolio components implementation will generally look something like:

1) Create Portfolio Instance
<etc>
q]

Could you show us how you'd code this as a sequence of function calls,
using some of the arguments to pass this state if needed? I'm not
clear what exactly you mean by "state", though it sounds as though
you might be trying to optimise something by doing one computation on
a particular data structure, thereby changing some of its fields, and then
later using it for another computation which takes values from those fields.
In which case the fields would be the state. Is this what you mean?

In any case, seeing it as function calls would be helpful, because it would
make everything explicit.

Cheers,
Jocelyn Paine
www.j-paine.org

-------------------------
Hoje � o primeiro dia do resto da tua vida.

Posts: 147
Joined: Jul 2002

Sat Sep 11, 04 04:30 AM

Dr Ben, thank you for your detailed explanation.

I think you will find people have solved that problem, typically at IBs (I have experience with 5, and know of more) using the handle-based methodology mentioned earlier in the thread. I have worked with Excel-based portfolio optimisers and MC evaluators that work basically in the way you have described in your examples. Not Solver-like (which is a macro call), but normal cell-embedded formulae which are linked together like any other Excel formulae. You control the sequence (and hence state changes) by linking cells in the appropriate order - Excel then manages the dependencies as normal. The hassle is in rendering/displaying results, in which case the notion of a result-rendering interface would be handy.

Quote

Top

Bottom

DrBen
Senior Member

Posts: 494
Joined: Feb 2003

Sat Sep 11, 04 09:09 AM

Quote
Originally posted by: Boofta
Dr Ben, thank you for your detailed explanation.

I think you will find people have solved that problem...

The question as ever is not whether it can be done/has been done but has it been not in a user
friendly way. There are also cultural issues since Excel was designed with a C approach in mind
(i.e. functions only), whereas now we live in a .NET world.

Ignoring making anything which is usable, from a technical point of view I am not even aware of
any general tool on the open market which does this. If there is such a tool then I can save myself
a job and avoid re-inventing the wheel.

-------------------------
My software site: here

Posts: 147
Joined: Jul 2002

Sun Sep 12, 04 05:47 AM

... whereas now we live in a .NET world ... I am not even aware of any general tool on the open market which does this.

As I mentioned earlier in the thread, Managed XLL Object Handles made by Jens, who posts here occasionally. Specifically, go to the point "Create and use .NET objects from the worksheet".

Anyway, as mentioned earlier in the thread, if Excel let you store an Object reference in a cell, along with the relevant interfaces for method exposure (as sheet formulae), result rendering/display and type-coercion, I think the scenario/requirement you describe would be largely catered for.

Finally, one wish-list item for me: named arguments in spreadsheet formulae vis-a-vis calling functions/methods in VBA.

Edited: Sun Sep 12, 04 at 05:55 AM by Boofta

Quote

Top

Bottom

DrBen
Senior Member

Posts: 494
Joined: Feb 2003

Sun Sep 12, 04 10:31 AM

Boofta: The ManagedXLL does not seems to contained the representation of state on
the "server side" (i.e. state of the add-in) which I am thinking of. On the link you gave
there are no private fields or set methods, which is the way the sort of state I was thinking
of is usually handled.

-------------------------
My software site: here

Quote

Top

Bottom

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Mon Sep 13, 04 12:13 PM

C approach in mind (i.e. functions only),
Why not variables ?

-------------------------
Discussion on the new regulations on bonuses here.

Quote

Top

Bottom

DrBen
Senior Member

Posts: 494
Joined: Feb 2003

Mon Sep 13, 04 12:24 PM

If you use C language then I mean Global variables, which are
updated by functions. This would simulate what I call state using
C, but Workbooks themselves as far as I know are stateless, i.e.
the constructor does not take parameters. You can write the
global variables in cells (if they are primitives) but them you lose
the transparent .NET <---> Excel which I am wanting to work
towards.

-------------------------
My software site: here

Edited: Mon Sep 13, 04 at 12:25 PM by DrBen

Quote

Top

Bottom

sysmod
Junior Member

Posts: 8
Joined: Sep 2004

Tue Sep 14, 04 10:02 AM

I've just joined this forum now, I hope it's not too late to comment on some earlier posts.
I'd like to name some products that I think address some of the wishlist so far.

>>
Posted By: tristanreid (Member)
One thing that would be helpful is a way to 'dump' a workbook into a fully descriptive format. I mean something like a reference to all the formulae, the code, and named ranges. Does such a thing exist?
<<
Not ONE thing, but SpACE can give you lists of unique formulas, range names, etc.
Take a look at the tools I list above such as Spreadsheet Professional etc.

>>
Posted By: MichaelA (Member)
This probably sounds obvious, but to eliminate the problems of bugs creeping into Excel spreadsheets, does anyone else have a system of peer review of spreadsheets prior to their being released ?
<<
Peer review is the single best idea one can introduce, but personalities get in the way. It requires a mature corporate culture.

>>
Posted By: DCFC (Senior Member)
The other thing I'd add is type information.
<<
See www.visualstrongtype.com

>> A smart file comparator, so I can see changes, and possibly their history.

Many auditing tools do that such as SpACE and Operis OAK.

>>I'd like to be able to single step through caculations.
In Excel 2002+

>>Obviously I'd like to be able to easily set breakpoints that calculation would freeze when a cell was hit, or when the range had some value or state.
Try Validation.

>>Exception events.
GoTo Error values.

>When the reference gets broken, I would like this to firstly call a handler as a VBA event.
Very nice idea.

>>get the formulas it is dependant upon.
Trace Precedents.

>>And see their values in a separate window.
XLDrill, ExChecker, maybe more.

>>I'd like to be able to search with Wildcards. ... across files,
Wilbur from redtree.com
ExChecker from Spreadsheet auditing.com
My own ScanXLS

>>It would be not very hard to add profiling, such that one could tell how often a cell was being calculated, and the contribution it made to calc times.
Chip Pearson has a VBA profiler. Now, a VBA code coverage tool would be very handy.
Anybody know a BNF for VBA?

>>C++ allows elegant code like Rates["GBP"] = 0.69434, rather than some ghastly mess of offset and vlookups.
Why not range name GBP or GBP_EURO or something?

>Exporting a speadsheet as a function would require the build-in spreadsheet functions to be available externally.
see Savvysoft.com for Excel DLL compiler

>>I like the idea of a Developer edition of Excel, and the ability to create spreadsheet objects of various types.
Oh yes please.. I wonder is there some extension set in MSDN?

>>-interface 'CellListener' ..etc..
Nice!

>>Finally, one wish-list item for me: named arguments in spreadsheet formulae vis-a-vis calling functions/methods in VBA.
Another goodie.

Patrick O'Beirne
http://www.sysmod.com/spreads.htm
Web page of links to sites on spreadsheet style, good practice, quality, etc.
http://www.sysmod.com/sslinks.htm

The EUSPRIG discussion group is at
http://groups.yahoo.com/group/eusprig

-------------------------
Patrick O'Beirne, Systems consultant
Spreadsheet model review, test, audit
[L=http://www.sysmod.com]http://www.sysmod.com[/L] +353 55 22294
.

Edited: Fri Dec 31, 04 at 02:43 PM by sysmod

Quote

Top

Bottom

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Tue Sep 14, 04 11:39 AM

Not ONE thing, but SpACE can give you lists of unique formulas, range names, etc.
Have you got a URL for SpACE ? Googling for it lists too many other things.

Peer review is the single best idea one can introduce, but personalities get in the way. It requires a mature corporate culture.
Oh god yes.
My favourite was a stand up row I had with an incoherent Microsoftie shouting "do you expect to go on doing this again and again forever until there are no fucking bugs left, no matter how much it costs ?"
Scary. The bug I had found was in the ChkDsk disk fixing utilility which if you clicked it "wrong" would break your disk.

See www.visualstrongtype.com
Looks cool, will report back.

>>I'd like to be able to single step through caculations.
In Excel 2002+
Good point, but are you 100% convinced the order of calculation is the same as in normal mode ?

>>Obviously I'd like to be able to easily set breakpoints that calculation would freeze when a cell was hit, or when the range had some value or state.
Try Validation.
A valid work around, but I can't see a way of making that clean.

>>Exception events.
GoTo Error values.
Falls into the corner retching uncontrollably

>>get the formulas it is dependant upon.
Trace Precedents.
I find this facility as so close to useful that it really pisses me off.
It is too sheet based, and I need it most when the formula is across multiple sheets. As for the range properties for dependancy, I have things growing in my fridge which would do a better job.

>>I'd like to be able to search with Wildcards. ... across files,
Wilbur from redtree.com
ExChecker from Spreadsheet auditing.com
My own ScanXLS does that for free.

>Exporting a speadsheet as a function would require the build-in spreadsheet functions to be available externally.
see Savvysoft.com for Excel DLL compiler
Yeah, but its a) thousand bucks, b) doesn't seem to cope with VBA.

Now ,why can't all newbies be like sysmod ?

-------------------------
Discussion on the new regulations on bonuses here.

Quote

Top

Bottom

sysmod
Junior Member

Posts: 8
Joined: Sep 2004

Tue Sep 14, 04 12:02 PM

Quote
Originally posted by: DCFC
Not ONE thing, but SpACE can give you lists of unique formulas, range names, etc.
>>Have you got a URL for SpACE ? Googling for it lists too many other things.

http://tinyurl.com/6e4m6

>>I'd like to be able to single step through caculations.
In Excel 2002+
>>Good point, but are you 100% convinced the order of calculation is the same as in normal mode ?

Ummm... I have no reason to believe it would be. After all, the order of precedence of operators is fixed, yes? (Please, let it be)

>>Obviously I'd like to be able to easily set breakpoints that calculation would freeze when a cell was hit, or when the range had some value or state.
>>Try Validation.A valid work around, but I can't see a way of making that clean.

Yes, you're right, it's not really getting into the state while calculating, but unless you're using iteration it should be useful.

>>Exception events. GoTo Error values.
>>Falls into the corner retching uncontrollably

Heh. yes, nothing to do with *events* all right, but I stil don't see in normal models how a cell that ends up with say #ERR would be anything else during the calc. It should be visited once. I'm assuming al lthe time no iteration, as above.

>>get the formulas it is dependant upon.
Trace Precedents.
>>It is too sheet based, and I need it most when the formula is across multiple sheets.

OK, then Xdrill sounds more like what you want, or ExChecker's tree diagram.

regards

Patrick

-------------------------
Patrick O'Beirne, Systems consultant
Spreadsheet model review, test, audit
[L=http://www.sysmod.com]http://www.sysmod.com[/L] +353 55 22294
.

Quote

Top

Bottom

sysmod
Junior Member

Posts: 8
Joined: Sep 2004

Wed Sep 15, 04 03:28 PM

Quote
>>Good point, but are you 100% convinced the order of calculation is the same as in normal mode ? Ummm... I have no reason to believe it would be.

<cough>
Of course, I meant to say that I believe the order of calculation IS the same as in normal mode.

BTW, anybody interested in pursuing strong typing and units checking might also want to see these:

http://www.cs.brown.edu/~sk/Publications/Papers/Published/asknf-valid-unit-sprdsht/
Validating the Unit Correctness of Spreadsheet Programs
Tudor Antoniu, Paul A. Steckler, Shriram Krishnamurthi, Erich Neuwirth
In this paper, we describe XeLda, our tool for unit checking Excel spreadsheets. The tool highlights cells if their formulas process values with
incorrect units and if derived units clash with unit annotations. The tool is sensitive to many of the intricacies of Excel spreadsheets including
tables, matrices, and even circular references. Using XeLda, we have detected errors in some published scientific spreadsheets.

http://lambda-the-ultimate.org/classic/message6689.html Ehud Lamm - XeLda: Enforcing Dimensional Consistency in Spreadsheets 5/4/2003; 4:57:12 AM

www.cs.brown.edu/~sk/Publications/ Papers/Published/aagk-type-error-sprdsht/
A Type System for Statically Detecting Spreadsheet Errors
Yanif Ahmad, Tudor Antoniu, Sharon Goldwater, Shriram Krishnamurthi

-------------------------
Patrick O'Beirne, Systems consultant
Spreadsheet model review, test, audit
[L=http://www.sysmod.com]http://www.sysmod.com[/L] +353 55 22294
.

Quote

Top

Bottom

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Thu Sep 16, 04 03:06 PM

Of course, I meant to say that I believe the order of calculation IS the same as in normal mode.

I've looked at subverting the watch mechanism for my own purposes, since I have what looks remarkably like a Sheet Compiler, but its elegance is gravely compromised by the properties which proport to tell you the dependancies of a cell. First they only give those that are on the sheet, even though the documentation implies in some places it should give all references.

Equally badly, the order of the collection has no correlation with order of evaluation. I don't know, but I'd guess that a Microsofite who wrote the watcher , might try to use the MS routines.

-------------------------
Discussion on the new regulations on bonuses here.

Quote

Top

Bottom


	Pages: [ << 1 2 3 Previous ]

FORUMS > General Forum

< refresh >

Forum Navigation: