use strict;
$| = 1;

my $Contract = 'us9h';
use LWP::Simple;
my $URL = 'http://www.cbot.com/mplex/quotes/tsfut';
my $text = get("$URL/tsf$Contract.htm");

my ($Day,$Time,$hhmm,$Open,$High,$Low,$Close,@Bars);
foreach (split "\n", $text) {
    # 03/12/1998 US 98Mar 12116 15:28:34 Open
    my ($Date,$Price,$Hour,$Min,$Sec,$Ind) =
        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 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;
    unless (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 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;

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

# Create candle stick chart as new object on worksheet
$Sheet->Range("A:E")->Select;
my $Chart = $Book->Charts->Add;
$Chart->{ChartType} = xlStockOHLC;
$Chart->Location(xlLocationAsObject, $Sheet->{Name});
$Chart = $Excel->ActiveChart;

# Add title and 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 ticks from decimals to fractions
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 { $_[0] | ($_[1] << 8) | ($_[2] << 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 workbook to file
my $Filename = 'i:\tmp\tpj\data.xls';
unlink $Filename if -f $Filename;
$Book->SaveAs($Filename);
$Book->Close;

# Start ActiveX Data Objects and update the database

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;

# Send mail to the customer using Lotus Notes
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);