autoexcel01.pl to HTML.

index -|- end

Generated: Tue Feb 2 17:54:21 2010 from autoexcel01.pl 2006/06/11 4.5 KB.

#!/Perl
###print "Hello, World...\n";
# from : http://www.foo.be/docs/tpj/issues/vol3_2/tpj0302-0008.html
use LWP::Simple;
my $URL = 'http://www.cbot.com/mplex/quotes/tsfut';
###my $text = get("$URL/tsf$Contract.htm");
# Save workbook to file 
my $Filename = 'c:\tmp\tpj\data4.xls';
# 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 @Bars = ();
my $text = "03/12/1998 US 98Mar 12116 15:28:34 Open\n";
$text .= "03/12/1998 US 98Mar 12117 15:43:34 Open\n";
$text .= "03/12/1998 US 98Mar 12118 15:58:34 Open\n";
$text .= "03/12/1998 US 98Mar 12120 16:03:34 Open\n";
$text .= "03/12/1998 US 98Mar 12118 16:18:34 Open\n";
$text .= "03/12/1998 US 98Mar 12110 16:23:34 Open\n";
$text .= "03/12/1998 US 98Mar 12110 16:38:34 Open\n";
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) {
      if (defined $Time) {
         print "Pushing $hhmm $Open $High $Low $Close ...\n";
      }
        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;
print "Pushing $hhmm $Open $High $Low $Close ...\n";
push @Bars, [$hhmm, $Open, $High, $Low, $Close];
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 "Adding data ...\n";
$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});
# Excel bug: the old $Chart is now invalid!
$Chart = $Excel->ActiveChart;
# Add title, remove legend
with($Chart, HasLegend => 0, HasTitle => 1);
$Chart->ChartTitle->Characters->{Text} = "US T-Bond";
# Set up 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 { $_[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 = 'c:\tmp\tpj\data.xls';
unlink $Filename if -f $Filename;
$Book->SaveAs($Filename);
$Book->Close;
# eof - autoexcel01.pl

index -|- top