/// <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)); } }