# Sample code from the Win32::OLE article in "The Perl Journal" #10

use strict;
$| = 1;

my $Contract = 'us8m';
my $text;
if (1) {
    # Offline debugging
    $text = `cat tsf$Contract.htm`;
}
else {
    use LWP::Simple;
    my $URL = 'http://www.cbot.com/mplex/quotes/tsfut';
    $text = get("$URL/tsf$Contract.htm");
}

my ($Day,$Time,$hhmm,$Open,$High,$Low,$Close,@Bars);
foreach (split "\n", $text) {
    my ($Date,$Price,$Hour,$Min,$Sec,$Ind) =
      # 03/12/1998  US   98Mar   12116  15:28:34  Open
      m|^\s*(\d+/\d+/\d+)        # "  03/12/1998"
	\s+US\s+\S+\s+(\d+)      # "  US  98Mar  12116"
	\s+(\d+):(\d+):(\d+)     # "  12:42:40"
	\s*(.*)$|x;              # "  Ask"
    next unless defined $Date;
    $Day = $Date;

    # Convert from implied 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)/900+1)*900;
    if (!defined $Time || $NewTime != $Time) {
	push @Bars, [$hhmm, $Open, $High, $Low, $Close]
	  if defined $Time;
	$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;
}

die "No Times & Sales data found" unless defined $Time;
push @Bars, [$hhmm, $Open, $High, $Low, $Close];

# 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);

$Win32::OLE::Warn = 3;

print "Start Excel\n";
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{SheetsInNewWorkbook} = 1;
my $Book  = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'Candle';

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

$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
print "Add data\n";
$Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars);
$Range->{Value} = \@Bars;

# Create candle stick chart as new object on worksheet
print "Create chart\n";
$Sheet->Range("A:E")->Select;
my $Chart = $Book->Charts->Add;
$Chart->{ChartType} = xlStockOHLC;
$Chart->Location(xlLocationAsObject, $Sheet->{Name});
# Excel bug: old $Chart has become invalid now!
$Chart = $Excel->ActiveChart;

# Add title, remove legend
with($Chart, HasLegend => 0, HasTitle => 1);
$Chart->ChartTitle->Characters->{Text} = "US T-Bond";

# Setup daily statistics
$Open  = $Bars[0][1];
$High  = $Sheet->Evaluate("MAX(C:C)");
$Low   = $Sheet->Evaluate("MIN(D:D)");
$Close = $Bars[$#Bars][4];

# Change tickmark spacing from decimal to fractional
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,
);

# Fat candles with only 5% gaps
$Chart->ChartGroups(1)->{GapWidth} = 5;

sub RGB {
    my ($red,$green,$blue) = @_;
    return $red | ($green<<8) | ($blue<<16);
}

# White background with a solid border
$Chart->PlotArea->Border->{LineStyle} = xlContinuous;
$Chart->PlotArea->Border->{Color} = RGB(0,0,0);
$Chart->PlotArea->Interior->{Color} = RGB(255,255,255);

# Add 1 hour moving average of the Close series
my $MovAvg = $Chart->SeriesCollection(4)->Trendlines
             ->Add({Type => xlMovingAvg, Period => 4});
$MovAvg->Border->{Color} = RGB(255,0,0);

# Save worbook to file
print "Save workbook\n";
my $Filename = 'i:\tmp\tpj\data.xls';
unlink $Filename if -f $Filename;
$Book->SaveAs($Filename);
$Book->Close;

############################################################
print "Start ADO and update database\n";
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

my $Connection = Win32::OLE->new('ADODB.Connection');
my $Recordset = Win32::OLE->new('ADODB.Recordset');
$Connection->Open('T-Bonds');

# Open a recordset for table of this contract
{
    local $Win32::OLE::Warn = 0;
    $Recordset->Open($Contract, $Connection, adOpenKeyset, 
		     adLockOptimistic, adCmdTable);
}
# Create table and index if it doesn't exist yet
if (Win32::OLE->LastError) {
    $Connection->Execute(<<"SQL");
        CREATE TABLE $Contract
	( 
          Day DATETIME,
	  Open DOUBLE, High DOUBLE, Low DOUBLE, Close DOUBLE
	)
SQL
    $Connection->Execute(<<"SQL");
        CREATE INDEX $Contract 
        ON $Contract (Day) WITH PRIMARY
SQL
    $Recordset->Open($Contract, $Connection, adOpenKeyset, 
		     adLockOptimistic, adCmdTable);
}

# Add new record to table
use Win32::OLE::Variant;
$Win32::OLE::Variant::LCID = $Win32::OLE::LCID;

my $Fields = [qw(Day Open High Low Close)];
my $Values = [Variant(VT_DATE, $Day),
	      $Open, $High, $Low, $Close];
{
    local $Win32::OLE::Warn = 0;
    $Recordset->AddNew($Fields, $Values);
}

# Replace existing record
if (Win32::OLE->LastError) {
    $Recordset->CancelUpdate;
    $Recordset->Close;
    $Recordset->Open(<<"SQL", $Connection, adOpenDynamic);
        SELECT * FROM $Contract
	WHERE Day = #$Day#
SQL
    $Recordset->Update($Fields, $Values);
}

$Recordset->Close;
$Connection->Close;

############################################################
print "Start Notes and send email\n";

sub EMBED_ATTACHMENT {1454;}

my $Notes = Win32::OLE->new('Notes.NotesSession');
my $Database = $Notes->GetDatabase('', '');
$Database->OpenMail;
my $Document = $Database->CreateDocument;

$Document->{Form} = 'Memo';
$Document->{SendTo} = ['Jon Orwant <orwant@media.mit.edu>',
		       'Jan Dubois <jan.dubois@ibm.net>'];
$Document->{Subject} = "US T-Bonds Chart for $Day";

my $Body = $Document->CreateRichtextItem('Body');
$Body->AppendText(<<"EOT");
I\'ve attached the latest US T-Bond data and chart for $Day.
The daily statistics were:

\tOpen\t$Open
\tHigh\t$High
\tLow\t$Low
\tClose\t$Close

Kind regards,
Mary

EOT

$Body->EmbedObject(EMBED_ATTACHMENT, '', $Filename);

#$Document->Send(0);
$Document->Save(0,0);

print "Done\n";