DevTalk.net

ActiveMesa's software development blog. MathSharp, R2P, X2C and more!

Five Ways of Improving Excel to Code Conversion

with 2 comments

Converting Excel to compile-ready C++ or C# code isn’t as easy as you might think: there are plenty of cases where generating code straight-ahead is either inefficient or would result in incorrect code.

Here is an overview of five different cases where X2C performs adjustments.

1. Sequence Folding

Say you decide to map a few cells containing values {0, 1, 2, 3, 4} to a vector:

If you were to perform initialization of this vector, it might look as follows:

double Foo[5];
void InitializeFoo()
{
  Foo[0] = 0;
  Foo[1] = 1;
  Foo[2] = 2;
  Foo[3] = 3;
  Foo[4] = 4;
}

The above is correct, but tedious. Wouldn’t it be great if we could detect repetition like this and put it in a loop? Well, X2C does exactly this, so your generated code will look as follows:

double Foo[5];
void InitializeFoo()
{
  for (int i = 0; i < 5; ++i)
    Foo[i] = i;
}

X2C does its best at detecting row/column-dependent values and, if it can see that even a part of the elements can be initialized in a for loop, it rewrites the code accordingly.

2. Range Function Substitutions

Let’s say you decided to find smallest of the elements above:

What sort of code would you expect? Maybe something like

double Min() const
{
  return std::min(Foo[0], std::min(Foo[1], std::min(Foo[2],  // you get the idea
}

Well, X2C could just give you that, but it’s a lot smarter. Depending on the language, X2C knows about ways of aggregating values so, for the above case, the generated code would be as follows:

double Min() const
{
  return *std::min_element(std::begin(Foo), std::end(Foo));
}

3. Complex Aggregate Calculations

Sometimes, though, aggregations get complicated – not just a simple sum, average or calculation of smallest or largest element, but rather something like the following:

In this case, what X2C does is perform a walk over each of the elements and outputs the final result based on element-wise reads, i.e.:

double FooBar() const
{
  return (Foo[0]*Bar[0] + Foo[1]*Bar[1] + Foo[2]*Bar[2] + 
    Foo[3]*Bar[3] + Foo[4]*Bar[4]);
}

In future, we aim to teach X2C to fold even these types of expressions into loops, as the above could be calculated using a simple for loop and just one temporary variable.

4. Synthetic Evaluation for Simple Functions

Some functions which appear deceptively easy in Excel require additional evaluation steps when translated to code. Here’s a simple example:

What would you expect X2C to do with the TRUNC() function in the above example? Well, for functions which are difficult to immediately express using existing APIs, X2C simply rewrites them the best way it knows. For the above, you’d get the following code:

double Foo::Pi() const
{
  return M_PI;
}
double Foo::PiTruncated() const
{
  return (boost::math::round(Pi() * 100) / 100);
}

The value 100 in the above comes from the fact we need to truncate to 2 decimal places (102 = 100).

5. Compile-Time Evaluation

Sometimes, the parameters to an Excel function actually determine which function to call. In this case, X2C simply checks the parameter at ‘compile-time’ and writes the appropriate code. Here’s an example:

In the above, it’s the 4th parameter to the normal distribution calculation that’s the problem. If set to FALSE, this is a calculation of the probability density function φ(x), whereas if it is TRUE, we need to compute the cumulative distribution function Φ(x) instead.

To get around this, X2C simply looks at the parameter value and infers the invocation for that. So for the cell above, you would get the following function:

double Foo() const
{
  return boost::math::cdf<>(
    boost::math::normal_distribution<>(0, 1), 0.5);
}

Conclusion

These are just some of the ways in which X2C improves the conversion process. If you want to see these, and many other transformations in action, download X2C and give it a go! ■

Written by Dmitri Nesteruk

August 14th, 2013 at 8:16 am

Posted in X2C

Tagged with , ,

  • Marek

    Can excel formulas, macros, populating of cells with data, be converted to an executable program?I am interested to find out if I can automate – for now I populate cells with data from measurements (obtained in text format) manually, use Solver for further calculations to obtain end result.

    • http://devtalk.net Dmitri

      X2C lets you convert Excel spreadsheets to source code. They you can simply compile it and there you go – you have an executable program.