How to find the Summary of a column in exploration

Hello,

Here I am trying to make a summary of the elements of one column without to use AddSummaryRows()
I cannot find what I am doing wrong here and is not possible to find Summary of the Rows.
Any help appreciate, thank you.

if( Status( "Action" ) == actionExplore )
{

    Filter = Status( "lastbarinrange" );

    uptest = 0;

// find the percent per Day for X days, then find the total of positive percent per column (per day) 

    for( i = 1; i < 5 ; i++ )
    {
        perc = Ref( ROC( C, 1 ), -i );   // _trace( "#  perc"+i + ", = " + perc   );

        // try to find the Sum of the column using a counter with varSet()
        counterUp =   IIf( perc > 0, VarSet( "SVup" + i, VarGet( "SVup" + i ) + 1 ), 0 );

        // Second attempt try to find the Sum of the column with a counter uptest++
        Addtest = IIf( perc > 0, uptest++, 0 );

        _trace( "#  " + i + ", uptest= " + uptest  + ",  counterUp=  " + counterUp  + ", " + Name()  + ", Addtest= " + Addtest );

        AddColumn( perc, "-" + i + "% ", 1.2 , IIf( perc < 0, colorRed, colorBlack ) );
        AddColumn( counterUp, "Up" + i, 1 );

    }

    AddSummaryRows( 1, 1 , 4, 6, 8, 10, 12 );  // 
}

image_2021_01_24T21_57_55_561Z

@portfoliobuilder,

You were once again using StaticvarAdd() incorrectly.
It should not be used that way.

if ( Status("action") == actionScan ) {
	if( Status( "stocknum" ) == 0 )
	{
		// cleanup variables created in previous runs (if any)
		StaticVarRemove( "~above*" );   
	}
	StaticVarAdd( "~above", ROC( C, 1 ) > 0 );
}
	
svABOVE = StaticVarGet( "~above" );

Edit:
Just seen that post has been deleted...

Anyway.. StaticVarAdd should not be used inside Symbollist loop calling SetForeign.
There is no looping shown in manual.

@fxshrat thanks, I had just remembered the repeated messages by @Tomasz to use SCAN and avoid the use of SetForeign. So I before you posted I removed my (working) example.

I must admit that in my small Explorations while working on debugging code that I find the "abuse" of SetForeign within that context of creating a simple Explore code that my AmiBroker runs very fast and I don't "see" the efficiency gains of running the SCAN, followed after by an EXPLORE to create the desired output. The explorations work really well.

I readily admit I have no programming background and perhaps due to this I don't have a CSC professor's voice ringing in my ears about proper and efficient code writing.

But I will attempt to avoid posting bad habits on the forum and improperly teaching other users.

Not sure why not accepting facts:

Q.E.D.

2 Likes

Agree with you and fully accept the facts. And thanks for the Q.E.D.! I have not seen or used that in a long time and brought a huge smile to my face. It is late at night and several beers into my evening so I appreciate your patience and posts tonight. :beer:

Hi Larry
Thank you for your reply.
I did try StaticVarAdd() but I didn’t think to go through the watch list, I was thinking will be an easier way.
Inneed I have the results correct with the code below, but honest I was thinking will be a better way.
Thank you very match again.

wlnum = GetOption( "FilterIncludeWatchlist" );
List = CategoryGetSymbols( categoryWatchlist, wlnum );

if( Status( "stocknum" ) == 0 )
{
    // cleanup variables created in previous runs (if any)
    StaticVarRemove( "~above*" );

 for( i = 1; i < 5 ; i++ )
{
    for( n = 0; ( Symbol = StrExtract( List, n ) )  != "";  n++ )
    {
        SetForeign( symbol );

        // above = ROC( C, 1 ) >= 0;
        // StaticVarAdd( "~above", above );

         above = Ref( ROC( C, 1 ), -i ) >0 ; // << we need x previous days  
        StaticVarAdd( "~above"+i, above );   

        RestorePriceArrays();
    }
 }
}
for( i = 1; i < 5 ; i++ )
{
AddColumn( StaticVarGet( "~above"+i), "Up" + i, 1 );
}

 Filter = Status( "stocknum" ) == 0 ;

image

Oh man, this is hilarious...
It's like talking to walls.
Unbelievable.

1 Like

@chrismet,

Did you not see this!

@chrismet remember @Tomasz advice, "SetForeign hundreds or thousands of times that makes your code slow. You should not be doing that.
You should be creating composites (that include StaticVarAdd) in a separate scan that is run ONCE ONLY to create composite. Once composite is created it can be used many times without calculating it over and over again (which is big no-no). "

I in no way disagree with @fxshrat or @Tomasz. But I am posting my observations.

I should stop posting on this thread before the computer science guys jump all over the idea of inefficient code writing. But I am now "looped" (an alcohol consumption term in North America) as my favorite North American football team lost and I am drowning my sorrows while posting on this forum - probably not a wise decision.

You write that you were hoping for a "better way" and by that I guess you mean less code lines. But StaticVarADD is pretty powerful and not complex.

If you want some day we can PM/Skype/Zoom and I will show you three methods for calculating a simple composite example. First using AddToComposite, second using StaticVarAdd "properly", and then thirdly using StaticVarAdd "improperly". They produce identical results and on small watch lists with limited number of bars can each be executed in milliseconds.

I have no doubt that a large WL with the Scan/Explore run over many bars of data will reveal tremendous efficiencies of the proper use of StaticVarAdd and the harmful effects of "abuse" of SetForeign.

Good luck!

1 Like

Thank you very match again
Yes I read that many times, that’s why I said should be a better way. I will delete/ correct also my previous post but i need the proper way how to write this exploration. I do not mind if the result will be based to matrix() or based to addrow() or to statickvar.
I already try 2 days now and I canot find the way how to.
Any good logic is welcome. I don’t like to give up, for that reason I ask help in the forum
Thank you

Wall and not worse ... :slightly_smiling_face: my friend, you are right.

New updated afl without to use of SetForeign
The question now is: how can i find the last value of a staticvar?

if( Status( "Action" ) == actionExplore )
{
	Filter = Status( "lastbarinrange" );

    if( Status( "stocknum" ) == 0 )
    {
        // cleanup variables created in previous runs (if any)
        StaticVarRemove( "~above*" );
    }

    for( i = 1; i < 5 ; i++ )
    {
        above = Ref( ROC( C, 1 ), -i ) > 0 ; // << we need x previous days
        StaticVarAdd( "~above" + i, above );
        AddColumn( StaticVarGet( "~above"+i), "Up" + i, 1 );
    }

} 

image_2021_01_25T18_47_25_355Z

@chrismet you could create the composite using StaticVarAdd by running a SCAN first, then if you want to see your values run an Explore.

Add your loops to improve the flexibility of this code,

 if( Status( "action" ) == actionScan )
{
    if( Status( "stocknum" ) == 0 )
    {
        // cleanup variables created in previous runs (if any)
        StaticVarRemove( "~above*" );
    }

    StaticVarAdd( "~above1", ROC( C, 1 ) > 0 );
    StaticVarAdd( "~above2", ROC( C, 2 ) > 0 );
    StaticVarAdd( "~above3", ROC( C, 3 ) > 0 );

}

svABOVE1 = StaticVarGet( "~above1" );
svABOVE2 = StaticVarGet( "~above2" );
svABOVE3 = StaticVarGet( "~above3" );
///////////////////////////////////////////////

Filter = 1;
AddColumn( ROC( C, 1 ), "ROC1", 1.2, colorDefault, IIf( ROC( C, 1 ) > 0, colorGreen, colorDefault ) );
AddColumn( svABOVE1, "# ROC(C,1)>0", 1.0 );

AddColumn( ROC( C, 2 ), "ROC2", 1.2, colorDefault, IIf( ROC( C, 2 ) > 0, colorGreen, colorDefault ) );
AddColumn( svABOVE2, "# ROC(C,2)>0", 1.0 );

AddColumn( ROC( C, 3 ), "ROC3", 1.2, colorDefault, IIf( ROC( C, 3 ) > 0, colorGreen, colorDefault ) );
AddColumn( svABOVE3, "# ROC(C,3)>0", 1.0 );

image

Of course if you are looking for a different sort of output you can change your Exploration filters to produce what pleases you, for example
image

Filter = Status("LastBarInRange") AND Status( "stocknum" ) == 0;
SetOption("NoDefaultColumns",True);
AddColumn(DateTime(), "Date", formatDateTime);
AddColumn( svABOVE1, "Total # ROC(C,1)>0", 1.0 );
AddColumn( svABOVE2, "Total # ROC(C,2)>0", 1.0 );
AddColumn( svABOVE3, "Total # ROC(C,3)>0", 1.0 );

Dear Larry, I appreciate your insistence on helping with the question I asked. I want to say a big thank you for that.
The same thanks I feel the need to say to any other gentleman who tried to see the question and contribute to its solution.

My question was if there is a solution without using static (StaticVarAdd ()). Because this process, especially when the number of shares you want to consider is large, it takes up a lot, up to too much memory. This is not good.

I would prefer (if possible) not to have a meter with StaticVarAdd but another meter, for example as in the first (original) post I posted and asked for public help. (.......... counter ++ or matrix () or varset () varget () ..........)

The reason is that I would not want to save (StaticVarAdd ()) something to use later. I just want to do a simple exploration and get the result.

In any case, with your help, and the rest, I reached the following result (I will put the code and an image), while closing, I would like to ask, what would be the best alternative that fits the title of thread? or else, what would be the shortest code one could follow?

/*
Uptrends, downtrends USA stocks and totals thereof (Uptrends, downtrends) separately for each day for xxx period (in the example we have it for 5 days).
First you do (click) Scan and when it is finished you click explore
*/

if( Status( "Action" ) == actionScan )
{
	Filter = Status( "lastbarinrange" );

    if( Status( "stocknum" ) == 0 )
    {
        // cleanup variables created in previous runs (if any)
        StaticVarRemove( "~above*" ); StaticVarRemove( "~below*" );
    }

    for( i = 1; i < 5 ; i++ )
    {
        above = Ref( ROC( C, 1 ), -i ) > 0 ; // << we need x previous days
        StaticVarAdd( "~above" + i, above );
        below = Ref( ROC( C, 1 ), -i ) < 0 ;
        StaticVarAdd( "~below" + i, below );
    }

}  

///////////////////////////////////////////
// outside of the first scan . Please press exploration to see the results 

if( Status( "Action" ) == actionExplore )
{
Filter = Status( "lastbarinrange" );
for( i = 1; i < 5 ; i++ )
    {
       perc = Ref( ROC( C, 1 ), -i ) ;
       AddColumn( perc, "-"+i+"d--"+StaticVarGet( "~above"+i)+ ","+StaticVarGet( "~below"+i), 1.2 , IIf( perc < 0, colorRed, IIf( perc > 0, colorGreen, colorBlack )) );
    }
}

image_2021_01_25T22_32_47_154Z

No, you can't use without static vars.
Also you do not need to store array here but just last value.
So you store just four values.
Also you do not need to run Scan and Explore.

Here is working code.
Choose watchlist then run Exploration.

num = GetOption("FilterIncludeWatchlist");
symlist = CategoryGetSymbols(categoryWatchlist, num);
sym_count = StrCount(symlist, ",")+1;

///@link https://www.amibroker.com/guide/afl/staticvaradd.html
///@link https://forum.amibroker.com/t/how-to-find-the-summary-of-a-column-in-exploration/23720/17
if ( Status("action") == actionExplore ) {
	if ( Status("stocknum") == 0 ) {
		StaticVarRemove("~above*");
		StaticVarRemove("rowCount");
	}  

	Filter = Status( "lastbarinrange" );
	SetSortColumns(1);

	for ( i = 1; i < 5; i++ ) {
		_perc = Ref(ROC(C, 1),-i);
		AddColumn(_perc, "-"+i+"%", 1.2, IIf(_perc <= 0, colorRed, -1));
		StaticVarAdd("~above"+i, LastValue(_perc) > 0);
	}
	StaticVarAdd("rowCount", 1);
	
	if (Nz(StaticVarGet("rowCount")) == sym_count) {			
		for( i = 1, str1=str2=""; i < 5; i++ ) {
			above = StaticVarGet("~above"+i);
			str1+= ""+above+"\t";
			str2+= ""+(sym_count-above)+"\t";
		}		
		AddRow("*SummaryRow Up*\t\t"+str1);
		AddRow("*SummaryRow Down*\t\t"+str2);
		Say("End of Exploration");
	}
}

25

6 Likes

@fxshrat, Excellent effort, thank you very much and a heart from me. I also want the black background in the analysis window. Good for the eyes, how is it done?

image

aa, good morning !!, I do not have this option, (v.6.28)

To point out and help those who want to use the solution of the dear @fxshrat, that if we want to explore the entire database and not only a Watchlist, the first two lines should be changed as follows.

//num = GetOption("FilterIncludeWatchlist");
//symlist = CategoryGetSymbols(categoryWatchlist, num);
symlist = CategoryGetSymbols(categoryAll, 0);

What else do you want / hope for, and what else will actually happen (for me I say). The really clever way that my friend @fxshrat thought works for Watchlists, but I estimate that rarely and on almost no basis, no one, will work, for the entire base (categoryAll). This is because the condition Nz (StaticVarGet ("rowCount")) == sym_count will very rarely apply, as shown in the attached image. Solutions are sought. Thus, virtually no result (sum) can be created by Exploration.

Debug_Exploration_All_Database