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