view release on metacpan or  search on metacpan

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

# This parses the monthly rates spreadsheet file from the
# RBA_HISTORICAL_PAGE_URL page above,
#
#     https://www.rba.gov.au/statistics/tables/xls/f11hist-1969-2009.xls
#
# but only the part from 1983 back is wanted since there's daily data for
# 1983 onwards.

my %monthly_fx_to_currency 
  = (
     # 'TWI'          # trade weighted index
     'CR'   => 'CNY', # chinese renminbi
     'JY'   => 'JPY', # japanese yen
     # 'EUR'
     # 'USD'
     'SKW'  => 'KRW', # South Korean won
     'UKPS' => 'GBP', # british pound sterling
     'SD'   => 'SGD', # singapore dollar
     'IRE'  => 'INR', # Indian rupee
     'TB'   => 'THB', # Thai baht
     # 'NZD'
     'NTD'  => 'TWD', # taiwan dollar
     'MR'   => 'MYR', # malaysian ringgit
     'IR'   => 'IDR', # indonesian rupiah
     'VD'   => 'VND', # Vietnamese dong
     'UAED' => 'AED', # UAE dirham
     'PNGK' => 'PGK', # PNG kina
     # 'HKD'   # Hong Kong dollar
     'CD'   => 'CAD', # Canadian dollar
     'SARD' => 'ZAR', # South African rand
     'SARY' => 'SAR', # Saudi riyal
     'SF'   => 'CHF',   # Swiss franc
     'SK'   => 'SEK', # Swedish krona
     # 'SDR'          # special drawing right
    );

sub monthly_parse {
  my ($resp, $stop_iso) = @_;
  ### RBA monthly_parse() ...
  my $content = $resp->decoded_content(raise_error=>1);

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

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

  my $excel = Spreadsheet::ParseExcel::Workbook->Parse (\$content);
  my $sheet = $excel->Worksheet (0);
  ### SheetCount: $excel->{'SheetCount'}
  ### Name: $sheet->{'Name'}

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

  # heading row repeats the filename "F11HIST.XLS" and then the currencies
  # in columns as say "FXRJY"
  my $heading_row = List::Util::first {
    my $cell = $sheet->Cell($_,$mincol);
    $cell && $cell->Value eq 'F11HIST.XLS' }
    ($minrow .. $maxrow)
      or die "RBA monthly: headings not found";
  ### $heading_row

  my @currencies = map {
    my $cell = $sheet->Cell($heading_row,$_);
    my $currency = $cell ? $cell->Value : '';
    $currency =~ s/^FXR//;
    ($monthly_fx_to_currency{$currency} || $currency)
  } ($mincol .. $maxcol);
  ### @currencies
  ### count: scalar(@currencies)

  my %currency_started;

 ROW: foreach my $row ($heading_row+1 .. $maxrow) {
    my $datecell = $sheet->Cell($row,0) or next;
    # seen 'Numeric', but presumably 'Date' is ok
    if ($datecell->{'Type'} ne 'Numeric'
        && $datecell->{'Type'} ne 'Date') {
      next;  # skip blanks at end
    }
    my $month = Spreadsheet::ParseExcel::Utility::ExcelFmt
      ('yyyy-mm-dd', $datecell->{'Val'}, $excel->{'Flg1904'});

    foreach my $col ($mincol+1 .. $maxcol) {
      my $currency = $currencies[$col-$mincol] or next;
      my $ratecell = $sheet->Cell($row,$col) or next;
      my $rate = $ratecell->Value;

      # avoid empty records until the start of data for a given currency is
      # reached
      if (! $rate && ! $currency_started{$currency}) { next; }

      my $symbol = "AUD$currency.RBA";
      $currency_started{$currency} = 1;
      foreach my $date (iso_weekdays_in_month ($month)) {
        if ($date gt $stop_iso) { last ROW; }
        push @data, { symbol   => $symbol,
                      currency => $currency,
                      date     => $date,
                      close    => $rate,
                    };
      }
    }
  }

  return $h;
}

# return a list of ISO date strings like '2008-09-08' which is all the
# weekdays in the month of ISO date $str
sub iso_weekdays_in_month {
  my ($str) = @_;
  my ($lo_year, $lo_month, undef) = App::Chart::iso_to_ymd ($str);
  my ($hi_year, $hi_month, undef) = Date::Calc::Add_Delta_YM
    ($lo_year,$lo_month,1, 0,1);