use DBI;

# Connect to the finance database on localhost
my $dsn="DBI:mysql:host=localhost;database=finance";
my $dbh = DBI->connect ($dsn, "","") or
    die "Cannot connect to server\n";
print "Connected\n";

open(outfile, ">Black-Scholes-Prob-Correlation");

my $symbolsQuery = $dbh->prepare('select symbol from SPBalanceSheet');
$symbolsQuery ->execute();
while($symbol = $symbolsQuery ->fetchrow_array()){

$queryStringForAssets = "select totalAssets from SPBalanceSheet where symbol= '$symbol' ";
my $assetValues = $dbh->prepare($queryStringForAssets);	
$assetValues ->execute();

$queryStringForNumOfAssets = "select count(*) from SPBalanceSheet where symbol= '$symbol' ";
my $numOfAssets = $dbh->prepare($queryStringForNumOfAssets );	
$numOfAssets ->execute();
while($countResult = $numOfAssets ->fetchrow_array()){
$count=$countResult;
}

@assetValues;
@ui;
$sumUi =0;
$countReal = $count-1; 
for $i (0.. $countReal){
  $assetValues[$i] = $assetValues ->fetchrow_array();
}

if($countReal>0){
for $t (1.. $countReal){
   if($assetValues[$t-1]==0){	
   $assetValues[$t-1]=1;
}
   if($assetValues[$t]==0){
   $assetValues[$t]=1;
}	
   $ui[$t-1] = log($assetValues[$t]/$assetValues[$t-1]);
   $sumUi = $sumUi + $ui[$t-1];
}
}
else{
   $ui[0]=0;
}

if($countReal>0){
$UiAverage = $sumUi/$countReal;
}
else{
$UiAverage = $sumUi;
}
$assetuiSquaredSum =0;

for $i(0..$countReal-1){
 $assetui = $ui[i] - $UiAverage;
 $assetuiSquared = $assetui * $assetui ;
 $assetuiSquaredSum = $assetuiSquaredSum + $assetuiSquared ;
}

if($countReal==1){
$sqrStdDev = $assetuiSquaredSum/$countReal;
}
else{
$sqrStdDev = $assetuiSquaredSum/($countReal-1);
}

$stdDevAssetVolatility = sqrt($sqrStdDev);


$queryStringForEquity = "select totalStockholderEquity from SPBalanceSheet where symbol= '$symbol' ";
my $equityValues = $dbh->prepare($queryStringForEquity );	
$equityValues ->execute();

@equityValues;
@ei;
$sumEi =0;
$countReal = $count-1; 
for $i (0.. $countReal){
  $equityValues[$i] = $equityValues ->fetchrow_array();
}

if($countReal>0){
for $t (1.. $countReal){
   if($equityValues[$t-1]==0 || $equityValues[$t-1]<0){	
   $equityValues[$t-1]=1;
}
   if($equityValues[$t]==0 || $equityValues[$t]<0){
   $equityValues[$t]=1;
}	
   $ei[$t-1] = log($equityValues[$t]/$equityValues[$t-1]);
   $sumEi = $sumEi + $ei[$t-1];
}
}
else{
   $ei[0]=0;
}

if($countReal>0){
$EiAverage = $sumEi/$countReal;
}
else{
$EiAverage = $sumEi;
}
$equityEiSquaredSum =0;

for $i(0..$countReal-1){
 $equityEi = $ei[i] - $EiAverage;
 $equityEiSquared = $equityEi * $equityEi ;
 $equityEiSquaredSum = $equityEiSquaredSum + $equityEiSquared ;
}

if($countReal==1){
$sqrStdDev = $equityEiSquaredSum /$countReal;
}
else{
$sqrStdDev = $equityEiSquaredSum /($countReal-1);
}

$stdDevEquityVolatility = sqrt($sqrStdDev);

$queryStringForMarketCap = "select marketCap from stcks where symbol= '$symbol' ";
my $marketCap = $dbh->prepare($queryStringForMarketCap);	
$marketCap ->execute();
while($marketCapResult = $marketCap ->fetchrow_array()){
$marketCapitalization =$marketCapResult ;
}

if($stdDevAssetVolatility>0){
$marketValueOfAsset = ($stdDevEquityVolatility * $marketCapitalization )/$stdDevAssetVolatility  ;
}
else{
$marketValueOfAsset = $assetValues[i];
}

$queryStringForLiabilities = "select totalLiabilities,longTermDebt from SPBalanceSheet where symbol= '$symbol' ";
my $liabilities = $dbh->prepare($queryStringForLiabilities);	
$liabilities ->execute();
while(@liabilitiesResult = $liabilities ->fetchrow_array()){
$Xt = $liabilitiesResult[0] - $liabilitiesResult[1]; 
}


$R = 0.05;
$T = 1;
$Va = $marketValueOfAsset ;
$assetVolatility = $stdDevAssetVolatility;


if(($Xt==0) || ($assetVolatility == 0)){
$DD = 10000; 
}
else{
if($Va==0){
$DD = 0;
}
else{
$DD = (($R + ($assetVolatility*$assetVolatility/2))*$T + log($Va/$Xt) ) / ($assetVolatility * sqrt($T) ) ;
}
}

$z= $DD;
#Calculating cumulative normal probability for z

if($z>6.0){
 $Prob = 1 ;
}
else{
if($z< -6.0){
 $Prob = 0; 
}
else{
$b1 = 0.31938153;
$b2 = -0.356563782;
$b3 = 1.781477937;
$b4 = -1.821255978;
$b5 = 1.330274429;
$p = 0.2316419;
$c2 = 0.3989423;

$a = abs($z);
$t = 1.0/(1.0 + $a*$p);
$b = $c2 * exp((-1*$z)*($z/2.0));
$n = ((((((($b5*$t + $b4)*$t)+$b3)*$t)+$b2)*$t)+$b1)*$t;
$n = 1.0 - $b*$n;
if($z < 0.0){
 $n = 1.0 -$n;
}
$Prob = $n;
}
}


if($lastSymbol ne $symbol){
print outfile "symbol=$lastSymbol	Default prob=$lastProb \n";
}

$lastSymbol = $symbol;
$lastDD = $DD;
$lastProb = $Prob;


splice(@assetValues,0,$countReal);
splice(@ui,0,$countReal);
splice(@equityValues,0,$countReal);
splice(@ei,0,$countReal);



}
print outfile "symbol=$lastSymbol	Default prob= $lastProb \n";




$dbh->disconnect;
close(INPUT);