finaquant.com - Calculation Engines and Frameworks - Finaquant Analytics

Testing Persistent Table Array with a Microsoft SQL database

Posted on April 9, 2013 by admin

With the PersistentTableArray class of the non-commercial .net library finaquant® protos you can read and write in-memory tables from/to a relational database together with their instance information.

If you have already installed Microsoft Visual Studio C# 2010 Express or a higher version on your computer it is quite easy to get started with persistent table arrays. The installation file of Visual Studio is packed with a free database SQL Server Express you don’t need to install a database software separately.

You will need Visual Studio in any case to run the demo function for persistent table arrays in the Visual Studio project file FinaquantProtosStarter which 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.SqlClient;
 
// initiate DataStore for database connection
DataStore datstore = new DataStore(Provider: "System.Data.SqlClient");
 
// open database connection
string ConnStr = “@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Tuncali\Documents\finaquant.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
datstore.OpenConnection(ConnStr);
 
// initiate a persistent table array
var PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);

Continue reading →

Next step: Worker ant ika (calculation node)

Posted on March 1, 2013 by tuncalik

Which societies were the first farmers? When and where did they emerge?

Early civilizations in the Fertile Crescent of Middle East or in China about 9000 BC? Or maybe ancient people in Solomon Islands or in the Highlands of New Guinea?

No, social insects like ants and termites were the first farmers on land; leaf-cutter ants began cultivating a fungus species about 50 million years ago!

Video: Coevolution of the ant and fungi

Watch also: Farming Ants Reveal Evolution Secrets

How about the domestication of animals? Which societies were the first breeders and shepherds? Continue reading →

Posted in Finaquant news | Leave a comment

Table data as input to estimation functions in R

Posted on February 19, 2013 by tuncalik

As mentioned earlier in related articles, functions written in other languages like R, matlab or ILNumerics can be integrated with table functions using the high-level constructs like function router¹ and subtable transformer².

Following example scenarios demonstrate how different estimation functions written with the popular free and open-source language R can be applied on selected parts (i.e. subtables) of an input table containing some historical data for price estimations.

You can find all the relevant code in R and C# at the download page, including the C# code for generating test data.

Price estimators in R

For the example scenarios below we have three estimation functions in R, all based on Linear Regression (LR) of the global indicators market index and oil price for delivering price predictions.

Price predictor based on Linear Regression (LR)

PriceEstimatorA: Estimated product price based on estimated market index and oil price
PriceEstimatorB: Estimated product price based on estimated market index only
PriceEstimatorC: Estimated product price based on estimated oil price only

R code for estimation functions A, B, C

# help function for displaying variables
display_variable = function(v, vname) { 
print(paste(vname,' ='),quote = FALSE)
print(v,quote = FALSE)
}
 
# Price Estimation Function A
# Estimation with 1st order Linear Regression
# based on historical market index and oil price
#
# Columns of input matrix HistoricalData:
# market index, oil price, product price
#
# MarketIndex: Estimated market index for the next year
# OilPrice: Estimated oil price for the next year
EstimatedProductPriceA = function(HistoricalData, MarketIndex, OilPrice) { 
# historical market index and oil price
RowCount = nrow(HistoricalData)
X_train = rbind(matrix(1,1,RowCount), t(HistoricalData[,1:2]))
 
# hostorical product prices
Y_train = matrix(HistoricalData[,3], RowCount, 1);
 
# optimal coefficient vector to minimize MSE
Bopt = solve(X_train %*% t(X_train)) %*% X_train %*% Y_train
 
# get estimated product price
X_test = matrix(c(1.0, MarketIndex, OilPrice), nrow=1)
Y_test = X_test %*% Bopt
return (Y_test)
}
 
# Price Estimator B
# Estimation with 1st order Linear Regression
# based on historical market index only
#
# Columns of input matrix HistoricalData:
# market index, product price
#
# MarketIndex: Estimated market index for the next year
EstimatedProductPriceB = function(HistoricalData, MarketIndex) { 
# historical market index and oil price
RowCount = nrow(HistoricalData)
X_train = rbind(matrix(1,1,RowCount), t(HistoricalData[,1]))
 
# hostorical product prices
Y_train = matrix(HistoricalData[,2], RowCount, 1);
 
# optimal coefficient vector to minimize MSE
Bopt = solve(X_train %*% t(X_train)) %*% X_train %*% Y_train
 
# get estimated product price
X_test = matrix(c(1.0, MarketIndex), nrow=1)
Y_test = X_test %*% Bopt
return (Y_test)
}
 
# Price Estimator C
# Estimation with 1st order Linear Regression
# based on historical oil price only
#
# Columns of matrix HistoricalData:
# oil price, product price
#
# OilPrice: Estimated oil price for the next year
EstimatedProductPriceC = function(HistoricalData, OilPrice) { 
# historical market index and oil price
RowCount = nrow(HistoricalData)
X_train = rbind(matrix(1,1,RowCount), t(HistoricalData[,1]))
 
# hostorical product prices
Y_train = matrix(HistoricalData[,2], RowCount, 1);
 
# optimal coefficient vector to minimize MSE
Bopt = solve(X_train %*% t(X_train)) %*% X_train %*% Y_train
 
# get estimated product price
X_test = matrix(c(1.0, OilPrice), nrow=1)
Y_test = X_test %*% Bopt
return (Y_test)
}
 
HistoricalData = matrix(c(1.48, 94.38, 2.89, 0.91, 91.68, 1.83, 1.27, 85.61, 2.74), nrow=3, byrow=TRUE)
print('HistoricalData')
print(HistoricalData)
 
MarketIndex = 1.2
OilPrice = 100
 
# method A
EstPrdPrice = EstimatedProductPriceA(HistoricalData, MarketIndex, OilPrice);
display_variable(EstPrdPrice,'Estimated product price with estimator A')
 
# method B
EstPrdPrice = EstimatedProductPriceB(HistoricalData[,c(1,3)], MarketIndex);
display_variable(EstPrdPrice,'Estimated product price with estimator B')
 
# method C
EstPrdPrice = EstimatedProductPriceC(HistoricalData[,c(2,3)], OilPrice);
display_variable(EstPrdPrice,'Estimated product price with estimator C')

Note that the details of the estimator functions (and whether they make sense in reality) are unimportant for the scenarios below. We will focus on the big picture and integration: How can we apply selected price predictors in R on selected subtables of an input table? Continue reading →

A function router applies selected table (or matrix) functions on selected subtables of an input table. [↩]
A subtable transformer applies the same table (or matrix) function on every subtable of an input table. [↩]

Posted in Calculation engine | Tagged integration, predictive model, R | Leave a comment

Calling user-defined functions in R from within c#

Posted on February 17, 2013 by admin

In order to integrate R with high-level table constructs like function router¹ or subtable transformer² it might be desirable to call user-defined functions in R from within c#.

This can be done quite easily with the help of the open-source codeplex library R.NET; following steps show how.

You may also want to see the related forum topic:
How can a function router call matrix functions written in R? Continue reading →

A function router applies selected table (or matrix) functions on selected subtables of an input table. [↩]
A subtable transformer applies the same table (or matrix) function on every subtable of an input table. [↩]