Happy New Year

Last task this work year. Upload currency rates for year end closing.

View from the office 08:20 in the morning. If you look closely to the right you can see reflections of me drinking coffee.

The dollar was sinking which worried the financial guys. I suspect it is the recent quibble between US and Russia that makes the market a bit apprehensive.

All the same - Happy New Year to all of you.

SharePoint 5000 limit trick

Another trick to circumvent the dreaded 5000 items limit in Sharepoint.

This trick is from Microsoft, on the page: https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-B8588DAE-9387-48C2-9248-C24122F07C59?ui=en-US&rs=en-US&ad=US

I found this trick how to create a very complex view filter (combining two or even more boolean expressions in the view filter):

“If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.”

This is an example of a view filter definition:

I have not figured out where I type or 'click in' the parentheses, but once I done that, I have great hope this Microsoft trick may work.
If you know how you create this 'dog or cat' filter in sharePoint please drop me a mail.

Update:
After rereading the post my eyes fell on the impossible filter query "Class = Mammals AND (Species = DOG OR Species = Cats)", the latter paranterized part filtering on species where DOG is upper case singular, while the feline Cats is plural lowercase with a leading uppercase 'C'. And missing quotes suround Mammals, DOG & Cats.
Do anyone beleive the author have tested this or even have had a SharePoint list to test the filter on?

Cash free

Andreas sent me this link https://youtu.be/NrmMk1Myrxc really cool. It reminded me I am cash free, it is probably about two year or so since I used cash in Sweden. I use a credit card and Swish for money transfer and the Inet bank for bills. Very convenient. I’m looking forward to the day when we can do away with the cashier counter, this post is unfortunately in swedish only but says a lot about at least my frustration of standing in line waiting to pay.     

Last time I used cash was probably in Antwerp buying bollekes and frietjes. Best beer, best french fries in the world.

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.

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

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 one, setting up the environment.

Part two, creating a data warehouse app.  

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;

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 one, setting up the environment.

Part two, creating a data warehouse app.  (This post.)

Part three, the PHP.

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

Part two, creating a data warehouse app.

Part three, the PHP.

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.