AFL script for deleting inactive symbols from database

Hi Amibroker users,
First of all, many thanks to Tomasz for creating this powerful and useful software.
I have around 1700 symbol that I need to delete. Halfway through symbols that start with A, I gave up deleting manually. So, I google for AFL script and found this.

// Read from del_tickers.txt file and delete symbol from database.
// del_ticker.txt is saved at desktop
// Apply = *All Symbols
// Range = All Quotes
// Click Parameters icon and click on "Click Here To Delete Tickers" to delete

DeleteTickers = ParamTrigger("Delete Tickers", "Click Here To Delete Tickers");
 
if ( DeleteTickers )
{
	fh = fopen("C:\\del_tickers.txt", "r");
	if (fh) {
		 oAB = CreateObject( "Broker.Application" );
		 oStocks = oAB.Stocks();
		 i=1;
		 while( ! feof( fh ) )
		 {
			Ticker = fgets( fh ); 
			Ticker = StrReplace(Ticker, "\n", "");
			oStocks.Remove( Ticker );
			i++;
		 }
		 fclose( fh );
     }
     else
     {
		printf("ERROR: del_tickers.txt can not be found (does not exist)");
     }
     oAB.RefreshAll();
}

In the original script, the inactive symbols are in a text string separated by comma and using for loop. I modify this script to read the inactive symbols from a text file and use a while loop. I use StrReplace to remove the carriage return ("\n"). Not sure if this is the proper regex to use.

So, what’s the problem with this script ? It works but it has some quirks. It might not work at the first run. But it will work at the 2nd or 3rd run. Any tips to fix this is much appreciated. Or any other alternative approach to mass delete is also appreciated. Thanks.

1 Like

@Peter2047 hello
If I understand well you are asking to delete the folder A from your database but NOT manually. Right?
If so the answer is here by fxshrat

also in your code you have

    AB.RefreshAll(); 
    // AVOID THIS and use RequestTimedRefresh() 
    //or SetOption("RefreshWhenCompleted", True )

@PanoS,

Sorry but that is a not a good idea.

My post to Sean was not related to DB folders even more so it was not related to applied DB folders.

Don’t do that.

Also using OLE within AFL (as in first post) is not recommended.

If you have inactive symbols you should use Database purify tool of AmiBroker (Tools - Database purify) to check for missing or no quotes. Then you can move all those results to watchlist of your choice. Once they are in watchlist you can select them all (the symbols) and right click and choose “Delete” option removing them from DB. (Or you may keep those symbols and exclude watchlist from analysis).

Instead of “Database purify” tool you could alternatively write a custom exploration code checking for last active date and you either move all symbols that don’t pass the set date test to watchlist programmatically (via CategoryAddSymbol AFL function) or you may mark the exploration result list and right click it to move selected rows to watchlist. Then again you do same procedure as above… Once they are in watchlist you can mark all its symbols and right click and choose “Delete” option of context menu.

Those are the safest methods, IMO.

PS: And if you want to use OLE then you should run it from outside of AB and you may call that non AFL program via ShellExecute() AFL function.

BTW, SetOption(“RefreshWhenCompleted”, True ) is applied in analysis only.

2 Likes

Thank you PanoS and fxshrat.

To delete in-active symbol manually, I put the chart cursor to right of an active symbol. I use arrow up or down to navigate through the symbol… one at a time. If the cursor disappear, I right click the symbol and select delete. It’s a tedious and haphazard way. So, I found this script. I suppose it’s OK to use it for a few symbols.
https://www.mail-archive.com/amibroker@yahoogroups.com/msg46479.html
It behaves erratically when the symbol list becomes very long.

Thanks for pointing to the solution of send exploration results to watchlist. In fact, I have already been using exploration to screen out those in-active symbols. I just need to right click on the Exploration Results and select “Add all results to watch list…”. From the watch list, highlight all symbols and right click and select delete. Done ! A lot faster than using script.

You should be using BUILT-IN tools instead of writing unreliable formulas taken from who-knows-where.

Use the following:

  1. Tools->Database Purify to generate list of inactive symbols and other data errors

  2. Right click on the result list "add all symbols to watch list"

    image

  3. Then you can delete all symbols from watch list (for example using assignment organizer or just straight from Symbols window (multi-select - click first, hold down SHIFT key and click last).

If you want to delete symbols that you already have in a file - IMPORT that file to watch list and delete symbols from that watch list. It is faster than any other method.
Recommended reading is the Users Guide Working with Watchlists
http://www.amibroker.com/guide/h_watchlist.html

Really entire tutorial is MUST-READ. Without reading the tutorial you will NEVER use AmiBroker efficiently.

3 Likes

My in-active list setting for data purity should be this.
Data_purity

My exploration AFL for in-active symbol list. Any symbols that do not have current data should be flagged.

// Set current year, current day and current month to the latest date
// Apply to = All symbols
// Range = 1 recent bar

Current_year = 2017;
Current_day = 19;
Current_month = 9;
Day_array=Day();
Month_array=Month();
Year_array=Year();
Filter = 0;
if (Year_array[BarCount - 1] < Current_year) {
	Filter = 1;
}
else {
	if ((Year_array[BarCount - 1] == Current_year)AND(Month_array[BarCount - 1] < Current_month)) {
		Filter = 1;
	}
	else {
		if ((Year_array[BarCount - 1] == Current_year)AND(Month_array[BarCount - 1] == Current_month)AND(Day_array[BarCount - 1] < Current_day)) {
			Filter = 1;		
		}
	}
}

You don’t understand. There is NO NEED for exploration. All work is already done with Data Purify.

1 Like

Sorry.
I am not looking for tickers with missing quote. I am look for symbols that are delisted or merged or taken over. These stocks no longer have any future EOD data. I tried your data purity setting on my system and many active symbols do appear.

@Peter2047

Firstly, the correct way to ascertain delisted/ merged/ suspended tickers is either through your data provider or information from your exchange's site!

Secondly, amibroker provides you with a 'heads up' if you are not in the habit of periodically maintaining your database! The tool for the same is inbuilt and is available in the 'tools' menu itself

cleanup

In addition,
do refer, https://www.amibroker.com/newsletter/05-2000.html

@zbin,
I manually scan through 5000+ charts after each trading day. Obviously, I am aware of changes since I looking for them. I left those symbols without current EOD data in the database for my back testing. Now that I am done with back testing, I have to remove them(1700+) in order to cut my manual scanning time by 45 minutes. Going forward, I can do it manually as it is not a common occurrence.

@Peter2047

Did you check out the reference?!

The quoted tool does what you require programmatically!

The script has a built-in user input requirement as a 'check-safe' operation

But if you REALLY KNOW what you're doing then the script can be modified to delete identified ticker(s) without requiring user input

The reason I am emphasizing on the 'REALLY KNOW' part is because thinly traded markets may not trade for a number of days and thus data not being available for a single day (as indicated by the 'most recent bar' in your code) is no guarantee that the ticker is delisted/ suspended etc. Even actively traded markets may not trade for a day or two due to various corporate actions (differs from exchange to exchange as per extant regulations). Some tickers may appear to be inactive just because you missed out the symbol/ name change down the line. Deleting those will end up in losing old data for a perfectly valid (albeit changed) ticker.

That is why the Cleanup database tool has a default inactive window of a month (another 'fail-safe' check which too can be configured, as required, in the script). The correct way is to generate inactive tickers by running the tool (it generates a text list upon user input) and then verifying the list vis-a-vis the concerned exchange.

However, if you're really sure of what you're doing then simply do as @Tomasz has instructed

If you want to delete symbols that you already have in a file - IMPORT that file to watch list and delete symbols from that watch list. It is faster than any other method.

Then you can delete all symbols from watch list (for example using assignment organizer or just straight from Symbols window (multi-select - click first, hold down SHIFT key and click last).

And press 'Delete'

sym_delsym_del2

1 Like

@Peter2047 your problem is not how many Symbols you have in your database (you can have as many symbols as you want) but which subgroup do you select when you run a scan/exploration/backtest or when you manually browse through charts. You don’t have to necessarily delete/remove not active symbols (but of course you can). Just don’t include them in the watchlists that you use. You can have 100 000 Symbols in your database, but perform all operations only on 10, 100, 1000 or 5000 stocks that you want. That’s all. For example if you want to identify the stocks which have not been traded in the last 2 months, and make a watchlist that include all other active symbols you can do it in a matter of seconds using Exploration.

And just to make clear - do you browse the charts manually after each session? That’s the reason why it takes you so much time?

https://www.amibroker.com/guide/h_watchlist.html
http://www.amibroker.com/kb/2014/11/13/how-to-add-exploration-results-to-a-watchlist/
http://www.amibroker.com/kb/2014/12/23/using-multiple-watchlists-as-a-filter-in-the-analysis/

1 Like

I want to identify the stocks that are traded every month in an analyzed period, in the explorer. Can someone help me with a code?

@Tomasz and other's

I executed the "Database Purify" and added all symbols to watch list". I saw the symbol list in the watchlist was 668 (out of 1800+ NSE India symbols) and some were good companies trading even now. Then i sorted on date and added only stocks whit the latest date. Now the watchlist came down 75+ Symbols. Now i see most of stocks not being traded currently but even here i see some stocks that were trading on the latest date (28th Jan 2022) like "AHLWEST.NS, JIKIND.NS. I observed that most of these symbols are low volume stocks which have hit circuit and no trading done (As shown in the Image. But few were normal ones without Circuit being hit like "DOLAT.NS".

Can you share your thoughts?

NOTE: I am using EOD data from Yahoo using Amiquote.

image