Last active
December 9, 2021 19:03
-
-
Save robulouski/7692098 to your computer and use it in GitHub Desktop.
Generate AmiBroker watchlist index files from data in spreadsheet. http://www.voidynullness.net/blog/2012/03/03/generate-amibroker-watchlist-indexes-from-spreadsheet/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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); | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment