This post presents a walk-through on how to load, normalize and consolidate vendor data in Asset Control.

While I use the Bloomberg interface as an example, the steps will apply to many other interfaces as well (either for external vendor data or in-house). What I do not discuss here is price consolidation which is covered in a separate post. With this out of the way, let’s jump straight in.

Loading vendor data

Before you can load any vendor data, you need to make the input files available on your AC system. What I have seen and/or implemented at various clients is a directory structure under $AC_DATA/vendor_files with a subdirectory for each vendor or upstream system.

For this post, I will load Bloomberg Back Office files and hence I will create $AC_DATA/vendor_files/bloomberg_bo. To keep my vendor files tidy before, during and after loading, I will create the following directories:

For our example run, I will place two Bloomberg Back Office files inside the process directory such that we have the following directory structure and files:

acdba@acdevbox:~$ mkdir -p $AC_DATA/vendor_files/bloomberg_bo/
acdba@acdevbox:~$ cd $AC_DATA/vendor_files/bloomberg_bo
acdba@acdevbox:~/ac/data/vendor_files/bloomberg_bo$ mkdir raw process success failed
acdba@acdevbox:~/ac/data/vendor_files/bloomberg_bo/process$ cp /vagrant/equity_namr.* process
acdba@acdevbox:~/ac/data/vendor_files/bloomberg_bo/process$ tree .

├── failed
├── process
│   ├── equity_namr.out.20180319
│   └── equity_namr.px.20180319
├── raw
└── success

4 directories, 2 files
acdba@acdevbox:~/ac/data/vendor_files/bloomberg_bo$ 

The out file holds static data and the px file some timeseries. Just enough to demonstrate working with vendor data.

In the next step, I need to prepare and configure the Bloomberg interface to allow me to load Bloomberg files using the ierun command.

  ierun is the central command to run interfaces in AC which are based on the Interface Engine. The Bloomberg interface is one such interface.

And while I have installed the Bloomberg interface as part of a previous post, I still need to make amendments to it to make it fully operational.

I navigate to $AC_SYSTEM/interfaces/BLOOMBERG/bin and execute the following commands to copy in scripts that I can reuse from the samples directory (which was created as part of installing the Interface Engine Utils package):

acdba@acdevbox:~$ cd ac/interfaces/BLOOMBERG/bin
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/bin$ cp ../../samples/unpack .
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/bin$ cp ../../samples/download .
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/bin$ cp ../../samples/jobprovider .
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/bin$ 

Then in $AC_SYSTEM/interfaces/BLOOMBERG/config I need to create a BLOOMBERG.properties file with the following (minimal) content:

acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ cat BLOOMBERG.properties
ie4.flowthreadcount=2
ie4.normalization.enabled=true
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ 

Next, I have to specify which files I want to load. This is done using the file download.urls in $AC_SYSTEM/interfaces/BLOOMBERG/config. I create/change the file to list the vendor files previously copied into the $AC_DATA/vendor_files/bloomberg_bo/process directory:

cdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ cat download.urls
file:///home/acdba/ac/data/vendor_files/bloomberg_bo/process/equity_namr.out.20180319
file:///home/acdba/ac/data/vendor_files/bloomberg_bo/process/equity_namr.px.20180319
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ 

Now, I can run the interface by invoking the following command:

acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ ierun BLOOMBERG -d 20180319
...
acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ 

Once this is finished, I can check the AC Desktop for any ADOs with symbols of the pattern *BB*. In my case, I get the following:

You can see the basic Bloomberg ADOs with symbols starting with BB. And their normalized counterparts starting with N0.BB. Of course, this simplistic approach is only useful in a previously empty environment (at least with regards to Bloomberg ADOs).

An alternative and more targeted way to identify ADOs that have recently been created is to run the following SQL, e.g. in the SQL Tool of the AC Desktop:

select * from ado_locks where symbol like '%BB%' and creation_time > sysdate-1

Normalizing data

Normalization of static data can be done as part of the ierun execution. If you remember, I did set the property ie4.normalization.enabled=true and you can see from the screenshot above that I have a matching N0.BB for every BB ADO. So, normalization of the basic BB ADOs has already been performed.

However, there are situations where you may want to run normalization separately and I want to show one way of how to do this here.

The Normalization interface includes the script ac_make_persistent.pl. A common practise is to create a link to this script in $AC_SYSTEM/local/bin:

acdba@acdevbox:~$ mkdir -p $AC_SYSTEM/local/bin
acdba@acdevbox:~$ cd $AC_SYSTEM/local/bin
acdba@acdevbox:~/ac/local/bin$ ln -s ../../interfaces/NORMALIZATION_N0/ac_make_persistent.pl .
acdba@acdevbox:~/ac/local/bin$ 

Then I amend $PATH in .bashrc to include $AC_SYSTEM/local/bin if it exists (ensure you have sourced bin/acenv.sh by the time you want to reference $AC_SYSTEM):

acdba@acdevbox:~$ tail -5 ~/.bashrc

if [ -d $AC_SYSTEM/local/bin ] ; then
  export PATH=$AC_SYSTEM/local/bin:$PATH
fi

acdba@acdevbox:~$ 

Either source .bashrc. Or - to test your changes will actually take effect going forward - log out and back in.

You can now run the following command:

acdba@acdevbox:~$ echo "select symbol from fundmstr where symbol like 'BB%'" | ac_bl -qs - | ac_make_persistent.pl -w BB - | ac_bl -
+++INFO+++ 20180306_12:20:11 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 14 (14 successful)
acdba@acdevbox:~$ 

Let me break this down a little bit:

• echo "select symbol from fundmstr where symbol like 'BB%'" | ac_bl -qs -
  I generate SQL to find all ADOs starting with BB and use ac_bl to execute it.

• ac_make_persistent.pl -w BB -
  Then I pipe these BB ADOs into ac_make_persistent.pl to generate ACBL for any of the input BB ADOs without a matching N0.BB. That’s with the option -w.

  If I had used the option -a, the script would generate ACBL for all N0.BB ADOs, irrespective of whether they already exist or not.

• ac_bl -
  In the last step, I use ac_bl to load the ACBL generated by ac_make_persistent.pl.

In combination, this will create matching N0.BB ADOs for all the BB ADOs. Knowing how to do this will come in handy when the situation arises. However, most of the time you will configure your interface to perform normalization transparently for you.

Consolidating data

After loading vendor data into basic ADOs and normalizing them, I now need to consolidate them. This is done using the script ematching.sh with the list of N0.BB ADOs as input:

acdba@acdevbox:~$ echo "select symbol from fundmstr where symbol like 'N0.BB%'" | ac_bl -qs - | ematching.sh -
++INFO+++ 20180306_12:24:31 ematching.sh: Starting entity matchingtool
+++INFO+++ 20180306_12:24:31 ematching.sh: called: ematching.sh -
+++INFO+++ 20180306_12:24:31 ematching.sh: Taking ADOs from <stdin>
+++INFO+++ 20180306_12:24:31 ematching.sh: Got lock on /home/acdba/ac/tools/matchingtool/matchingtool.lock [949]
+++INFO+++ 20180306_12:24:31 ematching.sh: Find all C0-ADOs
+++INFO+++ 20180306_12:24:31 ematching.sh: Match N0-ADOs against C0-ADOs
+++INFO+++ 20180306_12:24:37 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 12 (12 successful)
+++INFO+++ 20180306_12:24:37 ematching.sh: Start updating C0-ADOs with additional N0-ADOs
+++INFO+++ 20180306_12:24:39 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 12 (12 successful)
+++INFO+++ 20180306_12:24:39 ematching.sh: Finished updating C0-ADOs with additional N0-ADOs
+++INFO+++ 20180306_12:24:39 ematching.sh: Finished entity matchingtool
acdba@acdevbox:~$ 

I can now search for any C0* ADOs in the AC Desktop to confirm they have been created:

Thank you for reading.