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