Series precision during import/export

[jthodge]

I have a few hundred Excel files that I read in using the import command in a for-next loop. The numbers from the first file are integer values. Other files may contain integers or values up to a precision of two decimal places. I append the imported series from each new file to the the first workfile page, thus creating a stacked series containing the values from all the Excel files.

I've noticed when browsing through the stacked series view (by looking at the numbers on the status line) that once in a while, a value might show up as, for example, 547.0399999999999 instead of the 547.04 value it assumed in the Excel source file. Most two-digit values show up correctly; the integer values show up without any decimal places.

This becomes an issue when I try to export the stacked series to a csv file. Specifically if I do the export by selecting the relevant series in the workfile, right-clicking, and selecting export to file, then these high-precision entries show up instead of the two-digit values. However, I've noticed that if I instead do the export via the Eviews menu File -> Export -> Write Text-Lotus-Excel, then the numbers export correctly.

I'm curious as to why there is a difference in the two export procedures? And why does Eviews sometimes increase the precision of these two-digit values in the first place?

P.S. I'm using Eviews 7.1 (March 15 2011 build)

[startz]

This is almost certainly a display issue. Most likely, the internal representation is the same.

[EViews Chris]

The underlying cause for the weirdness that you're seeing is coming from the fact that numbers such as 547.04 aren't exactly representable in binary floating point format which is what EViews uses to hold the data (and is also what is used inside the floating point unit of your CPU).

All binary floating point numbers have the form: mantissa * (2 ^ expt) where mantissa and exponent are integers.

It's the power of two part that causes the problems. You're used to seeing numbers as decimals which use a power of ten instead (i.e. mantissa * (10 ^ expt)).

The problem is that numbers that are exactly representable in one floating point base may not be exactly representable in another. A simple example that you're familiar with is how one third is quite simple in base 3 floating point since it is equal to 1 * (3^ -1), but in base 10 (decimal) it can only be represented inexactly with an approximation such as .333333333.

All this is a long-winded way of saying that when a number such as 547.04 is read into EViews, it needs to be converted into the nearest possible binary floating point value which may not be identical to the original decimal value. Then, every time the number is displayed or written out to a text file, it is converted back into decimal. When a large number of digits are used in the decimal output, you may sometimes see this underlying inexact representation showing up in the output. It's unlikely that this will be of any real consequence since the differences we're talking about are typically of the order of 10^-17.

As to why the two export procedures are different, the old Write Text-Lotus-Excel procedure appears to write out values using 12 decimal digits of precision while the newer Workfile...Save As procedure writes out the number with 17 decimal digits of precision. The second format is more accurate, but is much more likely to create the sort of 99999 problems that you're seeing. I can't think of any good reason why we have chosen to use the two different formats - I'll look into whether we should change the first procedure to use the same precision as the second.