If you’re an AmiBroker user, you may have spent much time painstakingly creating and editing watchlists of stocks, with all the repetitive pointing and clicking that entails, to get them just right. With that done, and your watchlists for each and every database now organised to perfection, you might now be wondering: “How can I fuck it all up completely in the minimum amount of time?” Got Perl installed? Well then have I got the perfect Perl script for you!

(Because nothing has the power to mess up perfectly good data like a poorly written Perl script! Well, OK, that’s not really true. Regardless of programming language, any determinedly incompetent programmer could no doubt write a script to neatly screw up data and/or sanity with a simple command line execution. Sadly, sometimes this even happens to the best of us. Which is a roundabout way of saying having a backup of any data before running experimental scripts to mess with that data might be a good idea. I should also point out what follows is intended mostly as an attempt to get my own thoughts in order, rather than being any practical use to anyone else, as I realise the potential audience of Perl-programming AmiBroker users is small to non-existent. This is merely an idea I’ve been messing with.)

My motivation for this script (and this post) is a frustration with the unnecessarily repetitive pointing-and-clicking that many GUI applications inflict on their users (namely, me). Charting application are especially prone to this, and given their visual nature this might be somewhat unavoidable. But it seems to me even well designed charting applications — and I consider AmiBroker to be an excellent example of the genre — have their user interface annoyances. Or maybe I’ve just been spending too much time in the command-line world of Linux lately. Or maybe I expect things from charting applications that no one else does. Or maybe I do things with watchlists that no one else wants to do. But in any case, I have yet to find a charting application that provides some way to graphically maintain “watchlists” of stocks without making me pine for a command line alternative.

The annoyance in this case some might consider a minor one, but it annoyed me enough to make me turn to two things that I often find even more annoying — Excel and Perl.

Here’s the thing. Not counting databases for indices, I have 4 AmiBroker databases set up — one ASX database for Australian stocks, and three US databases, one for each of the major exchanges (NYSE, NASDAQ and AMEX, though can’t remember the last time I looked at an AMEX stock chart). Since each database has its own separate sets of watchlists, that means 4 sets of watchlists to maintain. (Note I’m talking about the names and order of the watchlists for each AmiBroker database, not the individual stocks, which obviously will be different for each exchange).

In an effort to keep on top of the more mundane record keeping tasks of trading, while spending as little time as possible doing so, I have been becoming more militant with maintaining watchlists. In some cases, I might want the same semantic watchlist across all 4 database — for example, a watchlist for currently open trades, so I can quickly scroll through charts of all my current trades with minimal keystrokes/mouseclicks. In other cases, however, I might have a watchlist that is only specific to ASX, and not to the US exchanges — for example, a watchlist for Australian resource companies. So in general, I want the AmiBroker databases for the 3 US exchanges to have identical watchlists, but the ASX one to be a bit different. Of course, I can do this manually. By pointing and clicking. But I am a coder. That’s not how I roll.

So I wrote a script.

Fortunately, AmiBroker uses a simple text-based watchlist format that lends itself to programmatic hacking. Within each AmiBroker database directory, there will a subdirectory for “watchlists”. Within this directory, there will be a file called index.txt. This is a simple text file, where each line is the name of a watchlist, specific to the database in question. There can apparently be any number of watchlists. The actual stocks that comprise each watchlist are stored in separate data files — for each entry in index.txt, there should be a corresponding text file with a .tls extension, which is simply a list of stock codes comprising that watchlist. But these tls files don’t concern us for now.

Of course, all this is current at the time of writing, and subject to change. But with keen awareness of the hackiness of it all, I created a spreadsheet with the names of watchlists, arranged how I wanted them, then wrote a script to export that spreadsheet into AmiBroker watchlist index files. This feels hacky, and will obviously break if future versions of AmiBroker handle watchlists differently, but for now it achieves its intended purpose of saving me some tedious mouse clicking. It means when I want to add, delete, or rearrange ordering of watchlists, I can far more quickly edit the spreadsheet and run the script. (Before backing up the AmiBroker databases first, of course! And making sure AmiBroker isn’t currently running at the time, or Bad Things Could Happen.)

For anyone who’s interested, here is the script:

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel;

#
# Check and extract command line arguments -- or setup OS-specific defaults.
#
my $g_infile = "AmibrokerWatchlists.xls";
my $g_dir = "watchlistdb";

if($#ARGV >= 1) {
    $g_infile = $ARGV[0];
    $g_dir = $ARGV[1];
}
else {
    die "Usage: $0 <infile.xls> <outdir>\n";
}

#
# Column 0 is index number of watchlist.
# Column 2 is ASX
#        3 is NYSE
#        4 is NASDAQ
#        5 is AMEX
#

#
# Indexes of columns in spreadsheet, and corresponding exchange names.
#
my $CI_INDEX    = 0;
my $CI_ASX      = 2;
my $CI_NYSE     = 3;
my $CI_NASDAQ   = 4;
my $CI_AMEX     = 5;

my %exchange;
$exchange{"ASX"}        = $CI_ASX;
$exchange{"NYSE"}       = $CI_NYSE;
$exchange{"NASDAQ"}     = $CI_NASDAQ;
$exchange{"AMEX"}       = $CI_AMEX;

my %watchlist;

unless (-d $g_dir) {
    mkdir($g_dir) or  die "Can't create $g_dir directory: $!";
}

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($g_infile);
if (!defined $workbook) {
    die $parser->error(), ".\n";
}

#
# Extract data from spreadsheet.
#
my $sheet = $workbook->worksheet(0);
my ($cell, $wname, $windex);

my ($row_min, $row_max) = $sheet->row_range();
my ($col_min, $col_max) = $sheet->col_range();

if ($row_min + 5 >= $row_max) {
    die "Invalid input file: not enough rows!";
}

foreach my $row ($row_min+5 .. $row_max) {
    #
    # Get value of first column.
    #
    $cell = $sheet->get_cell($row, $CI_INDEX);
    next if (!defined($cell)); # Skip row if the first column is blank
    $windex = $cell->value();

    print "$windex:\t";

    while (my ($e, $i) = each(%exchange)) {
        $cell = $sheet->get_cell($row, $i);
        if (!defined($cell)) {
            $wname = "List$windex";
        }
        else {
            $wname = $cell->value();
            $wname =~ s/^\s+//;
            $wname =~ s/\s+$//;
        }
        push(@{$watchlist{$e}}, $wname);
        print "$wname ($e)\t";
    }
    print "\n";
}

print "\n=====\n\n";

#
# Generate output files.
#
foreach my $k (keys %exchange) {
    if (-d "$g_dir/$k") {
        # Delete any existing TLS files in the directory.
        my @existing = <$g_dir/$k/*.tls>;
        for my $tls (@existing) {
            print "Deleting: $tls\n";
            unlink($tls);
        }
    }
    else {
        mkdir("$g_dir/$k") or  die "Can't create $g_dir directory: $!";
    }

    open(OUTPUT, ">", "$g_dir/$k/index.txt") || die "Can't open output file: $!";

    foreach my $l (@{$watchlist{$k}}) {
        print OUTPUT $l, "\n";
    }

    close(OUTPUT);
}

(View as a gist here)

The idea here is fairly simple. The script generates index files for each database based on the first worksheet in the spreadsheet file. It outputs one index file for each database (i.e. stock exchange), placing each one in a subdirectory with the database name. It doesn’t directly modify or overwrite any files in an AmiBroker database (I’ve got another script for that!), nor does it attempt to even mimic the proper directory structure. All it does is output index.txt files which can then be (manually, and carefully!) copied to the appropriate subdirectories within the respective AmiBroker databases.

The first 5 rows of the spreadsheet are ignored, so they can contain any arbitrary header information for human consumption.

The first column (from row 6 onwards) must contain either a number, or be blank. If it’s blank, that entire row is ignored. If it has a number, then this number represents a watchlist index number. Column B is ignroed. Columns C to F contain the names of watchlists for databases ASX, NYSE, NASDAQ, AMEX (in that order). So if column A has an index number, then columns C to F in that row represent the watchlist names with that index number, for all the respective databases.

Since I like to have all the US watchlists identical, I could have made the script more specific and just had 2 columns — one for ASX, and one for US databases, but I decided to implement the more general case, even if it means having to do a fill in Excel after editing US watchlists.