function simulate_range_trade($portfolioID, $start_date, $end_date, $enterCRSI, $enterRange, $enterLimit, $exitCRSI, $commission, $max_risk, $risk_factor, $stop_loss_multiplier, $orderBy) { global $original_investment; global $breakOutSignal; global $ADX_filter; global $breakOutSignal; global $breakOutOrderBy; global $simplePriceHistory; global $showOutput; global $risk_factor; global $max_risk; global $stop_loss_multiplier; global $tranHistArray; global $portfolio_table; $dailyBuyList = "crsi_daily_buy_list" . $portfolioID; ### drop recreate daily buy list table $query = "drop table if exists " . $dailyBuyList; $result = queryMysql($query); $create_sql = "CREATE TABLE {$dailyBuyList} ( "; $create_sql .= "portfolio_id int(11) DEFAULT NULL, "; $create_sql .= "rank int(11) NOT NULL DEFAULT '0', "; $create_sql .= "trade_date date DEFAULT NULL, "; $create_sql .= "symbol varchar(6) COLLATE latin1_german2_ci DEFAULT NULL, "; $create_sql .= "buy_price double DEFAULT NULL, "; $create_sql .= "UNIQUE KEY uniq_idx (portfolio_id,symbol), "; $create_sql .= "KEY pid_idex (portfolio_id) "; $create_sql .= ") ENGINE=MEMORY "; $result = queryMysql($create_sql); $tranHistory = "turtle_portfolio_transaction" . $portfolioID; reset_portfolio($portfolioID); prepare_transactions_table($portfolioID, $tranHistory, $portfolio_table); $query = "select trade_date from quotes_memory where symbol = 'AAPL' and trade_date >= '" . $start_date . "' and trade_date <= '" . $end_date . "' order by trade_date"; $result = queryMysql($query); $dateArray = array(); $dateCount = 0; while ($data = mysql_fetch_row($result)) { $dateArray[$dateCount]['trade_date'] = $data[0]; $dateCount++; } $count = 0; $minReturn = 100; $maxReturn = -100; $retArray = array(); for ($x = 0; $x < $dateCount - 1; $x++) { #while ($data = mysql_fetch_row($result)) { # $trade_date = $data[0]; $trade_date = $dateArray[$x]['trade_date']; $next_trade_date = $dateArray[$x + 1]['trade_date']; if ($showOutput) { print "trade date: {$trade_date} <br />"; } crsi_sell($trade_date, $portfolioID, $tranHistory, $exitCRSI, $stopLoss, $next_trade_date); crsi_buy($trade_date, $breakOutSignal, $ADX_filter, $breakOutOrderBy, $portfolioID, $dailyBuyList, $enterCRSI, $enterRange, $enterLimit, $tranHistory, $orderBy, $next_trade_date); $value = get_historical_turtle_portfolio_value($trade_date, $portfolioID); $preturn = ($value - $original_investment) / $original_investment * 100; $dollar_return = $value - $original_investment; if ($preturn > $maxReturn) { $maxReturn = $preturn; } if ($preturn < $minReturn) { $minReturn = $preturn; } $portfolioReturn[$count]['trade_date'] = $trade_date; $portfolioReturn[$count]['return'] = $preturn; $portfolioReturn[$count]['value'] = $value; $portfolioReturn[$count]['maxReturn'] = $maxReturn; $portfolioReturn[$count]['minReturn'] = $minReturn; $portfolioReturn[$count]['dollar_return'] = $dollar_return; $newDateStr = strtotime($trade_date); $newDateStr = $newDateStr * 1000 - 14400000; array_push($retArray, array($newDateStr, $preturn)); $count++; } ## batch insert into crsi_portfolio_performance afterward $sql = array(); foreach ($portfolioReturn as $row) { $sql[] = '(' . $portfolioID . ", '" . mysql_real_escape_string($row['trade_date']) . "', " . $row['dollar_return'] . ", " . $row['return'] . ", " . $row['value'] . ")"; } try { mysql_query('INSERT INTO crsi_portfolio_performance (portfolio_id, trade_date, return_dollar, return_pct, portfolio_value) VALUES ' . implode(',', $sql)); } catch (Exception $e) { echo "Query: {$query} \n"; echo 'Caught exception: ', $e->getMessage(), "\n"; } foreach ($tranHistArray as $th) { try { queryMysql($th); } catch (Exception $e) { echo "Query: {$th} \n"; echo 'Caught exception: ', $e->getMessage(), "\n"; } } mysql_close(); return json_encode($retArray); }
$dailyBuyList = "crsi_daily_buy_list" . $portfolioID; ### drop recreate daily buy list table $query = "drop table if exists " . $dailyBuyList; $result = queryMysql($query); $create_sql = "CREATE TABLE {$dailyBuyList} ( "; $create_sql .= "portfolio_id int(11) DEFAULT NULL, "; $create_sql .= "rank int(11) NOT NULL DEFAULT '0', "; $create_sql .= "trade_date date DEFAULT NULL, "; $create_sql .= "symbol varchar(6) COLLATE latin1_german2_ci DEFAULT NULL, "; $create_sql .= "buy_price double DEFAULT NULL, "; $create_sql .= "UNIQUE KEY uniq_idx (portfolio_id,symbol), "; $create_sql .= "KEY pid_idex (portfolio_id) "; $create_sql .= ")"; $result = queryMysql($create_sql); $tranHistory = "turtle_portfolio_transaction" . $portfolioID; prepare_transactions_table($portfolioID, $tranHistory); $breakOutSignal = $_GET['breakoutSignal']; $start_date = $_GET['start_date']; $end_date = $_GET['end_date']; // if end date is not supplied, default to today if (!$end_date) { $end_date = date("Y-m-d"); } $ADX_filter = $_GET['adx_filter']; $breakOutSignal = $_GET['breakoutSignal']; $breakOutOrderBy = $_GET['breakoutOrderBy']; $valueOrderByPctChange = 1.0001 - $_GET['valueOrderByPctChange'] / 100; $valueOrderByRelVol = 1.0001 - $_GET['valueOrderByRelVol'] / 100; $valueOrderByVsSpy = 1.0001 - $_GET['valueOrderByVsSpy'] / 100; $breakOutOrderBy = array(); //$breakOutOrderBy["pct_change"] = 0.1;
function simulate_range_trade($portfolioID, $start_date, $end_date, $enterCRSI, $enterRange, $enterLimit, $exitCRSI, $commission, $max_risk, $risk_factor, $stop_loss_multiplier, $orderBy, $skipFactor, $cash) { global $original_investment; global $breakOutSignal; global $ADX_filter; global $breakOutSignal; global $breakOutOrderBy; global $simplePriceHistory; global $showOutput; global $tranHistArray; global $portfolio_table; global $transactionHistory; global $portfolioArray; global $cash_balance; $dailyBuyList = "crsi_daily_buy_list" . $portfolioID; ### drop recreate daily buy list table $query = "drop table if exists " . $dailyBuyList; $result = queryMysql($query); $create_sql = "CREATE TABLE {$dailyBuyList} ( "; $create_sql .= "portfolio_id int(11) DEFAULT NULL, "; $create_sql .= "rank int(11) NOT NULL DEFAULT '0', "; $create_sql .= "trade_date date DEFAULT NULL, "; $create_sql .= "symbol varchar(6) COLLATE latin1_german2_ci DEFAULT NULL, "; $create_sql .= "buy_price double DEFAULT NULL, "; $create_sql .= "KEY p_idex (symbol) "; $create_sql .= ") ENGINE=MEMORY "; $result = queryMysql($create_sql); $tranHistory = "turtle_portfolio_transaction" . $portfolioID; reset_portfolio($portfolioID); prepare_transactions_table($portfolioID, $tranHistory, $portfolio_table); if ($cash) { $original_investment = $cash; } $cash_balance = $original_investment; $portfolioArray = array(); $query = "select trade_date from quotes_memory where symbol = 'AAPL' and trade_date >= '" . $start_date . "' and trade_date <= '" . $end_date . "' order by trade_date"; $result = queryMysql($query); $dateArray = array(); $dateCount = 0; while ($data = mysql_fetch_row($result)) { $dateArray[$dateCount]['trade_date'] = $data[0]; $dateCount++; } $count = 0; $minReturn = 100; $maxReturn = -100; $retArray = array(); for ($x = 0; $x < $dateCount - 1; $x++) { #while ($data = mysql_fetch_row($result)) { # $trade_date = $data[0]; $trade_date = $dateArray[$x]['trade_date']; $next_trade_date = $dateArray[$x + 1]['trade_date']; if ($showOutput) { print "trade date: {$trade_date} <br />"; } crsi_buy_and_sell($trade_date, $breakOutSignal, $ADX_filter, $breakOutOrderBy, $portfolioID, $dailyBuyList, $enterCRSI, $exitCRSI, $enterRange, $enterLimit, $tranHistory, $orderBy, $next_trade_date, $skipFactor, $stop_loss_multiplier, $max_risk, $risk_factor, $next_trade_date); $portfolio_value = 0; #calculate portfolio value foreach (array_keys($portfolioArray) as $key) { $portfolio_value += $portfolioArray[$key]['shares'] * $portfolioArray[$key]['adj_close']; } $portfolio_value += $cash_balance; $value = $portfolio_value; $preturn = ($value - $original_investment) / $original_investment * 100; $dollar_return = $value - $original_investment; if ($preturn > $maxReturn) { $maxReturn = $preturn; } if ($preturn < $minReturn) { $minReturn = $preturn; } $portfolioReturn[$count]['trade_date'] = $trade_date; $portfolioReturn[$count]['return'] = $preturn; $portfolioReturn[$count]['value'] = $value; $portfolioReturn[$count]['maxReturn'] = $maxReturn; $portfolioReturn[$count]['minReturn'] = $minReturn; $portfolioReturn[$count]['dollar_return'] = $dollar_return; $newDateStr = strtotime($trade_date); $newDateStr = $newDateStr * 1000 - 14400000; array_push($retArray, array($newDateStr, $preturn)); $count++; } ## batch insert into crsi_portfolio_performance afterward $sql = array(); foreach ($portfolioReturn as $row) { $sql[] = '(' . $portfolioID . ", '" . mysql_real_escape_string($row['trade_date']) . "', " . $row['dollar_return'] . ", " . $row['return'] . ", " . $row['value'] . ")"; } try { mysql_query('INSERT INTO crsi_portfolio_performance (portfolio_id, trade_date, return_dollar, return_pct, portfolio_value) VALUES ' . implode(',', $sql)); } catch (Exception $e) { echo "Query: {$query} \n"; echo 'Caught exception: ', $e->getMessage(), "\n"; } foreach ($tranHistArray as $th) { try { #echo "sql: $th ", PHP_EOL; queryMysql($th); } catch (Exception $e) { echo "Query: {$th} \n"; echo 'Caught exception: ', $e->getMessage(), "\n"; } } echo "var dump", PHP_EOL; echo var_dump($portfolioArray); /* $current_holding_string = "("; foreach(array_keys($portfolioArray) as $key){ $current_holding_string .= "'".$key."',"; } $current_holding_string .= " '' ) "; */ /* $sql = array(); foreach( $transactionHistory as $row ) { $sql[] = '('.$portfolioID.", '".$row['symbol']."', '".$row['buy_sell']."', '".mysql_real_escape_string($row['trade_date'])."', ".$row['shares'].", ".$row['price'].", ".$row['risk'].", ".$row['risk_pct'].", ".$row['trade_time'].", ".$row['gain_loss'].", ".$row['holding_period'].", ".$row['vsSpyRank'].")"; } try { echo "execute my sql"; mysql_query('INSERT INTO '.$tranHistory.' (portfolio_id, symbol, trade_type, trade_date, shares, price, risk, risk_pct, trade_time, gain_loss, holding_period, vsSpyRank) VALUES '.implode(',', $sql)); echo "result: ", $result, PHP_EOL; } catch (Exception $e) { echo "Query: $query \n"; echo 'Caught exception: ', $e->getMessage(), "\n"; } */ #echo "sql", $sql, PHP_EOL; #var_dump ($sql); $my_sql = "update {$portfolio_table} set shares = " . $cash_balance . " where symbol = 'CASH' and portfolio_id = " . $portfolioID; $result = queryMysql($my_sql); foreach (array_keys($portfolioArray) as $key) { #$portfolio_value += $portfolioArray[$key]['shares'] * $portfolioArray[$key]['adj_close']; $my_sql = "insert into {$portfolio_table} (portfolio_id, symbol, shares, risk, risk_pct, cost_basis) values ({$portfolioID}, '" . $key . "', " . $portfolioArray[$key]['shares'] . ", " . $portfolioArray[$key]['risk'] . ", " . $portfolioArray[$key]['risk_pct'] . ", " . $portfolioArray[$key]['cost_basis'] . ")"; $result = queryMysql($my_sql); } mysql_close(); return json_encode($retArray); }