How to calculate daily average return of a group (sector, sub sector) and show in exploration

Dear afl experts,

I am new to amibroker. I am trying to calculate the daily average return of a group (sector, sub sector) and display the result in Exploration. As I understand, Exploration is a querry tool like sql server and sql server, for example, does this kind of calculation quite easily. However, I find it pretty hard to do with exploration. Could you please help me with this!!! Thank you very much.

Have you read "How to create own exploration"?
Then please look at AFL function list.


You just need three lines of code to get average of multiple values in Exploration.

Filter = Status("lastbarinrange");
AddSummaryRows(2, 1.2);
AddColumn(ROC(C,1), "return", 1.2);

Choose your symbol list
15
11
Understanding categories
https://www.amibroker.com/guide/h_categories.html
https://www.amibroker.com/guide/w_categories.html


In analysis settings choose periodicity
13
Then hit Explore to get final result:
12


If you want symbols to be ensured using same date (e.g. if some symbol(s) of list have the set date missing)
then enable pad&align in analysis setting and set your reference symbol (having available data)
17


You may set different end date to get value for different day
13


Now...
If on the other hand you want to get average as ARRAY then you have to do two analysis steps with more code.

First you have to SCAN on your filtered list (see first two pictures above)
And with Pad&align enabled to some reference symbol of your database (having enough data).
17

Then after scan being finished to get result output you have to click Explore again (but this time on some single symbol -> see Apply to: Current of analysis tool bar).

So here is that code for both procedures

/// first create average return ARRAY via SCAN 
/// set symbol list in Analysis Filter
/// and enable pad&align
/// set Periodicity in analysis General settings
/// @link https://forum.amibroker.com/t/how-to-calculate-daily-average-return-of-a-group-sector-sub-sector-and-show-in-exploration/18174
symbol_name = "~AverageSectorReturn";
if ( Status( "action" ) == actionScan ) {
    /// @link https://www.amibroker.com/guide/afl/staticvaradd.html
    if( Status( "stocknum" ) == 0 ) {
        // remove any earier composite values
        StaticVarRemove( symbol_name );
        StaticVarRemove( "~cnt" );
    }
    StaticVarAdd( symbol_name, ROC( C, 1 ), True, persist = False );
    StaticVarAdd( "~cnt", 1, True, persist );
    Buy = 0;
}

// Then afterwards apply Exploration on 
// some single symbol of Apply to: Current setting
if ( GetOption("ApplyTo") == 1 ) {
	Filter = 1;
	Avg_return = Nz(StaticVarGet(symbol_name) / StaticVarGet("~cnt"));
	AddColumn(Avg_return, symbol_name, 1.2);
} else 
	if ( Status( "action" ) == actionExplore )
		Error("Set 'Apply to' to Current!");

Exploration result applied on pad&align index
11

9 Likes

Dear @fxshrat,

Thank you so much for your detail comments. It is very clear that I should spend sometime to start with AB from the BGN. Actually, your reply helps to solve my another difficulty while my origin question is generally about to create something like a pivot table summary for the eod performance of customized groups.

I am so sorry for my unclear question so I attach the picture of my expected display exploration in the pix below. For the summary columns, I just do manual calculation and display in exploration just for an example. Please give me a hand for this issue. Thank you once again.

_SECTION_BEGIN("Daily return by customized group/ EOD performance summary of a customized group");

Buy = C > 1 ;
Sell = C > 10^10;    

Filter = Buy ;

//Just an example of a customized group
AddTextColumn( FullName(), "A customized group (Just an example)", formatChar,-1,-1, 255  )		;

//Daily Return 
DR = 100*(C - Ref(C,-1))/Ref(C,-1)																;
AddColumn(DR, "EOD Return(%)" , 1.2, -1, -1, 116)			;

//External calculation and manual summary column add for group of "Aerospace" - Just for an example display
AddColumn(IIf(FullName() == "Aerospace", 6, 0), "Group Member Count" , 1, -1, -1, 136)			;
AddColumn(IIf(FullName() == "Aerospace", 3.3, 0), "Group Average EOD Return" , 1.2, -1, -1, 156);
AddColumn(IIf(FullName() == "Aerospace", 29.79, 0), "Group Max EOD Return" , 1.2, -1, -1, 136)	;
AddColumn(IIf(FullName() == "Aerospace", -7.76, 0), "Group Min EOD Return" , 1.2, -1, -1, 136)	;

SetSortColumns( 3, 1 );

_SECTION_END();

Exploration%20prob%20display

@longnh if you are looking to identify sectors or industry membership, there are many different classifications. For example in this post you will see how each stock can be in a different group and how to identify that group.

And BTW, your calculation for daily return can be simplified.

DR = 100*(C - Ref(C,-1))/Ref(C,-1)

Is the same as ROC(C, 1)

And if you are looking to build composite's of returns for a Watch List or Sector etc you should familiarize yourself with StaticVarAdd (as @fxshrat has done above) or AddToComposite.

https://www.amibroker.com/guide/afl/staticvaradd.html
https://www.amibroker.com/guide/afl/addtocomposite.html

There are many examples here on the forum. Good luck and welcome to AmiBroker!

1 Like

Hi @portfoliobuilder,

I have done a quick tour through the forum about StaticVarAdd function. It seems to be very powerful and is also related to many problems which were asked here. Thank you very much for your comments.

@portfoliobuilder,

he means to output data in form of pivot table in Explorer similar to Excel.


@longnh,
Yes, it is possible to create Excel like pivot table output as proven here.

So I scanned your picture of explorer result via OCR to get data as CSV.

Ticker 	A customized group	EOD Return	Group Member Count	Group Average EOD Return	Group Max EOD Return	Group Min EOD Return
ARNC	Aerospace	29.79	6	3.3	29.79	-7.76
BA	Aerospace	1.01	6	3.3	29.79	-7.76
HWM	Aerospace	0.32	6	3.3	29.79	-7.76
RTX	Aerospace	-7.76	6	3.3	29.79	-7.76
TDG	Aerospace	-4.98	6	3.3	29.79	-7.76
TXT	Aerospace	1.43	6	3.3	29.79	-7.76
AAL	Airlines	-6.66	0	0	0	0
ALK	Airlines	0.12	0	0	0	0
DAL	Airlines	-0.88	0	0	0	0
LUV	Airlines	-3.05	0	0	0	0
UAL	Airlines	-2.26	0	0	0	0
GPS	Apparel Retailers	2.73	0	0	0	0
JWN	Apparel Retailers	-0.99	0	0	0	0
KSS	Apparel Retailers	-1.71	0	0	0	0
LB	Apparel Retailers	11.72	0	0	0	0
LULU	Apparel Retailers	-2.29	0	0	0	0
ROST	Apparel Retailers	-5.86	0	0	0	0
TJX	Apparel Retailers	-6.46	0	0	0	0
BEN	Asset Managers	-2.8	0	0	0	0
BK	Asset Managers	0.33	0	0	0	0
BLK	Asset Managers	-1.98	0	0	0	0
IVZ	Asset Managers	-4.99	0	0	0	0
NTRS	Asset Managers	0.14	0	0	0	0
STT	Asset Managers	-0.5	0	0	0	0
TROW	Asset Managers	-2.01	0	0	0	0
APTV	Auto Parts	-5.04	0	0	0	0
BWA	Auto Parts	-4.44	0	0	0	0
GPC	Auto Parts	-5.11	0	0	0	0
LKQ	Auto Parts	-6.27	0	0	0	0
F	Automobiles	-2.75	0	0	0	0
GM	Automobiles	-0.82	0	0	0	0
HOG	Automobiles	-6.58	0	0	0	0

and then loaded that data into my custom pivot table function.

And result for average return of that data looks like this in AmiBroker explorer

11

For comparison Excel pivot table
12

Same can be done for sum, min, max, etc...

Please give me a hand for this issue.

There may be other ways but I used matrix feature of AmiBroker to create output in such a way.

4 Likes

Hi @fxshrat,

Thank you a lot. You make me to understand more about what I actually should build to solve my problem. I mean the matrix summary table should include columns: customized group (sector, subsectors or any other kinds of classification) and rows: time series - Day, Week, or Month. In more details, it might have the format similar to the first table in the picture you did created in a previous post

GfORSFU

I just wonder whether you could give me some details about how to create it, an example script or the part I should read to create such a thing. I have gone through the forum as well as youtube, but I still find it a bit difficult to start. The only thing till now I guess I should apply is to use 2 loops through the customized group first, then through each ticker in the group.

Thank you very much !

1 Like

That functionality goes beyond free forum help. Took me a while to figure out so won't post publicly.


But you don't need to create pivot table to get your sector information.

Below is code update of post #2 of this thread.
Code outputs average, min, max of each sector.
Also you can output results per single date or as date list.
To change between modes you have to change mode variable in line 31.

If using mode = "single"; then you can use single analysis project file (since "Apply to" analysis setting remains the same one for scan and explore) to run simple batch with that project loaded before.

If using mode = "multi"; then you need two project files for batch run since exploration is applied on single symbol then and scan on list of symbols.

/// first create ARRAY per Sector via SCAN: 
/// set symbol list in Analysis Filter or use 'All symbols'
/// set Scan Range in toolbar
/// set Periodicity in analysis General settings
/// and enable Pad&align in analysis General settings
/// @link https://forum.amibroker.com/t/how-to-calculate-daily-average-return-of-a-group-sector-sub-sector-and-show-in-exploration/18174
/// by fxshrat@gmail.com
symbol_name = "~AverageSectorReturn";
id = SectorID();
rc = ROC(C,1); 
/////////////////////////////// SCAN START ////////////////////////////////////
if ( Status( "action" ) == actionScan ) {
    /// @link https://www.amibroker.com/guide/afl/staticvaradd.html
    if ( Status( "stocknum" ) == 0 ) {
        // remove any earier composite values
        StaticVarRemove( symbol_name+"*");
        StaticVarRemove( "~Max*");
        StaticVarRemove( "~Min*");
        StaticVarRemove( "~cnt*" );
    }       
    StaticVarAdd( symbol_name + id, rc, True, persist = False );
    StaticVarAdd( "~cnt" + id, 1, True, persist );
    _max = Max(Nz(StaticVarGet("~Max"+id), -1e9),rc);  
    StaticVarSet( "~Max" + id, _max, persist );
    _min = Min(Nz(StaticVarGet("~Min"+id), 1e9),rc);  
    StaticVarSet( "~Min" + id, _min, persist );    
    Buy = 0;
}
//////////////////////////////// SCAN END ///////////////////////////////////////

//////////////////////////// EXPLORATION START //////////////////////////////////
mode = "single";// set "multi" or "single" dates 

if ( mode == "multi" ) { // output results of multiple dates
	// Apply Exploration on some single symbol
	// Set 'Apply to:' -> 'Current' setting
	// Set to same 'Range' as of Scan
	// (update project files after changes if there are any)
	if ( GetOption("ApplyTo") == 1 ) {
		Filter = 1;
		SetSortColumns(-2);		
		for ( id = 1; id < 10; id++ ) {// iterate sectors
		// e.g. BasicMaterials,Conglomerates,ConsumerGoods,Financial,HealthCare,IndustrialGoods,Services,Technology,Utilities
			Avg_return = Nz(StaticVarGet(symbol_name + id) / StaticVarGet("~cnt" + id));
			AddColumn(Avg_return, "Avg. return of Sector"+id, 1.2);
			_max = StaticVarGet("~Max"+id);
			AddColumn(_max, "Max of Sector"+id, 1.2, IIf(_max<=0, colorDarkRed, -1));
			_min = StaticVarGet("~Min"+id);
			AddColumn(_min, "Min of Sector"+id, 1.2, IIf(_min<=0, colorDarkRed, -1));
		}
	} else 
		if ( Status( "action" ) == actionExplore )
			Error("Set 'Apply to' to Current!");
} else {
	// output values per single date 
	// set 'Apply to:' to same setting as of SCAN
	// to apply on different date set to 'From-to' and change end date
	// (update project file after changes if there are any)
	Filter = Status("lastbarinrange");
	SetSortColumns(3);
	AddTextColumn(SectorID(1), "Sector", 1);
	AddColumn(rc, "Return", 1.2, IIf(rc<=0, colorDarkRed, -1) );
	Avg_return = Nz(StaticVarGet(symbol_name + id) / StaticVarGet("~cnt" + id));
	AddColumn(Avg_return, symbol_name, 1.2, IIf(Avg_return<=0, colorDarkRed, -1));
	_max = StaticVarGet("~Max"+id);
	AddColumn(_max, "Max of Sector", 1.2, IIf(_max<=0, colorDarkRed, -1));
	_min = StaticVarGet("~Min"+id);
	AddColumn(_min, "Min of Sector", 1.2, IIf(_min<=0, colorDarkRed, -1));
}

Here is sample output for mode = "single";
As you can see results are correct there.

12


Here is batch execution for mode = "single"; (as you can see just one project file)
6

6 Likes

Dear @fxshrat,

I would like to say very big thank to you. It is not a regular help (ask and answer the question), it does inspire me to optimize the usage of amibroker and to solve most of problems during trading life within this AB environment only !!! :grin:

3 Likes