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
Please note that this group is for discussion between users only.
To get support from AmiBroker please send an e-mail directly to
SUPPORT {at} amibroker.com
For other support material please check also:
http://www.amibroker.com/support.html
YAHOO! GROUPS LINKS