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

[Johnax]

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);

[mradtke]

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

[Johnax]

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?

[TrendSurfer]

@Johnax

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.

[mradtke]

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.

[Johnax]

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?

[Johnax]

Here are the database settings:

[Johnax]

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.

[Johnax]

Might as well post my filter settings also.

[Johnax]

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);

[fxshrat]

Code for equal weighted index exists already

Problems creating own Index with Norgate and store with AddToComposite

Do not use Barcount loop. BTW an even better way than using SetForeign wthin symbol loop is using StaticvarAdd(). Here is completely loopless code (no symbol loop with SetForeign and no Barcount loop). First put AFL to Analysis. Set Apply to: Filter. Enable Pad&align. Hit Scan. Then put on chart or create Exploration. //First put AFL to Analysis, //Set Apply to: Filter, Enable Pad&align and hit Scan. //Then put on chart or create Exploration. //by fxshrat@gmail.com procedure EqualWeight…

[NorgateData]

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

[fxshrat]

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.

[TrendSurfer]

Another reference worth noting.

Correct use of StaticVarAdd

Since wrong codes with StaticVarAdd are posted all the time, here is short summary on how to use it correctly: Correct use of StaticVarAdd is explained in the manual http://www.amibroker.com/guide/afl/staticvaradd.html The example shows clearly the code: // this code is intended to be run ON MULTIPLE SYMBOLS (e.g. watch list) // and only once to create composite / static var if( status("stocknum") == 0 ) { // remove any earier composite values StaticVarRemove("~Composite"); } // cre…