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 Bloomberg's - 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.)
Part four, requesting data

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:

// 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';

$parms = getParms([$job],['responseId'=>'M','wsdl'=>'M','certificate'=>'M','userid'=>'M']);
if (!$parms[0]) return FALSE;
extract($parms);

$usercred=getUserCredentials($context,'bloomberg.xml', "$userid");
var_dump($usercred);
if ($usercred == FALSE){
  $log->enter('Error',"Enter script=$scriptName Bloomberg user $userid credentials is missing"); 
  return FALSE;
}
$passphrase = $usercred['password'];

$BloombergResponse = 'BloombergResponse';
//$BloombergResponse = '/home/tooljn/dw/data/161108174334_getCurrencyRates/getRates/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 { // No response from Bloomberg
  $log->logit('Info',"Trying to contact Bloomberg web server");
  $params =['responseId' => "$responseId"];
  //$params =['responseId' => '1478085720-104624273'];
  //$Scheduledparams =['responseId' => '1478105035-72407552'];
   //$Scheduledparams =['responseId' => '1478861819-71753597'];
  try {
    $client = new SoapClient("$wsdl"
      , array('soap_version'   => SOAP_1_1
      , 'local_cert' => "$certificate"
      , 'passphrase' => "$passphrase"
      , '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;
$Stimestamp = substr($rhdr['timestarted'],0,19);
if (array_key_exists('time',$thdr)) $tmp_sched = $thdr['rundate'] . $thdr['time'] . '00';
else {
  $thdr['time'] = '0000'; // add a dummy entry for SQL insert
  $tmp_sched = $Stimestamp;
}
print "start time $Stimestamp $tmp_sched\n";
$Sdate = new DateTime($tmp_sched, new DateTimeZone('Europe/London'));  
$thdr['timescheduled'] = $Sdate->format('Y-m-d H:i:sP');  // Bloomberg schedule in London time!
$rhdr = array_merge($rhdr,$thdr);
print "start time 2\n";
//Bloomberg responds in 'New York time' no matter what!
// Convert started timestamp from Standard Estern Time day light Savings 'EDT' -> CET

$Sdate = new DateTime($Stimestamp, new DateTimeZone('EDT')); 
print "start time 3\n";
$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);
  if ($td2['TO_CURRENCY'] == '') $td2['TO_CURRENCY'] = $td2['FROM_CURRENCY'];
  $td2 = array_merge($td2, array_combine($fields, $td1));
//  var_dump($td2);
  $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);   
$stmtarray = $sqlit($currrate);
$crstmt = "INSERT INTO $crtable ". $stmtarray[0] . ' VALUES ' . $stmtarray[1];      
file_put_contents("$crtable.sql", $crstmt);
//var_dump($hdrstmt);
//var_dump($crstmt); 
return $_RESULT = TRUE;


2016-11-13

PHP at Bloomberg's - 2

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

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
  2. storing the retreived data in a MySQL database.
  3. Creating currency rates reports.
  4. Mailing the reports.
Part 1:
Here  I set up some constants, e.g. the name of MySQL database and tables. In the exit tag I define workflows to submit after successful exection of this workflow. Then the job getRates fetches the rates from Bloomberg.
Part 2:
These jobs inserts the retrieved data into the data warehouse.


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

Part 4:
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 retrieving data from Bloomberg, since I do not have a ready Soap interface I had to write an  extension in PHP calling Bloomberg web service.

PHP at Bloomberg's - 1

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. (This post.)

Recently I have created some web services clients importing data from cloud services. This has been very simple tasks, I just got a link to a REST service with a few parameters. Call the link and receive a JSON or XML response. Now the financial guys asked me to import currency rates from Bloomberg I recommended to use the SOAP web services from Bloomberg. Before I started I had to register at Bloomberg Customer Service Center to get access to documentation. I realised this was going to be more complicated than my previous web services clients, the Bloomberg SOAP protocol is complex I didn’t understand much of the voluminous documentation. I found a recommendation of using SoapUI to test and try Bloomberg’s web services. SoapUI turned out to be a great software, a must if you are new to Bloomberg web services. (There are other softwares similar to SoapUI.) Without SoapUI I would probably still be trying to figure out how to use the services and set up the complex parameters. Here you see part of a simple request:

With the help of SoapUI it was actually simple to setup and test requests. None of them worked so I started to create tickets at the service center, (the only way to get help from Bloomberg). I do not know if i was just unlucky or if I was not skilled enough to ask the right question but responses to my first ticket was far from good and slow. I had problems with the security certificate, when you access Bloomberg web services you need to authenticate yourself with a certificate. I installed the certificate wrongly, when you start installing the certificate you get this popup:

You should press save and store the certificate in a safe place, where the web services can access it.  (The Open button is a red herring that guides you through an installation process that ends with  ‘install was successful’, which led me to the faulty conclusion I had installed the certificate sucessfully.)
After I had sorted out the certificate issue, the web services started to respond that my request was in error. New tickets to the service center and now (I had more specific  questions) the service center responded promptly (often with an hour or two) after a while I could ask my first legal query.
This is how Bloomberg web services works. Send in a request and you receive a responseId, you then use this responseId to ask for the result of your request. A typical response looks like this (in SoapUI):
 
You then take the responseId and ask for the result of your request like this:

For me it normally takes 10 to 40 minutes before the result is ready for delivery.
I realised it would not be super simple to create, schedule and use Bloomberg Soap Web Services, instead of creating my web client in C# which I’m still far from comfortable with I decided to try PHP in Linux which I know much better.
I had to transfer my p12 access certificate (see above) to Linux. I did a binary FTP transfer to my Linux server, of course that did not work. This is how you should transfer the certificate to Linux and PHP. First to a normal ASCII transfer (I used FTP) then in a console session convert the p12 certificate to a pem certificate with this openssl command:
openssl pkcs12 -in Certificate.p12 -out Certificate.pem -clcerts
 
Now I’m ready to access Bloomberg web services from PHP/Linux.

Update:
In Windows you can start up a bash environment by firing off a Git console:
& 'C:\Program Files\Git\bin\sh.exe' --login
in Powershell and then run the openssl comand above.
If you do not have Git installed there are other opensll for Windows, Google around and download a version that suit you.


2016-11-12

The silent 2000 limit

Last week we hit the silent 2000 limit in SharePoint, this limit cuts off pull down menus after 2000 items, this bugger is really nasty because SharePoint does not warn you in anyway it just removes trailing entries. “I cannot find the entries I want in the GAC pulldown”. “Well you should there are not SP super many GACs (more than 5000), just SP extremly many (more than 2000) and that Sharepoint can handle”, sluggish yes, but it can handle more than 2000 items. “Show me my ‘UB40’ then”. Of course ‘UB40’ was missing, after a very, very long while I found the silent 2000 limit in Sharepoint, and ‘UB40’ was one of the last in a 2683 items long list of GACs and thus just cut off.
This time I did not try to find another solution I just replaced the pulldown with a simple text field. Of course I had to copy all the GAC values from the old pulldown to the new text field. Noone I had close at hand could help me with a ‘copy script’ so I had to copy the hard way copy’n’paste. (I can probably knock together a field copy Powershell Script, but I have not done this before and was in a hurry, and a bit upset about this 2000 limit ‘feature’.) Anyway it took me almost a day and I got a lateral epicondylitis. If you copy more than a SP many (a few) fields, SharePoint gets stressed and cannot communicate with the SharePoint server:
 
First you go into List Quick Edit mode:

Then you copy’n’paste SP reasonable amount (very few) items, then you stop edit (save), then you go into List Quick Edit again, scroll down to where you left and copy’n’paste a SP reasonable chunk, and stop edit… until you have processed this entire SP super large (more than 5000 items) list.

SharePoint is great for setting up simple collaboration web sites, but not for an application storing reference data. This I have learned the hard way.