Excel Add-in for Table-Valued Functions (Finaquant in Excel)

Excel Add-in for Table-Valued Functions (Finaquant in Excel)

This Excel add-in named Finaquant in Excel that you can install within a minute makes Matrix, Vector and most importantly, Table-Valued Functions of .NET Library Finaquant Cals available for Excel users and programmers.

Table Valued Functions in .NET

Uncommercial: finaquant® protos Commercial: finaquant® calcs
Uncommercial .Net Library with Table Functions
- Non-commercial & free .net library
- Analytical Table Functions with in-memory tables as input and output parameters (like standard formulas in excel)
- Table computations without any sql-based database programming
- A framework for user-defined Table Functions (like user-defined formulas in excel)
- Ability to calculate and store all the table valued parameters of multiple calculation instances in a relational database like MS SQL or MySQL – more info & downloads
Calculation Engine based on Table Functions (.NET)
- All the features and Table Functions of the non-commercial finaquant® protos, plus Calculation Nodes and Networks for enhanced automation, performance and modularity (Calculation Engine)
- Integration of table and matrix computations for applications like scenario analysis, estimations and predictions
- Can be used for applications like Business Analytics & Intelligence, Commission & Performance calculations, Simulations & Optimizations – more info & downloads

Posted on by admin | Leave a comment

A Simple Notation for Data Tables

We use the simple notation introduced here for data tables in order to formulate:

  1. Information and reporting requirements
  2. Analytical operations on data tables

This table notation can generally be used for requirement engineering. I will use the same notation in the upcoming article about Information Content of Data Tables, a concept that I had developed about eight years ago.

Let’s begin with a simple data table with two key figures (numbers) and three attributes:
Notation for Data Tables
Note that, independent of the storage or processing technology (database, excel, XML file) every data table is conceptually a mathematical information unit.

As an example, that you need tables T1, T2, and T3 for a report means, you need the information content (table data + metadata) of all these tables in order to generate this report (by applying some operations on these tables). That is, the information content (IC) of the requested report must be covered by the aggregated information content of these three tables:

IC (T1,T2,T3) ⊇ IC (report) ⇔ report can be generated with f(T1,T2,T3,metadata)

.. where f is any set of operations like filtering, aggregation, combination etc. to be applied on tables.

I will elaborate more about the information content of data tables in an upcoming article. Let’s continue with the table notation. Continue reading

Posted in Calculation engine | Tagged , , | Leave a comment

Calculating Sales Commissions in Excel

In a previous article I explained how sales commissions with tiered rates and product groups can be calculated using the table-valued functions of our .net library Finaquant Calcs.

In this article, I will explain how sales commissions can be calculated in Excel. The commission scenario is the same as explained in the previous article:

Commission Scenario

  • Dealers receive sales commissions proportional to their sales totals for each product group in a quarter or month (commission or payment period).
  • Product groups (or pools) can be defined by product category or individual products for each dealer.
  • The commission scale with tiered rates (class or level scale logic) for each product group (or pool) determines the amount of commissions to be paid at the end of each commission period.

Steps for Commission Calculation

Following 2.5-minute video shows all the steps explained below:

1) Download and install our Excel Add-in for Table Valued Functions. See Finaquant in Excel for downloads and installation

2) Run Create Test Tables to generate all the four input tables required for commission calculation:
Generate input tables for commission calculation in Excel Continue reading

Posted in Calculation engine | Tagged , , , , | Leave a comment

How to Add a User-Defined Table Function to Excel

Finaquant’s Excel add-in (Finaquant in Excel) comes with a list of table-valued functions like Table Arithmetics, Combine Tables, Filter Table, Aggregate Table, Distribute Table, and so on.

You can however extend this list with your own user-defined table functions. Following steps will show how you can do this by creating a simple and practical table function:
Get Price Table
Table Function: Get Price Table

This table function obtains a price table as output from the given input tables for product costs and margins. Note that with table function we mean a function whose input and output parameters are data tables.

When you add a table function to Excel as explained in the steps below, the new function will appear as a new item in the Command Menu named Table Functions:

Command Menu Item

And this is how the table calculation is performed in our example Get Price Table:

Table Calculation for Get Price Table

In our example here (Get Price Table), all the input and output tables (ListObjects in Excel) are read from or written to Excel. The more general schema can be depicted as follows:

General Schema for Table Calculation in Excel

As shown above, a user-defined table function could be written such that some data tables could be fetched from (or stored into) databases like MS SQL or MySQL.

For example, in a rule-based calculation, all the input tables that define the rules of the calculation (like contracts) can be kept in Excel sheets like a cockpit (as User Interface), and the bulk of the input/output data required for the calculation could be stored in an external database.

Let’s return back to our example. These are the steps required for adding the user-defined table function Get Price Table to Excel (for .NET developers): Continue reading

Posted in Calculation engine | Tagged , , | Leave a comment

How to Apply a User-Defined Function on Rows of a Table in Excel

Though simple in use, Transform Rows with UDF (User-Defined Function) is one of the most powerful and versatile table functions. With this function you can manipulate every field of a table row as function of all other fields.

A user-defined function (valid C# code) is applied on every row of a table (Row Transformer). User-defined function can contain anything including if statements and other structures, provided that it is a valid C# code. Hence, a user-defined function is more than a user-defined formula.

In order to try the following examples yourself you need to download and install Excel add-in for table-valued functions (Finaquant in Excel).

As an example assume you have cost and margin tables as shown below. You want to obtain a new price table with costs, margins and prices, where:
price = costs x (1 + margin) Continue reading

Copyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu
Posted in Calculation engine | Tagged , | Leave a comment