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

["Warren Turkal" <turkal@google.com>]

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.

>  > 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.

>  >  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. 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.

>  >  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?

>  > 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). 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.

wt