----- Original Message -----
Sent: Wednesday,
September 28, 2005 12:27 AM
Subject: RE: [amibroker]
Clean Up Database - AFL Included
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