CSI Stocks: Active/Inactive

Discussions about the testing and simulation of mechanical trading systems using historical data and other methods. Trading Blox Customers should post Trading Blox specific questions in the Customer Support forum.
Post Reply
AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

CSI Stocks: Active/Inactive

Post by AFJ Garner » Fri Jun 13, 2014 4:02 am

I recently built text files for the entire 26,000 US mutual funds in CSI's database and ran a test on the entire universe in a single test. TB just about coped with it: well done Trading Blox.

The bad news is that it is vital to know which are current and which are inactive and the disappointment is that CSI do NOT accurately label the mutual fund database. All mutual funds are marked "A". Same with Canadian stocks.

This wreaks havoc with any back test. I believe I can work around the problem but it is a large chink in CSI's armour.

Roger Rines
Roundtable Knight
Roundtable Knight
Posts: 1946
Joined: Wed Oct 06, 2004 10:52 am
Location: San Jose, CA

Post by Roger Rines » Fri Jun 13, 2014 11:09 am

Recent versions of Unfair Advantage provide an option to append an apostrophe/single quote (ASCII Dec#: 39) [ ' ] to Delisted instruments.

I've not tested this option to see if it would work with Mutual Funds, but I do know Mutual Funds are viewed as Stock-Type instruments that can be adjusted for dividend and capital distributions so this might be a helpful option.
Attachments
CSI Preferences Market Specs Delisted Append Apostropy.png
CSI Preferences Market Specs Delisted Append Apostropy
CSI Preferences Market Specs Delisted Append Apostropy.png (27.68 KiB) Viewed 5619 times

AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

Post by AFJ Garner » Fri Jun 13, 2014 11:26 am

Thanks Roger, I'll give that a try. Oddly enough, as it is CSI's fact sheets is supposed to append A for listed and I for inactive to the stocksinfo.txt file........but in the cases where I have used the appropriate procedure to write files for an entire market, this feature seem to have gone by the board. And as you can imagine writing thousands of txt files is not such a quick process!

AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

Post by AFJ Garner » Fri Jun 13, 2014 11:26 am

Thanks Roger, I'll give that a try. Oddly enough, as it is CSI's fact sheets is supposed to append A for listed and I for inactive to the stocksinfo.txt file........but in the cases where I have used the appropriate procedure to write files for an entire market, this feature seem to have gone by the board. And as you can imagine writing thousands of txt files is not such a quick process!

AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

Post by AFJ Garner » Fri Jun 13, 2014 11:47 am

I think I may have misunderstood the way CSI retrieves information. If you use the portfolio wizard and request mutual funds it only returns currently active mutual funds. If you request US de-listed stocks it looks as if then and only then will you get de-listed mutual funds. In addition to de-listed stocks.

So, what I will do instead is to make my stocksinfo.txt file by using the CSI API and get the info for the entire database. And go from there.

It is all rather laborious and tedious. Hitherto I have been back testing on FTSE 350, S&P 500 and S&P 400 past and present/ listed and de-listed. Laboriously ascertaining membership manually.

I then downloaded all Canadian stocks, all US mutual funds. But unfortunately it now seems these downloads did not include de listed instruments.

Live and learn.

Roger Rines
Roundtable Knight
Roundtable Knight
Posts: 1946
Joined: Wed Oct 06, 2004 10:52 am
Location: San Jose, CA

Post by Roger Rines » Fri Jun 13, 2014 12:48 pm

I didn't check the API documentation to check which DLL call would retrieve the "I" active property, but this setting in the UA Catalog might be a way of figuring out in bulk terms if a symbol is delisted and how many there are in the Catalog.

At a minimum, the Last Data date is alternate way to find instruments that are no longer being updated. Where this falls down would be in an instrument that is just be delisted, or there is a DB update issue. Still, it is a way to get current data details.
Attachments
CSI UA FactSheet Mutual Delisted Symbols 20140613.png
CSI UA FactSheet Mutual Delisted Symbols 20140613
CSI UA FactSheet Mutual Delisted Symbols 20140613.png (40.46 KiB) Viewed 5603 times

Roger Rines
Roundtable Knight
Roundtable Knight
Posts: 1946
Joined: Wed Oct 06, 2004 10:52 am
Location: San Jose, CA

Post by Roger Rines » Fri Jun 13, 2014 2:15 pm

Here are some additional detail sent by Josh Reed, CSI's UA developer.

Symbol's Active and Delisted coding isn't visible in the UA Catalog, but it is present in the SDBFacts.adm file.

Below are two record examples showing a delisted and active symbol.

Notice that the Active/Delisted designation is in the field just prior to the last data date:

Code: Select all

1922,DGLBX,MUTUAL,Dreyfus Intl Gr Fd Cl B,+2,,,,,,1,,,,,,USD,19940728,,,,I,20091014,,,,,,,,,,,,,Requires pricing...,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,19940728,,,, -, -,,,,, 0

1921,SESIX,MUTUAL,SB Social Awareness Fund Cl B,+2,,,,,,1,,,,,,USD,19870130,,,,A,20140612,,,,,,,,,,,,,Requires pricing...,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,19870130,,,, -, -,,,,, 0 
When the above example records are loaded into the Excel, column counting shows us the Active/Delisted field is column: 22
Attachments
SDBFacts Field Example 20140612.png
SDBFacts Field Example 20140612
SDBFacts Field Example 20140612.png (16.24 KiB) Viewed 5588 times

AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

Post by AFJ Garner » Fri Jun 13, 2014 5:06 pm

Thanks Roger.
The problem is the portfolio builder wizard. It is misleading as regards Mutual Funds. Choose mutual funds and it leaves out de-listed mutual funds. Choose US de-listed stocks and it does not include de-listed/defunct mutual funds. The only way to access them is as you describe above by choosing those whose end date is in the past.

Not worth worrying about too much but irritating nonetheless!

Roger Rines
Roundtable Knight
Roundtable Knight
Posts: 1946
Joined: Wed Oct 06, 2004 10:52 am
Location: San Jose, CA

Post by Roger Rines » Fri Jun 13, 2014 6:42 pm

Where the "Delisted" problem becomes more than a small issue is when the symbol disappears from a historical performance after a Stock Dictionary update, or on the next day after it has been Delisted and the dictionary is updated.

Symbols not in the Stock Dictionary are hidden from Trading Blox even when they are the directory folder.

TB is aware of this issue so it should go away before too long.

AFJ Garner
Roundtable Knight
Roundtable Knight
Posts: 2040
Joined: Fri Apr 25, 2003 3:33 pm
Location: London
Contact:

Post by AFJ Garner » Sat Jun 14, 2014 6:45 am

Ah!

Just the sort of problem I'm trying to avoid. Using a combination of Python and mySQL we are building our own database which will only allow updates not retrospective changes.

Instead of re-building the stocksinfo.txt file I guess one has to manually adapt for changes - and only for additions, IE IPOs.

I have created one huge dictionary for use in all my folders. From the CSI facts file. Rather than using the TB build approach for individual portfolios.

Equally, we are building in the ability to simply switch over to another data provider for updates as a fail safe back up.

Currently proposing to use Premium Data.

Post Reply