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
$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 acdba@acdevbox:~/ac/data/vendor_files/bloomberg_bo$
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$
$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
$AC_SYSTEM/interfaces/BLOOMBERG/config. I create/change the
file to list the vendor files previously copied into the
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
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
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.
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.
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  +++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.