GDPR Cookie Consent by Free Privacy Policy Generator
post thumb
Asset Control
by Matthias Hanitzsch-Moonlight/ on 02 Mar 2018

Loading, normalizing and consolidating vendor data

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:

Directory Content / purpose
raw Files as downloaded, before any pre-processing.
process Files ready to be loaded.
success Files moved here from process after successful loading.
failed Files moved here from process after an attempt to load them resulted in an error.


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

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 .

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

acdba@acdevbox:~/ac/interfaces/BLOOMBERG/config$ cat

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

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

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

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:

BB and N0.BB ADOs successfully created.

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
Recently created BB and N0.BB ADOs.

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

Then I amend $PATH in .bashrc to include $AC_SYSTEM/local/bin if it exists (ensure you have sourced bin/ 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


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 - | -w BB - | ac_bl -
+++INFO+++ 20180306_12:20:11 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 14 (14 successful)

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.

  • -w BB -
    Then I pipe these BB ADOs into 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

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 with the list of N0.BB ADOs as input:

acdba@acdevbox:~$ echo "select symbol from fundmstr where symbol like 'N0.BB%'" | ac_bl -qs - | -
++INFO+++ 20180306_12:24:31 Starting entity matchingtool
+++INFO+++ 20180306_12:24:31 called: -
+++INFO+++ 20180306_12:24:31 Taking ADOs from <stdin>
+++INFO+++ 20180306_12:24:31 Got lock on /home/acdba/ac/tools/matchingtool/matchingtool.lock [949]
+++INFO+++ 20180306_12:24:31 Find all C0-ADOs
+++INFO+++ 20180306_12:24:31 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 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 Finished updating C0-ADOs with additional N0-ADOs
+++INFO+++ 20180306_12:24:39 Finished entity matchingtool

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

Newly created C0 ADOs

Thank you for reading.

comments powered by Disqus