/** * DVARP * * Calculates the variance of a population based on the entire population by using the numbers * in a column of a list or database that match conditions that you specify. * * Excel Function: * DVARP(database,field,criteria) * * @access public * @category Database Functions * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param string|integer $field Indicates which column is used in the function. Enter the * column label enclosed between double quotation marks, such as * "Age" or "Yield," or a number (without quotation marks) that * represents the position of the column within the list: 1 for * the first column, 2 for the second column, and so on. * @param mixed[] $criteria The range of cells that contains the conditions you specify. * You can use any range for the criteria argument, as long as it * includes at least one column label and at least one cell below * the column label in which you specify a condition for the * column. * @return float * */ public static function DVARP($database, $field, $criteria) { $field = self::__fieldExtract($database, $field); if (is_null($field)) { return NULL; } // reduce the database to a set of rows that match all the criteria $database = self::__filter($database, $criteria); // extract an array of values for the requested column $colData = array(); foreach ($database as $row) { $colData[] = $row[$field]; } // Return return PHPExcel_Calculation_Statistical::VARP($colData); }
private static function _logGamma($x) { // Log Gamma related constants static $lg_d1 = -0.5772156649015329; static $lg_d2 = 0.42278433509846713; static $lg_d4 = 1.791759469228055; static $lg_p1 = array(4.945235359296727, 201.8112620856775, 2290.8383738313464, 11319.672059033808, 28557.246356716354, 38484.962284437934, 26377.487876241954, 7225.813979700288); static $lg_p2 = array(4.974607845568932, 542.4138599891071, 15506.93864978365, 184793.29044456323, 1088204.7694688288, 3338152.96798703, 5106661.678927353, 3074109.0548505397); static $lg_p4 = array(14745.0216605994, 2426813.3694867045, 121475557.40450932, 2663432449.630977, 29403789566.34554, 170266573776.5399, 492612579337.7431, 560625185622.3951); static $lg_q1 = array(67.48212550303778, 1113.3323938571993, 7738.757056935398, 27639.870744033407, 54993.102062261576, 61611.22180066002, 36351.2759150194, 8785.536302431014); static $lg_q2 = array(183.03283993705926, 7765.049321445006, 133190.38279660742, 1136705.8213219696, 5267964.117437947, 13467014.543111017, 17827365.303532742, 9533095.591844354); static $lg_q4 = array(2690.5301758708993, 639388.5654300093, 41355999.30241388, 1120872109.616148, 14886137286.788137, 101680358627.24382, 341747634550.73773, 446315818741.9713); static $lg_c = array(-0.001910444077728, 0.0008417138778129501, -0.0005952379913043012, 0.0007936507935003503, -0.0027777777777776816, 0.08333333333333333, 0.0057083835261); // Rough estimate of the fourth root of logGamma_xBig static $lg_frtbig = 2.25E+76; static $pnt68 = 0.6796875; if ($x == self::$_logGammaCache_x) { return self::$_logGammaCache_result; } $y = $x; if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) { if ($y <= EPS) { $res = -log(y); } elseif ($y <= 1.5) { // --------------------- // EPS .LT. X .LE. 1.5 // --------------------- if ($y < $pnt68) { $corr = -log($y); $xm1 = $y; } else { $corr = 0.0; $xm1 = $y - 1.0; } if ($y <= 0.5 || $y >= $pnt68) { $xden = 1.0; $xnum = 0.0; for ($i = 0; $i < 8; ++$i) { $xnum = $xnum * $xm1 + $lg_p1[$i]; $xden = $xden * $xm1 + $lg_q1[$i]; } $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden)); } else { $xm2 = $y - 1.0; $xden = 1.0; $xnum = 0.0; for ($i = 0; $i < 8; ++$i) { $xnum = $xnum * $xm2 + $lg_p2[$i]; $xden = $xden * $xm2 + $lg_q2[$i]; } $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden)); } } elseif ($y <= 4.0) { // --------------------- // 1.5 .LT. X .LE. 4.0 // --------------------- $xm2 = $y - 2.0; $xden = 1.0; $xnum = 0.0; for ($i = 0; $i < 8; ++$i) { $xnum = $xnum * $xm2 + $lg_p2[$i]; $xden = $xden * $xm2 + $lg_q2[$i]; } $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden)); } elseif ($y <= 12.0) { // ---------------------- // 4.0 .LT. X .LE. 12.0 // ---------------------- $xm4 = $y - 4.0; $xden = -1.0; $xnum = 0.0; for ($i = 0; $i < 8; ++$i) { $xnum = $xnum * $xm4 + $lg_p4[$i]; $xden = $xden * $xm4 + $lg_q4[$i]; } $res = $lg_d4 + $xm4 * ($xnum / $xden); } else { // --------------------------------- // Evaluate for argument .GE. 12.0 // --------------------------------- $res = 0.0; if ($y <= $lg_frtbig) { $res = $lg_c[6]; $ysq = $y * $y; for ($i = 0; $i < 6; ++$i) { $res = $res / $ysq + $lg_c[$i]; } } $res /= $y; $corr = log($y); $res = $res + log(SQRT2PI) - 0.5 * $corr; $res += $y * ($corr - 1.0); } } else { // -------------------------- // Return for bad arguments // -------------------------- $res = MAX_VALUE; } // ------------------------------ // Final adjustments and return // ------------------------------ self::$_logGammaCache_x = $x; self::$_logGammaCache_result = $res; return $res; }
/** * SUBTOTAL * * Returns a subtotal in a list or database. * * @param int the number 1 to 11 that specifies which function to * use in calculating subtotals within a list. * @param array of mixed Data Series * @return float */ public static function SUBTOTAL() { $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args()); // Calculate $subtotal = array_shift($aArgs); if (is_numeric($subtotal) && !is_string($subtotal)) { switch ($subtotal) { case 1: return PHPExcel_Calculation_Statistical::AVERAGE($aArgs); break; case 2: return PHPExcel_Calculation_Statistical::COUNT($aArgs); break; case 3: return PHPExcel_Calculation_Statistical::COUNTA($aArgs); break; case 4: return PHPExcel_Calculation_Statistical::MAX($aArgs); break; case 5: return PHPExcel_Calculation_Statistical::MIN($aArgs); break; case 6: return self::PRODUCT($aArgs); break; case 7: return PHPExcel_Calculation_Statistical::STDEV($aArgs); break; case 8: return PHPExcel_Calculation_Statistical::STDEVP($aArgs); break; case 9: return self::SUM($aArgs); break; case 10: return PHPExcel_Calculation_Statistical::VARFunc($aArgs); break; case 11: return PHPExcel_Calculation_Statistical::VARP($aArgs); break; } } return PHPExcel_Calculation_Functions::VALUE(); }
function listar_oportunidades_por_puntaje($fecha = array(), $dashboard = NULL) { $dataset = array(); $results = array(); $fields = array("opp.id_oportunidad", "opp.nombre", "HEX(opp.uuid_oportunidad) AS uuid_oportunidad", "HEX(cl.uuid_cliente) AS uuid_cliente", "HEX(opp.uuid_sociedad) AS uuid_sociedad", "HEX(opp.uuid_contacto) AS uuid_contacto", "ccat.etiqueta AS tipo_cliente", "cl.nombre AS nombre_cliente", "clso.nombre_comercial", "ocat.id_cat AS id_etapa", "ocat.etiqueta AS etapa", "opp.fecha_cierre", "opp.fecha_creacion", "prop.valor_alquiler", "CONCAT_WS(' ', IF(usr.nombre != '', usr.nombre, ''), IF(usr.apellido != '', usr.apellido, '')) AS usuario_asignado", "CONCAT_WS(' ', IF(con.nombre != '', con.nombre, ''), IF(con.apellido != '', con.apellido, '')) AS nombre_contacto"); /** * Permiso Default: ver las asignadas o las que ha creado */ $clause = array("ocat.valor NOT IN('vendido','venta_perdida')" => NULL); if (!empty($fecha)) { $replace = Grafica_usuario::condicion_fecha($fecha); $str = str_replace("fecha_creacion", "opp.fecha_creacion", $replace); $clause[$str] = NULL; } else { $clause["MONTH(opp.fecha_creacion) = '" . date('n') . "'"] = NULL; } /** * Array con los uuid de usuarios * que el usario actual puede ver. */ if (empty($dashboard)) { $ver_usuarios = @CRM_Controller::andrea_ACL(); } else { $ver_usuarios = @CRM_Controller::andrea_ACL('dashboard'); } $this->db->select($fields)->distinct()->from('opp_oportunidades AS opp')->join('cl_clientes AS cl', 'cl.uuid_cliente = opp.uuid_cliente', 'LEFT')->join('cl_cliente_sociedades_contactos AS clcon', 'clcon.uuid_cliente = cl.uuid_cliente', 'LEFT')->join('con_contactos AS con', 'con.uuid_contacto = clcon.uuid_contacto', 'LEFT')->join('opp_oportunidades_cat AS ocat', 'ocat.id_cat = opp.id_etapa_venta', 'LEFT')->join('usuarios AS usr', 'usr.uuid_usuario = opp.id_asignado', 'LEFT')->join('cl_clientes_cat AS ccat', 'ccat.id_cat = cl.id_tipo_cliente', 'LEFT')->join('rpo_propiedades AS prop', 'prop.uuid_propiedad = opp.uuid_propiedad', 'LEFT')->join('cl_clientes_sociedades AS clso', 'clso.uuid_sociedad = opp.uuid_sociedad', 'LEFT'); /** * verificar array $ver_usuarios */ if (!empty($ver_usuarios["uuid_usuario"])) { $usuarios = $ver_usuarios["uuid_usuario"]; $usuarios = !empty($usuarios) ? implode(", ", array_map(function ($usuarios) { return "'" . $usuarios . "'"; }, $usuarios)) : ""; $clause["HEX(opp.id_asignado) IN(" . $usuarios . ")"] = NULL; } $this->db->where($clause); $oportunidades = $this->db->group_by('opp.id_oportunidad', 'DESC')->get()->result_array(); if (!empty($oportunidades)) { $i = 0; foreach ($oportunidades as $oportunidad) { $uuid_oportunidad = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : ""; $results[$i]["id_oportunidad"] = !empty($oportunidad["id_oportunidad"]) ? $oportunidad["id_oportunidad"] : ""; $results[$i]["uuid_oportunidad"] = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : ""; $results[$i]["nombre"] = !empty($oportunidad["nombre"]) ? $oportunidad["nombre"] : ""; $results[$i]["nombre_comercial"] = !empty($oportunidad["nombre_comercial"]) ? $oportunidad["nombre_comercial"] : ""; $results[$i]["tipo_cliente"] = !empty($oportunidad["tipo_cliente"]) ? $oportunidad["tipo_cliente"] : ""; $results[$i]["nombre_cliente"] = !empty($oportunidad["nombre_cliente"]) ? $oportunidad["nombre_cliente"] : ""; $results[$i]["fecha_creacion"] = !empty($oportunidad["fecha_creacion"]) ? $oportunidad["fecha_creacion"] : ""; //------------------------- // Seleccionar la suma total // de las actividades completadas // de esta oportunidad. //------------------------- $fields = array("COALESCE(SUM(actta.puntaje), 0) AS puntaje_total"); $clause = array("actcat1.valor" => "oportunidades", "act.uuid_oportunidad = UNHEX('{$uuid_oportunidad}')" => NULL, "act.completada" => 1); $actividad = $this->db->select($fields)->distinct()->from('act_actividades AS act')->join("act_actividades_cat AS actcat1", "actcat1.id_cat = act.relacionado_con", 'LEFT')->join("act_tipo_actividades AS actta", "actta.uuid_tipo_actividad = act.uuid_tipo_actividad", 'LEFT')->where($clause)->get()->result_array(); $results[$i]["puntaje_total"] = 0; if (!empty($actividad[0]["puntaje_total"]) && $actividad[0]["puntaje_total"] > 0) { $results[$i]["puntaje_total"] = !empty($actividad[0]["puntaje_total"]) ? (int) $actividad[0]["puntaje_total"] : 0; $dataset[$i] = (int) $actividad[0]["puntaje_total"]; $i++; } else { unset($results[$i]); unset($dataset[$i]); } } //Si no tiene actividad retornar false /*if(@Util::is_array_empty($hasActividades)){ return false; }*/ /** * Hack: para que la funcion PHPExcel_Calculation_Statistical::PERCENTRANK * No envie el siguiente error, si el array $dataset contiene un solo valor: * * A PHP Error was encountered * Severity: Warning * Message: Division by zero * Filename: Calculation/Statistical.php */ if (count($dataset) == 1) { $dataset[] = 0; } //ordenar los puntajes //de menor a mayor ksort($dataset); //Recorrer los resultados y calcular el PERCENTRANK //Usando una clase de la libreria PHPEXcel y introducir //el resultado en el array de $results $j = 0; $percentagerank = ""; foreach ($results as $result) { /*if(empty($result) || empty($result["puntaje_total"]) && $result["puntaje_total"] == "" || empty($result["puntaje_total"]) && $result["puntaje_total"] <= 0 || !empty($result["puntaje_total"]) && count($dataset) == 1 && $dataset[0] == $result["puntaje_total"]){ continue; }*/ if (!empty($result["puntaje_total"]) && $result["puntaje_total"] != "" && $result["puntaje_total"] > 0) { $percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 2) * 100; //$percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 1); $results[$j]["percentagerank"] = !empty($percentagerank) ? $percentagerank : 0; } else { unset($result); } $j++; } } //Ordenar el arreglo segun los valores de porcentaje. (mayor -> menor) usort($results, function ($a, $b) { $array_a = !empty($a['percentagerank']) ? $a['percentagerank'] : 0; $array_b = !empty($b['percentagerank']) ? $b['percentagerank'] : 0; if ($array_a == $array_b) { return 0; } else { if ($array_a > $array_b) { return -1; } else { return 1; } } }); return $results; }
public static function listar_oportunidades_por_puntaje($fecha = array(), $subordinados) { $dataset = array(); $results = array(); $replace = Grafica_usuario::condicion_fecha($fecha); $fecha_clause = str_replace("fecha_creacion", "opp.fecha_creacion", $replace); $uuid_subordinados = !empty($subordinados) ? implode(", ", array_map(function ($subordinados) { return "'" . $subordinados . "'"; }, $subordinados)) : ""; $clause = " AND {$fecha_clause}"; if (!empty($uuid_subordinados)) { $clause .= " AND HEX(opp.id_asignado) IN(" . $uuid_subordinados . ")"; } $sql = "SELECT DISTINCT `opp`.`id_oportunidad`, `opp`.`nombre`, HEX(opp.uuid_oportunidad) AS uuid_oportunidad, HEX(cl.uuid_cliente) AS uuid_cliente, HEX(opp.uuid_sociedad) AS uuid_sociedad, HEX(opp.uuid_contacto) AS uuid_contacto,\n\t\t\t\t`ccat`.`etiqueta` AS tipo_cliente, `cl`.`nombre` AS nombre_cliente, `clso`.`nombre_comercial`, `ocat`.`id_cat` AS id_etapa, `ocat`.`etiqueta` AS etapa, `opp`.`fecha_cierre`, `opp`.`fecha_creacion`, `prop`.`valor_alquiler`,\n\t\t\t\tCONCAT_WS(' ', IF(usr.nombre != '', usr.nombre, ''), IF(usr.apellido != '', usr.apellido, '')) AS usuario_asignado,\n\t\t\t\tCONCAT_WS(' ', IF(con.nombre != '', con.nombre, ''), IF(con.apellido != '', con.apellido, '')) AS nombre_contacto\n\t\t\t\tFROM `opp_oportunidades` AS opp\n\t\t\t\tLEFT JOIN `cl_clientes` AS cl ON `cl`.`uuid_cliente` = `opp`.`uuid_cliente`\n\t\t\t\tLEFT JOIN `cl_cliente_sociedades_contactos` AS clcon ON `clcon`.`uuid_cliente` = `cl`.`uuid_cliente`\n\t\t\t\tLEFT JOIN `con_contactos` AS con ON `con`.`uuid_contacto` = `clcon`.`uuid_contacto`\n\t\t\t\tLEFT JOIN `opp_oportunidades_cat` AS ocat ON `ocat`.`id_cat` = `opp`.`id_etapa_venta`\n\t\t\t\tLEFT JOIN `usuarios` AS usr ON `usr`.`uuid_usuario` = `opp`.`id_asignado`\n\t\t\t\tLEFT JOIN `cl_clientes_cat` AS ccat ON `ccat`.`id_cat` = `cl`.`id_tipo_cliente`\n\t\t\t\tLEFT JOIN `rpo_propiedades` AS prop ON `prop`.`uuid_propiedad` = `opp`.`uuid_propiedad`\n\t\t\t\tLEFT JOIN `cl_clientes_sociedades` AS clso ON `clso`.`uuid_sociedad` = `opp`.`uuid_sociedad`\n\t\t\t\tWHERE `ocat`.`valor` NOT IN('vendido','venta_perdida') {$clause}\n\t\t\t\tGROUP BY `opp`.`id_oportunidad`"; self::$db->sql($sql); $oportunidades = self::$db->getResult(); if (!empty($oportunidades)) { $i = 0; foreach ($oportunidades as $oportunidad) { $uuid_oportunidad = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : ""; $results[$i]["id_oportunidad"] = !empty($oportunidad["id_oportunidad"]) ? $oportunidad["id_oportunidad"] : ""; $results[$i]["uuid_oportunidad"] = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : ""; $results[$i]["nombre"] = !empty($oportunidad["nombre"]) ? $oportunidad["nombre"] : ""; $results[$i]["nombre_comercial"] = !empty($oportunidad["nombre_comercial"]) ? $oportunidad["nombre_comercial"] : ""; $results[$i]["tipo_cliente"] = !empty($oportunidad["tipo_cliente"]) ? $oportunidad["tipo_cliente"] : ""; $results[$i]["nombre_cliente"] = !empty($oportunidad["nombre_cliente"]) ? $oportunidad["nombre_cliente"] : ""; $results[$i]["fecha_creacion"] = !empty($oportunidad["fecha_creacion"]) ? $oportunidad["fecha_creacion"] : ""; $results[$i]["usuario_asignado"] = !empty($oportunidad["usuario_asignado"]) ? $oportunidad["usuario_asignado"] : ""; $actividad = array(); if (!empty($oportunidad["uuid_oportunidad"])) { $sql = "SELECT DISTINCT COALESCE(SUM(actta.puntaje), 0) AS puntaje_total\n\t\t\t\t\t\t\tFROM (`act_actividades` AS act)\n\t\t\t\t\t\t\tLEFT JOIN `act_actividades_cat` AS actcat1 ON `actcat1`.`id_cat` = `act`.`relacionado_con`\n\t\t\t\t\t\t\tLEFT JOIN `act_tipo_actividades` AS actta ON `actta`.`uuid_tipo_actividad` = `act`.`uuid_tipo_actividad`\n\t\t\t\t\t\t\tWHERE `actcat1`.`valor` = 'oportunidades'\n\t\t\t\t\t\t\tAND `act`.`uuid_oportunidad` = UNHEX('" . $oportunidad["uuid_oportunidad"] . "')\n\t\t\t\t\t\t\tAND `act`.`completada` = 1"; self::$db->sql($sql); $actividad = self::$db->getResult(); } $results[$i]["puntaje_total"] = 0; if (!empty($actividad[0]["puntaje_total"]) && $actividad[0]["puntaje_total"] > 0) { $results[$i]["puntaje_total"] = !empty($actividad[0]["puntaje_total"]) ? (int) $actividad[0]["puntaje_total"] : 0; $dataset[$i] = (int) $actividad[0]["puntaje_total"]; $i++; } else { unset($results[$i]); unset($dataset[$i]); } } /** * Hack: para que la funcion PHPExcel_Calculation_Statistical::PERCENTRANK * No envie el siguiente error, si el array $dataset contiene un solo valor: * * A PHP Error was encountered * Severity: Warning * Message: Division by zero * Filename: Calculation/Statistical.php */ if (count($dataset) == 1) { $dataset[] = 0; } //ordenar los puntajes //de menor a mayor ksort($dataset); //Recorrer los resultados y calcular el PERCENTRANK //Usando una clase de la libreria PHPEXcel y introducir //el resultado en el array de $results $j = 0; $percentagerank = ""; foreach ($results as $result) { if (!empty($result["puntaje_total"]) && $result["puntaje_total"] != "" && $result["puntaje_total"] > 0) { $percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 2) * 100; $results[$j]["percentagerank"] = !empty($percentagerank) ? $percentagerank : 0; } else { unset($result); } $j++; } } return $results; }
/** * DVARP * * Calculates the variance of a population based on the entire population by using the numbers * in a column of a list or database that match conditions that you specify. * * Excel Function: * DVARP(database,field,criteria) * * @access public * @category Database Functions * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param string|integer $field Indicates which column is used in the function. Enter the * column label enclosed between double quotation marks, such as * "Age" or "Yield," or a number (without quotation marks) that * represents the position of the column within the list: 1 for * the first column, 2 for the second column, and so on. * @param mixed[] $criteria The range of cells that contains the conditions you specify. * You can use any range for the criteria argument, as long as it * includes at least one column label and at least one cell below * the column label in which you specify a condition for the * column. * @return float * */ public static function DVARP($database, $field, $criteria) { $field = self::fieldExtract($database, $field); if (is_null($field)) { return null; } // Return return PHPExcel_Calculation_Statistical::VARP(self::getFilteredColumn($database, $field, $criteria)); }
public static function CalcAllStats1($returnTarget, $returnBenchmark) { // initialize the variables $returnCount = 0; $sumTarget = 0.0; $sumBench = 0.0; $sumTargetMinusBench = 0.0; $sumTargetTimesBench = 0.0; $sumBenchMinusTargetOnBadDays = 0.0; $sumBenchMinusTargetSquaredOnBadDays = 0.0; $sumTargetSquared = 0.0; $sumBenchSquared = 0.0; $returnTargetCumulative = 1.0; $returnBenchCumulative = 1.0; $countGoodDays = 0; $returnMax = -10000.0; $returnMin = 10000.0; $varianceTarget = 0.0; $varianceBench = 0.0; $varianceTargetMinusBench = 0.0; // do the first loop foreach ($returnTarget as $r) { $sumTarget += $r; $sumBench += $returnBenchmark[$returnCount]; $returnTargetCumulative *= $r; $returnBenchCumulative *= $returnBenchmark[$returnCount]; $sumTargetMinusBench += $r - $returnBenchmark[$returnCount]; $sumTargetTimesBench += $r * $returnBenchmark[$returnCount]; $sumTargetSquared += pow($r, 2); $sumBenchSquared += pow($returnBenchmark[$returnCount], 2); if ($r > $returnBenchmark[$returnCount]) { // A good day $countGoodDays++; } if ($r < 1) { //threshold = 0 $sumBenchMinusTargetOnBadDays += $returnBenchmark[$returnCount] - $r; $sumBenchMinusTargetSquaredOnBadDays += pow($returnBenchmark[$returnCount] - $r, 2); } if ($r > $returnMax) { $returnMax = $r; } if ($r < $returnMin) { $returnMin = $r; } $returnCount++; } if ($returnCount > 1) { // calculate some figures $returnTargetCumulative -= 1.0; $returnBenchCumulative -= 1.0; $averageTarget = $sumTarget / $returnCount; $averageBench = $sumBench / $returnCount; $averageTargetMinusBench = $sumTargetMinusBench / $returnCount; $averageBenchMinusTargetOnBadDays = $sumBenchMinusTargetOnBadDays / $returnCount; $averageBenchMinusTargetSquaredOnBadDays = $sumBenchMinusTargetSquaredOnBadDays / $returnCount; $covarTargetBench = ($sumTargetTimesBench - $sumTarget * $sumBench / $returnCount) / ($returnCount - 1); // second loop $i = 0; foreach ($returnTarget as $r) { $varianceTarget += pow($r - $averageTarget, 2); $varianceBench += pow($r - $averageBench, 2); $varianceTargetMinusBench += pow($r - $averageBench - $averageTargetMinusBench, 2); $i++; } // calculate the output $varianceTarget /= $returnCount - 1; $varianceBench /= $returnCount - 1; $varianceTargetMinusBench /= $returnCount - 1; $volTarget = sqrt($varianceTarget) * sqrt(240); $volBench = sqrt($varianceBench) * sqrt(240); $volTargetMinusBench = sqrt($varianceTargetMinusBench) * sqrt(240); if ($varianceBench == 0) { $beta = 0; } else { $beta = $covarTargetBench / $varianceBench; } if ($volTarget > 0) { $sharpe = $returnTargetCumulative / $volTarget; } else { $sharpe = 0; } $alpha = $averageTarget - $beta * $averageBench; if ($beta == 0) { $treynor = 0; } else { $treynor = $returnTargetCumulative / $beta; } $trackingError = $volTargetMinusBench; if ($volTargetMinusBench > 0) { $infoQuota = ($returnTargetCumulative - $returnBenchCumulative) / $volTargetMinusBench; } else { $infoQuota = 0; } $consistency = $countGoodDays / $returnCount; $x = pow(($returnCount * $sumTargetSquared - pow($sumTarget, 2)) * ($returnCount * $sumBenchSquared - pow($sumBench, 2)), 1 / 2); //$x = pow(($returnCount*$sumTargetTimesBench-$sumTarget*$sumBench)/pow(($returnCount*$sumTargetSquared-pow($sumTarget,2))*($returnCount*$sumBenchSquared-pow($sumBench,2)),1/2),2); if ($x > 0) { $R2 = pow(($returnCount * $sumTargetTimesBench - $sumTarget * $sumBench) / $x, 2); } else { $R2 = 0; } if ($averageBenchMinusTargetOnBadDays == 0) { $omega = 0; $sortino = 0; // "Div/Null!"; } else { $omega = 1 + $averageTargetMinusBench / $averageBenchMinusTargetOnBadDays; $sortino = $averageTargetMinusBench / pow($averageBenchMinusTargetOnBadDays, 1 / 2); } $VaR = $averageTargetMinusBench + $volTarget * PHPExcel_Calculation_Statistical::NORMSINV(0.01); //$VaR=$averageTargetMinusBench+$volTarget*$this->inverse_ncdf(0.01); } else { $volTarget = 0; $sharpe = 0; $beta = 0; $treynor = 0; $trackingError = 0; $infoQuota = 0; $consistency = 0; $R2 = 0; $VaR = 0; $averageTarget = 1; $returnMax = 1; $returnMin = 1; $sortino = 0; $omega = 0; $averageTargetMinusBench = 0; $averageBenchMinusTargetOnBadDays = 0; } return array($volTarget, $sharpe, $alpha, $beta, $treynor, $trackingError, $infoQuota, $consistency, $R2, $VaR, $averageTarget - 1, $returnMax - 1, $returnMin - 1, $sortino, $omega, $averageTargetMinusBench, $averageBenchMinusTargetOnBadDays); }