#!/usr/bin/perl -w

###############################################################################
#
# A simple demo of Stock charts in Spreadsheet::WriteExcel.
#
# reverse('©'), January 2010, John McNamara, jmcnamara@cpan.org
#


use strict;
use Spreadsheet::WriteExcel;

my $workbook  = Spreadsheet::WriteExcel->new( 'chart_stock.xls' );
my $worksheet = $workbook->add_worksheet();


###############################################################################
#
# Set up the data worksheet that the charts will refer to. We read the example
# data from the __DATA__ section at the end of the file. This simulates
# reading the data from a database or other source.
#
# The default Excel Stock chart is an Open-High-Low-Close chart. Therefore
# we will need data for each of those series.
#
# The layout of the __DATA__ section is similar to the layout of the worksheet.
#

# Add some formats.
my $bold        = $workbook->add_format( bold       => 1 );
my $date_format = $workbook->add_format( num_format => 'dd/mm/yyyy' );

# Increase the width of the column used for date to make it clearer.
$worksheet->set_column( 'A:A', 12 );

# Read the data from the __DATA__ section at the end. In a real example this
# would probably be a database query.
my @stock_data;

while ( <DATA> ) {
    next unless /\S/;    # Skip blank lines.
    next if /^#/;        # Skip comments.

    push @stock_data, [split];
}

# Write the data to the worksheet.
my $row = 0;
my $col = 0;

my $headers = shift @stock_data;
$worksheet->write( $row++, $col, $headers, $bold );

for my $stock_data ( @stock_data ) {

    my @data = @$stock_data;
    my $date = shift @data;

    $worksheet->write( $row, $col, $date, $date_format );
    $worksheet->write( $row, $col + 1, \@data );

    $row++;
}


###############################################################################
#
# Example 1. A default Open-High-Low-Close chart with series names, axes labels
#            and a title.
#

my $chart1 = $workbook->add_chart( type => 'stock' );

# Add a series for each of the Open-High-Low-Close columns. The categories are
# the dates in the first column.

$chart1->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$B$2:$B$10',
    name       => 'Open',
);

$chart1->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$C$2:$C$10',
    name       => 'High',
);

$chart1->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$D$2:$D$10',
    name       => 'Low',
);

$chart1->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$E$2:$E$10',
    name       => 'Close',
);

# Add a chart title and axes labels.
$chart1->set_title( name => 'Open-High-Low-Close', );
$chart1->set_x_axis( name => 'Date', );
$chart1->set_y_axis( name => 'Share price', );

###############################################################################
#
# Example 2. Same as the previous as an embedded chart.
#

my $chart2 = $workbook->add_chart( type => 'stock', embedded => 1 );

# Add a series for each of the Open-High-Low-Close columns. The categories are
# the dates in the first column.

$chart2->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$B$2:$B$10',
    name       => 'Open',
);

$chart2->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$C$2:$C$10',
    name       => 'High',
);

$chart2->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$D$2:$D$10',
    name       => 'Low',
);

$chart2->add_series(
    categories => '=Sheet1!$A$2:$A$10',
    values     => '=Sheet1!$E$2:$E$10',
    name       => 'Close',
);

# Add a chart title and axes labels.
$chart2->set_title( name => 'Open-High-Low-Close', );
$chart2->set_x_axis( name => 'Date', );
$chart2->set_y_axis( name => 'Share price', );

# Insert the chart into the main worksheet.
$worksheet->insert_chart( 'G2', $chart2 );


__DATA__
# Some sample stock data used for charting.
Date        Open    High    Low     Close
2009-08-19  100.00  104.06  95.96   100.34
2009-08-20  101.01  109.08  100.50  108.31
2009-08-23  110.75  113.48  109.05  109.40
2009-08-24  111.24  111.60  103.57  104.87
2009-08-25  104.96  108.00  103.88  106.00
2009-08-26  104.95  107.95  104.66  107.91
2009-08-27  108.10  108.62  105.69  106.15
2009-08-30  105.28  105.49  102.01  102.01
2009-08-31  102.30  103.71  102.16  102.37