/// <summary>
/// Created on 2014-02-17, kind web page changed on 2014-01-20
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool UpdateEquityISINReportNew(KoreaEquityInfo item)
{
HtmlNodeCollection records = SearchISIN(item.KoreaName, true, true);
if (records == null || records.Count > 1)
{
return(false);
}
string isin;
string copName;
foreach (HtmlNode n in records)
{
HtmlNode td = n.SelectSingleNode(".//td[2]");
isin = td.InnerText.Trim();
copName = n.SelectSingleNode(".//td[3]").InnerText.Trim();
if (copName.Contains("보통주"))
{
item.Type = "ORD";
}
else if (copName.Contains("우선주"))
{
item.Type = "PRF";
}
item.ISIN = isin;
return(true);
}
return(false);
}
public static bool UpdateKDRISINReport(KoreaEquityInfo item)
{
HtmlNodeCollection records = SearchISIN(item.KoreaName, false, false);
if (records == null)
{
return(false);
}
string isin;
string ticker;
foreach (HtmlNode n in records.Where(n => n.SelectSingleNode(".//td[4]").InnerText.Trim().Equals("예탁증서")))
{
isin = n.SelectSingleNode(".//td[2]").InnerText.Trim();
ticker = GetTickerByISIN(isin, 2);
if (item.Ticker.Equals(ticker))
{
item.ISIN = isin;
item.Type = "KDR";
return(true);
}
}
return(false);
}
/// <summary>
/// Format NDA IA suffix of column "ASSET COMMON NAME"
/// </summary>
/// <param name="type">equity type</param>
/// <returns>suffix</returns>
private string FormatNdaSuffix(KoreaEquityInfo item)
{
string type = item.Type;
if (string.IsNullOrEmpty(type))
{
string msg = string.Format("Name change item {0} do not have a type. Please confirm.", item.RIC);
Logger.Log(msg, Logger.LogType.Warning);
return("");
}
string suffix = "";
if (type.Equals("ORD"))
{
suffix = " Ord Shs";
}
else if (type.Equals("PRF"))
{
suffix = " Prf Shs";
}
else if (type.Equals("KDR"))
{
suffix = " KDR";
}
else
{
string msg = string.Format("Name change for {0} occurs. RIC:{1}. Please format the suffix of column: 'ASSET COMMON NAME' in NDA IA file. ", item.Type, item.RIC);
Logger.Log(msg, Logger.LogType.Warning);
}
return(suffix);
}
private void WriteFMEquityItem(_Worksheet wSheet, int startLine, KoreaEquityInfo item)
{
KoreaEquityInfo peoTemp = item;
((Range)wSheet.Cells[startLine, 1]).NumberFormat = "@";
wSheet.Cells[startLine, 1] = peoTemp.UpdateDate;
((Range)wSheet.Cells[startLine, 2]).NumberFormat = "@";
wSheet.Cells[startLine, 2] = peoTemp.EffectiveDate;
if (peoTemp.RIC.Length < 9)
{
((Range)wSheet.Cells[startLine, 3]).Interior.Color = ColorTranslator.ToOle(Color.Red);
}
wSheet.Cells[startLine, 3] = peoTemp.RIC;
wSheet.Cells[startLine, 4] = peoTemp.Type;
wSheet.Cells[startLine, 5] = peoTemp.RecordType;
wSheet.Cells[startLine, 6] = peoTemp.FM;
wSheet.Cells[startLine, 7] = peoTemp.IDNDisplayName;
if (peoTemp.IDNDisplayName.Length > 16)
{
((Range)wSheet.Cells[startLine, 7]).Interior.Color = ColorTranslator.ToOle(Color.Pink);
Logger.Log("IDN Display Name is highlight because of the length is over 16 characters!");
}
wSheet.Cells[startLine, 8] = peoTemp.ISIN;
((Range)wSheet.Cells[startLine, 9]).NumberFormat = "@";
if (String.IsNullOrEmpty(peoTemp.Ticker))
{
((Range)wSheet.Cells[startLine, 9]).Interior.Color = ColorTranslator.ToOle(Color.Red);
}
wSheet.Cells[startLine, 9] = peoTemp.Ticker;
wSheet.Cells[startLine, 10] = peoTemp.BcastRef;
wSheet.Cells[startLine, 11] = peoTemp.LegalName;
wSheet.Cells[startLine, 12] = peoTemp.KoreaName;
wSheet.Cells[startLine, 13] = peoTemp.Lotsize;
}
private void AppendOtherColumnToNDAFile(StringBuilder kqBuilder, KoreaEquityInfo item, string lotSize)
{
if (string.IsNullOrEmpty(item.ISIN))
{
kqBuilder.AppendFormat(",{0}", "==NOISIN==");
}
else
{
kqBuilder.AppendFormat(",{0}", "ISIN:" + item.ISIN);
}
kqBuilder.AppendFormat(",{0}", item.Ticker);
kqBuilder.AppendFormat(",{0}", item.IDNDisplayName);
kqBuilder.AppendFormat(",{0}", item.IDNDisplayName + " " + item.Type);
kqBuilder.AppendFormat(",{0}", "EQUITY");
if (string.IsNullOrEmpty(item.Type))
{
kqBuilder.AppendFormat(",{0}", "==NOTYPE==");
}
else
{
kqBuilder.AppendFormat(",{0}", item.Type.Equals("KDR") ? "DRC" : item.Type);
}
kqBuilder.AppendFormat(",{0}", "KRW");
kqBuilder.AppendFormat(",{0}", lotSize);
kqBuilder.AppendLine();
}
private void CompareIpoIdn(KoreaEquityInfo ipo)
{
if (idnDic == null)
{
return;
}
if (idnDic.ContainsKey(ipo.Ticker))
{
ReutersIdnInfo idnInfo = idnDic[ipo.Ticker];
if ((idnInfo.BcastRef == ipo.BcastRef) && (idnInfo.DsplyName == ipo.IDNDisplayName) && (idnInfo.OffclCode == ipo.Ticker))
{
return;
}
else
{
KoreaCheckIpoData changeData = new KoreaCheckIpoData();
changeData.ProductionType = ReutersProductionType.IDN;
changeData.TickerFm = ipo.Ticker;
changeData.TickerProduct = idnInfo.OffclCode;
changeData.BcastRefFm = ipo.BcastRef;
changeData.BcastRefProduct = idnInfo.BcastRef;
if (idnInfo.OffclCode != ipo.Ticker)
{
changeData.IsTickerSame = false;
}
if (idnInfo.BcastRef != ipo.BcastRef)
{
changeData.IsIsinSame = false;
}
changedIpo.Add(changeData);
}
}
else
{
// Mark Ticker missed.
KoreaCheckIpoData missData = new KoreaCheckIpoData();
missData.ProductionType = ReutersProductionType.IDN;
missData.TickerFm = ipo.Ticker;
missData.TickerProduct = string.Empty;
missData.BcastRefFm = ipo.BcastRef;
missData.BcastRefProduct = string.Empty;
missData.IsTickerSame = false;
missData.IsBcastRefSame = false;
missedIpo.Add(missData);
}
}
public static int UpdateEquity(KoreaEquityInfo equity)
{
string condition = string.Format("where RIC = '{0}' and FM = '{1}' and Status = 'Active'", equity.RIC, equity.FM);
DataTable dt = Select(ETI_KOREA_EQUITY_TABLE_NAME, new string[] { "*" }, condition);
if (dt == null)
{
return(0);
}
string effectiveDate = equity.EffectiveDate;
if (equity.EffectiveDate.Length == 4)
{
effectiveDate = equity.EffectiveDate + "-01-01";
}
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["UpdateDate"] = equity.UpdateDate;
dr["EffectiveDateAdd"] = effectiveDate;
dr["RIC"] = equity.RIC;
dr["Type"] = equity.Type;
dr["RecordType"] = equity.RecordType;
dr["IDNDisplayName"] = equity.IDNDisplayName;
dr["ISIN"] = equity.ISIN;
dr["BCAST_REF"] = equity.BcastRef;
dr["LegalName"] = equity.LegalName;
dr["KoreaName"] = equity.KoreaName;
dr["LotSize"] = equity.Lotsize;
dr["Status"] = equity.Status;
}
}
else
{
DataRow dr = dt.NewRow();
dr["UpdateDate"] = equity.UpdateDate;
dr["EffectiveDateAdd"] = effectiveDate;
dr["RIC"] = equity.RIC;
dr["Type"] = equity.Type;
dr["RecordType"] = equity.RecordType;
dr["IDNDisplayName"] = equity.IDNDisplayName;
dr["ISIN"] = equity.ISIN;
dr["BCAST_REF"] = equity.BcastRef;
dr["LegalName"] = equity.LegalName;
dr["KoreaName"] = equity.KoreaName;
dr["LotSize"] = equity.Lotsize;
dr["Ticker"] = equity.Ticker;
dr["FM"] = equity.FM;
dr["Status"] = equity.Status;
dt.Rows.Add(dr);
}
return(UpdateDbTable(dt, ETI_KOREA_EQUITY_TABLE_NAME));
}
/// <summary>
/// Created on 2014-02-17, kind web page changed on 2014-01-20
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool UpdateKDRISINReportNew(KoreaEquityInfo item)
{
HtmlNodeCollection records = SearchISIN(item.KoreaName, false);
if (records == null || records.Count < 1)
{
return(false);
}
string isin = null;
string copName = null;
//foreach (HtmlNode n in records)
//{
// if (!n.SelectSingleNode(".//td[4]").InnerText.Trim().Equals("예탁증서"))
// {
// continue;
// }
// isin = n.SelectSingleNode(".//td[2]").InnerText.Trim();
// item.ISIN = isin;
// item.Type = "KDR";
// return true;
//}
//return false;
foreach (HtmlNode n in records)
{
HtmlNode td = n.SelectSingleNode(".//td[2]");
isin = td.InnerText.Trim();
HtmlNode aNode = td.SelectSingleNode(".//a");
string checkMod = aNode.Attributes["href"].Value.Trim();
// string type = checkMod.Split(',')[2].Trim(new char[] { '\'', ')', ';' });
copName = n.SelectSingleNode(".//td[3]").InnerText.Trim();
if (copName.Contains("보통주"))
{
item.Type = "ORD";
}
else if (copName.Contains("우선주"))
{
continue;
//item.Type = "PRF";
}
item.ISIN = isin;
return(true);
}
return(false);
}
public static void GetTickerAndLegalNameByISIN(KoreaEquityInfo item)
{
string isin = item.ISIN;
string type = item.Type;
if (string.IsNullOrEmpty(isin) || string.IsNullOrEmpty(type))
{
return;
}
string url = null;
if (type.Equals("ORD") || type.Equals("PRF"))
{
url = string.Format(detailURLFormat, isin);
}
else if (type.Equals("KDR"))
{
url = string.Format(detailURLFormatKDR, isin);
}
HtmlDocument detailDoc = WebClientUtil.GetHtmlDocument(url, 180000, null, Encoding.GetEncoding("EUC-KR"));
if (detailDoc == null)
{
return;
}
if (detailDoc.DocumentNode.SelectNodes("//table").Count < 2)
{
return;
}
HtmlNode tickerNode = detailDoc.DocumentNode.SelectNodes("//table")[2].SelectSingleNode(".//tr[3]/td[4]");
HtmlNode legalNameNode;
if (type.Equals("ORD") || type.Equals("PRF"))
{
legalNameNode = detailDoc.DocumentNode.SelectNodes("//table")[2].SelectSingleNode(".//tr[11]/td[4]");
}
else
{
legalNameNode = detailDoc.DocumentNode.SelectNodes("//table")[2].SelectSingleNode(".//tr[5]/td[4]");
}
if (tickerNode == null)
{
return;
}
item.Ticker = tickerNode.InnerText.Trim().Substring(1).Trim();
item.LegalName = legalNameNode.InnerText.Trim();
}
private void FormatAnnouncement()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
for (var i = 0; i < announcementList.Count; i++)
{
KoreaEquityInfo peoTemp = announcementList[i];
if (string.IsNullOrEmpty(peoTemp.Ticker))
{
continue;
}
peoTemp.UpdateDate = DateTime.Today.ToString("dd-MMM-yy");
peoTemp.EffectiveDate = DateTime.Today.Year.ToString();
peoTemp.RecordType = "113";
if (peoTemp.Type.Equals("PRF"))
{
peoTemp.RecordType = "97";
if (peoTemp.KoreaName.Contains("우선주") && peoTemp.KoreaName.Length > 14)
{
string tempName = peoTemp.KoreaName.Replace("우선주", "");
if (tempName.Length > 14)
{
peoTemp.KoreaName = tempName.Substring(0, 13) + "우";
}
else
{
peoTemp.KoreaName = tempName + "우";
}
}
}
if (peoTemp.KoreaName.Length > 14)
{
peoTemp.KoreaName = peoTemp.KoreaName.Substring(0, 14);
}
peoTemp.FM = "1";
peoTemp.RIC = peoTemp.Ticker + "." + peoTemp.Market;
peoTemp.BcastRef = peoTemp.RIC;
KoreaEquityCommon.FormatEQIdnDisplayName(peoTemp);
}
}
public static bool UpdateEquityISINReport(KoreaEquityInfo item)
{
HtmlNodeCollection records = SearchISIN(item.KoreaName, true, true);
if (records == null)
{
return(false);
}
string isin;
string ticker = null;
string copName;
foreach (HtmlNode n in records)
{
HtmlNode td = n.SelectSingleNode(".//td[2]");
isin = td.InnerText.Trim();
HtmlNode aNode = td.SelectSingleNode(".//a");
string checkMod = aNode.Attributes["href"].Value.Trim();
string type = checkMod.Split(',')[2].Trim(new[] { '\'', ')', ';' });
if (type.Equals("STK"))
{
ticker = GetTickerByISIN(isin, 1);
}
if (item.Ticker.Equals(ticker))
{
copName = n.SelectSingleNode(".//td[3]").InnerText.Trim();
if (copName.Contains("보통주"))
{
item.Type = "ORD";
}
else if (copName.Contains("우선주"))
{
item.Type = "PRF";
}
item.ISIN = isin;
return(true);
}
}
return(false);
}
public static KoreaEquityInfo SelectEquityFMOne(string ric)
{
string condition = string.Format("where RIC = '{0}' and FM = 1", ric);
DataTable dt = Select(ETI_KOREA_EQUITY_TABLE_NAME, new string[] { "*" }, condition);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
KoreaEquityInfo itemFm1 = new KoreaEquityInfo();
itemFm1.LegalName = Convert.ToString(dr["LegalName"]).Trim();
itemFm1.KoreaName = Convert.ToString(dr["KoreaName"]).Trim();
itemFm1.IDNDisplayName = Convert.ToString(dr["IDNDisplayName"]).Trim();
itemFm1.ISIN = Convert.ToString(dr["ISIN"]).Trim();
return(itemFm1);
}
public static KoreaEquityInfo SelectEquityByRic(string ric)
{
string condition = string.Format("where RIC = '{0}' and FM ='2' and Status = 'Active'", ric);
DataTable dt = Select(ETI_KOREA_EQUITY_TABLE_NAME, new string[] { "*" }, condition);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
KoreaEquityInfo item = new KoreaEquityInfo();
item.ISIN = Convert.ToString(dr["ISIN"]).Trim();
item.LegalName = Convert.ToString(dr["LegalName"]).Trim();
item.IDNDisplayName = Convert.ToString(dr["IDNDisplayName"]).Trim();
item.Type = Convert.ToString(dr["Type"]).Trim();
item.RIC = ric;
return(item);
}
public static List <KoreaEquityInfo> SelectEquityByDate(string updateDate)
{
string condition = string.Format("where UpdateDate = '{0}'", updateDate);
DataTable dt = Select(ETI_KOREA_EQUITY_TABLE_NAME, new string[] { "*" }, condition);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
List <KoreaEquityInfo> equities = new List <KoreaEquityInfo>();
foreach (DataRow dr in dt.Rows)
{
KoreaEquityInfo item = new KoreaEquityInfo();
item.RIC = Convert.ToString(dr["RIC"]).Trim();
item.Ticker = Convert.ToString(dr["Ticker"]).Trim();
item.IDNDisplayName = Convert.ToString(dr["IDNDisplayName"]).Trim();
item.ISIN = Convert.ToString(dr["ISIN"]).Trim();
item.BcastRef = Convert.ToString(dr["BCAST_REF"]).Trim();
equities.Add(item);
}
return(equities);
}
private List <KoreaEquityInfo> FilterTodayAnnounce(IEnumerable <List <string> > records)
{
List <KoreaEquityInfo> anns = new List <KoreaEquityInfo>();
//If code existed in DB. Ignore this announcement.
foreach (List <string> record in records)
{
string code = record[0];
if (KoreaEquityManager.ExistsFmOneCode(code))
{
Logger.Log(string.Format("Announcement of {0} is already existed in database.", record[1]));
continue;
}
KoreaEquityInfo ann = new KoreaEquityInfo
{
KoreaName = record[1],
EffectiveDate = record[2],
Market = GetDutyCode(record[3])
};
anns.Add(ann);
}
return(anns);
}
/// <summary>
/// Format equity's ASSET COMMON NAME in NDA IA file.
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private string FormatAssetCommonName(KoreaEquityInfo item)
{
string company = ClearCoLtdForName(item.LegalName);
if (item.Type.Equals("PRF"))
{
int index = 0;
string ending = null;
if (string.IsNullOrEmpty(item.PrfEnd))
{
Regex regex = new Regex("[0-9]+P");
Match match = regex.Match(company);
if (match.Success)
{
index = match.Index;
ending = match.Value;
}
}
else
{
index = company.IndexOf(item.PrfEnd);
ending = item.PrfEnd;
}
company = company.Replace(ending, "");
FormatAssetNameNoEnd(ref company);
if (!string.IsNullOrEmpty(ending))
{
company = company.Insert(index, ending);
}
}
else
{
FormatAssetNameNoEnd(ref company);
}
return(company);
}
public static List <KoreaEquityInfo> SelectEquityByEffectiveDateChange(string effectiveDate)
{
string condition = string.Format("where EffectiveDateChange = '{0}' and FM = '2'", effectiveDate);
DataTable dt = Select(ETI_KOREA_EQUITY_TABLE_NAME, new string[] { "*" }, condition);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
List <KoreaEquityInfo> equities = new List <KoreaEquityInfo>();
foreach (DataRow dr in dt.Rows)
{
KoreaEquityInfo item = new KoreaEquityInfo();
item.RIC = Convert.ToString(dr["RIC"]).Trim();
item.Type = Convert.ToString(dr["Type"]).Trim();
item.LegalName = Convert.ToString(dr["LegalName"]).Trim();
item.KoreaName = Convert.ToString(dr["KoreaName"]).Trim();
item.IDNDisplayName = Convert.ToString(dr["IDNDisplayName"]).Trim();
item.ISIN = Convert.ToString(dr["ISIN"]).Trim();
equities.Add(item);
}
return(equities);
}
private void CompareIpoNda(KoreaEquityInfo ipo)
{
if (dseDic == null)
{
return;
}
if (dseDic.ContainsKey(ipo.Ticker))
{
DseFileInfo dseInfo = dseDic[ipo.Ticker];
string[] securityNames = dseInfo.SecurityDescription.Split(' ');
string type = string.Empty;
if (securityNames.Length > 1)
{
type = securityNames[securityNames.Length - 1];
dseInfo.SecurityDescription = dseInfo.SecurityDescription.Replace(type, "").Trim();
}
if ((dseInfo.ISIN == ipo.ISIN) && (dseInfo.SecurityDescription == ipo.IDNDisplayName))
{
return;
}
else
{
KoreaCheckIpoData changeData = new KoreaCheckIpoData();
changeData.ProductionType = ReutersProductionType.NDA;
changeData.TickerFm = ipo.Ticker;
changeData.TickerProduct = dseInfo.Ticker;
changeData.IsTickerSame = true;
changeData.IsinFm = ipo.ISIN;
changeData.IsinProduct = dseInfo.ISIN;
if (dseInfo.ISIN != ipo.ISIN)
{
changeData.IsIsinSame = false;
}
changedIpo.Add(changeData);
}
}
else
{
// Mark Ticker missed.
KoreaCheckIpoData missData = new KoreaCheckIpoData();
missData.ProductionType = ReutersProductionType.NDA;
missData.TickerFm = ipo.Ticker;
missData.TickerProduct = string.Empty;
missData.IsinFm = ipo.ISIN;
missData.IsinProduct = string.Empty;
missData.IsTickerSame = false;
missData.IsIsinSame = false;
missedIpo.Add(missData);
}
}
public static void GetTickerAndLegalNameByISIN(KoreaEquityInfo item)
{
string isin = item.ISIN;
string type = item.Type;
if (string.IsNullOrEmpty(isin) || string.IsNullOrEmpty(type))
{
return;
}
string url = @"http://isin.krx.co.kr/srch/srch.do?method=srchPopup2";
HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
request.Timeout = 300000;
request.Method = "POST";
request.CookieContainer = cookies;
request.Headers["Accept-Encoding"] = @"gzip,deflate,sdch";
request.Headers["Accept-Language"] = @"en-US,en;q=0.8,zh-CN;q=0.6,zh;q=0.4";
request.Headers["Cache-Control"] = @"max-age=0";
request.UserAgent = @"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.57 Safari/537.36";
request.Accept = @"text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8";
request.ContentType = @"application/x-www-form-urlencoded";
request.Headers["Origin"] = @"http://isin.krx.co.kr";
request.Referer = @"http://isin.krx.co.kr/srch/srch.do?method=srchList";
request.Host = @"isin.krx.co.kr";
request.KeepAlive = true;
string postData = @"stdcd_type=2&std_cd=" + item.ISIN;
byte[] buf = Encoding.UTF8.GetBytes(postData);
request.ContentLength = buf.Length;
request.GetRequestStream().Write(buf, 0, buf.Length);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
StreamReader sr = new StreamReader(response.GetResponseStream());
string ss = sr.ReadToEnd();
HtmlDocument detailDoc = new HtmlDocument();
detailDoc.LoadHtml(ss);
//string url = null;
//if (type.Equals("ORD") || type.Equals("PRF"))
//{
// url = string.Format(detailURLFormat, isin);
//}
//else if (type.Equals("KDR"))
//{
// url = string.Format(detailURLFormatKDR, isin);
//}
//HtmlDocument detailDoc = WebClientUtil.GetHtmlDocument(url, 180000, null, Encoding.GetEncoding("EUC-KR"));
//if (detailDoc == null)
//{
// return;
//}
String ticker = String.Empty;
if (detailDoc.DocumentNode.SelectNodes("//table").Count < 2)
{
return;
}
HtmlNode tickerNode = detailDoc.DocumentNode.SelectNodes("//table")[1].SelectSingleNode(".//tr[2]/td[2]");
HtmlNode legalNameNode = null;
legalNameNode = detailDoc.DocumentNode.SelectNodes("//table")[1].SelectSingleNode(".//tr[3]/td[2]");
//if (type.Equals("ORD") || type.Equals("PRF"))
//{
// legalNameNode = detailDoc.DocumentNode.SelectNodes("//table")[2].SelectSingleNode(".//tr[11]/td[4]");
//}
//else
//{
// legalNameNode = detailDoc.DocumentNode.SelectNodes("//table")[2].SelectSingleNode(".//tr[5]/td[4]");
//}
if (tickerNode == null)
{
return;
}
item.Ticker = tickerNode.InnerText.Trim().Substring(1).Trim();
item.LegalName = legalNameNode.InnerText.Trim();
}
