/// <summary>
/// Generate ConsensusData Column Headers
/// </summary>
/// <param name="workSheet">Current Excel Worksheet</param>
/// <param name="firstYear"></param>
/// <param name="lastYear"></param>
/// <returns>The Worksheet with Column Headers</returns>
private static Worksheet GenerateConsensusColumnHeaders(Worksheet workSheet, int firstYear, int lastYear)
{
try
{
var row = 1;
workSheet.Cells[row, "A"] = "Data Id";
workSheet.Cells[row, "B"] = "Data Description";
int numberOfYears = lastYear - firstYear;
for (int i = 1; i <= numberOfYears * 5 + 1; i = i + 5)
{
workSheet.Cells[row, i + 2] = firstYear + " Q1";
workSheet.Cells[row, i + 3] = firstYear + " Q2";
workSheet.Cells[row, i + 4] = firstYear + " Q3";
workSheet.Cells[row, i + 5] = firstYear + " Q4";
workSheet.Cells[row, i + 6] = firstYear + " A";
firstYear++;
}
return(workSheet);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Generate Address for the File
/// </summary>
/// <returns></returns>
private static string GetFileName()
{
try
{
string fileName = Path.GetTempPath() + Guid.NewGuid() + "_Model.xlsx";
return(fileName);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to Pivot the Unpivoted Data
/// </summary>
/// <param name="worksheet">Current Excel Worksheet</param>
/// <param name="financialData">list of type FinancialStatementData</param>
/// <returns>The Worksheet with Pivoted Data</returns>
private static Excel.Worksheet DisplayReutersData(Excel.Worksheet worksheet, List <FinancialStatementData> financialData)
{
try
{
List <string> dataDescriptors = financialData.Select(a => a.Description).Distinct().ToList();
var row = 2;
var maxRowCount = dataDescriptors.Count + 2;
List <int> financialPeriodYears = financialData.Select(a => a.PeriodYear).OrderBy(a => a).Distinct().ToList();
int firstYear = financialData.Select(a => a.PeriodYear).OrderBy(a => a).FirstOrDefault();
int lastYear = financialData.Select(a => a.PeriodYear).OrderByDescending(a => a).FirstOrDefault();
int numberOfYears = lastYear - firstYear;
while (row < maxRowCount)
{
foreach (string item in dataDescriptors)
{
firstYear = financialData.Select(a => a.PeriodYear).OrderBy(a => a).FirstOrDefault();
worksheet.Cells[row, 1] = financialData.Where(a => a.Description == item).Select(a => a.DataId).FirstOrDefault();
worksheet.Cells[row, 2] = financialData.Where(a => a.Description == item).Select(a => a.Description).FirstOrDefault();
string aa = financialData.Where(a => a.Description == item).Select(a => a.DataId).FirstOrDefault().ToString();
for (int i = 1; i <= numberOfYears * 5 + 1; i = i + 5)
{
worksheet.Cells[row, i + 2] = financialData.Where(a => a.PeriodYear == (firstYear) && a.Description == item && a.PeriodType.Trim() == "Q1").
Select(a => a.Amount).FirstOrDefault();
worksheet.Cells[row, i + 3] = financialData.Where(a => a.PeriodYear == (firstYear) && a.Description == item && a.PeriodType.Trim() == "Q2")
.Select(a => a.Amount).FirstOrDefault();
worksheet.Cells[row, i + 4] = financialData.Where(a => a.PeriodYear == (firstYear) && a.Description == item && a.PeriodType.Trim() == "Q3")
.Select(a => a.Amount).FirstOrDefault();
worksheet.Cells[row, i + 5] = financialData.Where(a => a.PeriodYear == (firstYear) && a.Description == item && a.PeriodType.Trim() == "Q4")
.Select(a => a.Amount).FirstOrDefault();
worksheet.Cells[row, i + 6] = financialData.Where(a => a.PeriodYear == (firstYear) && a.Description == item && a.PeriodType.Trim() == "A")
.Select(a => a.Amount).FirstOrDefault();
firstYear++;
}
row++;
}
}
return(worksheet);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to Pivot the Unpivoted Data
/// </summary>
/// <param name="worksheet">Current Excel Worksheet</param>
/// <param name="financialData">List of type ModelConsensusEstimatesData</param>
/// <returns>The Worksheet with Pivoted Data</returns>
private static Worksheet DisplayConsensusData(Worksheet worksheet, List <ModelConsensusEstimatesData> consensusData)
{
try
{
List <string> dataDescriptors = consensusData.Select(a => a.ESTIMATE_DESC).Distinct().ToList();
var row = 2;
var maxRowCount = dataDescriptors.Count + 2;
List <int> financialPeriodYears = consensusData.Select(a => a.PERIOD_YEAR).OrderBy(a => a).Distinct().ToList();
int firstYear = consensusData.Select(a => a.PERIOD_YEAR).OrderBy(a => a).FirstOrDefault();
int lastYear = consensusData.Select(a => a.PERIOD_YEAR).OrderByDescending(a => a).FirstOrDefault();
int numberOfYears = lastYear - firstYear;
while (row < maxRowCount)
{
foreach (string item in dataDescriptors)
{
firstYear = consensusData.Select(a => a.PERIOD_YEAR).OrderBy(a => a).FirstOrDefault();
worksheet.Cells[row, 1] = consensusData.Where(a => a.ESTIMATE_DESC == item).Select(a => a.ESTIMATE_ID).FirstOrDefault();
worksheet.Cells[row, 2] = consensusData.Where(a => a.ESTIMATE_DESC == item).Select(a => a.ESTIMATE_DESC).FirstOrDefault();
string aa = consensusData.Where(a => a.ESTIMATE_DESC == item).Select(a => a.ESTIMATE_ID).FirstOrDefault().ToString();
for (int i = 1; i <= numberOfYears * 5 + 1; i = i + 5)
{
worksheet.Cells[row, i + 2] = consensusData.Where(a => a.PERIOD_YEAR == (firstYear) && a.ESTIMATE_DESC == item && a.PERIOD_TYPE.Trim() == "Q1").
Select(a => a.AMOUNT).FirstOrDefault();
worksheet.Cells[row, i + 3] = consensusData.Where(a => a.PERIOD_YEAR == (firstYear) && a.ESTIMATE_DESC == item && a.PERIOD_TYPE.Trim() == "Q2")
.Select(a => a.AMOUNT).FirstOrDefault();
worksheet.Cells[row, i + 4] = consensusData.Where(a => a.PERIOD_YEAR == (firstYear) && a.ESTIMATE_DESC == item && a.PERIOD_TYPE.Trim() == "Q3")
.Select(a => a.AMOUNT).FirstOrDefault();
worksheet.Cells[row, i + 5] = consensusData.Where(a => a.PERIOD_YEAR == (firstYear) && a.ESTIMATE_DESC == item && a.PERIOD_TYPE.Trim() == "Q4")
.Select(a => a.AMOUNT).FirstOrDefault();
worksheet.Cells[row, i + 6] = consensusData.Where(a => a.PERIOD_YEAR == (firstYear) && a.ESTIMATE_DESC == item && a.PERIOD_TYPE.Trim() == "A")
.Select(a => a.AMOUNT).FirstOrDefault();
firstYear++;
}
row++;
}
}
return(worksheet);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to Check Availability of WCF O Data Service
/// </summary>
/// <returns>returns True if Service is Up, else False</returns>
public static bool ServiceAvailability()
{
bool isServiceUp = true;
try
{
MetadataExchangeClient client = new MetadataExchangeClient(new Uri(ConfigurationManager.AppSettings["DimensionWebService"]), MetadataExchangeClientMode.HttpGet);
MetadataSet metaData = client.GetMetadata();
return(isServiceUp);
}
catch (Exception ex)
{
isServiceUp = false;
ExceptionTrace.LogException(ex);
return(isServiceUp);
}
}
/// <summary>
/// Generate Excel
/// </summary>
/// <param name="financialData"></param>
public static byte[] GenerateExcel(List <FinancialStatementData> financialData, List <ModelConsensusEstimatesData> consensusData)
{
try
{
var excelApp = new Excel.Application();
Workbook workBook = excelApp.Workbooks.Add(Type.Missing);
int firstYear = financialData.Select(a => a.PeriodYear).OrderBy(a => a).FirstOrDefault();
int lastYear = financialData.Select(a => a.PeriodYear).OrderByDescending(a => a).FirstOrDefault();
int numberOfYears = lastYear - firstYear;
Excel.Worksheet workSheetReuters;
Excel.Worksheet workSheetConsensus;
int abv = workBook.Sheets.Count;
workSheetReuters = (Excel.Worksheet)workBook.Sheets[1];
workSheetReuters = GenerateReutersColumnHeaders(workSheetReuters, firstYear, lastYear);
workSheetReuters = DisplayReutersData(workSheetReuters, financialData);
workSheetReuters.Name = "Reuters Reported";
firstYear = consensusData.Select(a => a.PERIOD_YEAR).OrderBy(a => a).FirstOrDefault();
lastYear = consensusData.Select(a => a.PERIOD_YEAR).OrderByDescending(a => a).FirstOrDefault();
workSheetConsensus = (Excel.Worksheet)workBook.Sheets[2];
workSheetConsensus = GenerateConsensusColumnHeaders(workSheetConsensus, firstYear, lastYear);
workSheetConsensus = DisplayConsensusData(workSheetConsensus, consensusData);
workSheetConsensus.Name = "Consensus Reported";
string fileName = GetFileName();
workBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, false, false,
Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true,
Missing.Value, Missing.Value, Missing.Value);
workBook.Saved = true;
workBook.Close(false, Type.Missing, Type.Missing);
return(GetBytsForFile(fileName));
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Generate byte-Array from Excel
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static byte[] GetBytsForFile(string filePath)
{
try
{
FileStream fileStream;
byte[] fileByte;
using (fileStream = File.OpenRead(filePath))
{
fileByte = new byte[fileStream.Length];
fileStream.Read(fileByte, 0, Convert.ToInt32(fileStream.Length));
}
return(fileByte);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to calculate EndDates
/// </summary>
/// <returns>List of Dates</returns>
public static List <DateTime> CalculateEndDates()
{
try
{
List <DateTime> result = new List <DateTime>();
DateTime startDate = DateTime.Today;
int currentMonth = DateTime.Today.Month;
int currentYear = DateTime.Today.Year;
int numberOfDays = 0;
DateTime lastDay;
DateTime addDate = DateTime.Today;
//Find last dates of months in current year
for (int i = 1; i < currentMonth; i++)
{
numberOfDays = DateTime.DaysInMonth(currentYear, i);
lastDay = new DateTime(currentYear, i, numberOfDays);
result.Add(lastDay);
}
for (int i = 12; i > currentMonth - 1; i--)
{
numberOfDays = DateTime.DaysInMonth(currentYear - 1, i);
lastDay = new DateTime(currentYear - 1, i, numberOfDays);
result.Add(lastDay);
}
return(result.OrderBy(a => a.Date).ToList());
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to calculate RelativePerformanceUI Data
/// </summary>
/// <param name="dimensionDailyPerfData">Collection of type GF_PERF_DAILY_ATTRIBUTION retrieved from Dimension</param>
/// <returns>List of RelativePerformanceUIData</returns>
public static List <RelativePerformanceUIData> CalculateRelativePerformanceUIData(List <GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION> dimensionDailyPerfData,
GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION dimensionBenchmarkReturnData)
{
try
{
if (dimensionDailyPerfData == null && dimensionBenchmarkReturnData == null)
{
return(new List <RelativePerformanceUIData>());
}
List <RelativePerformanceUIData> result = new List <RelativePerformanceUIData>();
RelativePerformanceUIData data = new RelativePerformanceUIData();
if (dimensionDailyPerfData != null)
{
if (dimensionDailyPerfData.Count != 0)
{
foreach (GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION item in dimensionDailyPerfData)
{
data = new RelativePerformanceUIData();
data.EffectiveDate = Convert.ToDateTime(item.TO_DATE);
if (item.NODE_NAME.ToUpper().Trim() == "SECURITY ID")
{
data.EntityType = item.NODE_NAME;
data.EntityName = item.AGG_LVL_1_LONG_NAME;
data.QTDReturn = Convert.ToDecimal(item.ADJ_RTN_POR_RC_TWR_QTD) * 100;
data.MTDReturn = Convert.ToDecimal(item.ADJ_RTN_POR_RC_TWR_MTD) * 100;
data.YTDReturn = Convert.ToDecimal(item.ADJ_RTN_POR_RC_TWR_YTD) * 100;
data.OneYearReturn = Convert.ToDecimal(item.ADJ_RTN_POR_RC_TWR_1Y) * 100;
data.SortId = 1;
}
else
{
data.EntityType = item.NODE_NAME;
data.EntityName = item.AGG_LVL_1_LONG_NAME;
data.MTDReturn = Convert.ToDecimal(item.BM1_RC_TWR_MTD) * 100;
data.QTDReturn = Convert.ToDecimal(item.BM1_RC_TWR_QTD) * 100;
data.YTDReturn = Convert.ToDecimal(item.BM1_RC_TWR_YTD) * 100;
data.OneYearReturn = Convert.ToDecimal(item.BM1_RC_TWR_1Y) * 100;
if (item.NODE_NAME.ToUpper().Trim() == "COUNTRY")
{
data.SortId = 3;
}
else
{
data.SortId = 4;
}
}
result.Add(data);
}
}
}
if (dimensionBenchmarkReturnData != null)
{
//Adding Returns for Benchmark
if (data != null)
{
data = new RelativePerformanceUIData();
data.EntityType = "BENCHMARK";
data.EntityName = Convert.ToString(dimensionBenchmarkReturnData.BMNAME);
data.MTDReturn = Convert.ToDecimal(dimensionBenchmarkReturnData.BM1_TOP_RC_TWR_MTD) * 100;
data.QTDReturn = Convert.ToDecimal(dimensionBenchmarkReturnData.BM1_TOP_RC_TWR_QTD) * 100;
data.YTDReturn = Convert.ToDecimal(dimensionBenchmarkReturnData.BM1_TOP_RC_TWR_YTD) * 100;
data.OneYearReturn = Convert.ToDecimal(dimensionBenchmarkReturnData.BM1_TOP_RC_TWR_1Y) * 100;
data.SortId = 2;
result.Add(data);
}
}
//To be Removed
if (result != null)
{
RelativePerformanceUIData removeSecurity = result.Where(a => a.EntityType.ToUpper().Trim() == "SECURITY ID").FirstOrDefault();
if (removeSecurity != null)
{
result.Remove(removeSecurity);
}
}
return(result);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Pricing Calculations for Security
/// </summary>
/// <param name="dimensionSecurityPricingData">Collection of type GF_PRICING_BASEVIEW</param>
/// <param name="totalReturnCheck">Total/Gross Return</param>
/// <returns>Collection of type ChartExtensionData</returns>
public static List <ChartExtensionData> CalculateSecurityPricing(List <GreenField.DAL.GF_PRICING_BASEVIEW> dimensionSecurityPricingData, bool totalReturnCheck = false)
{
try
{
List <ChartExtensionData> result = new List <ChartExtensionData>();
if (dimensionSecurityPricingData == null)
{
throw new InvalidOperationException();
}
decimal objAdjustedDollarPrice = 0;
decimal objPreviousDailySpotFx = 0;
decimal objPriceReturn = 0;
decimal objReturn = 0;
if (dimensionSecurityPricingData.Count != 0)
{
List <GreenField.DAL.GF_PRICING_BASEVIEW> dimensionPricingData = new List <GreenField.DAL.GF_PRICING_BASEVIEW>(dimensionSecurityPricingData);
foreach (GreenField.DAL.GF_PRICING_BASEVIEW item in dimensionPricingData)
{
if (item.DAILY_SPOT_FX == 0)
{
dimensionSecurityPricingData.Remove(item);
}
}
foreach (GreenField.DAL.GF_PRICING_BASEVIEW pricingItem in dimensionSecurityPricingData)
{
if (pricingItem.DAILY_SPOT_FX == 0)
{
continue;
}
ChartExtensionData data = new ChartExtensionData();
data.Ticker = pricingItem.TICKER;
data.Type = "SECURITY";
data.ToDate = (DateTime)pricingItem.FROMDATE;
data.Price = Convert.ToDecimal(pricingItem.DAILY_CLOSING_PRICE);
data.SortId = 1;
if (pricingItem.FROMDATE == dimensionSecurityPricingData[0].FROMDATE)
{
data.AdjustedDollarPrice = (Convert.ToDecimal(pricingItem.DAILY_CLOSING_PRICE) / Convert.ToDecimal(pricingItem.DAILY_SPOT_FX));
}
else
{
data.AdjustedDollarPrice = objAdjustedDollarPrice / ((1 + (objReturn / 100)) * (Convert.ToDecimal(pricingItem.DAILY_SPOT_FX) / objPreviousDailySpotFx));
}
objAdjustedDollarPrice = data.AdjustedDollarPrice;
objPreviousDailySpotFx = Convert.ToDecimal(pricingItem.DAILY_SPOT_FX);
objReturn = ((totalReturnCheck) ? (Convert.ToDecimal(pricingItem.DAILY_GROSS_RETURN)) : (Convert.ToDecimal(pricingItem.DAILY_PRICE_RETURN)));
result.Add(data);
}
if ((result).ToList().Count() > 0)
{
result.OrderBy(r => r.ToDate).FirstOrDefault().PriceReturn = 100;
}
foreach (ChartExtensionData objChartExtensionData in result.OrderBy(r => r.ToDate).ToList())
{
if (objChartExtensionData.ToDate == result.OrderBy(r => r.ToDate).First().ToDate)
{
objAdjustedDollarPrice = objChartExtensionData.AdjustedDollarPrice;
objPriceReturn = objChartExtensionData.PriceReturn;
}
else
{
objChartExtensionData.PriceReturn = (objChartExtensionData.AdjustedDollarPrice / objAdjustedDollarPrice) * objPriceReturn;
objPriceReturn = objChartExtensionData.PriceReturn;
objAdjustedDollarPrice = objChartExtensionData.AdjustedDollarPrice;
}
}
foreach (ChartExtensionData item in result)
{
item.PriceReturn = item.PriceReturn - 100;
}
}
return(result);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Performs calculations for Daily Transactions
/// </summary>
/// <param name="dimensionTransactionData">collection of Data of type GF_TRANSACTION returned from Dimension</param>
/// <param name="securityExtensionData">Collection of type ChartExtensionData, containing calculated data for the security</param>
/// <returns>Collection of ChartExtensionData</returns>
public static List <ChartExtensionData> CalculateTransactionValues(List <GreenField.DAL.GF_TRANSACTIONS> dimensionTransactionData, List <ChartExtensionData> securityExtensionData)
{
try
{
//arguement null exception
if (dimensionTransactionData == null || securityExtensionData == null)
{
return(new List <ChartExtensionData>());
}
if (dimensionTransactionData.Count == 0)
{
return(securityExtensionData);
}
if (securityExtensionData.Count == 0)
{
return(new List <ChartExtensionData>());
}
decimal sumBuyTransactions;
decimal sumSellTransactions;
decimal sumTotalTransaction;
ChartExtensionData data = new ChartExtensionData();
string securityLongName = securityExtensionData.Select(a => a.Ticker).First();
List <DateTime?> transactionDates = dimensionTransactionData.Select(a => a.TRADE_DATE).ToList();
if (transactionDates == null || transactionDates.Count == 0)
{
return(securityExtensionData);
}
transactionDates = transactionDates.Distinct().ToList();
foreach (DateTime tradeDate in transactionDates)
{
sumBuyTransactions = dimensionTransactionData.Where(a => a.TRADE_DATE == tradeDate && a.TRANSACTION_CODE.ToUpper() == "BUY").
Sum(a => Convert.ToDecimal(a.VALUE_PC));
sumSellTransactions = (-1) * dimensionTransactionData.Where(a => a.TRADE_DATE == tradeDate && a.TRANSACTION_CODE.ToUpper() == "SELL").
Sum(a => Convert.ToDecimal(a.VALUE_PC));
sumTotalTransaction = sumBuyTransactions + sumSellTransactions;
if (securityExtensionData.Where(a => a.ToDate == tradeDate) != null)
{
if (securityExtensionData.Where(a => a.ToDate == tradeDate).ToList().Count != 0)
{
securityExtensionData.Where(a => a.ToDate == tradeDate).First().AmountTraded = sumTotalTransaction;
}
else
{
data = new ChartExtensionData();
data.Ticker = securityLongName;
data.ToDate = tradeDate;
data.Type = "Transaction Only";
data.AmountTraded = sumTotalTransaction;
data.SortId = 2;
securityExtensionData.Add(data);
}
}
else
{
data = new ChartExtensionData();
data.Ticker = securityLongName;
data.ToDate = tradeDate;
data.Type = "Transaction Only";
data.AmountTraded = sumTotalTransaction;
data.SortId = 2;
securityExtensionData.Add(data);
}
}
return(securityExtensionData);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Calculations for MultiLineBenchmarkUI Grid Data
/// </summary>
/// <param name="sectorCountryReturn">Collection of GF_PERF_DAILY_ATTRIBUTION retrieved from Dimension for the selected security, portfolio
/// and the associated Index for a specified date</param>
/// <returns>Collection of BenchmarkGridReturnData</returns>
public static List <BenchmarkGridReturnData> RetrieveBenchmarkGridData(List <GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION> sectorCountryReturn, List <GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION> benchmarkReturn)
{
try
{
List <BenchmarkGridReturnData> result = new List <BenchmarkGridReturnData>();
//arguement null exception
if (benchmarkReturn == null)
{
return(result);
}
if (benchmarkReturn.Count == 0)
{
return(result);
}
#region Dates
int numberOfDays = 0;
DateTime lastDayPreviousMonth;
DateTime currentDate = DateTime.Today;
DateTime endDatePreviousYear = new DateTime(currentDate.Year - 1, 12, 31);
DateTime endDateTwoPreviousYear = new DateTime(currentDate.Year - 2, 12, 31);
DateTime endDateThreePreviousYear = new DateTime(currentDate.Year - 3, 12, 31);
if (currentDate.Month == 1)
{
lastDayPreviousMonth = new DateTime(currentDate.Year - 1, 12, 1);
}
else
{
numberOfDays = DateTime.DaysInMonth(currentDate.Year, currentDate.Month - 1);
lastDayPreviousMonth = new DateTime(currentDate.Year, currentDate.Month - 1, numberOfDays);
}
#endregion
BenchmarkGridReturnData data = new BenchmarkGridReturnData();
data.Name = Convert.ToString(sectorCountryReturn.Select(a => a.BMNAME).FirstOrDefault()) + " " +
Convert.ToString(sectorCountryReturn.Select(a => a.AGG_LVL_1_LONG_NAME).FirstOrDefault());
data.Type = Convert.ToString(sectorCountryReturn.Select(a => a.NODE_NAME).FirstOrDefault());
data.MTD = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_RC_TWR_MTD).FirstOrDefault()));
data.QTD = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_RC_TWR_QTD).FirstOrDefault()));
data.YTD = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_RC_TWR_YTD).FirstOrDefault()));
data.PreviousYearReturn = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == endDatePreviousYear).Select(a => a.BM1_RC_TWR_YTD).FirstOrDefault()));
data.TwoPreviousYearReturn = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == endDateTwoPreviousYear).Select(a => a.BM1_RC_TWR_YTD).FirstOrDefault()));
data.ThreePreviousYearReturn = Convert.ToDecimal((sectorCountryReturn.
Where(a => a.TO_DATE == endDateThreePreviousYear).Select(a => a.BM1_RC_TWR_YTD).FirstOrDefault()));
result.Add(data);
data = new BenchmarkGridReturnData();
data.Name = Convert.ToString(benchmarkReturn.Select(a => a.BMNAME).FirstOrDefault());
data.Type = "BENCHMARK";
data.MTD = Convert.ToDecimal((benchmarkReturn.Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_TOP_RC_TWR_MTD).FirstOrDefault()));
data.QTD = Convert.ToDecimal((benchmarkReturn.
Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_TOP_RC_TWR_QTD).FirstOrDefault()));
data.YTD = Convert.ToDecimal((benchmarkReturn.
Where(a => a.TO_DATE == lastDayPreviousMonth).Select(a => a.BM1_TOP_RC_TWR_YTD).FirstOrDefault()));
data.PreviousYearReturn = Convert.ToDecimal((benchmarkReturn.
Where(a => a.TO_DATE == endDatePreviousYear).Select(a => a.BM1_TOP_RC_TWR_YTD).FirstOrDefault()));
data.TwoPreviousYearReturn = Convert.ToDecimal((benchmarkReturn.
Where(a => a.TO_DATE == endDateTwoPreviousYear).Select(a => a.BM1_TOP_RC_TWR_YTD).FirstOrDefault()));
data.ThreePreviousYearReturn = Convert.ToDecimal((benchmarkReturn.
Where(a => a.TO_DATE == endDateThreePreviousYear).Select(a => a.BM1_TOP_RC_TWR_YTD).FirstOrDefault()));
result.Add(data);
return(result);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Calculations for Multi-LineBenchmarkUI chart
/// </summary>
/// <param name="dimensionMonthlyPerfData">Collection of GF_PERF_MOTHLY_ATTRIBUTION retrieved from Dimension for the selected security & portfolio for a specified date </param>
/// <returns>List of type BenchmarkChartReturnData</returns>
public static List <BenchmarkChartReturnData> RetrieveBenchmarkChartData(List <GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION> countrySectorReturns, List <GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION> benchmarkReturns)
{
try
{
//arguement null exception
if (benchmarkReturns == null)
{
throw new InvalidOperationException();
}
if (benchmarkReturns.Count == 0)
{
return(new List <BenchmarkChartReturnData>());
}
List <BenchmarkChartReturnData> result = new List <BenchmarkChartReturnData>();
BenchmarkChartReturnData data = new BenchmarkChartReturnData();
if (countrySectorReturns != null)
{
if (countrySectorReturns.Count != 0)
{
foreach (GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION item in countrySectorReturns)
{
data = new BenchmarkChartReturnData();
data.Name = (item.BMNAME + " " + Convert.ToString(item.AGG_LVL_1_LONG_NAME));
data.Type = (item.NODE_NAME.ToUpper().Trim() == "COUNTRY") ? "COUNTRY INDEX" : "SECTOR";
data.FromDate = (DateTime)item.TO_DATE;
data.OneD = Convert.ToDecimal(item.BM1_RC_TWR_1D);
data.WTD = Convert.ToDecimal(item.BM1_RC_TWR_1W);
data.MTD = Convert.ToDecimal(item.BM1_RC_TWR_MTD);
data.QTD = Convert.ToDecimal(item.BM1_RC_TWR_QTD);
data.YTD = Convert.ToDecimal(item.BM1_RC_TWR_YTD);
data.OneY = Convert.ToDecimal(item.BM1_RC_TWR_1Y);
result.Add(data);
}
}
}
if (benchmarkReturns != null)
{
if (benchmarkReturns.Count != 0)
{
foreach (GreenField.DAL.GF_PERF_DAILY_ATTRIBUTION item in benchmarkReturns)
{
data = new BenchmarkChartReturnData();
data.Name = Convert.ToString(item.BMNAME);
data.Type = "BENCHMARK";
data.FromDate = Convert.ToDateTime(item.TO_DATE);
data.OneD = Convert.ToDecimal(item.BM1_TOP_RC_TWR_1D);
data.WTD = Convert.ToDecimal(item.BM1_TOP_RC_TWR_1W);
data.MTD = Convert.ToDecimal(item.BM1_TOP_RC_TWR_MTD);
data.QTD = Convert.ToDecimal(item.BM1_TOP_RC_TWR_QTD);
data.YTD = Convert.ToDecimal(item.BM1_TOP_RC_TWR_YTD);
data.OneY = Convert.ToDecimal(item.BM1_TOP_RC_TWR_1Y);
result.Add(data);
}
}
}
return(result.OrderBy(a => a.Type).ToList());
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to calculate Dates on which the chart should be plotted
/// </summary>
/// <param name="objEndDates">The dates for which data is present</param>
/// <param name="startDate">Start Date for the Chart</param>
/// <param name="endDate">End Date for the Chart</param>
/// <param name="FrequencyInterval">Selected Frequency Interval</param>
/// <returns>List of DateTime</returns>
public static List <DateTime> RetrieveDatesAccordingToFrequency(List <DateTime> objEndDates, DateTime startDate, DateTime endDate, string FrequencyInterval)
{
try
{
//List<PricingReferenceData> resultFrequency = new List<PricingReferenceData>();
List <DateTime> EndDates = new List <DateTime>();
DateTime chartStartDate = startDate;
DateTime chartEndDate = endDate;
TimeSpan timeSpan = chartEndDate - chartStartDate;
switch (FrequencyInterval)
{
case ("Weekly"):
{
#region CalculateWeeksBetweenDates
int totalWeeks = timeSpan.Days / 7;
#endregion
#region calculating LastDayOfAllWeeks
DateTime endDay = (chartStartDate.AddDays(5 - (int)chartStartDate.DayOfWeek));
GetEndDatesForEachWeek(endDay, totalWeeks, ref EndDates);
#endregion
break;
}
case ("Monthly"):
{
#region CalculateMonthsBetweenDates
int totalMonths = ((chartEndDate.Year - chartStartDate.Year) * 12) + chartEndDate.Month - chartStartDate.Month;
int totalYear = (chartEndDate.Year - chartStartDate.Year);
#endregion
#region calculating LastDayOfAllMonths
int month = chartStartDate.Month;
int year = chartStartDate.Year;
int monthsLeftInCurrentYear = 12 - month;
for (int i = 0; i <= monthsLeftInCurrentYear; i++)
{
GetEndDatesForEachMonth(ref year, ref month, ref EndDates);
}
for (int i = 0; i < totalYear - 1; i++)
{
month = 1;
year++;
while (month <= 12)
{
GetEndDatesForEachMonth(ref year, ref month, ref EndDates);
}
}
int totalMonthsLeft = totalMonths - monthsLeftInCurrentYear - 12 * (totalYear - 1);
if (totalMonthsLeft > 0)
{
for (int i = 0; i < 1; i++)
{
year++;
month = 1;
while (month <= totalMonthsLeft)
{
GetEndDatesForEachMonth(ref year, ref month, ref EndDates);
}
}
}
#endregion
break;
}
case ("Quarterly"):
{
int startDateQuarter = GetQuarter(startDate.Month);
DateTime lastDate = startDate;
#region CalculateQuartersBetweenDates
int totalMonths = ((chartEndDate.Year - chartStartDate.Year) * 12) + chartEndDate.Month - chartStartDate.Month;
#endregion
#region CalculatingQuarters
switch (startDateQuarter)
{
case (1):
{
lastDate = new DateTime(chartStartDate.Year, 3, 31);
GetEndDatesForEachQuarter(lastDate, chartEndDate, ref EndDates);
break;
}
case (2):
{
lastDate = new DateTime(chartStartDate.Year, 6, 30);
GetEndDatesForEachQuarter(lastDate, chartEndDate, ref EndDates);
break;
}
case (3):
{
lastDate = new DateTime(chartStartDate.Year, 9, 30);
GetEndDatesForEachQuarter(lastDate, chartEndDate, ref EndDates);
break;
}
case (4):
{
lastDate = new DateTime(chartStartDate.Year, 12, 31);
GetEndDatesForEachQuarter(lastDate, chartEndDate, ref EndDates);
break;
}
}
#endregion
break;
}
case ("Half-Yearly"):
{
int startDateSemiAnnually = GetHalfYearly(startDate.Month);
DateTime lastDate = startDate;
switch (startDateSemiAnnually)
{
case (1):
{
lastDate = new DateTime(chartStartDate.Year, 6, 30);
GetEndDatesForEachHalfYear(lastDate, chartEndDate, ref EndDates);
break;
}
case (2):
{
lastDate = new DateTime(chartStartDate.Year, 12, 31);
GetEndDatesForEachHalfYear(lastDate, chartEndDate, ref EndDates);
break;
}
}
break;
}
case ("Yearly"):
{
int totalYearBetweenDates = chartEndDate.Year - chartStartDate.Year;
DateTime lastDate = new DateTime(chartStartDate.Year, 12, 31);
GetEndDatesForEachYear(lastDate, chartEndDate, ref EndDates);
break;
}
default:
{
return(objEndDates);
}
}
return(EndDates);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Static Method calculating asset allocations
/// </summary>
/// <param name="dimensionPortfolioHoldingsData">Collection GF_PORTFOLIO_HOLDINGS retrieved from Dimension</param>
/// <param name="portfolioSelectionData">Data of Currently selected Portfolio</param>
/// <returns>Collection of Asset Allocation Data</returns>
public static List <AssetAllocationData> CalculateAssetAllocationValues(List <GreenField.DAL.GF_PORTFOLIO_HOLDINGS> dimensionPortfolioHoldingsData, List <GreenField.DAL.GF_BENCHMARK_HOLDINGS> dimensionBenchmarkHoldingsData, PortfolioSelectionData portfolioSelectionData)
{
try
{
decimal?modelWeight = 0;
decimal?benchmarkWeight = 0;
decimal?activePosition = 0;
decimal?modelWeightCash = 0;
decimal?portfolioWeightCash = 0;
decimal?benchmarkWeightCash = 0;
decimal?portfolioWeight = 0;
decimal?sumDirtyValuePC = 0;
decimal?sumModelWeight = 0;
decimal?sumBenchmarkWeight = 0;
List <AssetAllocationData> result = new List <AssetAllocationData>();
if ((dimensionPortfolioHoldingsData == null) || (portfolioSelectionData == null))
{
throw new ArgumentNullException();
}
List <string> countryNames = dimensionPortfolioHoldingsData.Select(a => a.COUNTRYNAME).Distinct().ToList();
if (countryNames.Count == 0)
{
throw new InvalidOperationException();
}
foreach (string item in countryNames)
{
sumDirtyValuePC = dimensionPortfolioHoldingsData.Sum(a => Convert.ToDecimal(a.DIRTY_VALUE_PC));
sumBenchmarkWeight = dimensionBenchmarkHoldingsData.Sum(a => Convert.ToDecimal(a.BENCHMARK_WEIGHT));
//if sum of DirtyValuePC or ModelWeight is zero then return empty set
if ((sumDirtyValuePC == 0) || (sumBenchmarkWeight == 0) || (sumModelWeight == 0))
{
return(result);
}
modelWeight = dimensionPortfolioHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() != "CASH")).
Sum(a => Convert.ToDecimal(a.ASH_EMM_MODEL_WEIGHT)) / sumModelWeight;
portfolioWeight = dimensionPortfolioHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() != "CASH")).
Sum(a => Convert.ToDecimal(a.DIRTY_VALUE_PC)) / sumDirtyValuePC;
modelWeightCash = modelWeightCash + dimensionPortfolioHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() == "CASH")).
Sum(a => Convert.ToDecimal(a.ASH_EMM_MODEL_WEIGHT)) / sumModelWeight;
portfolioWeightCash = portfolioWeightCash + dimensionPortfolioHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() == "CASH")).
Sum(a => Convert.ToDecimal(a.DIRTY_VALUE_PC)) / sumDirtyValuePC;
benchmarkWeight = dimensionBenchmarkHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() != "CASH")).
Sum(a => Convert.ToDecimal(a.BENCHMARK_WEIGHT));
benchmarkWeightCash = benchmarkWeightCash + dimensionBenchmarkHoldingsData.
Where(a => (a.COUNTRYNAME == item) && (a.SECURITYTHEMECODE.ToUpper().Trim() == "CASH")).
Sum(a => Convert.ToDecimal(a.BENCHMARK_WEIGHT));
activePosition = modelWeight - benchmarkWeight;
AssetAllocationData data = new AssetAllocationData();
data.BenchmarkWeight = benchmarkWeight;
data.Country = item;
data.ModelWeight = modelWeight;
data.PortfolioWeight = portfolioWeight;
data.PortfolioId = portfolioSelectionData.PortfolioId;
data.ActivePosition = activePosition;
result.Add(data);
}
if (dimensionPortfolioHoldingsData.Any(a => a.SECURITYTHEMECODE.ToUpper().Trim() == "CASH"))
{
AssetAllocationData dataCash = new AssetAllocationData();
dataCash.BenchmarkWeight = benchmarkWeight;
dataCash.Country = "CASH";
dataCash.ModelWeight = modelWeightCash;
dataCash.PortfolioWeight = portfolioWeightCash;
dataCash.PortfolioId = portfolioSelectionData.PortfolioId;
dataCash.ActivePosition = modelWeightCash - benchmarkWeightCash;
result.Add(dataCash);
}
return(result.OrderBy(a => a.Country).ToList());
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}
/// <summary>
/// Method to fetch the Values of EPS/EPSREP/EBG
/// </summary>
/// <param name="medianData">Collection of type ConsensusEstimateMedian returned from DB</param>
/// <param name="periodType">Selected PeriodType</param>
/// <returns>Collection of type ConsensusEstimateMedian</returns>
public static List <ConsensusEstimateMedian> CalculateEPSValues(List <ConsensusEstimateMedian> medianData, FinancialStatementPeriodType periodType = FinancialStatementPeriodType.ANNUAL)
{
try
{
List <ConsensusEstimateMedian> localMedianData = new List <ConsensusEstimateMedian>(medianData);
ConsensusEstimateMedian addData;
List <int> periodYear = medianData.Select(a => a.PeriodYear).ToList();
List <string> periodTypeList = medianData.Select(a => a.PeriodType).ToList();
List <string> quartersList = new List <string>()
{
"Q1", "Q2", "Q3", "Q4"
};
List <int> EstimateID = new List <int>()
{
8, 9, 5
};
if (periodType.Equals(FinancialStatementPeriodType.ANNUAL))
{
foreach (int item in periodYear)
{
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 8 && a.PeriodType == "A").FirstOrDefault() == null)
{
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 9 && a.PeriodType == "A").FirstOrDefault() != null)
{
addData = medianData.Where(a => a.PeriodYear == item && a.EstimateId == 9 && a.PeriodType == "A").FirstOrDefault();
addData.EstimateId = 8;
addData.Description = "Net Income (Pre Exceptional)";
localMedianData.Add(addData);
}
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 5 && a.PeriodType == "A").FirstOrDefault() != null)
{
addData = medianData.Where(a => a.PeriodYear == item && a.EstimateId == 5 && a.PeriodType == "A").FirstOrDefault();
addData.EstimateId = 8;
addData.Description = "Net Income (Pre Exceptional)";
localMedianData.Add(addData);
}
}
}
}
else if (periodType.Equals(FinancialStatementPeriodType.QUARTERLY))
{
foreach (int item in periodYear)
{
foreach (string quarterValue in quartersList)
{
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 11 && a.PeriodType == quarterValue).FirstOrDefault() == null)
{
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 13 && a.PeriodType == quarterValue).FirstOrDefault() != null)
{
addData = medianData.Where(a => a.PeriodYear == item && a.EstimateId == 13 && a.PeriodType == quarterValue).FirstOrDefault();
addData.EstimateId = 11;
addData.Description = "Net Income (Pre Exceptional)";
localMedianData.Add(addData);
}
if (medianData.Where(a => a.PeriodYear == item && a.EstimateId == 12 && a.PeriodType == quarterValue).FirstOrDefault() != null)
{
addData = medianData.Where(a => a.PeriodYear == item && a.EstimateId == 12 && a.PeriodType == quarterValue).FirstOrDefault();
addData.EstimateId = 11;
addData.Description = "Net Income (Pre Exceptional)";
localMedianData.Add(addData);
}
}
}
}
}
List <ConsensusEstimateMedian> result = new List <ConsensusEstimateMedian>(localMedianData.Where(a => a.EstimateId != 9).ToList());
result = new List <ConsensusEstimateMedian>(result.Where(a => a.EstimateId != 5).ToList());
return(result);
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
return(null);
}
}