Re: [office] Excel 2007 != Ecma spec YEARFRAC. Not even slightly.What should we do?

["Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>]

On Fri, 2008-04-18 at 13:15 -0700, Warren Turkal wrote:
> On Fri, Apr 18, 2008 at 12:11 PM, Andreas J. Guelzow
> <aguelzow@math.concordia.ab.ca> wrote:
> >
> >  On Fri, 2008-04-18 at 10:13 -0700, Warren Turkal wrote:
> >  > On Fri, Apr 18, 2008 at 7:00 AM, David A. Wheeler <dwheeler@dwheeler.com> wrote:
> >  > >  The problem is that basis values need not be a constant in the cell; it might be
> >  > >  from another cell and/or a calculation (directly or indirectly), e.g.:
> >  > >  YEARFRAC([.A5];[.B5];[.C5]+1)
> >  >
> >  > This seems like an unrealistic use case. In all sincerity, why would
> >  > someone do that? Is it common? I just don't see folks selecting the
> >  > basis by saying I want whatever the basis is identified by taking the
> >  > identifier of some basis (e.g. actual/actual) and adding 1 to it. I
> >  > cannot think of any valid use of that technique.
> >
> >  Well, how would you translate the formula
> >  YEARFRAC([.A5];[.B5];[.C5])
> 
> How would you translate it if it is some other spreadsheet than Excel
> 2007. That's not the only target of import filters.

What does this have to do with Excel? Currently various program try to
emulate what Excel is doing with these bases. If you rather use what
another program is doing that is fine with me, just don't pick the very
worst so that all imports are messed up.

And I think it is a safe bet that most spreadsheets these days are in
some Excel format.
  
> 
> >  > Having said that. If people do it, import filters can just map it to
> >  > whatever fixed string it's value corresponds to on import.
> >
> >  But that does not give you the same formula. Potentially, the content of
> >  cell C5 is calculated dependent on the type of a certain investment. SO
> >  this may be indeed a valid use case for a spreadsheet.
> 
> We can't just use the IDs from Excel directly if we want to be
> compatible with other spreadsheet programs and formats.

Please explain which conflict you are seeing? I was really hoping that
we use Gnumeric's IDs (and get Excel's for free in the process.) 
> 
> >  >  It's not
> >  > that big of a deal. Just map the basis IDs to strings.
> >
> >  How do you identify which numbers in teh program would need to change to
> >
> > strings?
> 
> How do you identify which numbers need to change to which numbers if
> you use integer IDs. 

I can do that calculation easily inside the yearfrac invocation. If my
program uses ids that are 20 bigger than excels then when I import from
excel I just turn  
YEARFRAC([.A5];[.B5];[.C5])
into 
YEARFRAC([.A5];[.B5];[.C5]+20)

this is much simpler than constructing a complicated formula involving
strings.

> Using integer IDs doesn't change the problem, as
> I see it. The problem, as I see it, is to have unique IDs for all the
> basis algs so that we can represent anything imported into and ODF
> spreadsheet app.

Well, using strings may be pretty but it makes import/export more
complicated.

> 
> >  >  Maybe you would
> >  > vary the map depending the version of the program that produces the
> >  > file being imported.
> >  >
> >  > >  I'd expect most basis values to be embedded constants.  But if we can make life easier for
> >  > >  those doing transitions, or having to convert back-and-forth, we should try.
> >  >
> >  > The help that we provide should be a consistent representation of
> >  > various algorithms. For instance,
> >  > YEARFRAC(A5;B5;"msexcel_2007_actual_actual") would be the
> >  > representation for calculating YEARFRAC with the reverse engineered MS
> >  > Excel 2007 Actual/Actual basis algorithm. If you want to only specify
> >  > the basis in one place, create another sheet and store it there. For
> >  > example:
> >  >
> >  > Config.A1="msexcel_2007_actual_actual"
> >  > Calc.A5=a date
> >  > Calc.B5=b date
> >  > Calc.C5==YEARFRAC(A5;B5;Config.A1)
> >
> >  This is all nice and cute but completely useless for existing files.
> >
> >  For example let's say a sheet has Config.A1=0
> >  and that cell is used both as an index into a table and as a base for
> >  the yearfrac. How would you automatically modify the sheet on import?
> 
> That seems like another artificial example to me. However, how will
> you deal with it when you cannot represent what Lotus123 thinks is
> basis==0 with basis==0?

see above. It would just be nice if all the common programs
interpretations can be just a constant offset from ODF's, even if that
may require several ids for the same basis algorithm.
> 
> >  > Spreadsheet programs could provide an interface to alter the basis of
> >  > all formulas if they wanted.
> >
> >  You are assuming that the basis is fixed for the whole sheet. Different
> >  investments require different bases so it is very likely that the same
> >  sheet will in fact use several bases, or bases calculated or looked up
> >  for the different investments.
> 
> I never said that the individual formulas couldn't have different
> bases. I said that a tool could be used to change the whole sheet for
> what I presume to be a fairly common case. The case I imagine is the
> someone imports a spreadsheet from some other app that only has one
> basis (e.g. a number of mortgage calculations).

I would think that that is a pretty artificial example.

>  Now they want to
> migrate to using some other basis, the interface would allow them to
> easily do such a thing. It could even allow them to change only X
> basis to Y basis in order to be more generally applicable.
> 
> >  >  They could even ask whether the user
> >  > wants to emulate the basis calculations of the spreadsheet being
> >  > imported on opening the file.
> >  >
> >
> >  see above
> 
> In the end, allowing the representation of other formats within the
> constraints of ODF is what we should be looking for. If we can make it
> easier to write import filters, I think we should do that. However, we
> shouldn't focus on only one import case (MS Excel 2007 in this case).
> We need to make sure that we have a solution that is workable for
> importing all kinds of formats (MS Excel 95-2007, Lotus123, blah blah
> blah).
> 
> And to make it clear. While I still support the idea of using string
> identifiers, I wouldn't oppose using integers to much.
> 

Andreas