QUIK real time data export over DDE in C++ networking server

Financial software development peak happened during dot-com bubble and later in 2000’s. At the time, many trading clients were based on Windows/Intel platform and thus in the application core it still relies on legacy technologies.

Dynamic Data Exchange (aka DDE) was introduced back in 1987, as Microsoft functional alternative to RPC (Remote Procedure Call), known from some Unix platforms. Primary function of DDE is interprocess communication and sharing big amounts of data. By using menu system in QUIK, users can set up export of live market tick datafeed, for entire market or securities of interest. We measured that on a 100M internet connection, application can receive stock exchange tick update in less than a 100ms.

We implemented a networking server that starts in parallel to QUIK on a server machine, hooks up to a data feed and distributes tick data over a number of connected algotrade processes over TCP/IP network. Networking server is implemented by using MINGW/GCC C++/Boost.

QUIK is using Excel protocol, pretty much sending binary compressed Excel sheets over the link.

Here is WinMain function for DDE listening server.  Please excuse some debug output in the code, that captures the datefeed, feel free to comment that out:

LPCWSTR szTopic = (LPCWSTR)L"[Book1]deals"; 
HDDEDATA EXPENTRY DdeCallback(UINT type, UINT fmt, HCONV hConv, HSZ hsz1, HSZ hsz2, HDDEDATA hData, DWORD dwData1, DWORD dwData2);

int APIENTRY WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int nCmdShow) {
    if(DMLERR_NO_ERROR!=DdeInitialize(&idInst, DdeCallback, APPCLASS_STANDARD, 0)) {
        MessageBox(NULL, L"DdeInitialize() failed", L"Error", MB_SETFOREGROUND);
        return -1;
    }

    std::stringstream sssr;
	time_t t = time(0);   // get time now
	struct tm * now = localtime( & t );
	sssr << "/home/dmi/soft/dde/ticks/" << (now->tm_year + 1900) << "-" << (now->tm_mon + 1) << "-" << now->tm_mday << ".csv";

	std::cout << welcomeMsg << std::flush;

	f.open(sssr.str().c_str());

 	std::cout << "DdeInitialize: success" << std::endl;
    hszService = DdeCreateStringHandle(idInst, L"EXCEL", NULL);
    hszTopic = DdeCreateStringHandle(idInst, szTopic, NULL);
    hszItem = DdeCreateStringHandle(idInst, L"MyItem", NULL);
    if( (hszService == 0) || (hszTopic == 0) || (hszItem == 0) ) {
        MessageBox(NULL, L"Id can not be created", L"Error", MB_SETFOREGROUND);
        return -2;
    }
    if(!DdeNameService(idInst, hszService, NULL, DNS_REGISTER)) {
        MessageBox(NULL, L"DDE Server Registration failed!", L"Error", MB_SETFOREGROUND);
        return -3;
    }

	    MSG msg;
	    while(GetMessage(&msg, NULL, 0, 0)) {

	        if(msg.message == WM_TIMER) {
	            g_count++;
	            DdePostAdvise(idInst, hszTopic, hszItem);
	        }
	        TranslateMessage(&msg);
	        DispatchMessage(&msg);
	     }


	f.close();
    return msg.wParam;
}

Here is Excel Xtbl parsing callback:

std::vector< std::vector<Cell> > ParseXTbl(unsigned char bf[], short &Rows, short &Colls)
{
    int a = 0;
    unsigned char cb = (unsigned char)(bf[a] + bf[a + 1] * 256);
    a += 2;
    if(cb==16)
    {
        a = 2;
        cb = (unsigned char)(bf[a] + bf[a + 1] * 256);
        a += 2;
        Rows = (short)(bf[a] + bf[a + 1] * 256);
        a += 2;
        Colls = (short)(bf[a] + bf[a + 1] * 256);
        a += 2;

    	//std::cout << "Readng table " << Rows << "x" << Colls << std::endl;

        std::vector< std::vector<Cell> > Tbl(Rows, std::vector<Cell>(Colls));

        unsigned char* ptr = bf;
        for(int R=0; R<Rows; R++)
            for(int C=0; C<Colls;)
            {
                unsigned char Tp = (bf[a] + bf[a + 1] * 256);
                a += 2;
                cb = (unsigned char)(bf[a] + bf[a + 1] * 256);
                a += 2;
                if(Tp==1)
                {
                    for (; cb > 0; cb -= 8, a += 8, C++)
                    {
                        Tbl[R][C].d = *((double*)(ptr + a));
                        Tbl[R][C].t = static_cast<Cell::cType>(Tp);
                    }
                }
                else if(Tp==2)
                {
                    for (; cb > 0; cb --, C++)
                    {
                        unsigned char cb1 = bf[a];
                        a++;
                        cb -= cb1;
                        Tbl[R][C].s.clear();
                        for(; 0<cb1; cb1--, a++)
                            Tbl[R][C].s += (char)bf[a];
                        Tbl[R][C].t = static_cast<Cell::cType>(Tp);
                    }
                }
            }

        return Tbl;
    }
}

DDE callback where actual XTable parsing takes place:

HDDEDATA EXPENTRY DdeCallback(UINT wType, UINT fmt, HCONV hConv, HSZ hsz1, HSZ hsz2, HDDEDATA hData, DWORD dwData1, DWORD dwData2) {

    switch (wType) {

        case XTYP_CONNECT:
        ....
            	// XLTABLE parsing
		case XTYP_POKE:
			{
				if(!DdeCmpStringHandles(hsz1, hszTopic))
				{
					//--if(hConvApp == hConv)
					//--{
						//std::cout << "Client started sending the table"<<std::endl;
						int r=0, c=0;
						int iCount = DdeQueryString(idInst, hsz2, NULL, 0, CP_WINANSI) + 1;
						char* bf = new char[iCount];
						DdeQueryString(idInst, hsz2, (LPWSTR)bf, iCount, CP_WINANSI);
						std::string s = bf;
						iCount = DdeGetData(hData, NULL, 0, 0);
						bf = new char[iCount];
						DdeGetData(hData, (unsigned char*)bf, iCount, 0);
						short Rows, Cols;

						std::vector< std::vector<Cell> > Tbl = ParseXTbl((unsigned char*)bf, Rows, Cols);

In the end of DDE callback procedure, on receiving a complete column of XTable data, we invoke   NetworkSender::add_line method that makes this data available to all the connected networking clients. Since we may have DDE process and few network socket connections running in parallel, thread safety is a key in this application:

class NetworkSender : public ActiveObject {
    public:
	NetworkSender(){
		_thread.Resume();
		}
	~NetworkSender(){
		Kill ();
		}
	void add_line(const std::string& l)
	{
		{
		Lock lk(_mutex,"ADDLINE");
		server << l;
		}
		_event.Release();
	}
	Event _event;
	private:
		void InitThread(){}
		void Run();
		void FlushThread(){}
		Mutex _mutex;
		ServerNetwork server;
	};

ServerNetwork class handles distribution of data. Connecting and implementation networking clients are discussed in a blog most “Effective C++ implementation of server client network” blog post

class ServerNetwork
{

    std::vector<std::string> quotes;

public:

	struct Walker{
		unsigned long pos;
		Walker() { pos=0; }
		};

	 // table to keep track of each client's socket
	 std::map<SOCKET,Walker> sessions;

	BOOL hasQueue() const { 
	  
	    for(std::map<SOCKET,Walker>::const_iterator i=sessions.begin();i!=sessions.end();i++)
		
		if(i->second.pos<quotes.size()) return true;
		  
	    return false;
	    }
	    
	BOOL hasClients() const { 
	    return sessions.size()!=0;   
	    }
	    
	 ServerNetwork(void);
	 ~ServerNetwork(void);

	bool acceptNewClient();

	void update();
	void processQueue(int n=10);

	ServerNetwork& operator << (const std::string& s) { quotes.push_back(s); }

	void sendToAll(const char * packets, int totalSize);

	void sendActionPackets();

	 // Socket to listen for new connections
	 SOCKET ListenSocket;

	 // Socket to give to the clients
	 SOCKET ClientSocket;

	 // for error checking return values
	 int iResult;

};

 

Extract and SQL intraday trading data from binary QUIK database

Screen Shot 2014-12-17 at 3.03.12 PM
QUIK trading platform creates binary historical database that can be found in /archive folder. We reverse engineered the binary format and figured how to export tick and minute data. All the derived data is extrapolated to 2,3,5,10 minutes and so on.

QUIK is a windows application, but we have completely migrated to Mac Mavericks in 2014 so we are running it in Wine Mac emulation layer for Microsoft Windows software. The binary export solution however is written in PHP and works on any platforms, so if you can copy the binary database into high-performance Linux or MAC machine so it can upload data to SQL directly. Uploading Gigabytes of stock trading data may take really significant amount of time (days) so using powerful machine for data loading is quintessential for this task.

Make sure that your SQL server allows DATA INFILE transactions. That is one of the fastest methods of importing large amounts of trading data, but it may require changing security settings for your database installation.

Stock exchange binary data extractor is a part of our C++ web based trading robot project

#!/usr/bin/php
<?php
// $Id: iQuik.php,v 1.2 2013/09/02 18:12:44 dmi Exp $

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);

include "connecti.php";

$quikDir="/mnt/sda2/QUIK_VTB24/archive";
//echo $quikDir."\n";

$eqs=array();

foreach(scandir($quikDir) as $f){
  //echo "$f\n";
  if(preg_match("/EQ[A-z]+_([A-z0-9]+)_([A-z0-9]+).dat/i",$f,$matches)){
 // if(preg_match("/EQ[A-z]+_(ABRD|URKA)_([A-z0-9]+).dat/i",$f,$matches)){
    //echo $matches[1] . " " . $matches[2] ."\n";
    $eqs[$matches[1]][$matches[2]]=$f;
    }
  }
 
class BinFile {

  protected $h;
  
  protected $flagError=0;
  
  function __construct($fn){
    $this->h = fopen($fn, "rb");
    }
    
 function __destruct(){
    if($this->h) fclose($this->h);
    }

  public function readInt(){
    $v = unpack("i",fread($this->h, 4));
    return $v[1];
    }

  public function readDouble(){
    $dv=fread($this->h, 8);
    if(strlen($dv)==0) {
      $this->flagError=1;
      return -1;
      }
    $v = unpack("d",$dv);
    return $v[1];
    }
   };

class QuikEntry {

  public $Open;
  public $High;
  public $Low;
  public $Close;
  public $Volume;
  public $Time;
  public $Date;
    
  public function printMe(){
    echo "{$this->Date} {$this->Time} Open {$this->Open} Close {$this->Close} High {$this->High} Low {$this->Low} Volume {$this->Volume}\n";
    }
    
  public function getTimestamp() {
      	$rv="";
  		if(preg_match("/([0-9]{4})([0-9]{2})([0-9]{2})/",$this->Date,$matches)){
  				$rv=$matches[1]."-".$matches[2]."-".$matches[3]." ";
  				}
		if($this->Time==""){
			// weekly, daily
			return $rv." 18:59:59";
			}
		if(strlen($this->Time)<6) {
			// Add leading 0 to 9-00-00
			$this->Time="0".$this->Time;
			}
  		if(preg_match("/([0-9]{2})([0-9]{2})([0-9]{2})/",$this->Time,$matches)){
  				$rv.=$matches[1]."-".$matches[2]."-".$matches[3];
  				return $rv;
  				}
  		die("Date format is wrong {$this->Date} {$this->Time}");
  		}
  	
  	public function cvsMe($handle) {
  		$timestamp=$this->getTimestamp();
  		fwrite($handle, 
  			"{$timestamp};{$this->Open};{$this->Close};{$this->High};{$this->Low};{$this->Volume}\n"
  			);
  		}
    
  public function sqlMe($tab){
  		$timestamp=$this->getTimestamp();
  		mysqli_query($db,"INSERT INTO `quik`.`{$tab}`
(`open`,
`high`,
`low`,
`close`,
`volume`,
`timestamp`)
VALUES
(
'{$this->Open}',
'{$this->High}',
'{$this->Low}',
'{$this->Close}',
'{$this->Volume}',
'{$timestamp}'
);
") or die(mysqli_error());
		mysqli_query($db,"OPTIMIZE TABLE `quik`.`{$tab}`") or die(mysqli_error($db));
  		}
  };
  
class DatFile extends BinFile {

  public $entries=array();
  
  public function __construct($fn,$tab){
  		global $db;
      parent::__construct($fn);
      echo "Reading {$fn}\n";
      echo "Version: ".$this->readInt($this->h)."\n";
      $uid=fread($this->h, 37);

	$cvsFile="/home/dmi/Trading/cvs/".$tab.".cvs";
	$handle=fopen($cvsFile,"w") or die("Unable to open write cvs file\n");
      
    while(!feof($this->h)){
      $v1=$this->readEntry();
      if($this->flagError) break;
      //$v1->printMe();
      //$v1->sqlMe($tab);
	$v1->cvsMe($handle);
      $entires[]=$v1;
      }      

	$sql="LOAD DATA LOCAL INFILE '{$cvsFile}' INTO TABLE `quik`.`{$tab}`
			FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
			(`timestamp`,`open`,`close`,`high`,`low`,`volume`)" or die(mysqli_error($db));
			
	//echo $sql."\n";
	
	// Load CVS into mysql server
	mysqli_query($db,$sql) or die(mysqli_error($db));
	mysqli_query($db,"OPTIMIZE TABLE `quik`.`{$tab}`") or die(mysqli_error($db));
	fclose($handle);

    echo "Done\n";
    }
  
  public function readEntry(){
    $q=new QuikEntry();
    $q->Open=$this->readDouble();
    if($this->flagError) return "";
    $q->High=$this->readDouble();
    $q->Low=$this->readDouble();
    $q->Close=$this->readDouble();
    $q->Volume=$this->readDouble();
    $q->Date=$this->readInt();
    $q->Time=$this->readInt();
    return $q;
    }
  };

function tabCr($tab){
	global $db;
	mysqli_query($db,"DROP TABLE IF EXISTS `quik`.`{$tab}`");
	mysqli_query($db,"CREATE TABLE `quik`.`{$tab}` (
  `open` double DEFAULT NULL,
  `high` double DEFAULT NULL,
  `low` double DEFAULT NULL,
  `close` double DEFAULT NULL,
  `volume` double DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`timestamp`),
	UNIQUE KEY `timestamp_UNIQUE` (`timestamp`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8") or die(mysqli_error($db));
	}

  //$df = new DatFile($quikDir."/EQBR_URKA_120.dat");
  
mysqli_query($db,"DROP TABLE IF EXISTS `quik`.`periods`") or die(mysqli_error($db));

mysqli_query($db,"CREATE TABLE `quik`.`periods` (
  `symbol` varchar(12) NOT NULL,
  `1` varchar(1) DEFAULT ' ',
  `2` varchar(1) DEFAULT ' ',
  `3` varchar(1) DEFAULT ' ',
  `4` varchar(1) DEFAULT ' ',
  `5` varchar(1) DEFAULT ' ',
  `6` varchar(1) DEFAULT ' ',
  `10` varchar(1) DEFAULT ' ',
  `15` varchar(1) DEFAULT ' ',
  `20` varchar(1) DEFAULT ' ',
  `30` varchar(1) DEFAULT ' ',
  `60` varchar(1) DEFAULT ' ',
  `120` varchar(1) DEFAULT ' ',
  `240` varchar(1) DEFAULT ' ',
  `day` varchar(1) DEFAULT ' ',
  `month` varchar(1) DEFAULT ' ',
  `week` varchar(1) DEFAULT ' ',
  PRIMARY KEY (`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8") or die(mysqli_error($db));


  foreach($eqs as $eq=>$graphs){
		mysqli_query($db,"INSERT INTO `quik`.`periods` (`symbol`) VALUES('{$eq}')") or die(mysqli_error($db));
  		foreach($graphs as $dur=>$fn){
  				$tab="z".$eq."_".$dur;
		  		echo "$tab = $fn\n";
		  		tabCr($tab);
				$df = new DatFile($quikDir."/".$fn,$tab);
				mysqli_query($db,"UPDATE `quik`.`periods` SET `{$dur}`='x' WHERE `symbol`='{$eq}'") or die(mysqli_error($db));
  				}
  		}
?>

Bug tracking system

Virus SearchBug tracking system aka BTS is a critical software development  component, a corner stone for product quality process, quality management and release planning. BTS is serving a communication media role between development, quality assurance and a customer. For management, it allows to introduce quality metrics and provides birds view on product quality, allows planning a release.

There plenty of various commercial and open-source BTS and issue trackers available (most famous are Bugzilla, Trac, ClearQuest, Sugar) and often customers prefer developing their own custom bug tracking solution.

In our work, we identified following BTS key features that most of the customers need to look for:

  • Group access control and flexible security policies
  • Integration with version control system such as CVS
  • Full bug report lifecycle management
  • E-mail notifications
  • Integration with code browser
  • Bug priority levels
  • Bug criticality
  • Managerial release maturity and quality metrics
  • Project Management integration
  • High performance

It happened in our practice that a wrong choice of bug tracking system often leads to a serious and sometime critical shortcomings in software development process. Not only quality of the products is suffering, it may create unmanageable QA situation, poor and outdated response to problems and lead to major customer and employee unhappiness.

For Electronics Design Automation projects, we do always recommend Bugzilla as #1 choice for new developments.

High performance C++ web-based trading robot

Screen Shot 2014-12-17 at 2.20.31 PM

We have a few private and corporate clients in Russian and US financial sector and completed a number of projects for them. In 2014, we had a project with private customer who uses QUIK for online trading with somewhat sophisticated personal trading strategies. Not only our client knows certain businesses and industries well, he also wanted rapid automation of his custom market trading approaches. A client asked to implement a high-speed trading robot and machine-learning library that optimizes for certain stock market patterns. Novorado first needed to export historical binary data for 3 years from QUIK database into his SQL DB (size of the DB reached few Gigabytes), than train used algorithms for certain share holders behavioral patterns (such as end of quarter, release of annual financial results, dividends payments, etc) and learn optimal behavior from particular market situations and market data.

We want to share our findings with you and below will publish essential source code that will help building your own C++ trading robot for Russian stock market with web management and QUIK as backend solution.

The project essentially brake down into following sub-stages:

  • Automate QUIK GUI inputs so it generates high-frequency minute and tick historical data for select stock
  • Analyze historical binary QUIK database and extract data from it, upload into SQL
  • Using Technical Analysis library, implement few cost functions
  • Using historical data, train those cost functions for optimal intra-day trading and specific situations (selected by customer trough web interface)
  • Implement DDE data export routine that interfaces trading robot with QUIK for market orders
  • Implement high-speed trading robot in C++. It should react to market change in 100ms
  • Minimize risk and maximize profit: automatically generate stop-loss and take-profit orders
  • Using real-time market data, run real-time trading simulation and evaluate trading strategy results. Present log of actions for thorough trader inspection and evaluation
    • Run real-time algorithm re-training using historical intraday data of the same day
  • Trade on a stock market

Using the tool we developed, our client was able to achieve outstanding financial results and reached 50+% annual ROI in 2014 on MICEX stock exchange. Our research demonstrated that there can be no miracle, and while trading robots can deliver fantastic results in a trending market automatically, general strategy needs to be identified by a qualified industry and market specialist. Even the slow speed (100ms market delay is dictated by ability of QUIK servers to deliver data to a client application and than there can be some DDE delay as well) trading robots dramatically improve daily trading performance over traditional market automation tools.