Transpose exploration

Announcements & Discussions New to Community
1

Hello Ami-pros.
Is there a way to transpose the output of an exploration such that symbols appear as column headers?

I have been using Amibroker in combination with Excel for many years. Excel has an approximate 1 MM limit on the number of rows. So for example, when I output data on the S&P 500 over a 15 year timeframe, I exceed Excel's limit. In order to circumvent this issue, I am breaking up the exploration output into separate timeframes and then using Excel's filtering, pivottable and data consolidation functions to transpose the data such that the symbols are columns (fields) and dates are rows (records) . It would be so much easier to execute a transposition in Amibroker.

fyi...I am very experienced with Excel and moderately experienced with Amibroker but have never written a line of code in my life.

Any advice would be very greatly appreciated.

2

Maybe if you describe your goal in Excel after all this work, we could suggest you a way to do everything in Amibroker....

3

Thank you. 80% of the models I actively trade are based on rotational systems that use ranking. However, neither the EnableRotationalTrading function nor the AddRankColumn function seem to work well in my models; the former because it requires that a top/bottom position is always held (I don't always hold a position) and the latter because it is intended for exploration output. I suspect that a bar by bar ranking using static variables would be the way to go. But, thanks to my inability to program, this sort of function is not available or understandable to me. Ranking in an Excel table with tickers as column headers and dates in rows is trivially simple.

I do hope to learn programming when I retire (or if I'm retired).

4

Not correct... you don't have to hold position all the time.

AFL Function Reference - ENABLEROTATIONALTRADING

The score has the following meaning:

  • higher positive score means better candidate for entering long trade
  • lower negative score means better candidate for entering short trade
  • the score of zero means no trade (exit the trade if there is already open position on given symbol)
  • the score equal to scoreNoRotate constant means that already open trades should be kept and no new trades entered
  • the score equal to scoreExitAll constant causes rotational mode backtester to exit all positions regardless of HoldMinBars. Note that this is global flag and it is enough to set it for just any single symbol to exit all currently open positions, no matter on which symbol you use scoreExitAll (it may be even on symbol that is not currently held). By setting PositionScore to scoreExitAll you exit all positions immediatelly regardless of HoldMinBars setting

E.g.

PositionScore = IIf(index > MA(index,200), score, 0);
1 Like
5

Thanks. Not sure how I missed the zero score. I'll give it a try.