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.
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:
And this is how the table calculation is performed in our example 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:
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