Generated: Tue Feb 2 17:54:22 2010 from autoexcel05.pl 2006/06/19 9.8 KB.
#!/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> </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