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

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 will 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.

This free Excel add-in (Finaquant in Excel) will be available for download within a couple of months. Excel users can however, already install the beta (draft) release here, and get an idea of what’s coming up with the examples below I selected for getting started.

Installation

Download Description
FinaquantInExcel_beta103.xll Excel add-in Finaquant in Excel, beta release R103, incl. matrix functions (digitally signed by Finaquant Analytics GmbH)
IntroToFinaquantInExcel.pdf Introduction to the beta release of Finaquant in Excel (add-in); a Visual Guide with table function examples and developer notes.
FinaquantInExcelDemo.xls Demo Excel file with a worksheet named Parameters for Getting Started
FinaquantInExcel_beta103.zip Zip package with Excel add-in (xll file), excel file with a sheet named Parameters for Getting Started, and Visual Studio project 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

Copyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu
Posted in Calculation engine | Tagged , | 4 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

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

Building a Web Service that Receives and Returns Data Tables

As you will see below, it is quite easy to build a web service with the Web API Framework (ASP.NET), which:

  1. receives data tables from a web client as input parameters,
  2. makes some analytical operations on these input tables using table-valued functions,
  3. returns some resultant (output) tables to web client as output parameters.

web service that receive and return data tables

Download Description
WebService_MatrixTable MS Visual Studio 2012 project Web Service (.NET 4.0)
WebClient_MatrixTable MS Visual Studio 2012 project Web Client (.NET 4.0)

Application Examples

As you can imagine, you could implement a variety of data processing services with such a web service which is capable of receiving and returning data tables (of type MatrixTable). Here are some examples: Continue reading

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

Optimizing Marketing Investment

Table Functions of our .NET libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial) can be used to formulate evaluation function (also known as objective or fitness function) and constraints in any optimization scenario quite easily without cumbersome database programming; following model example shows how.

The output of an evaluation function can be measures like profit, time or costs, depending on the figure you want to minimize or maximize. In the following example, we want to get optimal marketing investment amounts per brand (value drivers) in order to maximize total profit from sales.

We will use Nelder-Mead Solver from Microsoft Solver Foundation (free .NET library) to find optimal investment amounts in this example.

You may download the Visual Studio project file with all the C# code required to run this example here.

Business Scenario

An online digital products dealer wants to increase its sales through advertisements on internet. Each ad will promote a certain brand (i.e. ads at brand level). The increase in sales for each product depends on (1) investment amount, and (2) sensitivity α of the product.
Marketing Investment as Value Driver Continue reading

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