#!/usr/bin/ruby -w
# -*- coding: utf-8 -*-
#
###############################################################################
#
# A simple demo of Stock charts in Spreadsheet::WriteExcel.
#
# reverse('©'), January 2010, John McNamara, jmcnamara@cpan.org
#
# original written in Perl by John McNamara
# converted to Ruby by Hideo Nakamura, cxn03651@msj.biglobe.ne.jp
#
require 'writeexcel'

workbook  = WriteExcel.new('chart_stock.xls')
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.
bold        = workbook.add_format(:bold       => 1)
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)

stock_data = [
  %w(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]
]

# Write the data to the worksheet.
row = 0
col = 0

headers = stock_data.shift
worksheet.write(row, col, headers, bold)
row += 1

stock_data.each do |data|
  date = data.shift
  worksheet.write(row, col, date, date_format)
  worksheet.write(row, col + 1, data)
  row += 1
end

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

chart1 = workbook.add_chart(:type => Chart::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.
#

chart2 = workbook.add_chart(:type => Chart::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)

# File save
workbook.close