Analytics Functions Example 1: Splits

Historical Closing Prices Adjusting for Splits

The following code snippets demonstrate how to adjust the historical closing prices of IBM stocks for the effect of splits.

First, in the following C API code snippet, assume that &sec is an object handle of an instance of a class representing the equity with symbol "IBM":

 
    mco_seq_iterator_t split;
    mco_seq_iterator_t prd_split_factor;
    mco_seq_iterator_t price_adjustment;
    mco_seq_iterator_t trade_date;
    mco_seq_iterator_t split_date;
    mco_seq_iterator_t closing_price;
    mco_seq_iterator_t adjusted_price;
    Security_SplitFactor_iterator(&sec, &trade_date);
    Security_SplitFactor_iterator(&sec, &split_date);
    Security_SplitFactor_iterator(&sec, &closing_price);
     
    rc = mco_seq_cum_agg_prd_float(&prd_split_factor, &split);
    mco_seq_reverse_double(&rev_prd_split_factor, &prd_split_factor);
    mco_seq_stretch_uint4_double(&price_adjustment, &trade_date, &split_date, &rev_prd_split_factor,1.0);
    mco_seq_mul_double(&adjusted_price, &closing_price, &price_adjustment);
     

Note that sequence iterators trade_date, split_date and closing_price are instantiated by application level function Security_SplitFactor_iterator() (not shown) then the “pipeline” of statistical functions mco_seq_cum_agg_prd_float(), mco_seq_reverse_double(), mco_seq_stretch_uint4_double() and mco_seq_mul_double() are called to perform operations on the sequences to produce the resulting sequence adjusted_price.

The same operations can be accomplished through SQL with the following select statement:

 
    SELECT seq_mul(ClosePrice, seq_stretch(TradeDate, SplitDate,
        seq_reverse(seq_cum_agg_prd(SplitFactor)))) as AdjustedClose
    FROM Security
    WHERE Security.Symbol = ‘IBM’
     

Note the relationship between the functions in SQL compared to those in the C API snippet: The inner-most nested function in the select statement seq_cum_agg_prd() is the first sequence function called in the C API "pipeline"; the next most nested function seq_reverse() is the second function called in the C API "pipeline", and so on.

Note also that the declaration and initialization of the iterator variables is carried out within the SQL API, freeing the application from those implementation details.

To run this example

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    x 1