#!/Perl
#
use LWP::Simple;
my $URL = 'http://www.cbot.com/cbot/pub/page/0,3181,1196+2006N,00.html';

# Save workbook to file - ensure path exists
my $FileBase = 'autoexcel05';
my $OutPath = 'c:\tmp';
my $OutBase = "$OutPath\\$FileBase";

my $Filename = $OutBase.'.xls';
my $write_graph = 1;
my $Filter = 'GIF';
my $Count = 1;
my $write_html = 1;
my $write_bars = 0; # output the list

# Start Excel and create new workbook with a single sheet
use Win32::OLE qw(in valof with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);

my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
$Win32::OLE::LCID = MAKELCID($lgid);

# just for LOG FILE ouput ...
my $out_lines = 0; # for DEBUG only ;=))
my ($LOG);
my $write_log = 0;
my $verb = 1; # increase to 9 to see more output
my $outfile = "temp.$0.txt"; # note name of perl file used as base
if ( open( $LOG, ">$outfile" ) ) {
    $write_log = 1; # we have a LOG file
} else {
    $write_log = 0;
    prt( "WARNING: Unable to open $outfile LOG ...\n" );
}

$Win32::OLE::Warn = 3;

my $secs = 900;	# sampling interval
my @Bars = ();
prt("Fetching text from $URL ...\n");
my $text = get("$URL");
my $tcnt = length($text);
prt( "Got $tcnt characters ...\n");
while( $text =~ /\n/ ) {
	$text =~ s/\n/<br>/g;
}

prt("Processing split on <br> ...\n");
## foreach (split "\n", $text)
$tcnt = 0;
foreach (split "<br>", $text) {
    # 03/12/1998 C 98Mar 12116 15:28:34 Open
    my ($Date,$Price,$Hour,$Min,$Sec,$Amp,$Ind) =
         m|^\s*(\d+/\d+/\d+)     # " 03/12/1998"
            \s+\S+\s+\S+\s+(\d+)   # " C 98Mar 12116"
            \s+(\d+):(\d+):(\d+) # " 12:42:40"
			\s+(\w{2})           # "AM-PM"
            \s*(.*)$|x;          # " Ask"
    next unless defined $Date;
	$tcnt++;
    $Day = $Date;
	if (($Amp =~ /PM/i) && ($Hour < 12)) {
		$Hour += 12;
	}
	if ($out_lines) {
		prt("$tcnt D[$Date],P[$Price],H[$Hour],M[$Min],S[$Sec],A[$Amp],I[$Ind] ...\n"); 
	}
    # Convert from fractional to decimal format
    $Price = int($Price/100) + ($Price%100)/32;
	
    # Round up time to next multiple of 15 minutes
    my $NewTime = int(( $Sec + ($Min*60) + ($Hour*3600)) / ($secs+1)) * $secs;
    unless (defined $Time && $NewTime == $Time) {
		if (defined $Time) {
			prt( "Pushing $hhmm $Open $High $Low $Close ...\n" );
	        push @Bars, [$hhmm, $Open, $High, $Low, $Close];
		}
        $Open = $High = $Low = $Close = undef;
        $Time = $NewTime;
        my $Hour = int($Time/3600);
        $hhmm = sprintf "%02d:%02d", $Hour, $Time/60-$Hour*60;
    }
	
    # Update 15 minute bar values
    $Close = $Price;
    $Open = $Price unless defined $Open;
    $High = $Price unless defined $High && $High > $Price;
    $Low = $Price unless defined $Low && $Low > $Price;
}

mydie( "No data found" ) unless defined $Time;

prt( "Final pushing $hhmm $Open $High $Low $Close ...\n" );
push @Bars, [$hhmm, $Open, $High, $Low, $Close];

prt("Loading Excel Application ...\n");
my $Excel = Win32::OLE->new('Excel.Application', 'Quit')
or mydie( "Error: Failed to load excel ...\n" );

$Excel->{SheetsInNewWorkbook} = 1;
prt( "Adding a workbook ...\n" );
my $Book = $Excel->Workbooks->Add;
prt( "Getting and naming the sheet ...\n" );
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'Corn';

# Insert column titles
prt( "Insert column titles ... bold font ...\n" );
my $Range = $Sheet->Range("A1:E1");
$Range->{Value} = [qw(Time Open High Low Close)];
$Range->Font->{Bold} = 1;

prt( "Formatting ranges ...\n" );
$Sheet->Columns("A:A")->{NumberFormat} = "h:mm";
# Open/High/Low/Close to be displayed in 32nds
$Sheet->Columns("B:E")->{NumberFormat} = "# ?/32";

# Add 15 minute data to spreadsheet
prt( "Adding ".scalar @Bars." columns of data ...\n");
$Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars);
$Range->{Value} = \@Bars;

# Create candle stick chart as new object on worksheet
prt( "Selecting range for chart creation ...\n" );
$Sheet->Range("A:E")->Select;

prt( "Add the chart ... as xlStockOHLC chart ...\n" );
my $Chart = $Book->Charts->Add;
$Chart->{ChartType} = xlStockOHLC;
$Chart->Location(xlLocationAsObject, $Sheet->{Name});
# Excel bug: the old $Chart is now invalid!
$Chart = $Excel->ActiveChart;

# Add title, remove legend
prt( "Remove legend, and add title ...\n" );
with($Chart, HasLegend => 0, HasTitle => 1);
$Chart->ChartTitle->Characters->{Text} = "US Corn";

# Set up daily statistics
prt( "Set up the statistics ..." );
$Open  = $Bars[0][1];
$High  = $Sheet->Evaluate("MAX(C:C)");
$Low   = $Sheet->Evaluate("MIN(D:D)");
$Close = $Bars[$#Bars][4];
prt( "Open[$Open] High[$High] Low[$Low] Close[$Close] ...\n" );
prt( "Change tickmark spacing from decimal to fractional ...\n" );
with($Chart->Axes(xlValue),
    HasMajorGridlines => 1,
    HasMinorGridlines => 1,
    MajorUnit => 1/8,
    MinorUnit => 1/16,
    MinimumScale => int($Low*16)/16,
    MaximumScale => int($High*16+1)/16
);

prt( "Set fat candles with only 5% gaps ...\n" );
$Chart->ChartGroups(1)->{GapWidth} = 5;

sub RGB { $_[0] | ($_[1] << 8) | ($_[2] << 16) }

prt( "Set Yellow background, with a solid border ...\n");
$Chart->PlotArea->Border->{LineStyle} = xlContinuous;
$Chart->PlotArea->Border->{Color} = RGB(0,0,0);
$Chart->PlotArea->Interior->{Color} = RGB(246,246,104);

# NOTE: This requires an Add-In to be installed in Excel ...
prt( "Add 1 hour, blue, moving average of the Close series ...\n" );
my $MovAvg = $Chart->SeriesCollection(4)->Trendlines->Add({Type => xlMovingAvg, Period => 4});
$MovAvg->Border->{Color} = RGB(0,0,255);

# Save workbook to file my $Filename = 'c:\tmp\tpj\data[?].xls';
if ( -f $Filename) {
	prt( "Removing existing file $Filename ...\n" );
	unlink $Filename;
}

prt( "Save the sheet as $Filename ...\n" );
$Book->SaveAs($Filename);

if ($write_graph) {
	foreach my $Sheet (in $Book->Sheets) {                # loop through all sheets
		foreach my $ChartObj (in $Sheet->ChartObjects) {  # loop through all chartobjects in the sheet
			my $savename = "$OutBase" . $Count++ . ".$Filter";
			unlink $savename if -f $savename; # remove any existing
			prt( "Exporting $savename ....\n" );

			$ChartObj->Chart->Export({
				FileName    => $savename,
				FilterName  => $filter,
				Interactive => 0});
		}
	}
}

output_html() if ($write_html);
output_bars() if ($write_bars);

prt( "Close the work book ..\n" );
$Book->Close;
prt( "All done ...\n" );
log_close();
exit(0);

sub output_bars {
	$tcnt = scalar @Bars;
	prt( "my \@Bars2 = ();" );
	prt( "sub fill_bars2 {\n" );
	for (my $i = 0; $i < $tcnt; $i++) {
		prt( "\tpush(\@Bars2, [ " );
		for (my $j = 0; $j < 5; $j++) {
			prt( '"'.$Bars[$i][$j].'"' );
			if (($j + 1) < 5) {
				prt(", ");
			}
		}
		prt( "] );\n" );
	}
	prt("}\n");
}

sub output_html {
	my $htmlname = $OutBase . '.htm';
	unlink $htmlname if -f $htmlname;
	open( FH, ">$htmlname") or return;

	prt( "Outputting HTML to $htmlname ...\n" );

	print FH <<"EOF";
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Perl - Automation - OLE - 19 June 2006</title>
<style type="text/css">
<!-- /* Style Definitions */
body {
   margin-left:1cm;
   margin-right:1cm;
   margin-top:0cm;
   margin-bottom:0cm;
}
p.nm {
margin:0cm;
margin-bottom:.0001pt;
}
p.cn {margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
}
.bld { font-weight:bold; }
.cnb2 { font-family:"Courier New"; font-weight:bold; } 
.cnb { font-family:fixedsys; } 
-->
</style>

</head>

<body>

<h1 align="center">Perl - Automation - OLE - 19 June 2006</h1>

<p>This data came from the site <a href="$URL" target="_blank">$URL</a> ... all right reserved 
by the data owner, the Chicago Board of Trade. This is only a demonstration using a Perl 
script, with Win32::OLE on a Microsoft Excel OLE object ...</p>

<table border="1" summary="US Corn Futures" bgcolor="#eeeeee">
<caption>US Corn Futures</caption>
<tr>
	<td><b>Time</b></td><td><b>Open</b></td><td><b>High</b></td><td><b>Low</b></td><td><b>Close</b></td>
</tr>

EOF

	my $grafname = "$FileBase" . "1" . ".$Filter";
	$tcnt = scalar @Bars;
	for (my $i = 0; $i < $tcnt; $i++) {
		print FH "<tr>\n";
		for (my $j = 0; $j < 5; $j++) {
			print FH "<td>".$Bars[$i][$j]."</td>\n";
		}
		print FH "</tr>\n";
	}

	print FH "</table>\n";

	print FH "<p>Graph of the above data ...<p>\n";

	print FH "<p><img src='$grafname' alt='US Corn Futures Graph'></p>";

	print FH <<"EOF";

<p>All generated using a Perl script, $0, and OLE automation of Microsoft Excel application. The 
download requires LWP::Simple; the parsing of the downloaded page is done in Perl; also 
uses Win32::OLE qw(in valof with); and Win32::OLE::Const 'Microsoft Excel'; the chart used is an 
xlStockOHLC chart; the trendline added to the graph requires the Analysis ToolPack add-in to 
be loaded in Excel -> Tools -> add-in ...\n" );
</p>

<p>&nbsp;</p>

</body>

<!-- Perl generated HTML, from Web download, using Microsoft Excel OLE automation
geoff mclane - 19 June 2006 -->

</html>
EOF

	close FH;
}

################################
### output and log file
sub wlog {
	my $ml = shift;
	print $LOG $ml;
}

sub prt {
	my $m = shift;
	if ($write_log) {
		wlog($m);
	}
	print STDOUT $m;
}

sub mydie {
	my $msg = shift;
	if ($write_log) {
		wlog($msg);
	}
	die $msg;
}

sub log_close {
	if ($write_log) {
		close( $LOG );
	}
}

sub close_log {
	if ($write_log) {
		prt( "Closing LOG file, and passing to 'system($outfile)'\nMay need to CLOSE notepad to continue ...\n" );
		log_close();
		system( $outfile );
	}
}


# eof - autoexcel05.pl

