2016-12-02

Blue & Lonesome

A new Stones album doesn’t come often these days and this new one is a cover album, I expected a lot, these guys have been around for a while and they did some stunning covers some 50 years ago.  After listen to the songs once I think the album is a bit "pale", the only really good track was Howlin’ Wolf’s “Just like I treat you”.  “Just  your fool” and “I can’t quit you baby” are alright, the rest of the songs I don’t remember.
In school I once used the word “quit” in an english essay, my english teacher told me there is no such word but I had heard “I can’t quit you baby” so I knew. However my teacher was not impressed by my reference to a rhythm and blues song. I was as usually given a bad mark in english, "quit" probably didn't matter much. I didn’t write better english then and I wasn’t good teacher’s pet material either, so I expected a bad english mark.

2016-11-19

A Bash snippet

We have a problem with Cron we cannot debug, very seldom but it happens Cron goes wild and incorrectly submits jobs. We have not been able to debug and fix the problem. We only schedule shell scripts (Bash) in Cron so I wrote a snippet to add at the beginning of such shell scripts to prevent execution daytime:
This snippet is far from perfect but it should capture most of these "spurious" submits, until we find the cause, if the submit is trapped by the snippet we will have some more info about the incident and hopefully catch the bug(ger).

2016-11-14

PHP at Bloombergs - 3

This is not a Bloomberg Web Services tutorial. It is a post how I use PHP to connect to Bloomberg Web Services.

Part three, the PHP.  (This post.)

In the previous post I described how i built a Data Warehouse app around a scheduled web service from Bloomberg, ‘daily currency rates’, fetch rates from Bloomberg and distribute reports via mail. The Data Warehouse is built on top of PHP, so it’s natural to build the Bloomberg web client in PHP. I do not have a ready made Soap client for the Data Warehouse, so I built one for Bloomberg.
First you must a have pem access certificate(see part one).
If we look at the integration tag language specification of the web  client:
In the job declaration the parameters needed are specified (with the exception of the passphrase of the certificate).  
  1. The link to the wsdl
  2. The responseId
  3. The certificate
You can also see the program this job invokes pgm=BloombergSoapCurrRate1.php, when this program gets control the parameters is in an array called $job. In the initialization we set up these parameters and others we also define some lambdas used in the program:

The start of  BloombergSoapCurrRate1.php, we take care of the ‘Soap parameters’ and bomb out if someone is missing. You have to replace all $log-> stuff with ‘echo’ or whatever. Now when we have the parms we can call Bloomberg:

I use PHP’s SoapClient, just add in my parameters, exception  and trace I just copied from some example. As you see I bomb out if the return status code is non zero. Now we take care of the response (which is rather complex), first I take care of the response headers:

I just create a more friendly PHP associative array of the headers, and create timestamps in clear text  of the mismash of Bloomberg timestamps in the response headers. I also create a timestamp in local time (which is CET for me) of the started timestamp, so we know when the currency rates were produced, ‘NewYork time’ is not natural for Europeans, especially when we are forced to schedule in ‘London Time’. With that done next thing take care of the currencies:

Now we are almost done, I like to save the result in two SQL tables headers and rates, so while I’m at it I create two SQL insert queries, one for the header and one for the rates:

As you see I save the sql statements in two files in the current working directory.
At last I use two helper lambda functions to create the SQL statements:

The PHP code in it’s entirety:
<?php
/*Copyright (C) 2016 Lars Johansson
This program product is free software;
you can redistribute it and/or modify it under the terms of the
GNU General Public License as published by the Free Software Foundation;
either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. */
/*

// OK Here we go...
//initialize set up lambdas, constants, parms etc.
$sanitize = function ($val){
// This skeleton lambda can be used for sanitizing SQL statements in lambda sqlit
  return $val;
};
$sqlit = function ($sqlarr) use($sanitize){
// This lambda creates the field and value clauses of an SQL insert from an array of assoc arrays
 $fldstmt = ',('.implode(', ', array_keys($sqlarr[0])).')';
 foreach($sqlarr as $arr){
   $val= [];
   foreach($arr as $k => $v){
 $val[] = "'".$sanitize($v)."'";
   }
   $valstmt .= ',('.implode(', ', $val).')';
 }
 return [substr($fldstmt,1), substr($valstmt,1)];
};

$scriptName = $job['data'];
$scriptParm = $job['parm'];
$hdrtable = array_key_exists('HEADTAB',$schedule['_xlate']) ? $schedule['_xlate']['HEADTAB'] : 'HEADTAB';
$crtable = array_key_exists('RATETAB',$schedule['_xlate']) ? $schedule['_xlate']['RATETAB'] : 'RATETAB';

if (array_key_exists('responseId',$job)) {
 $responseId = $job['responseId'];
} else {
 $log->dumpit('Note',"Job",$job);
 $log->enter('Error',"Enter script=$scriptName responseId is missing");
 return FALSE;
}
if (array_key_exists('wsdl',$job)) {
 $wsdl = $job['wsdl'];
} else {
 $log->dumpit('Note',"Job",$job);
 $log->enter('Error',"Enter script=$scriptName wsdl is missing");
 return FALSE;
}
if (array_key_exists('certificate',$job)) {
 $certificate = $job['certificate'];
 if (!file_exists($certificate)){
   $log->enter('Error',"Enter script=$scriptName certificate file=$certificate does not exists");
   return FALSE;
 }
} else {
 $log->dumpit('Note',"Job",$job);
 $log->enter('Error',"Enter script=$scriptName certificate is missing");
 return FALSE;
}

$BloombergResponse = 'BloombergResponse';

$log->enter('Info',"Enter script=$scriptName responseId=$responseId,wsdl=$wsdl,certificate=$certificate");
$log->logit('Info',"Header table=$hdrtable, Rate table=$crtable");

// Rock'n'Roll
if (file_exists("$BloombergResponse")){
 $log->logit('Info',"We alreay have a response from Bloomberg($BloombergResponse)");
 if (!is_object($response = unserialize(file_get_contents("$BloombergResponse")))) {
   $log->logit('Error',"$BloombergResponse can not be unserialized");
   return FALSE;
 }
} else {
 $log->logit('Info',"Trying to contact Bloomberg web server");
 $pp = 'passphrase';
 $params =['responseId' => "$responseId"];
  try {
   $client = new SoapClient("$wsdl"
     , array('soap_version'   => SOAP_1_1
     , 'local_cert' => "$certificate"
     , 'passphrase' => "$pp"
     , 'cache_wsdl' => WSDL_CACHE_NONE
     , 'connection_timeout' => 15
     , 'exception' => TRUE
     , 'trace' => 1
   ));
   $response = $client->retrieveGetDataResponse($params);
 } catch (SoapFault $e){
   $log->logit('Error',"catching pretty bad errors, something went terribly wrong");
   $log->dumpit('Note',"Soap error",$e->getMessage());
   $log->dumpit('Note',"Soap error",$e);
   return FALSE;
 }
 $log->logit('Info',"Bloomberg responded");
 file_put_contents("$BloombergResponse",serialize($response)); // Save it for a rainy day
} // if (file_exists("$BloombergResponse"))

$stscd = $response->statusCode->code;
if ($stscd) {
   $log->logit('Error',"Invalid Bloomberg status code=$stscd");
   return FALSE;
}
$log->logit('Info',"Bloomberg status code=$stscd");
//var_dump($response);
//print "end dumping response\n";

//ok we got an answer first we deal with the headers
$rhdr = [];
$rhdr['stsCode'] = $response->statusCode->code;
$rhdr['stsDescr'] = $response->statusCode->description;
$rhdr['requestId'] = $response->requestId;
$rhdr['responseId'] = $response->responseId;
$rhdr['timestarted'] = $response->timestarted;
$rhdr['timefinished'] = $response->timefinished;

$thdr = (array) $response->headers;
$Sdate = new DateTime($thdr['rundate'] . $thdr['time'] . '00', new DateTimeZone('Europe/London'));  
$thdr['timescheduled'] = $Sdate->format('Y-m-d H:i:sP');  // Bloomberg schedule in London time!
$rhdr = array_merge($rhdr,$thdr);

//Bloomberg responds in 'New York time' no matter what!
// Convert started timestamp from Standard Estern Time day light Savings 'EDT' -> CET
$Stimestamp = substr($rhdr['timestarted'],0,19);
$Sdate = new DateTime($Stimestamp, new DateTimeZone('EDT'));  
$SedtDtTm = $Sdate->format('Y-m-d H:i:sP') . "<br>";  // 'SET' Date time
$Sdate->setTimezone(new DateTimeZone('CET'));   
$ScetDtTmX = $Sdate->format('Y-m-d H:i:sP') . "<br>";  // 'CET' Date time
$log->logit('Info',"start time=$started SET=$SedtDtTm, CET=$ScetDtTmX");
//$rhdr['timestarted_CET'] =  $Sdate->format('Y-m-d H:i:s');
$rhdr = array_merge(['started_local' => $Sdate->format('Y-m-d H:i:s')], $rhdr);
$started = $rhdr['started_local'];
//var_dump($rhdr);
//print "end dumping headers\n";

// Now the names of fields in Bloombergs currency rate response
$fields = (array) $response->fields->field;
//var_dump($fields);
//print "end dumping fields\n";

// Massage the response into a an array of associative arrays (one per currency)
$currrate = [];
foreach($response->instrumentDatas->instrumentData as $daId){
//print "start dumping instrumentData\n";
//  var_dump($daId);
 $dacode = $daId->code;
 $idfromto = $daId->instrument->id;
//  print "DATA CODE $dacode $idfromto\n";
 $td1 = [];
 foreach($daId->data as $da){
//  var_dump($da);
   $td1[] = $da->value;
 }
 $td2['started_local'] = $started;
 $td2['CODE'] = $dacode;
 $td2['FROM_CURRENCY'] = substr($idfromto,0,3);
 $td2['TO_CURRENCY'] = substr($idfromto,3);
 $td2 = array_merge($td2, array_combine($fields, $td1));
//  var_dump($td2);
 if ($dacode == 10 and $td2['FROM_CURRENCY'] == $td2['TO_CURRENCY']){
// Bloomberg's inability to deal with ticker 'USDUSD', should probably reset code as well  
     $td2['NAME'] = $td2['FROM_CURRENCY'] . '-' . $td2['TO_CURRENCY'] . ' F-RATE';
     $td2['TICKER'] = $idfromto;
     $td2['PX_LAST'] = 1;
 }
 $currrate[] = $td2;
}
//print "end dumping instrumentData\n";
//var_dump($currrate);
//print "end dumping currencies\n";

//create SQL statements
$stmtarray = $sqlit([$rhdr]);
$hdrstmt = "INSERT INTO $hdrtable ". $stmtarray[0] . ' VALUES ' . $stmtarray[1];
file_put_contents("$hdrtable.sql", $hdrstmt);   
//var_dump($hdrstmt);
$stmtarray = $sqlit($currrate);
$crstmt = "INSERT INTO $crtable ". $stmtarray[0] . ' VALUES ' . $stmtarray[1];      
file_put_contents("$crtable.sql", $crstmt);
$_RESULT = TRUE;  // indicate successful execution
return(TRUE);


2016-11-13

PHP at Bloomberg - 2

This is not a Bloomberg Web Services tutorial. It is a post how I use PHP to connect to Bloomberg Web Services.

Part one, setting up the environment.
Part two, creating a data warehouse app.  (This post.)

In the first post I described how I got started with Bloomberg web services. In this post I create a Data Warehouse app, which starts by calling a web service and ends by sending this mail from my PHP/Linux Data Warehouse:

For those of you interested in writing jobs for my data warehouse you can start here.
This is a prototype, I did this to get acquainted with  Bloomberg’s web services quickly in an environment I’m familiar with, since these web services are complex and I have to do some trial and error, (later I will transcribe this into PowerShell script or C#).

Before I wrote this application I scheduled a daily request at Bloomberg. It is a request for currency rates and it is released for processing at 06:00 each morning at Bloombergs. I use one  responseId (see the first post) fetching the daily currency rates. Bloomberg is a USA company so I anticipated there would be funny details with time and date values. USA people in general do have little insights in other standards etc than US standards. For USA people USA=America=The world. This means you have to pay attention dates and unit of measure, they tend to use archaic measures, based on anything but metric and ISO systems. I live in the Central European Time Zone. At Bloomberg I have to schedule in London time I think they mean GMT with Daylight Saving, responses I get in New York time, which I think is Eastern Standard Time with DLS. Bloomberg nice support personnel, do not understand I have to think in three time zones, they seemed to think it was an odd request to specify locale time zone=CET. More of that in the next post.     

Without more ado here is the data warehouse application written in DTL, I divided the code into three parts:
  1. Initialisation, setting up constants, retrieving currency rates from Bloomberg and storing those in a MySQL database.
  2. Creating currency rates reports.
  3. Mailing the reports.
Part 1:
Here  I set up some constants, e.g. the name of MySQL database and tables. Then the first job getRates fetches the rates from Bloomberg, and the second inserts the rates into the MySQL database.

Part 2:
This job creates the currency rate report. The sqlconverters creates 3 versions:, excel, CSV and an HTML table.

Part 3:
The last job sendTable, sends the reports to specified recipients.

And that’s it. Most of the code is standard Data Warehouse functionality. The only exception is the job retrieveing data from Bloomberg, since I do not have a ready Soap interface I had to write an extension in PHP calling Bloomberg web service.