# Script to find EPICs, ISINs, etc for FTSE-350 constituents, and dump
# into a tab-delimited file
# Rev 0.1 01Dec11
#    version to add Dividend Investor share identifier
# Rev 0.2 08Jan12
#    version to replace DI share identifier with currency

use strict;
use warnings;

use WWW::Mechanize;
use HTML::TableExtract;

my ($url, $browser, $response, $content, $page, $dummy, $last_page, $te, $ts, $row,
	%data_hash, $epic, $name, $f_output, $key, $isin, $f_html1, $epic_temp, $lse_segment,
	$sector, $url_base, $sub_sector, $mstar_id, %yahoo, $f_output_a, $f_output_b, $f_output_c,
	$f_output_d, $f_output_e, $f_output_f
);

# %data_hash  key 	= epic
#			0		= name
#			1   	= index
#			2		= ISIN
#			3		= LSE Segment
#			4		= LSE sector
#			5		= LSE sub-sector
#			6		= Morningstar ID
#			7		= Yahoo symbol
#			8		= Yahoo name
#           9		= Fund flag
#		   10		= Morningstar URL
#			11		= DividendInvestor ID

$f_output	= 'ftse350.xls';		# tab-delimited output file
$f_output_a = 'ftse350_a.txt';
$f_output_b = 'ftse350_b.txt';
$f_output_c = 'ftse350_c.txt';
$f_output_d = 'ftse350_d.txt';
$f_output_e = 'ftse350_e.txt';
$f_output_f = 'ftse350_f.txt';
$f_html1	= 'html1.txt';			# for debug purposes

# Get FTSE-100 constituents from LSE
	print "1....Processing ftse100\n";
	&ftse100;

	
# Get FTSE-250 constituents from LSE
	print "2....Processing ftse250\n";
	&ftse250;

# dump data into file
	open OUTFILE, ">$f_output_a";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[0]\t$data_hash{$key}[1]\t$data_hash{$key}[2]\t$data_hash{$key}[3]\n";
	}
	close OUTFILE;


# for debug purposes
#foreach $key (sort keys %data_hash){
#		unless ($key eq 'AGK'  
#		or $key eq 'REX'){delete $data_hash{$key}}
#}
#goto TEMP;
# Now get sector and sub-sector	from LSE
	print "3....Processing LSE sector\n";
	&lse_sector;

TEMP:
# Get Morningstar ID
	print "4....Processing Morningstar ID\n";
	&morningstar_id;
	

# get Yahoo symbol data
	print "5....Getting Yahoo symbol\n";
	&yahoo_symbol;

# Add Morningstar URL
	print "6....Creating Morningstar URL\n";
	&morningstar_url;
TEMP1:
# Add Dividend Investor ID
	print "7....Creating Dividend Investor currency\n";
	&dividendinvestor;
	

# Dump into tab-delimited file
open OUTFILE, ">$f_output";
print OUTFILE "EPIC\tName\tINDEX\tISIN\tLSE Segment\tSector\tSub-sector\tMorningstar ID\tYahoo symbol\tYahoo Name\tFund\tMstar URL\tCurrency\n";
foreach $key (sort keys %data_hash){
	print OUTFILE "$key\t$data_hash{$key}[0]\t$data_hash{$key}[1]\t$data_hash{$key}[2]\t$data_hash{$key}[3]\t";
	print OUTFILE "$data_hash{$key}[4]\t$data_hash{$key}[5]\t$data_hash{$key}[6]\t$data_hash{$key}[7]\t";
	print OUTFILE "$data_hash{$key}[8]\t$data_hash{$key}[9]\t$data_hash{$key}[10]\t$data_hash{$key}[11]\n";
}
close OUTFILE;



END_OF_PROCESS:
print "End of process\n";


#######################################################################################################
############# Subroutines  ############################################################################
#######################################################################################################


##########################################################
### start of FTSE100
sub ftse100 {

	# Get data from web page
	$page = 1;
	while ($page) {
		#print "$page\n";
		$url = 'http://www.londonstockexchange.com/exchange/prices-and-markets/stocks/indices/summary/summary-indices-constituents.html?index=UKX&page='.$page;
		$browser = WWW::Mechanize->new();
		$response = $browser->get($url);
		$content = $browser->content;
		
		# Find last page number
		$content =~/.*;Page (\d*) of (\d*).*/;
		$last_page = $2;
		
		$te = HTML::TableExtract->new();
		$te->parse($content);
		$content =~s/\n//g;		# remove linefeed
		
		# dump data for debugging
		#open OUTFILE, ">$f_html1";
		#print OUTFILE "$content";
		#close OUTFILE;
		
		foreach $ts ($te->tables) {
			foreach $row ($ts->rows) {
					$epic = @$row[0];
					$name = @$row[1];
					$name =~s/,/ /g;
					$epic =~ s/[\r\n]//g;
					if ($name){$name =~ s/[\r\n]//g}
					if ($epic eq 'Code' or $epic eq 'FTSE 100'){next};
					$epic_temp = $epic;
					$epic_temp =~s/\./\\./;
					$content =~/.*?>($epic_temp)<.*?fourWayKey=(\w{12})(\w{9}).*/;
					$isin = $2;
					$lse_segment = $3;
					print "1....$epic   $name   FTSE-100    $isin   $lse_segment\n";
			   		$data_hash{$epic} = [$name,'FTSE-100',$isin,$lse_segment];
			   } # end of foreach $row
		}	# end of foreach $ts
		
		#if ($page == 3){goto TEMP}
		if ($page == $last_page){last};
		$page++;
	}

}
### end of FTSE100
##########################################################


##########################################################
### start of FTSE250
sub ftse250 {

	$page = 1;
	while ($page) {
		#print "$page\n";
		$url = 'http://www.londonstockexchange.com/exchange/prices-and-markets/stocks/indices/summary/summary-indices-constituents.html?index=MCX&page='.$page;
		$browser = WWW::Mechanize->new();
		$response = $browser->get($url);
		$content = $browser->content;
		
		# Find last page number
		$content =~/.*;Page (\d*) of (\d*).*/;
		$last_page = $2;
		
		$te = HTML::TableExtract->new();
		$te->parse($content);
		$content =~s/\n//g;
		
		foreach $ts ($te->tables) {
			foreach $row ($ts->rows) {
					$epic = @$row[0];
					$name = @$row[1];
					$name =~s/,/ /g;
					$epic =~ s/[\r\n]//g;
					if ($name){$name =~ s/[\r\n]//g}
					if ($epic eq 'Code' or $epic eq 'FTSE 250'){next};
					$epic_temp = $epic;
					$epic_temp =~s/\./\\./;
					$content =~/.*?>($epic_temp)<.*?fourWayKey=(\w{12})(\w{9}).*/;
					$isin = $2;
					$lse_segment = $3;
					print "2....$epic   $name   FTSE-250    $isin    $lse_segment\n";
			   		$data_hash{$epic} = [$name,'FTSE-250',$isin,$lse_segment];
			   }  # end of foreach $row
		}  # end of foreach $ts
		
		if ($page == $last_page){last};
		$page++;
	}  # end of while
	
}
### end of FTSE250
##########################################################


##########################################################
### start of LSE sector
sub lse_sector {

	$content = '';
	$url_base = 'http://www.londonstockexchange.com/exchange/prices-and-markets/stocks/summary/company-summary.html?fourWayKey=';
	
	foreach $key (sort keys %data_hash){
		sleep 1;
		$browser = WWW::Mechanize->new();
		$url = $url_base.$data_hash{$key}[2].$data_hash{$key}[3];
		print "3....$key     ";
		#if ($key eq 'COLT'){
		#	print "$url\n";
		#}
		$response = $browser->get($url);
		$content = $browser->content;
		
		#open OUTFILE, ">$f_html1";
		#	print OUTFILE "$content";
		#close OUTFILE;
		
		$content =~s/\n//g;		# remove line feeds
	
		#$content =~/.*FTSE sector.*?name">(.*?)<.*/;
		$content =~/.*FTSE sector.*?name">(.*?)<.*sub-sector.*?name">(.*?)<.*/;
		$sector = $1;
		$sub_sector = $2;
		$sector =~s/,/ /g;
		$sub_sector =~s/,/ /g;
		$data_hash{$key}[4] = $sector;
		$data_hash{$key}[5] = $sub_sector;
		print "$sector   $sub_sector\n";
	}  # end of foreach $key

	open OUTFILE, ">$f_output_b";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[4]\t$data_hash{$key}[5]\n";
	}
	close OUTFILE;

}
### end of LSE sector
##########################################################


##########################################################
### start of Morningstar ID
sub morningstar_id{
	my($uri, $url_base, $content);
	
	$url_base = 'http://www.morningstar.co.uk/uk/stockquicktake/default.aspx?source=HS&isin=';
	
	$browser = WWW::Mechanize->new(autocheck => 0);

	foreach $key (sort keys %data_hash){
		$browser = WWW::Mechanize->new(autocheck => 0);
		
		RETRY_MSTAR:
		print "4...$key    ";
		$mstar_id = '';
		$content = '';
		$url = $url_base.$data_hash{$key}[2];
		unless ($browser->get($url)){
			sleep 5;
			goto RETRY_MSTAR;
		}
		
		$uri = $browser->uri();
		$uri =~/.*SecurityToken=(\w{10}).*/;		# extract morningstar id from url
		print "$uri\n";
		$mstar_id = $1;
		$data_hash{$key}[6] = $mstar_id;
		
		# check if Fund
		$content = $browser->content;
		$content =~s/\n//g;
		if ($content =~/Fund Structure/){
			$data_hash{$key}[9] = "Yes";
		} else {
			$data_hash{$key}[9] = "No";
		}
		
		
		print "Morningstar ID = $mstar_id\n";

	}	# end of foreach
	
	open OUTFILE, ">$f_output_c";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[6]\t$data_hash{$key}[9]\n";
	}
	close OUTFILE;
	
}
### end of Morningstar ID
##########################################################


##########################################################
### start of yahoo symbol
sub yahoo_symbol {
	my ($content, $yahoo_symbol, $yahoo_name, $url_base, $page, $browser, $response, $url,
		$te, $row, $ts
	);

	$url_base = 'http://uk.finance.yahoo.com/q/cp?s=^FTAS&c=';		# Original
	$url_base = 'http://uk.finance.yahoo.com/q/cp?s=%5EFTAS&c=';	# updated 01Jun13
	
	# Get data from web page
	$page = 0;
	while (1) {
		#print "$page\n";
		$url = $url_base.$page;
		$browser = WWW::Mechanize->new();
		$response = $browser->get($url);
		$content = $browser->content;
		
		$te = HTML::TableExtract->new();
		$te->parse($content);
		$content =~s/\n//g;		# remove linefeed
		
		open OUTFILE, ">temp.xls";
		print OUTFILE "$content";
		close OUTFILE;
		
		no warnings;
		foreach $ts ($te->tables) {
			#print "Table (", join(',', $ts->coords), "):\n";
			foreach $row ($ts->rows) {
				if (@$row[0] =~/.+.L$/){
					$yahoo{@$row[0]} = @$row[1];
				}
			}
		} # end of foreach $ts
		use warnings;
		
		$page++;
		if ($content =~/.*<b>Last<.*/){last}
	}	# end of while
	
	# create computed yahoo symbol from EPIC and add to %data_hash.
	# Note.... not guaranteed to be accurate in 100% of cases.
	# The rules for generating Yahoo symbols are unknown.
	foreach $key (sort keys %data_hash){
		if ($key =~/.+\.$/){			# identify symbols ending in .
			$data_hash{$key}[7] = $key.'L';
		} elsif ($key =~/\.\w/) {			# identify symbols containing . and ending with a character
			$key =~/(\w+)\.(\w+)/;
			$data_hash{$key}[7] = $1.'-'.$2.'.L';
		} else {
			$data_hash{$key}[7] = $key.'.L';
		}
		$data_hash{$key}[8] = $yahoo{$data_hash{$key}[7]};
		$data_hash{$key}[8] =~s/,/ /g;
	} # end of foreach $key
	
	open OUTFILE, ">$f_output_d";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[7]\t$data_hash{$key}[8]\n";
	}
	close OUTFILE;

}
### end of yahoo symbol
##########################################################


##########################################################
### start of Morningstar URL
sub morningstar_url {

	my ($equity_url, $equity_url_base, $fund_url, $fund_url_base,
	    $key);

	$equity_url_base = 'http://tools.morningstar.co.uk/uk/stockreport/default.aspx?Site=uk&id=mstar_id&LanguageId=en-GB&SecurityToken=mstar_id]3]0]E0WWE$$ALL';
	$fund_url_base   = 'http://tools.morningstar.co.uk/uk/cefreport/default.aspx?Site=uk&id=mstar_id&LanguageId=en-GB&SecurityToken=mstar_id]2]0]FCGBR$$ALL';

	foreach $key (sort keys %data_hash){
		if ($data_hash{$key}[9] eq 'No'){
			$equity_url = $equity_url_base;
			$equity_url =~s/mstar_id/$data_hash{$key}[6]/g;
			$data_hash{$key}[10] = $equity_url;
		}
		if ($data_hash{$key}[9] eq 'Yes'){
			$fund_url = $fund_url_base;
			$fund_url =~s/mstar_id/$data_hash{$key}[6]/g;
			$data_hash{$key}[10] = $fund_url;
		}
	}	# end of foreach
	
	open OUTFILE, ">$f_output_e";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[10]\n";
	}
	close OUTFILE;	
	
}
### end of Morningstar URL
##########################################################

##########################################################
### start of DividendInvestor currency
sub dividendinvestor {
	my ($di_url_base, $di_url, $key, $epic, $response, $content);
	
	$di_url_base = 'http://www.dividendinvestor.co.uk/?symbol=EPIC&submit=GO';
	
	foreach $key (sort keys %data_hash){
		print "7....$key   ";
		sleep 1;
		$browser = WWW::Mechanize->new();
		$di_url = $di_url_base;
		$epic = $key;
		$di_url =~s/EPIC/$key/g;
		#print "$di_url\n";
		
		$response = $browser->get($di_url);
		$content = $browser->content;
		$content =~s/\n//g;		# remove line feeds
		
		#open OUTFILE, ">$f_html1";
		#	print OUTFILE "$content";
		#close OUTFILE;

		#$content =~/.*(historical\.php\?no=)(\d{1,6}).*(Dividends Paid Since).*/;
		#$data_hash{$key}[11] = $2;
		#print "ID = $data_hash{$key}[11]\n";
		
		$content =~/.*Currency:.*right">(\w\w\w)<.*/;
		$data_hash{$key}[11] = $1;
		print "Currency = $data_hash{$key}[11]\n";
		
	}	# end of foreach

	open OUTFILE, ">$f_output_f";
	foreach $key (sort keys %data_hash){
		print OUTFILE "$key\t$data_hash{$key}[11]\n";
	}
	close OUTFILE;
	
}
### end of DividendInvestor ID
##########################################################