Create historical custom index

Hi.

I am trying to create a custom index of stocks. I have the dates that they enter and exit my index.

How would I go about creating this index (I'm thinking as a watchlist) but also take into account the rebalancing of the constituents in this index.

The goal is to be able to backtest this index without issues such as survivorship, removals and delistings. The rebalancing takes place on a weekly basis.

In effect I'm trying for something similar to what Norgate offers with their historical constituent database but for my unique index based on other criteria. I don't mind updating my custom index manually.

Thanks

Please refer to Calculating multiple-security statistics with AddToComposite function. Also there is a PDF at the footnote of the article, download and read through it. And use Plot() function for charting purposes not Graph0 since it was an old method (now obsolete)!

1 Like

What’s your custom criteria based off? It’s it’s OHLC data just use it in AFL for your backtest and ignore making watchlist.

Perhaps someone with Norgate data could chime in. Be a lot of code to recreate the Norgate built in functions for iscontituant()

1 Like

Hi guys.
Perhaps "index" is a wrong choice of word. My watchlist will be based on existing stocks in the Norgate database of S&P 500 stocks. It's the movement of stocks in and out of this watchlist that I need to reflect.

So for example say if my watchlist is based on PE ratio below 30 then I need to plug in dates when stocks entered my watchlist when they met the criteria and also dates when they left my list after PE criteria no longer met. This would enable me to backtest correctly.
Thanks

This should be relatively easy to implement. You just need time series data that indicates when each symbol is in (1) or out (0) of your "index". Since Norgate already utilizes the Aux1 and Aux2 fields in the database, you will probably need to create new ticker symbols based on the original symbol name. For example ~MyIndexAAPL would hold the index membership data for AAPL, ~MyIndexMSFT would hold the index membership data for MSFT, etc. You could use any field within the new tickers to hold your membership data, but I would probably use one of the Aux fields. If you decide to use a price field, you will need to set "no quotation data" when you import the membership data.

Once the data is in your database, you can just use the Foreign() function to retrieve the new ticker symbol and therefore the index membership data.

2 Likes

Many thanks for pointing me in this direction. I'll give this a go once I've collated all of my data, and will be sure to come back and mark your post as a solution once I get it working