/// <summary>
/// dataFetchWorker DoWork EventHandler
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void dataFetchWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
InputData inputData = e.Argument as InputData;
if (inputData == null)
{
throw new Exception("Unable to parse async argument to input data type...");
}
ExcelModelRefreshOperationsClient client = new ExcelModelRefreshOperationsClient();
ExcelModelData outputData = client.RetrieveStatementData(inputData.IssuerID, inputData.Currency);
if (outputData == null)
{
throw new Exception("Unable to retrieve data from service...");
}
e.Result = outputData;
}
catch (Exception ex)
{
AppendMessage(ex.Message, true);
return;
}
}
/// <summary>
/// dataFetchWorker RunWorkerCompleted EventHandler
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void dataFetchWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
try
{
if (e.Error != null)
{
throw new Exception(e.Error.Message);
}
ExcelModelData outputData = e.Result as ExcelModelData;
if (outputData == null)
{
throw new Exception("Unable to parse async result to output data type...");
}
Globals.ThisAddIn.AppendMessage("Model data retrieval complete...");
Globals.ThisAddIn.AppendMessage("Updating workbook data...");
Boolean result = Model.Update(outputData);
if (result == false)
{
throw new Exception("");
}
Globals.ThisAddIn.AppendMessage("Refresh process complete");
}
catch (Exception ex)
{
AppendMessage(ex.Message, true);
AppendMessage("Import Failed!");
return;
}
finally
{
ModifyTaskPaneEnabled(true);
}
}
/// <summary>
/// Generate Excel
/// </summary>
/// <param name="financialData"></param>
public static Boolean Update(ExcelModelData excelModelData)
{
try
{
Globals.ThisAddIn.Application.ScreenUpdating = false;
Globals.ThisAddIn.AppendMessage("Validating model data received from service...");
List <FinancialStatementDataModels> financialData = excelModelData.ReutersData;
List <ModelConsensusEstimatesData> consensusData = excelModelData.ConsensusEstimateData;
List <DataPointsModelUploadData> modelUploadData = excelModelData.ModelUploadDataPoints;
if (financialData == null || consensusData == null || modelUploadData == null)
{
throw new Exception("Model data received from service is corrupt");
}
var excelApp = Globals.ThisAddIn.Application;
Workbook workBook = excelApp.ActiveWorkbook;
//'Reuters Reported' data fetch
Globals.ThisAddIn.AppendMessage("Updating reuters reported headers...");
List <int?> distinctPeriodYears_reuters = financialData.Where(a => a.PeriodYear != null)
.Select(a => a.PeriodYear).OrderBy(a => a).ToList().Distinct().ToList();
if (distinctPeriodYears_reuters == null || distinctPeriodYears_reuters.Count == 0)
{
throw new Exception("No reuters data available");
}
//'Consensus Data' data fetch
Globals.ThisAddIn.AppendMessage("Updating consensus headers...");
List <int> distinctPeriodYears_consensus = consensusData.Select(a => a.PERIOD_YEAR).OrderBy(a => a).ToList().Distinct().ToList();
if (distinctPeriodYears_consensus == null || distinctPeriodYears_consensus.Count == 0)
{
throw new Exception("No consensus data available");
}
//'Reuters Reported' worksheet write
int?firstYear_reuters = distinctPeriodYears_reuters[0];
int?lastYear_reuters = distinctPeriodYears_reuters[distinctPeriodYears_reuters.Count - 1];
int?numberOfYears_reuters = lastYear_reuters - firstYear_reuters;
Worksheet workSheetReuters = workBook.Worksheets[ThisAddIn.REUTERS_REPORTED_WORKSHEET_NAME];
headerColor = (workSheetReuters.Cells[1, 1] as Range).Interior.Color;
workSheetReuters.Cells.ClearContents();
workSheetReuters.Cells.ClearOutline();
workSheetReuters.Cells.ClearFormats();
workSheetReuters = UpdateReutersHeaders(workSheetReuters, Convert.ToInt32(firstYear_reuters), Convert.ToInt32(lastYear_reuters));
if (workSheetReuters == null)
{
throw new Exception("An Error occured while updating reuters reported headers");
}
Globals.ThisAddIn.AppendMessage("Updating reuters reported data...");
workSheetReuters = UpdateReutersData(workSheetReuters, financialData);
if (workSheetReuters == null)
{
throw new Exception("An Error occured while updating reuters reported data");
}
Globals.ThisAddIn.AppendMessage("Reuters reported data update complete");
//'Consensus Data' worksheet write
int?firstYear_consensus = distinctPeriodYears_consensus[0];
int?lastYear_consensus = distinctPeriodYears_consensus[distinctPeriodYears_consensus.Count - 1];
int?numberOfYears_consensus = lastYear_consensus - firstYear_consensus;
Worksheet workSheetConsensus = workBook.Worksheets[ThisAddIn.CONSENSUS_DATA_WORKSHEET_NAME];
workSheetConsensus.Cells.ClearContents();
workSheetConsensus.Cells.ClearOutline();
workSheetConsensus.Cells.ClearFormats();
workSheetConsensus = UpdateConsensusHeaders(workSheetConsensus, Convert.ToInt32(firstYear_consensus), Convert.ToInt32(lastYear_consensus));
if (workSheetConsensus == null)
{
throw new Exception("An Error occured while updating consensus headers");
}
Globals.ThisAddIn.AppendMessage("Updating consensus data...");
workSheetConsensus = UpdateConsensusData(workSheetConsensus, consensusData);
if (workSheetConsensus == null)
{
throw new Exception("An Error occured while updating consensus data");
}
Globals.ThisAddIn.AppendMessage("Consensus data update complete");
//'Model Upload' worksheet
Worksheet workSheetModelUpload = workBook.Worksheets[ThisAddIn.MODEL_UPLOAD_WORKSHEET_NAME];
Globals.ThisAddIn.AppendMessage("Updating model upload data...");
workSheetModelUpload = UpdateModelUploadData(workSheetModelUpload, modelUploadData);
if (workSheetModelUpload == null)
{
throw new Exception("An Error occured while updating model upload data");
}
Globals.ThisAddIn.AppendMessage("Model upload data update complete");
}
catch (Exception ex)
{
Globals.ThisAddIn.AppendMessage(ex.Message + "|" + ex.StackTrace, true);
return(false);
}
finally
{
Globals.ThisAddIn.Application.ScreenUpdating = true;
}
return(true);
}
public ExcelModelData RetrieveStatementData(string issuerId, String currency)
{
List <ModelConsensusEstimatesData> resultConsensus = new List <ModelConsensusEstimatesData>();
List <FinancialStatementDataModels> resultReuters = new List <FinancialStatementDataModels>();
List <FinancialStatementData> resultStatement = new List <FinancialStatementData>();
List <string> commodities = new List <string>();
ExcelModelData modelData = new ExcelModelData();
List <DataPointsModelUploadData> dataPointsExcelUpload = new List <DataPointsModelUploadData>();
ModelReferenceDataPoints dataPointsModelReference = new ModelReferenceDataPoints();
string currencyReuters = "";
string currencyConsensus = string.Empty;
try
{
ExternalResearchEntities entity = new ExternalResearchEntities();
if (issuerId == null)
{
throw new Exception("Issuer Id is not Valid");
}
GreenField.DAL.GF_SECURITY_BASEVIEW securityDetails = DimensionEntity.GF_SECURITY_BASEVIEW
.Where(record => record.ISSUER_ID == issuerId).FirstOrDefault();
if (securityDetails == null)
{
throw new Exception("Issuer Id is not Valid");
}
//External_Country_Master countryDetails = entity.External_Country_Master
//.Where(record => record.COUNTRY_CODE == securityDetails.ISO_COUNTRY_CODE &&
// record.COUNTRY_NAME == securityDetails.ASEC_SEC_COUNTRY_NAME)
//.FirstOrDefault();
string issuerID = issuerId;
//string currency = countryDetails.CURRENCY_CODE;
if (currency != null)
{
resultReuters = RetrieveFinancialData(issuerID, currency);
resultConsensus = RetrieveModelConsensusData(issuerID, currency);
currencyReuters = currency;
currencyConsensus = currency;
}
if (resultReuters != null)
{
resultReuters = resultReuters.Where(a => a.PeriodYear != 2300).ToList();
}
if (resultReuters == null || resultReuters.Count == 0)
{
//if (currency != "USD")
//{
// resultReuters = RetrieveFinancialData(issuerID, "USD");
// currencyReuters = "USD";
//}
//else
//{
// resultReuters = new List<FinancialStatementDataModels>();
//}
throw new Exception("Data does not exist for the selected currency '"
+ currency + "' and issuer id '" + issuerId + "'.");
}
resultReuters = resultReuters.Where(a => a.PeriodYear != 2300).ToList();
if (resultConsensus == null || resultConsensus.Count == 0)
{
//if (currency != "USD")
//{
// resultConsensus = RetrieveModelConsensusData(issuerID, "USD");
// currencyConsensus = "USD";
//}
//else
//{
// resultConsensus = new List<ModelConsensusEstimatesData>();
//}
throw new Exception("Data does not exist for the selected currency '"
+ currency + "' and issuer id '" + issuerId + "'.");
}
if (resultReuters == null || resultReuters.Count == 0)
{
throw new Exception("No Data Returned from server");
}
dataPointsExcelUpload = RetrieveModelUploadDataPoints(issuerID);
commodities = entity.RetrieveCommodityForecasts().ToList();
ExcelModelData excelModelData = new ExcelModelData();
excelModelData.ConsensusEstimateData = new List <ModelConsensusEstimatesData>();
excelModelData.ModelReferenceData = dataPointsModelReference;
excelModelData.ModelUploadDataPoints = dataPointsExcelUpload;
excelModelData.Currencies = entity.RetrieveDistinctFXRates().ToList();
excelModelData.Commodities = commodities;
excelModelData.ReutersData = resultReuters;
excelModelData.CurrencyReuters = currencyReuters;
excelModelData.ConsensusEstimateData = resultConsensus;
return(excelModelData);
}
catch (Exception ex)
{
string networkFaultMessage = ServiceFaultResourceManager.GetString("NetworkFault").ToString();
throw new FaultException <ServiceFault>(new ServiceFault(networkFaultMessage), new FaultReason(ex.Message));
}
}
public byte[] RetrieveStatementData(EntitySelectionData selectedSecurity)
{
List <ModelConsensusEstimatesData> resultConsensus = new List <ModelConsensusEstimatesData>();
List <FinancialStatementDataModels> resultReuters = new List <FinancialStatementDataModels>();
List <FinancialStatementData> resultStatement = new List <FinancialStatementData>();
List <string> commodities = new List <string>();
ExcelModelData modelData = new ExcelModelData();
List <DataPointsModelUploadData> dataPointsExcelUpload = new List <DataPointsModelUploadData>();
ModelReferenceDataPoints dataPointsModelReference = new ModelReferenceDataPoints();
string currencyReuters = "";
string currencyConsensus = "";
try
{
ExternalResearchEntities entity = new ExternalResearchEntities();
if (selectedSecurity == null)
{
return(new byte[1]);
}
GreenField.DAL.GF_SECURITY_BASEVIEW securityDetails = DimensionEntity.GF_SECURITY_BASEVIEW
.Where(record => record.ASEC_SEC_SHORT_NAME == selectedSecurity.InstrumentID &&
record.ISSUE_NAME == selectedSecurity.LongName &&
record.TICKER == selectedSecurity.ShortName).FirstOrDefault();
if (securityDetails == null)
{
return(new byte[1]);
}
External_Country_Master countryDetails = entity.External_Country_Master
.Where(record => record.COUNTRY_CODE == securityDetails.ISO_COUNTRY_CODE &&
record.COUNTRY_NAME == securityDetails.ASEC_SEC_COUNTRY_NAME)
.FirstOrDefault();
string issuerID = securityDetails.ISSUER_ID;
string currency = countryDetails.CURRENCY_CODE;
if (issuerID == null)
{
return(new byte[1]);
}
if (currency != null)
{
resultReuters = RetrieveFinancialData(issuerID, currency);
resultConsensus = RetrieveModelConsensusData(issuerID, currency);
currencyReuters = currency;
currencyConsensus = currency;
}
if (resultReuters != null)
{
resultReuters = resultReuters.Where(a => a.PeriodYear != 2300).ToList();
}
if (resultReuters == null || resultReuters.Count == 0)
{
if (currency != "USD")
{
resultReuters = RetrieveFinancialData(issuerID, "USD");
currencyReuters = "USD";
}
else
{
resultReuters = new List <FinancialStatementDataModels>();
}
}
resultReuters = resultReuters.Where(a => a.PeriodYear != 2300).ToList();
if (resultConsensus == null || resultConsensus.Count == 0)
{
if (currency != "USD")
{
resultConsensus = RetrieveModelConsensusData(issuerID, "USD");
currencyConsensus = "USD";
}
else
{
resultConsensus = new List <ModelConsensusEstimatesData>();
}
}
dataPointsExcelUpload = RetrieveModelUploadDataPoints(issuerID);
dataPointsModelReference = RetrieveExcelModelReferenceData(issuerID, securityDetails);
commodities = entity.RetrieveCommodityForecasts().ToList();
ExcelModelData excelModelData = new ExcelModelData();
excelModelData.ModelReferenceData = dataPointsModelReference;
excelModelData.ModelUploadDataPoints = dataPointsExcelUpload;
excelModelData.Currencies = entity.RetrieveDistinctFXRates().ToList();
excelModelData.Commodities = commodities;
return(GenerateOpenXMLExcelModel.GenerateExcel(resultReuters, resultConsensus, currencyReuters, currencyConsensus, excelModelData));
}
catch (Exception ex)
{
ExceptionTrace.LogException(ex);
string networkFaultMessage = ServiceFaultResourceManager.GetString("NetworkFault").ToString();
throw new FaultException <ServiceFault>(new ServiceFault(networkFaultMessage), new FaultReason(ex.Message));
}
}
