How to get each quarter closing price per year with in the date range

[_Bobby]

Hello All,
I have been amazed by looking at some excellent AFL's in this forum by many talented experts.
This is my first post in the forum and looking for some help.
I would really appreciate it if anyone could provide a sample code (snippet) or point me in right for the following requirement.

1. Exploration AFL code that allows user to pick the date range
2. Filter equals 1 entry per ticker/symbol per year between #1 date range ( i.e show entry for either year-end i.e. 31DecYYYY or if current year then Previous Month End Date)
3. Include columns:

• Default Exploration columns
• 52WkLow/High columns: YYYY-52WL, YYYY-52WH
• Q1/Q2/Q3/Q4 Closing Price (CP) per Year: YYYY-Q1CP, YYYY-Q2CP, YYYY-Q3CP, YYYY-Q4CP.

Please note: YYYY is for each year i.e. if the date range is 01-Jan-2019 to 30-Oct-2020 then columns would be Y2019-52WL, Y2019-52WH, Y2020-52WL, Y2020-52WH, Y2019-Q1CP, Y2019-Q2CP, Y2019-Q3CP, Y2019-Q4CP, Y2020-Q1CP, Y2020-Q2CP, Y2020-Q3CP, Y2020-Q4CP

System Info: Amibroker 6.35.1 | Data: EOD Equity (US/UK)

Any help would be much appreciated.
Cheers
Bobby

[snoopy.pa30]

@_Bobby, Welcome to the forum.

You will find that you get best results if you show what you have tried to do yourself.

You might want to review "How to use this site" and "How to ask a good question".

Here your question is reasonable, but you have not shown what you have tried, and what part you are not able to get working on your own. In general - post your code (use the code blocks "</>"), and tell us what part isn't working for you.

[beppe]

@_Bobby, to achieve what you have in mind, one way is to use the SparseCompress() function.

Iterate over the years in your interval using the year loop variable to create the "only_when" condition and apply it to the desired price array (there are multiple ways to figure out the required price from the Low and High compressed arrays).
For the quarters, make another loop, using the Close array, creating a new "only_when" condition based on year AND month (in your case, using only months 3, 6, 9, 12). In this case, the closing price is the last bar of the (unsorted) compressed array.

Each time you find the desired value, you should add it to your exploration (creating the "name" of the column dynamically using the StrFormat() function).
Apply the exploration to one recent bar/day only.

The last few post of this old thread (ignore the Lookup() suggestion) may help you to figure out the above hints.

[_Bobby]

Thanks for looking into my requirement and sharing your thoughts.
Please find below my attempt at doing the above. As this is my first AFL in the forum, could you please review if I am doing it correctly and also please advise how to add columns (Q1, Q2, etc) to this result?

_SECTION_BEGIN( "how-to-get-each-quarter-closing-price-per-year-with-in-the-date-range" );

tradingDays = 252; // trading days in a year

bi = BarIndex();
fbr = Status( "firstbarinrange" );
lbr = Status( "lastbarinrange" );
dt = DateTime();

startDate = ValueWhen( fbr, dt );
endDate = ValueWhen( lbr, dt );

high1Y = HHV( H, 1 * tradingDays );
high1YDate = ValueWhen( H == high1Y, DateTime(), 1 );

high2Y = HHV( H, 2 * tradingDays );
high2YDate = ValueWhen( H == high2Y, DateTime(), 1 );

low1Y = LLV( L, 1 * tradingDays );
low1YDate = ValueWhen( L == low1Y, DateTime(), 1 );

low2Y = LLV( L, 2 * tradingDays );
low2YDate = ValueWhen( L == low2Y, DateTime(), 1 );

currentY = Year();
lookBackY1 = Year() - 1 ;

monthEnd    = Month() != Ref( Month(), 1 );
quarterEnd  = Month()%3  == 0 AND MonthEnd;

_SECTION_END();

///////////////////////////////////////////////////////////////////////////////////////////
// EXPLORATION CODE 																	 //
///////////////////////////////////////////////////////////////////////////////////////////
if( Status( "action" ) == actionExplore )
{
    filterCondition = lbr;
    Filter = filterCondition; //Filter equals 1 entry per ticker/symbol

    AddColumn( Close, "Close", format = 1.2 );
    AddColumn( IIf( filterCondition, 'T', 'F' ), "FilterCondition", formatChar, IIf( filterCondition, colorGreen, colorOrange ) );

    AddColumn( high1Y, NumToStr(currentY,1)+"-52WH" );
    AddColumn( high1YDate, NumToStr(currentY,1)+"-52WH Date", formatdateTime );

    AddColumn( low1Y,NumToStr(currentY,1)+"-52WL" ); //YYYY-52WL
    AddColumn( low1YDate, NumToStr(currentY,1)+"-52WL Date", formatdateTime );
    
    AddColumn( high2Y, NumToStr(lookBackY1,1)+"-52WH" );
    AddColumn( high2YDate, NumToStr(lookBackY1,1)+"-52WH Date", formatdateTime );
    
    AddColumn( low2Y, NumToStr(lookBackY1,1)+"-52WL" );
    AddColumn( low2YDate, NumToStr(lookBackY1,1)+"-52WL Date", formatdateTime );

    AddColumn( startDate, "startDate", formatdateTime );
    AddColumn( endDate, "endDate", formatdateTime );
}

Cheers
Bobby

[_Bobby]

Thanks, Beppe for your suggestion. It was very helpful. However, I still struggling to get the closing price of the last trading day of the quarter. Please see my attempt in the code below.

yearList = StrFormat("%g,%g",currentY, lookBackY1); // any comma separated list
    for( i = 0; ( sYear = StrTrim(StrExtract( yearList, i )," ") ) != ""; i++ )
	{	
                //q1s = Lookup(bi, StrToDateTime(sYear+"-01-01"),  1);
		//q1e = Lookup(bi, StrToDateTime(sYear+"-03-31"), -1);
		//q2s = Lookup(bi, StrToDateTime(sYear+"-04-01"),  1);
		//q2e = Lookup(bi, StrToDateTime(sYear+"-06-30"), -1);
		//q3s = Lookup(bi, StrToDateTime(sYear+"-07-01"),  1);
		//q3e = Lookup(bi, StrToDateTime(sYear+"-09-30"), -1);
		//q4s = Lookup(bi, StrToDateTime(sYear+"-10-01"),  1);
		//q4e = Lookup(bi, StrToDateTime(sYear+"-12-31"), -1);
	  
		//q1CP = Sort(C, q1s, q1e);
		//q2CP = Sort(C, q2s, q2e);
		//q3CP = Sort(C, q3s, q3e);
		//q4CP = Sort(C, q4s, q4e);

          //AddTextColumn( q1CP, sYear+"-Q1CP",1.2); // YYYY-Q1CP  - ERROR   
	  AddTextColumn( sYear, sYear+"-Q1CP",1.2); // YYYY-Q1CP 
	  AddTextColumn( sYear, sYear+"-Q2CP",1.2); // YYYY-Q2CP 
	  AddTextColumn( sYear, sYear+"-Q3CP",1.2); // YYYY-Q3CP 
	  AddTextColumn( sYear, sYear+"-Q4CP",1.2); // YYYY-Q4CP 
	}

Any help would be much appreciated.
Cheers
Bobby

[_Bobby]

Hi Beppe,

I tried the second approach as well as per your recommendation but getting incorrect results.

 
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;
}

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

function getCP( whenCondition )
{
    x = SparseCompress( whenCondition, Close ); // compact sparse data
    y = C; // regular calculation
    y = SparseExpand( whenCondition, y ); // expand sparse data
    return SelectedValue( y );
}

yearList = StrFormat("%g,%g",currentY, lookBackY1); // any comma separated list
for( i = 0; ( sYear = StrTrim(StrExtract( yearList, i )," ") ) != ""; i++ )
{		
	whenYQ1 = (( Month() % 3 ) AND (Year() == StrToNum(sYear))) == 0; 
	whenYQ2 = (( Month() % 6 ) AND (Year() == StrToNum(sYear))) == 0; 
	whenYQ3 = (( Month() % 9 ) AND (Year() == StrToNum(sYear))) == 0; 
	whenYQ4 = (( Month() % 12 ) AND (Year() == StrToNum(sYear))) == 0; 

	q1CP = getCP(whenYQ1);
	q2CP = getCP(whenYQ2);
	q3CP = getCP(whenYQ3);
	q4CP = getCP(whenYQ4);
	
	AddColumn( q1CP, sYear+"-Q1CP",1.2); // YYYY-Q1CP 
	AddColumn( q2CP, sYear+"-Q2CP",1.2); // YYYY-Q2CP 
	AddColumn( q3CP, sYear+"-Q3CP",1.2); // YYYY-Q3CP 
	AddColumn( q4CP, sYear+"-Q4CP",1.2); // YYYY-Q4CP 
}

Any help would be much appreciated.
Cheers
Bobby

[fxshrat]

For example this one is incorrect

@_Bobby

function getCP( whenCondition )
{
    x = SparseCompress( whenCondition, Close ); // compact sparse data
    y = C; // regular calculation
    y = SparseExpand( whenCondition, y ); // expand sparse data
    return SelectedValue( y );
}

Replace

y = C; // regular calculation
y = SparseExpand( whenCondition, y ); // expand sparse data

by

y = SparseExpand( whenCondition, x ); // expand sparse data

Also move loop invariant code outside of loop (see Month() and Year()) and don't repeat function calls (again see Month() and Year() functions). Store those to variables before loop.

Also I do not understand why you are using string list if you look for analysis range.
So simply extract year from start and end of date range.

function getCP( whenCondition )
{
	x = SparseCompress( whenCondition, Close ); // compact sparse data
	y = SparseExpand( whenCondition, x ); // expand sparse data
	return SelectedValue(ValueWhen(whenCondition, y));
}

/// code fix at
/// @link https://forum.amibroker.com/t/how-to-get-each-quarter-closing-price-per-year-with-in-the-date-range/22223/7
start_yr = DateTimeConvert(8, DateTimeConvert(2, Status("rangefromdate")));
end_yr = DateTimeConvert(8, DateTimeConvert(2, Status("rangetodate")));
mth = Month();
yr = Year();
Filter = Status("lastbarinrange");
for ( i = start_yr; i <= end_yr; i++ ){		
	for ( n = 3, j = 1; n <= 12; n+=3 ) {	
		when = mth == n AND yr == i;
		qCP = getCP(when);
		AddColumn( qCP, ""+i+"-Q"+(j++)+"CP",1.2); // YYYY-QnCP 
	}
}

Besides you can write short using

start_yr = DateTimeConvert(8, DateTimeConvert(2, Status("rangefromdate")));
end_yr = DateTimeConvert(8, DateTimeConvert(2, Status("rangetodate")));
mth = Month();
yr = Year();

Filter = Status("lastbarinrange");
for ( i = start_yr; i <= end_yr; i++ ){		
	for ( n = 3, j = 1; n <= 12; n+=3 ) {	
		when = mth == n AND yr == i;
		qCP = SelectedValue(ValueWhen(when, C));
		AddColumn( qCP, ""+i+"-Q"+(j++)+"CP",1.2); // YYYY-Q1CP 
	}
}

[beppe]

For the Close prices there is no need to expand back the compressed array:

function getCP( whenCondition )
{
    x = SparseCompress( whenCondition, Close ); // compact sparse data 
    // the compressed array may be {EMPTY} and will return an {EMPTY} value
    return ( x[BarCount-1] );
}

[fxshrat]

@beppe

For the Close prices there is no need to expand back the compressed array:

function getCP( whenCondition )
{
    x = SparseCompress( whenCondition, Close ); // compact sparse data 
    // the compressed array may be {EMPTY} and will return an {EMPTY} value
    return ( x[BarCount-1] );
}

There is no need for Sparse and extra custom function at all... See upper post.... just use ValueWhen.

@fxshrat

when = mth == n AND yr == i;
qCP = SelectedValue(ValueWhen(when, C));

[_Bobby]

Thanks, fxshrat for your prompt response and help.
Your both code fix worked perfectly however I really liked your short syntax.
Much appreciated your time and effort.
Cheers
Bobby

[_Bobby]

Thanks, Beppe for your help.