App/Chart/Suffix/LME.pm

filter: Statementstmt 95.1% 39 / 41 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 14 / 14 Podpod n/a totaltotal 96.3% 53 / 55 CC 1 SCAR 0.2
CC
1
Cov
95%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
« App/Chart.pm ↑ Summary App/Chart/Yahoo.pm »
1 # London Metal Exchange (LME) setups.
2  
3 # Copyright 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2013, 2016 Kevin Ryde
4  
5 # This file is part of Chart.
6 #
7 # Chart is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 3, or (at your option) any later version.
10 #
11 # Chart is distributed in the hope that it will be useful, but WITHOUT ANY
12 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
13 # FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
14 # details.
15 #
16 # You should have received a copy of the GNU General Public License along
17 # with Chart. If not, see <http://www.gnu.org/licenses/>.
18  
19 package App::Chart::Suffix::LME;
20 1 ▶use 5.010;
Subroutine BEGIN: called CC=1 SCAR=0.0
21 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
22 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
23 1 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
24 1 ▶use Date::Calc;
Subroutine BEGIN: called CC=1 SCAR=0.0
25 1 ▶use Date::Parse;
Subroutine BEGIN: called CC=1 SCAR=0.0
26 1 ▶use File::Temp;
Subroutine BEGIN: called CC=1 SCAR=0.0
27 1 ▶use File::Basename;
Subroutine BEGIN: called CC=1 SCAR=0.0
28 1 ▶use HTML::Form;
Subroutine BEGIN: called CC=1 SCAR=0.0
29 1 ▶use List::Util;
Subroutine BEGIN: called CC=1 SCAR=0.0
30 1 ▶use File::Slurp;
Subroutine BEGIN: called CC=1 SCAR=0.0
31 1 ▶use URI::Escape;
Subroutine BEGIN: called CC=1 SCAR=0.0
32 1 ▶use Locale::TextDomain ('App-Chart');
Subroutine BEGIN: called CC=1 SCAR=0.0
33  
34 0 ▶use App::Chart;
Subroutine BEGIN: called CC=1 SCAR=2.6
35 use App::Chart::Database;
36 use App::Chart::Download;
37 use App::Chart::DownloadHandler;
38 use App::Chart::Sympred;
39 use App::Chart::Timebase::Months;
40 use App::Chart::TZ;
41 use App::Chart::Weblink;
42  
43 use constant DEBUG => 0;
44  
45  
46 # As of July 2007, in https requests to secure.lme.com for the daily metals
47 # prices it seems essential to use http/1.1 persistent connections. If
48 # "Connection: close" is requested by the client something fishy happens and
49 # the connection hangs at about byte 48887 out of about 62110 (waiting for
50 # the last 16kbyte tls packet). This is with either gnutls or openssl and a
51 # trace with gnutls shows it just stops sending, though the TCP connection
52 # remains up. Either the default http/1.1 persistence (no Connection header
53 # at all) or the compatibility "Connection: keep-alive" style seems to make
54 # it better. Presumably it's something buggy in the server (Microsoft-IIS
55 # 6.0).
56  
57 my $pred = App::Chart::Sympred::Suffix->new ('.LME');
58 App::Chart::TZ->london->setup_for_symbol ($pred);
59  
60 # App::Chart::setup_source_help
61 # ($pred, __p('manual-node','London Metal Exchange'));
62  
63  
64 my %polypropylene_hash = ('PP'=>1,'PA'=>1,'PE'=>1,'PN'=>1);
65 my %linearlow_hash = ('LP'=>1,'LA'=>1,'LE'=>1,'LN'=>1);
66 my %steel_hash = ('FM'=>1,'FF'=>1);
67  
68 sub type {
69 my ($symbol) = @_;
70 my $commodity = App::Chart::symbol_commodity ($symbol);
71 if ($polypropylene_hash{$commodity} || $linearlow_hash{$commodity}) {
72 return 'plastics';
73 }
74 if ($steel_hash{$commodity}) {
75 return 'steels';
76 }
77 return 'metals';
78 }
79  
80 #-----------------------------------------------------------------------------
81 # weblink - commodity pages
82  
83 App::Chart::Weblink->new
84 (pred => $pred,
85 name => __('LME _Commodity Page'),
86 desc => __('Open web browser at the London Metal Exchange page for this commodity'),
87 proc => sub {
88 my ($symbol) = @_;
89  
90 if ($symbol =~ /^AA/) { return 'http://www.lme.co.uk/aluminiumalloy.asp' }
91 if ($symbol =~ /^AH/) { return 'http://www.lme.co.uk/aluminium.asp' }
92 if ($symbol =~ /^CA/) { return 'http://www.lme.co.uk/copper.asp' }
93 if ($symbol =~ /^NA/) { return 'http://www.lme.co.uk/nasaac.asp' }
94 if ($symbol =~ /^NI/) { return 'http://www.lme.co.uk/nickel.asp' }
95 if ($symbol =~ /^PB/) { return 'http://www.lme.co.uk/lead.asp' }
96 if ($symbol =~ /^SN/) { return 'http://www.lme.co.uk/tin.asp' }
97 if ($symbol =~ /^ZS/) { return 'http://www.lme.co.uk/zinc.asp' }
98 if ($symbol =~ /^F/) { return 'http://www.lme.co.uk/steel.asp' }
99 if ($symbol =~ /^P/) { return 'http://www.lme.co.uk/plastics.asp' }
100 if ($symbol =~ /^L/) { return 'http://www.lme.co.uk/plastics.asp' }
101 return undef;
102 });
103  
104  
105 #-----------------------------------------------------------------------------
106 # HTTP::Cookies extras
107  
108 # $jar is a HTTP::Cookies object, read $str into it with $jar->load (which
109 # would normally read from a file)
110 #
111 sub http_cookies_set_string {
112 my ($jar, $str) = @_;
113 my $fh = File::Temp->new (TEMPLATE => 'chart-cookie-jar-XXXXXX',
114 TMPDIR => 1);
115 if (DEBUG) { print "cookie set tempfile ",$fh->filename,"\n"; }
116 print $fh $str;
117 close $fh or die;
118 $jar->load ($fh->filename);
119 }
120  
121 # $jar is a HTTP::Cookies object, return a string which is $jar->save output
122 # (which would normally go to a file)
123 #
124 sub http_cookies_get_string {
125 my ($jar) = @_;
126 my $fh = File::Temp->new (TEMPLATE => 'chart-cookie-jar-XXXXXX',
127 TMPDIR => 1);
128 if (DEBUG) { print "cookie get $fh tempfile ",$fh->filename,"\n"; }
129 $jar->save ($fh->filename);
130 close $fh or die;
131 # not certain if File::Temp 0.21 blessed handle is ok, use the filename
132 return File::Slurp::slurp ($fh->filename);
133 }
134  
135  
136 #-----------------------------------------------------------------------------
137 # secure login
138 #
139 # This logs in at the data service page,
140 #
141 use constant LOGIN_URL =>
142 'https://secure.lme.com/Data/Community/Login.aspx?ReturnUrl=%2fData%2fcommunity%2findex.aspx';
143 #
144 # The result is a cookie ".ASPXAUTH" recorded under "lme-cookie-jar" in the
145 # database ready for subsequent use. An extra cookie with a dummy domain,
146 #
147 use constant LOGIN_DOMAIN => 'chart-lme-logged-in.local';
148 #
149 # is used to note success. Not sure how long a login is supposed to last
150 # (the server doesn't put an expiry on the cookie), but for now consider it
151 # expired after an hour,
152 #
153 use constant LOGIN_EXPIRY_SECONDS => 3600;
154 #
155  
156 # create and return a new HTTP::Cookies which is the jar in the database
157 sub login_read_jar {
158 require HTTP::Cookies;
159 my $jar = HTTP::Cookies->new;
160 my $str = App::Chart::Database->read_extra ('', 'lme-cookie-jar');
161 if ($str) { http_cookies_set_string ($jar, $str); }
162 return $jar;
163 }
164  
165 # $jar is a HTTP::Cookies object, save it to the database
166 sub login_write_jar {
167 my ($jar) = @_;
168 App::Chart::Database->write_extra ('', 'lme-cookie-jar',
169 http_cookies_get_string ($jar));
170 }
171  
172 # return true if we're still logged in
173 sub login_is_logged_in {
174 my $jar = login_read_jar();
175 my $login_timestamp = jar_get_login_timestamp ($jar);
176 return App::Chart::Download::timestamp_within ($login_timestamp,
177 LOGIN_EXPIRY_SECONDS);
178 }
179  
180 sub login_ensure {
181 if (login_is_logged_in()) { return; }
182  
183 App::Chart::Download::status (__('LME login'));
184 App::Chart::Database->write_extra ('', 'lme-cookie-jar', undef);
185  
186 my $username = App::Chart::Database->preference_get ('lme-username', undef);
187 my $password = App::Chart::Database->preference_get ('lme-password', '');
188 if (! defined $username || $username eq '') {
189 die 'No LME username set in preferences';
190 }
191  
192 require App::Chart::UserAgent;
193 require HTTP::Cookies;
194 my $ua = App::Chart::UserAgent->instance->clone;
195 my $jar = HTTP::Cookies->new;
196 $ua->cookie_jar ($jar);
197  
198 my $login_url = LOGIN_URL;
199 $login_url = 'http://localhost/Login.aspx';
200 my $resp = App::Chart::Download->get ($login_url, ua => $ua);
201  
202 my $content = $resp->decoded_content(raise_error=>1);
203 my $form = HTML::Form->parse($content, $login_url)
204 or die "LME login page not a form";
205  
206 # these are literal "$" in the field name
207 $form->value ("_logIn\$_userID", $username);
208 $form->value ("_logIn\$_password", $password);
209  
210 my $req = $form->click();
211 $ua->requests_redirectable ([]);
212 $resp = $ua->request ($req);
213 # The POST is to the Login.aspx page and success is a redirect to the main
214 # data page /Data/community/index.aspx. So failure is anything other than
215 # 302, or no Location, or a Location but containing "Login".
216 if ($resp->code != 302
217 || ! $resp->header ('Location')
218 || $resp->header ('Location') =~ /Login/) {
219 die "LME: login failed";
220 }
221  
222 jar_set_login_timestamp ($jar);
223 login_write_jar ($jar);
224 }
225  
226  
227 sub jar_get_login_timestamp {
228 my ($jar) = @_;
229 my $login_timestamp;
230 $jar->scan(sub {
231 my ($version, $key, $val, $path, $domain, $port, $path_spec,
232 $secure, $expires, $discard, $hash) = @_;
233 if ($domain eq LOGIN_DOMAIN && $key eq 'timestamp') {
234 $login_timestamp = $val;
235 }
236 });
237 return $login_timestamp;
238 }
239 sub jar_set_login_timestamp {
240 my ($jar) = @_;
241 $jar->set_cookie (0, # version
242 'timestamp', # key
243 App::Chart::Download::timestamp_now(), # value
244 '/', # path
245 LOGIN_DOMAIN, # domain
246 0, # port
247 0, # path_spec
248 0, # secure
249 LOGIN_EXPIRY_SECONDS, # maxage
250 0); # discard
251 }
252  
253  
254 #-----------------------------------------------------------------------------
255 # Daily data
256  
257 # return tdate for available daily report
258 #
259 sub daily_available_date {
260 my ($symbol) = @_;
261 my $type = type($symbol);
262 if ($type eq 'metals') {
263 # http://www.lme.co.uk/who_how_ringtimes.asp
264 # Prices after second ring session each trading day, which would be
265 # 16:15 maybe, try at 16:30.
266 return App::Chart::Download::weekday_date_after_time
267 (16,30, App::Chart::TZ->london, -1);
268 }
269 if ($type eq 'plastics') {
270 # https://secure.lme.com/Data/community/Dataprices_daily_prices_plastics.aspx
271 # per prices page, available at 2am the following day
272 return App::Chart::Download::weekday_date_after_time
273 (2,0, App::Chart::TZ->london, -1);
274 }
275 if ($type eq 'steels') {
276 # per prices page, available at 2am the following day
277 return App::Chart::Download::weekday_date_after_time
278 (2,0, App::Chart::TZ->london, -1);
279 }
280 die;
281 }
282  
283  
284  
285 #-----------------------------------------------------------------------------
286 # Daily price page parsing
287  
288 sub daily_parse {
289 my ($resp, $want_tdate) = @_;
290 my @data = ();
291 my $h = { source => __PACKAGE__,
292 currency => 'USD',
293 data => \@data };
294  
295 my $content = $resp->decoded_content (raise_error => 1);
296 $content = mung_1x1_tables ($content);
297  
298 # Eg. "Official Prices, US$ per tonne for\n\t\t19 September 2008"
299 # Eg. "LME Official Prices, US$ per tonne for 18 September 2008"
300 #
301 $content =~ /Prices.*?for\s*\n?\s*([0-9]{1,2}\s+[A-Za-z]+\s+[0-9][0-9][0-9][0-9])/i
302 or die "LME daily: date not found";
303 my $date = App::Chart::Download::Decode_Date_EU_to_iso ($1);
304 if (defined $want_tdate) {
305 my $want_date = App::Chart::tdate_to_iso($want_tdate);
306 if ($date ne $want_tdate) {
307 die "LME daily: didn't get expected date, got $date want $want_tdate";
308 }
309 }
310  
311 require HTML::TableExtract;
312 my $te = HTML::TableExtract->new (headers => [qr/PP.*Global/is],
313 keep_headers => 1,
314 slice_columns => 0);
315 $te->parse($content);
316 my $ts = $te->first_table_found();
317 if (! $ts) {
318 $te = HTML::TableExtract->new (headers => [qr/COPPER|STEEL/i],
319 keep_headers => 1,
320 slice_columns => 0);
321 $te->parse($content);
322 $ts = $te->first_table_found()
323 || die "LME daily: prices table not found";
324 }
325  
326 my $rows = $ts->rows();
327 my $lastrow = $#$rows;
328 my $lastcol = $#{$rows->[0]};
329  
330 my @column;
331 my @column_commodity;
332 my @column_name;
333 foreach my $c (2 .. $lastcol) {
334 my $commodity = $rows->[0]->[$c] || next;
335 my $name;
336 if ($commodity =~ /ALUMINIUM ALLOY/i) { $commodity = 'AA'; }
337 elsif ($commodity =~ /ALUMINIUM/i) { $commodity = 'AH'; }
338 elsif ($commodity =~ /COPPER/i) { $commodity = 'CA'; }
339 elsif ($commodity =~ /LEAD/i) { $commodity = 'PB'; }
340 elsif ($commodity =~ /NICKEL/i) { $commodity = 'NI'; }
341 elsif ($commodity =~ /TIN/i) { $commodity = 'SN'; }
342 elsif ($commodity =~ /ZINC/i) { $commodity = 'ZS'; }
343 elsif ($commodity =~ /NASAAC/i) { $commodity = 'NI'; }
344 elsif ($commodity =~ /STEEL.*MEDITERRANEAN/s) { $commodity = 'FM'; }
345 elsif ($commodity =~ /STEEL.*FAR EAST/s) { $commodity = 'FF'; }
346 elsif ($commodity =~ /^([A-Z][A-Z])\s+(.*)/is) { $commodity = $1; $name = $2; }
347 else { next; }
348  
349 push @column, $c;
350 push @column_commodity, $commodity;
351 push @column_name, $name;
352 }
353 if (DEBUG) { require Data::Dumper;
354 print "columns ", Data::Dumper::Dumper(\@column);
355 print "columns ", Data::Dumper::Dumper(\@column_commodity);
356 print "columns ", Data::Dumper::Dumper(\@column_name); }
357  
358 my %bid;
359 foreach my $r (1 .. $lastrow) {
360 my $row = $rows->[$r];
361 if (DEBUG) { require Data::Dumper;
362 print Data::Dumper::Dumper($row); }
363 my $type = $row->[1];
364 if (! $type) { next; }
365  
366 my $side;
367 if ($type =~ /^\s*$/is) {
368 next; # empty
369 } elsif ($type =~ /buyer/i) {
370 $side = 'bid';
371 } elsif ($type =~ /seller/i) {
372 $side = 'offer';
373 } else {
374 die "LME daily: unrecognised row type '$type'\n";
375 }
376  
377 my $month;
378 my $post;
379 if (DEBUG) { print "type $type\n"; }
380 if ($type =~ /cash/i) {
381 $post = '';
382 } elsif ($type =~ /([0-9]+)[- \t]*month/s) {
383 $post = $1;
384 } elsif ($type =~ /^(.*?)\s+(buyer|seller)/i) {
385 $month = month_str_to_nearest_iso ($1);
386 $post = " " . App::Chart::Download::iso_to_MMM_YY($month);
387 } else {
388 die "LME daily: unrecognised row type '$type'\n";
389 }
390  
391 foreach my $i (0 .. $#column) {
392 my $c = $column[$i];
393 my $commodity = $column_commodity[$i];
394 my $price = $row->[$c];
395  
396 if ($side eq 'bid') {
397 $bid{$commodity} = $price;
398 next;
399 }
400 push @data, { symbol => "$commodity$post.LME",
401 month => $month,
402 name => $column_name[$i],
403 date => $date,
404 bid => delete $bid{$commodity},
405 offer => $price,
406 close => $price,
407 };
408 }
409 }
410  
411 return $h;
412 }
413  
414 # $str is some html (in wide chars)
415 # flatten out any little 1x1 tables to their contents
416 # such tables are found in the rows of the daily plastics page
417 #
418 sub mung_1x1_tables {
419 my ($str) = @_;
420 require HTML::TreeBuilder;
421 my $top = HTML::TreeBuilder->new_from_content ($str);
422 my $changed = 0;
423 $top->traverse
424 ([sub {
425 my ($elem) = @_;
426 if ($elem->tag ne 'table') { return 1; }
427 my $table = $elem;
428  
429 # possible tbody within
430 my $tbody = List::Util::first {ref $_ && $_->tag eq 'tbody'}
431 $table->content_list;
432 if (! $tbody) { $tbody = $table; }
433  
434 my @rows = grep {ref $_ && $_->tag eq 'tr'} $tbody->content_list;
435 if (@rows != 1) { return 1; }
436 my $row = $rows[0];
437  
438 my @cols = grep {ref $_ && $_->tag eq 'td'
439 && ! html_element_contains_only_img($_) }
440 $row->content_list;
441 if (@cols != 1) { return 1; }
442 my $col = $cols[0];
443  
444 $table->replace_with ($col->content_list);
445 $changed = 1;
446 return 0; # prune
447 }
448 ],
449 1); # pre-order, no text
450 if (DEBUG) { print "mung_1x1 changed $changed\n"; }
451 if ($changed) {
452 return $top->as_HTML;
453 } else {
454 return $str;
455 }
456 }
457  
458 sub html_element_contains_only_img {
459 my ($elem) = @_;
460 my @list = $elem->content_list;
461 return (@list == 1
462 && ref $list[0]
463 && $list[0]->tag eq 'img');
464 }
465  
466 sub month_str_to_nearest_iso {
467 my ($str) = @_;
468 my $month = Date::Calc::Decode_Month ($str)
469 || die "LME parse: unrecognised month: '$str'";
470 my $year = App::Chart::Download::month_to_nearest_year ($month);
471 return App::Chart::ymd_to_iso ($year, $month, 1);
472 }
473  
474  
475 #-----------------------------------------------------------------------------
476 # historical download page
477 #
478 # This uses the historical data at
479 #
480 use constant HISTORICAL_XLS_URL =>
481 'http://www.lme.co.uk/dataprices_historical.asp';
482 #
483 # That page is downloaded to get urls of XLS files for prices and volumes
484 # for each calendar month. A price file is like
485 #
486 # http://www.lme.co.uk/downloads/January_2007.xls
487 #
488 # and a volumes file
489 #
490 # http://www.lme.co.uk/downloads/volumes_September_2007.xls
491 #
492 # Sometimes there's a rev num like
493 #
494 # http://www.lme.co.uk/downloads/historic_data/May_2008(1).xls
495 # http://www.lme.co.uk/downloads/historic_data/December_2008_3.xls
496  
497 sub historical_xls_files {
498 require App::Chart::Pagebits;
499 my $h = App::Chart::Pagebits::get
500 (name => __('LME historical downloads page'),
501 url => HISTORICAL_XLS_URL,
502 method => 'POST',
503 data => 'disclaimer=agreed',
504 key => 'lme-historical-xls',
505 freq_days => 2,
506 timezone => App::Chart::TZ->london,
507 parse => \&historical_xls_parse);
508 my $aref = $h->{'files'} || [];
509 return @$aref;
510 }
511  
512 # $content is the "dataprices_historical.asp" page.
513 # Return a hashref like { 'files' => [ {elem}, {elem}, ...] }
514 #
515 # At the start of the year there can be nothing available (the previous year
516 # files being made chargable items) so it's possible for 'urls' to be empty.
517 #
518 # There's a size in the text following each link, but since there's no
519 # overlapping files to choose between there's no need to pick that out.
520 #
521 sub historical_xls_parse {
522 my ($content) = @_;
523  
524 my %urls;
525 require HTML::LinkExtor;
526 my $p = HTML::LinkExtor->new
527 (sub {
528 my($tag, %links) = @_;
529 $tag eq 'a' or return;
530 my $link = $links{'href'} or return;
531  
532 # only the .xls files
533 $link =~ /\.xls$/i or return;
534  
535 # exclude warehouse stocks
536 if ($link =~ /stocks/i) { return; }
537  
538 $urls{$link} = 1;
539 }, HISTORICAL_XLS_URL);
540 $p->parse($content);
541  
542 my @files;
543 foreach my $url (keys %urls) {
544 if (DEBUG) { print "url $url\n"; }
545  
546 $url =~ m{([^/]+)$} or die; # only a plain file
547 my $basename = $1;
548  
549 # rev num in parens like "May_2008(1).xls"
550 $basename =~ s/%28.*%29//;
551 $basename =~ s/\(.*\)//;
552  
553 # rev num with underscore like "December_2008_3.xls"
554 $basename =~ s/(\d\d\d\d)_\d+(\.)/$1$2/;
555  
556 $basename =~ s/volumes//i;
557 my $month = App::Chart::Download::Decode_Date_EU_to_iso ("1 $basename");
558 push @files, { url => $url,
559 month_iso => $month };
560 }
561  
562 @files = sort {$a->{'month_iso'} cmp $b->{'month_iso'}
563 || $a->{'url'} cmp $b->{'url'}
564 } @files;
565 return { 'files' => \@files };
566 }
567  
568 # return mdate for STR like "January_2007" or "Jan_07", or #f if not that
569 # format
570 # sub Mmm_yyy_str_to_mdate {
571 # my ($str) = @_;
572 # # drop "(1)" part of "http://www.lme.co.uk/downloads/March_2008(1).xls"
573 # $str =~ s/\(.*\)//;
574 # $str = '1_' . $str;
575 # my ($year, $month, $day) = Date::Calc::Decode_Date_EU ($str);
576 # if (! $year || ! $month) { die "LME: unrecognised filename month: $str"; }
577 # return App::Chart::Timebase::Months::ymd_to_mdate ($year, $month, 1);
578 # }
579  
580  
581 #-----------------------------------------------------------------------------
582 # download - month price xls files
583 #
584 # This crunches files like
585 # http://www.lme.co.uk/downloads/April_2008.xls
586 #
587  
588 App::Chart::DownloadHandler->new
589 (name => __('LME month xls'),
590 pred => $pred,
591 proc => \&monthxls_download,
592 # backto => \&monthxls_backto,
593 available_tdate => \&monthxls_available_tdate);
594  
595 # Return tdate of anticipated available montly .xls download, that being
596 # the end of the previous month.
597 #
598 # Don't know exactly when a new month full of data becomes available,
599 # assume here midnight at the start of the second trading day of the new
600 # month.
601 #
602 sub monthxls_available_tdate {
603 my $tdate = App::Chart::Download::tdate_today
604 (App::Chart::TZ->london);
605 $tdate--; # not until second business day into this month
606 $tdate = tdate_start_of_month ($tdate);
607 return $tdate - 1; # last day of previous month
608 }
609  
610 sub monthxls_download {
611 my ($symbol_list) = @_;
612 if (DEBUG) { print "LME ",@$symbol_list,"\n"; }
613  
614 my $lo_tdate = App::Chart::Download::start_tdate_for_update (@$symbol_list);
615 my $hi_tdate = monthxls_available_tdate();
616  
617 my @files = grep {$_->{'url'} !~ /volume/i} historical_xls_files();
618 my $files = App::Chart::Download::choose_files (\@files, $lo_tdate, $hi_tdate);
619  
620 foreach my $f (@$files) {
621 my $url = $f->{'url'};
622 require File::Basename;
623 my $filename = File::Basename::basename($url);
624 App::Chart::Download::status (__x('LME data {filename}',
625 filename => $filename));
626 my $resp = App::Chart::Download->get ($url);
627 my $h = monthxls_parse ($resp);
628 App::Chart::Download::write_daily_group ($h);
629 }
630 }
631  
632 sub tdate_start_of_month {
633 my ($tdate) = @_;
634 my ($year,$month,$day) = App::Chart::tdate_to_ymd ($tdate);
635 return App::Chart::ymd_to_tdate_ceil ($year, $month, 1);
636 }
637  
638 my %monthxls_sheet_to_commodity =
639 ('Copper' => 'CA',
640 'Al. Alloy' => 'AA',
641 'NASAAC' => 'NA',
642 'Zinc' => 'ZS',
643 'Lead' => 'PB',
644 'Pr. Aluminium' => 'AH',
645 'Tin' => 'SN',
646 'Nickel' => 'NI',
647 'Far East' => 'FF', # steel
648 'Med' => 'FM', # steel
649 'Averages' => undef,
650 'Plastic Avg' => undef,
651 'Averages inc. Euro Eq' => undef);
652  
653 sub monthxls_parse {
654 my ($resp) = @_;
655 my $content = $resp->decoded_content (charset => 'none', raise_error => 1);
656  
657 require Spreadsheet::ParseExcel;
658 require Spreadsheet::ParseExcel::Utility;
659  
660 my @data = ();
661 my $h = { source => __PACKAGE__,
662 cover_pred => $pred,
663 data => \@data };
664  
665 my $excel = Spreadsheet::ParseExcel::Workbook->Parse (\$content);
666 foreach my $sheet (@{$excel->{Worksheet}}) {
667 my $sheet_name = $sheet->{'Name'};
668 if (DEBUG) { print "Sheet: $sheet_name\n"; }
669 my $commodity;
670 if ($sheet_name =~ /^[A-Z][A-Z]$/) {
671 # plastics symbol
672 $commodity = $sheet_name;
673 } elsif (exists $monthxls_sheet_to_commodity{$sheet_name}) {
674 $commodity = $monthxls_sheet_to_commodity{$sheet_name}
675 // next; # undef for ignored sheets
676 } else {
677 warn "LME: unrecognised month data sheet: $sheet_name\n";
678 next;
679 }
680  
681 my ($minrow, $maxrow) = $sheet->RowRange;
682 my ($mincol, $maxcol) = $sheet->ColRange;
683  
684 my $heading_row = $minrow;
685 my $date_col;
686 my $seller_col;
687 HEADING: for (;; $heading_row++) {
688 if ($heading_row > $maxrow) { die "LME: headings row not found\n"; }
689 for ($seller_col = $mincol; $seller_col <= $maxcol; $seller_col++) {
690 my $cell = $sheet->Cell($heading_row,$seller_col) // next;
691 my $str = $cell->Value;
692 if (DEBUG >= 2) { print " cell $heading_row,$seller_col $str\n"; }
693 if ($str =~ /SELLER/i) { last HEADING; }
694 }
695 }
696 $date_col = $seller_col - 2;
697 if (DEBUG) { print " heading row $heading_row seller col $seller_col\n"; }
698  
699 my @column_num = ();
700 my @column_symbol = ();
701 for (my $col = $seller_col; $col+2 <= $maxcol; $col += 3) {
702 my $cell = $sheet->Cell($heading_row,$col) || last;
703 $cell->Value =~ /SELLER/i or next;
704  
705 my $period = $sheet->Cell($heading_row-1,$col)->Value;
706 if (DEBUG >= 2) { print " col=$col period=$period\n"; }
707 if ($period =~ /cash/i) {
708 $period = '';
709 } elsif ($period =~ /([0-9]+).*(months|mths)/i) {
710 $period = $1;
711 } elsif ($period eq '') {
712 last;
713 } else {
714 die "LME: month sheet '$sheet_name' heading row=$heading_row col=$col period unrecognised: '$period'\n";
715 }
716 push @column_num, $col;
717 push @column_symbol, "$commodity$period.LME";
718 }
719 if (! @column_num) {
720 die "LME: oops, sheet '$sheet_name' month data columns not matched\n";
721 }
722  
723 my $seen_date = 0;
724 foreach my $row ($heading_row+1 .. $maxrow) {
725 my $datecell = $sheet->Cell($row,$date_col) or next;
726 # skip blanks at end, avoid "Total"
727 $datecell->{'Type'} eq 'Date' or next;
728 # default format is like 31-Jan-08, go straight to ISO to be unambiguous
729 my $date = Spreadsheet::ParseExcel::Utility::ExcelFmt
730 ('yyyy-mm-dd', $datecell->{'Val'}, $excel->{'Flg1904'});
731 $seen_date = 1;
732  
733 foreach my $i (0 .. $#column_num) {
734 my $col = $column_num[$i];
735 my $symbol = $column_symbol[$i];
736  
737 # unformatted value gets '1490.00' instead of '$1,490.00'
738 my $seller = $sheet->Cell($row,$col)->{'Val'};
739 push @data, { symbol => $symbol,
740 date => $date,
741 close => $seller,
742 };
743 }
744 }
745 if (! $seen_date) {
746 die "LME month data: no dates found in sheet '$sheet_name'";
747 }
748 }
749 my $date = $data[0]->{'date'};
750 my ($year, $month, $day) = App::Chart::iso_to_ymd ($date);
751 $h->{'cover_lo_date'} = App::Chart::ymd_to_iso ($year, $month, 1);
752 ($year, $month, $day) = Date::Calc::Add_Delta_YMD ($year, $month, $day,
753 0, 1, -1);
754 $h->{'cover_hi_date'} = App::Chart::ymd_to_iso ($year, $month, $day);
755 return $h;
756 }
757  
758 #-----------------------------------------------------------------------------
759 # download - volume xls files
760 #
761 # This crunches files like
762 # http://www.lme.co.uk/downloads/volumes_Jan_08.xls
763 #
764  
765 # App::Chart::DownloadHandler->new
766 # (name => __('LME month volumes'),
767 # pred => $pred,
768 # proc => \&volume_download,
769 # # backto => \&volume_backto,
770 # available_tdate => \&monthxls_available_tdate);
771  
772 sub volume_download {
773 my ($symbol_list) = @_;
774  
775 my $lo_tdate = App::Chart::Download::start_tdate_for_update (@$symbol_list);
776 my $hi_tdate = monthxls_available_tdate();
777  
778 my @files = grep {$_->{'url'} =~ /volume/i} historical_xls_files();
779 my $files = App::Chart::Download::choose_files (\@files, $lo_tdate, $hi_tdate);
780  
781 foreach my $f (@$files) {
782 my $url = $f->{'url'};
783 require File::Basename;
784 my $filename = File::Basename::basename($url);
785 App::Chart::Download::status (__x('LME volumes {filename}',
786 filename => $filename));
787 my $resp = App::Chart::Download->get ($url);
788 my $h = volume_parse ($resp);
789 App::Chart::Download::write_daily_group ($h);
790 }
791 }
792  
793 sub volume_parse {
794 my ($resp) = @_;
795 my $content = $resp->decoded_content (charset => 'none', raise_error => 1);
796  
797 require Spreadsheet::ParseExcel;
798 require Spreadsheet::ParseExcel::Utility;
799  
800 my @data = ();
801 my $h = { source => __PACKAGE__,
802 data => \@data };
803  
804 my $excel = Spreadsheet::ParseExcel::Workbook->Parse (\$content);
805 my $sheet = $excel->Worksheet (0);
806 if (DEBUG) { print "Sheet: ",$sheet->{'Name'},"\n"; }
807  
808 my ($minrow, $maxrow) = $sheet->RowRange;
809 my ($mincol, $maxcol) = $sheet->ColRange;
810  
811 # headings are like "AAFUT" for Aluminium Alloy, find that row
812 my $heading_row;
813 HEADINGROW: foreach my $row ($minrow .. $maxrow) {
814 foreach my $col ($mincol .. $maxcol) {
815 my $cell = $sheet->Cell($row,$col) or next;
816 if ($cell->Value =~ /FUT$/) {
817 $heading_row = $row;
818 last HEADINGROW;
819 }
820 }
821 }
822 if (! $heading_row) { die 'LME Volumes: unrecognised headings'; }
823 if (DEBUG) { print " heading row $heading_row\n"; }
824  
825 # look for each "AAFUT" etc column in the heading row
826 my @column_num = ();
827 my @column_symbol = ();
828 foreach my $col ($mincol .. $maxcol) {
829 my $cell = $sheet->Cell($heading_row,$col) // next; # skip empties
830 $cell->{'Type'} eq 'Text' or next; # skip dates in heading
831 my $str = $cell->Value;
832 $str =~ /(.*)FUT$/ or next;
833 my $commodity = $1;
834 push @column_num, $col;
835 push @column_symbol, $commodity . '.LME';
836 }
837  
838 my $seen_date = 0;
839 foreach my $row ($heading_row+1 .. $maxrow) {
840 my $date;
841 # Jan 2008 has 'Date' type in column 1
842 # May 2008 onwards has text d-Mmm-yy in column 0
843 my $datecell = $sheet->Cell($row,0);
844 if ($datecell->{'Type'} eq 'Text') {
845 $date = App::Chart::Download::Decode_Date_EU_to_iso($datecell->{'Val'},1);
846 # skip blanks at end, avoid "Total"
847 if (! defined $date) { next; }
848 } else {
849 $datecell = $sheet->Cell($row,1);
850 # skip blanks at end, avoid "Total"
851 $datecell->{'Type'} eq 'Date' or next;
852 # default format is like 31-Jan-08, go straight to ISO to be unambiguous
853 $date = Spreadsheet::ParseExcel::Utility::ExcelFmt
854 ('yyyy-mm-dd', $datecell->{'Val'}, $excel->{'Flg1904'});
855 }
856 $seen_date = 1;
857  
858 foreach my $i (0 .. $#column_num) {
859 my $col = $column_num[$i];
860 my $symbol = $column_symbol[$i];
861 my $volume = $sheet->Cell($row,$col)->Value;
862 push @data, { symbol => $symbol,
863 date => $date,
864 volume => $volume,
865 };
866 }
867 }
868 if (! $seen_date) {
869 die 'LME volumes: no dates found';
870 }
871  
872 return $h;
873 }
874  
875 #-----------------------------------------------------------------------------
876 # download - daily
877 #
878 # This uses the metals and plastics settlement pages (login required) at
879 #
880 # https://secure.lme.com/Data/community/Dataprices_daily_metals.aspx
881 # https://secure.lme.com/Data/community/Dataprices_daily_prices_plastics.aspx
882 # https://secure.lme.com/Data/community/Dataprices_Steels_OfficialPrices.aspx
883 #
884  
885 my $daily_pred = App::Chart::Sympred::Proc->new (\&is_daily_symbol);
886 sub is_daily_symbol {
887 my ($symbol) = @_;
888 return ($pred->match ($symbol) && is_enabled());
889 }
890 sub is_enabled {
891 my $username = App::Chart::Database->preference_get ('lme-username', undef);
892 return (defined $username && $username ne '');
893 }
894  
895 # App::Chart::DownloadHandler->new
896 # (name => __('LME daily'),
897 # pred => $daily_pred,
898 # proc => \&daily_download,
899 # available_tdate_by_symbol => \&daily_available_tdate);
900 #
901 # sub daily_available_tdate {
902 # my ($symbol) = @_;
903 # return
904 # App::Chart::Download::iso_to_tdate_floor (daily_available_date ($symbol));
905 # }
906  
907 sub daily_download {
908 my ($symbol_list) = @_;
909  
910 my $sm = partition_by_key ($symbol_list, \&type);
911 while (my ($type, $symbol_list) = each %$sm) {
912 App::Chart::Download::verbose_message ('LME', $type, @$symbol_list);
913  
914 login_ensure();
915 my $l = daily_latest ($type);
916  
917 my $lo_tdate = App::Chart::Download::start_tdate_for_update (@$symbol_list);
918 my $hi_tdate = $l->{'tdate'} - 1;
919  
920 foreach my $tdate ($lo_tdate .. $hi_tdate) {
921 my $resp = daily_download_one ($type, $tdate, $l);
922 my $h = daily_parse ($resp, $tdate);
923 App::Chart::Download::write_daily_group ($h);
924 }
925 App::Chart::Download::write_daily_group ($l->{'h'});
926 }
927 }
928  
929 sub partition_by_key {
930 my ($list, $func) = @_;
931 require Tie::IxHash;
932 my %sm;
933 tie %sm, 'Tie::IxHash';
934 foreach my $elem (@$list) {
935 my $key = $func->($elem);
936 push @{$sm{$key}}, $elem;
937 }
938 return \%sm;
939 }
940  
941 sub daily_download_one {
942 my ($type, $tdate, $l) = @_;
943  
944 require HTML::Form;
945 my $content = $l->{'content'};
946 my $url = $l->{'url'};
947 my $form = HTML::Form->parse($content, $url)
948 or die "LME metals page not a form";
949  
950 my ($year, $month, $day) = App::Chart::tdate_to_ymd ($tdate);
951 # these are literal "$" in the field name
952 $form->value ("_searchForm\$_lstdate", $day);
953 $form->value ("_searchForm\$_lstmonth", $month);
954 $form->value ("_searchForm\$_lstyear", $year);
955  
956 App::Chart::Download::status
957 (__x('LME daily {type} {date}',
958 type => $type,
959 date => App::Chart::Download::tdate_range_string ($tdate)));
960  
961 require App::Chart::UserAgent;
962 require HTTP::Cookies;
963 my $ua = App::Chart::UserAgent->instance->clone;
964 $ua->requests_redirectable ([]);
965 my $jar = HTTP::Cookies->new;
966 $ua->cookie_jar ($jar);
967  
968 my $req = $form->click();
969 my $resp = $ua->request ($req);
970  
971 if (! $resp->is_success) {
972 die "Cannot download $url\n",$resp->headers->as_string,"\n";
973 }
974 return $resp;
975 }
976  
977 my %type_to_daily_url
978 = (metals => 'https://secure.lme.com/Data/community/Dataprices_daily_metals.aspx',
979 plastics => 'https://secure.lme.com/Data/community/Dataprices_daily_prices_plastics.aspx',
980 steels => 'https://secure.lme.com/Data/community/Dataprices_Steels_OfficialPrices.aspx');
981  
982 sub daily_latest {
983 my ($type) = @_;
984 require App::Chart::Pagebits;
985 return App::Chart::Pagebits::get
986 (name => __x('LME daily latest {type}',
987 type => $type),
988 url => $type_to_daily_url{$type},
989 key => "lme-daily-latest-$type",
990 freq_days => 0,
991 timezone => App::Chart::TZ->london,
992 parse => \&daily_latest_parse);
993 }
994  
995 sub daily_latest_parse {
996 my ($resp) = @_;
997 my $content = $resp->decoded_content (raise_error => 1);
998 my $h = daily_parse ($resp);
999 return { h => $h,
1000 date => $h->{'data'}->[0]->{'date'},
1001 url => $resp->uri->as_string,
1002 content => $content };
1003 }
1004  
1005  
1006 1;
1007 __END__
« App/Chart.pm ↑ Summary App/Chart/Yahoo.pm »