function get_portfolio_risk($trade_date, $portfolioID)
{
#$p_value =get_real_time_turtle_portfolio_value($portfolioID);
$p_value = get_historical_turtle_portfolio_value($trade_date, $portfolioID);
$tmp_sql = "select sum(risk) from turtle_portfolio where symbol != 'CASH' and portfolio_id = " . $portfolioID;
$tmp_result = queryMysql($tmp_sql);
while ($tmp_data = mysql_fetch_row($tmp_result)) {
$r_value = $tmp_data[0];
}
$current_risk = $r_value / $p_value * 100;
return $current_risk;
}
function turtle_portfolio_pyramid_buy($date)
{
global $max_num_holdings;
global $max_risk;
global $risk_factor;
$risk_factor = 1 / $max_num_holdings;
$my_sql = "select a.symbol, a.shares, c.high, c.trade_date, a.stop_loss, a.stop_buy, c.ATR, a.cost_basis, a.risk, a.risk_pct, c.low, c.open from turtle_portfolio a, price_history c where a.symbol = c.symbol and c.trade_date = '" . $date . "' and c.high > a.stop_buy order by c.pct_change desc";
/*if ($date == "2012-12-05")
{
print "pyramid buy: ";
print $my_sql;
print "<br>";
}
*/
$result = queryMysql($my_sql);
while ($data = mysql_fetch_row($result)) {
$this_symbol = $data[0];
$this_shares = $data[1];
$this_today_high = $data[2];
$this_trade_date = $data[3];
$this_stop_loss = $data[4];
$this_stop_buy = $data[5];
$this_ATR = $data[6];
$this_cost_basis = $data[7];
$this_risk = $data[8];
$this_risk_pct = $data[9];
$this_today_low = $data[10];
$this_today_open = $data[11];
### get portfolio value
$portfolio_value = get_historical_turtle_portfolio_value($date);
### get current available cash
$query = "select shares from turtle_portfolio where portfolio_id = 1 and symbol = 'CASH'";
$result2 = queryMysql($query);
while ($data2 = mysql_fetch_row($result2)) {
$cash = $data2[0];
}
$risk_value = $portfolio_value * $risk_factor / 100;
$num_shares = floor($risk_value / (2 * $this_ATR));
// set would-be purchase price of the stock
// if high > moving avg
// AND moving avg > low, then purchase price = moving avg
// AND moving avg < open, the purchase price = opening price
$purchase_price = $this_stop_buy;
if ($this_stop_buy > $this_today_low) {
$purchase_price = $this_stop_buy;
} elseif ($this_stop_buy < $this_today_open) {
$purchase_price = $this_today_open;
} elseif ($this_stop_buy < $this_today_low) {
$purchase_price = $this_today_low;
}
$this_stop_buy = $purchase_price;
// $purchase_value = $num_shares * $this_stop_buy;
$purchase_value = $num_shares * $purchase_price;
$current_risk = get_current_risk();
if ($cash > $purchase_value && $current_risk < $max_risk) {
$stop_loss = $this_stop_buy - 2 * $this_ATR;
$stop_buy = $this_stop_buy + $this_ATR;
$avg_cost_basis = ($this_shares * $this_cost_basis + $purchase_value) / ($this_shares + $num_shares);
$total_shares = $this_shares + $num_shares;
$risk_dollar = $this_shares * (2 * $this_ATR) + $this_risk;
$risk_pct = $risk_dollar / $portfolio_value * 100;
$query3 = "update turtle_portfolio set shares = " . $total_shares . ", cost_basis = " . $avg_cost_basis . ", stop_loss = " . $stop_loss . ", stop_buy=" . $stop_buy . ", risk=" . $risk_dollar . ", risk_pct=" . $risk_pct . " where symbol = '" . $this_symbol . "'";
$result3 = queryMysql($query3);
//print "query 3: $query3 ";
// insert into transaction history table
$query4 = "insert into turtle_portfolio_transaction (portfolio_id, symbol, trade_type, trade_date, shares, price, risk, risk_pct) ";
$query4 .= "values (1, '" . $this_symbol . "', ";
$query4 .= "'BUY', ";
$query4 .= "'" . $date . "', ";
$query4 .= $num_shares . ", ";
$query4 .= $this_stop_buy . ", ";
$query4 .= $risk_dollar . ", ";
$query4 .= $risk_pct . " )";
$result4 = queryMysql($query4);
$cash_left = $cash - $purchase_value;
// update cash position
$query5 = "update turtle_portfolio set shares = " . $cash_left . " where symbol = 'CASH'";
$result5 = queryMysql($query5);
}
}
}
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);
}
function crsi_buy($date, $breakOutSignal, $ADX_filter, $breakOutOrderBy, $portfolioID, $dailyBuyList)
{
global $max_num_holdings;
global $max_risk;
global $risk_factor;
global $stop_loss_muptiplier;
global $portfolio_tbl;
global $showOutput;
if (!$portfolioID) {
$portfolioID = 1;
}
if (!$dailyBuyList) {
$dailyBuyList = "crsi_daily_buy_list";
}
//$breakOutSignal = "55_DAY_HIGH";
### get list of breakout stocks
//$breakOutStockArray = array();
$crsiThreshold = 20;
$pctLimitBelow = 2;
// $breakOutStockArray = getBreakoutStock5 ($date, $breakOutSignal, $breakOutOrderBy, $portfolioID, $dailyBuyList);
// $breakOutStockArray = getBreakoutStock6 ($date, $breakOutSignal, $breakOutOrderBy, $portfolioID, $dailyBuyList);
$breakOutStockArray = getBuyList($date, $crsiThreshold, $breakOutOrderBy, $portfolioID, $dailyBuyList, $dailyBuyList);
// echo json_decode($breakOutStockArray);
// set how many breakout stocks to buy
//$len_array = count($breakOutStockArray);
$len_array = count($breakOutStockArray);
$risk_factor = 1 / $max_num_holdings;
$portfolio_value = 0;
$current_pos = 0;
$next_buy_point = 100000;
$stop_loss = 0;
$num_shares = 0;
$current_N = 0;
$risk_value = 0;
$purchase_value = 0;
$sim_start_day = 55;
$pyramid_mode = 0;
$stop_loss = 0;
$current_trade_date_id = 0;
$workingArray = $breakOutStockArray;
$adxArray = "";
$adxCount = 1;
$len_array = count($breakOutStockArray);
for ($x = 1; $x < $len_array; $x++) {
### get portfolio value
$portfolio_value = get_historical_turtle_portfolio_value($date, $portfolioID);
### get current available cash
$query = "select shares from turtle_portfolio where symbol = 'CASH' and portfolio_id = " . $portfolioID;
$result = queryMysql($query);
while ($data = mysql_fetch_row($result)) {
$cash = $data[0];
}
$risk_value = $portfolio_value * $risk_factor / 100;
$current_N = $breakOutStockArray[$x]['ATR'];
if ($current_N > 0) {
$num_shares = floor($risk_value / ($stop_loss_muptiplier * $current_N));
}
/* $purchase_value = $num_shares * $breakOutStockArray[$x][$breakOutSignal];
$stop_loss = $breakOutStockArray[$x][$breakOutSignal] - (2*$current_N);
$stop_buy = $breakOutStockArray[$x][$breakOutSignal] + $current_N;
*/
## find purchase price
## if target buy price > open (gap down at open, past buy price)
## set purchase price = (open + low ) / 2
## if open > target buy price > close (regular price action
## set purchase price = target buy price
if ($breakOutStockArray[$x]['buy_price'] > $breakOutStockArray[$x]['open']) {
$breakOutStockArray[$x]['purchase_price'] = ($breakOutStockArray[$x]['open'] + $breakOutStockArray[$x]['low']) / 2;
$s = $breakOutStockArray[$x]['symbol'];
$p = $breakOutStockArray[$x]['purchase_price'];
$b = $breakOutStockArray[$x]['buy_price'];
#print "buy price > open: $s buy price = $b purchase price = $p <br />";
} else {
##$breakOutStockArray[$x]['purchase_price'] = ($breakOutStockArray[$x]['high'] + $breakOutStockArray[$x]['buy_price'] + $breakOutStockArray[$x]['low'] + $breakOutStockArray[$x]['close'] )/4;
$breakOutStockArray[$x]['purchase_price'] = $breakOutStockArray[$x]['close'];
$s = $breakOutStockArray[$x]['symbol'];
$p = $breakOutStockArray[$x]['purchase_price'];
$b = $breakOutStockArray[$x]['buy_price'];
$h = $breakOutStockArray[$x]['high'];
$l = $breakOutStockArray[$x]['low'];
$o = $breakOutStockArray[$x]['open'];
$c = $breakOutStockArray[$x]['close'];
#print "regular price: $o high $h low $l close $c : $s buy price = $b purchase price = $p <br />";
}
#$breakOutStockArray[$x]['purchase_price'] = $breakOutStockArray[$x]['buy_price'];
$purchase_value = $num_shares * $breakOutStockArray[$x]['purchase_price'];
$stop_loss = $breakOutStockArray[$x]['purchase_price'] - $stop_loss_muptiplier * $current_N;
$stop_buy = $breakOutStockArray[$x]['purchase_price'] + $current_N;
$current_risk = get_current_risk($portfolioID);
if ($cash > $purchase_value && $current_risk < $max_risk) {
$cash = $cash - $purchase_value;
$risk_dollar = $num_shares * ($stop_loss_muptiplier * $current_N);
$risk_pct = $risk_dollar / $portfolio_value * 100;
// insert into turtle_portfolio
$my_sql = "insert into turtle_portfolio (portfolio_id, symbol, last_price, shares, cost_basis, stop_loss, stop_buy, risk, risk_pct) ";
$my_sql .= "values (" . $portfolioID . ", '" . $breakOutStockArray[$x]['symbol'] . "', ";
$my_sql .= $breakOutStockArray[$x]['close'] . ", ";
$my_sql .= $num_shares . ", ";
// $my_sql .= $breakOutStockArray[$x][$breakOutSignal].", ";
$my_sql .= $breakOutStockArray[$x]['purchase_price'] . ", ";
$my_sql .= $stop_loss . ", ";
$my_sql .= $stop_buy . ", ";
$my_sql .= $risk_dollar . ", ";
$my_sql .= $risk_pct . " )";
if ($showOutput) {
print "num: {$x} sql: {$my_sql} <br />";
}
$result = queryMysql($my_sql);
// trigger on transaction table
/*
DELIMITER $$
CREATE TRIGGER ins_buy AFTER INSERT ON turtle_portfolio_transaction
FOR EACH ROW
BEGIN
DECLARE xid int;
IF (NEW.trade_type = 'BUY')
THEN
insert IGNORE into open_buy_transaction (portfolio_id, symbol, trade_type, trade_date, shares, price) values (NEW.portfolio_id, NEW.symbol, NEW.trade_type, NEW.trade_date, NEW.shares, NEW.price);
ELSE
insert IGNORE into transactions (portfolio_id, symbol, sell_date, sell_shares, sell_price) values (NEW.portfolio_id, NEW.symbol, NEW.trade_date, NEW.shares, NEW.price);
SET xid = select 1 from transactions;
update transactions, open_buy_transaction set transactions.buy_date = open_buy_transaction.trade_date, transactions.buy_shares = open_buy_transaction.shares, transactions.buy_price=open_buy_transaction.price where transactions.symbol = open_buy_transaction and transactions.xid = xid ;
delete from open_buy_transaction where symbol = NEW.symbol
END IF;
END;
$$
DELIMITER ;
*/
// insert into transaction history table
$my_sql = "insert into turtle_portfolio_transaction (portfolio_id, symbol, trade_type, trade_date, shares, price, risk, risk_pct, vsSpyRank) ";
$my_sql .= "values (" . $portfolioID . ", '" . $breakOutStockArray[$x]['symbol'] . "', ";
$my_sql .= "'BUY', ";
$my_sql .= "'" . $date . "', ";
$my_sql .= $num_shares . ", ";
// $my_sql .= $breakOutStockArray[$x][$breakOutSignal].", ";
$my_sql .= $breakOutStockArray[$x]['purchase_price'] . ", ";
$my_sql .= $risk_dollar . ", ";
$my_sql .= $risk_pct . ", ";
$my_sql .= $breakOutStockArray[$x]['vsSpyRank'] . " )";
if ($showOutput) {
print "{$my_sql} <br />";
}
$result = queryMysql($my_sql);
// update cash position
$my_sql = "update turtle_portfolio set shares = " . $cash . " where symbol = 'CASH' and portfolio_id = " . $portfolioID;
if ($showOutput) {
print "{$my_sql} <br />";
}
$result = queryMysql($my_sql);
} elseif ($current_risk > $max_risk) {
$s = $breakOutStockArray[$x]['symbol'];
}
$pyramid_mode++;
}
#$time_end = microtime(true);
#$time = $time_end - $time_first;
#print "-------------------------\n";
#print "second part of turtle buy time: $time \n";
populateDailyBuyList($date, $crsiLimit, $symbol, $portfolioID, $dailyBuyList, $pctLimitBelow);
#populateDailyBuyList ($date, $breakOutSignal, $rankAndWeightArray, $portfolioID, $dailyBuyList);
}
