Problems creating own Index with Norgate and store with AddToComposite

[jerdchen]

Hi there,
I would like to create my own calculated index with Norgate data and save it as a composite. The index should logically only be calculated for the current members. This works without problems with data from another provider.

I am using the following code ...

procedure CreateIndex(lNorgateData, sNorgateIndexName, sCompositName)
{
	wlnum = GetOption( "FilterIncludeWatchlist" );
	List = CategoryGetSymbols( categoryWatchlist, wlnum ); 
	IndexCount = 0;
	IndexClose = 0;
	//IndexHigh = 0;
	//IndexLow = 0;
	//IndexOpen = 0;
	//IndexVol = 0;
    for (f = 0; ( Symbol = StrExtract(List, f ) )  != "";  f++) 
    { 
		SetForeign (symbol); 
		if (lNorgateData)
		{
			inIndexNorgate = NorgateIndexConstituentTimeSeries(sNorgateIndexName, Symbol);
			IndexClose += IIf(Nz(inIndexNorgate),Nz(Close), 0);
			//IndexHigh += IIf(Nz(inIndexNorgate),Nz(High), 0);
			//IndexLow += IIf(Nz(inIndexNorgate),Nz(Low), 0);
			//IndexOpen += IIf(Nz(inIndexNorgate),Nz(Open), 0);
			//IndexVol += IIf(Nz(inIndexNorgate),Nz(Volume), 0);
			IndexCount += IIf(Nz(inIndexNorgate), 1, 0);
		}
		else
		{
			IndexClose += Nz(Close);
			//IndexHigh += Nz(High);
			//IndexLow += Nz(Low);
			//IndexOpen += Nz(Open);
			//IndexVol += Nz(Volume);
			IndexCount += IIf(Nz(Close) == 0, 0, 1);
		}
		RestorePriceArrays();
	}
        AddToComposite(IndexClose / IndexCount,sCompositName,"x", 1|2|8|32|64|128);
		//AddToComposite(IndexCount,sCompositName,"X", 1|2|8|32|64|128); // !!!!! Test !!!!!!
		
        //AddToComposite(IndexClose / IndexCount,sCompositName,"C", 1|2|8|32|64|128);
		//AddToComposite(IndexHigh / IndexCount,sCompositName,"H", 1|2|8|32|64|128);
		//AddToComposite(IndexLow / IndexCount,sCompositName,"L", 1|2|8|32|64|128);
		//AddToComposite(IndexOpen / IndexCount,sCompositName,"O", 1|2|8|32|64|128);
		//AddToComposite(IndexVol / IndexCount,sCompositName,"V", 1|2|8|32|64|128);
}

I get the following result

IndexCount works correctly ... I get the following result ...

I've tried everything. I don't know what to do next. Maybe someone can help me. Thanks in advance.

[jerdchen]

Sorry .... the code i post was the wrong Version ....

The correct Version is ....

procedure CreateIndex(lNorgateData, sNorgateIndexName, sCompositName)
{
	wlnum = GetOption( "FilterIncludeWatchlist" );
	List = CategoryGetSymbols( categoryWatchlist, wlnum ); 
	IndexCount = 0;
	IndexClose = 0;
	//IndexHigh = 0;
	//IndexLow = 0;
	//IndexOpen = 0;
	//IndexVol = 0;
    for (f = 0; ( Symbol = StrExtract(List, f ) )  != "";  f++) 
    { 
		SetForeign (symbol); 
		if (lNorgateData)
		{
			inIndexNorgate = NorgateIndexConstituentTimeSeries(sNorgateIndexName);
			IndexClose += IIf(Nz(inIndexNorgate),Nz(Close), 0);
			//IndexHigh += IIf(Nz(inIndexNorgate),Nz(High), 0);
			//IndexLow += IIf(Nz(inIndexNorgate),Nz(Low), 0);
			//IndexOpen += IIf(Nz(inIndexNorgate),Nz(Open), 0);
			//IndexVol += IIf(Nz(inIndexNorgate),Nz(Volume), 0);
			IndexCount += IIf(Nz(inIndexNorgate), 1, 0);
		}
		else
		{
			IndexClose += Nz(Close);
			//IndexHigh += Nz(High);
			//IndexLow += Nz(Low);
			//IndexOpen += Nz(Open);
			//IndexVol += Nz(Volume);
			IndexCount += IIf(Nz(Close) == 0, 0, 1);
		}
		RestorePriceArrays();
	}
        AddToComposite(IndexClose / IndexCount,sCompositName,"x", 1|2|8|32|64|128);
	//AddToComposite(IndexCount,sCompositName,"X", 1|2|8|32|64|128); // !!!!! Test !!!!!!
		
        //AddToComposite(IndexClose / IndexCount,sCompositName,"C", 1|2|8|32|64|128);
	//AddToComposite(IndexHigh / IndexCount,sCompositName,"H", 1|2|8|32|64|128);
	//AddToComposite(IndexLow / IndexCount,sCompositName,"L", 1|2|8|32|64|128);
	//AddToComposite(IndexOpen / IndexCount,sCompositName,"O", 1|2|8|32|64|128);
	//AddToComposite(IndexVol / IndexCount,sCompositName,"V", 1|2|8|32|64|128);
}

Sorry for my mistake ..

[mradtke]

It's not at all clear what you are trying to achieve, or why you think the results you're getting are incorrect. Please see this post: How to ask a good question. You may also find this one helpful: How do I debug my formula?

It appears that what you're trying to calculate is the average price per share for members of the S&P 500 index, and if I had to guess, you're wondering why that average price drops so much in 1996. But you haven't shared your full code, so nobody knows what your sNorgateIndexName variable is set to, or whether you are using prices adjusted for splits and dividends, or what watchlist you're running against, or...

My suggestion would be to create an Exploration that outputs the daily closing price of each member of the index. Run that Exploration for a portion of 1996 or any other time of interest, export the data into Excel, and see if you can get any clues as to why the average price changes so dramatically. Or if that's not the problem you're trying to solve, then provide more details so that people can help you without guessing what you want.

[jerdchen]

Ok ... my question was really incomplete. Sorry ....

That's right. It is the S + P 500. And it is correct. I would like to have an unweighted price index.

I have set "S&P 500 Current and Past" as the watch list.

The function is called like this ...

CreateIndex(True, "$SPXTR", "~SPXTRCALC");

I configured Norgate Data that way.

Yes, I actually wonder about the wild jumps in the calculated index. An effect that also occurs in other Nogate data on other indices.

[portfoliobuilder]

@jerdchen I haven't really looked at your code as I would use StaticVarAdd instead of AddToComposite (just a personal preference). But I would create an Exploration and look over your stocks on the dates of the "wild jump".

I suspect you will find on those dates there will be some low priced stock(s) that leave the Index, and some relatively higher priced stock(s) join the Index. How to find these instances? Maybe something like this

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

InIndex = NorgateIndexConstituentTimeSeries("S&P 500");

// try to identify when they enter or exit the index
NotInIndex = NOT NorgateIndexConstituentTimeSeries( "S&P 500");
Dropped = NotInIndex AND Ref(InIndex, -1);
Added	= InIndex AND Ref(NotInIndex, -1);

You might find something like this

or this,

On a related point of interest, if you are looking for an equal weighted version of the S&P 500 there is the ETF symbol RSP in the U.S. that has been trading since 2003.

[mradtke]

What you are creating is not an unweighted index, but rather a price-weighted index. Consider the following very simple example of an index with two symbols, A and B.

1. On Day 1, A closes at $10 and B closes at $100. The average price is $55, so that is what you are assigning to your index.
2. On Day 2, A closes at $11 (+10%), and B closes at $90 (-10%). The average price is now $50.50, or a drop of ~8%.

In an unweighted index, the change in the index should be (+10% + (-10%)) / 2 = 0%. In your price-weighted code, the change in price of B has much more effect than the price change in A. In short, you should be using the average ROC(C,1) for all the symbols in the index, and applying that percentage on a daily basis to your index price.

The example from @portfoliobuilder illustrates what can happen when you're not only building a price-weighted index, but also using split and dividend adjusted prices. Even though DYN-201804 was trading around $56 in late December 2000, its adjusted price was nearly $18,000. If you were doing your calculations with a 1-day ROC (which is expressed as a percentage), then you would get the same result using the adjusted price as the unadjusted price. But because you are summing the closing prices of all index members, it can make a huge difference which price you decide to use.

[jerdchen]

@mradtke
This is exactly where my mistake was probably. Thank you for the clarification. I'll give it a try and rebuild the code accordingly.

@portfoliobuilder
Thanks for the good, very memorable example.

[fxshrat]

Creating equally weighted Index is quite simple.

function EqualWeightIndex( symlist, initial_value, fixup ) {
	/// code source AB forum
	/// @link https://tinyurl.com/yu9ray8y
	local i, cnt, sum_rc, price, rc; 
	local sum_rc, cnt, avg_rc, result; 
	sum_rc = cnt = 0;
	for ( i = 0; (sym = StrExtract(symlist, i)) != ""; i++ ) {
		SetForeign(sym, fixup);
			//is_inIndex = Nz(....);
			rc = C/Ref(C,-1)-1;
			sum_rc += Nz(rc) /*AND is_inIndex*/;
			cnt += IIf(NOT IsNull(rc) /*AND is_inIndex*/, 1, 0);
		RestorePriceArrays();
	}
	avg_rc = SafeDivide(sum_rc, cnt);// SafeDivide - AB 6.35
	result = CumProd(avg_rc+1)*initial_value;// CumProd - AB 6.20
	return result;
}

Version(6.35);// minimum AB version required (because of SafeDivide)

wlnum = 0;
initial_value = 1000;
symlist = CategoryGetSymbols( categoryWatchlist, wlnum ); 
Plot( EqualWeightIndex( symlist, initial_value, 1 ), "EWI", colorRed );
PlotGrid(initial_value, colorGold);

Similar codes have been posted in old AB forum already.

---

@jerdchen

//IndexHigh += IIf(Nz(inIndexNorgate),Nz(High), 0);
			//IndexLow += IIf(Nz(inIndexNorgate),Nz(Low), 0);

High/Low price may occur at different times for each symbol.
Rather use Close only at highest granularity then AB creates High Low of stored Index on longer Interval automatically.

[jerdchen]

I had already changed my code last night (live in Switzerland). Looks very similar to yours. You solved it a little more elegantly, I a little more laborious. The result should be the same.

Again, many, many thanks to @portfoliobuilder and @mradtke . You put me on the path of knowledge.

I like to use indices as an entry filter in portfolio trading. I hope using this calculated index gives better results.

procedure CreateIndex(lNorgateData, sNorgateIndexName, sCompositName)
{
	local wlnum, List, IndexCount, IndexClose, f, g, Symbol, inIndexNorgate;
	local InitialIndexValue, AvgRoc, CumRoc;
	InitialIndexValue = 1000;
	CumRoc = 0;
	IndexCount = 0;
	IndexClose = 0;
	wlnum = GetOption( "FilterIncludeWatchlist" );
	List = CategoryGetSymbols( categoryWatchlist, wlnum ); 
    for (f = 0; ( Symbol = StrExtract(List, f ) )  != "";  f++) 
    { 
		SetForeign (symbol); 
		if (lNorgateData)
		{
			inIndexNorgate = NorgateIndexConstituentTimeSeries(sNorgateIndexName);
			IndexClose += IIf(Nz(inIndexNorgate) AND Ref(Nz(Close), -1) != 0,ROC(Nz(Close),1), 0);
			IndexCount += IIf(Nz(inIndexNorgate) AND Ref(Nz(Close), -1) != 0, 1, 0);
		}
		else
		{
			IndexClose += IIf(Ref(Nz(Close), -1) != 0,ROC(Nz(Close),1), 0);
			IndexCount += IIf(Nz(Close) == 0, 0, 1);
		}
		RestorePriceArrays();
	}
	AvgRoc = IIf(IndexCount != 0, IndexClose / IndexCount, 0);
	CumRoc[0] = InitialIndexValue;
	for( g = 1; g < BarCount; g++ )
	{
		if (AvgRoc[g] != 0)
		{
			CumRoc[g] = CumRoc[g -1] * (1 + (AvgRoc[g] / 100));
		}
		else
		{
			CumRoc[g] = CumRoc[g - 1];
		}
	}
    AddToComposite(CumRoc,sCompositName,"x", 1|2|8|32|64|128);
}

[fxshrat]

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 EqualWeightIndexScan() {
	/// code source AB forum
	/// @link https://tinyurl.com/yu9ray8y
	local not_null, rc, sum_rc;	
	//is_inIndex = Nz(...);
	rc = ROC(C,1)/100;	
	not_null = NOT IsNull(C) /*AND is_inIndex*/;
	sum_rc = not_null * Nz(rc);
	/// @link https://www.amibroker.com/guide/afl/staticvaradd.html
	if(Status("stocknum") == 0)
		StaticVarRemove("~EWI*");
	StaticVarAdd("~EWI_total", sum_rc);	
	StaticVarAdd("~EWI_count", not_null);
}
function EqualWeightIndex( initial_value ) {
	/// code source AB forum
	/// @link https://tinyurl.com/yu9ray8y
	local cnt, sum_rc, avg_rc, result; 
	sum_rc = Nz(StaticVarGet("~EWI_total"));
	cnt = Nz(StaticVarGet("~EWI_count"));
	avg_rc = SafeDivide(sum_rc,cnt);// SafeDivide - AB 6.35
	result = CumProd(avg_rc+1)*initial_value;// CumProd - AB 6.20 
	return result;
}

Version(6.35);// minimum AB version required (because of SafeDivide)

initial_value = 1000;

if ( Status("action") == actionScan ) {
	EqualWeightIndexScan();
}

ewi = EqualWeightIndex( initial_value );

Plot( ewi, "EWI", colorRed );
PlotGrid(initial_value, colorGold);

[jerdchen]

First of all, thank you very much for your good advice.

I am completely aware that AddToComposit is considerably slower than StaticVars. It is also clear to me that SetForeign also slow down the program execution considerably. A bar count loop slows things down again.

But ... this procedure is only called once within the first thread. I can also switch off this function in the AFL. One run and I have the composite I need. In optimizations, I run the procedure once and the AFL has the required composite then i switch it off.

The AFL consists of up to 4 systems running in parallel and has around 2500 lines. Each system accesses the index once in Stocknum == 0. I would have had to convert all systems to static vars. I didn't feel like doing that.

The time required would have been an hour or more.

So I left AddToComposit.

Or do you see another bug in my code? I did the first test. It looks like using it brings quite significant improvements over using the normal index. CAR significantly better, drawdown considerably lower, CAR / MDD significantly higher. Everything over a period of at least 30 years. Yeah ... it looks like I had a good idea.

Nevertheless, thank you again for your reference.

[portfoliobuilder]

@jerdchen I guess I don't understand what you are trying to accomplish even after all of these posts. Your last post seems to imply you have a trading strategy that uses an equal weighted version of the S&P 500 as in input. And you are a Norgate data subscriber. Why are you doing all this composite creation when data for an equal weighted index already exists? You can use that in your strategy and access it with either Foreign or SetForeign

Your results are improving according to your last post so that sounds like good news and good luck.

[jerdchen]

You are absolutely right ... I saw that too this morning. Well ... I learned anyway and it was good practice. And yes ... it was a good idea to use this index ...

[fxshrat]

@jerdchen

The AFL consists of up to 4 systems running in parallel and has around 2500 lines. Each system accesses the index once in Stocknum == 0. I would have had to convert all systems to static vars. I didn't feel like doing that.

Quite frankly I don't care what other code you have and what you don't want to do. It is not about that.
You are not the only user reading this thread.

So comparing "your" code with the other ones of this thread yours simply is technically inferior and it should not be done the way as you did. So others should not do same mistake.

BTW the other codes may use AddToComposite too to store to DB but is not required.

---

As for whether indexes exists from vendor... not everyone uses same vendor. Also it is unknown whether they are correct. Besides with own code you can create any index that does not exist at data vendor.