private void LoadData()
{
DataView dv = DeriLib.Util.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
if (dv.Count > 0)
{
dt.Rows.Clear();
foreach (DataRowView drv in dv)
{
DataRow dr = dt.NewRow();
string warrantType = drv["WarrantType"].ToString();
string type = "";
string cp = "";
CallPutType cpType = CallPutType.Call;
double financialR = 0.0;
if (warrantType == "浮動重設認購權證")
{
type = "重設型";
cp = "C";
cpType = CallPutType.Call;
}
else if (warrantType == "浮動重設認售權證")
{
type = "重設型";
cp = "P";
cpType = CallPutType.Put;
}
else if (warrantType == "重設型牛證認購")
{
type = "牛熊證";
cp = "C";
cpType = CallPutType.Call;
financialR = 5.0;
}
else if (warrantType == "重設型熊證認售")
{
type = "牛熊證";
cp = "P";
cpType = CallPutType.Put;
financialR = 5.0;
}
else if (warrantType == "一般型認售權證")
{
type = "一般型";
cp = "P";
cpType = CallPutType.Put;
}
else
{
type = "一般型";
cp = "C";
cpType = CallPutType.Call;
}
double s = Convert.ToDouble(drv["MPrice"]);
double k = Convert.ToDouble(drv["K"]);
int t = Convert.ToInt32(drv["T"]);
double cr = Convert.ToDouble(drv["exeRatio"]);
double hv = Convert.ToDouble(drv["HV"]);
double iv = Convert.ToDouble(drv["IV"]);
double resetR = Convert.ToDouble(drv["ResetR"]);
double barrierR = Convert.ToDouble(drv["BarrierR"]);
DateTime issueDate = Convert.ToDateTime(drv["IssueDate"]);
string issueDateStr = issueDate.ToShortDateString();
DateTime expiry = Convert.ToDateTime(drv["ExpiryDate"]);
string expiryStr = expiry.ToShortDateString();
double price = 0.0;
double delta = 0.0;
if (s != 0.0)
{
if (type == "牛熊證")
{
price = Pricing.BullBearWarrantPrice(cpType, s, (resetR / 100), GlobalVar.globalParameter.interestRate, (iv / 100), t, (financialR / 100), cr);
}
else if (type == "重設型")
{
price = Pricing.ResetWarrantPrice(cpType, s, (resetR / 100), GlobalVar.globalParameter.interestRate, (iv / 100), t, cr);
}
else
{
price = Pricing.NormalWarrantPrice(cpType, s, k, GlobalVar.globalParameter.interestRate, (iv / 100), t, cr);
}
if (warrantType == "牛熊證")
{
delta = 1.0;
}
else
{
delta = Pricing.Delta(cpType, s, k, GlobalVar.globalParameter.interestRate, (iv / 100), (t * 30.0) / GlobalVar.globalParameter.dayPerYear, GlobalVar.globalParameter.interestRate) * cr;
}
}
dr["標的代號"] = drv["UnderlyingID"].ToString();
dr["標的名稱"] = drv["UnderlyingName"].ToString();
dr["權證代號"] = drv["WarrantID"].ToString();
dr["型態"] = type;
dr["CP"] = cp;
dr["S"] = s;
dr["K"] = k;
dr["T"] = t;
dr["行使比例"] = cr;
dr["HV"] = hv;
dr["IV"] = iv;
dr["重設比"] = resetR;
dr["界限比"] = barrierR;
dr["財務費用"] = financialR;
dr["發行日"] = issueDate;
dr["到期日"] = expiry;
dr["今日理論價"] = Math.Round(price, 2);
dr["Delta"] = Math.Round(delta, 4);
dr["交易員"] = drv["TraderID"].ToString().PadLeft(7, '0');
dt.Rows.Add(dr);
}
}
}
private void LoadData()
{
try {
dt.Rows.Clear();
string sql = @"SELECT a.SerialNumber
,a.TraderID
,b.WarrantName
,a.UnderlyingID
,a.Apply1500W
,b.Market
,a.Type
,a.CP
,IsNull(c.MPrice,0) MPrice
,a.K
,a.T
,a.R
,a.HV
,CASE WHEN a.Apply1500W='Y' THEN a.IVNew ELSE a.IV END IVNew
,a.ResetR
,a.BarrierR
,a.FinancialR
,a.IssueNum
,b.EquivalentNum
,b.Result
,a.IV
,CASE WHEN a.CP='C' THEN d.Reason ELSE d.ReasonP END Reason
FROM [EDIS].[dbo].[ApplyOfficial] a
LEFT JOIN [EDIS].[dbo].[ApplyTotalList] b ON a.SerialNumber=b.SerialNum
LEFT JOIN [EDIS].[dbo].[WarrantPrices] c on a.UnderlyingID=c.CommodityID
left join Underlying_TraderIssue d on a.UnderlyingID=d.UID
ORDER BY b.Market desc, a.Type, a.CP, a.UnderlyingID, a.SerialNumber"; //or (a.UnderlyingID = 'IX0001' and d.UID ='TWA00')
DataView dv = DeriLib.Util.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
foreach (DataRowView drv in dv)
{
DataRow dr = dt.NewRow();
dr["序號"] = drv["SerialNumber"].ToString();
dr["交易員"] = drv["TraderID"].ToString().TrimStart('0');
dr["權證名稱"] = drv["WarrantName"].ToString();
dr["標的代號"] = drv["UnderlyingID"].ToString();
dr["1500W"] = drv["Apply1500W"].ToString();
dr["市場"] = drv["Market"].ToString();
dr["張數"] = drv["IssueNum"];
dr["約當張數"] = drv["EquivalentNum"];
dr["額度結果"] = drv["Result"];
dr["IVOri"] = drv["IV"];
double underlyingPrice = 0.0;
underlyingPrice = Convert.ToDouble(drv["MPrice"]);
dr["股價"] = underlyingPrice;
double k = Convert.ToDouble(drv["K"]);
dr["履約價"] = k;
int t = Convert.ToInt32(drv["T"]);
dr["期間"] = t;
double cr = Convert.ToDouble(drv["R"]);
dr["行使比例"] = cr;
dr["HV"] = Convert.ToDouble(drv["HV"]);
double vol = Convert.ToDouble(drv["IVNew"]) / 100;
dr["IV"] = Convert.ToDouble(drv["IVNew"]);
double resetR = Convert.ToDouble(drv["ResetR"]) / 100;
dr["重設比"] = Convert.ToDouble(drv["ResetR"]);
//double barrierR = Convert.ToDouble(drv["BarrierR"]);
dr["界限比"] = Convert.ToDouble(drv["BarrierR"]);
double financialR = Convert.ToDouble(drv["FinancialR"]) / 100;
dr["財務費用"] = Convert.ToDouble(drv["FinancialR"]);
string warrantType = drv["Type"].ToString();
dr["類型"] = warrantType;
CallPutType cp = drv["CP"].ToString() == "C" ? CallPutType.Call : CallPutType.Put;
dr["CP"] = drv["CP"].ToString();
dr["發行原因"] = drv["Reason"] == DBNull.Value ? " " : reasonString[Convert.ToInt32(drv["Reason"])];
double price = 0.0;
if (underlyingPrice != 0)
{
if (warrantType == "牛熊證")
{
price = Pricing.BullBearWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (warrantType == "重設型")
{
price = Pricing.ResetWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
price = Pricing.NormalWarrantPrice(cp, underlyingPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
}
dr["發行價格"] = Math.Round(price, 2);
dt.Rows.Add(dr);
}
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void ultraGrid1_AfterCellUpdate(object sender, CellEventArgs e)
{
if (e.Cell.Column.Key != "交易員" && e.Cell.Column.Key != "權證名稱" && e.Cell.Column.Key != "發行價格" && e.Cell.Column.Key != "標的代號" && e.Cell.Column.Key != "市場" && e.Cell.Column.Key != "1500W")
{
double price = 0.0;
double underlyingPrice = 0.0;
underlyingPrice = e.Cell.Row.Cells["股價"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["股價"].Value);
double k = 0.0;
k = e.Cell.Row.Cells["履約價"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["履約價"].Value);
int t = 0;
t = e.Cell.Row.Cells["期間"].Value == DBNull.Value ? 0 : Convert.ToInt32(e.Cell.Row.Cells["期間"].Value);
double cr = 0.0;
cr = e.Cell.Row.Cells["行使比例"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["行使比例"].Value);
double vol = 0.0;
vol = e.Cell.Row.Cells["IV"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["IV"].Value) / 100;
double resetR = 0.0;
resetR = e.Cell.Row.Cells["重設比"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["重設比"].Value) / 100;
double financialR = 0.0;
financialR = e.Cell.Row.Cells["財務費用"].Value == DBNull.Value ? 0 : Convert.ToDouble(e.Cell.Row.Cells["財務費用"].Value) / 100;
string warrantType = "一般型";
warrantType = e.Cell.Row.Cells["類型"].Value == DBNull.Value ? "一般型" : e.Cell.Row.Cells["類型"].Value.ToString();
string cpType = "C";
cpType = e.Cell.Row.Cells["CP"].Value == DBNull.Value ? "C" : e.Cell.Row.Cells["CP"].Value.ToString();
if (warrantType != "一般型" && warrantType != "牛熊證" && warrantType != "重設型")
{
if (warrantType == "2")
{
warrantType = "牛熊證";
}
else if (warrantType == "3")
{
warrantType = "重設型";
}
else
{
warrantType = "一般型";
}
}
if (cpType != "C" && cpType != "P")
{
if (cpType == "2")
{
cpType = "P";
}
else
{
cpType = "C";
}
}
CallPutType cp = CallPutType.Call;
if (cpType == "P")
{
cp = CallPutType.Put;
}
else
{
cp = CallPutType.Call;
}
if (warrantType == "牛熊證")
{
price = Pricing.BullBearWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (warrantType == "重設型")
{
price = Pricing.ResetWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
price = Pricing.NormalWarrantPrice(cp, underlyingPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
/*e.Cell.Row.Cells["履約價"].Appearance.ForeColor = Color.Black;
* if (warrantType != "牛熊證") {
* if (cpType == "C" && k / underlyingPrice >= 1.5) {
* e.Cell.Row.Cells["履約價"].Appearance.ForeColor = Color.Red;
* } else if (cpType == "P" && k / underlyingPrice <= 0.5) {
* e.Cell.Row.Cells["履約價"].Appearance.ForeColor = Color.Red;
* }
* }*/
double shares = 0.0;
shares = e.Cell.Row.Cells["張數"].Value == DBNull.Value ? 10000 : Convert.ToDouble(e.Cell.Row.Cells["張數"].Value);
/*
* string is1500W = "N";
* is1500W = e.Cell.Row.Cells["1500W"].Value == DBNull.Value ? "N" : (string)e.Cell.Row.Cells["1500W"].Value;
* if (e.Cell.Column.Key == "1500W" && is1500W=="Y")
* {
* double totalValue = 0.0;
* totalValue = price * shares * 1000;
* while (totalValue < 15000000)
* {
* vol += 0.01;
* if (warrantType == "牛熊證")
* price = Pricing.BullBearWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
* else if (warrantType == "重設型")
* price = Pricing.ResetWarrantPrice(cp, underlyingPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
* else
* price = Pricing.NormalWarrantPrice(cp, underlyingPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
* totalValue = price * shares * 1000;
* }
* e.Cell.Row.Cells["IV"].Value = Math.Round(vol * 100, 0);
* }
* */
e.Cell.Row.Cells["發行價格"].Value = Math.Round(price, 2);
}
string is1500W = "N";
is1500W = e.Cell.Row.Cells["1500W"].Value.ToString();
if (e.Cell.Column.Key == "1500W")
{
if (is1500W == "N")
{
e.Cell.Row.Cells["IV"].Value = e.Cell.Row.Cells["IVOri"].Value;
}
}
}
private void 證系統上傳檔ToolStripMenuItem_Click(object sender, EventArgs e)
{
string fileName = "D:\\權證發行_相關Excel\\上傳檔\\權證發行匯入檔.xls";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook workBook = null;
try {
string sql = @"SELECT a.UnderlyingID
,c.TraderID
,a.WarrantName
,c.Type
,c.CP
,IsNull(b.MPrice,0) MPrice
,c.K
,c.ResetR
,c.BarrierR
,c.T
,a.CR
,c.HV
,c.IV
,a.IssueNum
,c.FinancialR
,a.UseReward
,c.Apply1500W
,c.SerialNumber
FROM [EDIS].[dbo].[ApplyTotalList] a
LEFT JOIN [EDIS].[dbo].[WarrantPrices] b ON a.UnderlyingID=b.CommodityID
LEFT JOIN [EDIS].[dbo].[ApplyOfficial] c ON a.SerialNum=c.SerialNumber
WHERE a.ApplyKind='1' AND a.Result+0.00001 >= a.EquivalentNum
ORDER BY a.Market desc, a.Type, a.CP, a.UnderlyingID, a.SerialNum"; //a.SerialNum
//DataView dv = DeriLib.Util.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
System.Data.DataTable dv = MSSQL.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
if (dv.Rows.Count > 0)
{
int i = 3;
app.Visible = true;
workBook = app.Workbooks.Open(fileName);
//workBook.EnvelopeVisible = false;
Worksheet workSheet = (Worksheet)workBook.Sheets[1];
workSheet.get_Range("A3:BZ1000").ClearContents();
//workSheet.UsedRange.
foreach (DataRow dr in dv.Rows)
{
string date = DateTime.Today.ToString("yyyyMMdd");
string underlyingID = dr["UnderlyingID"].ToString();
string traderID = dr["TraderID"].ToString().TrimStart('0');
if (traderID == "10120" || traderID == "10329")
{
traderID = "7643";
}
string warrantName = dr["WarrantName"].ToString();
string type = dr["Type"].ToString();
string cp = dr["CP"].ToString();
string isReset = "N";
if (type == "重設型" || type == "牛熊證")
{
isReset = "Y";
}
double stockPrice = Convert.ToDouble(dr["MPrice"]);
double k = Convert.ToDouble(dr["K"]);
double resetR = Convert.ToDouble(dr["ResetR"]);
double barrierR = Convert.ToDouble(dr["BarrierR"]);
if (isReset == "Y")
{
k = Math.Round(resetR / 100 * stockPrice, 2);
}
double barrierP = Math.Round(barrierR / 100 * stockPrice, 2);
if (type == "牛熊證")
{
if (cp == "C")
{
barrierP = Math.Round(Math.Floor(barrierR * stockPrice) / 100, 2);
}
else if (cp == "P")
{
barrierP = Math.Round(Math.Ceiling(barrierR * stockPrice) / 100, 2);
}
}
//Check for moneyness constraint
if (type != "牛熊證")
{
if ((cp == "C" && k / stockPrice >= 1.5) || (cp == "P" && k / stockPrice <= 0.5))
{
MessageBox.Show(warrantName + " 超過價外50%限制");
// continue;
}
}
int t = Convert.ToInt32(dr["T"]);
double cr = Convert.ToDouble(dr["CR"]);
double r = GlobalVar.globalParameter.interestRate * 100;
double hv = Convert.ToDouble(dr["HV"]);
double iv = Convert.ToDouble(dr["IV"]);
double issueNum = Convert.ToDouble(dr["IssueNum"]);
double price = 0.0;
double financialR = Convert.ToDouble(dr["FinancialR"]);
string isReward = dr["UseReward"].ToString();
string is1500W = dr["Apply1500W"].ToString();
string serialNum = dr["SerialNumber"].ToString();
double p = 0.0;
double vol = iv / 100;
if (is1500W == "Y")
{
CallPutType cpType = CallPutType.Call;
if (cp == "P")
{
cpType = CallPutType.Put;
}
if (type == "牛熊證")
{
p = Pricing.BullBearWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (type == "重設型")
{
p = Pricing.ResetWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
p = Pricing.NormalWarrantPrice(cpType, stockPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
double totalValue = p * issueNum * 1000;
double volUpperLimmit = vol * 2;
while (totalValue < 15000000 && vol < volUpperLimmit)
{
vol += 0.01;
if (type == "牛熊證")
{
p = Pricing.BullBearWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (type == "重設型")
{
p = Pricing.ResetWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
p = Pricing.NormalWarrantPrice(cpType, stockPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
totalValue = p * issueNum * 1000;
}
if (vol < volUpperLimmit)
{
iv = vol * 100;
string cmdText = "UPDATE [ApplyOfficial] SET IVNew=@IVNew WHERE SerialNumber=@SerialNumber";
List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();
pars.Add(new SqlParameter("@IVNew", SqlDbType.Float));
pars.Add(new SqlParameter("@SerialNumber", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);
h.SetParameterValue("@IVNew", iv);
h.SetParameterValue("@SerialNumber", serialNum);
h.ExecuteCommand();
h.Dispose();
}
}
if (type == "重設型")
{
type = "一般型";
}
if (cp == "P")
{
cp = "認售";
}
else
{
cp = "認購";
}
try {
// workSheet.Cells[1][i] = date;
workSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value = date;
workSheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value = underlyingID;
workSheet.get_Range("C" + i.ToString(), "C" + i.ToString()).Value = traderID;
workSheet.get_Range("D" + i.ToString(), "D" + i.ToString()).Value = warrantName;
workSheet.get_Range("E" + i.ToString(), "E" + i.ToString()).Value = type;
workSheet.get_Range("F" + i.ToString(), "F" + i.ToString()).Value = cp;
workSheet.get_Range("G" + i.ToString(), "G" + i.ToString()).Value = isReset;
workSheet.get_Range("H" + i.ToString(), "H" + i.ToString()).Value = stockPrice;
workSheet.get_Range("I" + i.ToString(), "I" + i.ToString()).Value = k;
workSheet.get_Range("J" + i.ToString(), "J" + i.ToString()).Value = resetR;
workSheet.get_Range("K" + i.ToString(), "K" + i.ToString()).Value = barrierP;
workSheet.get_Range("L" + i.ToString(), "L" + i.ToString()).Value = barrierR;
workSheet.get_Range("M" + i.ToString(), "M" + i.ToString()).Value = t;
workSheet.get_Range("N" + i.ToString(), "N" + i.ToString()).Value = cr;
workSheet.get_Range("O" + i.ToString(), "O" + i.ToString()).Value = r;
workSheet.get_Range("P" + i.ToString(), "P" + i.ToString()).Value = hv;
workSheet.get_Range("Q" + i.ToString(), "Q" + i.ToString()).Value = iv;
workSheet.get_Range("R" + i.ToString(), "R" + i.ToString()).Value = issueNum;
workSheet.get_Range("S" + i.ToString(), "S" + i.ToString()).Value = price;
workSheet.get_Range("T" + i.ToString(), "T" + i.ToString()).Value = financialR;
workSheet.get_Range("Y" + i.ToString(), "Y" + i.ToString()).Value = isReward;
i++;
} catch (Exception ex) {
MessageBox.Show("write" + ex.Message);
}
}
string sql2 = "SELECT [UnderlyingID] FROM [EDIS].[dbo].[ApplyOfficial] as A "
+ " left join (Select CS8010, count(1) as count from [10.19.1.20].[VOLDB].[dbo].[ED_RelationUnderlying] "
+ $" where RecordDate = (select top 1 RecordDate from [10.19.1.20].[VOLDB].[dbo].[ED_RelationUnderlying])"
+ " group by CS8010) as B on A.UnderlyingID = B.CS8010 "
+ " left join (SELECT stkid, MAX([IssueVol]) as MAX, min(IssueVol) as min FROM[10.19.1.20].[EDIS].[dbo].[WARRANTS]"
+ " where kgiwrt = '他家' and marketdate <= GETDATE() and lasttradedate >= GETDATE() and IssueVol<> 0 "
+ " group by stkid ) as C on A.UnderlyingID = C.stkid "
+ " WHERE B.count > 0 and (((IVNew > C.MAX or IVNew < C.min) and Apply1500W = 'Y') or ((IV > C.MAX or IV < C.min) and Apply1500W = 'N'))";
System.Data.DataTable badParam = MSSQL.ExecSqlQry(sql2, GlobalVar.loginSet.edisSqlConnString);
foreach (DataRow Row in badParam.Rows)
{
//WindowState = FormWindowState.Minimized;
//Show();
//WindowState = FormWindowState.Normal;
Activate();
MessageBox.Show(Row["UnderlyingID"] + " 為關係人標的，波動度超過可發範圍，會被稽核該該叫，請修改條件。");
}
GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + "產發行上傳檔");
app.Visible = false;
MessageBox.Show("發行上傳檔完成!");
}
else
{
MessageBox.Show("無可發行權證");
}
} catch (Exception ex) {
MessageBox.Show(ex.Message);
} finally {
if (workBook != null)
{
workBook.Save();
workBook.Close();
}
if (app != null)
{
app.Quit();
}
}
}