$result = queryMysql($query);
$query = "ALTER TABLE crsi_portfolio_performance AUTO_INCREMENT = 1 ";
$result = queryMysql($query);
$query = "delete from turtle_transaction_pandl where portfolio_id = ".$pid;
$result = queryMysql($query);
$query = "delete from transactions where portfolio_id = ".$pid;
$result = queryMysql($query);
$query = "ALTER TABLE transactions AUTO_INCREMENT = 1 ";
$result = queryMysql($query);
$query = "delete from open_buy_transaction where portfolio_id = ".$pid;
$result = queryMysql($query);
*/
reset_portfolio($pid);
} elseif ($_GET['action'] == 'get_close_price') {
$symbol = $_GET['symbol'];
$date = $_GET['date'];
$priceArray = array();
$query = "select close from quotes where symbol = '" . $symbol . "' and trade_date = '" . $date . "'";
$result = queryMysql($query);
while ($data = mysql_fetch_row($result)) {
$priceArray[0]['price'] = $data[0];
}
echo json_encode($priceArray);
} elseif ($_GET['action'] == 'get_num_of_trade_days') {
$start_date = $_GET['start_date'];
$end_date = $_GET['end_date'];
// if end date is not supplied, default to today
if (!$end_date) {
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);
}
}
echo json_encode($dateArray);
} elseif ($_GET['action'] == 'simulate_range_trade') {
global $original_investment;
global $breakOutSignal;
global $ADX_filter;
global $breakOutSignal;
global $breakOutOrderBy;
global $simplePriceHistory;
// $spyReturn = array();
$portfolioReturn = array();
$retArray = array();
$count = 0;
$ADX_filter = "Off";
$portfolioID = 1;
reset_portfolio($portfolioID);
$dailyBuyList = "turtle_daily_buy_list";
//print strftime('%c');
$breakOutSignal = $_GET['breakoutSignal'];
//$breakOutSignal = "20_DAY_HIGH";
$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;
$count++;
}
echo json_encode($dateArray);
} elseif ($_GET['action'] == 'simulate_range_trade') {
global $original_investment;
global $breakOutSignal;
global $ADX_filter;
global $breakOutSignal;
global $breakOutOrderBy;
global $simplePriceHistory;
// $spyReturn = array();
$portfolioReturn = array();
$retArray = array();
$count = 0;
$ADX_filter = "Off";
reset_portfolio();
//print strftime('%c');
$breakOutSignal = "55_DAY_HIGH";
$start_date = $_GET['start_date'];
$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;
//$breakOutOrderBy["relative_avg_vol"] = 0.1;
//$breakOutOrderBy["vsSpyEMA"]=1;
$breakOutOrderBy["pct_change"] = $valueOrderByPctChange;
$breakOutOrderBy["relative_avg_vol"] = $valueOrderByRelVol;
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);
}
