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.
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.
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.
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).
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: