Calculate equal weighted average daily total return for S&P 100 index

I am trying to calculate the equal weighted historical average daily total return across tickers in S&P 100 index over time. For some reason my code generates a result starting on March 20, 2019, but nothing prior to that. I am confused why that is. Any assistance appreciated. Note that I am using data and functions supplied by Norgate Data.

My Watchlist is set to "S&P 100 Current & Past". The date range on the Explore is set to 1/1/2017 thru 3/1/2024.

For some reason the output of the Explore gives results every day in the columns labeled "Close" and "TR", but the output is blank for dates prior to 3/20/2019 in the columns labeled "TotCount", "TotTR", and "AvgTotTR". But for dates after 3/20/2019 there is data in those 3 columns that looks correct. Why would those columns not have any output at all (i.e. blank) prior to March 20, 2019 in the output of my Explore? Is my Norgate database corrupt?

The AFL code I am using is below.

#include_once "Formulas\Norgate Data\Norgate Data Functions.afl"

oc = NorgateOriginalCloseTimeSeries();
ov = NorgateOriginalVolumeTimeSeries();

Tickerlist = CategoryGetSymbols( categoryWatchlist, GetOption( "FilterIncludeWatchlist" ));

if( Status( "stocknum" ) == 0 ) 
{

	StaticVarRemove("TotTR");
	StaticVarRemove("TotCount");
	TotTR = 0;
	TotCount = 0;
	
    for( n = 0; ( Symbol = StrExtract( Tickerlist, n ) )  != "";  n++ )
    {
        SetForeign( symbol );
        TR = IIf(NorgateIndexConstituentTimeSeriesOther("S&P 100", symbol), C/Ref(C, -1)-1, 0);
        Count = IIf(NorgateIndexConstituentTimeSeriesOther("S&P 100", symbol), 1, 0);
        RestorePriceArrays();
        
        TotTR += TR;
        TotCount += Count;
    }
    
    StaticVarSet("TotTR", TotTR);
    StaticVarSet("TotCount", TotCount);
    
}

TotTR = StaticVarGet("TotTR");
TotCount = StaticVarGet("TotCount");

AddColumn(C, "Close", 1.2);
AddColumn(C/Ref(C, -1)-1, "TR", 1.4);
AddColumn(TotCount, "TotCount", 1.0);
AddColumn(TotTR, "TotTR", 1.4);
AddColumn(TotTR/TotCount, "Avg TotTR", 1.4);

Filter = Name() == "IBM";


SetSortColumns(1,2);

Do you have Pad & Align enabled in the Analysis Settings window?

Thanks for your reply. No I did not have the Pad and Align box checked. But I went into the settings and checked that box and the result is the same. The ticker associated with the Pad and Align box is currently set to ^DJI. I'm not sure if I should set that ticker to something else?

It's only an issue if you have Pad & Align checked and the Symbol linked to it has less bars than Symbol/s under test.

Post up your Database settings.

You need to use a symbol that's actually in your database that has a long and well-behaved history. With Norgate, I use $SPX.

I tried using $SPX with the Pad and Align and still same result. I'll try to post a screen shot of my settings.

What is the best way to post the database settings?

Here are the database settings:

image

Here I show what the result indow looks like on ticker IBM. This doesn't show all the way back to 1/1/2017 but shows where the data starts on 3/20/2019.

Might as well post my filter settings also.

image

Okay I believe I am getting much closer. I rewrote the AFL code using StaticVarAdd instead of looping through each symbol individually. This seems to work better as now I am getting results for each date in the Explore date range. However when I run the Explore I'm not sure how to set the Filter so I only get the last symbol in the SP100 index on each day. But I can work that out in Excel, I would just prefer to know how to do this using Amibroker Explore.


#include_once "Formulas\Norgate Data\Norgate Data Functions.afl"

oc = NorgateOriginalCloseTimeSeries();
ov = NorgateOriginalVolumeTimeSeries();

Tickerlist = CategoryGetSymbols( categoryWatchlist, GetOption( "FilterIncludeWatchlist" ));

if( Status( "stocknum" ) == 0 ) 
{
	StaticVarRemove("TotTR");
	StaticVarRemove("TotCount");
}

StaticVarAdd("TotTR", NorgateIndexConstituentTimeSeries("S&P 100")*(C/Ref(C, -1)-1));
StaticVarAdd("TotCount", NorgateIndexConstituentTimeSeries("S&P 100"));

TotTr = StaticVarGet("TotTR");
TotCount = StaticVarGet("TotCount");

AddColumn(C, "Close", 1.2);
AddColumn(C/Ref(C, -1)-1, "TR", 1.4);
AddColumn(TotCount, "TotCount", 1.0);
AddColumn(TotTR, "TotTR", 1.4);
AddColumn(TotTR/TotCount, "Avg TotTR", 1.4);

Filter = NorgateIndexConstituentTimeSeries("S&P 100");

SetSortColumns(2, 5);

Code for equal weighted index exists already

Your issue is probably related to the way AmiBroker uses the dates from the first symbol (Status( "stocknum" ) == 0 ) l in the loop.

Workarounds are described here:
https://norgatedata.com/amibroker-faq.php#limitedresults

Some of you guys don't get it.
You do not need any AFL looping for that and shouldn't.
It is not needed and not recommended.
Neither for ranking nor for this.
Just use Staticvaradd here and enable pad&align with proper symbol.

Another reference worth noting.