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

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

Empowering Excel with Table Valued Functions

We are currently working on an Excel add-in named Finaquant in Excel that will make all the Matrix, Vector and most importantly, Table Valued Functions of finaquant libraries available for Excel users and programmers (macro, VBA).

Excel Add-In: Finaquant In Excel

Table Valued Functions will transform your Excel into a Business Intelligence Tool, as Business Intelligence is all about:

  1. Table Valued Functions
  2. Data Visualization (reports, pivot tables, charts..)

Note that the so called OLAP functions (slice-and-dice, aggregate, drill-down etc.) are only a small subset of the table functions available in finaquant libraries.

Typical table calculations like Commission, Performance, Bonus, Financial Planning and Forecasting will become a matter of one-week configuration and testing with the table functions in Excel (rather than expensive software projects) provided that your machine can handle the performance and data size requirements.

Video: Installing and Testing Table Functions in Excel
Video: Installing and Testing Table Functions in Excel

We offer all the related code for the Excel integration (based on NetOffice and Excel DNA) as open-source software to .NET developers. Even though we develop this add-in specifically for the integration of finaquant libraries (Finaquant Protos and Calcs) it can also be used for other integration projects as well due to its general features for excel and .NET integration.

The beta (draft) release of this Excel add-in (Finaquant in Excel) is already available for download (see table below). I recommend Excel users to have a look at the visual User Guide with the examples I selected for getting started.

Installation

Download Description
FinaquantInExcel_R105.xll Excel add-in (Finaquant in Excel) for Table-Valued Functions, including basic matrix functions (digitally signed by Finaquant Analytics GmbH)
IntroToFinaquantInExcel.pdf Introduction to Table-Valued Functions in Excel (add-in), release 30 Oct 2014; a Visual Guide with table function examples and developer notes.
FinaquantInExcelDemo.xls Demo Excel file with a worksheet named Parameters for Getting Started
FinaquantInExcel_R105.zip Zip package with the Visual Studio project (C#/.NET) for developing the add-in (open source)

Prerequisites for installing the Excel add-in:

  1. MS .NET Framework 4.0 (normally comes with Windows operation systems for free)
  2. MS Office Excel 2000 or above

Download and unzip the zip package to your document folder:
Content of zip pack Finaquant in Excel

  1. Open the Excel file FinaquantInExcelDemo.xls with the sheet named Parameters
  2. Go to Developer-tab (normally between View and Team), click on Add-Ins button, browse to the add-in file FinaquantInExcel_beta101.xll and press OK

Installing Excel Add-in Finaquant in Excel Continue reading

Digiprove sealCopyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu
Posted in Calculation engine | Tagged , | 5 Comments

Testing Table Valued Functions with LINQPad

LINQPad is a lightweight software development tool for .net developers that can be installed easily within a minute. LINQPad users may find it practical to test table-valued functions of finaquant’s .net libraries Finaquant Protos or Calcs directly in LINQPad, especially for running queries on input and output tables. This is quite easy as the following steps will illustrate.

Table Functions with LINQPad

How to run demo queries with table functions in LINQPad

Download Description
LINQPad_demo_queries Demo queries (scripts in C# and VB.NET) for finaquant’s table-valued functions

1) Download and install LINQPad
How LINQPad is defined by its developer: Querying is merely a special case of using LINQPad. More generally, LINQPad is a C#/VB/F# scratchpad that instantly executes any expression, statement block or program with rich output formatting and a wealth of features that “just work”.

2) Download and install one of the .net libraries with table-valued functions, Finaquant Protos (free) or Finaquant Calcs (commercial).
All the demo queries (i.e scripts) in LINQPad can be run with both libraries, excluding the Commission Calculation example which requires Finaquant Calcs.

These libraries include high-level methods (of class MatrixTable) with table-valued parameters, as often required in analytics or business intelligence. Continue reading

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