// for the purposes of establishing profitability of position
public Positions GetInitialPositions()
{
try
{
Positions retlist = new Positions();
Positions allpos = new Positions();
// establish connection
App.OpenConnection();
// step thru open holdings
string sql = "SELECT symbol, transgroupid, type, datetime(expiredate) AS ExpireDate, strike, sum(amount) as amount, datetime(Time) AS TransTime, [Open-Close] FROM transactions";
sql += " WHERE (transgroupid = @gr) and [Open-Close] = 'Open' GROUP BY symbol, type, expiredate, strike, [Open-Close]";
SQLiteCommand cmd = new SQLiteCommand(sql, App.ConnStr);
cmd.Parameters.AddWithValue("gr", this.GroupID);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//decimal strike = reader["strike"].ToString();
decimal strike = 0m;
if (reader["Strike"] != DBNull.Value)
{
strike = Convert.ToDecimal(reader["Strike"]);
}
decimal amount = 0.0m;
if (reader["Amount"] != DBNull.Value)
{
amount = Convert.ToDecimal(reader["Amount"]);
}
DateTime expDate = DateTime.MinValue;
if (reader["ExpireDate"] != DBNull.Value)
{
expDate = Convert.ToDateTime(reader["ExpireDate"].ToString());
}
DateTime transTime = DateTime.MinValue;
if (reader["TransTime"] != DBNull.Value)
{
transTime = Convert.ToDateTime(reader["TransTime"].ToString());
}
transTime = DateTime.SpecifyKind(transTime, DateTimeKind.Utc);
allpos.Add(reader["symbol"].ToString(), reader["type"].ToString(), expDate, strike, 0.0m, amount, transTime, 0, "", 0);
}
// find first day of group
DateTime startDay = DateTime.MaxValue;
foreach (KeyValuePair <string, Position> item in allpos)
{
if (item.Value.TransTime < startDay)
{
startDay = item.Value.TransTime;
}
}
startDay = new DateTime(startDay.Year, startDay.Month, startDay.Day);
// remove all transactions that didn't occur on the first day
foreach (KeyValuePair <string, Position> item in allpos)
{
DateTime day = new DateTime(item.Value.TransTime.Year, item.Value.TransTime.Month, item.Value.TransTime.Day);
if (day == startDay)
{
retlist.Add(item.Value);
}
}
return(retlist);
}
catch (Exception ex)
{
Console.WriteLine("GetOpeningPositions: " + ex.Message);
}
return(null);
}
public string GetHistory()
{
SortedList <DateTime, Positions> data = new SortedList <DateTime, Positions>();
// step thru open transactions
string sql = "SELECT datetime(time) AS Time, symbol, type, datetime(expiredate) AS ExpireDate, strike, quantity, amount, TransSubType FROM transactions";
sql += " WHERE (transgroupid = @gr) ORDER BY time";
SQLiteCommand cmd = new SQLiteCommand(sql, App.ConnStr);
cmd.Parameters.AddWithValue("gr", this.GroupID);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime time = ((reader["Time"] != DBNull.Value) ? Convert.ToDateTime(reader["Time"].ToString()) : DateTime.MinValue);
time = DateTime.SpecifyKind(time, DateTimeKind.Utc);
if (!data.ContainsKey(time))
{
data.Add(time, new Positions());
}
Positions tr = new Positions();
string symbol = reader["Symbol"].ToString();
string type = reader["Type"].ToString();
DateTime expDate = DateTime.MinValue;
if (reader["ExpireDate"] != DBNull.Value)
{
expDate = Convert.ToDateTime(reader["ExpireDate"].ToString());
}
decimal strike = 0;
if (reader["Strike"] != DBNull.Value)
{
strike = Convert.ToDecimal(reader["Strike"]);
}
decimal quantity = Convert.ToDecimal(reader["Quantity"]);
decimal amount = Convert.ToDecimal(reader["Amount"]);
string transType = reader["TransSubType"].ToString();
data[time].Add(symbol, type, expDate, strike, quantity, amount, time, 0, transType, 0);
}
string returnValue = "";
foreach (KeyValuePair <DateTime, Positions> key in data)
{
Positions positions = key.Value;
decimal total = SumAmounts(positions);
returnValue += String.Format("{0} {1} for {2:C0}\n", key.Key.ToString(), GetDescription(positions), total);
foreach (KeyValuePair <string, Position> pkey in positions)
{
Position pos = pkey.Value;
string code = "";
switch (pos.TransType)
{
case "Buy to Open":
code = "BTO";
break;
case "Buy to Close":
code = "BTC";
break;
case "Sell to Open":
code = "STO";
break;
case "Sell to Close":
code = "STC";
break;
}
if (pos.Type == "Stock")
{
returnValue += String.Format(" {0,2} {1} {2}\n", pos.Quantity, pos.Type, code);
}
else
{
returnValue += String.Format(" {0,2} {1} {2} {3:MMMd} {4}\n", pos.Quantity, pos.Type, pos.Strike, pos.ExpDate, code);
}
}
}
return(returnValue);
}
private static string GuessStrategy(Positions positions)
{
string retval = "";
if (positions.Count == 1)
{
retval = positions.ElementAt(0).Value.Quantity < 0 ? "Short " : "Long ";
retval += positions.ElementAt(0).Value.Type;
}
else if (positions.Count == 2)
{
if (positions.ElementAt(0).Value.ExpDate != positions.ElementAt(1).Value.ExpDate)
{
retval = "Calendar Spread";
}
else if (positions.ElementAt(0).Value.Type == positions.ElementAt(1).Value.Type)
{
if ((positions.ElementAt(0).Value.Quantity + positions.ElementAt(1).Value.Quantity) != 0)
{
retval = "Ratio " + positions.ElementAt(0).Value.Type + " Spread";
}
else
{
retval = "Vertical " + positions.ElementAt(0).Value.Type + " Spread";
}
}
else if (positions.ElementAt(0).Value.Strike == positions.ElementAt(1).Value.Strike)
{
retval = "Straddle";
}
else
{
retval = "Strangle";
}
}
else if (positions.Count == 4)
{
decimal sum = 0;
DateTime expDate = DateTime.MinValue;
bool dateError = false;
foreach (KeyValuePair <string, Position> item in positions)
{
Position p = item.Value;
sum += p.Quantity;
if (expDate == DateTime.MinValue)
{
expDate = p.ExpDate; // set date first time thru
}
else if (expDate != p.ExpDate)
{
dateError = true; // set flag if any dates don't match
}
if (sum == 0)
{
if (dateError)
{
retval = "Calendar Spread";
}
else
{
retval = "Iron Condor";
}
}
}
}
return(retval += "");
}
private static void ProcessTransactionGroup(string account, string symbol, DataTable dt, string time, Positions holdings, SortedList <string, string> times)
{
Debug.WriteLine("Entering ProcessTransactionGroup (" + symbol + ")");
// Collect all 'opens' for the selected time
DataRow[] rows = dt.Select("TransTime = '" + time + "'");
if (rows.Count() == 0)
{
return; // nothing found
}
bool somethingAdded = false;
for (int i = 0; i < rows.Count(); i++)
{
DataRow r = rows[i];
DateTime expDate = DateTime.MinValue;
if (r["ExpireDate"] != DBNull.Value)
{
expDate = Convert.ToDateTime(r.Field <string>("ExpireDate").ToString());
}
string type = r.Field <string>("Type").ToString();
decimal strike = 0;
if (r["Strike"] != DBNull.Value)
{
strike = (decimal)r.Field <Double>("Strike");
}
Int32 quantity = (Int32)r.Field <Int64>("Quantity");
decimal amount = 0;
if (r["Amount"] != DBNull.Value)
{
amount = (decimal)r.Field <Double>("Amount");
}
Int32 row = (Int32)r.Field <Int64>("ID");
string openClose = r.Field <string>("Open-Close").ToString();
int grpID = 0;
if (r["TransGroupID"] != DBNull.Value)
{
grpID = (int)r.Field <Int64>("TransGroupID");
}
if (openClose == "Open")
{
bool process = true;
if ((type == "Stock") && (r.Field <string>("TransType").ToString() != "Trade"))
{
// need to handle stock activity at expiration differently since there are 'opens' that can cause
// all other transactions for symbol to be sucked up into this chain
decimal price = (decimal)r.Field <Double>("Price");
// there is an option with this price already in the chain
process = holdings.Includes("", Convert.ToDateTime(time).Date, price);
}
if (process)
{
// add transaction to the chain
string key = holdings.Add(symbol, type, expDate, strike, quantity, amount, null, row, openClose, grpID);
Debug.WriteLine(" Opening transaction added to holdings: " + key + " " + r.Field <Int64>("Quantity").ToString() + " " + time.ToString());
// add the associated time to the hierarchy for chain
if (!times.ContainsKey(time))
{
times.Add(time, time);
}
somethingAdded = true;
}
}
}
if (!somethingAdded)
{
return;
}
// run thru what is found, and search for matching/closing transactions
for (int i = 0; i < holdings.Count; i++)
{
Position p = holdings[holdings.Keys.ElementAt(i)];
if (p.Quantity != 0)
{
Func <DataRow, bool> func;
// define the appropriate linq filter
if (p.Type == "Stock")
{
func = r => (r.Field <string>("Type")) == p.Type &&
r.Field <string>("Open-Close") == "Close" &&
Convert.ToDateTime(r.Field <string>("TransTime")) >= Convert.ToDateTime(time);
}
else
{
func = r => (r.Field <string>("Type")) == p.Type &&
(decimal)r.Field <Double>("Strike") == p.Strike &&
Convert.ToDateTime(r.Field <string>("ExpireDate")) == p.ExpDate && r.Field <string>("Open-Close") == "Close" &&
Convert.ToDateTime(r.Field <string>("TransTime")) >= Convert.ToDateTime(time);
}
var closingRow = dt.AsEnumerable()
.Where(func);
// generally should only be one transaction, but occassionaly the closing transaction can split lots accross multiple transactions
foreach (DataRow r in closingRow)
{
bool process = true;
if ((r.Field <string>("Type") == "Stock") && (r.Field <string>("TransType").ToString() != "Trade"))
{
// need to handle stock activity at expiration differently since there are 'closes' that can cause
// all other transactions for symbol to be sucked up into this chain
decimal price = (decimal)r.Field <Double>("Price");
// there is an option with this price already in the chain
process = holdings.Includes("", Convert.ToDateTime(time).Date, price);
}
if (process)
{
int grpID = 0;
if (r["TransGroupID"] != DBNull.Value)
{
grpID = (int)r.Field <Int64>("TransGroupID");
}
string key = holdings.Add(p.Symbol, p.Type, p.ExpDate, p.Strike, (Int32)r.Field <Int64>("Quantity"), (Int32)r.Field <Int64>("ID"), r.Field <string>("Open-Close").ToString(), grpID);
Debug.WriteLine(" Closing transaction added to holdings: " + key + " " + r.Field <Int64>("Quantity").ToString() + " " + time.ToString());
// add the associated time to the hierarchy for chain
string t = r.Field <string>("TransTime");
if (!times.ContainsKey(t))
{
times.Add(t, t);
}
}
if (p.Quantity == 0)
{
break;
}
}
}
}
// purge out collected rows from original table with all transactions for a given symbol
List <int> idList = holdings.GetRowNumbers();
foreach (int id in idList)
{
rows = dt.Select("ID = " + id.ToString());
for (int i = 0; i < rows.Count(); i++)
{
rows[i].Delete();
}
dt.AcceptChanges();
}
// recurse for anything else in the transaction that might open new positions
for (int i = 0; i < times.Count(); i++)
{
string t = times[times.Keys[i]];
if (Convert.ToDateTime(t) > Convert.ToDateTime(time))
{
ProcessTransactionGroup(account, symbol, dt, t, holdings, times);
}
}
Debug.WriteLine("Exiting ProcessTransactionGroup (" + symbol + ")");
}
//
// Header function for setting up recursion
// starts with data table of all possible transaction, account/symbol and a particular transaction time
// chain is built from there
//
private static void ProcessTransactionChain(string account, string symbol, DataTable dt, string time)
{
Positions holdings = new Positions();
SortedList <string, string> times = new SortedList <string, string>();
// start recursion
ProcessTransactionGroup(account, symbol, dt, time, holdings, times);
Debug.WriteLine("First pass of chain completed. Retrieving manually matched transactions...");
// retrieve existing group id
int groupID = holdings.GroupID();
Debug.WriteLine("GroupID: " + groupID.ToString());
// Collect remaining transactions that have been manually merged
if (groupID > 0)
{
DataRow[] remainingRows = dt.Select("TransGroupID = " + groupID.ToString());
while (remainingRows.Length > 0)
{
string t = remainingRows[0]["TransTime"].ToString(); // maintain sqlite date format as a string
ProcessTransactionGroup(account, symbol, dt, t, holdings, times);
//refresh
remainingRows = dt.Select("TransGroupID = " + groupID.ToString());
}
}
Debug.WriteLine("Chain completed for GroupID: " + groupID.ToString());
try
{
if (groupID > 0)
{
// update chain status
string sql = "UPDATE TransGroup SET Open = @op WHERE ID=@row";
SQLiteCommand cmdUpd = new SQLiteCommand(sql, App.ConnStr);
cmdUpd.Parameters.AddWithValue("op", !holdings.IsAllClosed());
cmdUpd.Parameters.AddWithValue("row", groupID);
cmdUpd.ExecuteNonQuery();
}
else
{
string sql = "INSERT INTO TransGroup(Account, Symbol, Open, Strategy, DefinedRisk, NeutralStrategy, CapitalRequired, Risk) Values(@ac,@sym,@op,@str,@dr,@ns,@cap,@rsk)";
SQLiteCommand cmd = new SQLiteCommand(sql, App.ConnStr);
cmd.Parameters.AddWithValue("ac", account);
cmd.Parameters.AddWithValue("sym", symbol);
cmd.Parameters.AddWithValue("op", !holdings.IsAllClosed());
string strat = GuessStrategy(holdings);
cmd.Parameters.AddWithValue("str", strat);
cmd.Parameters.AddWithValue("dr", DefaultDefinedRisk(strat));
cmd.Parameters.AddWithValue("ns", DefaultNeutralStrategy(strat));
decimal capital = DefaultCapital(account, strat, holdings);
cmd.Parameters.AddWithValue("rsk", DefaultRisk(strat, capital, holdings));
cmd.Parameters.AddWithValue("cap", FindTWMarginRequirement(account, holdings));
cmd.ExecuteNonQuery();
groupID = DBUtilities.GetMax("SELECT max(id) FROM TransGroup");
}
}
catch (Exception ex)
{
Debug.WriteLine("ProcessTransactionChain (Update/insert TransGroup): " + ex.Message + "(groupID: " + groupID.ToString() + ")");
}
try
{
List <int> rows = holdings.GetRowNumbers();
foreach (int r in rows)
{
// update all of the rows in the chain
string sql = "UPDATE transactions SET TransGroupID = @id WHERE ID=@row";
SQLiteCommand cmdUpd = new SQLiteCommand(sql, App.ConnStr);
cmdUpd.Parameters.AddWithValue("id", groupID);
cmdUpd.Parameters.AddWithValue("row", r);
cmdUpd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine("ProcessTransactionChain (Updating transactions): " + ex.Message);
}
}
