As promised, below is a script that can be used to detect remove bad tickers and categories that you do not want. As the script runs, it cleans up the target Watchlists, finds “bad” tickers, categorizes them by type (Bad Ticker or Bad Category), and loads them to the target Watchlist for deletion.
After it runs, you can DELETE the tickers from either or both Watchlists. Note that “removing” a ticker from a Watchlist does NOT delete it. To DELETE a bunch of tickers, the easiest way (only way?) is to “delete” them from the “Assignments Organizer” window.
Here’s how to use it:
ONE TIME PREP:
1) Select two Watchlists and rename them to “Bad Ticker” WatchLists. Use a name that makes sense to you. I call mine “BadTickers (56)” and “BadCategories (57)”. Note the numbers of the Watchlists.
2) At the top of the AFL script (below), reset the WL Numbers. E.g., if your Bad Tickers Watchlist is “24”, change “WLBadTickers = 56” to “WLBadTickers = 24”.
3) Save the script to a file with a name that makes sense to you. For example, my filename is “Wkly_BadSymbols_SaveToWatchlist.afl”. (I run this weekly.)
RUNNING THE SCRIPT AND DELETING TICKERS:
1) Open the script in the Automatic Analysis (AA) window.
2) Click the “all symbols” radio button in the “Apply to” frame (upper left).
3) Click the “Explore Button”. The script will run and display tickers that are “Bad Ticker”, “Bad Category or “Bad Ticker and Category”. (I got 1546 in my last run.)
4) Review the tickers either in the AA window or in the target Watchlists. (You could “delete” theme here, but that’s a waste of time.)
5) Select the menu “Symbol à Organize Assignments à Watch
lists”.
6) Select the target Watchlist from the Watchlist dropdown in the upper left. E.g., select “Bad Tickers (56)”. You see a list of tickers in the Watchlist.
7) Select the tickers to delete by either <Shift-Click> or <Ctrl-Click>. (Since I want to whack ‘em all, I select using <Shift-Click>.)
8) Click the “Delete” button in the center of the window. A warning window displays saying, “You are about to delete multiple symbols from the DATABASE. Are you sure?” (I’m damn sure.)
9) Click the “OK” button. The system will pause for a few seconds and you should see all of the symbols disappear from the Watchlist (and database).
10) Now select the other Watchlist from the Watchlist dropdown. E.g., “Bad Categories (57)”.
11) Select the target symbols to delete and click the “Delete” button again to delete these symbols.
12) Click “Close”.
After running the process and deleting the symbols, you should have gotten rid of all or almost all “Bad” symbols. You should review your symbols to see if a few slipped through the cracks. (I found one.)
One thing I noticed is that there were still a lot of symbols in the “Undefined” Group. These are valid symbols with valid data, but not useful for me. To get rid of them, return to “Symbol à Organize Assignments à Groups” window and select the “Undefined” group. Select the unneeded symbols and click “Delete”.
This process is pretty simple and fast. After you’ve done it once, it should take about 10 minutes next time.
I hope this works for you.
Best regards,
//Watchlist Numbers
WLBadTickers = 56;
WLBadCategories = 57;
CategoryRemoveSymbol("", categoryWatchlist, WLBadTickers);
CategoryRemoveSymbol("", categoryWatchlist, WLBadCategories);
IssueType = "";
IssueStatus = "";
SymbolName = "";
GroupName = "";
bBadTicker = False;
bBadCategory = False;
TickerFlag = "";
Buy = 0;
SymbolName = FullName();
GroupName = GroupID(1);
BarCountCurrent = BarCount - 1;
if ((BarCount - 2) < 0)
BarCountMinus1Day = BarCountCurrent ;
else
BarCountMinus1Day = BarCount - 2 ;
if ((BarCount - 3) < 0)
BarCountMinus2Days = BarCountCurrent ;
else
BarCountMinus2Days = BarCount - 3 ;
if ((BarCount - 4) < 0)
BarCountMinus3Days = BarCountCurrent ;
else
BarCountMinus3Days = BarCount - 4 ;
if ((BarCount - 22) < 0)
BarCountMinus1Month = BarCountCurrent ;
else
BarCountMinus1Month = BarCount - 22 ;
if ((BarCount - 44) < 0)
BarCountMinus2Months = BarCountCurrent ;
else
BarCountMinus2Months = BarCount - 44 ;
if ((BarCount - 66) < 0)
BarCountMinus3Months = BarCountCurrent ;
else
BarCountMinus3Months = BarCount - 66 ;
if ((BarCount - 126) < 0)
BarCountMinus6Months = BarCountCurrent ;
else
BarCountMinus6Months = BarCount - 126 ;
if ((BarCount - 256) < 0)
BarCountMinus12Months= BarCountCurrent ;
else
BarCountMinus12Months= BarCount - 256 ;
CurPrice = C[BarCountCurrent ] ;
PriceMinus1Day = C[BarCountMinus1Day ] ;
PriceMinus2Days = C[BarCountMinus2Days ] ;
PriceMinus3Days = C[BarCountMinus3Days ] ;
PriceMinus1Month = C[BarCountMinus1Month ] ;
PriceMinus2Months = C[BarCountMinus2Months ] ;
PriceMinus3Months = C[BarCountMinus3Months ] ;
PriceMinus6Months = C[BarCountMinus6Months ] ;
PriceMinus12Months = C[BarCountMinus12Months] ;
if (CurPrice == PriceMinus1Day
AND CurPrice == PriceMinus1Day
AND CurPrice == PriceMinus2Days
AND CurPrice == PriceMinus3Days
AND CurPrice == PriceMinus1Month
AND CurPrice == PriceMinus2Months
AND CurPrice == PriceMinus3Months
AND CurPrice == PriceMinus6Months
AND CurPrice == PriceMinus12Months
)
bBadTicker = True;
bBadTicker = Nz(LastValue(bBadTicker ));
if (bBadTicker )
{
CategoryAddSymbol( "" , categoryWatchlist, WLBadTickers); //USTradable Symbols = 1
}
if ( GroupName == "Preferred stocks"
OR GroupName == "Warrant or right"
OR GroupName == "Mutual or inv. trust fund"
OR GroupName == "Convertible preferred"
OR GroupName == "Certificate"
OR GroupName == "Unit"
)
bBadCategory = True;
bBadCategory = Nz(LastValue(bBadCategory ));
if (bBadCategory )
{
CategoryAddSymbol( "" , categoryWatchlist, WLBadCategories); //USTradable Symbols = 1
}
if (bBadTicker OR bBadCategory)
Buy = 1;
if (bBadTicker AND bBadCategory)
TickerFlag = "Bad Ticker and Category";
else
{
if (bBadTicker)
TickerFlag = "Bad Ticker";
if (bBadCategory)
TickerFlag = "Bad Category";
}
Filter=Buy;
SetOption("NoDefaultColumns", True );
AddTextColumn(TickerFlag, "TickerFlag");
AddTextColumn(Name(), "Symbol");
AddTextColumn(SymbolName, "Company");
AddTextColumn(GroupName, "Group");
AddColumn(CurPrice, "CurPrice");
AddColumn(PriceMinus1Day, "PriceMinus1Day");
AddColumn(PriceMinus2Days, "PriceMinus2Days");
AddColumn(PriceMinus3Days, "PriceMinus3Days");
AddColumn(PriceMinus1Month, "PriceMinus1Month" );
AddColumn(PriceMinus2Months, "PriceMinus2Months" );
AddColumn(PriceMinus3Months, "PriceMinus3Months" );
AddColumn(PriceMinus6Months, "PriceMinus6Months" );
AddColumn(PriceMinus12Months, "PriceMinus12Months" );
From: amibroker@xxxxxxxxxxxxxxx [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of dan_public@xxxxxxxxxxx
Sent: Tuesday, September 27, 2005 7:03 PM
To: amibroker@xxxxxxxxxxxxxxx; amibroker@xxxxxxxxxxxxxxx
Cc: Ernie Newman
Subject: Re: [amibroker] Clean Up
Database
I see two categories of symbols that I don't want - those with no quotes or static quotes, and those that are not useful to me. I currently have an AFL script that looks for the first, but not the second.
Tonight, I'm going to modify my AFL script so that it will:
1) Find all symbols with no prices or static prices in the last month.
2) Find all symbols in categories in which I'm not interested (Preferred stocks, Mutual Funds, etc.).
3) Stuff the static symbols in one watchlist.
4) Stuff the "not interested" symbols in another watchlist.
Then it's a simple matter to delete the symbols from the target watchlist. This process should be fewer steps, but still allow review prior to deletion.
When it works smoothly (maybe tonight), I'll post it here.
-------------- Original message --------------
Is there a way to set Clean up database into automatic mode so I don't
have to spent all day deleting symbols that have no data or the data is
not up to date because the company may hve merged or the symbol has
changed. I am using Quotes plus data and after a while it seems the
database loads up with hundreds of these symbols. The only way I see to
delete them is to click on delete for each symbol as it is found my
computer just spent 6 hours just finding them. I can't sit for that
long and delete them.
There must be a better way.
Ernie