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