#!/usr/bin/perl
#@ _COMMENT_
#Created by tech @ltimas on 2017-09-22
#
# **************** Program Notes  ***********************************
# 	    1. Script requests user to enter a date to create indices record.
# 	    2. Creates an array with the stock records based on a date and equity table "list".
#           3. Open table "portfolio" and run for loop to populate stock variables for each equity.
# 	    4. Sum combined indices and calculate indices stk variables.
#           5. Use foreach and if statements to increment through sectors and calculate sctr based indices.
#           6. Write financial indices data into text file.
# **********     NOTE: Program only accepts equities in NYSE or NM **
# **********     
# *******************************************************************

#@ _Version_****
#V0.0 - Created Script for ltimas.com

#@ _USE_
use strict;
use DBI;
#@ _USE_

#@ _VARDECL_
my $dsn = "DBI:mysql:"ENTER YOUR DB";	#database source name
my $user = "ENTER YOUR PASSWORD";		# user name
my $password = "ENTER YOUR PASSWORD";	# password
my($query);			# Declare query
#declarations for quotes
my (@quote);			#declarations for quotes
my (%finData);

# Declare variables for financial data records ###########################################
my ($sym, $sect, $price, $keyNum, $curdate, $eps, $pe, $ps, $pb, $ds, $vol, $cap);
my (@record, @new);

# Declarations for date and and array tracking #########################################################
my ($sdate, $row, $record, $result, $index);
my (@test,@sctrIndex);
$index = 0;
# Declarations for market cap manipulation #############################################################
my ($mag, $noShares, $earn, $sales, $book, $payout);
my ($sumEarn, $sumSales, $sumBook, $sumDiv, $sumVol, $sumCap, $indexPrice, $sumShares, $indexEPS, $indexPE, $indexPS, $indexPB, $indexDS);
$sumShares=0;
$sumCap=0;

# Declarations for indices
my (@pIndex, @pChem, @pEng, @pFood, @pFinc, @pHc, @pInds, @pMat, @pTech);
#@ _VARDECL_

#ask user for current date
print stdout "Enter current date use (20XX-month-day, ISO format)";
chomp($sdate = <STDIN>); 

#@ _CONNECT_
my $dbh = DBI->connect($dsn, $user, $password)
	or die $DBI::errstr;
#@ _CONNECT

#@ _FETCH_ data from portfolio, univ data bases_
my $query = qq{ Select portfolio.tkr, list.mrkt, list.sctr, portfolio.lst, portfolio.pe, portfolio.ps, portfolio.pb, portfolio.eps, portfolio.ds, portfolio.vol, portfolio.cap 
FROM portfolio, list
WHERE portfolio.tkr=list.tckr and portfolio.curdate like ? and list.active like 'yes'
ORDER BY portfolio.tkr asc};
my $sth = $dbh->prepare ($query);
$sth->execute ($sdate);
#@ _FETCH_LOOP_
while (@record = $sth->fetchrow_array())
{
    	@new[$index]= join ("\t", @record), "\n";
        $index = $index + 1;
}
#@ _FETCH_LOOP_
#	print join("\n",@new),"\n";
$index=0; 

############################## for each to loop through every element in the @new ##
	foreach (@new) {
		######################### use split to partition each record in variables
		my @tmpAry = split("\t", @new[$index]);
		$sym = @tmpAry[0];
		$sect = @tmpAry[2];
		$price = @tmpAry[3];
		$pe = @tmpAry[4];
		$ps = @tmpAry[5];
		$pb = @tmpAry[6];
		$eps = @tmpAry[7];
		$ds = @tmpAry[8];
		$vol= @tmpAry[9];
		$cap = @tmpAry[10];

		################### Calculate variables used in summations ######
		$noShares = $cap / $price;
		$earn = $eps * $noShares;
		$sales = $cap / ($ps);
		if ( $pb < 0.001 ) {
			$pb = 4.42;  #If really close to zero assume error and populate with an average value
		}
		$book = $cap/ ($pb);
		$payout = $ds * $noShares;

		################### Variable Summations #########################
                  $sumShares = $sumShares + $noShares;
                  $sumCap= $sumCap + $cap;
                  $sumEarn = $sumEarn + $earn;
                  $sumSales = $sumSales + $sales;
                  $sumBook = $sumBook + $book;
                  $sumDiv = $sumDiv + $payout;
                  $sumVol = $sumVol + $vol;
		##################################################################

		################## Check indices variables #######################
                   $indexPrice = $sumCap / $sumShares;
                   $indexEPS = $sumEarn / $sumShares;
                   $indexPE = $indexPrice / $indexEPS;
                   $indexPS = $sumCap / $sumSales;
                   $indexPB = $sumCap / $sumBook;
                   $indexDS = $sumDiv / $sumShares;
		##################################################################
		$index = $index + 1;
		}

############# Create pIndex Data Array #######################
		@pIndex[0] = "pIndex" ;
		@pIndex[1] = $sdate ;
		@pIndex[2] = $indexPrice ;
		@pIndex[3] = $indexEPS ;
		@pIndex[4] = $indexPE ;
		@pIndex[5] = $indexPS ;
		@pIndex[6] = $indexPB ;
		@pIndex[7] = $indexDS ;
		@pIndex[8] = $sumVol ;
		@pIndex[9] = $sumCap ;
########  some print variables to check things #############
#              			  print join (" ", @pIndex), "\n";
##################################################################


			################### first zero variables ######
	                   $sumShares = 0;
	                   $sumCap= 0;
	                   $sumEarn = 0;
	                   $sumSales = 0;
	                   $sumBook = 0;
	                   $sumDiv = 0;
	                   $sumVol = 0;
	                   $indexPrice = 0;
	                   $indexEPS = 0;
	                   $indexPE = 0;
	                   $indexPS = 0;
	                   $indexPB = 0;
	                   $sumDiv = 0;
			##################################################################


	######################### for each to loop through specific sectors in the @new ##
	my ($count, $dbRowCount);
	@sctrIndex[0] = "chem";
	@sctrIndex[1] = "eng";
	@sctrIndex[2] = "food";
	@sctrIndex[3] = "finc";
	@sctrIndex[4] = "hc";
	@sctrIndex[5] = "inds";
	@sctrIndex[6] = "mat";
    	@sctrIndex[7] = "tech";

$count = 0;

for ($count = 0; $count < 8; $count++) {  
			################### first zero variables ######
	                   $sumShares = 1;
	                   $sumCap= 0;
	                   $sumEarn = 0;
	                   $sumSales = 1;
	                   $sumBook = 1;
	                   $sumDiv = 0;
	                   $sumVol = 0;
	                   $indexPrice = 1;
	                   $indexEPS = 1;
  	                   $indexPE = 0;
	                   $indexPS = 0;
	                   $indexPB = 0;
	                   $sumDiv = 0;
			##################################################################
	foreach $dbRowCount(0..$index) {
		######################### use split to partition each record in variables
		my @tmpAry = split("\t", @new[$dbRowCount]);
	      if (@tmpAry[2] eq @sctrIndex[$count]) {
  			$sym = @tmpAry[0];
			$sect = @tmpAry[2];
			$price = @tmpAry[3];
			$pe = @tmpAry[4];
			$ps = @tmpAry[5];
			$pb = @tmpAry[6];
			$eps = @tmpAry[7];
			$ds = @tmpAry[8];
			$vol= @tmpAry[9];
			$cap = @tmpAry[10];
	
			################### Calculate variables used in summations ######
			$noShares = $cap / $price;
			$earn = $eps * $noShares;
		        $sales = $cap / ($ps);
			if ( $pb < 0.001 ) {
				$pb = 4.42;  #If really close to zero assume error and populate with an average value
			}
		        $book = $cap/ ($pb);
			$payout = $ds * $noShares;

			################### Variable Summations #########################
	                   $sumShares = $sumShares + $noShares;
	                   $sumCap= $sumCap + $cap;
	                   $sumEarn = $sumEarn + $earn;
	                   $sumSales = $sumSales + $sales;
	                   $sumBook = $sumBook + $book;
	                   $sumDiv = $sumDiv + $payout;
	                   $sumVol = $sumVol + $vol;
			##################################################################
	
			################## Create indices variables #######################
	                   $indexPrice = $sumCap / $sumShares;
	                   $indexEPS = $sumEarn / $sumShares;
	                   $indexPE = $indexPrice / $indexEPS;
	                   $indexPS = $sumCap / $sumSales;
	                   $indexPB = $sumCap / $sumBook;
	                   $indexDS = $sumDiv / $sumShares;
			##################################################################

            ############## CHEM ##################### 
				if (@tmpAry[2] eq "chem") {
				############# Create pChem Data Array #######################
						@pChem[0] = "pChem" ;
						@pChem[1] = $sdate ;
						@pChem[2] = $indexPrice ;
						@pChem[3] = $indexEPS ;
						@pChem[4] = $indexPE ;
						@pChem[5] = $indexPS ;
						@pChem[6] = $indexPB ;
						@pChem[7] = $indexDS ;
						@pChem[8] = $sumVol ;
						@pChem[9] = $sumCap ;
				}

            ############## ENG #####################
				if (@tmpAry[2] eq "eng") {
				############# Create pEng Data Array #######################
						@pEng[0] = "pEng" ;
						@pEng[1] = $sdate ;
						@pEng[2] = $indexPrice ;
						@pEng[3] = $indexEPS ;
						@pEng[4] = $indexPE ;
						@pEng[5] = $indexPS ;
						@pEng[6] = $indexPB ;
						@pEng[7] = $indexDS ;
						@pEng[8] = $sumVol ;
						@pEng[9] = $sumCap ;
				}

            ############## FOOD #####################
				if (@tmpAry[2] eq "food") {
				############# Create pFood Data Array #######################
						@pFood[0] = "pFood" ;
						@pFood[1] = $sdate ;
						@pFood[2] = $indexPrice ;
						@pFood[3] = $indexEPS ;
						@pFood[4] = $indexPE ;
						@pFood[5] = $indexPS ;
						@pFood[6] = $indexPB ;
						@pFood[7] = $indexDS ;
						@pFood[8] = $sumVol ;
						@pFood[9] = $sumCap ;
				}

            ############## FINC #####################
				if (@tmpAry[2] eq "finc") {
				############# Create pFinc Data Array #######################
						@pFinc[0] = "pFinc" ;
						@pFinc[1] = $sdate ;
						@pFinc[2] = $indexPrice ;
						@pFinc[3] = $indexEPS ;
						@pFinc[4] = $indexPE ;
						@pFinc[5] = $indexPS ;
						@pFinc[6] = $indexPB ;
						@pFinc[7] = $indexDS ;
						@pFinc[8] = $sumVol ;
						@pFinc[9] = $sumCap ;
				}

            ############## HC #####################
				if (@tmpAry[2] eq "hc") {
				############# Create pHc Data Array #######################
						@pHc[0] = "pHc" ;
						@pHc[1] = $sdate ;
						@pHc[2] = $indexPrice ;
						@pHc[3] = $indexEPS ;
						@pHc[4] = $indexPE ;
						@pHc[5] = $indexPS ;
						@pHc[6] = $indexPB ;
						@pHc[7] = $indexDS ;
						@pHc[8] = $sumVol ;
						@pHc[9] = $sumCap ;
				}

            ############## INDS #####################
				if (@tmpAry[2] eq "inds") {
				############# Create pInds Data Array #######################
						@pInds[0] = "pInds" ;
						@pInds[1] = $sdate ;
						@pInds[2] = $indexPrice ;
						@pInds[3] = $indexEPS ;
						@pInds[4] = $indexPE ;
						@pInds[5] = $indexPS ;
						@pInds[6] = $indexPB ;
						@pInds[7] = $indexDS ;
						@pInds[8] = $sumVol ;
						@pInds[9] = $sumCap ;
				}
				if (@tmpAry[2] eq "mat") {

				############# Create pMat Data Array #######################
						@pMat[0] = "pMat" ;
						@pMat[1] = $sdate ;
						@pMat[2] = $indexPrice ;
						@pMat[3] = $indexEPS ;
						@pMat[4] = $indexPE ;
						@pMat[5] = $indexPS ;
						@pMat[6] = $indexPB ;
						@pMat[7] = $indexDS ;
						@pMat[8] = $sumVol ;
						@pMat[9] = $sumCap ;
				}
            ############## tech #####################
				if (@tmpAry[2] eq "tech") {
				############# Create pTech Data Array #######################
						@pTech[0] = "pTech" ;
						@pTech[1] = $sdate ;
						@pTech[2] = $indexPrice ;
						@pTech[3] = $indexEPS ;
						@pTech[4] = $indexPE ;
						@pTech[5] = $indexPS ;
						@pTech[6] = $indexPB ;
						@pTech[7] = $indexDS ;
						@pTech[8] = $sumVol ;
						@pTech[9] = $sumCap ;
				}
		  }

	}
}
              	#		  print join (" ", @pIndex), "\n";
              	#		  print join (" ", @pChem), "\n";
             	#		  print join (" ", @pEng), "\n";
            	#		  print join (" ", @pFood), "\n";
	        #      		  print join (" ", @pFinc), "\n";
              	#		  print join (" ", @pHc), "\n";
              	#		  print join (" ", @pInds), "\n";
              	#		  print join (" ", @pMat), "\n";
              	#		  print join (" ", @pTech), "\n";
#@ _TERMINATE_
$dbh->disconnect ();
#@ _TERMINATE_DB
#create filename
my $filename="indices$sdate.txt";
open FILE, ">$filename" or die $!;
              			  print FILE join (" ", @pIndex), "\n";
              			  print FILE join (" ", @pChem), "\n";
             			  print FILE join (" ", @pEng), "\n";
            			  print FILE join (" ", @pFood), "\n";
	           		  print FILE join (" ", @pFinc), "\n";
              			  print FILE join (" ", @pHc), "\n";
              			  print FILE join (" ", @pInds), "\n";
              			  print FILE join (" ", @pMat), "\n";
				  print FILE join (" ", @pTech), "\n";
close FILE;
exit (0);
#@ _TERMINATE Program_