Generate AmiBroker watchlist index files from data in spreadsheet. http://www.voidynullness.net/blog/2012/03/03/generate-amibroker-watchlist-indexes-from-spreadsheet/

amibroker_excel2watchlist.pl

#!/usr/bin/perl
#
#  http://www.voidynullness.net/blog/2012/03/03/generate-amibroker-watchlist-indexes-from-spreadsheet/
#
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);
}