/** * Generate IDN Bulk file * Return : void * Parameter: TailandRicTemplate objAQSTQS */ private void GenerateIDNBulkFile(TailandRicTemplate objAQSTQS) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { logger.LogErrorAndRaiseException("EXCEL could not be started. Check that your office installation and project references are correct"); return; } xlApp.Visible = false; try { //create a new excel file if (!Directory.Exists(configObj.SavePath)) { DirectoryInfo dir = new DirectoryInfo(configObj.SavePath); dir.Create(); } Workbook wBook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet wSheet = (Worksheet)wBook.Worksheets[1]; wSheet.Name = "SET_EQLB_W"; ((Range)wSheet.Columns["A:Q", System.Type.Missing]).ColumnWidth = 18; xlApp.Cells.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft; if (wSheet == null) { logger.LogErrorAndRaiseException("Worksheet could not be created. Check that your office installation and project references are correct."); } ((Range)wSheet.Rows["1", System.Type.Missing]).Font.Bold = true; ((Range)wSheet.Rows["1", System.Type.Missing]).Font.Color = ColorTranslator.ToOle(Color.Red); ((Range)wSheet.Rows["1", System.Type.Missing]).Interior.Color = ColorTranslator.ToOle(Color.Yellow); wSheet.Cells[1, 1] = "SYMBOL"; wSheet.Cells[2, 1] = objAQSTQS.ricStr; wSheet.Cells[1, 2] = "DSPLY_NAME"; wSheet.Cells[2, 2] = objAQSTQS.displayNameStr; wSheet.Cells[1, 3] = "RIC"; wSheet.Cells[2, 3] = objAQSTQS.ricStr; wSheet.Cells[1, 4] = "OFFCL_CODE"; wSheet.Cells[2, 4] = objAQSTQS.officialCodeStr; wSheet.Cells[1, 5] = "EX_SYMBOL"; wSheet.Cells[2, 5] = "DUMMY-" + objAQSTQS.ricStr.Substring(0, objAQSTQS.ricStr.IndexOf(".")).ToUpper(); wSheet.Cells[1, 6] = "BCKGRNDPAG"; wSheet.Cells[2, 6] = "****"; wSheet.Cells[1, 7] = "BCAST_REF"; wSheet.Cells[2, 7] = objAQSTQS.bcastRefStr; ((Range)wSheet.Cells[2, 8]).NumberFormat = "@"; wSheet.Cells[1, 8] = "#INSTMOD_EXPIR_DATE"; wSheet.Cells[2, 8] = objAQSTQS.maturDateDT.ToString("dd/MM/yyyy"); wSheet.Cells[1, 9] = "#INSTMOD_LONGLINK1"; wSheet.Cells[2, 9] = objAQSTQS.underlyingRicStr; wSheet.Cells[1, 10] = "#INSTMOD_LONGLINK2"; wSheet.Cells[2, 10] = objAQSTQS.longlink2Str; ((Range)wSheet.Cells[2, 11]).NumberFormat = "@"; wSheet.Cells[1, 11] = "#INSTMOD_MATUR_DATE"; wSheet.Cells[2, 11] = objAQSTQS.maturDateDT.ToString("dd/MM/yyyy"); wSheet.Cells[1, 12] = "#INSTMOD_OFFC_CODE2"; wSheet.Cells[2, 12] = objAQSTQS.offc_code2Str; wSheet.Cells[1, 13] = "#INSTMOD_STRIKE_PRC"; wSheet.Cells[2, 13] = objAQSTQS.strikePrcStr; ((Range)wSheet.Cells[2, 14]).NumberFormat = "@"; wSheet.Cells[1, 14] = "#INSTMOD_WNT_RATIO"; wSheet.Cells[2, 14] = objAQSTQS.wntRatioStr.Substring(0, objAQSTQS.wntRatioStr.IndexOf(":") - 1); wSheet.Cells[1, 15] = "#INSTMOD_MNEMONIC"; wSheet.Cells[2, 15] = objAQSTQS.officialCodeStr; wSheet.Cells[1, 16] = "#INSTMOD_TDN_SYMBOL"; wSheet.Cells[2, 16] = objAQSTQS.officialCodeStr; wSheet.Cells[1, 17] = "EXL_NAME"; wSheet.Cells[2, 17] = "SET_EQLB_W"; xlApp.DisplayAlerts = false; xlApp.AlertBeforeOverwriting = false; string fullpath = configObj.SavePath + "\\" + objAQSTQS.officialCodeStr + "_" + DateTime.Now.ToString("d").Replace('/', '-') + ".xls"; wBook.SaveCopyAs(fullpath); } catch (SystemException ex) { logger.LogErrorAndRaiseException(ex.ToString()); } finally { xlApp.Quit(); KillExcelProcess(xlApp); } }
/** * Generate FM template file * Return : void * Parameter: TailandRicTemplate objAQSTQS */ private void GenerateFMTemplate(TailandRicTemplate objAQSTQS) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { logger.LogErrorAndRaiseException("EXCEL could not be started. Check that your office installation and project references are correct"); return; } xlApp.Visible = false; try { //create a new excel file if (!Directory.Exists(configObj.SavePath)) { DirectoryInfo dir = new DirectoryInfo(configObj.SavePath); dir.Create(); } Workbook wBook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet wSheet = (Worksheet)wBook.Worksheets[1]; wSheet.Name = "TailandRicInfo"; ((Range)wSheet.Columns["A", System.Type.Missing]).ColumnWidth = 30; ((Range)wSheet.Columns["B", System.Type.Missing]).ColumnWidth = 4; ((Range)wSheet.Columns["C", System.Type.Missing]).ColumnWidth = 50; xlApp.Cells.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft; if (wSheet == null) { logger.LogErrorAndRaiseException("Worksheet could not be created. Check that your office installation and project references are correct."); } //Generate data for AQS/TQS template wSheet.Cells[1, 1] = "Official Code"; wSheet.Cells[1, 2] = ":"; wSheet.Cells[1, 3] = objAQSTQS.officialCodeStr; ((Range)wSheet.Cells[3, 1]).NumberFormat = "@"; wSheet.Cells[3, 1] = "========================================================"; wSheet.Cells[4, 1] = "Total RICs"; wSheet.Cells[4, 2] = ":"; wSheet.Cells[4, 3] = objAQSTQS.totalRicsStr; wSheet.Cells[5, 1] = "Exchange"; wSheet.Cells[5, 2] = ":"; wSheet.Cells[5, 3] = objAQSTQS.exchangeStr; ((Range)wSheet.Cells[7, 1]).Font.Bold = true; ((Range)wSheet.Cells[8, 1]).Font.Bold = true; wSheet.Cells[7, 1] = "1"; wSheet.Cells[8, 1] = "For AQS/TQS"; wSheet.Cells[9, 1] = "--------------------"; ((Range)wSheet.Cells[10, 3]).NumberFormat = "@"; wSheet.Cells[10, 1] = "Effective Date"; wSheet.Cells[10, 2] = ":"; wSheet.Cells[10, 3] = objAQSTQS.effectiveDateStr; wSheet.Cells[11, 1] = "RIC"; wSheet.Cells[11, 2] = ":"; wSheet.Cells[11, 3] = objAQSTQS.ricStr; wSheet.Cells[12, 1] = "Displayname"; wSheet.Cells[12, 2] = ":"; wSheet.Cells[12, 3] = objAQSTQS.displayNameStr; wSheet.Cells[13, 1] = "Official Code"; wSheet.Cells[13, 2] = ":"; wSheet.Cells[13, 3] = objAQSTQS.officialCodeStr; ((Range)wSheet.Cells[14, 1]).Font.Color = ColorTranslator.ToOle(Color.Red); ((Range)wSheet.Cells[14, 2]).Font.Color = ColorTranslator.ToOle(Color.Red); ((Range)wSheet.Cells[14, 1]).Font.Bold = true; wSheet.Cells[14, 1] = "Exchange Symbol"; wSheet.Cells[14, 2] = ":"; wSheet.Cells[15, 1] = "OFFC_CODE2"; wSheet.Cells[15, 2] = ":"; wSheet.Cells[15, 3] = objAQSTQS.offc_code2Str; wSheet.Cells[16, 1] = "Currency"; wSheet.Cells[16, 2] = ":"; wSheet.Cells[16, 3] = objAQSTQS.currencyStr; wSheet.Cells[17, 1] = "Recordtype"; wSheet.Cells[17, 2] = ":"; wSheet.Cells[17, 3] = objAQSTQS.recordtypeStr; wSheet.Cells[18, 1] = "Chain RIC (Old)"; wSheet.Cells[18, 2] = ":"; wSheet.Cells[18, 3] = objAQSTQS.chainRicStr; wSheet.Cells[19, 1] = "Chain RIC (New)"; wSheet.Cells[19, 2] = ":"; wSheet.Cells[19, 3] = objAQSTQS.chainRicOnListingDateStr; wSheet.Cells[21, 1] = "BCAST-REF"; wSheet.Cells[21, 2] = ":"; wSheet.Cells[21, 3] = objAQSTQS.bcastRefStr; wSheet.Cells[22, 1] = "UNDERLYING RIC (LONGLINK1)"; wSheet.Cells[22, 2] = ":"; wSheet.Cells[22, 3] = objAQSTQS.underlyingRicStr; wSheet.Cells[23, 1] = "LONGLINK2"; wSheet.Cells[23, 2] = ":"; wSheet.Cells[23, 3] = objAQSTQS.longlink2Str; ((Range)wSheet.Cells[24, 3]).NumberFormat = "@"; wSheet.Cells[24, 1] = "WNT_RATIO (DW per Underlying)"; wSheet.Cells[24, 2] = ":"; wSheet.Cells[24, 3] = objAQSTQS.wntRatioStr.Substring(0, objAQSTQS.wntRatioStr.IndexOf(":") - 1); ((Range)wSheet.Cells[25, 3]).NumberFormat = "0.00"; wSheet.Cells[25, 1] = "STRIKE_PRC (WT)"; wSheet.Cells[25, 2] = ":"; wSheet.Cells[25, 3] = objAQSTQS.strikePrcStr; ((Range)wSheet.Cells[26, 3]).NumberFormat = "@"; wSheet.Cells[26, 1] = "MATUR_DATE / EXPIR_DATE"; wSheet.Cells[26, 2] = ":"; wSheet.Cells[26, 3] = objAQSTQS.maturDateDT.Day + "-" + MonthTrans(objAQSTQS.maturDateDT) + "-" + objAQSTQS.maturDateDT.Year; //Generate data for NDA Template ((Range)wSheet.Cells[28, 1]).Font.Bold = true; wSheet.Cells[28, 1] = "For NDA"; wSheet.Cells[29, 1] = "--------------------"; ((Range)wSheet.Cells[30, 1]).Font.Bold = true; wSheet.Cells[30, 1] = "B. Existing Organisation Listing"; wSheet.Cells[31, 1] = "RIC"; wSheet.Cells[31, 2] = ":"; wSheet.Cells[31, 3] = objAQSTQS.ricStr; wSheet.Cells[32, 1] = "IDN Longname"; wSheet.Cells[32, 2] = ":"; wSheet.Cells[32, 3] = objAQSTQS.underlyingRicStr.Substring(0, objAQSTQS.underlyingRicStr.IndexOf(".BK")) + "_" + objAQSTQS.dwIssueShortNameStr + "@" + IDNLongNameFormat(objAQSTQS.strikePrcStr) + MonthTrans(objAQSTQS.maturDateDT).ToUpper() + (objAQSTQS.maturDateDT.Year).ToString().Substring(2, 2) + "CWNT"; wSheet.Cells[33, 1] = "Issue Classification"; wSheet.Cells[33, 2] = ":"; wSheet.Cells[33, 3] = "WT"; wSheet.Cells[34, 1] = "Primary Listing (RIC) /EDCOID"; wSheet.Cells[34, 2] = ":"; wSheet.Cells[34, 3] = objAQSTQS.bcastRefStr; wSheet.Cells[35, 1] = "Organisation Name (DIRNAME)"; wSheet.Cells[35, 2] = ":"; wSheet.Cells[35, 3] = objAQSTQS.dwIssuerStr; ((Range)wSheet.Cells[37, 1]).Font.Bold = true; wSheet.Cells[37, 1] = "Note (For Warrant Term & Conditions)"; wSheet.Cells[38, 1] = "RIC"; wSheet.Cells[38, 2] = ":"; wSheet.Cells[38, 3] = objAQSTQS.ricStr; ((Range)wSheet.Cells[39, 3]).NumberFormat = "@"; wSheet.Cells[39, 1] = "Issue Date"; wSheet.Cells[39, 2] = ":"; wSheet.Cells[39, 3] = objAQSTQS.effectiveDateStr; ((Range)wSheet.Cells[40, 3]).NumberFormat = "@"; wSheet.Cells[40, 1] = "Listed Date"; wSheet.Cells[40, 2] = ":"; wSheet.Cells[40, 3] = objAQSTQS.effectiveDateStr; ((Range)wSheet.Cells[41, 3]).NumberFormat = "@"; wSheet.Cells[41, 1] = "First Exercise Date"; wSheet.Cells[41, 2] = ":"; wSheet.Cells[41, 3] = objAQSTQS.exerciseDateStr + "(Automatic Exercise)"; ((Range)wSheet.Cells[42, 3]).NumberFormat = "@"; wSheet.Cells[42, 1] = "Last Exercise Date"; wSheet.Cells[42, 2] = ":"; wSheet.Cells[42, 3] = objAQSTQS.exerciseDateStr + "(Automatic Exercise)"; ((Range)wSheet.Cells[43, 3]).NumberFormat = "@"; wSheet.Cells[43, 1] = "Last Actual Trading Date"; wSheet.Cells[43, 2] = ":"; wSheet.Cells[43, 3] = objAQSTQS.lastTradingDateStr; ((Range)wSheet.Cells[44, 3]).NumberFormat = "@"; wSheet.Cells[44, 1] = "Expiry Date"; wSheet.Cells[44, 2] = ":"; wSheet.Cells[44, 3] = objAQSTQS.maturDateDT.Day + "-" + MonthTrans(objAQSTQS.maturDateDT) + "-" + objAQSTQS.maturDateDT.Year; ((Range)wSheet.Cells[45, 3]).NumberFormat = "0.00"; wSheet.Cells[45, 1] = "Strike Price"; wSheet.Cells[45, 2] = ":"; wSheet.Cells[45, 3] = objAQSTQS.strikePrcStr; ((Range)wSheet.Cells[46, 3]).NumberFormat = "@"; wSheet.Cells[46, 1] = "Ratio warrant : underlying"; wSheet.Cells[46, 2] = ":"; wSheet.Cells[46, 3] = objAQSTQS.wntRatioStr + " ( " + objAQSTQS.wntRatioStr.Substring(0, objAQSTQS.wntRatioStr.IndexOf(':') - 1) + " Derivative Warrants " + " : " + objAQSTQS.wntRatioStr.Substring(objAQSTQS.wntRatioStr.IndexOf(':') + 2) + " Stock )"; ((Range)wSheet.Cells[47, 3]).NumberFormat = "@"; wSheet.Cells[47, 1] = "Outstanding Warrant Quantity"; wSheet.Cells[47, 2] = ":"; wSheet.Cells[47, 3] = objAQSTQS.numberOfWarrants; wSheet.Cells[48, 1] = "ISIN"; wSheet.Cells[48, 2] = ":"; wSheet.Cells[48, 3] = objAQSTQS.offc_code2Str; //((Range)wSheet.Rows["49", System.Type.Missing]).Font.Color = ColorTranslator.ToOle(Color.Blue); wSheet.Cells[49, 1] = "Exercise Period"; wSheet.Cells[49, 2] = ":"; wSheet.Cells[49, 3] = "European Style; DW can be exercised only on Automatic Exercise Date."; ((Range)wSheet.Cells[51, 1]).Font.Bold = true; ((Range)wSheet.Cells[51, 3]).Font.Bold = true; wSheet.Cells[51, 1] = "RICs(ADD) in the Search"; wSheet.Cells[51, 2] = ":"; wSheet.Cells[51, 3] = objAQSTQS.ricStr; ((Range)wSheet.Cells[53, 1]).NumberFormat = "@"; wSheet.Cells[53, 1] = "===========================END============================="; xlApp.DisplayAlerts = false; xlApp.AlertBeforeOverwriting = false; string fullpath = configObj.SavePath + "\\" + "FM Temp-" + objAQSTQS.officialCodeStr + "_" + DateTime.Now.ToString("d").Replace('/', '-') + ".xls"; wBook.SaveCopyAs(fullpath); } catch (SystemException ex) { logger.LogErrorAndRaiseException(ex.ToString()); } finally { xlApp.Quit(); KillExcelProcess(xlApp); } }
/** * Quick method to get data from a dedicated URL * */ //private void DataCaptureByUrl() //{ // try // { // selenium.Open(configObj.html_url); // string txt = selenium.GetText("//pre"); // DataAnalysis(txt); // count++; // } // catch (SeleniumException ex) // { // logger.LogErrorAndRaiseException(ex.ToString()); // } //} /** * DataAnalysis method used to split pre txt to get required data * Return : void * Parameter: String sourceStr */ private void DataAnalysis(String sourceStr) { TailandRicTemplate objAQSTQS = new TailandRicTemplate(); Hashtable ht = new Hashtable(); //create a backup string to keep source string String bksourceStr = sourceStr; for (int i = 0; i < configObj.TailandRicFields.Count; i++) { sourceStr = sourceStr.Substring(sourceStr.IndexOf(configObj.TailandRicFields[i])); if (configObj.TailandRicFields[i] == "Exercise ratio (DW : Underlying asset)") { sourceStr = sourceStr.Substring(sourceStr.IndexOf(")") + 6); } else if (configObj.TailandRicFields[i] == "Number of derivative warrants (unit:") { sourceStr = sourceStr.Substring(sourceStr.IndexOf(":") + 8); } else { sourceStr = sourceStr.Substring(sourceStr.IndexOf(":") + 2); } ht.Add(configObj.TailandRicFields[i], sourceStr.Substring(0, sourceStr.IndexOf("\n"))); } //Get short name of issuer, need dealing if no short name contained in () String tempStr = bksourceStr.Substring(bksourceStr.IndexOf(configObj.TailandRicFields[0]), bksourceStr.IndexOf(configObj.TailandRicFields[1]) - bksourceStr.IndexOf(configObj.TailandRicFields[0])); if (tempStr.Contains("(THAILAND)")) { tempStr = tempStr.Substring(tempStr.IndexOf("(THAILAND)") + "(THAILAND)".Length); } objAQSTQS.dwIssueShortNameStr = tempStr.Substring(tempStr.IndexOf("(") + 1, tempStr.IndexOf(")") - tempStr.IndexOf("(") - 1); objAQSTQS.dwIssuerStr = DWIssuerFormat((string)ht["DW issuer"], objAQSTQS.dwIssueShortNameStr); objAQSTQS.totalRicsStr = "1"; objAQSTQS.exchangeStr = "SET"; objAQSTQS.effectiveDateStr = (string)ht["Trading date"]; objAQSTQS.officialCodeStr = (string)ht["DW name"]; objAQSTQS.ricStr = objAQSTQS.officialCodeStr + "tc.BK"; objAQSTQS.displayNameStr = (string)ht["Underlying asset(s)"] + " BY " + objAQSTQS.dwIssueShortNameStr + " CA#" + objAQSTQS.officialCodeStr.Substring(objAQSTQS.officialCodeStr.Length - 1, 1); //Get ISIN number objAQSTQS.offc_code2Str = DataCaptureFromSetSmart(objAQSTQS.officialCodeStr); objAQSTQS.currencyStr = "THB"; objAQSTQS.recordtypeStr = "97"; objAQSTQS.chainRicStr = "0#IPO.BK"; objAQSTQS.chainRicOnListingDateStr = "0#DW.BK, 0#" + ((string)ht["Underlying asset(s)"]).Substring(0, 1) + ".BK"; objAQSTQS.bcastRefStr = objAQSTQS.dwIssueShortNameStr + ".BK"; objAQSTQS.underlyingRicStr = (string)ht["Underlying asset(s)"] + ".BK"; objAQSTQS.longlink2Str = (string)ht["DW name"] + "tcta.BK"; objAQSTQS.wntRatioStr = (string)ht["Exercise ratio (DW : Underlying asset)"]; objAQSTQS.strikePrcStr = (string)ht["Exercise price (baht per share)"]; objAQSTQS.lastTradingDateStr = (string)ht["Last trading date"]; objAQSTQS.exerciseDateStr = (string)ht["Last exercise date"]; DateTime mDT = new DateTime(); if (DateTime.TryParse(objAQSTQS.exerciseDateStr, out mDT)) { objAQSTQS.maturDateDT = mDT.AddDays(1); } else { logger.LogErrorAndRaiseException("Fail to parse maturity date to date"); objAQSTQS.maturDateDT = DateTime.Now; } objAQSTQS.numberOfWarrants = (string)ht["Number of derivative warrants (unit:"]; GenerateFMTemplate(objAQSTQS); GenerateIDNBulkFile(objAQSTQS); }