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

Installing Excel Add-in: 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.

In order to execute a table function in Excel, first select the single-column range with parameters (see sheet named Parameters in the excel file named FinaquantInExcelDemo), then press OK as shown below:

Testing Table Functions in Excel
All the table functions included in this beta release are explained in the downloadable user guide (pdf file). There are also useful notes for VBA and .NET developers in this visual guide.

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 functions for your applications, and make them available in Excel.

I think, the transition from VBA to a .NET language like VB.NET should not be too difficult for an excel programmer.

Any questions? Need training or project support?

You may direct your questions and comments to Finaquant Community Forum. If nobody answers, I (Tunc) personally try to answer every question within a week or so.

Otherwise:

  • Do you need training for using the table functions of finaquant?
  • Do you need to develop new Table Functions or Calculation Engines for your business applications? (incl. operational calculations like commissions, performance, bonus.. or analytical applications like forecast, estimation, simulation, optimization..)

Please don’t hesitate to contact us.

Written by: Tunc A. Kütükcüoglu

Digiprove sealCopyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu
This entry was posted in Calculation engine and tagged , . Bookmark the permalink.

5 Responses to Empowering Excel with Table Valued Functions

  1. Avatar photo tuncalik says:

    Tutorial: Adding a new table function to Excel add-in, step by step
    http://forum.finaquant.com/viewtopic.php?f=4&t=1281

  2. Avatar photo tuncalik says:

    New beta release (R102) includes Matrix Functions in addition to the primary feature of the excel add-in (Table Valued Functions). More information about Matrix Functions with developer notes can be found at:
    http://forum.finaquant.com/viewtopic.php?f=9&t=1282

  3. Avatar photo admin says:

    New beta release (R103) of Excel add-in for Table and Matrix functions comes with more refined and practical parameter forms:
    https://www.facebook.com/finaquant/posts/707716322632587

  4. ugurtanriverdi says:

    Hi Tunc

    As far as i have understood, with this xls add-in, we will have a set of mathematical, financial, statistics etc… functions, which will take as input and output parameter, excel tables.

    This looks like a nice enhancement to excel formulas, which only support few build in functions with xls tables input, like vlookup, hlookup and which doesn’t support at all, functions with table output.

    But, if i understood correctly, from your first demo version, there is no way to see which table has been created by which table function (functionality brought by xls formula bar, for xls cells) and it is not possible to refresh output table data manually or automatically, if one of its source tables, has been updated (for analogy; excel, does this automatically, for cells with formula).

    I believe that these 2 features will be exteremely useful for usability of “excel functions with tables parameters” add-in.

    My question is simple; Do you have any plan, about adding these features or some similar functionality, to productive version of your add-in?

    thanks & regards

  5. Avatar photo admin says:

    New release B104 of excel add-in (Finaquant in Excel) is available for download.

    Information about the 10 new table function added in this release:
    https://plus.google.com/b/101460024578785928760/101460024578785928760/posts/H89tFCoZb1i

Leave a Reply