How to Query this data for Exploration Report?

AFL Programming
1

Below screenshot is scaled down version of how my actual data looks like.

I want to make an exploration Report for tickers with Sales Growth on QoQ basis.

So my Filter Condition will be Latest Qtr Sales (300) should be more than the Previous year's Corresponding Qtr (200) Sales.

Since Qtr Sales Array (Aux1) is not continuous data, how How to make the Filter Query in this case? Else I could have used Ref(Array, +-n);

image

2

This is just one possible solution with a caveat: see the note at the bottom of this post.
(Maybe someone else will provide a better alternative)

function SparseCompressEquiv( sparse_array, data_array )
{
    result = Null;

    j = BarCount - 1;

    for( i = BarCount - 1; i >= 0; i-- )
    {
        if( sparse_array[ i ] ) result[ j-- ] = data_array[ i ];
    }

    return result;
}

// ticker = Name(); // for debugging/ tracing - Not used here
sales = Aux1;
// mask = ( !IsNull( Sales ) AND( Sales > 0 ) ); // in case there are nulls in the used field
mask =  Sales > 0;
// In production replace the following with the SparseCompress function
// I used the "equiv" (pasted from the AB docs) here to help you  to figure out 
// its logic (that could be adapted if needed to other data structures like a Matrix)
lastQuarters = SparseCompressEquiv( mask, sales );
Filter = 1;
AddColumn( Aux1, "Aux1" );
AddColumn( Mask, "Sales > 0" );
AddColumn( lastQuarters, "Last Quarters" );
lastQuarterSales = lastQuarters[BarCount - 1];
prevYearQSales = lastQuarters[BarCount - 5];
AddRow( StrFormat( "Last quarter sales\t%f", lastQuarterSales ) );
AddRow( StrFormat( "Prev year sales\t%f", prevYearQSales ) );

// Uncomment the next line to actually filter if:
// lastQuarterSales >  prevYearQSales

// Filter = lastQuarterSales >  prevYearQSales;

Obviously, this exploration should be over a range period to include both the last quarter sales data and the previous year one (and no other sales data stored in the Aux1 field except at quarters end dates - the dates in the image I suppose are not where the sales numbers are actually stored, but simply to show that you have some “sparse” data).

VERY IMPORTANT: due to the way the SparseCompress function works, this solution is applicable as it is, only if the exploration includes the last bar (since the compressed values are squeezed at the end of resulting array).

A little more coding is needed to use a different end date and/or to take care of other data discrepancies (no sales data, no enough bars, etc.)

3

Thanks @beppe

SparseCompress squeezed data like you said. I rather need a solution where data remains as is ( ie scattered / sparsed ) still I can make the Filter Query.

4

It is very very simple, one liner:

// assuming that "missing" data is ZERO
data = ValueWhen( Aux1 != 0, Aux1 );
1 Like
5

@Tomasz

your one-liner is the perfect solution to fill the missing values in Aux1 (when zero) and get an array with the last "Latest Qtr Sales" number (as per his example) associated to every bar, but I do not understand how this could then be used easily to find the previous year quarter number (this was the original question)?

So my Filter Condition will be Latest Qtr Sales (300) should be more than the Previous year’s Corresponding Qtr (200) Sales.

The most obvious way that comes to my mind, is to traverse the original array backward and look for the 1st and 5th valid values and compare them.
If instead of the sparse original data (where the zero/null values act as separators of quarters) we will use the "filled" data array (using your code) we could do the same looking for value changes (but this would fail if, for any reason, two quarters in a row in the original array have the exactly the same value).

So, what is the optimal solution to the above problem, (supposing we could not use some fixed calendar rules)? I still have so much to learn here!

6

Previous year quarter number can be retrieved by simple Ref. There are 252 trading days per year so, Ref( data, -252/4)

7

Try this…

LatestQtrSales = ValueWhen( Aux1 != 0, Aux1 );
Previousyear = ValueWhen( Aux1 != 0, Aux1, 4 );
2 Likes
8

@awilson,

thanks (to @Tomasz too) . Such a simple solution!

I’m not sure if this is what @Neil81 was looking for (probably yes) but I learned something worthwhile. :wink: (For sure I need to spend more time exploring ValueWhen)

9

Example using SparseCompres() to calculate Buy signals:

rnd = mtRandomA( 1 );
sparse = IIf( rnd < 0.6, Null, rnd ) ;  // sparse array

index = SparseCompress( sparse, BarIndex() );

first = NullCount( index ) ;

buy = 0;
lookback = 1;
for( i = first + lookback ; i <  BarCount ; i ++ )
{
    j = index[i];
    k = index[i - lookback];
    buy [j] = sparse [j] > sparse[k];

}

Filter  = 1;
//Filter = sparse;
//Filter = Buy ;

AddColumn( Close, "close" );
AddColumn( sparse, "sparse" );
AddColumn( BarIndex(), "barindex" );
AddColumn( buy, "buy" );
2 Likes