} // method ends
private static int ConsumeSplitsUntilTime(Dictionary<IAssetID, AssetDesc> assetDescs, List<SplitAndDividendInfo> splitDivInfosByDate, DbPortfolio portf, int splitIndInclusive, DateTime trTime)
{
for (int i = splitIndInclusive; i < splitDivInfosByDate.Count; i++)
{
if (splitDivInfosByDate[i].TimeUtc > trTime)
break;
SplitAndDividendInfo splitInfo = splitDivInfosByDate[i];
var pipS = portf.TodayPositions.FirstOrDefault(r => r.SubTableID == splitInfo.StockID && r.AssetTypeID == AssetType.Stock);
if (pipS != null)
{
if (splitInfo.IsSplit)
{
// VXX: OldVolume: 4, NewVolume: 1 means that for every 4 old stocks, there is 1 new stock. The Fractional stocks can be calculated using PrevClosePrice as cash
double oldVolume = pipS.Volume;
int nOldGroups = (int)(oldVolume / (double)splitInfo.OldVolume + 0.0001);
double fractionalOldShares = oldVolume - nOldGroups * splitInfo.OldVolume;
int nNewShares = nOldGroups * splitInfo.NewVolume; // rounding to the nearest Integer, but consider these are doubles
double cashSaleInCurrency = fractionalOldShares * splitInfo.DividendOrPrevClosePrice;
SqCommon.Utils.Logger.Debug($"TransactionAccumulator splits. Date:{splitInfo.TimeUtc.ToString("yyyy-MM-dd")}, oldVolume:{oldVolume:F2}, fractionalOldShares:{fractionalOldShares}, nNewShares:{nNewShares}, cashSaleInCurrency:{cashSaleInCurrency:F2}");
pipS.Volume = nNewShares;
pipS.LastSplitAdjustedTransactionPrice *= (double)splitInfo.OldVolume / (double)splitInfo.NewVolume;
// cashSaleInCurrency should be added to Cash, find currencyID and the corresponding cash and book the cash
if (Math.Abs(cashSaleInCurrency) > 0.002)
{
CurrencyID currencyID = assetDescs[pipS.AssetID].CurrencyID;
var pipCashInCurrency = portf.TodayPositions.FirstOrDefault(r => r.AssetTypeID == AssetType.HardCash && r.SubTableID == (int)currencyID);
if (pipCashInCurrency == null)
{
pipCashInCurrency = new PortfolioPosition()
{
AssetID = DbUtils.MakeAssetID(AssetType.HardCash, (int)currencyID),
Volume = 0.0,
LastSplitAdjustedTransactionPrice = 0,
LastTransactionTimeUtc = trTime
};
portf.TodayPositions.Add(pipCashInCurrency);
}
pipCashInCurrency.Volume += cashSaleInCurrency;
}
}
else
throw new Exception("Dividend is not implemented yet.");
}
splitIndInclusive++;
}
return splitIndInclusive;
}
private static void TransactionAccumulator(List<DbPortfolio> p_portfolios, IList<List<object[]>> sqlResult, DateTime p_accumulationEndDate)
{
var fileSystemTbl = sqlResult[0];
List<object[]> transactionsTbl = null; // some portfolios don't have transactions, and SQL DB returns nothing, and sqlResult[1] indexing would crash
if (sqlResult.Count >= 2)
transactionsTbl = sqlResult[1]; // SQL query asked it to be ordered by PortfolioID, Date
else
transactionsTbl = new List<object[]>();
List<object[]> splitDividendTbl = null; // some portfolios don't have splits or dividends, and SQL DB returns nothing, and sqlResult[2] indexing would crash
if (sqlResult.Count >= 3)
splitDividendTbl = sqlResult[2]; // SQL query asked it to be ordered by StockID, Date (Date, StockID would be more useful, but we don't want sorting computation in the SQL server, do it locally)
else
splitDividendTbl = new List<object[]>();
List<object[]> stockAssetTbl = null;
if (sqlResult.Count >= 4)
stockAssetTbl = sqlResult[3]; // sqlResult[3] indexing would crash if it is not there
else
stockAssetTbl = new List<object[]>();
// 1. Prepare data of assets, splits, portfolios
p_portfolios.ForEach(p =>
{
p.TodayPositions = new List<PortfolioPosition>();
p.PortfolioID = (int)fileSystemTbl.Find(row => (string)row[1] == p.Name && (HQUserID)row[2] == p.HQUserID)[0];
});
Dictionary<IAssetID, AssetDesc> assetDescs = new Dictionary<IAssetID, AssetDesc>();
foreach (var stockAsset in stockAssetTbl)
{
int stockID = (int)stockAsset[1];
IAssetID assetID = DbUtils.MakeAssetID(AssetType.Stock, stockID);
assetDescs.Add(assetID, new AssetDesc() { AssetID = assetID, Ticker = (string)stockAsset[2], FullTicker = (string)stockAsset[3], StockExchangeID = (StockExchangeID)(byte)stockAsset[4], CurrencyID = (CurrencyID)(short)stockAsset[5] });
}
var splitDivInfosByDate = splitDividendTbl.Select(r =>
{
int stockID = (int)r[0];
IAssetID assetID = DbUtils.MakeAssetID(AssetType.Stock, stockID);
DateTime dateLocal = (DateTime)r[1];
StockExchangeID stockExchangeId = assetDescs[assetID].StockExchangeID;
DateTime timeUtc = TimeZoneInfo.ConvertTime(dateLocal, DbUtils.StockExchangeToTimeZoneData[(int)stockExchangeId].TimeZoneInfo, TimeZoneInfo.Utc);
return new SplitAndDividendInfo() { StockID = stockID, TimeUtc = timeUtc, IsSplit = (bool)r[2], DividendOrPrevClosePrice = (double)(float)r[3], OldVolume = (int)r[4], NewVolume = (int)r[5] };
}).OrderBy(r => r.TimeUtc).ToList();
// 2. Process transactions one by one
int trInd = 0;
while (trInd < transactionsTbl.Count) // ordered by PortfolioID, Date
{
var transaction = transactionsTbl[trInd];
int portfInd = (int)transaction[0];
DbPortfolio portf = p_portfolios.Where(r => r.PortfolioID == portfInd).FirstOrDefault();
int splitIndInclusive = 0;
while (trInd < transactionsTbl.Count) // essentially while(true), // consume the whole portfolio
{
transaction = transactionsTbl[trInd];
TransactionType transactionType = (TransactionType)transaction[1];
AssetType assetType = (AssetType)transaction[2];
int assetSubTableID = (int)transaction[3];
IAssetID assetID = DbUtils.MakeAssetID(assetType, assetSubTableID);
double volume = (double)(int)transaction[4];
double price = (double)(float)transaction[5];
DateTime trTime = (DateTime)transaction[6];
// 2.1. Process splits until the point of TransactionTime
splitIndInclusive = ConsumeSplitsUntilTime(assetDescs, splitDivInfosByDate, portf, splitIndInclusive, trTime);
// 2.2. Process transaction
var pipT = portf.TodayPositions.FirstOrDefault(r => r.AssetID == assetID);
switch (transactionType)
{
case TransactionType.Deposit:
if (pipT == null)
{
pipT = new PortfolioPosition() { AssetID = assetID };
portf.TodayPositions.Add(pipT);
}
pipT.Volume += (assetType == AssetType.HardCash) ? volume * price : volume; // for Cash, Volume = 1, Price = $5000. The reason was, because in DB, the Volume was Int32, so the Price as Double was used to store non-Integer values
pipT.LastTransactionTimeUtc = trTime;
pipT.LastSplitAdjustedTransactionPrice = price;
break;
case TransactionType.BuyAsset:
case TransactionType.SellAsset: // Shorting is equivalent to Selling. There is no difference in IB. Let's try to use that freedom.
case TransactionType.ShortAsset: // decided that we allow Selling even if we have no position. It is the freedom how IB works. So, in the future we allow it in transactions
case TransactionType.CoverAsset: // in real life at IB, imagine that we have 5 stocks, and we sell 8. The result is that we will have -3 shorts. It is allowed there. Let's allow here too.
double assetVolumeMultiplier = (transactionType == TransactionType.BuyAsset || transactionType == TransactionType.CoverAsset) ? 1.0 : -1.0;
// I. find currencyID and the corresponding cash and book the cash
CurrencyID currencyID = assetDescs[assetID].CurrencyID;
var pipCashInCurrency = portf.TodayPositions.FirstOrDefault(r => r.AssetTypeID == AssetType.HardCash && r.SubTableID == (int)currencyID);
if (pipCashInCurrency == null)
{
pipCashInCurrency = new PortfolioPosition()
{
AssetID = DbUtils.MakeAssetID(AssetType.HardCash, (int)currencyID),
Volume = 0.0,
LastSplitAdjustedTransactionPrice = 0,
LastTransactionTimeUtc = trTime
};
portf.TodayPositions.Add(pipCashInCurrency);
}
pipCashInCurrency.Volume += -1 * assetVolumeMultiplier * volume * price;
// II. add the Asset
if (pipT == null)
{
pipT = new PortfolioPosition() { AssetID = assetID, Volume = 0.0 };
portf.TodayPositions.Add(pipT);
}
//Console.WriteLine($"Transaction ind: {trInd}, OldVolume: {pipT.Volume}, NewVolume: {pipT.Volume + assetVolumeMultiplier * volume} ");
pipT.Volume += assetVolumeMultiplier * volume;
pipT.LastTransactionTimeUtc = trTime;
pipT.LastSplitAdjustedTransactionPrice = price;
if (SqCommon.Utils.IsNearZero(pipT.Volume))
portf.TodayPositions.Remove(pipT);
break;
default:
throw new Exception("not expected transactionType");
}
trInd++;
bool isLastTransactionOfPortfolio = false;
if (trInd >= transactionsTbl.Count)
isLastTransactionOfPortfolio = true;
else
isLastTransactionOfPortfolio = ((int)(transactionsTbl[trInd][0]) != portfInd); // if the next transaction is a different portfolio // remember: transactions are grouped by PortfolioID
if (isLastTransactionOfPortfolio) // exit inner loop
{
// 2.3. consume Splits from last transaction until today/p_accumulationEndDate
splitIndInclusive = ConsumeSplitsUntilTime(assetDescs, splitDivInfosByDate, portf, splitIndInclusive, DateTime.UtcNow);
break;
}
} // inner while for one portfolio
} // outer while for all portfolios
} // method ends