CSI Stocks: Active/Inactive
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
CSI Stocks: Active/Inactive
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.
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.
-
- Roundtable Knight
- Posts: 2038
- Joined: Wed Oct 06, 2004 10:52 am
- Location: San Marcos, CA
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.
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
- CSI Preferences Market Specs Delisted Append Apostropy.png (27.68 KiB) Viewed 8652 times
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
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!
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
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!
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
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.
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.
-
- Roundtable Knight
- Posts: 2038
- Joined: Wed Oct 06, 2004 10:52 am
- Location: San Marcos, CA
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.
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
- CSI UA FactSheet Mutual Delisted Symbols 20140613.png (40.46 KiB) Viewed 8636 times
-
- Roundtable Knight
- Posts: 2038
- Joined: Wed Oct 06, 2004 10:52 am
- Location: San Marcos, CA
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:
When the above example records are loaded into the Excel, column counting shows us the Active/Delisted field is column: 22
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
- Attachments
-
- SDBFacts Field Example 20140612
- SDBFacts Field Example 20140612.png (16.24 KiB) Viewed 8621 times
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
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!
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!
-
- Roundtable Knight
- Posts: 2038
- Joined: Wed Oct 06, 2004 10:52 am
- Location: San Marcos, CA
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.
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.
-
- Roundtable Knight
- Posts: 2071
- Joined: Fri Apr 25, 2003 3:33 pm
- Location: London
- Contact:
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.
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.