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

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 Excel

Add Data & Business Intelligence to Excel

 
Evaluation
Permanent
Price
free
$75
License type
Single User
Single User
Allowed row count for tables
5’000
unlimited
Allowed usage duration
30 days
unlimited
Get a Permanent User License  

* For permanent use, a license is required for the underlying .net library Finaquant Calcs; not for the Excel add-in itself which is free and open source.
* See installation for requesting and validating a permanent license key.
* Owner of a permanent license can install and use all future releases of the .net library Finaquant Calcs.
* Don’t hesitate to contact us for any inquiries, special licenses (product, service etc.), training and project support.

Download Description
FinaquantInExcel_R105.xll Excel add-in (Finaquant in Excel) for Table-Valued Functions, release 105, incl. 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 Visual Studio project (C#/.NET) for developing the add-in Finaquant in Excel (open source)

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

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.

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

Installation

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

Installing Excel Add-in for Table Valued Functions
If you don’t see a Developer-tab in your Excel file you can add it by customizing the ribbon: Right-mouse menu on any Excel tab > Customize Ribbon > Check Developer group on the right side

Or another way for adding the add-in:
2. File > Options > Add-Ins > Manage [Excel Add-ins] GO

After a successful installation you should see a new menu named Table Functions in Menu Commands group in Add-Ins tab.

You will also see new buttons (Create Test Tables, Combine Tables..) in the new ribbon named Finaquant Table Functions. However, some older releases of MS Excel that doesn’t support ribbons may not show these buttons.

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

Troubleshooting
If you can’t install the add-in successfully, you may need to adjust your macro and add-in permissions:
File > Options > Trust Center > Trust Center Settings
Trust Center Settings

Testing Table Functions in Excel

If everything went alright and you could install the add-in successfully, you can now begin to test the table functions in Excel. But first, create some test tables by clicking on the button Create Test Tables.

Your first table function: Select Columns
Select the function from the menu named Table Functions:
Table Function: Select Columns

This simple table function creates a subtable with the selected fields (i.e. columns) of input table:
Select Fields of Data Table

You can determine name of the resultant subtable (i.e. output table), sheet name for output table and the upper-left corner of this output table in the parameter form as seen above.

If the exclude option is checked, selected fields are excluded from the input table.

Your second table function: Round Key Figures
Round selected key figures of input table to given number of digits after decimal point:
Table Function: Round Selected Key Figures

Resultant output table with rounded key figures costs and price:
Round Key Figures, Resultant Table

Your third table function: Table Arithmetics
Table Arithmetics is similar to matrix arithmetics, with the difference that attribute value combinations determine the outcome rather than the order of rows.

The kind of operation (addition, multiplication, subtraction or division) can be selected in the parameter form. In the following example, table multiplication is selected.

Assume you have a cost table, and you want to determine product prices with multiplicative margins as shown below:
Cost and Margin Tables

For this example, I created the table named MultMargin manually. Now we can select parameters and multiply tables:
Table Arithmetics

Following resultant table named PriceTbl will be inserted into an Excel sheet with the same name:
Resultant Price Table

We strongly recommend you to download User Guide for other table function examples, license information and developer notes.

List of Table Functions in the Latest Release

Table Function Description
Create Test Tables Creates several excel tables (ListObjects)
Combine Tables Combine two input tables that have some common attributes (text or numeric).
Add Tables Table Addition: Add two input tables with selected key figures.
Table Arithmetics Add, Multiply, Subtract or Divide two input tables.
Distribute Table Simple pro-rate distribution; returns table with distributed amounts.
Combine Field Values Create a new table with all possible combinations of given field values in an input table.
Filter Table Filter 1. input table (Base Table) with 2. input table (Condition Table).
Combinate Table Rows Cartesian Multiplication of Table Rows: Generate a new table with all possible row combinations of input tables.
Aggregate Table Aggregate all selected key figures of input table with the selected aggregation function. Resultant aggregated table contains selected attributes and key figures.
Transform Rows with UDF Apply user-defined function (valid C# code) on every row of input table (Row Transformer). User-defined function can contain anything including if statements and other programming structures, provided that it is a valid C# code.
Filter Rows with UDF Include or exclude table rows with a user-defined filter function (valid C# code) that returns true or false. Depending on exclude option, a row is included or excluded when user-defined filter function returns true.
Select Columns Return a subtable with selected fields of input table.
Scalar Arithmetic Operation Add, Multiply, Subtract or Divide selected key figures of input table by a scalar number like 2.5
Round Selected Key Figures Round selected key figures of input table to given number of digits after decimal point.
Insert New Field into Table Insert a new field into input table.
Assign Random Numbers Assign uniformly distributed random values to selected key figures and numeric attributes (of date or integer type) of input table, within the given lower and upper limits.
Sort Rows Sort rows of table w.r.t. given fields and sort options (ASC/DESC). Comma-separated field names and sort options are entered like: category ASC, product DESC, ..
Insert Aggregate Key Figure Insert a new (output) key figure which is the aggregate of selected (input) key figure w.r.t. selected reference attributes. For example, KF sales_per_category as aggregate of KF sales w.r.t. attribute category.
Append Tables Vertically Append table2 to table1 vertically. Two input tables must have identical fields for this vertical append.
Append Tables Horizontally Append table2 to table1 horizontally. Two input tables must have distinct fields with identical number of rows.
Simple Date Range Filter Selects (or deselects if exclude option is checked) rows of input table within the given date range.
Date Filter Filters rows of input table based on selected date field and period (month, quarter, year). Includes or excludes days considering date range as well as allowed period and week days.
Date Sampler Samples dates for allowed period (month, quarter, year) and week days with the given search logic and returns a subtable with the source and target days.
Get Price Table (UDF) Get price table with costs, margins and prices. Cost table must contain a key figure named costs, and margin table must contain a key figure named margin. Resultant price table contains all three key figures: costs, margin and price. Example for a User-Defined table function (UDF)
Sales Commissions (UDF) Typical commission calculation with tiered rates and product poools (or groups). Calculates sales commissions per product pool and per dealer, given four input tables: Sales table (transactions), Commission-Scale table (tiered rates), Product-Pool table (defines product groups), Scale-To-Pool table (tiered rates per dealer and product group). Two output tables will be generated: Commissions per Product Pool, and Commissions per Dealer

What can you do with this Excel add-in?

  1. As an Excel user you can use the table functions for various purposes.
  2. As an Excel programmer you can call the matrix, vector and table functions of Finaquant Calcs in VBA to develop some powerful macros.
  3. As an Excel and .NET programmer you can develop new table and matrix valued functions for your applications and distribute them as your customized excel add-in.
  4. As an Excel and .NET programmer you can use the source code of this excel add-in to integrate matrix and table functions of other .NET libraries with Excel.

More Information

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

Leave a Reply