String output in Exploration (i.e. percent sign)

hello
You can do that, with many ways as usally with powerfull amibroker AddTextColumn() or AddRow() or AddMultiTextColumn() or , or , or …but in here i will saw you using the AddTextColumn

Filter = 1;

// find the Percent for the last 10 bars
Percent = ROC( C, 10 );  
AddColumn(Percent, "Roc%");

//convert numeric value of NUMBER or ARRAY to string using NumToStr() function
PercentTxT= NumToStr(Percent,1.2)+"%";
AddTextColumn(PercentTxT, "Daily%");

// AddTextColumn using the 4th parameter textColor
Color_using_IIF = IIf( Percent >0, colorBlue, colorDefault );
AddTextColumn(PercentTxT, "Day%", 1.2,Color_using_IIF);

@Munichtrader i just notice that we have in few hours later a Dublicate Thread
is this a Dublicate Thread?

1 Like

@PanoS I was just trying to solve this too but with your code we only get the last value in an exploration, and I've tried using SelectedValue, LastValue etc but always with the same result.

Any idea how to get the bar by bar indicator value?
image

PS I also see this looks like a duplicate thread, but an interesting question.

@portfoliobuilder
hello Larry.
this is from the manual

Please note that AddTextColumn takes single string as a parameter, so you can only display text that does NOT vary on bar-by-bar basis.

You can do varying bar by bar string output only via AddRow() function.

2017-11-18_160411

2 Likes

If there is not an easier way, maybe this could be considered an area of improvement for future versions of AmiBroker (not a priority in my opinion since at least a work-around is possible as suggested by @fxshrat )

Anyway, instead of writing an additional function I imagine that it could implemented adding as 2 optional parameters to the existing AddColumn procedure that now is declared as:

AddColumn( array, name, format = 1.2, textColor = colorDefault, bkgndColor = colorDefault, width = -1, barchart = Null )

I think it would be nice to modify it as follow:

(Proposed modification to existing function)

AddColumn( array, name, format = 1.2, textColor = colorDefault, bkgndColor = colorDefault, width = -1, barchart = Null, prefix = "", postfix = "" )

In this way, it could be used to print the “%” (passing it as a postfix) but also other things (like “M” for millions, etc.). Prefixes may be useful to specify other text literals like for instance currencies symbols as"¥" “$”, “£”, etc.

As an alternative the same result could be achieved adding a single new (optional) parameter called “formatstr” that, if defined, will used to get the advance formatting similar to the one provided by the fprint / strformat functions - de facto ignoring/overriding the format parameter ).
But in this second case, a completely new AddFormattedColumn maybe a better and a cleaner solution:

(Non exisiting function)

AddFormattedColumn( array, name, formatstr = "%1.2f", textColor = colorDefault, bkgndColor = colorDefault, width = -1, barchart = Null )

Just my 2c, but it is better to let any decision to TJ!

1 Like

I'm correcting my upper statement...
It is also possible to achieve that via AddMultiTextColumn().

But (as it seems) there are two disadvantages over using AddRow():

  1. it seems to be (much) slower than using AddRow()... in my test around 20 times slower
  2. you can not choose via right and left aligning like via using AddRow since it is called MultiTextColumn and text columns are always left aligned.

11

1 Like

hello @beppe :smiley:

I just like to remind you that AmiBroker has also this ASC() Function for exploration

https://www.amibroker.com/guide/afl/asc.html

@beppe - as @fxshrat has just mentioned - don't forget about AddMultiTextColumn() which adds a text column to the exploration where the text is choosen based on array value:

https://www.amibroker.com/guide/afl/addmultitextcolumn.html

If the additional string character changes from bar to bar, dispalying it might be beneficial, but personally I don't see much sense in repeating (for example the % or "M" sign) over and over in every single line. For me one in the heading of the column is enough. For example:

Przechwytywanie

Besides sorting of columns is a very useful feature and in case of Text columns it is carried out in alphabetical order ...

Some additional threads which you and other users might find interesting:

1 Like

@PanoS, @Milosz

thanks for pointing that to me. So many functions to learn!

Milosz, the sorting issue indeed is an important one I overlooked.
I agree that repeating % or M seems not to be useful, but it could be in case you plan to export the exploration and you want to have the data already properly formatted for re-use it in some places without headers.

Anyway, in addition to the above suggestion an ever more flexible solution, if feasible, will be the addition of a new AddColumnUdf() function that instead of the format parameter will accept a user-defined function.
In this case, this user-defined function needs to be declared as accepting the “array” as input and to return a string. In this way, the user may be able to create any kind of fancy formatting for his columns., Sorting still an issue!

The solution to formatting fanciness has been named already... AddRow(). It is not a workaround but existing functionality.

And at minimum it is just two/three additional lines of code. AddRow() can be used for any kind of (flexible) table output. And it is not significantly slower than using just Add(Text)Column functions.

And also you are mistaken about sorting... if using AddRow then you have not sorting issues as it is sorted in the same way as if using column functions only! Just try yourself instead of assuming much.

11

3 Likes

Sorting becomes problematic in cases like below when prefixes (characters) are used before some values (but of course there are ways to deal with it):

Przechwytywanie

By the way, the above example shows, varying bar by bar string output using AddTextColumn(). In this case AddTextColumn() is used inside a loop iterating through n last bars. So as usual there are many different approaches to the same topic in AmiBroker.

1 Like

Just add "Db" or "Kb", "JFK", "Holy Cow" etc. in front of an entire series of array elements using AddRow and sort the column. You will see that it will still be getting sorted properly. In 99.9% of cases it is about adding things like percent, currency, measuring units and such to a value, see Excel. Do such things vary within a column? No. What major sense does it make to add single prefix in front of just a few values of a column. Please don't confuse people and come up with cases that only 0.01% of users would ever do. I repeat there are no sorting issues neither if adding single same prefix in front or behind a whole series of values or not adding anything to it using AddRow.
test

3 Likes

For me - it makes a lot of sense - it allows me to show that something interesting happened on some selected bar. For example one of the setups that I am looking for, occured 15 days (or minutes) ago on some stock, and a different setup - maybe confirming the first one - appeared on this stock 3 days (or minutes) ago. Whether these string characters are before or after the value, is less important. In this case each cell contains information about the % rate of change, relative volume ( compared to the average volume of this issue) and the name of the setup if it occurs on that bar. It's another way of visualising things on many different stocks on n consecutive bars/days/minutes at once. A very effective way of sifting through lots of (hundreds or thousands) issues. Especially if we take into account various sorting possibilities. Another example of some sorted results:

N consecutive bars

Repeating the same information in each line makes much less sense for me - I don't find it very useful - especially if it's 600 times "Holy Moly, what's going on here..." :wink:

Another reason, why I would prefer my solution in this case is the fact, that I can make use of colors - which currently is not possible with AddRow() - but I have suggested such feature in the Feedback Centre ( Issue # 2855). Colors provide the 3rd dimention - in this case convey relative volume information.

I try not to do that. AmiBroker is so versatile, that everyone can choose what suits him or her best. Showing another alternative might be useful for some users. Even if only one percent of users would find it useful, it might still be worth it :wink:

3 Likes

This thread is it not about what you want but about what the OP wants to achieve. What does he want to achieve? Exactly, adding (constant) percent sign behind cell value… nothing more nothing less. The ways on how to achieve that goal have been named. I’m out.

@fxshrat

thanks for showing me the light! :wink:

...if using AddRow then you have no sorting issues as it is sorted in the same way as if using column functions only! Just try yourself instead of assuming much.

I must tell you that just looking at the documentation of this function - still marked as "preliminary and its parameters are subject to change." - (and in particular at its code example) I would never have been able to imagine your suggested use here and elsewhere in this forum.

Anyway, I followed your suggestion and tried to make a code snippet to compare the standard way to build an exploration to an alternative one using the AddRow() function.
Here is the result of my first attempt at using the AddRow() function:

// Sample code - to compare different ways to build explorations
// Not validated - Maybe (and probably is) buggy!
// Explorations were only tested only in Daily interval

Version(6.20);

// Some utility code to improve the visual presentation of data
// I like empty cells instead of zeros: so in some arrays, I replace zeros with Null
// This return an array replacing values are <= 0 with Null - to be used with normal columns
function Nil(array) {
   return IIf(array > 0, array, Null);
}
// FormatStrGtz where Gtz means Greater Than Zero 
// Returns a formatted string only if value is > 0, 
// otherwise an empty string; to be used with AddRow
function StrFormatGtz(fmt, value) {
	if (value > 0) {
		s = StrFormat(fmt, value);
	} else {
		s = "";
	}
	return s;
}
// Using red/green colors to visually compare array1 to array2 
function colorize(a1, a2) {
	return iif(a1>a2, colorRed, iif(a1<a2, colorGreen, colorDefault));
}
// Add a ">" or "<" symbol before the value1 comparing to reference value2
function StrFormatCmp(fmt, value1, value2) {
	s = StrFormat(fmt, value1);
	if (value2 < value1) {
		s = StrFormat("< " + fmt, value1);
	} 
	if (value2 > value1) {
		s = StrFormat("> " + fmt, value1);
	} 
	return s;
}

   
// Preparing some data - actual logic not important here...
// Looking to find how many days passed since MA20 was above MA50 and 
// MA50 was above MA100. Price may be over/below any of the above MAs 
// Will use colors or simbols to show the relation
MA20 = MA(C, 20);
MA50 = MA(C, 50);
MA100 = MA(C, 100);
xcross = (MA20 > MA50) AND (MA50 > MA100);
xsince = BarsSince(!xcross);

// Change this param to (1) to use a standard exploration or
// (2) simulate it using the AddRow function and soem extra code
mode = Param("Exploration Mode 1 = Standard - 2 = AddRow", 1, 1, 2, 1);

filterCondition = V > MA(V, 10);

// Here we actually compare the 2 ways to do the exploration
if (mode == 1) {
	Filter = filterCondition;
	SetOption("NoDefaultColumns", false ); // Adding automatically Ticker/DateTime
	AddColumn(C, "Close");
	AddColumn(ROC(C,1), "%", 3.2); // unfortunately no special format exists "formatPct" yet....
	AddColumn(MA20, "MA20", 3.2, colorize(MA20, C));
	AddColumn(MA50, "MA50", 3.2, colorize(MA50, C));
	AddColumn(MA100, "MA100", 3.2, colorize(MA100, C));
	AddColumn(Nil(xcross), "Cross", 1.0);
	AddColumn(Nil(xsince), "Since", 3.0);
} else {
	Filter = 0;
	pct = ROC(C, 1);
	dt = DateTime();
	lbr = Status("lastbarinrange");
	fbr = Status("firstbarinrange");
	processing = False;
	SetOption("NoDefaultColumns", True );	
	AddColumn(Null, "Ticker");
	AddColumn(Null, "Date/Time");
	AddColumn(Null, "Close");
	AddColumn(Null, "%");	
	AddColumn(Null, "MA20");
	AddColumn(Null, "MA50");
	AddColumn(Null, "MA100");
	AddColumn(Null, "Cross");
	AddColumn(Null, "Since");
	AddColumn(Null, "#");
	SetSortColumns( 10, 2 ); // need to add this to get the same as defaut sorting 
		
	for( i = 0; i < BarCount; i++) {
		if (fbr[i] == 1) { // not sure this will work in any conditions due to multi-threading
			processing = True;
		}
		if (processing) {	
			if (filterCondition[i]) { // Apply theStatus("stocknum") actual filter
				sn = Status("stocknum");
				s = Name()                             + "\t" + // Ticker            
				DateTimeToStr(dt[i], 0)                + "\t" + // DateTime
				StrFormat("%3.2f", C[i])               + "\t" + // Close 
				StrFormat("%3.2f%%", pct[i])           + "\t" + // ROC(1)
				StrFormatCmp("%3.2f", MA20[i], C[i])   + "\t" + // MA20
				StrFormatCmp("%3.2f", MA50[i], C[i])   + "\t" + // MA50
				StrFormatCmp("%3.2f", MA100[i], C[i])  + "\t" + // MA100
				StrFormatGTz("%1.0f", xcross[i])       + "\t" + // Cross
				StrFormatGtz("%1.0f", xsince[i])       + "\t" + // BarSince
				StrFormat("%5.0f", sn);                // adding stocknum to sort in the same default order
				AddRow(s);
			}
			if (lbr[i] == 1) {
				processing = False;
				break;
			}
		}
	}
}

This code is to be loaded in a new analysis. Apply a filter to operate on a group of tickers (a watchlist is fine), select a date range (or the recent bar) and use the Explore button.
The code includes a Param. When such param is set to:

  1. the code is executed as a standard exploration (about 10 lines of actual code)
  2. the code is executed using the suggested AddRow function with the standard Filter variable set to 0

In the second case, I had to add some extra code to handle the selected exploration data range (and I wonder if this will actually work properly due to potential non-sequential execution due to multithreading - any hint in this direction will be truly appreciated).

Moreover, I add to use an extra conditional "if" using the same the logical filter that I assigned to "Filter" in the standard exploration.

In both cases, I employed some extra code (see it at the beginning of the code snippet) to improve the visual presentation of data.

Here a comparison of the resulting exploration reports:

immagine

In the second one, no colors, no way to set column width in code, but the Percentage Sign is properly appended to the percentage change value, and you are absolutely right SORTING works well even with prefixes (like I did with the "> " and "< " in the MAs columns). I think this is some brilliant coding by TJ!.

Is the second solution worthwhile the extra trouble? Does it work properly as coded here or there are some bugs to fix and/or improvments to be done?
I welcome any suggestion since this is completely new territory for me!

2 Likes

@beppe,

OK, observations...

  1. First of all, as for...

there is a Status function code called "barsinrange". So simply use that one instead of what you have made above.

bir = Status( "barinrange" );

for( i = 0; i < BarCount; i++) {
	if( bir[i] ) { // if code iterates within range from-to of analysis toolbar
		// then do something
	}
}

Or if having more filters

bir = Status( "barinrange" );

AR_Filter = bir AND V > MA(V, 10);

for( i = 0; i < BarCount; i++) {
	if( AR_Filter[i] ) { // if code iterates based on set filter.
		// then do something
	}
}
  1. move loop invariant code outside of loop.

  2. you use much too many Strformat functions for my taste. This can all be done by using just one of them. And

+ "\t" +

outside of StrFormat is overkill too.

  1. "no way to set column width" - This is incorrect statement. Of course you can set column width also when using AddRow with AddColumn. If using output solely based on AddRow then AddColumn is just there to initialize columns.

So for example, for columns containing numbers

AddColumn( Null, "MyValues", 1.2, -1, -1, width = 100 );

or if DateTime content

AddColumn( Null, "Date/Time", formatDateTime, -1, -1 );
// There are also formatDateTimeISO, formatDateTimeISON

or for text columns

AddTextColumn( "", "MyText", 1, -1, -1, width = 100 );

So column width is set in Add(Text)Column functions (just as usual). Alternativally there is a new functionality "Auto-size column to fit content".

11

And another hint for initializing multiple columns of same types and settings.

AddTextColumn( "", "Ticker", 1);
AddColumn(Null, "Date/Time", formatDateTime );

acstr = "Close,%,MA20,MA50,MA100,Cross,Since";
for( i = 0; i < 7; i++) 
	AddColumn(Null, StrExtract(acstr, i));	

if wanting to have same size

acstr = "Close,%,MA20,MA50,MA100,Cross,Since";
for( i = 0; i < 7; i++) 
	AddColumn(Null, StrExtract(acstr, i), 1.2, -1, -1, 70);	

The only missing part compared to just using AddColumn is coloring options of AddRow cells. That's all.

6 Likes

@fxshrat

Thanks a lot for the very detailed and useful answer and all the suggested improvements.

I’m sure that also other users here will greatly appreciate your effort to improve our coding skills.

2 Likes

I would like to clarify what I meant writing that sorting in some cases might be problematic. I didn't write or suggest at any point, that it is performed by AB incorrectly. I was probably misunderstood. I should have been more precise - sorry for that. Maybe this example will be helpful to clarify this matter to all participants:

Sorting1

If the cell contains some string prefix, sorting gives different results comparing to the situation when the string follows the value (or is not present). That is expected and very useful, because in my case I can easily group all setups that appeared on all stocks on some selected bar. But when I want to sort strictly by the values, I need to do this in a different way. In this case I used two additional columns which are almost invisible - (only 1 pixel width) but can be retrieved using mouse if they are needed. I have already written about some advantages of such solution in this thread: How do I Change the Width of Default 2 Columns of Exploration Thanks to that I can perform sorting in 3 different ways - also by the relative volume which values are not displayed in the main cell - they are only visualised using different colors.


Another thing - I didn't suggest, that my solution should by used by the original poster - I'm far from that. As I wrote - in my opinion, changing the whole exploration just to be able to repeat x times the % sign and not be able to use colors is not worth it - but that is just my point of view. Because OP wasn't especially interested in taking part in the conversation (until now users who put some effort to help him, haven't received any feedback) and other participants of this thread started discussing different ways of presenting varying bar by bar string output in Exploration, I gave my example. I thought that some users might be interested because it shows another approach to presenting the results - not vertical but horizontal which in my opinion sometimes is very useful. It allows to present i.e. 25 bars in one row instead of 25 rows which makes the output much more compact, provides lots of additional sorting possibilities and allows to easily display string variables that change bar by bar. Of course I didn't reinvent the wheel, but 99% of explorations that I see, utilizes the standard approach, so for some users it might be something new and worth trying in some cases.


@beppe Apart from AB documentation (which you have already read) you might also find these resources useful - very interesting examples with detailed explanations provided by @fxshrat AddRow 1 | AddRow 2

It's your decision. It all depends (for example) on what data you want to present and in what form. I personally use AddRow() only when I need to output data, that cannot be presented in another way. Why? Because using AddColumn() and AddTextColumn() is easier and allows to change text and background color of each cell. But each user has different preferences and priorities. For example coding the first two examples in this thread: AmiBroker - much more than just ordinary technical analysis software wouldn't be possible without using AddRow(). I was very satisfied with the results - the codes work really well. Do I still use these them? Rather not - because I always try to improve my work and decided to try out something really different. I created a chart based solution which looks like exploration. An example is presented in the last post of that thread. This kind of solutions give me unmatched possibilities of presenting the data and interaction with the results. I am extremely satisfied with the results - this first attempt was an eye opener for me. Do I recommend it to other users? Of course not - I won't recommend it to 99% of users, because for most of them, standard Exploration is more than enough. Besides coding such unusual solution requires some skills and experience - there are lots of things that must be taken into account to make it light and effective.

So why do I mention such things? Because I hope, that sometimes it might inspire someone to go off the beaten track and look at some aspects of AmiBroker from another perspective. @fxshrat's achievements in this field are far superior, but I also try to do my best. Besides I believe that the forum is a place where different ideas and views should be presented not discarded.

5 Likes

With regards to sorting: sorting in Analysis list depends on column type. There are three column types:

  • string (text)
  • numeric
  • date/time

Depending on column type sorting is done differently. The type of column is determined by AddColumn/AddTextColumn call that created that column. If you use AddTextColumn() the column is marked as text and sorted alphabetically. If you use AddColumn(), the column is either marked as numeric or as datetime (but that only if you use formatDateTime format spec).

Text columns are sorted alphabetically. Numbers in text columns are treated as texts.

Numeric columns are sorted by numeric value in the cell. Numeric value is calculated using C standard atof() function. It works perfectly fine for all numbers regardless if they have any suffix or not. So regardless if you have 10 or 10% it would sort equally well, but 10% is treated as 10, not as 0.1, because any non-numeric character just stops the conversion so if you have "10%" or "10blabla" or "10!!&$@" it will yield number of 10.

The effect of “any non-numeric character stops the conversion” rule is that that if you PREFIX the number like this "sometext 20" its numerical value will be ZERO (0). So suffixes in numeric columns are fine with sorting, but prefixes are NOT.

2 Likes

I fully agree. From Wikipedia:

Brainstorming is a group creativity technique by which efforts are made to find a conclusion for a specific problem by gathering a list of ideas spontaneously contributed by its members.

I think this is one of the best potential use of this forum. I’m not afraid to come up with what “a posteriori” seem not so smart ideas, if others users, like you and @fxshrat and many others, will show me better and/or different ways to achieve a certain result.

4 Likes