view release on metacpan or  search on metacpan

lib/App/Chart/Suffix/LME.pm  view on Meta::CPAN

# Return tdate of anticipated available montly .xls download, that being
# the end of the previous month.
#
# Don't know exactly when a new month full of data becomes available,
# assume here midnight at the start of the second trading day of the new
# month.
#
sub monthxls_available_tdate {
  my $tdate = App::Chart::Download::tdate_today
    (App::Chart::TZ->london);
  $tdate--; # not until second business day into this month
  $tdate = tdate_start_of_month ($tdate);
  return $tdate - 1; # last day of previous month
}

sub monthxls_download {
  my ($symbol_list) = @_;
  if (DEBUG) { print "LME ",@$symbol_list,"\n"; }

  my $lo_tdate = App::Chart::Download::start_tdate_for_update (@$symbol_list);
  my $hi_tdate = monthxls_available_tdate();

  my @files = grep {$_->{'url'} !~ /volume/i} historical_xls_files();
  my $files = App::Chart::Download::choose_files (\@files, $lo_tdate, $hi_tdate);

  foreach my $f (@$files) {
    my $url = $f->{'url'};
    require File::Basename;
    my $filename = File::Basename::basename($url);
    App::Chart::Download::status (__x('LME data {filename}',
                                     filename => $filename));
    my $resp = App::Chart::Download->get ($url);
    my $h = monthxls_parse ($resp);
    App::Chart::Download::write_daily_group ($h);
  }
}

sub tdate_start_of_month {
  my ($tdate) = @_;
  my ($year,$month,$day) = App::Chart::tdate_to_ymd ($tdate);
  return App::Chart::ymd_to_tdate_ceil ($year, $month, 1);
}

my %monthxls_sheet_to_commodity =
  ('Copper'        => 'CA',
   'Al. Alloy'     => 'AA',
   'NASAAC'        => 'NA',
   'Zinc'          => 'ZS',
   'Lead'          => 'PB',
   'Pr. Aluminium' => 'AH',
   'Tin'           => 'SN',
   'Nickel'        => 'NI',
   'Far East'      => 'FF',  # steel
   'Med'           => 'FM',  # steel
   'Averages'              => undef,
   'Plastic Avg'           => undef,
   'Averages inc. Euro Eq' => undef);

sub monthxls_parse {
  my ($resp) = @_;
  my $content = $resp->decoded_content (charset => 'none', raise_error => 1);

  require Spreadsheet::ParseExcel;
  require Spreadsheet::ParseExcel::Utility;

  my @data = ();
  my $h = { source     => __PACKAGE__,
            cover_pred => $pred,
            data       => \@data };

  my $excel = Spreadsheet::ParseExcel::Workbook->Parse (\$content);
  foreach my $sheet (@{$excel->{Worksheet}}) {
    my $sheet_name = $sheet->{'Name'};
    if (DEBUG) { print "Sheet: $sheet_name\n"; }
    my $commodity;
    if ($sheet_name =~ /^[A-Z][A-Z]$/) {
      # plastics symbol
      $commodity = $sheet_name;
    } elsif (exists $monthxls_sheet_to_commodity{$sheet_name}) {
      $commodity = $monthxls_sheet_to_commodity{$sheet_name}
        // next;  # undef for ignored sheets
    } else {
      warn "LME: unrecognised month data sheet: $sheet_name\n";
      next;
    }

    my ($minrow, $maxrow) = $sheet->RowRange;
    my ($mincol, $maxcol) = $sheet->ColRange;

    my $heading_row = $minrow;
    my $date_col;
    my $seller_col;
  HEADING: for (;; $heading_row++) {
      if ($heading_row > $maxrow) { die "LME: headings row not found\n"; }
      for ($seller_col = $mincol; $seller_col <= $maxcol; $seller_col++) {
        my $cell = $sheet->Cell($heading_row,$seller_col) // next;
        my $str = $cell->Value;
        if (DEBUG >= 2) { print "  cell $heading_row,$seller_col $str\n"; }
        if ($str =~ /SELLER/i) { last HEADING; }
      }
    }
    $date_col = $seller_col - 2;
    if (DEBUG) { print "  heading row $heading_row seller col $seller_col\n"; }

    my @column_num = ();
    my @column_symbol = ();
    for (my $col = $seller_col; $col+2 <= $maxcol; $col += 3) {
      my $cell = $sheet->Cell($heading_row,$col) || last;
      $cell->Value =~ /SELLER/i or next;

      my $period = $sheet->Cell($heading_row-1,$col)->Value;
      if (DEBUG >= 2) { print "  col=$col period=$period\n"; }
      if ($period =~ /cash/i) {
        $period = '';
      } elsif ($period =~ /([0-9]+).*(months|mths)/i) {
        $period = $1;
      } elsif ($period eq '') {
        last;
      } else {
        die "LME: month sheet '$sheet_name' heading row=$heading_row col=$col period unrecognised: '$period'\n";
      }

lib/App/Chart/Suffix/LME.pm  view on Meta::CPAN

        my $symbol = $column_symbol[$i];

        # unformatted value gets '1490.00' instead of '$1,490.00'
        my $seller = $sheet->Cell($row,$col)->{'Val'};
        push @data, { symbol => $symbol,
                      date   => $date,
                      close  => $seller,
                    };
      }
    }
    if (! $seen_date) {
      die "LME month data: no dates found in sheet '$sheet_name'";
    }
  }
  my $date = $data[0]->{'date'};
  my ($year, $month, $day) = App::Chart::iso_to_ymd ($date);
  $h->{'cover_lo_date'} = App::Chart::ymd_to_iso ($year, $month, 1);
  ($year, $month, $day) = Date::Calc::Add_Delta_YMD ($year, $month, $day,
                                                     0, 1, -1);
  $h->{'cover_hi_date'} = App::Chart::ymd_to_iso ($year, $month, $day);
  return $h;
}

#-----------------------------------------------------------------------------
# download - volume xls files
#
# This crunches files like
#     http://www.lme.co.uk/downloads/volumes_Jan_08.xls
#

# App::Chart::DownloadHandler->new
#   (name   => __('LME month volumes'),
#    pred   => $pred,
#    proc   => \&volume_download,
#    # backto => \&volume_backto,
#    available_tdate => \&monthxls_available_tdate);

sub volume_download {
  my ($symbol_list) = @_;

  my $lo_tdate = App::Chart::Download::start_tdate_for_update (@$symbol_list);
  my $hi_tdate = monthxls_available_tdate();

  my @files = grep {$_->{'url'} =~ /volume/i} historical_xls_files();
  my $files = App::Chart::Download::choose_files (\@files, $lo_tdate, $hi_tdate);

  foreach my $f (@$files) {
    my $url = $f->{'url'};
    require File::Basename;
    my $filename = File::Basename::basename($url);
    App::Chart::Download::status (__x('LME volumes {filename}',
                                     filename => $filename));
    my $resp = App::Chart::Download->get ($url);
    my $h = volume_parse ($resp);
    App::Chart::Download::write_daily_group ($h);
  }
}

sub volume_parse {
  my ($resp) = @_;
  my $content = $resp->decoded_content (charset => 'none', raise_error => 1);

  require Spreadsheet::ParseExcel;
  require Spreadsheet::ParseExcel::Utility;

  my @data = ();
  my $h = { source => __PACKAGE__,
            data   => \@data };

  my $excel = Spreadsheet::ParseExcel::Workbook->Parse (\$content);
  my $sheet = $excel->Worksheet (0);
  if (DEBUG) { print "Sheet: ",$sheet->{'Name'},"\n"; }

  my ($minrow, $maxrow) = $sheet->RowRange;
  my ($mincol, $maxcol) = $sheet->ColRange;

  # headings are like "AAFUT" for Aluminium Alloy, find that row
  my $heading_row;
 HEADINGROW: foreach my $row ($minrow .. $maxrow) {
    foreach my $col ($mincol .. $maxcol) {
      my $cell = $sheet->Cell($row,$col) or next;
      if ($cell->Value =~ /FUT$/) {
        $heading_row = $row;
        last HEADINGROW;
      }
    }
  }
  if (! $heading_row) { die 'LME Volumes: unrecognised headings'; }
  if (DEBUG) { print "  heading row $heading_row\n"; }

  # look for each "AAFUT" etc column in the heading row
  my @column_num = ();
  my @column_symbol = ();
  foreach my $col ($mincol .. $maxcol) {
    my $cell = $sheet->Cell($heading_row,$col) // next; # skip empties
    $cell->{'Type'} eq 'Text' or next;  # skip dates in heading
    my $str = $cell->Value;
    $str =~ /(.*)FUT$/ or next;
    my $commodity = $1;
    push @column_num, $col;
    push @column_symbol, $commodity . '.LME';
  }

  my $seen_date = 0;
  foreach my $row ($heading_row+1 .. $maxrow) {
    my $date;
    # Jan 2008 has 'Date' type in column 1
    # May 2008 onwards has text d-Mmm-yy in column 0
    my $datecell = $sheet->Cell($row,0);
    if ($datecell->{'Type'} eq 'Text') {
      $date = App::Chart::Download::Decode_Date_EU_to_iso($datecell->{'Val'},1);
      # skip blanks at end, avoid "Total"
      if (! defined $date) { next; }
    } else {
      $datecell = $sheet->Cell($row,1);
      # skip blanks at end, avoid "Total"
      $datecell->{'Type'} eq 'Date' or next;
      # default format is like 31-Jan-08, go straight to ISO to be unambiguous
      $date = Spreadsheet::ParseExcel::Utility::ExcelFmt
        ('yyyy-mm-dd', $datecell->{'Val'}, $excel->{'Flg1904'});
    }