finaquant® protos – free .NET Library 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).

NOTE: Allowed table size is limited (max 20’000 rows) in the published standard release of finaquant® protos. See commercial finaquant® calcs or contact us if you need unlimited table size.

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

Table Functions in .NET

• 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 as XML files.

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

Microsoft Visual Studio Project with Table Function Examples
Download Visual Studio project FinaquantProtosStarter which is the best training tool for Getting Started with Table Functions. You may find in it a demo function for every group of Table Function.

Easy installation with standard NuGeT package
finaquant® protos can easily be installed directly from a Visual Studio project with a NuGeT pack. 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® protos will be downloaded and referenced to your Visual Studio project automatically.

Quick Start with Table Functions of finaquant® protos

1. Open and Save a new Visual Studio project (console application in C#) named QuickStartProtos
2. Install finaquant® protos as 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 FinaquantProtos;
 
namespace QuickStartProtos
{
    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");
        }
    }
}

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

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® protos

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

Table Functions in finaquant® protos

• 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

finaquant® protos can be used for applications like:

• Table Computations and Table Mathematics in .NET
• Analytics: Simulations, Estimations and Optimizations based on historical data
• Function Trees with data tables as input and output parameters
• Cause-effect relationships and networks with table-valued parameters

What is new with finaquant® protos:
Table functions and table mathematics. Some table functions like Allocations & Distributions and FOX formulas are embedded in expensive business software like SAP that are used for accounting, financial planning and reporting. These are but only a subset of the table functions that are available in finaquant® protos.
Read: Why finaquant® protos? Listen: Why Table Functions?

More Information

• Finquant’s Community Forum
• How to call table functions directly from an excel sheet
• Follow us at twitter, facebook or google+ to remain uptodate
• 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

1. Matrix and vector functions come as a byproduct. See User Manual for the list of available table, matrix and vector functions. [↩]
2. For commercial use see finaquant® calcs, Calculation Engine based on Table Functions. [↩]
3. A subtable transformer applies the same table (or matrix) function on every subtable of an input table. [↩]
4. A function router applies selected table (or matrix) functions on selected subtables of an input table. [↩]