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

Price consolidating data

In this article I demonstrate how to configure and run price consolidation for a simple straight-through scenario.

Installation of Price Consolidation module

First of all, I need to install the price consolidation module. I simply place the installation package into $AC_SYSTEM and extract it. Then I change to tools/pc and run ./pc_install ALL:

acdba@acbox:~/ac$ tar xzf price-consolidation-2.0.10-pkg.tar.gz
cdba@acbox:~/ac$ cd tools/pc
acdba@acbox:~/ac/tools/pc$ ls
bin  build_price-consolidation  datamodel  formulas  local  pc_install  readme
acdba@acbox:~/ac/tools/pc$ ./pc_install ALL
+++INFO+++ 20180714_10:24:04 pc_install: Installing scripts...
+++INFO+++ 20180714_10:24:04 pc_install: Installing scripts completed
+++INFO+++ 20180714_10:24:04 pc_install: Installing formulas...
+++INFO+++ 20180714_10:24:04 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 1 (1 successful)
+++INFO+++ 20180714_10:24:04 pc_install: Installing formulas completed
+++INFO+++ 20180714_10:24:04 pc_install: Installing attributes and templates...
+++INFO+++ 20180714_10:24:05 @(#)ac_bl[7.3/$Revision: 47831 $]: Number of delivered transactions: 4 (4 successful)
+++INFO+++ 20180714_10:24:05 pc_install: Installing attributes and templates completed
+++INFO+++ 20180714_10:24:05 pc_install: Installation completed
acdba@acbox:~/ac/tools/pc$

With this, the installation is complete.

A quick overview

Before I continue, it is probably a good time to look at the bigger picture. What does price consolidation do? And how does it do that?

In simple terms, price consolidation is the process of taking pricing data from one or more sources (think data vendors) and - through a set of price consolidation rules - determine the golden copy price to be persisted for a given date.

The central element of this process are the price consolidation rules. These are stored on a price consolidation config ADO, often PC ADO in short. Also stored on this PC ADO are the tree to store the prices in, the time intervals of source data to be considered and the attribute on the C0 ADO that links to the source ADOs to be used for price consolidation.

So, the latter needs to be an attribute on the C0 ADO that returns for example its underlying BB ADOs. As I do not have such an attribute yet, I will extend the C0 data model to create this plus an attribute to hold the consolidated price in the next section.

Extending the C0 data model

As mentioned above, I need to create two custom attributes:

  • A link to price consolidation sources. This will be C0#SRC01.
  • An attribute to store the consolidated price. This will be C0#TS001.

This follows the AC naming convention of:

  • C0_ - standard attribute
  • C0+ - enriched attribute
  • C0# - custom attribute

In order to extend the existing standard C0 data model with my custom attributes, I will need to create the following files under $AC_SYSTEM/interfaces/CONSOLIDATION_C0/datamodel/additional.

Files shown are tsv files, so columns are separated by a TAB. But you don’t need to type this all yourself. Instead, you can download the C0 data model extension from here.

C0#sht.tsv

The main sheet tying all our custom sheets together and linking back to the main sheet of the standard C0 data model.

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat C0#sht.tsv
sheet	file	description	client
met	C0#met.tsv	meta data sheet	1
sht	C0_sht.tsv	main datamodel sheet	0
sap	C0#sap.tsv	static attribute properties	1
frm	C0#frm.tsv	formulas	1
dap	C0#dap.tsv	datafile attribute properties	1

C0#met.tsv

Holds necessary meta information about our C0 data model.

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat C0#met.tsv
key	value
PREFIX	C0
INTERFACE_ID	CONSOLIDATION_C0
FORMAT	1

C0#sap.tsv

The static attribute properties sheet, where I define our new C0#SRC01 attribute as pure derived, multi-valued, non-profiled and link it to a formula of the same name.

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat C0#sap.tsv
attribute_id	name	type	domain_id	is_derived	is_trigger	is_multivalued	is_profiled	formula_id
C0#SRC01	PRICE CONSOLIDATION SOURCE ADOS	ADO		Y	N	Y	N	C0#SRC01

C0#frm.tsv

The formula sheet in which I define the C0#SRC01 formula and specify the file holding its implementation.

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat C0#frm.tsv
formula_id	name	text_file	dimension	type
C0#SRC01	PRICE CONSOLIDATION SOURCE ADOS	C0#SRC01.fe	UNDEFINED	DERIVED_STATIC_ATTRIBUTE

The implementation of the formula C0#SRC01 is shown below:

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat fe_code/C0#SRC01.fe
function getBasicAdoOf(n0Ado) {
  return elt(n0Ado, [3, len(n0Ado)]);
}

function select() {
  local list n0Sources = attribute($SYMBOL, 'C0_SRC01');
  return map(getBasicAdoOf, n0Sources);
}

It falls back on the attribute C0_SRC01, a standard attribute that already links back to the underlying N0 ADOs. All I have to do is strip off the ‘N0.’ prefix using the elt function.

C0#dap.tsv

The datafile attribute properties sheet with the new custom datafile attribute C0#TS001 to hold the consolidated price.

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cat C0#dap.tsv
attribute_id	name	type	formula_id	is_key	is_checked	is_corrected	is_derived	storage_size
C0#TS001	PRICE	FLOAT		N	Y	N	N	8
acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$

With these files in place, I can load my custom C0 data model:

acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/datamodel/additional$ cd ../../bin
acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/bin$ ./installdm
...
acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/bin$ ./installdm -u
...
T The data model has been updated 397ms
Initializing Update and Query server processes
acdba@acbox:~/ac/interfaces/CONSOLIDATION_C0/bin$

I can now move on to setting up a price consolidation config ADO.

Creating a PC config ADO

An easy way to set up a PC ADO is to use the AC Desktop (I am loyal to the 2.x series for its administrative capabilities).

I just open the ADO module and create a new ADO PC.00-01 with the default template PC_PC_LSA like shown below:

PC.00-01

PC.00-01

I set PC_DESTINATION_TREE to CONSOLIDATION_C0 and PC_SOURCE_ADOS_ATTRID to the C0#SRC01 created above.

The next attribute, PC_TIME_INTERVALS, controls which time intervals of pricing data of the underlying ADOs are considered for usage in price consolidation.

I set this up as follows:

Time intervals

PC time intervals

Every day of the week except weekends is considered valid.

Next, I set up the attribute PC_PRICE_RULE. This is a compound with the following structure and content:

Price consolidation rules

PC rules

Let me walk through each block:

  • Option - Contains options/settings to configure the price consolidation process
    • AllRecords - All records within the specified time intervals (see above) are returned. Per default only the last record is returned.
    • GenerateAlwaysRecord - To always generate a record, whether the source has a price for that day or not.
  • Definition - this block contains:
    • A filter elt(symbol, [0, 1]) == 'BB' to show I am only interested in BB sources.
    • These are accessible via the handle bb defined to the right of the filter.
    • I am interested in timeseries of these BB ADOs from their BLOOMBERG_DATA tree.
    • Values for the attribute BB_PR005 are accessible via the handle bb_last.
  • Begin
    • There is a log statement to say which ADO we are consolidating prices for. This log can be found in $AC_WORKDIR/log/PC.log.
    • Then I define a global variable bb_ado as first(get_ados('bb')), taking the first of the BB ADOs matching the filter in the Definition block above.
    • And then the ID of this BB ADO is printed to the log file as well.
  • PreConsolidation - is empty here
  • PriceRule
    • Notice how these rules are in the third column of the compound.
    • Apart from the log statements, you see how I get all prices via get_all(bb_ado, 'bb_last') and then reduce that to only the last non-$NA price received via last(strip(all_prices)).
    • Then this price is assigned to the attribute C0#TS001.
  • End - to mark the end of the price consolidation rule.

  Again, rather than you having to type this all out, you can download the price consolidaton rule here. From Excel, you can copy the relevant cells. In the AC Desktop, you double click the compound, then right-click and select Paste New Table. Voila!

Running price consolidation

Finally, I can run price consolidation. For this, I will need to have a C0 ADO with underlying BB ADO(s). From loading BB vendor data in the previous article, I have C0.ISS.1 such that the new custom attribute C0#SRC01 links to a BB source:

acdba@acbox:~/ac$ echo "attribute('C0.ISS.1', 'C0#SRC01');" | ac_evalform -
BB.00-2143

On BB.00-2143, I have stored timeseries in its BLOOMBERG_DATA tree:

BB timeseries

Based on this, I can now run price consolidation as follows:

acdba@acbox:~/ac$ echo "CONSOLIDATION_C0,C0.ISS.1,20180319,235959,PC.00-01" | update_prices.sh
+++INFO+++ 20180818_14:31:31 runparallel: Starting: runparallel -l250 -n5 ac_evalform -l - -f /home/acdba/ac/formulas/PriceConsolidation/pc_price_rule.fe
+++INFO+++ 20180818_14:31:31 runparallel: Finished runparallel
+++INFO+++ 20180818_14:31:31 @(#)amd_pr[7.3/$Revision: 48207 $]: 1 record updated, 0 failed: 1 row affected, 0 headers affected, 32b logged, 0 delta suspects, 0 messages

As you can see, I use update_prices.sh to trigger price consolidation. The input is a string of comma separated values: tree, C0 ADO, date, time and PC ADO. The script update_prices.sh accepts a variety of combinations of these input values. Please consult the Price Consolidation User Manual to learn.

But for now, you have a working example of price consolidation in your installation.

I can see the result of price consolidation in the AC Desktop:

PC Result

Thank you for reading.

Tags:
comments powered by Disqus