finaquant® calcs – Calculation Engine with Table Functions

Important update on 16. February 2019: Some links, and especially download links on this website may not work properly as I recently transferred the whole site from www.finaquant.com to software.tuncalik.com

Check my shared folder MyAnalyticalSoftware_AllDownloads for most uptodate downloads. Open-Source downloads of the C#/.NET libraries Finaquant Calcs and Finaquant Protos are also included in this shared download folder as zipped Visual Studio 2012 files. You may contact me if you need consulting for my analytical software (C#/.NET, Matlab, Python, R).

1 hour with Table Functions may replace 100 hours of SQL-Programming

Responsibility of use:
Finaquant Analytics provides no warranties for finaquant® calcs, and accepts no liability for direct or consequential damages caused by the use of this software product.[/expand]* Before buying a license please make sure that you read, understand and agree with the license conditions above.
* See installation for requesting and validating a permanent license key.
* Owner of a permanent license can install and use all future releases of the library.
* Don’t hesitate to contact us for any inquiries, special licenses (product, service etc.), training and project support.

All table operations and calculations with in-memory data, just like matrices!

Table Functions of finaquant® calcs can be compared to well-designed stored procedures of database engines like Oracle and MySQL, with the difference that table functions operate on in-memory tables.

Half of Business Intelligence
Business Intelligence is all about (1) Table Valued Functions, and (2) Data Visualization.

Hence, combined with a reporting library like Crystal Reports table function library finaquant® calcs offers a versatile but affordable solution for Business Intelligence.

NEW: Table Functions in Excel
Install an excel add-in and start testing table functions within a minute. More info

A .NET Calculation Engine based on Table Valued Functions

• Analytical table-valued functions with in-memory tables as input and output parameters (like standard formulas in excel)
• Standard methods can be extended with user-defined table functions (like user-defined formulas in excel).
• Table Functions can replace database programming for analytical operations on data tables.
• Table Functions can be compared to Matrix Functions of math software like Matlab or R, with the difference that their input & output parameters are data tables instead of matrices.
• Library includes methods to store multiple arrays of data tables in a relational database like MS SQL or MySQL. Tables can also be stored in XML files.
• Calculation Nodes & Networks can be used to implement modular, scalable and easily extendable Calculation Engines for complex rule-based computations.
• Table Functions can be integrated with other libraries (incl. math software like matlab and R) for matrix & vector operations (see related articles and forum posts).

Microsoft Visual Studio project with Table Function Examples
Download Visual Studio project FinaquantCalcsStarter which is the best training tool for Getting Started with Table Functions. There is a Demo Function for every group of Table Functions, including an example for Sales Commissions with a Calculation Network.

NEW Easy installation with standard NuGeT Package
finaquant® calcs can easily be installed directly from a Visual Studio project as a standard NuGeT package. Steps for installation are:

1. Open and Save a new Visual Studio project.
2. From the Tools menu, select Library Package Manager, then click Package Manager Console.
3. Enter following command in Package Manager Console:
   

That’s it; the latest release of finaquant® calcs will be downloaded and referenced to your Visual Studio project automatically.

NEW Quick Start with Table Functions of finaquant® calcs

1. Open and Save a new Visual Studio project (console application in C#) named QuickStartCalcs
2. Install finaquant® calcs with its NuGet Package using the Package Manager Console (required steps are explained above)
3. Coply and Paste following example C# code into the program file Program.cs, and Run

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FinaquantCalcs;
 
namespace QuickStartCalcs
{
    class Program
    {
        static void Main(string[] args)
        {
            #region "Create data tables"
            //*******************************************************
            // First, create some data tables of type MatrixTable
            //*******************************************************
 
            // define meta data (all table fields)
            MetaData md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
 
            // define fields of CostTable
            var CostTableFields = TableFields.CreateEmptyTableFields(md);
            TableFields.AddNewField(CostTableFields, "category");
            TableFields.AddNewField(CostTableFields, "brand");
            TableFields.AddNewField(CostTableFields, "product");
            TableFields.AddNewField(CostTableFields, "costs");
 
            // create CostTable with elements
            MatrixTable CostTable = MatrixTable.CreateTableWithElements_A(CostTableFields,
                "Notebook", "Ignor", "Ignor UX Notebook", 850.0,
                "Notebook", "Ignor", "Ignor AX Notebook", 950.0,
                "Notebook", "Euphor", "Euphor 5 Notebook", 1200.0,
                "Notebook", "Euphor", "Euphor 10 Notebook", 1450.0,
                "Desktop", "Ignor", "Ignor 4D Desktop", 650.0,
                "Desktop", "Ignor", "Ignor 6D Desktop", 800.0,
                "Desktop", "Euphor", "Euphor 2E Desktop", 1050.0,
                "Desktop", "Euphor", "Euphor 5E Desktop", 1300.0
                );
 
            // define fields of MarginTable
            var MarginTableFields = TableFields.CreateEmptyTableFields(md);
            TableFields.AddNewField(MarginTableFields, "category");
            TableFields.AddNewField(MarginTableFields, "margin");
 
            // create MarginTable with elements
            MatrixTable MarginTable = MatrixTable.CreateTableWithElements_A(MarginTableFields,
                "Notebook", 0.40,
                "Desktop", 0.30
                );
 
            // View tables
            MatrixTable.View_MatrixTable(CostTable, "CostTable");
            MatrixTable.View_MatrixTable(MarginTable, "MarginTable1");
 
            #endregion "Create data tables"
 
            // EXAMPLE 1: Table-Scalar Multiplication
 
            // Multiply all key figures (numbers) of CostTable by a scalar number
            MatrixTable CostTable_fractions = CostTable * 1.3333;
            // view table with fractional numbers
            MatrixTable.View_MatrixTable(CostTable_fractions, "CostTable after multiplication by 1.3333");
 
            // EXAMPLE 2: Rounding all key figures of a table
 
            // round numbers to 2 digits after decimal point
            MatrixTable CostTable_rounded = MatrixTable.Round(CostTable_fractions, 2);
            // view table with rounded numbers
            MatrixTable.View_MatrixTable(CostTable_rounded, "CostTable after rounding");
 
            // EXAMPLE 3: Obtain PriceTable by Table Multiplication
 
            // MarginTable specifies margins per category
            MatrixTable PriceTable = MatrixTable.MultiplySelectedKeyFigures(CostTable, (MarginTable + 1),
                InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
            // view PriceTable
            MatrixTable.View_MatrixTable(PriceTable, "PriceTable: Prices calculated with margins per category");
 
            // show license information
            HelpCalcs.ShowLicenseInformation();
        }
    }
}

NEW Run demo queries in LINQPad
You can install the popular development tool LINQPad and begin to test demo scripts with finaquant’s table-valued functions (in C# or VB.NET) within minutes. See required steps..

NEW Building a Web Service with table-valued parameters
You can easily build a web service based on the Web API framework to utilize the power of table-valued functions. Downloads and explanations here..

Primary classes of the .NET library finaquant® calcs

Note: You may download Class Library Document for Finaquant Calcs (1553 downloads) for a complete list of classes, methods and properties.

Table Functions in finaquant® calcs

• Getting Started with Table Functions (a short tutorial)
• Defining fields and hierarchies centrally in MetaData (data universe)
• Converting a MatrixTable to a DataTable, and vice versa
• Creating tables manually
• Creating test tables by combinations
• Importing/Exporting tables to/from Excel and XML files
• Table element, row and column operations
• Table arithmetics (addition, subtraction, multiplication, division)
• Table aggregation functions
• Combining (joining) tables
• Table transformation functions (incl. row-by-row processing with user-defined formula)
• Filtering and sorting tables
• Filtering and sampling tables by dates
• Subtable transformers¹ and function routers²
• Persistent Table Arrays for storing and reading multiple arrays of in-memory tables to/from a relational database like MS SQL or MySQL

Video Introductions

5-Minute Introduction to Finaquant Calcs

Table Function Demo with Excel

finaquant® calcs CAN be used for following purposes:

• To implement analytical relationships (function trees) quickly and efficiently without tedious database programming (SQL, stored procedures etc.).
• Once the analytical relationship (function tree) is implemented, all sorts of quantitative analysis can be carried out by programmatically varying the input values to generate some graphical reports for decision support, like forecasts and estimations, scenario analysis, simulations and optimizations.

You can connect math software like Matlab, R or ILNumerics with the Calculation Engine implemented with finaquant® calcs for simulations, reporting and data visualization.

Relevant Applications

• Machine Learning, Artificial Intelligence
• Statistical Estimations/Predictions/Simulations/Optimizations in Business & Science
• Business Intelligence & Analytics (incl. Financial Planning, OLAP)
• Rule-based Commission/Fee/Performance/Bonus calculations
• Analytical Data Processing for Reporting
• Web or Data Services with tables as input/output data

… wherever analytical operations on historical data tables are involved.

finaquant® calcs CANNOT be used for following purposes:

• Daily business transactions like data registration or updates, and queries; this is a software for analytical computations only.
• Data Visualization: Presenting sections and views of existing data with well formatted tables and graphs.
• Data Mining: Automated detection of analytical relationships, classifications, correlation analysis..

Why you might need finaquant® calcs

• You need to implement some analytical computations that are too complex for excel sheets.
• You find no other choice to implement these computations other than time-consuming database programming.
• Existing implementations (with any business application, or database programming) may not be not atomic, independent or flexible enough for scenario analysis and simulation.
• To automate whole calculation processes including tasks like reading/writing data tables from/to databases, data validation, custom events functions triggered at configured stages.

Application Scope
finaquant® calcs can be used for both operational and analytical purposes.

Examples for operations:
Calculation of Dealer or Sales Commissions, Performance Fees etc. to ultimately produce some invoices.

Examples for decision support:
Simulations for prediction models or optimizations, like estimated sales or expected commission amounts.

Tables In, Tables Out
Solving analytical problems needs a holistic approach ignoring artificial business classifications like marketing analytics, risk analytics, commission or performance analytics, and so on.

In all these analytical applications you have some input tables, and you apply some analytical operations on these input tables to generate some meaningful output tables.

Why Table Functions?

• To save much time and money for analytical database and table operations; you don’t need to invest in expensive software and consultants for Business Intelligence which is essentially Table Analytics.
• A single general-purpose table function of finaquant® calcs can save you many weeks of sql-based database programming.
• To convert database programming into table mathematics; focus on analytical essentials. If you can use excel and write macros you can learn table functions within weeks.
• Audio: Why Table Functions?

Calculation Nodes & Networks

Briefly, this is what a Calculation Node does:

1. Reads all input tables from databases or from the output of a another node.
2. Validates all input tables.
3. Executes the assigned table function to generate output tables.
4. Validates all output tables.
5. Writes output tables to the configured data stores.

More Information

• Finquant’s Community Forum
• How to call table functions directly from an excel sheet
• To remain uptodate follow us at twitter, facebook or google+
• Don’t hesitate to contact us for any questions or inquiries.
• See help page for technical user information about Table Functions and Calculation Nodes & Networks.
• Open-Source DataTable Extensions for .NET developers

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

1. A subtable transformer applies the same table (or matrix) function on every subtable of an input table. [↩]
2. A function router applies selected table (or matrix) functions on selected subtables of an input table. [↩]