/// <summary> /// takes each element within a List of InvestorPcap and inserts or updates the database /// </summary> /// <param name="pcaps">List<InvestorPcap></InvestorPcap> </param> public static void UpdateOrInsertPcaps(List <InvestorPcap> pcaps) { if (pcaps.Count() == 0) { return; } using (HqTrustData dbContext = new HqTrustData()) { foreach (InvestorPcap pcap in pcaps) { if (pcap.Id == 0) { dbContext.InvestorPcaps.Add(pcap); } else { InvestorPcap existingPcap = dbContext.InvestorPcaps.FirstOrDefault(p => p.Id == pcap.Id); if (existingPcap == null) { throw new Exception($"Das PCAP mit der Id {pcap.Id} wurde nicht gefunden"); } dbContext.Entry(existingPcap).CurrentValues.SetValues(pcap); } } try { dbContext.SaveChanges(); } catch (Exception ex) { throw new Exception($"Fehler beim Ändern der Tabelle InvestorPcaps. {ex.InnerException.Message}"); } } }
private void OnRowEditEnded() { if (Pcaps.CurrentItem == null) { return; } ExtendedPcap epcap = Pcaps.CurrentItem as ExtendedPcap; InvestorPcap pcap = InvestorCommitment.InvestorPcaps.FirstOrDefault(p => p.AsOfDate == epcap.AsOfDate); if (pcap == null) { return; } pcap.FinalPcapAmount = epcap.NavAmount; try { investorAccess.InsertOrUpdateInvestorPcap(pcap); } catch (Exception ex) { NotificationRequest.Raise(new Notification() { Title = ApplicationNames.NotificationTitle, Content = ex.Message }); } }
private void OnConfirmNewPcaps(IConfirmation obj) { if (obj.Confirmed) { isNavCalculating = true; NewPcap = 0; pcaps.Clear(); // neue Pcaps anlegen (errechnen aus letztem Pcap + cashflows) foreach (InvestorCommitment c in commitments) { InvestorPcap pcap = new InvestorPcap() { AsOfDate = selectedQuarter.Date, DateOfFinalPcap = DateTime.Now, FinalPcapAmount = 0, EstimatedPcapAmount = 0, FinalPcapAmountInInvestorCurrency = 0, InvestorCommitmentId = c.Id, InvestorHqTrustAccount = c.Investor.InvestorHqTrustAccount, InvestorReference = c.Investor.InvestorReference, CommitmentAmount = c.CommitmentAmount }; pcap.EstimatedPcapAmount = CalculatePcap(c, selectedQuarter); pcap.FinalPcapAmount = pcap.EstimatedPcapAmount; NewPcap += pcap.EstimatedPcapAmount; pcaps.Add(pcap); } isNavCalculating = false; } RaisePropertyChanged("Pcaps"); }
private void CreatePcapList() { pcaps.Clear(); isNavCalculating = true; NewPcap = 0; totalCommitments = 0; ContainsCalculatedItems = false; foreach (InvestorCommitment c in commitments) { InvestorPcap pcap = PefundAccess.GetPcapForCommitmentAndDate(c.Id, selectedQuarter); if (pcap == null) { // if there is no record --> create a new record // this record may be inserted into the database when saving changes pcap = new InvestorPcap() { AsOfDate = selectedQuarter, DateOfFinalPcap = DateTime.Now, InvestorCommitmentId = c.Id }; pcap.EstimatedPcapAmount = CalculatePcap(c, selectedQuarter); pcap.FinalPcapAmount = pcap.EstimatedPcapAmount; pcap.IsCalculated = true; ContainsCalculatedItems = true; } // Add InvestorReference and InvestorHqTrustAccount to display properties in the list pcap.InvestorReference = c.Investor.InvestorReference; pcap.InvestorHqTrustAccount = c.Investor.InvestorHqTrustAccount; pcap.CommitmentAmount = c.CommitmentAmount; pcaps.Add(pcap); NewPcap += Math.Round(pcap.FinalPcapAmount, 2); totalCommitments += c.CommitmentAmount; } //if (Pcaps != null) Pcaps.CurrentChanged -= Pcaps_CurrentChanged; Pcaps = CollectionViewSource.GetDefaultView(pcaps); //Pcaps.CurrentChanged += Pcaps_CurrentChanged; Pcaps.MoveCurrentToFirst(); RaisePropertyChanged("Pcaps"); if (Pcaps.CurrentItem == null) { return; } // calculate sum of Pcaps and compare with NewPcap // display difference if any PcapDifference = NewPcap; foreach (InvestorPcap pcap in pcaps) { PcapDifference -= Math.Round(pcap.FinalPcapAmount, 2); } PcapDifference = Math.Round(PcapDifference, 2); isNavCalculating = false; }
private async Task ImportNavListAsync() { for (int counter = 0; counter < navList.Count; counter++) { if (counter % 100 == 0) { CurrentRecordNumber = $"Es wurden {counter:n0} von {navList.Count:n0} Sätzen verarbeitet."; } ExcelNav nav = navList.ElementAt(counter); InvestorPcap pcap = investorAccess.GetPcapForInvestorByCommitmentAndDate(nav.InvestorCommitmentId, nav.NavDate); if (pcap == null) { // insert new pcap InvestorPcap newPcap = new InvestorPcap() { AsOfDate = nav.NavDate, FinalPcapAmount = nav.NavAmount, InvestorCommitmentId = nav.InvestorCommitmentId, DateOfFinalPcap = nav.NavDate, EstimatedPcapAmount = nav.NavAmount }; try { investorAccess.InsertOrUpdateInvestorPcap(newPcap); } catch (Exception ex) { ErrorMessages.Add(ex.Message); } } else { if (pcap.FinalPcapAmount == nav.NavAmount) { continue; } pcap.FinalPcapAmount = nav.NavAmount; pcap.DateOfFinalPcap = nav.NavDate; pcap.EstimatedPcapAmount = nav.NavAmount; try { investorAccess.InsertOrUpdateInvestorPcap(pcap); } catch (Exception ex) { ErrorMessages.Add(ex.Message); } } } CurrentRecordNumber = $"Es wurden alle Datensätze verarbeitet."; }
private double CalculatePcap(InvestorCommitment c, DateTime selectedQuarter) { InvestorPcap lastPcap = PefundAccess.GetLastPCap(c, selectedQuarter); if (lastPcap == null) { lastPcap = new InvestorPcap() { InvestorCommitmentId = c.Id, AsOfDate = DateTime.MinValue, FinalPcapAmount = 0 }; } return(PefundAccess.NavCalculation(lastPcap, selectedQuarter)); }
/// <summary> /// return a calculated NAV using a Investorpcap and cashflows between investorpcap and date /// </summary> /// <param name="lastPcap"></param> /// <param name="selectedQuarter"></param> /// <returns></returns> public static double NavCalculation(InvestorPcap lastPcap, DateTime selectedQuarter) { double nav = lastPcap.FinalPcapAmount; using (HqTrustData dbContext = new HqTrustData()) { List <InvestorCashFlow> cfList = dbContext.InvestorCashFlows. Where(c => c.InvestorCommitmentId == lastPcap.InvestorCommitmentId && c.EffectiveDate > lastPcap.AsOfDate && c.EffectiveDate <= selectedQuarter).ToList(); foreach (InvestorCashFlow cashflow in cfList) { if (cashflow.CashFlowType == "Capital Call") { nav -= cashflow.CashFlowAmount; } if (cashflow.CashFlowType == "Distribution") { nav -= cashflow.CashFlowAmount; } } } return(nav); }
public void InsertOrUpdateInvestorPcap(InvestorPcap pcap) { if (pcap.Id == 0) { // inser new record using (HqTrustData dbContext = new HqTrustData()) { dbContext.InvestorPcaps.Add(pcap); try { dbContext.SaveChanges(); } catch (Exception ex) { throw new Exception($"Fehler beim Eintragen eines PCap. {ex.InnerException.Message}"); } } } else { using (HqTrustData dbContext = new HqTrustData()) { InvestorPcap existingPcap = dbContext.InvestorPcaps.FirstOrDefault(p => p.Id == pcap.Id); if (existingPcap == null) { throw new Exception($"Ein NAV für das Commitment {pcap.InvestorCommitmentId} mit dem Datum {pcap.AsOfDate:d} wurde nicht gefunden."); } dbContext.Entry(existingPcap).CurrentValues.SetValues(pcap); try { dbContext.SaveChanges(); } catch (Exception ex) { throw new Exception($"Fehler beim Ändern eines PCap. {ex.InnerException.Message}"); } } } }
private void CalculateResults() { ClearFields(); foreach (InvestorCommitment commitment in commitments) { if (commitment.InvestorCashFlows.Count == 0) { continue; } try { PeFundPerformance performance = new PeFundPerformance(commitment, dateFrom, dateTo); // the individual Performance of each PeFund is added to a collection performance.InvestorCommitmentId = commitment.Id; fundPerformances.Add(performance); } catch (Exception) { continue; } foreach (InvestorCashFlow cashFlow in commitment.InvestorCashFlows) { if (cashFlow.EffectiveDate > dateTo) { continue; } if (cashFlow.EffectiveDate < dateFrom) { continue; } cfCollection.Add(new CashFlowLight() { CDate = cashFlow.EffectiveDate, CAmount = cashFlow.CashFlowAmount, CAmountInvestorCurrency = cashFlow.CashFlowAmountInInvestorCurrency } ); if (cashFlow.CashFlowType == "Capital Call") { amountCalled += cashFlow.CashFlowAmount; amountCalledInvestorCurrency += cashFlow.CashFlowAmountInInvestorCurrency; } else if (cashFlow.CashFlowType == "Distribution") { amountDistributed += cashFlow.CashFlowAmount; amountDistributedInvestorCurrency += cashFlow.CashFlowAmountInInvestorCurrency; if (cashFlow.RecallableAmount != 0) { amountDistributed -= cashFlow.RecallableAmount; amountDistributedInvestorCurrency -= Math.Round(cashFlow.RecallableAmount / cashFlow.CashFlowAmount * cashFlow.CashFlowAmountInInvestorCurrency, 2); amountCalled -= cashFlow.RecallableAmount; amountCalledInvestorCurrency -= Math.Round(cashFlow.RecallableAmount / cashFlow.CashFlowAmount * cashFlow.CashFlowAmountInInvestorCurrency, 2); } } } // use market value (of DateTo) als last distribution InvestorPcap pcap = PefundAccess.GetLastPCap(commitment, dateTo); if (pcap == null) { pcap = new InvestorPcap() { InvestorCommitmentId = commitment.Id, AsOfDate = DateTime.MinValue, FinalPcapAmount = 0 }; } pcap.FinalPcapAmount = PefundAccess.NavCalculation(pcap, dateTo); //InvestorPcap pcap = commitment.InvestorPcaps.Find(p => p.AsOfDate == dateTo); //if (pcap == null) //{ // //throw new Exception($"der NAV für Fund {commitment.PeFund.FundName} und Datum {dateTo:d} ist nicht vorhanden"); // pcap = commitment.InvestorPcaps.Find(p => p.AsOfDate > dateTo); // if (pcap == null) // { // pcap = commitment.InvestorPcaps.Find(p => p.AsOfDate < dateTo); // if (pcap == null) // { // throw new Exception($"der NAV für Fund {commitment.PeFund.FundName} und Datum {dateTo:d} ist nicht vorhanden"); // } // } //} cfCollection.Add(new CashFlowLight() { CDate = dateTo, CAmount = pcap.FinalPcapAmount, CAmountInvestorCurrency = pcap.FinalPcapAmountInInvestorCurrency } ); valuationFundCurrency += pcap.FinalPcapAmount; valuationInvestorCurrency += pcap.FinalPcapAmountInInvestorCurrency; commitmentAmount += commitment.CommitmentAmount; openCommitment = commitmentAmount + amountCalled; amountCalledInPercentOfCommitment = Math.Abs(Math.Round(amountCalled / commitmentAmount, 4)); } tvpi = Math.Round((amountDistributed + valuationFundCurrency) / amountCalled, 2); dpi = Math.Round(amountDistributed / amountCalled, 2); rvpi = Math.Round(valuationFundCurrency / amountCalled, 2); tvpi = Math.Abs(tvpi); rvpi = Math.Abs(rvpi); dpi = Math.Abs(dpi); totalValue = Math.Round(amountDistributed + valuationFundCurrency, 2); Comparer.CashFlowLightComparer cashFlowLightComparer = new Comparer.CashFlowLightComparer(); cfCollection.Sort(cashFlowLightComparer); foreach (CashFlowLight light in cfCollection) { cfDates.Add(light.CDate); cfAmounts.Add(light.CAmount); cfAmountsInvestorCurrency.Add(light.CAmountInvestorCurrency); } if (cfCollection.Count > 1) { try { irr = Financial.XIrr(cfAmounts, cfDates) * 100; irrInvestorCurrency = Financial.XIrr(cfAmountsInvestorCurrency, cfDates) * 100; } catch (Exception) { irr = 0; irrInvestorCurrency = 0; } } else { irr = 0; irrInvestorCurrency = 0; } }