finaquant.com - Calculation Engines and Frameworks

Uncommercial: finaquant® protos (available now)	Commercial: finaquant® calcs (upcoming: June 2013)

Uncommercial calculation engine based on table functions - Non-commercial & free .net library - Table calculations without any sql-based database programming - Analytical table functions with in-memory tables as input and output parameters (like standard formulas in excel) - 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	Commercial calculation engine based on table functions - All the features and functions of the non-commercial finaquant® protos, plus Calculation Nodes and Networks for enhanced calculation automation, performance and modularity - Integration of table and matrix computations for applications like scenario analysis and estimations based on historical data - Can be used for applications like business analytics and intelligence, financial planning, fee and commission calculations, simulations and optimizations with table data, reporting – more info

Posted on February 15, 2013 by admin | Leave a comment

Importing and Exporting a DataTable from/to an Excel file

Posted on May 2, 2013 by tuncalik

As explained in the related article, a MatrixTable of finaquant can easily be converted to or from a DataTable of the .net framework (ADO.net).

Data integration of excel with .net framework

That is, importing or exporting a MatrixTable from/to an excel sheet can be accomplished in two steps:

Import: (1) From Excel to DataTable (2) From DataTable to MatrixTable

Export: (1) From MatrixTable to DataTable (2) From DataTable to Excel

Data integration of .net with excel

Tables and values can be exchanged between .net and excel by using the methods in C# listed below.

The requirements for debugging and running these methods:

1) Microsoft Office and Excel must be installed
2) Following excel-related COM assemblies must be referenced:
Microsoft Office XX.0 Object Library
Microsoft Excel XX.0 Object Library
3) Following namespaces must be included in reference list in the code:
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

Feel free to use the code wherever you want provided that you leave any copyright information for Finaquant Analytics intact. We would appreciate any comment for possible corrections and improvements in the code. Continue reading →

Posted in Calculation engine | Tagged DataTable, excel, integration | 4 Comments

Converting a MatrixTable to a DataTable, and vice versa

Posted on May 1, 2013 by admin

MatrixTable is a type (class) for in-memory representation of data tables just like DataTable of the .net framework (ADO.net). MatrixTable has but a much simplified data structure compared to DataTable with primary focus on mathematical (or analytical) table functions rather than typical sql-based data operations like inserts and updates.

DataTable to or from MatrixTable

With the following methods of the MatrixTable class, a DataTable can easily be converted into a MatrixTable, and vice versa:

// From DataTable to MatrixTable
public static MatrixTable Import_from_DataTable(DataTable dtable, MetaData md,
   string TextReplaceNull = "NULL", int NumReplaceNull = 0, double KeyFigReplaceNull = 0)
 
// From MatrixTable to DataTable
public static DataTable Export_To_DataTable(MatrixTable tbl)

Continue reading →

Posted in Calculation engine | Tagged DataTable, integration, matrixtable | Leave a comment

Persistent Table Array

Posted on April 9, 2013 by tuncalik

PersistentTableArray and DataStore are new classes of the non-commercial .net library finaquant® protos (starting from release 1.04) that enable connection to relational databases like MySQL and Microsoft SQL in order to read/write complete tables from/to a database together with their instance information.

Each table instance is an element of a persistent table array. Following C# code example illustrates how separate cost tables for each country and year can be stored in a MySQL database with a persistent table array:

using FinaquantProtos;
using System.Data.Odbc;
 
// Connection string for MySQL database: 
// Replace it with your own valid connection string on your computer
string ConnStr = @"Driver={MySQL ODBC 5.2w Driver};Server=127.0.0.1;Database=testdb;uid=root;pwd=";
 
// Data provider
// You need to install ODBC driver for MySQL on your computer (win 32 bit version)
// see: http://dev.mysql.com/downloads/connector/odbc/
string DataProvider = "System.Data.Odbc";
 
// initiate DataStore object for database connection
DataStore datstore = new DataStore(Provider: DataProvider); 
 
// open database connection
datstore.OpenConnection(ConnStr);
 
// initiate "persistent table array" objects
PersistentTableArray PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);
 
// Create TableRow objects as instance keys for tables
TableRow trow1 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2008);
TableRow trow2 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2012);
TableRow trow3 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Bolivia", 2010);
 
// store each table instance separately into database (yes, just by assigning!)
PTblCost[trow1] = CostTblPeru2008;      // store CostTblPeru2008 with instance information in trow1
PTblCost[trow2] = CostTblPeru2012;
PTblCost[trow3] = CostTblBolivia2010;

Note that CostTblPeru2008, CostTblPeru2012 and CostTblBolivia2010 are different instances of in-memory cost tables (of type MatrixTable) with the same field structure. Continue reading →

Posted in Calculation engine | Tagged .net library, calculation engine, database | Leave a comment

Testing Persistent Table Arrays with a MySQL database

Posted on April 9, 2013 by admin

MySQL is definitely one of the most popular database systems. It is free and open-source, and comparably easy to install and use.

The all-in-one installation package makes the installation quite simple. This package also includes MySQL workbench for tasks like executing SQL statements and database administration with a nice graphical user interface (GUI).

PersistentTableArray class of the non-commercial .net library finaquant® protos can be connected with a relational database like MySQL (or Microsoft SQL) in order to read or write in-memory tables from/to the database together with their instance information.

You may find some interesting articles in internet if you search for MySQL versus SQL Express.

PersistentTableArray and DataStore classes of finaquant® protos that are responsible for the database connection don’t need many widgets and gadgets of a sophisticated commercial database; they are based on most basic standard (ANSI) SQL statements for reading or writing complete tables. So technically, MySQL database is more than enough for persistent table arrays with the advantage that it doesn’t have the storage space limitations of Microsoft SQL Server Express.

You will also need Microsoft Visual Studio (VS) to run the demo function for persistent table arrays included in the VS project file FinaquantProtosStarter that you can download at the product page of finaquant® protos.

Initiating a persistent table array is about setting the correct database connection string and data provider to a DataStore object:

using FinaquantProtos;
using System.Data.Odbc;
 
// initiate DataStore for database connection
DataStore datstore = new DataStore(Provider: "System.Data.Odbc");
// open database connection
string ConnStr = @"Driver={MySQL ODBC 5.2w Driver};Server=localhost;Database=finaquant;uid=root;pwd=MyPassword";
datstore.OpenConnection(ConnStr);
 
// initiate a persistent table array
var PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);