Exploration with sequential watchlists, or exploration with ratios that use one security as numerator for several ratios

Why do you need to have two rows for XLF with a single "Ratio" column, instead of one row with two columns, say "Ratio1" and "Ratio2"?

You might also look into `AddRow()`, which @fxshrat does a nice job explaining in this post: Add Dates in Row

because the exploration that I want to run is to test if each ratio as a separate numerical series passes certain Conditional Filter, for example, RSI crosses 30, or one MovingAverage crosses another Moving Average.... etc... Each ratio is a separate numerical series that needs to pass or fail the test of the exploration in a separate line.

If you want to do a calculation on the Ratio1 time series for a specific symbol (for example XLF), and then do another (or the same) calculation on the Ratio 2 time series for that same symbol, I don't think you need two rows of output for the same date and same symbol. Since you seem convinced that you do need two rows, it must be that I'm just not understanding your intent. If `AddRow()` also doesn't fulfill your needs, then perhaps another forum member will be able to step in and help you out.

Not sure that I understand your process, but could you use the BATCH processor to process your two Watchlists seperately, then combine the results in list 8?

That would require a slightly different AFL code for each process, but you would then have full control during each run to do what you want.

Additionally, perhaps if we saw what you are trying to produce (an exploration result) we might get a better idea on how to produce it.

1 Like

@malierta you can probably accomplish what you have in mind in some different ways, but I think that @mradtke's suggestion to use AddRow was correct.

Here is a skeleton code (it is not fully tested) to demonstrate how it works.
Apply it to any watchlist of symbols per any period or "1 recent bar".
As "denominators" I hard-coded SPY and QQQ; you should add your logic to change them dynamically per each "numerator".

``````Version(6.25);
SetOption("RequireDeclarations", True );

local Filter, dt, bir,  dateStr, i;

// May be you could use a loop with VarSet/VarGet to create multiple ratio pairs using dynamic vars...
local sym1, cf1, sym2, cf2;
local ratioName1, ratioValue1, conditionRow1, toInclude1, ratioMaF1, ratioMaS1;
local ratioName2, ratioValue2, conditionRow2, toInclude2, ratioMaF2, ratioMaS2;

// Required by Exploration using AddRow
Filter = 0;
SetOption("NoDefaultColumns", True );
AddTextColumn("", "Date", 1.0, colorDefault, colorDefault, 90); // colors are ignored dy AddRow
SetSortColumns(-1, 2);

sym1 = "SPY"; // use any other method here to get the denominator
ratioName1 = Name() + "/" + sym1;
cf1 = Foreign(sym1, "C");
ratioValue1 = C / cf1;
ratioMaF1 = MA(RatioValue1, 50);
ratioMaS1 = MA(RatioValue1, 200);

sym2 = "QQQ"; // use any other method here to get the denominator
ratioName2 = Name() + "/" + sym2;
cf2 = Foreign(sym2, "C");
ratioValue2 = C / cf2;
ratioMaF2 = MA(RatioValue2, 50);
ratioMaS2 = MA(RatioValue2, 200);

// Show exploration results using AddRow
dt = DateTime();
bir = Status( "barinrange" );
for( i = 0; i < BarCount; i++) {

// if code iterates within range from-to of analysis toolbar
if ( bir[i] )
{

// Here you can add something to filter on BOTH/MULTIPLE ratios
// if ( Something(RatioValue1[i]) AND/OR OtherSomething(RatioValue2[i]) )
{
dateStr = DateTimeToStr(dt[i], 4); // to sort

// Create first Ratio conditions....
ConditionRow1[i] = (ratioMaF1[i] > ratioMaS1[i]); // figure out what to do here
// Show all for demo purposes ....
toInclude1 = True; // ConditionRow1[i]; // AND AnotherConditionRow1[i]....
if (toInclude1)
{
dateStr, RatioName1, RatioValue1[i], ratioMaF1[i], ratioMaS1[i], ConditionRow1[i]));
}

// Create second Ratio conditions...
ConditionRow2[i] = (ratioMaF2[i] > ratioMaS2[i]); // figure out what to do here
// Show all for demo purposes ....
toInclude2 = True;
// Try to to filter some rows when (MA200 > MA50) OR StockNum is even.... Uncomment next line
// toInclude2 = ConditionRow2[i] OR (Status("stocknum") % 2 == 0);
if (toInclude2)
{
dateStr, RatioName2, RatioValue2[i], ratioMaF2[i], ratioMaS2[i], ConditionRow2[i]));
}
}
}
}

``````

I'm not sure it does exactly what you want, but you can take a look anyway to evaluate if it can be adapted to your goal. In such a case, be sure also to search and study other similar code examples by @fxshrat (he is a true master in the use of AddRow).

Finally, I suggest also to modify the code to create dynamic vars (naming ratio arrays/variables progressively with a counter), so you can apply it to as many ratios per symbol as you want.

(If you are not yet familiar with the dynamic vars, a search here in the forum will find some threads, like this one, that shows how to use them).

1 Like

thanks for your help, but I am afraid that AddRow is not what i am looking for. The thing is that I want to have hundreds of securities in the exploration (in different lists), and the relevant issue for me is the SEQUENTIALITY of lists in the exploration.

I want to use ratios for multiple securities in exploration, ratios which I build using the classic formula

ratio = Close / Foreign("Symbol2", "C");

The way I determine the denominator is by the watchlist:

if ( InWatchList ( 1 ) ) Symbol2 = "SPY" ;

My problem is when I want to pass an exploration using more than one ratio for any symbol as numerator. For example, let´s suppose that I want to use two ratios with XLF as numerator in an exploration. One ratio would be using the denominator SPY and the other ratio using the denominator BND.

I would put XLF both in List6 and List7, and then in List8 (which would include all the components of List6 and List7). Then I would run an exploration to List8 with using this code:

if ( InWatchList ( 6 ) ) Symbol2 = "SPY" ; // Symbol2 is denominator

if ( InWatchList ( 7 ) ) Symbol2 = "BND" ;

But I Would only get the exploration of ratio XLF/BND, not the first one XLF/SPY, for the second line of the code would overwrite the first one.

********* What i would like is to tell Amibroker to go throgh an exploration with all the components of a watchlist1 (for eg. wl #6), creating ratios with all components of the watchlist , and then, go sequentially to another watchlist2 (e.g. wl #7 and create new ratios with all componets of the second watchlist) and ALL ratios created would participate in the exploration. *******

This way, if I have one symbol which is a component of both watchlist 6 and 7, it will be explored as two different ratios.

What I would like to do is to force the exploration to go SEQUENTIALLY through LIST 6 and then through LIST 7 creating different ratios for exploration with all the components of both lists, duplicating the common components of both lists. I would pass the exploration to a List8 with all the securities of 6+7.

At the moment, the only way I can do it is by creating AddtoComposites that duplicate the security that i want to explore in two ratios, so it would be like this:

if( Name( ) == "XLF" ) Symbol2 = "SPY" ;
if( Name( ) == "~ XLF_1" ) Symbol2 = "BND" ;

The way i tried of doing this by myself was using the formula "CategoryGetSymbols", but I cannot succesfully make it.

I also investigated if there is an alternative with "StaticVar", but sincerely, I cannot figure it out how to code it.

I sincerely need help to overcome this issue. thanks in advance

@malierta Similarily to other users, I don't quite understand your approach to this issue, but I advise you to go through some example codes posted on this forum which you can find under the "Exploration" Tag. I'm under the impression, that you tend to overcomplicate things because you lack the necessary experience when it comes to possible ways of solving issues in AFL. You will find there many useful solutions and various approaches to problems similar to yours:

http://forum.amibroker.com/tags/exploration

Besides practice using the functions that you wrote you don't understand well, on much easier examples before you move to more complex ones. Proceed slowly but surely

Regards

1 Like

@malierta, with some changes to my previous code (now using dynamic variables) I got this exploration result in a single pass:

I applied the exploration to a watchlist of SP500 stocks.

I also defined for "denominators" 10 other watchlists (the first one is the same list used for the exploration, for which I defined "SPY" as a denominator, plus I also used other nine watchlists where I have the XLB...XLY sectors holdings with corresponding denominators (all my watchlists are not up to date, but you can safely ignore this for this demo).

I also defined some arbitrary extra "individual" rules (denominators) for stocks like AAPL, ABBV, and ADM.

As you can see in the screenshot, the results grid lists sequentially all the tickers I submitted to the exploration, creating one or more ratio for each of them.

In my case, in general, each ticker got two ratios, except for individual rules stocks that got three or four. In few cases, only one: this happens when the stock is not part of any "main sectors" watchlist, nor there are extra rules for it.

All the tickers that submitted to the exploration are automatically checked, one by one, and my code logic evaluate what "denominators" could be applied to each of them, creating specific ratios, following both your methods (i.e. belonging to a certain watchlist or by hard-coded rule).

When doing an exploration the entire list of tickers (according to your "Apply to:" filter settings) is de-facto processed in parallel, but you can force a true sequential process using a specific #pragma directive (something to do only for debugging purposes - see below)

The extra MA() columns are included in my code to simply demonstrate that you can also directly calculate any derived array based on the ratios that you are progressively creating, doing it in the same single-pass exploration (no need to export and apply the results to a subsequent analysis).

Anyway, this is what I figured out from your description...

Still not OK?

Please, do not repeat all your textual explanation but, if you think it will help us to understand further what you want to achieve, feel free to post a "fake" exploration grid, and provide a list of the symbols you use in each of your ideal steps (simplify it to a minimum).

In any case, for anyone interested, here is the modified code(as usual not fully tested, but enough to give you the idea):

``````// #pragma maxthreads 1
Version(6.2);

// SetOption("RequireDeclarations", True );

local Filter, dt, bir,  dateStr;
local i, j, wlPairsCnt, wlPairs, wlNumStr, wlNum, wlSym, ratioDenCnt, cf;
local denominator, ratioName, ratioValues, toInclude, ratioMaFast, ratioMaSlow;

// Required by Exploration using AddRow
Filter = 0;
SetOption("NoDefaultColumns", True );
AddTextColumn("", "Date", 1.0, colorDefault, colorDefault, 90); // colors are ignored dy AddRow
AddTextColumn("", "Ticker", 1.0, colorDefault, colorDefault, 90);
AddColumn(Null, "R#", 1.0, colorDefault, colorDefault, 30);
AddTextColumn("", "Ratio Name", 1.0, colorDefault, colorDefault, 140);
SetSortColumns(-1, 2, 3);

// Do it only ONCE. Save to some StaticVars and reuse them for the following names()
if (Status("stocknum") == 0)
{

// Define all the watchlists to look at in order to find a corresponding "denominator"
// In my testing database the SP550 holdings are in Watchlist #2 and
// XLP holdings are in watchlist #12, XLY in watchlist #15

wlPairs = "2,SPY,7,XLB,8,XLE,9,XLF,10,XLI,11,XLK,12,XLP,13,XLU,14,XLV,15,XLY"; // no spaces or parse will fail!
wlPairsCnt = 0;
for( i = 0; ( wlNumStr = StrExtract( wlPairs, i ) ) != ""; i += 2 ) // we increment by 2 for a pair
{
// "wlSym" here is what will be used as a ratio denominator
wlNum = StrToNum(wlNumStr);
wlSym = StrExtract( wlPairs, i+1 );
StaticVarSet("__wl__Pairs_Num_" + wlPairsCnt, wlNum);
StaticVarSetText("__wl__Pairs_Sym__" + wlPairsCnt, wlSym);
// _TRACE("Parsing wlPairs : #" + wlPairsCnt+1 + " wlNum: " + wlNum + " wlSym : " + wlSym);
wlPairsCnt++;
}
StaticVarSet("__wl__Pairs_Cnt__", wlPairsCnt);
}
denominatorsCnt = 1; // Counter used to create dynamic variables names
wlPairsCnt = StaticVarGet("__wl__Pairs_Cnt__");
for( i = 0; i < wlPairsCnt; i++)
{
// "wlSym" here is what will be used as a ratio denominator
wlNum  = StaticVarGet("__wl__Pairs_Num_" + i);
wlSym  = StaticVarGetText("__wl__Pairs_Sym__" + i);
// _TRACE("Name = " + Name() + " I = " + i + " WL# " + wlNum + " SYM = " + wlSym);
if (InWatchList(wlNum))
{

VarSetText("ratioDenSym" + denominatorsCnt, wlSym);
// _TRACE("Name = " + Name() + " found in WL " + wlNum + " denominatorsCnt# " + denominatorsCnt + " - Denom " + VarGetText("ratioDenSym" + denominatorsCnt));
denominatorsCnt++;
}
}

// Let's define some extra ratios denominators based on individual tickers rules....
switch (Name())
{
case "AAPL":
// you can add as many individual ruls to a sigle name
VarSetText("ratioDenSym" + denominatorsCnt, "QQQ");
denominatorsCnt++;
VarSetText("ratioDenSym" + denominatorsCnt, "DIA");
denominatorsCnt++;
break;
case "ABBV":
VarSetText("ratioDenSym" + denominatorsCnt, "XBI");
denominatorsCnt++;
break;
VarSetText("ratioDenSym" + denominatorsCnt, "MOO");
denominatorsCnt++;
break;

// etc....
}

// _TRACE("Name = " + Name() + " found in " + (denominatorsCnt-1) + " watchlists");

// Ratio calculations and associated derived arrays
for (j = 1; j < denominatorsCnt; j++) {
denominator = VarGetText("ratioDenSym" + j);
VarSetText("ratioName" + j, Name() + " / " + denominator);
cf = Foreign(denominator, "C");
VarSet("ratioValues" + j, C / cf);
ratioValues = VarGet("ratioValues" + j);
VarSet("ratioMaFast" + j, MA(ratioValues, 50));
VarSet("ratioMaSlow" + j, MA(ratioValues, 200));
// _TRACE("Name = " + Name() + " Processed ratio" + j + " with name " + VarGetText("ratioName" + j));
}

// Show the exploration results using AddRow
dt = DateTime();
bir = Status( "barinrange" );
for( i = 0; i < BarCount; i++) {

// if code iterates within range from-to of analysis toolbar
if ( bir[i] )
{

dateStr = DateTimeToStr(dt[i], 4); // to sort

for (j = 1; j < denominatorsCnt; j++)
{
ratioMaFast = VarGet("ratioMaFast" + j);
ratioMaSlow = VarGet("ratioMaSlow" + j);
isBullish = ratioMaFast[i] > ratioMaSlow[i];
// Here you can create any condition to filter results...
toInclude = isBullish; // AND ... etcetera - isBullish is not an array but boolean value
ToInclude = True; // ignore the condition to show all the calculated ratios
if (toInclude)
{
ratioName = VarGetText("ratioName" + j);
ratioValues = VarGet("ratioValues" + j);
dateStr, Name(), "#"+j, RatioName, RatioValues[i], ratioMaFast[i], ratioMaSlow[i], isBullish) );
}
}
}
}
``````

This limited code sample to be used exclusively for explorations, for sure, could be improved....
For instance, there is no need for some intermediate variables that I used only to make it a bit more easy to understand.
And if you spot any mistake (always possible), please, post your fix!

Finally, uncomment the first line #pragma if you want to _Trace() your code "sequentially".

12 Likes

very impressed, i see that you understood what I was looking for and accomplished successfully the challenge, thank you very much.
I need sometime to study how it works and apply it to my analysis, but thank you very much for your excellence and generosity

1 Like

This is very instructive. I had no idea AB could do that in one pass.

1 Like

@gerryj I too was utterly unaware of such a possibility until this thread, just a few months ago.

In that discussion, @fxshrat showed how to solve a problem using AddRow.
I later used what I learned from his very accurate comments to my post also to provide a new code sample using AddRow on another thread.

This technique is very powerful and flexible.

For such a reason, again, I suggest anyone that wants to learn it to search the forum for any thread in which @fxshrat shares some code or screenshot of the very advanced solutions he is was able to achieve using it.
(Use the advanced search feature to find messages from a specific user).

All his answers are always very instructive: also when he does not post full solutions, but he only provides some hints and images to encourage us to find our solution.

In this thread, a thank is also due to @mradtke (another of the must-follow users of this forum) who was the first here to post the idea to use AddRow to solve this particular request.

IMHO, it is the generosity of this people, and the hard work of @Tomasz, that makes this forum a truly a great resource to learn how to use AmiBroker!

Personally, I'm getting new ideas, perspectives, and code buiding blocks every day, both from very experienced users and newbies.

Sharing what I learned is my way of giving back to the community.

10 Likes

@beppe @fxshrat - I reafirm myself in the great usefulness and programming excellence displayed by your code. I have one last obstacle to overcome with your help and the community´s.

Related to AddRow in Beppe´s formula, we use :

SetOption("NoDefaultColumns", True );

(so exploration does not have default Ticker and Date/Time columns)

The problem is that when I double click on Analysis-Exploration result list to see the chart of each security-(ratio) that passses my filters, it goes like:

Error 706. Show Arrows feature needs a Trade list.

I have tried to solve the issue by using different options like:

SetOption( "PortfolioReportMode", 0 );

or

I have not been able to overcome the issue. I have looked in your posts related to AddRow but I have not found the solution.

Thank you very much again

1 Like

@malierta I have never seen that error but looking at the help file (Amibroker Formula Language/AFL Error List) I read:

Error 706. Show Arrows feature needs a Trade list.

You attempted to use "Show arrows" feature (or double clicked on Analysis result list) but AmiBroker can not display trading arrows unless you run backtest with Report mode set to Trade List.
You would need to go to the Settings window, Report tab, and change Result list shows to Trade list (the default setting).

Hope this will help

1 Like

@beppe - I forgot to say that I have already checked that "Report mode set to Trade List". In fact, I also tried to use the programming method of doing it:
SetOption( "PortfolioReportMode", 0 );
I am trying also to include Buy orders, but no way, I cannot double click in the exploration result list without getting the Error 701. Missing buy/sell variable assignments.
I get this error also using the code just as you wrote it. The error is not in the exploration itself, which is fine, but when I double click in the result list to see the charts of the securities-ratios.

@malierta, I run the code and saw the issue. I don't know if there is a way to avoid it via code, but I suggest you a couple of workarounds.

Usually, I save the exploration results to a watchlist using one of the menu items of the popup menu that you get when you click on the result list with the right mouse button. (Mainly I use "Replace watch list with the results...").

As an alternative, if in your code (like in the above screenshot) you put the ticker in the first column, you could use this other option:

In this case, you should not double click on the rows but simply select them with a single mouse click.
The above menu is available clicking on the small "triangle down" symbol to the right of the "tools" icon .

If someone else has a better solution, let us know!

1 Like

@Malierta I played a little more with my code: here just putting the Name() in the first column seems to solve the issue (I simply inverted the Name() a Date data columns and corresponding headers).

4 Likes

Bravo & Fino @beppe
But I am afraid your solution has arisen one last problematic issue. Remember that in your code there are three ratios for AAPL: one from the wl- watchlists (with denominator SPY), and two from the individual tickers section (one with QQQ and the 3rd one with DIA). Now I can double click, but when I chart , using the code:

Plot(ratioValues, Name() + " / " + denominator, colorwhite , styleLine );

which i insert in your code:

// Ratio calculations and associated derived arrays
for (j = 1; j < denominatorsCnt; j++) {
denominator = VarGetText("ratioDenSym" + j);
VarSetText("ratioName" + j, Name() + " / " + denominator);
cf = Foreign(denominator, "C");
VarSet("ratioValues" + j, C / cf);
ratioValues = VarGet("ratioValues" + j);
VarSet("ratioMaFast" + j, MA(ratioValues, 50));
VarSet("ratioMaSlow" + j, MA(ratioValues, 200));
// _TRACE("Name = " + Name() + " Processed ratio" + j + " with name " + VarGetText("ratioName" + j));

``````Plot(ratioValues, Name() + " / " + denominator, colorwhite , styleLine  );
``````

}

For AAPL ... I get the three ratios charted... as you can see in the picture that I send.

So if there is only one ratio for a security, then there is no problem, but if there is more than one ratio, I get all the ratios charted as you can see.

How can I tell amibroker to chart ONLY the ONE ratio that is selected by clicking in the exploration report list?

Thanks again Beppe for your pronta finezza

1 Like

Hello, I have the same problem too. Please help me, I have followed the instructions and still failed. Thanks very much

Do NOT remove default columns. Default Ticker and Date/Time columns are REQUIRED. Among others for "show arrows" and chart syncing feature.

This code is source of your problems

``````SetOption("NoDefaultColumns", True ); // do NOT do that
``````

Remove such lines from the code.

1 Like

Thank you very much Mr. Tomasz for your help. GOD bless you...