What is a calculation engine based on table functions?

There is some confusion about the meaning of calculation engine because this term may have different meanings for different people in different contexts.

finaquant calcs: A real calculation engine with table functionsOther than chained formulas in excel (see calculation engine with scalar parameters below), I have personally heard the term calculation engine mostly in the context of financial planning and fee or commission calculations. Actually, calculation engines can be used in any area where chained formulas with a number of input and output parameters are needed.

Overview: Primary calculation engine features

  1. Ability to configure dependency trees with input and output parameters combined through (table) functions determining the dependencies. Dependent output parameters are updated automatically if an input parameter is changed.
  2. A decent library of (table) functions as well as a structure for enabling the users formulate their own formulas (user-defined functions).
  3. Ability to configure multiple input and output (table) parameters related with nodes of a calculation network (function tree).
  4. Managing and storing multiple calculation instances of a function tree. As an example, a function tree for computing the expected month-end price of a product which must be executed at the beginning of every month. This function tree has then a calculation instance for every month.

The features 1-2 define for us (by definition) a calculation engine in the narrow sense.

All the features above (1-4) define a calculation engine in the broad sense.

Calculation engine based on table functions

A calculation engine based on table functions and parameters can be defined as follows:

  • It must be able to fetch input data from a database, make some calculations on them, and write the results back to a database, with acceptable performance.
  • It must be fully configurable by the user. Configuration can be done either by setting parameters, or by combining (i.e. chaining) deterministic functions (i.e. same outputs for same inputs) with well-defined input and output parameters. Ideally, an average user with some analytical flair should be able to configure the calculation engine.
  • It must have a well-documented library of table, matrix and vector functions covering primary calculation patterns frequently used in applications like financial planning, price and commission calculations.
  • It must significantly reduce implementation time and complexity compared to classical database programming for the same tasks.
  • It must work with any database with any data structure.

Aside from reader and writer functions that read/write data from/into a database, a calculation engine has no connection at all with a database during the calculations. All calculations are done with arrays in RAM that represent tables, matrices or vectors.

That is, a calculation engine works totally independent of the data structure of a database. This makes it possible to integrate a real calculation engine with any database with any data structure. The only interface or link, that need to be adjusted to a database are the reader and writer functions, and this can be done quite easily.

In order to better understand this advantage, you can think of some stored procedures in a database, whose parameters and functionality depend on the data structure of its database like table definitions and fields. You can’t detach such stored procedures and install them on another database with a different data structure.

Calculation engine with scalar and table parameters

In its most general meaning, a calculation engine is used for computing chain of functions (or formulas) considering the dependencies among parameters. For example, consider the formulas below:
1) c = f1(a, b)
2) e = f2(c, d)

Dependency tree for calculation engineThe parameter c depends on other parameters a and b. That is, if values of a or b is updated, c needs to be updated as well by executing the function f1.

Similarly, e depends on c and d. Because c depends on a and b, e depends indirectly on a and b as well. These relations can be visualized with a dependency tree as shown at the left.

Excel is -among other things- a typical calculation engine; you can build dependency trees by combining parameters with formulas in excel sheets.

Note that the parameters like a, b, c we are talking about so far are scalar parameters; they represent scalar values like 5 and 120. There are (or there will be, thanks to finaquant) also calculation engines with table functions that take tables as parameters. The main principle is but the same:
1) (T4, T5) = TableFunction1(T1, T2, T3)
2) (T7, T8) = TableFunction2(T2, T4)

The values of tables T7 and T8 depend on tables T2 and T4. Therefore, the tables T7 and T8 need to be updated by executing the function TableFunction2 if there is a change in one of the input parameters T2 or T4.

Network calculations with tables

Configuring network calculations is possible with a calculation engine based on table parameters and table functions. In the exemplary network topology below, there are four primary input and two ultimate output parameters. A table function with tables as input and output parameters sits at each node (or contract) of the network. The output parameters of a node can be input parameters for other nodes.

Such calculation networks can be used in many areas including financial planning, data preparation for reporting, provider-dealer or cause-effect networks, commission calculations and so on.

Calculation networks based on table functions
Written by: Tunç Ali Kütükçüoglu

Copyright secured by Digiprove © 2012-2013 Tunc Ali Kütükcüoglu

One Response to What is a calculation engine based on table functions?

  1. shakirshabbir says:

    A very well written article defining “Calculation Engine” in the simplest idea.
    The reader must have some basic knowledge of how mathematical functions work and also knowledge related to terms like, Stored Procedures; Tables; Database; Data Structure; is also required.

Leave a Reply