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

Continue reading →

Posted in Calculation engine | Tagged , | Leave a comment

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 →

Posted in Calculation engine | Tagged , | Leave a comment

Next step: Worker ant ika (calculation node)

Posted on March 1, 2013 by tuncalik

worker ant ika - designer: Jeff Cameron CollingwoodWhich 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 →

Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
Posted in Finaquant news | 2 Comments

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 router1 and subtable transformer2.

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)

  1. PriceEstimatorA: Estimated product price based on estimated market index and oil price
  2. PriceEstimatorB: Estimated product price based on estimated market index only
  3. 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 →

Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
  1. A function router applies selected table (or matrix) functions on selected subtables of an input table. [↩]
  2. A subtable transformer applies the same table (or matrix) function on every subtable of an input table. [↩]
Posted in Calculation engine | Tagged , , | Leave a comment