Numeric Types

Besides the default double, AFC supports a number of alternative numeric types you can choose from for the computations performed by generated engines. This is because double is often not precise enough for financial applications.

Choices

double

is the default. It is what Excel and other spreadsheet applications use internally. So this choice is consistent with how the original spreadsheet behaves.

BigDecimal

can be used with a either a fixed precision (MathContext), unlimited precision, or using a fixed scale. With a fixed precision, this type is a robust general choice for financial applications.

long

can be used either as an integer type, or using a fixed scale. With a fixed scale of 4 decimal places, this type corresponds to the currency type found in Microsoft COM and Borland Delphi. Support for long might be dropped unless we see real demand.

Note that the choice of numeric type affects the entire engine compiled by a given compiler, not just individual cells.

Using double

Since double is the default, you can simply use the default engine builder, without specifying the type. If you want to give the type explicitly, use:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( file );
builder.setFactoryClass( Factory.class );
builder.setNumericType( SpreadsheetCompiler.DOUBLE );
builder.bindAllByName();
Engine engine = builder.compile();
Factory factory = (Factory) engine.getComputationFactory();

When using double, the natural type for our numeric inputs and outputs is, of course, double as well. Other types are converted by AFC to a double prior to using them in the computation.

public static class Input
{
  public double getA() { return 1.0; }
  public double getB() { return 6.0; }
}

public static interface Output
{
  double getResult();
  double getNegated();
}

public static interface Factory
{
  Output newInstance( Input _input );
}

In this example, we divide InputA by InputB and add InputA, ie. 1 + 1/6. The result is returned with double’s precision:

Output output = factory.newInstance( new Input() );
assertEquals( "1.1666666666666667", String.valueOf( output.getResult() ) );

as is witnessed by the generated engine:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.UsingDouble;

final class $Root implements Computation, UsingDouble.Output
{
    private final UsingDouble.Input $inputs;
    final Environment $environment;
    
    $Root(UsingDouble.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final double get$0() {
        return get$1() / get$2() + get$1();
    }
    
    public final double getResult() {
        return get$0();
    }
    
    final double get$1() {
        return $inputs.getA();
    }
    
    final double get$2() {
        return $inputs.getB();
    }
    
    final double get$3() {
        return -get$1();
    }
    
    public final double getNegated() {
        return get$3();
    }
}

Using BigDecimal

BigDecimal can be used with either an arbitrary, fixed precision, or an arbitrary, fixed scale. In both cases you also specify a rounding mode. The scale variant is, however, a choice that is difficult for users to fully understand and is therefore not really recommended.

Predefined BIGDECIMAL128

In this example, we choose BigDecimal with great precision (34 digits), and IEEE rounding. We only need to change one line compared to the builder setup for the double example:

builder.setNumericType( SpreadsheetCompiler.BIGDECIMAL128 );

The numeric type used is predefined by AFC and corresponds to MathContext.DECIMAL128. To quote: a precision setting matching the IEEE 754R Decimal128 format, 34 digits, and a rounding mode of HALF_EVEN, the IEEE 754R default.

The natural type for our numeric inputs and outputs is now BigDecimal. Again, other types are converted by AFC to a BigDecimal prior to using them in the computation.

public static class Input
{
  public Input( double a, double b ) { this.a = BigDecimal.valueOf( a ); this.b = BigDecimal.valueOf( b ); }
  public BigDecimal getA() { return this.a; }
  public BigDecimal getB() { return this.b; }
  private final BigDecimal a;
  private final BigDecimal b;
}

public static interface Output
{
  BigDecimal getResult();
  BigDecimal getNegated();
}

We again compute 1 + 1/6. The result is returned with the increased precision we demanded, rounded up:

Output output = factory.newInstance( new Input( 1, 6 ) );
assertEquals( "1.166666666666666666666666666666667", output.getResult().toPlainString() );

If, instead, we compute 1 + 1/3, we get a result that is rounded down since the last 3 is less than a half:

Output output = factory.newInstance( new Input( 1, 3 ) );
assertEquals( "1.333333333333333333333333333333333", output.getResult().toPlainString() );

All this is again witnessed by the generated engine:

package org.formulacompiler.gen;
import java.math.BigDecimal;
import java.math.MathContext;

import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.RuntimeBigDecimal_v2;
import org.formulacompiler.tutorials.AbstractUsingBigDecimalTest;

final class $Root implements Computation, AbstractUsingBigDecimalTest.Output
{
    private final AbstractUsingBigDecimalTest.Input $inputs;
    final Environment $environment;
    static final MathContext mathContext = MathContext.DECIMAL128;
    
    $Root(AbstractUsingBigDecimalTest.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final BigDecimal get$0() {
        return get$1().divide(get$2(), mathContext).add(get$1(), mathContext);
    }
    
    public final BigDecimal getResult() {
        return get$0();
    }
    
    final BigDecimal get$1() {
        return RuntimeBigDecimal_v2.toNum($inputs.getA());
    }
    
    final BigDecimal get$2() {
        return RuntimeBigDecimal_v2.toNum($inputs.getB());
    }
    
    final BigDecimal get$3() {
        return get$1().negate();
    }
    
    public final BigDecimal getNegated() {
        return get$3();
    }
}

Explicit MathContext

We can also use a freely defined MathContext:

MathContext mathContext = new MathContext( 4, RoundingMode.HALF_UP );
builder.setNumericType( SpreadsheetCompiler.getNumericType( BigDecimal.class, mathContext ) );

We again compute 1 + 1/6. The result is returned with the defined precision of 4 significant digits, rounded up since the last 6 is higher than a half:

Output output = factory.newInstance( new Input( 1, 6 ) );
assertEquals( "1.167", output.getResult().toPlainString() );

If, instead, we compute 1’000’000 + 1’000’000 / 3, we can see what 4 significant digits means:

Output output = factory.newInstance( new Input( 1000000, 6 ) );
assertEquals( "1167000", output.getResult().toPlainString() );

Precision Is Minimum

Note, however, that the given precision is only a minimum, not a prescription. AFC will not normally truncate values to the supplied precision unless performing operations that might increase the precision. So a simple negation of a value leaves the precision you provide in the input value intact:

Output output = factory.newInstance( new Input( 12345678, 1 ) );
assertEquals( "-12345678", output.getNegated().toPlainString() );

Explicit Fixed Scale

Instead of a fixed precision, we can choose a fixed scale (digits after the decimal point). In this example, we choose 4 digits after the point, and always round up. We only need to change one line compared to the builder setup for the double example:

builder.setNumericType( SpreadsheetCompiler.getNumericType( BigDecimal.class, 4, BigDecimal.ROUND_UP ) );

We again compute 1 + 1/6. The result is returned with the scale we demanded, rounded up:

Output output = factory.newInstance( new Input( 1, 6 ) );
assertEquals( "1.1667", output.getResult().toPlainString() );

When we again compute 1’000’000 + 1’000’000 / 3, we can clearly see the difference of scale vs. precision above:

Output output = factory.newInstance( new Input( 1000000, 6 ) );
assertEquals( "1166666.6667", output.getResult().toPlainString() );

Unlike the precision, the scale is always enforced:

Output output = factory.newInstance( new Input( 12.345678, 1 ) );
assertEquals( "-12.3457", output.getNegated().toPlainString() );

All this is again witnessed by the generated engine:

package org.formulacompiler.gen;
import java.math.BigDecimal;

import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.RuntimeBigDecimal_v2;
import org.formulacompiler.tutorials.AbstractUsingBigDecimalTest;

final class $Root implements Computation, AbstractUsingBigDecimalTest.Output
{
    private final AbstractUsingBigDecimalTest.Input $inputs;
    final Environment $environment;
    
    $Root(AbstractUsingBigDecimalTest.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final BigDecimal get$0() {
        return get$1().divide(get$2(), 4, 0).add(get$1());
    }
    
    public final BigDecimal getResult() {
        return get$0();
    }
    
    final BigDecimal get$1() {
        return RuntimeBigDecimal_v2.toNum($inputs.getA()).setScale(4, 0);
    }
    
    final BigDecimal get$2() {
        return RuntimeBigDecimal_v2.toNum($inputs.getB()).setScale(4, 0);
    }
    
    final BigDecimal get$3() {
        return get$1().negate();
    }
    
    public final BigDecimal getNegated() {
        return get$3();
    }
}

Unscaled BigDecimal

Let’s use a BigDecimal without a fixed scale:

builder.setNumericType( SpreadsheetCompiler.getNumericType( BigDecimal.class ) );

With this beast, you have to be very careful about the computations your users specify in the spreadsheets. This is because BigDecimal will fail for non-representable fractions. The value of 1 + 1/4 can be computed ok:

Output output = factory.newInstance( new Input( 1, 4 ) );
assertEquals( "1.25", output.getResult().toPlainString() );

But 1 + 1/3 does not have a finite decimal representation, so BigDecimal faults:

try {
  Output output = factory.newInstance( new Input( 1, 3 ) );
  output.getResult();
  fail( "ArithmeticException expected" );
}
catch (ArithmeticException e) {
  assertEquals( "Non-terminating decimal expansion; no exact representable decimal result.", e.getMessage() );
}

as is again witnessed by the generated engine:

package org.formulacompiler.gen;
import java.math.BigDecimal;

import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.RuntimeBigDecimal_v2;
import org.formulacompiler.tutorials.AbstractUsingBigDecimalTest;

final class $Root implements Computation, AbstractUsingBigDecimalTest.Output
{
    private final AbstractUsingBigDecimalTest.Input $inputs;
    final Environment $environment;
    
    $Root(AbstractUsingBigDecimalTest.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final BigDecimal get$0() {
        return get$1().divide(get$2()).add(get$1());
    }
    
    public final BigDecimal getResult() {
        return get$0();
    }
    
    final BigDecimal get$1() {
        return RuntimeBigDecimal_v2.toNum($inputs.getA());
    }
    
    final BigDecimal get$2() {
        return RuntimeBigDecimal_v2.toNum($inputs.getB());
    }
    
    final BigDecimal get$3() {
        return get$1().negate();
    }
    
    public final BigDecimal getNegated() {
        return get$3();
    }
}

Using long

The long type can be used either as an integer type, or in scaled mode. In scaled mode, it is still a long, but interpreted as if the n rightmost digits were decimal places.

Note: Support for long might be dropped unless we see real demand. This because its limited and fixed precision leads to a fairly unintuitive numerical model for your typical spreadsheet user.

Unscaled long

Let’s use a plain long without scaling:

builder.setNumericType( SpreadsheetCompiler.LONG );

If we compute 1 + 1/6 here, we simply get 1:

Output0 output = (Output0) factory.newComputation( new Input0( 6 ) );
assertEquals( 1L, output.getResult() );

as is again witnessed by the generated engine:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.UsingScaledLong;

final class $Root implements Computation, UsingScaledLong.Output0
{
    private final UsingScaledLong.Input $inputs;
    final Environment $environment;
    
    $Root(UsingScaledLong.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final long get$0() {
        return get$1() / get$2() + get$1();
    }
    
    public final long getResult() {
        return get$0();
    }
    
    final long get$1() {
        return $inputs.getA();
    }
    
    final long get$2() {
        return $inputs.getB() / 1000L;
    }
    
    final long get$3() {
        return -get$1();
    }
    
    public final long getNegated() {
        return get$3();
    }
}

Scaled long

In this example, we choose 3 digits after the decimal point. Note that as of release 0.4.1, scaled long does not support rounding modes. It always truncates. Again, we only need to change one line compared to the builder setup for the double example:

builder.setNumericType( SpreadsheetCompiler.getNumericType( Long.TYPE, 3 ) );

The natural type for our numeric inputs and outputs is now long. But we have a problem here: long is not normally scaled. So AFC needs a way to differentiate between values that are normal, unscaled long values, and values that are scaled.

AFC does not simply assume all long values to be scaled. This would be unexpected behaviour. And it would prohibit using the same interface for engines of different numeric types. For example, you could have a base interface you use for both scaled and unscaled long computations, which has a long getNumberOfItems() method.

There are two ways you can tell AFC that a long is already scaled:

• Use the annotation ScaledLong on the input or output method.
• Use the annotation ScaledLong on the entire input or output type. This sets the default for all long return types of method of the type that don’t override the scale themselves.

The annotation controls the scale of the value obtained or returned by AFC. This scale is independent of the scale AFC uses for the internal computations. If the scales equal, then no conversion is needed. Otherwise AFC rescales the values appropriately.

public static class Input
{
  public Input( int b ) { this.b = b; }
  public long getA() { return 1; } // will be scaled by AFC
  public @ScaledLong( 3 ) long getB() { return ScaledLongSupport.scale( this.b, 3 ); }
  private final int b;
}

@ScaledLong( 3 )
public static interface Output
{
  long getResult();
  long getNegated();
}

We again compute 1 + 1/6. The result is a scaled long (so 1166L is the scaled long equivalent of 1.166). We get 1.166 and not 1.167 because the scaled long always truncates results (this is how long itself works in Java):

Output output = factory.newInstance( new Input( 6 ) );
assertEquals( 1166L, output.getResult() );

as is again witnessed by the generated engine:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.UsingScaledLong;

final class $Root implements Computation, UsingScaledLong.Output
{
    private final UsingScaledLong.Input $inputs;
    final Environment $environment;
    
    $Root(UsingScaledLong.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final long get$0() {
        return get$1() * 1000L / get$2() + get$1();
    }
    
    public final long getResult() {
        return get$0();
    }
    
    final long get$1() {
        return $inputs.getA() * 1000L;
    }
    
    final long get$2() {
        return $inputs.getB();
    }
    
    final long get$3() {
        return -get$1();
    }
    
    public final long getNegated() {
        return get$3();
    }
}

Numeric Type Is A Global Setting

Why is the numeric type set once per compiler, and not individually, per input or output cell? After all, consider a computation where we have as input the integer lengths of the three sides of a block in millimetres, and the price per millimetre cubed. We want the total price. So, typically:

total := (width * height * depth) * price_per_mm3;

Since all of the dimensions are integer, we might code this as:

long width, height, depth;
BigDecimal total, price_per_mm3;

This would ensure that the two multiplications computing the volume would run at long-speed. Is this correct? It depends. If you can be sure that the volume will always fit into the long range, then yes. But even if this were so, once you use AFC to outsource this computation to a spreadsheet, you lose control over the exact way the computation is performed. It could, for instance, be changed by one user to:

total := (width / 1000) * (height / 1000) * (depth / 1000) * price_per_m3;

where price_per_m3 is looked up in a table. What should AFC choose as the data type for the intermediate divisions? Hmm.

An even simpler change, building a slight oversize into the computation, would be:

total := ((width + 2) * (height + 2) * (depth + 2)) * price_per_mm3;

So you – the programmer – may have been sure before that the volume would always fit into a long. But how can AFC be sure that the slightly larger volume computed above will still fit? It cannot.

In view of these overflow and precision problems, AFC does not try to be clever about inferring fast types. Every simple addition of input values already forces escalation to a bigger and slower type, so without hints from outside, AFC would have to infer slow types for nearly everything very quickly. Who could give the hints? You, the programmer, cannot, because you do not know the computations performed by the sheets defined by users later on. So it would have to be the sheet designers. I cannot imagine them caring about and being able to specify overflow conditions.

What you can tell AFC is the general class of computation you are dealing with. You simply specify the type being used for all numeric computations by a particular engine.

The responsibility for this choice, and for communicating its consequences to the sheet designers, rests fully with you, the programmer. But it does allow you to generate engines suited for precise financial or very fast pure integer computations.

Beware!

Scaled Multiplication

That the choice of numeric type affects the entire engine is particularly problematic for scaled types because of their limited precision. Consider the case of a ScaledLong(6) engine being used to compute 1.2 * 10’000’000. The result, 12’000’000 would easily fit into the scaled long, but the problem is the intermediate result, which is at double the scale for every multiplication. If we look at the actual long values used, it becomes clear:

long scale = 1000000L;
long a = (long) (1.2 * scale);
long b = 10000000L * scale;
long intermediate = a * b;
long result = intermediate / scale;

assertEquals( 1200000L, a );
assertEquals( 10000000000000L, b );
assertEquals( -6446744073709551616L, intermediate ); // silent integer overflow!
assertEquals( -6446744073709L, result );

whereas multiplying by only 10’000 works ok:

long scale = 1000000L;
long a = (long) (1.2 * scale);
long b = 100000L * scale;
long intermediate = a * b;
long result = intermediate / scale;

assertEquals( 1200000L, a );
assertEquals( 100000000000L, b );
assertEquals( 120000000000000000L, intermediate );
assertEquals( 120000000000L, result );

So, in essence, you lose the size of the scale in precision as soon as you start doing multiplication.

Seconds

Another issue is that all time values in Excel are stored as numbers internally. To represent seconds, you need at least a ScaledLong(6).