Expected Shortfall Portfolio Optimization in R using nloptr

I have previously done examples of QP optimization in for financial portfolios.  I am not a huge fan of variance optimization in finance.  Return distributions are not normal, are often skewed, and are usually leptokurtic.  In plain speak, the distributions have fat tails and while the mean may be 0, the median is shifted to one side or the other.

Because of the shape of return distributions, variance optimization fails to capture the true risk of a portfolio. The risk measure I prefer to use is Expected Shortfall.  Expected Shortfall is defined as the mean of the left tail of the distribution, below some given alpha.

Beyond VaR:

We call the value at the apha Value-at-Risk (VaR).  VaR has been much maligned over the last few years -- not always for the right reasons.  Some people confuse all VaR measures as assuming a normal distribution. Not so.  The definition is that is it some left tail alpha value of the expected P/L distribution.  No where does it assume a normal distribution -- people just wrongly use a normal distribution when calculating VaR.

Jorion's definition of VaR fromValue at Risk: The New Benchmark for Managing Financial Risk, 3rd Edition says (I'm paraphrasing) VaR is the maximum expected loss at a given level of certainty during normal market conditions.  That often is misinterpreted by management as THE number of the maximum loss.  Jorion's definition has a positive spin that pushes the misinterpretation.

I prefer to but a negative spin on it.  VaR is the minimum expected loss when you have a bad day.  If I have a day that is, at least, in the alpha percential of crappy-ness, then I will lose MORE than VaR.

VaR is the floor, not the ceiling.  That's where it has been misused.

So why not optimize on VaR?  Short answer is that VaR is highly nonlinear.  Add nonlinear instruments to your portfolio and you can no longer guarantee the concavity of the objective function.

Enter ES:
Expected Shortfall (ES) is the average of the values above the VaR value.  That is, when I'm having a really bad day, what is my expected loss.  If I say a 1 in 20 day is bad (alpha=.05) then I should have 12-13 bad days a year.  If my portfolio was static and the return distributions unchanging (not likely), then the average of those 12-13 days should center on ES.

ES is still not perfect because you don't know how far to the left you can go.  But it does factor in the fat, skewed tail that we see.  It is a good starting point for risk analysis.

Because ES is a mean, it is guaranteed to be a concave function (I'm still looking for the reference -- I've seen it before and will update the post if I find it).  So ES is a better measure of risk than VaR and we can trust optimization results from it.  So let's build an optimization routine for ES.

nloptr:
nloptr is a nonlinear optimization library in R wrapping the GNU NLopt library.  ES, while well behaved, is nonlinear.  nloptr provides a number of nonlinear solvers.  It's what we want.

For our test case, we will simulate a 4 variable normal distribution with 10,000 draws (correlation given below).  We will build the ES function and a gradient function for ES.  We will constrain portfolio weights to be between [0,1].  We will impose an equality constraint that the sum of the weights = 1.

require(MASS)

require(nloptr)

#Covariance structure for the simulation

#give everything a std=.1

n = 4

corr = c(1, .7,  .5,  .1,

              .7,  1,  .4, -.1,

              .5, .4,   1,  .6,

              .1, -.1, .6,  1)

corr = matrix(corr,n,n)

std = matrix(0,n,n)

for(i in 1:n){      

       std[i,i] = .1

}

cov = std %*% corr %*% std

#Simulate 10,000 draws

sim = mvrnorm(n=10000,rep(0,n),cov)

#feasible starting values of equal weights

w = rep(1/n,n)

#ES function.  Mean of values above alpha

es = function(w,sim=NA,alpha=.05){

       ret = sort(sim %*% w)

      

       n = length(ret)

       i = alpha * n

      

       es = mean(ret[1:i])

      

       return(-es)  

}

#linear equality constraint

#note: nloptr requires all functions to have the same signature

eval_g0 <- function(w,sim=NA,alpha=NA) {

       return( sum(w) - 1 )

}

#numerical approximation of the gradient

des = function(w,sim=NA,alpha=.05){

       n = length(w)

       out = w;

       for (i in 0:n){

              up = w;

              dn = w;

              up[i] = up[i]+.0001

              dn[i] = dn[i]-.0001

              out[i] = (es(up,sim=sim,alpha=alpha) - es(dn,sim=sim,alpha=alpha))/.0002

       }

       return(out)

}

#use nloptr to check out gradient

check.derivatives(w,es,des,sim=sim, alpha=.05)

#function to optimize -- a list of objective and gradient

toOpt = function(w,sim=NA,alpha=.05){

       list(objective=es(w,sim=sim,alpha=alpha),gradient=des(w,sim=sim,alpha=alpha))    

}

#equality constraint function.  The jacobian is 1 for all variables

eqCon = function(w,sim=NA,alpha=.05){

       list(constraints=eval_g0(w,sim=NA,alpha=.05),jacobian=rep(1,4))     

}

#optimization options

opts <- list( "algorithm" = "NLOPT_LD_SLSQP",

              "xtol_rel" = 1.0e-7,

              "maxeval" = 1000)

#run optimization and print results

nl = nloptr(w,toOpt,

              lb = rep(0,4),

              ub = rep(1,4),

              eval_g_eq=eqCon,

              opts=opts,

              sim=sim,alpha=.05)

print(nl)

s = nl$solution

obj = nl$objective

To confirm the results, I ran this 100 times and averaged the resulting weights and objective value.  I did the same in SAS IML.  UPDATE: SAS code located here.

From R we get

> apply(s,2,mean)

[1] 0.101697131193604 0.420428046895474 0.000000000004212 0.477874821906712

> mean(obj)

[1] 0.1371

In SAS we get

wgt

0.1015076 0.4218076 -4.75E-20 0.4766848

obj

0.1375891

I am comfortable with the results.

Final Thoughts:
I was only able to get the SLSQP routine to converge.  This routine uses a local quadratic approximation of the function, runs a QP to update the variables, and then iterates.  I am not sure why the others failed.

The number of routines available for problems with equality constraints are limited.  On top of that, the NLopt documentation gives a number of other routines that should accept linear constraints but the R implementation throws an error.  A number of augmented LM routines are available for equality constraints, but again the only one that worked used SLSQP as the sub-optimizer and produced the same result as SLSQP (in about 5x the time).

The time to run the optimization in R is high.  During the 100 iteration sample it took from 1.5-10 seconds per loop depending on the internal iterations needed by nloptr.  In all it took about 10 minutes.  The SAS code ran in 45 seconds.  I see the following as reasons:

1. SLSQP is not as efficient as other routines.  It has to approximate a hessian matrix numerically.  Finding another routine that reliably converges could be a big help.
2. My gradient function is numeric.  Each time the gradient is computed it takes a large number of FLOPs.  This is the same in SAS where the IML optimizer calculates numeric derivatives.
3. As previously discussed, the linear algebra matrix multiplication in R is slow.  This code relies heavily on it.  SAS IML is optimized for matrix math.

If anyone has ideas on how to speed up the R processing, I would love to hear them.  Maybe there are other, more efficient, nonlinear optimization libraries in R.  I'm sure there are parts of my code that can be sped up as well. 

Calling a 3rd party DLL from Base SAS and SAS IMLPlus

I recently finished Rick Wicklin's Statistical Programming with SAS/IML Software.  Great book and provides a great learning resource for SAS IML.

One of the neat things about SAS IMLPlus is it's ability to call 3rd party libraries.  These libraries can be written in Java or any Windows DLL.  Base SAS, since 9.2, has had the ability to link and call a C/C++ compiled library (DLL in Windows, or .so in *NIX).  So let's compare the two ways to write a function and use it in your SAS programs.

First, go read the great tutorial on using FCMP and Visual Studio to create a function for Base SAS.  Many thanks to the excellent folks at SAS Tech Support for writing that.  We'll use the same function for our example.

First the Base SAS example.  We are going to call the DLL two ways.  The TS example shows how to create an FCMP wrapper around a function linked in PROC PROTO.   FCMP also allows us to write functions in SAS code to be called in the Data Step.  So we will write a SAS factorial function, call it,  and the C function wrapped in a SAS function.

options cmplib=(sasuser.proto_ds sasuser.fcmp_ds);

proc proto stdcall package=sasuser.proto_ds.cfcns;

  link 'c:\users\pazzula\documents\visual studio 2010\Projects\SASExampleLib\Debug\SASExampleLib.dll';

 

  int myfactorial(int n) ;

run;

proc fcmp inlib=sasuser.proto_ds outlib=sasuser.fcmp_ds.sasfcns;

   function cfactorial(x);

      return (myfactorial(x));

   endsub;

   function sasFactorial(x);

      p = 1;

      do i=2 to x;

           p = p*i;

        end;

        return (p);

   endsub;

quit;

data test;

n=100000000;

format test $24.;

start = datetime();

Test = "cfactorial";

do i=1 to n;

sp = cfactorial(10);

end;

end = datetime();

elapse = end - start;

ave = elapse / n;

output;

start = datetime();

Test = "sasFactorial";

do i=1 to n;

sp = sasFactorial(10);

end;

end = datetime();

elapse = end - start;

ave = elapse / n;

output;

drop i;

format start end datetime.

       n comma16.

         elapse time12.4;

run;

On my laptop, the cFactorial function averages 1.1633E-7 per call and the sasFactorial averages 1.0204E-7.  The times are relatively close.  It has been my experience that a more complex, well written, C function can outperform a FCMP written function.  There is overhead in calling the function, so that is why we see the simple SAS function running faster.

To call the function in IML Studio (using IMLPlus), we must create and declare a DllFuntion object.  In this object we specify the path to the DLL, the function name, and the number of parameters it takes.  We pass the parameters in order using the NextArgIs*() functions where * is the type.  We call the function with the Call_*() method (again * is the return type).

declare String sPathName;

sPathName = "c:\users\pazzula\documents\visual studio 2010\Projects\SASExampleLib\Debug\SASExampleLib.dll";

declare String sFuncName = "myfactorial";

declare DllFunction func = new DllFunction();

func.Init(sPathName, sFuncName, 1);

func.NextArgIsInt32(10);

s = datetime();

n = 10000;

do i=1 to n;

ret = func.Call_Int32();

end;

el = datetime() - s;

print "elapse: " el ;

print "Average call time: " (el/n) ;

The time elapsed on my laptop is 2.017 seconds for an average of 2.017E-4.  MUCH slower than Base SAS. Why?

The reason is that IML Studio is written in Java.  It is use the Java Native Interface to call the function.  Every time it calls it, the return value is taken from the C dll, into Java, and then into SAS.  Modify the do loop like this and you see a much higher throughput.

declare int objRet;

n = 10000;

do i=1 to n;

objRet = func.Call_Int32();

end;

Now the time is .967 seconds or 9.67E-5.  Still not as fast as Base SAS, but pretty quick.  I actually chatted with the guys at SAS TS about this and they tell me the limiting factor is the jump from Java into SAS.  That is why declaring the objRet in IMLPlus (which is held on the client, not the SAS session) is so much faster.  The take away is to limit the number of trips from the client objects into SAS IML variables.  If you have an array, fill it fully in IMLPlus, and then pass it to SAS.  Don't pass each element from IMLPlus into an IML matrix.

I hope this helps as people look to extend SAS and SAS IML.  Feel free to ask me a question in the comments if you have further questions.

Performance with foreach, doSNOW, and snowfall

Is it just me, or does the performance of the foreach package with a doSNOW backend operating on a socket grid suck?

Here at work, I am helping to setup a cluster of Windows machines for distributed R processing.  We have lots of researchers running code that takes hours to complete and are essentially large for loops with lots of analysis in between.  These guys and gals are not hard core programmers, so there is lots of interest in foreach (as opposed to something like RMPI).

I have successfully setup a POC grid between mutliple machines using sockets and public key authentication.  Assuming we use this, I'll post a how-to, as there is not much on the web on how to get it working on Windows.

In the meantime, I am testing performance.  There is something going on with foreach that I do not understand.  Performance numbers are really bad.

Can anyone explain what is going on here?

> require(doSNOW)
Loading required package: doSNOW
Loading required package: foreach
foreach: simple, scalable parallel programming from Revolution Analytics
Use Revolution R for scalability, fault tolerance and more.
http://www.revolutionanalytics.com
Loading required package: iterators
Loading required package: snow
> require(snowfall)
Loading required package: snowfall
>
> sfInit(parallel=TRUE,socketHosts=rep("localhost",3))
R Version:  R version 2.15.0 (2012-03-30)
snowfall 1.84 initialized (using snow 0.3-9): parallel execution on 3 CPUs.
> cl = sfGetCluster()
>
> f = function(x) {
+    sum = 0
+    for (i in seq(1,x)) sum = sum + i
+    return(sum)
+ }
>
> registerDoSNOW(cl)
>
> out = vector("logical",length=10000)
> system.time( (for (i in seq(1,10000)) out[i]=f(i) ))
   user  system elapsed
  25.99    0.00   25.99
>
> system.time( (out = lapply(seq(1,10000),f) ))
   user  system elapsed
  26.55    0.00   26.55
>
> system.time( (out = parLapply(cl,seq(1,10000),f) ))
   user  system elapsed
   0.02    0.00   15.85
>
> system.time( (out = foreach(i=seq(1,10000)) %dopar% f(i) ))
   user  system elapsed
   6.64    0.42   98.31
>
> getDoParWorkers()
[1] 3

EDIT: HA!  Figured it out.  foreach is not very efficient in communicating tasks as compared to par*apply().  The time to communicate the process overwhelmed the actual processing time.

When I change the code to this, it runs fast (about the same as parLapply()):

> system.time( (out = foreach(i=seq(0,9),.combine='c') %dopar% {
+    apply(as.array(seq(i*1000+1,(i+1)*1000)),1,f)
+ }))
   user  system elapsed
   0.00    0.00   14.03

Calling R from SAS IML Studio

I am playing around with SAS IML Studio 3.4.  For those that do not know, IML (Interactive Matrix Language) is the Matlab-esk language from SAS.  It opperates from normal SAS code through the PROC IML procedure.  A new (to me at least) UI has been developed for analysts called IML Studio.  IML Studio uses a superset of the IML language called IMLPlus.  I'll be digging into it (and the goodies like linked graphs, Java integration, and the ability to call 3rd party dll's) later.

One of the more recent additions to the IML and IMLPlus languages is the ability to run SAS routines from within IML.  At the same time this functionality was added, SAS also added the ability to call R from within IML.  You can now pass IML matrices back and forth betwen R matrices and SAS Datasets back and forth to R Data Frames (and other types).

Having never done this, I fired up IML Studio and set out to learning.

First, save the macros created in my last post into the an Autocall library.  You can modify the autocall libraries by modifying the sasv9.cfg file and adding the path to the SASAUTOS list.  Mine looks like this:

-SET SASAUTOS (
       
        "C:\Users\pazzula\Documents\My SAS Files(32)\9.3\macros"
        "!SASROOT\core\sasmacro"
        "!SASROOT\accelmva\sasmacro"
        "!SASROOT\dmscore\sasmacro"
        "!SASROOT\ets\sasmacro"
        "!SASROOT\iml\sasmacro"
        "!SASROOT\stat\sasmacro"
        )

The SAS file for the macro can be downloaded here.

To submit SAS code, surround the code with "submit;" and "endsubmit;".  This piece will download data for the SPY ETF:

submit;

%get_stocks(spy,25MAY2010,);

endsubmit;

Next, let's create 2 vectors, X and Y.  Make Y and linear function of X.

x = (1:10)`;

y = 1 + 3*x;

e = j(10,1,0);

do i=1 to 10;

               e[i] = .5*rannor(12345);

end;

y = y + e;

 Nothing hard about that. Those new to IML will want to know that ` is the transpose operator and "j(n,m,value)" creates a matrix (n x m) filled with "value."

Exporting IML matrices and SAS Data Sets to R is straight forward.  Use the modules ExportMatrixToR() and ExportDataSetToR().

run ExportMatrixToR(x,"x");

run ExportMatrixToR(y,"y");

run ExportDataSetToR("returns","returns");

The second parameter to each module is the name to give the object in R.  To call R, we again use "submit" and "endsubmit," only this time we add "/ R" to the submit line.  So let's run a linear model on y~x, create an XTS object from the returns Data Frame, chart the cumulative returns of SPY and create an AnnualizedReturn table.

submit /R;

require(xts);

require(PerformanceAnalytics);

m = lm(y~x);

summary(m);

returns = xts(returns$spy,returns$Date);

colnames(returns) = {"SPY"};

chart.CumReturns(returns[,"SPY"],main="Total Return");

table.AnnualizedReturns(returns);

endsubmit;

Produces

Call:
lm(formula = y ~ x)
Residuals:
Min 1Q Median 3Q Max
-0.79782 -0.04944 0.04503 0.17198 0.33329
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 0.85948 0.22469 3.825 0.00505 **
x 3.02539 0.03621 83.548 4.7e-13 ***
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Residual standard error: 0.3289 on 8 degrees of freedom
Multiple R-squared: 0.9989, Adjusted R-squared: 0.9987
F-statistic: 6980 on 1 and 8 DF, p-value: 4.698e-13
                                                SPY
Annualized Return                0.1041
Annualized Std Dev              0.1956
Annualized Sharpe (Rf=0%) 0.5323
and:

The SAS Data Set contained a column called Date that had a SAS Date format applied.  During the conversion to R Data Frame, SAS was nice enough to convert that column into an R date.  

That's pretty much it.  It's pretty straight forward.  Personally, I'm excited about this.  There are some things, like data manipulation, that SAS is way better than R at.  But then there are things that R gives me that I have to work to code in SAS (like easy functions for portfolio analytics).  Now I get the best of both worlds.

Frustration

Google has failed me.  Cannot get RMySQL to install on my laptop.  Looks like I am going to need a different method to get data from MySQL into R.

If anyone has pointers, I'm all ears.

Windows 7 x64, R 2.13.1

Capturing Tick Data via C#, Interactive Brokers, and MySQL - Fixes

It was pointed out to me that MySQL does not store fractional seconds in a timestamp column.  Hat tip to the guys at the Yahoo! Groups for the Interactive Brokers API.

I've modified the SQL to make the time column be a DECIMAL(17,10).

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `tick_db` ;

CREATE SCHEMA IF NOT EXISTS `tick_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `tick_db` ;

-- -----------------------------------------------------

-- Table `ticks`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `ticks` ;

CREATE  TABLE IF NOT EXISTS `ticks` (

  `idticks` INT NOT NULL ,

  `symbol` VARCHAR(8) NOT NULL ,

  `date` DATE NOT NULL  ,

  `time` DECIMAL(17,10) NOT NULL  ,

  `value` FLOAT NOT NULL ,

  `type` VARCHAR(12) NOT NULL ,

  PRIMARY KEY (`idticks`, `date`) )

ENGINE = InnoDB PARTITION BY KEY(date) PARTITIONS 1;

CREATE INDEX `Symbol` ON `ticks` (`symbol` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

And I adjusted the Tick class accordingly.

public class Tick

{

    public String type = "";

    public decimal value = 0;

    public String date = System.DateTime.Now.ToString("yyyy-MM-dd");

    public decimal time = (decimal)System.DateTime.Now.ToOADate();

    public String symbol = "";

    public Int32 index = 0;

    public Tick(String type, decimal value, String symbol, Int32 index)

    {

        this.type = type;

        this.value = value;

        this.symbol = symbol;

        this.index = index;

    }

    public Tick() { }

    public String toInsert()

    {

        String output = "insert into ticks (idticks,symbol,date,time,value,type) values (" +

                            index +

                            ",'" + symbol +

                            "', DATE('" + date + "')," +

                            time + "," +

                            value + ",'" +

                            type + "')";

        return output;

    }

} 

Source is updated here.

Capturing Tick Data via C#, Interactive Brokers, and MySQL

Interactive Brokers is a discount brokerage that provides a good API for programatically accessing their platform.  The purpose of this post is to create an application that will capture tick level data and save that data into a database for future use.

I started to use the IBrokers package in R to do this post.  However, as R is NOT easily threaded and the IB API is heavily threaded, well... oil and water.

Instead I went with the C# port of the API from DinosaurTech.  It's good and it's free.

Luckily you do not need an account with Interactive Brokers for this project.  They have a demo environment available on their webpage.  The data is FAKE, but it's good enough to test connectivity.  Simply run the demo, then go to Configure->API->Settings and insure that the "Enable ActiveX and Socket Clients" is checked and the port is set to 7496.

To follow along in this post, you will need

1. MySQL, MySQL Workbench, and MySQL Connector for .NET (all available here).
2. VS2010 with C#.  You can download the free Express version here.
3. The libraries from DinosaurTech available at the link above.
4. A basic understanding of C# (available here [Pro C# 2010 and the .NET 4 Platform], here [Beginning Visual C# 2010 (Wrox Programmer to Programmer)], or here[Google] ).

To start, install all of the above items.  In MySQL workbench, run the following SQL to create the table to store ticks.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `tick_db` ;

CREATE SCHEMA IF NOT EXISTS `tick_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `tick_db` ;

-- -----------------------------------------------------

-- Table `ticks`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `ticks` ;

CREATE  TABLE IF NOT EXISTS `ticks` (

  `idticks` INT NOT NULL ,

  `symbol` VARCHAR(8) NOT NULL ,

  `date` DATE NOT NULL  ,

  `time` TIME NOT NULL  ,

  `value` FLOAT NOT NULL ,

  `type` VARCHAR(12) NOT NULL ,

  PRIMARY KEY (`idticks`, `date`) )

ENGINE = InnoDB PARTITION BY KEY(date) PARTITIONS 1;

CREATE INDEX `Symbol` ON `ticks` (`symbol` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

You will note that we are partitioning the table by date.  The goal of this is so that as the data grows, day over day, that we can still query quickly.

If you do not not have a user in the database, create one and give it permissions on this table (my user is dom and the password, a super secure dom123).

Before we go on, let's discuss how the IB API works.  Your programming environment connects through their trading platform called TWS (Trader Work Station) as it is running on your computer.  It uses the TWS connections to the IB servers to make your requests.  When you request market tick data for a stock, the data arrives whenever it wants.  There is no synchronous get() function.  You ask for data and the API fires events when that data comes back.  In this, you can have multiple requests running at the same time.  You just have to keep straight what equity data you are processing at any given time.

This threaded nature means that we can process a large number of requests.  It also means that we need to free up the threads that are receiving data as quickly as possible.  I.E.  they shouldn't be writing to the database.  We will create a queue of ticks in a separate thread that will post those to the database as quickly as it can.

Ticks are reported as updates to BID, BID_SIZE, ASK, ASK_SIZE, LAST, LAST_SIZE, VOLUME, HIGH, LOW, and CLOSE.

Here is the plan of action:

1. Create a new console application in C#.
2. Create a Tick class for holding the tick and give it a method that returns the SQL INSERT statement to put that tick into our database.
3. Create a TickQueue class that will process ticks that are inserted into its Queue object as fast as possible.
4. Create a TickMain class that will request all the market data and insert the tick events into the TickQueue.
5. Call the TickMain class from a separate thread in the console application and watch the magic happen.

First, our Tick class

public class Tick

{

    public String type = "";

    public decimal value = 0;

    public String date = System.DateTime.Now.ToString("yyyy-MM-dd");

    public String time = System.DateTime.Now.ToString("H:mm:ss.ss");

    public String symbol = "";

    public Int32 index = 0;

    public Tick(String type, decimal value, String symbol, Int32 index)

    {

        this.type = type;

        this.value = value;

        this.symbol = symbol;

        this.index = index;

    }

    public Tick() { }

    public String toInsert()

    {

        String output = "insert into ticks (idticks,symbol,date,time,value,type) values (" +

                            index +

                            ",'" + symbol +

                            "', DATE('" + date +

                            "'),TIME('" + time + "')," +

                            value +

                            ",'" + type + "')";

        return output;

    }

}

This class is basic.  We simply hold the information about the tick, and create a method that will create our SQL INSERT statement.

Next is the TickQueue.  This is more involved...

public class TickQueue

{

    // Internal queue of db inserts

    private Queue<Tick> queue = new Queue<Tick>();

       

    // Locking Object.  Necessary as multiple threads will be

    // accessing this object simultanously

    private Object qLockObj = new object();

    //Connection object to MySQL

    public MySqlConnection conn = null;

    //Flag to stop processing

    public bool stop = false;

    //Method to enqueue a write

    public void Add(Tick item)

    {

        //Lock for single access only

        lock(qLockObj)

        {

            queue.Enqueue(item);

        }

    }

    //Method to write items as they are enqueued

    public void Run()

    {

        int n = 0;

        //Loop while the stop flag is false

        while (!stop)

        {

            //Lock and get a count of the object in the queue

            lock (qLockObj)

            {

                n = queue.Count;

            }

            //If there are objects in the queue, then process them

            if (n > 0)

            {

                process();

            }

            //Sleep for .1 seconds before looping again

            System.Threading.Thread.Sleep(100);

        }

        //When the shutdown flag is received, write any

        //values still in the queue and then stop

        Console.WriteLine("Shutting Down TickQueue; " + queue.Count + " items left");

        process();

    }

    //Method to process items in the queue

    private void process()

    {

        List<Tick> inserts = new List<Tick>();

        int i = 0;

        //Loop through the items in the queue and put them in a list

        lock (qLockObj)

        {

            for (i = 0; i < queue.Count; i++)

                inserts.Add(queue.Dequeue());

        }

        Console.WriteLine("Processing " + i + " items into database");

        //call insert for each item.

        foreach (Tick t in inserts)

            insert(t);

    }

    //Method to insert a tick

    private void insert(Tick t)

    {

        using (MySqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandText = t.toInsert();

            try

            {

                cmd.ExecuteNonQuery();

            }

            catch (Exception exp)

            {

                Console.WriteLine("OOPS " + exp.Message);

            }

        }

    }

}

You will notice that I am not using threads in this object.  The TickMain object will have the worker thread that call the Run() method in TickQueue.  Because multiple threads will be accessing the object, I've surrounded all access points to the actual queue with a lock() statement.  That will insure that only 1 thread at a time gets access.

I've tried to comment enough to give an idea of what is happening in there.  If not, let me know and I will expand.  The same goes for TickMain below.

Now the  TickMain class:

public class TickMain

{

    //Private and public accessor for the IBClient object

    private IBClient _client = null;

    public IBClient client

    {

        get { return _client; }

        set

        {

            _client = value;

            //If the client is connected, then set the queue.stop = false

            if (_client.Connected)

                queue.stop = false;

            else

                queue.stop = true;

        }

    }

    public List<String> stockList = new List<string>();

    public MySqlConnection conn = null;

    public bool doGet = true;

    private TickQueue queue = new TickQueue();

    private BackgroundWorker bg = new BackgroundWorker();

    private Dictionary<int, String> tickId = new Dictionary<int, string>();

    private int tickIndex = 0;

    private object lockObj = new object();

    //Constructors

    public TickMain()

    {

        initialize();

    }

    public TickMain(IBClient client, List<String> stockList, MySqlConnection conn)

    {

        this.client = client;

        this.stockList = stockList;

        this.conn = conn;

        initialize();

    }

    //Initialization method

    private void initialize()

    {

        //Setup the background worker to run the queue

        bg.DoWork += new DoWorkEventHandler(bg_DoWork);

        //Connect to MySQL if we haven't already

        if (conn.State != System.Data.ConnectionState.Open)

            conn.Open();

        //Don't process the queue if not connected to IB

        if (!client.Connected)

            queue.stop = true;

        //Set the MySQL connection for hte queue

        queue.conn = conn;

        //Get the next value of the queue index

        using (MySqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandText = "select coalesce(max(idticks),0) from ticks";

            MySqlDataReader Reader;

            Reader = cmd.ExecuteReader();

            Reader.Read();

            tickIndex = Reader.GetInt32(0) + 1;

            Reader.Close();

        }

    }

    //Method for getting market prices

    public void Run()

    {

        if (client.Connected)

        {

            //Set up the event handlers for the ticks

            client.TickPrice += new EventHandler<TickPriceEventArgs>(client_TickPrice);

            client.TickSize += new EventHandler<TickSizeEventArgs>(client_TickSize);

               

            //Initialize a counter for stock symbols

            int i = 1;

               

            //Start the queue

            bg.RunWorkerAsync();

            //Request market data for each stock in the stockList

            foreach (String str in stockList)

            {

                tickId.Add(i, str);

                client.RequestMarketData(i, new Equity(str), null, false, false);

                i++;

            }

            //Hang out until told otherwise

            while (doGet)

            {

                System.Threading.Thread.Sleep(100);

            }

            //Remove event handlers

            Console.WriteLine("Shutting Down TickMain");

            client.TickPrice -= new EventHandler<TickPriceEventArgs>(client_TickPrice);

            client.TickSize -= new EventHandler<TickSizeEventArgs>(client_TickSize);

            queue.stop = true;

        }

    }

    //Event handler for TickSize events

    void client_TickSize(object sender, TickSizeEventArgs e)

    {

        //Get the symbol from the dictionary

        String symbol = tickId[e.TickerId];

        int i = 0;

           

        //As this is asynchronous, lock and get the current tick index

        lock (lockObj)

        {

            i = tickIndex;

            tickIndex++;

        }

        //Create a tick object and enqueue it

        Tick tick = new Tick(EnumDescConverter.GetEnumDescription(e.TickType),

            e.Size, symbol, i);

        queue.Add(tick);

    }

    //Event Handler for TickPrice events

    void client_TickPrice(object sender, TickPriceEventArgs e)

    {

        //Get the symbol from the dictionary

        String symbol = tickId[e.TickerId];

        int i = 0;

        //As this is asynchronous, lock and get the current tick index

        lock (lockObj)

        {

            i = tickIndex;

            tickIndex++;

        }

        //Create a tick object and enqueue it

        Tick tick = new Tick(EnumDescConverter.GetEnumDescription(e.TickType),

            e.Price, symbol, i);

        queue.Add(tick);

    }

    //BackgroundWorker delegate to run the queue.

    private void bg_DoWork(object sender, DoWorkEventArgs e)

    {

        queue.Run();

    }

}

Stock requests are given a unique ID.  The tick events have this ID, not the symbol.  So we keep track of the ID and symbol pairs in a Dictionary.  You will note that we have a BackgroundWorker in here that calls and run the database write queue. The tick event handlers process each tick, assign it a unique index ID (another source of possible thread contention, hence the lock() around the index creation).

Finally the program Class in the console application

class Program

{

    static MySqlConnection conn =

        new MySqlConnection("server=LOCALHOST;DATABASE=tick_db;USER=dom;PASSWORD=dom123");

    static TickMain main = null;

    static void Main(string[] args)

    {

        //Open the connections.

        conn.Open();

        IBClient client = new IBClient();

        client.Connect("localhost", 7496, 2);

        //List of stock ticks to get

        List<String> stockList = new List<string>();

        stockList.Add("GOOG");

        stockList.Add("SPY");

        stockList.Add("SH");

        stockList.Add("DIA");

        //Initialize the TickMain object

        main = new TickMain(client, stockList, conn);

        main.doGet = true;

        //Setup a worker to call main.Run() asycronously.

        BackgroundWorker bg = new BackgroundWorker();

        bg.DoWork += new DoWorkEventHandler(bg_DoWork);

        bg.RunWorkerAsync();

           

        //Chill until the user hits enter then stop the TickMain object

        Console.ReadLine();

        main.doGet = false;

        //disconnect

        client.Disconnect();

           

        Console.WriteLine("Hit Enter to Continue");

        Console.ReadLine();

    }

    //Delegate for main.Run()

    static void bg_DoWork(object sender, DoWorkEventArgs e)

    {

        main.Run();

    }

  

}

The application will run, scrolling updates on how many records are being written to the database until you hit the enter key.  After that, the system shuts down and you are prompted to hit enter once more to exit the application.

The entire VS2010 project and SQL for the table can be found here.

My plan is to set this up to run all next week with live data, pull it into R next weekend, and see what we can see.