Producing a sorted report of only top N rows

Hi All,
My analysis produces a sorted report. How can I generate a report with only the top N rows programmatically? I fully understand I can export to CSV file and trim the report with Excel. But this is not quite acceptable to me when I need to do this many times in a day. I prefer I can do this with my code automatically.

As a compromise, if my report can generate row number as one of the columns in the final sorted report, that will work for me too. But I could not find any API to generate row numbers in the report.

Any help or pointer will be greatly appreciated.


@kzliao, if you are speaking about explorations, there is one specific function to add a rank number:


In this forum, I already frequently suggested bookmarking the AFL Function Reference - Categorized list of functions page.
I find it very useful to locate related functions.

Yes, I am talking about Exploration. Thank you and I will looking this function!

AddRankColumn() works great in producing the visual cue of the position in a large report. I wonder if the runtime rank numbers in this column can be read during the report generation time? If so, I have a perfect way to produce the Top N stocks report. For example, I have a very large report, but I only want to have the top 100 rows. If the rank column values can be read during runtime, I can easily say Filter = (RankColumnValue <=100), etc.

Use StaticVarGenerateRanks to Filter by rank.

@kzliao I suggest studying this past thread

Thanks for the pointer!

Thanks, will do that!

Think about it. RANK can only be calculated AFTER all values known, because to know the rank you have to perform SORTING of entire result set. For this reason you can't know the rank when before all rows are outputted. Doing what you wanted would either involve DELETION of extra rows or calculating ranks (and all data anyway) using separate function like StaticVarGenerateRanks so they are known before formula finishes.

Easy solution is to TRIM the output using tools like Unix tail and head commands:

You can schedule exploration, then export, then head/tail command and it will do all processing for you automatically.

1 Like

On Windows (without installing Cygwin or a Bash shell) you can use PowerShell to do the same (i.e head/tail), redirecting the output to a destination file).

See these examples in StackOverflow

When I need to invoke these commands from an AmiBroker batch, usually I create a batch file (.bat) that encapsulate them and invoke it using the "Execute and Wait" action.

Example of a .bat file (named truncate.bat)

CD "C:\Program Files\AmiBroker\Exports"
powershell gc %1.csv -head %2 > %1_top_%2.csv
type %1_top_%2.csv

(%1 and %2 are the replacement parameters for the arguments I pass to the DOS/Windows batch file - the filename without extension and # of lines to keep). To call it from a .abb file, in the "Execute and Wait" line I use something like:

"C:\Program Files\Amibroker\Exports\truncate.bat" Analisys_03 25


@Tomasz @beppe Thank you all for your great suggestions! I have used the StaticVarGenerateRanks function to achieve my purpose. Many thanks as always!


1 Like

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.