Database clean up

I have deleted many tickers from symbol list after stocks have been delisted or merged. But ticker symbols is still in database folder “Data”.

I know I can do it manually, but is there a easier way to do this clean up?

The “Delete” seems to work well for me; as soon as I delete a symbol from the program (and confirm the operation) the corresponding file is deleted.
In any case, you may want to look here:

If you are familiar with JavaScript you can also modify it as needed; the “CleanUp.js” script is in the “Scripts” subfolder of the directory where AB was installed.

If for any reason the script does not work for you for you, maybe you should try the following alternative.

This works ONLY if you update your databases frequently (each day, each week, etc) since it will locate files older than x day (where x in the following example is 10 days).

If you update your database in a regular way the “active” tickers files will have a recently modified date: the stale ones (no longer traded, delisted, etc.) will have an older date (you can check this manually ordering the files by the modified date in any DB subfolder).

You need to be familiar with the file system, the OS command line, and batch files (and have batch files execution enabled in your system). In such a case, there is a DOS/Windows command that is worth investigating.

But before doing anything be sure to have a FULL backup copy of your database!

Close any instance of AB.

At the command line prompt, change the working directory to the base folder of your database, and issue the following command:

forfiles /S /D -10 /C "cmd /C Echo @Path" > files_to_delete_older_than_10days.bat

The above command will list all the files (also in subfolders) older the 10 days (and save this list in a text file that since it has the .bat extension will be later used as a batch file).

Then open the resulting batch file with a text editor and delete from it all the lines that refers to the files and folders that should not be deleted (since the database structure/folders and many AB specific files were created before x days ago they will be included the list and you need to remove from the batch the corresponding lines).
Do not remove the line that refers to the “broker.master” file (this file need to be deleted - AB will recreate it automatically when needed).

Once done (double check carefully the list to in order to be very sure that it no longer includes any important/system file or folder except the “broker-master”), use the find/replace command of your text editor to change the beginning of each line to include the DEL command in such a way that each line is now a command to delete a single file like this:

For example replace all the occurrences of :

 "C:\

with

DEL "C:\

In such a way, each line will be similar to this one:

"C:\Program Files (x86)\AmiBroker\DB\USA\a\ACW"

After replace

DEL "C:\Program Files (x86)\AmiBroker\DB\USA\a\ACW"

(Each line of the batch now is a DEL command to delete a ticker data file).

Save the file and launch the batch file. When the batch execution is complete, reopen AB.
Done.

I use this way to delete stale tickers, from time to time, from multiple databases (I used one DB for each market, all of them under a common base folder).

While the provided “Cleanup database” script (available under the “Tools” menu in AB) does the same thing in any individual database (and is the recommended way to go), I prefer this procedure in order to keep the list of all the deleted/old tickers from multiple DBs in a single text file).

3 Likes

I forgot one important point. Amibroker is very smart since it is capable of removing automatically from its watchlists the missing tickers (I mean the ones deleted via the batch file).

In any case, the BUILT-IN script is for sure safer and, as per the documentation, takes care also fo any tickers with bad data.
(The above procedure is a brute force method that only uses a cut-off date to decide what to keep and what to delete).

1 Like

It won't remove the ticker, it won't display it, but will remain in the watchlist file. If you delete broker.master then very likely it will be removed from the watchlist. I'm loathe to test that out to see. Either way, the safest way is not to delete broker.master if you just want to wipe the historical data (which is the only way to deal with the wrongful merging of old and new history under same ticker, a horribly common problem with newly-listed ETP's).

  • broker.master - binary data file containing the table of all symbols available in the database. Used for quick loading of symbols. The table of symbols includes information about assignments of symbols to categories (markets, groups, sectors, industries, watch lists, indices and favorites). If you delete this file AmiBroker will re-create it because this information is available also in the individual symbol data files.

@traderx this other thread discusses in details how Amibroker deals with the watchlists.
As I wrote, the method I described here is NOT the recommended way to clean a database. If using it, to avoid the problems indicated in the linked discussion, I suggest also to delete the existing watchlists and rebuild them as needed.