public ProfilesUpdateSummaryResultModel BatchUpdateProfiles(string currentInvestor) { // Partially updates all investors' asset Profiles (freq, months, & payday), based on *12 month* revenue history. int updateCount = 0; IncomeData.IncomeDataProcessing incomeDataAccessComponent = new IncomeData.IncomeDataProcessing(_ctx); CommonSvc svc = new CommonSvc(_ctx); ProfilesUpdateSummaryResultModel results = new ProfilesUpdateSummaryResultModel(); ProfileForUpdateVm processedResults = incomeDataAccessComponent.Process12MosRevenueHistory(svc.GetInvestorIdFromInvestor(currentInvestor)); if (!processedResults.UpdateHasErrors) { List <Data.Entities.Profile> profilesToUpdate = processedResults.BatchProfilesList; try { _ctx.UpdateRange(profilesToUpdate); updateCount = _ctx.SaveChanges(); } catch (Exception ex) { Log.Error("Error updating Profile(s) via ProfileDataProcessing.BatchUpdateProfiles(), due to :{0} '", ex.InnerException + "'"); return(null); } } else { Log.Error("Error processing revenue history for dividend payment data, recorded via ProfileDataProcessing.BatchUpdateProfiles()."); return(null); } // Initialize with just relevant info, for use by UI. results.ProcessedTickersCount = updateCount > 0 ? updateCount : 0; results.OmittedTickers = processedResults.ExceptionTickerSymbols != string.Empty ? processedResults.ExceptionTickerSymbols.Trim() : string.Empty; Log.Information("Updated " + results.ProcessedTickersCount + " Profiles based on revenue history, for " + currentInvestor); if (results.OmittedTickers != string.Empty) { Log.Information("Omitted Profile updates based on revenue history for " + results.OmittedTickers); } return(results); }
public Dictionary <string, string> CalculateDivFreqAndDivMonths(string tickerSymbol, Data.PIMS3Context ctx) { StringBuilder oneYearDivPayMonths_1 = new StringBuilder(); string oneYearDivPayMonths_2 = string.Empty; int divFreqCounter = 0; string tempDate = ""; var profileDataAccessComponent = new ProfileDataProcessing(ctx); JArray orderedJsonTickerPriceData = profileDataAccessComponent.FetchDividendSpecsForTicker(tickerSymbol); if (orderedJsonTickerPriceData == null) { Log.Warning("ProfileProcessing.CalculateDivFreqAndDivMonths() - found unresolved ticker in Tiingo service, unable to update Profile price data for {0}: ", tickerSymbol); return(null); } List <int> divPayoutDays = new List <int>(); foreach (JObject objChild in orderedJsonTickerPriceData.Children <JObject>()) { // Loop will key in on "divCash" for gathering needed freq & month specs. foreach (var property in objChild.Properties()) { if (property.Name == "date") { tempDate = property.Value.ToString(); } if (property.Name == "divCash") { if (decimal.Parse(property.Value.ToString()) > 0) { divFreqCounter += 1; oneYearDivPayMonths_1.Append(ExtractMonthFromDivPayDate(tempDate)); divPayoutDays.Add(ExtractDivPayDay(tempDate)); oneYearDivPayMonths_1.Append(","); } } } } int medianDivPayoutDay = CommonSvc.CalculateMedianValue(divPayoutDays); if (oneYearDivPayMonths_1.Length == 0) { return(null); } // Strip trailing comma. oneYearDivPayMonths_2 = oneYearDivPayMonths_1.ToString().Substring(0, oneYearDivPayMonths_1.Length - 1); string[] oneYearDivPayMonths_3 = oneYearDivPayMonths_2.Split(','); Dictionary <string, string> finalProfileSpecs = new Dictionary <string, string>(); int monthsCount = oneYearDivPayMonths_3.Length; /* == finalProfileSpecs Keys legend: == * "DF"(dividend frequency) * "DM"(dividend months) * "DPD"(dividend payout day) */ if (monthsCount >= 3 && monthsCount <= 5) { // Due to possible inconsistent number of income receipts made within the last 12 month price history obtained // from our 3rd party service (Tiingo), we'll account for this by designating as (Q)uarterly dividend frequency. finalProfileSpecs.Add("DF", "Q"); finalProfileSpecs.Add("DM", oneYearDivPayMonths_2); } else if (monthsCount >= 5) { finalProfileSpecs.Add("DF", "M"); finalProfileSpecs.Add("DM", "-"); } else if (monthsCount == 2) { finalProfileSpecs.Add("DF", "S"); finalProfileSpecs.Add("DM", oneYearDivPayMonths_2); } else { finalProfileSpecs.Add("DF", "A"); finalProfileSpecs.Add("DM", oneYearDivPayMonths_2); } if (medianDivPayoutDay > 0) { finalProfileSpecs.Add("DPD", medianDivPayoutDay.ToString()); } return(finalProfileSpecs); }
public IEnumerable <Data.Entities.Income> ParseRevenueSpreadsheetForIncomeRecords(string filePath, ImportFileDataProcessing dataAccessComponent, string loggedInvestorId) { List <Data.Entities.Income> newIncomeListing = new List <Data.Entities.Income>(); IncomeDataProcessing incomeDataAccessComponent = new IncomeDataProcessing(_ctx); DataAccess.Asset.AssetData assetDataAccessComponent = new DataAccess.Asset.AssetData(_ctx); IQueryable <string> fetchedPositionId; try { FileInfo importFile = new FileInfo(filePath); using (var package = new ExcelPackage(importFile)) { ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; int totalRows = workSheet.Dimension.End.Row; int totalColumns = workSheet.Dimension.End.Column; _xlsTickerSymbolsOmitted = string.Empty; for (var rowNum = 2; rowNum <= totalRows; rowNum++) { // Validate XLS IEnumerable <string> headerRow = workSheet.Cells[1, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString()); if (!ValidateFileAttributes(true, headerRow) || !ValidateFileType(filePath)) { return(null); } IEnumerable <string> row = workSheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString()); string[] enumerableCells = row as string[] ?? row.ToArray(); // 'totalRows' may yield inaccurate results; we'll test for last record, e.g., 'enumerableCells[0] ('Recvd Date'). if (!enumerableCells.Any() || enumerableCells[0] == "") { if (_xlsTickerSymbolsOmitted.Any()) { dataAccessComponent._exceptionTickers = _xlsTickerSymbolsOmitted; return(null); } return(newIncomeListing); } string xlsTicker = enumerableCells.ElementAt(3).Trim(); string xlsAccount = CommonSvc.ParseAccountTypeFromDescription(enumerableCells.ElementAt(1).Trim()); fetchedPositionId = assetDataAccessComponent.FetchPositionId(loggedInvestorId, xlsTicker, xlsAccount).AsQueryable(); // Checking PositionId rather than asset is sufficient. // Validate either a bad ticker symbol, or that no account was affiliated with the position/asset in question. if (!fetchedPositionId.Any()) { if (_xlsTickerSymbolsOmitted == string.Empty) { _xlsTickerSymbolsOmitted += xlsTicker; } else { _xlsTickerSymbolsOmitted += ", " + xlsTicker; } continue; } duplicateResults = incomeDataAccessComponent.FindIncomeDuplicates(fetchedPositionId.First().ToString(), enumerableCells.ElementAt(0), enumerableCells.ElementAt(4)); if (duplicateResults.Any()) { if (_xlsTickerSymbolsOmitted == string.Empty) { _xlsTickerSymbolsOmitted += xlsTicker; } else { _xlsTickerSymbolsOmitted += ", " + xlsTicker; } continue; } if (_xlsTickerSymbolsOmitted != string.Empty) { _viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted; } Data.Entities.Income newIncomeRecord = new Data.Entities.Income { IncomeId = Guid.NewGuid().ToString(), PositionId = fetchedPositionId.First().ToString(), DateRecvd = DateTime.Parse(enumerableCells.ElementAt(0)), AmountRecvd = decimal.Parse(enumerableCells.ElementAt(4)), LastUpdate = DateTime.Now }; newIncomeListing.Add(newIncomeRecord); _totalXlsIncomeRecordsToSave += 1; newIncomeRecord = null; } // end 'for' if (_xlsTickerSymbolsOmitted.Length > 0) { _viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted; Log.Warning("Invalid XLS/XLSX position(s) found, revenue import aborted for {0}.", _xlsTickerSymbolsOmitted); } return(newIncomeListing); } // end 'using' } catch (Exception ex) { if (ex.Message.Length > 0) { Log.Error("Invalid xlsx format, or bad file path found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords(), due to {0}.", ex.Message); } else { Log.Error("Error found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords()."); } return(null); } }
public ProfileForUpdateVm Process12MosRevenueHistory(string investorId) { // Revenue processing in response to investor-initiated Profile updating. ProfileForUpdateVm vm = new ProfileForUpdateVm(); try { string exceptionTickers = string.Empty; IQueryable <string> currentPositions = _ctx.Position.Where(p => p.PositionAsset.InvestorId == investorId && p.Status == "A") .Select(p => p.PositionAsset.Profile.TickerSymbol) .Distinct() .AsQueryable(); IQueryable <PositionIncomeVm> joinedPositionIncome = _ctx.Position.Where(p => p.PositionAsset.InvestorId == investorId && p.Status == "A") .Join(_ctx.Income, p => p.PositionId, i => i.PositionId, (p, i) => new PositionIncomeVm { ProfileId = p.PositionAsset.Profile.ProfileId, PositionId = p.PositionId, TickerSymbol = p.PositionAsset.Profile.TickerSymbol, Account = p.AccountType.AccountTypeDesc, DateRecvd = i.DateRecvd, DividendYield = Convert.ToDecimal(p.PositionAsset.Profile.DividendYield), TickerDescription = p.PositionAsset.Profile.TickerDescription }) .OrderBy(results => results.TickerSymbol) .ThenBy(results => results.Account) .AsQueryable(); joinedPositionIncome.OrderBy(pi => pi.DateRecvd); // To be initialized return collection. List <Data.Entities.Profile> updateProfileModelList = new List <Data.Entities.Profile>(); foreach (string currentTicker in currentPositions) { // Do we have income data for this position that is greater than or equal to a least a year old, as is necessary // for accurate calculation of both 1) dividend distribution frequency, and 2) dividend payout months values. // Back-dated info is always from the 1st of the calculated year ago month. int olderThanOneYearRevenueCount = joinedPositionIncome .Where(pi => pi.TickerSymbol == currentTicker && pi.DateRecvd <= DateTime.Now.AddMonths(-12).AddDays(-DateTime.Now.Day)).Count(); if (olderThanOneYearRevenueCount > 0) { Data.Entities.Profile updateProfileModel = new Data.Entities.Profile { TickerSymbol = currentTicker }; IQueryable <PositionIncomeVm> withinLastYearData = joinedPositionIncome .Where(pi => pi.TickerSymbol == currentTicker && pi.DateRecvd >= DateTime.Now.AddMonths(-12).AddDays(-DateTime.Now.Day)) .AsQueryable(); // Investor may have same position in multiple accounts, e.g. IRA, Roth-IRA. int dupAcctCount = withinLastYearData.Select(pi => pi.Account).Distinct().Count(); if (dupAcctCount > 1) { IQueryable <PositionIncomeVm> withUniqueAcct = withinLastYearData .Where(pi => pi.Account == withinLastYearData.First().Account).OrderBy(pi => pi.TickerSymbol); updateProfileModel.DividendFreq = CalculateDividendFrequency(withUniqueAcct); updateProfileModel.DividendMonths = updateProfileModel.DividendFreq != "M" ? CalculateDividendMonths(withUniqueAcct) : "N/A"; updateProfileModel.DividendPayDay = CommonSvc.CalculateMedianValue(BuildDivDaysList((withUniqueAcct))); } else { updateProfileModel.DividendFreq = CalculateDividendFrequency(withinLastYearData); updateProfileModel.DividendMonths = updateProfileModel.DividendFreq != "M" ? CalculateDividendMonths(withinLastYearData) : "N/A"; updateProfileModel.DividendPayDay = CommonSvc.CalculateMedianValue(BuildDivDaysList((withinLastYearData))); } // Satisfy 'required' Profile attributes & ProfileId for updating. updateProfileModel.ProfileId = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().ProfileId; updateProfileModel.DividendYield = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().DividendYield; updateProfileModel.TickerDescription = joinedPositionIncome.Where(d => d.TickerSymbol == currentTicker).First().TickerDescription; updateProfileModel.LastUpdate = DateTime.Now; updateProfileModelList.Add(updateProfileModel); } else { exceptionTickers += currentTicker + ", "; } } if (exceptionTickers.Length > 0) { exceptionTickers.Trim(); exceptionTickers = exceptionTickers.Remove(exceptionTickers.LastIndexOf(','), 1); } vm.BatchProfilesList = updateProfileModelList; vm.ExceptionTickerSymbols = (exceptionTickers.Length > 0 ? exceptionTickers : ""); vm.UpdateHasErrors = false; } catch (Exception) { vm.UpdateHasErrors = true; } return(vm); }