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); }