//public ArrayList getWealthPCustomize(CWeight[] cweight)
public WealthPCustomize getWealthPCustomize(CWeight[] cweight)
{
//var jss = new JavaScriptSerializer();
//var results = jss.Deserialize<CWeight>(cweight);
//Dictionary<string, double> results = jss.Deserialize<Dictionary<string, double>>(cweight);
//double _lenght = results .Count;
/*
* MySql.Data.MySqlClient.MySqlConnection conn;
* string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
* conn = new MySql.Data.MySqlClient.MySqlConnection();
*/
ArrayList WealthPCustomizeArray = new ArrayList();
OleDbConnection conn = null;
OleDbCommand command = null;
OleDbDataReader mySQLReader = null;
try
{
WealthPCustomize wpc = new WealthPCustomize();
//List<string> check_port = new List<string>(new string[] { "BFIXED", "BKA", "BKA2", "PREIT&IFF", "B-GLOBAL", "BGOLD", "B-TREASURY", "B-TNTV", "BKD", "BCAP", "BBASIC", "B-INFRA", "BTK", "BTP", "B-ASEAN", "B-HY (H75) AI", "B-HY (UH) AI", "B-NIPPON", "B-BHARATA", "B-ASIA", "BCARE", "B-INNOTECH" });--"B-FUTURE","B-CHINE-EQ","BSIRICG"
//List<string> check_port = new List<string>(new string[] { "BFIXED", "BKA", "REIT", "PREIT&IFF", "B-GLOBAL", "BGOLD", "B-ASIA", "BCARE", "B-INNOTECH", "B-BHARATA", "B-TREASURY", "B-TNTV", "BCAP", "BBASIC", "BKA2", "B-INFRA", "BTK", "BTP", "B-ASEAN", "B-FUTURE", "B-CHINE-EQ", "BSIRICG" });
List <string> check_port = new List <string>(new string[] { "BFIXED", "BKA", "REIT", "PREIT&IFF", "B-GLOBAL", "BGOLD", "B-ASIA", "BCARE", "B-INNOTECH", "B-BHARATA", "B-TREASURY", "B-TNTV", "BCAP", "BBASIC", "BKA2", "B-INFRA", "BTK", "BTP", "B-ASEAN", "B-FUTURE", "B-CHINE-EQ", "BSIRICG", "BKD", "BKIND", "B-THAICG", "B-HY (H75) AI", "B-HY (UH) AI", "B-NIPPON", "B-IR-FOF" });
if (cweight.Count() == 0)
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Value can not be null.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
double check_weight = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
if (!check_port.Any(c => c.Equals(item.PortCode, StringComparison.OrdinalIgnoreCase)))
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Can not find port '" + item.PortCode + "'.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
else
{
check_weight += item.Weight;
}
}
if (check_weight != 100)
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Weight not equal 100.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
/*
* foreach (var item in cweight.OrderBy(t => t.PortCode))
* {
* if (item.Weight.Equals(null))
* {
* WealthPCustomize wpc2 = new WealthPCustomize();
* wpc2.RET = 0.0;
* wpc2.SD = 0.0;
* wpc2.XX = cweight.Count().ToString();
*
* WealthPCustomizeArray.Add(wpc2);
*
* return WealthPCustomizeArray;
* }
* }
*/
int dim = cweight.Count();
//throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
Matrix <double> A = DenseMatrix.OfArray(new double[1, dim]);
//Matrix<double> tmp = DenseMatrix.OfArray(new double[1, dim]);
double monthly_multi_asset = 0;
double multi_asset = 0;
double multi_asset_start = 0;
double multi_asset_end = 0;
double tmp = 0;
double div = 0;
Matrix <double> tmp_weight = DenseMatrix.OfArray(new double[1, dim]);
Matrix <double> tmp_index = DenseMatrix.OfArray(new double[1, dim]);
Matrix <double> tmp_index_old = DenseMatrix.OfArray(new double[1, dim]);
Matrix <double> tmp_daily = DenseMatrix.OfArray(new double[1, dim]);
DateTime iDt = new DateTime();
DateTime sDt = new DateTime();
DateTime eDt = new DateTime();
// วันทำการสิ้นเดือน เพื่อหา monthly return
List <DateTime> eMonth = new List <DateTime>();
/*eMonth.Add(new DateTime(2005, 12, 31));
* eMonth.Add(new DateTime(2006, 01, 31));
* eMonth.Add(new DateTime(2006, 02, 28));
* eMonth.Add(new DateTime(2006, 03, 31));
* eMonth.Add(new DateTime(2006, 04, 30));
* eMonth.Add(new DateTime(2006, 05, 31));
* eMonth.Add(new DateTime(2006, 06, 30));
* eMonth.Add(new DateTime(2006, 07, 31));
* eMonth.Add(new DateTime(2006, 08, 31));
* eMonth.Add(new DateTime(2006, 09, 30));
* eMonth.Add(new DateTime(2006, 10, 31));
* eMonth.Add(new DateTime(2006, 11, 30));*/
/*
* eMonth.Add(new DateTime(2006, 12, 31));
* eMonth.Add(new DateTime(2007, 01, 31));
* eMonth.Add(new DateTime(2007, 02, 28));
* eMonth.Add(new DateTime(2007, 03, 31));
* eMonth.Add(new DateTime(2007, 04, 30));
* eMonth.Add(new DateTime(2007, 05, 31));
* eMonth.Add(new DateTime(2007, 06, 30));
* eMonth.Add(new DateTime(2007, 07, 31));
* eMonth.Add(new DateTime(2007, 08, 31));
* eMonth.Add(new DateTime(2007, 09, 30));
* eMonth.Add(new DateTime(2007, 10, 31));
* eMonth.Add(new DateTime(2007, 11, 30));
* eMonth.Add(new DateTime(2007, 12, 31));
* eMonth.Add(new DateTime(2008, 01, 31));
* eMonth.Add(new DateTime(2008, 02, 29));
* eMonth.Add(new DateTime(2008, 03, 31));
* eMonth.Add(new DateTime(2008, 04, 30));
* eMonth.Add(new DateTime(2008, 05, 31));
* eMonth.Add(new DateTime(2008, 06, 30));
* eMonth.Add(new DateTime(2008, 07, 31));
* eMonth.Add(new DateTime(2008, 08, 31));
* eMonth.Add(new DateTime(2008, 09, 30));
* eMonth.Add(new DateTime(2008, 10, 31));
* eMonth.Add(new DateTime(2008, 11, 30));
* eMonth.Add(new DateTime(2008, 12, 31));
* eMonth.Add(new DateTime(2009, 01, 31));
* eMonth.Add(new DateTime(2009, 02, 28));
* eMonth.Add(new DateTime(2009, 03, 31));
* eMonth.Add(new DateTime(2009, 04, 30));
* eMonth.Add(new DateTime(2009, 05, 31));
* eMonth.Add(new DateTime(2009, 06, 30));
* eMonth.Add(new DateTime(2009, 07, 31));
* eMonth.Add(new DateTime(2009, 08, 31));
* eMonth.Add(new DateTime(2009, 09, 30));
* eMonth.Add(new DateTime(2009, 10, 31));
* eMonth.Add(new DateTime(2009, 11, 30));
* eMonth.Add(new DateTime(2009, 12, 31));
* eMonth.Add(new DateTime(2010, 01, 31));
* eMonth.Add(new DateTime(2010, 02, 28));
* eMonth.Add(new DateTime(2010, 03, 31));
* eMonth.Add(new DateTime(2010, 04, 30));
* eMonth.Add(new DateTime(2010, 05, 31));
* eMonth.Add(new DateTime(2010, 06, 30));
* eMonth.Add(new DateTime(2010, 07, 31));
* eMonth.Add(new DateTime(2010, 08, 31));
* eMonth.Add(new DateTime(2010, 09, 30));
* eMonth.Add(new DateTime(2010, 10, 31));
* eMonth.Add(new DateTime(2010, 11, 30));
*/
eMonth.Add(new DateTime(2010, 12, 31));
eMonth.Add(new DateTime(2011, 01, 31));
eMonth.Add(new DateTime(2011, 02, 28));
eMonth.Add(new DateTime(2011, 03, 31));
eMonth.Add(new DateTime(2011, 04, 30));
eMonth.Add(new DateTime(2011, 05, 31));
eMonth.Add(new DateTime(2011, 06, 30));
eMonth.Add(new DateTime(2011, 07, 31));
eMonth.Add(new DateTime(2011, 08, 31));
eMonth.Add(new DateTime(2011, 09, 30));
eMonth.Add(new DateTime(2011, 10, 31));
eMonth.Add(new DateTime(2011, 11, 30));
eMonth.Add(new DateTime(2011, 12, 31));
eMonth.Add(new DateTime(2012, 01, 31));
eMonth.Add(new DateTime(2012, 02, 29));
eMonth.Add(new DateTime(2012, 03, 31));
eMonth.Add(new DateTime(2012, 04, 30));
eMonth.Add(new DateTime(2012, 05, 31));
eMonth.Add(new DateTime(2012, 06, 30));
eMonth.Add(new DateTime(2012, 07, 31));
eMonth.Add(new DateTime(2012, 08, 31));
eMonth.Add(new DateTime(2012, 09, 30));
eMonth.Add(new DateTime(2012, 10, 31));
eMonth.Add(new DateTime(2012, 11, 30));
eMonth.Add(new DateTime(2012, 12, 31));
eMonth.Add(new DateTime(2013, 01, 31));
eMonth.Add(new DateTime(2013, 02, 28));
eMonth.Add(new DateTime(2013, 03, 31));
eMonth.Add(new DateTime(2013, 04, 30));
eMonth.Add(new DateTime(2013, 05, 31));
eMonth.Add(new DateTime(2013, 06, 30));
eMonth.Add(new DateTime(2013, 07, 31));
eMonth.Add(new DateTime(2013, 08, 31));
eMonth.Add(new DateTime(2013, 09, 30));
eMonth.Add(new DateTime(2013, 10, 31));
eMonth.Add(new DateTime(2013, 11, 30));
eMonth.Add(new DateTime(2013, 12, 31));
eMonth.Add(new DateTime(2014, 01, 31));
eMonth.Add(new DateTime(2014, 02, 28));
eMonth.Add(new DateTime(2014, 03, 31));
eMonth.Add(new DateTime(2014, 04, 30));
eMonth.Add(new DateTime(2014, 05, 31));
eMonth.Add(new DateTime(2014, 06, 30));
eMonth.Add(new DateTime(2014, 07, 31));
eMonth.Add(new DateTime(2014, 08, 31));
eMonth.Add(new DateTime(2014, 09, 30));
eMonth.Add(new DateTime(2014, 10, 31));
eMonth.Add(new DateTime(2014, 11, 30));
eMonth.Add(new DateTime(2014, 12, 31));
eMonth.Add(new DateTime(2015, 01, 31));
eMonth.Add(new DateTime(2015, 02, 28));
eMonth.Add(new DateTime(2015, 03, 31));
eMonth.Add(new DateTime(2015, 04, 30));
eMonth.Add(new DateTime(2015, 05, 31));
eMonth.Add(new DateTime(2015, 06, 30));
eMonth.Add(new DateTime(2015, 07, 31));
eMonth.Add(new DateTime(2015, 08, 31));
eMonth.Add(new DateTime(2015, 09, 30));
eMonth.Add(new DateTime(2015, 10, 31));
eMonth.Add(new DateTime(2015, 11, 30));
eMonth.Add(new DateTime(2015, 12, 31));
eMonth.Add(new DateTime(2016, 01, 31));
eMonth.Add(new DateTime(2016, 02, 29));
eMonth.Add(new DateTime(2016, 03, 31));
eMonth.Add(new DateTime(2016, 04, 30));
/*eMonth.Add(new DateTime(2015,12,31));
* eMonth.Add(new DateTime(2016, 01, 31));
* eMonth.Add(new DateTime(2016, 02, 29));
* eMonth.Add(new DateTime(2016, 03, 31));
* eMonth.Add(new DateTime(2016, 04, 30));*/
eMonth.Add(new DateTime(2016, 05, 31)); //------------Old
eMonth.Add(new DateTime(2016, 06, 30));
eMonth.Add(new DateTime(2016, 07, 31));
eMonth.Add(new DateTime(2016, 08, 31));
eMonth.Add(new DateTime(2016, 09, 30));
eMonth.Add(new DateTime(2016, 10, 31));
eMonth.Add(new DateTime(2016, 11, 30));
eMonth.Add(new DateTime(2016, 12, 31));
eMonth.Add(new DateTime(2017, 01, 31));
eMonth.Add(new DateTime(2017, 02, 28));
eMonth.Add(new DateTime(2017, 03, 31));
eMonth.Add(new DateTime(2017, 04, 30));
eMonth.Add(new DateTime(2017, 05, 31));
eMonth.Add(new DateTime(2017, 06, 30));
eMonth.Add(new DateTime(2017, 07, 31));
eMonth.Add(new DateTime(2017, 08, 31));
eMonth.Add(new DateTime(2017, 09, 30));
eMonth.Add(new DateTime(2017, 10, 31));
eMonth.Add(new DateTime(2017, 11, 30));
eMonth.Add(new DateTime(2017, 12, 31));
eMonth.Add(new DateTime(2018, 01, 31));
eMonth.Add(new DateTime(2018, 02, 28));
eMonth.Add(new DateTime(2018, 03, 31));
eMonth.Add(new DateTime(2018, 04, 30));
eMonth.Add(new DateTime(2018, 05, 31));
eMonth.Add(new DateTime(2018, 06, 30));
eMonth.Add(new DateTime(2018, 07, 31));
eMonth.Add(new DateTime(2018, 08, 31));
eMonth.Add(new DateTime(2018, 09, 30));
eMonth.Add(new DateTime(2018, 10, 31));
eMonth.Add(new DateTime(2018, 11, 30));
eMonth.Add(new DateTime(2018, 12, 28));
eMonth.Add(new DateTime(2019, 01, 31));
eMonth.Add(new DateTime(2019, 02, 28));
eMonth.Add(new DateTime(2019, 03, 31));
eMonth.Add(new DateTime(2019, 04, 30));
eMonth.Add(new DateTime(2019, 05, 31));
eMonth.Add(new DateTime(2019, 06, 30));
eMonth.Add(new DateTime(2019, 07, 31));
eMonth.Add(new DateTime(2019, 08, 31));
eMonth.Add(new DateTime(2019, 09, 30));
eMonth.Add(new DateTime(2019, 10, 31));
eMonth.Add(new DateTime(2019, 11, 30));
eMonth.Add(new DateTime(2019, 12, 30));
eMonth.Add(new DateTime(2020, 01, 31));
eMonth.Add(new DateTime(2020, 02, 29));
eMonth.Add(new DateTime(2020, 03, 31));
eMonth.Add(new DateTime(2020, 04, 30));
eMonth.Add(new DateTime(2020, 05, 31));
eMonth.Add(new DateTime(2020, 06, 30));
eMonth.Add(new DateTime(2020, 07, 31));
eMonth.Add(new DateTime(2020, 08, 31));
eMonth.Add(new DateTime(2020, 09, 30));
eMonth.Add(new DateTime(2020, 10, 31));
eMonth.Add(new DateTime(2020, 11, 30));
eMonth.Add(new DateTime(2020, 12, 30));
List <double> retList = new List <double>();
List <string> _port = new List <string>();
int i = 0;
double yr;
double pow;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
_port.Add(item.PortCode);
tmp_weight[0, i] = item.Weight / 100;
i++;
}
string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;;
conn = new OleDbConnection(myConnectionString);
conn.Open();
command = new OleDbCommand();
command.Connection = conn;
command.CommandTimeout = 0;
//-------------Return
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_WealthPFindReBalanceDate";
command.Parameters.Clear();
mySQLReader = command.ExecuteReader();
ArrayList rbDateArray = new ArrayList();
while (mySQLReader.Read())
{
//rbDateArray.Add(mySQLReader.GetDateTime(mySQLReader.GetOrdinal("RBDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")));
rbDateArray.Add(mySQLReader.GetDateTime(mySQLReader.GetOrdinal("RBDATE")));
}
mySQLReader.Close();
//-------------Return
/*
* iDt = new DateTime(2015, 12, 01);
* sDt = new DateTime(2016, 05, 31);
* eDt = new DateTime(2018, 12, 28);
*/
/*
* iDt = new DateTime(2005, 12, 01);
* sDt = new DateTime(2005, 12, 31);
*/
/*
* iDt = new DateTime(2006, 12, 01);
* sDt = new DateTime(2006, 12, 31);
*/
/*
* iDt = new DateTime(2010, 12, 01);
* sDt = new DateTime(2010, 12, 31);
*/
iDt = new DateTime(2010, 12, 01);
sDt = new DateTime(2010, 12, 31);
//eDt = new DateTime(2018, 12, 28);
//eDt = new DateTime(2019, 12, 30);
eDt = new DateTime(2020, 12, 30);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_WealthPCustomizeData";
command.Parameters.Clear();
command.Parameters.AddWithValue("@port", string.Join(",", _port));
mySQLReader = command.ExecuteReader();
/*
* while (mySQLReader.Read()) { }
* mySQLReader.Close();
* return wpc;
*/
string test = "";
DateTime vDate = new DateTime();
while (mySQLReader.Read())
{
vDate = mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE"));
if (vDate.Equals(iDt)) // if init
{
multi_asset = 100;
i = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
//_port.Add(item.PortCode);
tmp_weight[0, i] = item.Weight / 100;
tmp_index[0, i] = 100;
tmp_index_old[0, i] = 100;
i++;
}
}
else
{
i = 0;
tmp = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
tmp_index[0, i] = ((mySQLReader.GetDouble(mySQLReader.GetOrdinal(item.PortCode)) / 100) + 1) * tmp_index_old[0, i];
tmp += (mySQLReader.GetDouble(mySQLReader.GetOrdinal(item.PortCode)) * tmp_weight[0, i] / 100);
i++;
}
multi_asset = multi_asset * (1 + tmp);
i = 0;
div = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
div += tmp_weight[0, i] * tmp_index[0, i] / tmp_index_old[0, i];
i++;
}
i = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
tmp_weight[0, i] = (tmp_weight[0, i] * tmp_index[0, i] / tmp_index_old[0, i]) / div;
tmp_index_old[0, i] = tmp_index[0, i];
i++;
}
if (rbDateArray.Contains(vDate))
{
i = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
tmp_weight[0, i] = item.Weight / 100;
i++;
}
}
//--------------------------------- SD
if (eMonth.Contains(vDate))
{
//if (!mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).Equals(new DateTime(2016, 05, 31))) {
//if (!vDate.Equals(new DateTime(2005, 12, 31))) {
//if (!vDate.Equals(new DateTime(2006, 12, 31)))
if (!vDate.Equals(new DateTime(2010, 12, 31)))
{
retList.Add((multi_asset / monthly_multi_asset) - 1);
//wpc.STATUS += ((multi_asset / monthly_multi_asset) - 1).ToString() + "__";
}
monthly_multi_asset = multi_asset;
}
//--------------------------------- /SD
/*
* if (mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).Equals(new DateTime(2016, 06, 03)))
* {
* //wpc.STATUS += multi_asset + "__" + tmp_weight[0, 1].ToString();
* wpc.STATUS = tmp_index[0, 0].ToString() + "__" + tmp_index[0, 1].ToString() + "__" + tmp_index[0, 2].ToString() + "__" + tmp_index[0, 3].ToString() + "__" + multi_asset.ToString() + "__" + tmp_weight[0, 0] + "__" + tmp_weight[0, 1] + "__" + tmp_weight[0, 2] + "__" + tmp_weight[0, 3];
* }
*/
} // end if init
if (vDate.Equals(sDt))
{
multi_asset_start = multi_asset;
//test += multi_asset + "__";
}
if (vDate.Equals(eDt))
{
multi_asset_end = multi_asset;
//test += multi_asset + "__";
}
//wpc.STATUS += multi_asset + "__";
} // end while
mySQLReader.Close();
yr = eDt.Subtract(sDt).TotalDays;// / 365;
pow = 365.25 / yr;
//wpc.STATUS = yr.ToString(); //941
wpc.RET = (double)(Math.Pow((multi_asset_end / multi_asset_start), pow) - 1);
var statistics = new DescriptiveStatistics(retList);
wpc.SD = statistics.StandardDeviation * Math.Sqrt(12);
wpc.STATUS = "Success";
//wpc.STATUS = test;
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
if (mySQLReader != null)
{
mySQLReader.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
//public ArrayList getWealthPCustomize(CWeight[] cweight)
public WealthPCustomize getWealthPCustomize(CWeight[] cweight)
{
//var jss = new JavaScriptSerializer();
//var results = jss.Deserialize<CWeight>(cweight);
//Dictionary<string, double> results = jss.Deserialize<Dictionary<string, double>>(cweight);
//double _lenght = results .Count;
/*
* MySql.Data.MySqlClient.MySqlConnection conn;
* string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
* conn = new MySql.Data.MySqlClient.MySqlConnection();
*/
ArrayList WealthPCustomizeArray = new ArrayList();
OleDbConnection conn = null;
OleDbCommand command = null;
OleDbDataReader mySQLReader = null;
try
{
WealthPCustomize wpc = new WealthPCustomize();
List <string> check_port = new List <string>(new string[] { "BFIXED", "BKA", "BKA2", "REIT", "B-GLOBAL", "BGOLD", "B-TREASURY"
, "B-TNTV", "BKD", "BCAP", "BBASIC", "B-INFRA", "BTK", "BTP", "B-ASEAN"
, "B-HY (H75) AI", "B-HY (UH) AI", "B-NIPPON", "B-BHARATA", "B-ASIA", "BCARE", "B-INNOTECH" });
if (cweight.Count() == 0)
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Value can not be null.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
double check_weight = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
if (!check_port.Any(c => c.Equals(item.PortCode, StringComparison.OrdinalIgnoreCase)))
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Can not find port '" + item.PortCode + "'.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
else
{
check_weight += item.Weight;
}
}
if (check_weight != 100)
{
wpc.RET = null;
wpc.SD = null;
wpc.STATUS = "Error : Weight not equal 100.";
//wpc.XX = cweight.Count().ToString() + "_00_" + cweight.Length.ToString();
WealthPCustomizeArray.Add(wpc);
return(wpc);
}
/*
* foreach (var item in cweight.OrderBy(t => t.PortCode))
* {
* if (item.Weight.Equals(null))
* {
* WealthPCustomize wpc2 = new WealthPCustomize();
* wpc2.RET = 0.0;
* wpc2.SD = 0.0;
* wpc2.XX = cweight.Count().ToString();
*
* WealthPCustomizeArray.Add(wpc2);
*
* return WealthPCustomizeArray;
* }
* }
*/
int dim = cweight.Count();
//throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
double money = 1000000;
double tmp_money = 0;
double tmp_sd = 1;
Matrix <double> A = DenseMatrix.OfArray(new double[1, dim]);
Matrix <double> tmp = DenseMatrix.OfArray(new double[1, dim]);
DateTime sDt = new DateTime();
DateTime eDt = new DateTime();
double sd = 0;
double ret = 0;
List <double> retList = new List <double>();
List <string> _port = new List <string>();
int i = 0;
int j = 0;
double yr;
double pow;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
_port.Add(item.PortCode);
tmp[0, i] = money * item.Weight / 100;
i++;
}
/*
* Matrix<double> B = DenseMatrix.OfArray(new double[,] {
* { 20.0, 20.0, 20.0, 20.0, 20.0 }
* });
*/
/*
* conn.ConnectionString = myConnectionString;
* conn.Open();
*/
string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;;
conn = new OleDbConnection(myConnectionString);
conn.Open();
command = new OleDbCommand();
command.Connection = conn;
command.CommandTimeout = 0;
//-------------Return
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_WealthPFindReBalanceDate";
command.Parameters.Clear();
mySQLReader = command.ExecuteReader();
ArrayList rbDateArray = new ArrayList();
while (mySQLReader.Read())
{
rbDateArray.Add(mySQLReader.GetDateTime(mySQLReader.GetOrdinal("RBDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")));
}
mySQLReader.Close();
//-------------Return
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_WealthPCustomizeData";
command.Parameters.Clear();
command.Parameters.AddWithValue("@port", string.Join(",", _port));
mySQLReader = command.ExecuteReader();
/*
* MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
*
* //string sqlString = "select * from bblamapidb.azure_dividend where id=1";// tran_date = '" + dt + "'";
* string sqlString = "SP_WealthPCustomize";
* MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
* cmd.CommandType = System.Data.CommandType.StoredProcedure;
* cmd.Parameters.AddWithValue("@dt", dt);
*/
//double? double_null;
//string<T>? string_null = null;
//mySQLReader = cmd.ExecuteReader();
/*
* wpc.FUND_CODE = mySQLReader.GetValue(mySQLReader.GetOrdinal("PORT_CODE")).Equals(DBNull.Value) ? null : mySQLReader.GetString(mySQLReader.GetOrdinal("PORT_CODE"));
* wpc.UPDATED_DATE = mySQLReader.GetValue(mySQLReader.GetOrdinal("VDATE")).Equals(DBNull.Value) ? null : mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"));
*/
j = 0;
string test = "";
int jj = 0;
while (mySQLReader.Read())
{
if (j == 0)
{
sDt = mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE"));
j++;
}
else
{
eDt = mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE"));
}
jj++;
//_port2 = mySQLReader.GetValue(mySQLReader.GetOrdinal("Cov1")).Equals(DBNull.Value) ? null : mySQLReader.GetString(mySQLReader.GetOrdinal("Cov1"));
//A[i, j] = mySQLReader.GetValue(mySQLReader.GetOrdinal("Covv")).Equals(DBNull.Value) ? 0 : (double)mySQLReader.GetDecimal(mySQLReader.GetOrdinal("Covv"));
//A[i,j] = mySQLReader.GetValue(mySQLReader.GetOrdinal("Covv")).Equals(DBNull.Value) ? 0 : mySQLReader.GetDouble(mySQLReader.GetOrdinal("Covv"));
if (rbDateArray.Contains(mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"))))
{
//if(jj == 1) test = mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"));
//test = "doraemon";
money = 0;
i = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
money += tmp[0, i];
i++;
}
//reBalance
i = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
tmp[0, i] = money * item.Weight / 100;
i++;
}
/*
* i = 0;
* foreach (var item in cweight.OrderBy(t => t.PortCode))
* {
* A[0, i] = (mySQLReader.GetValue(mySQLReader.GetOrdinal(item.PortCode)).Equals(DBNull.Value) ? 0 : mySQLReader.GetDouble(mySQLReader.GetOrdinal(item.PortCode)) / 100 + 1) * tmp[0, i];
* tmp[0, i] = A[0, i];
* i++;
* }
*/
}
i = 0;
tmp_money = 0;
foreach (var item in cweight.OrderBy(t => t.PortCode))
{
//A[0, i] = (mySQLReader.GetValue(mySQLReader.GetOrdinal(item.PortCode)).Equals(DBNull.Value) ? 0 : mySQLReader.GetDouble(mySQLReader.GetOrdinal(item.PortCode)) / 100 + 1) * tmp[0, i]; //mix ใช้ได้
A[0, i] = (mySQLReader.GetValue(mySQLReader.GetOrdinal(item.PortCode)).Equals(DBNull.Value) ? 0 : mySQLReader.GetDouble(mySQLReader.GetOrdinal(item.PortCode)) + 1) * tmp[0, i]; // เดี่ยวใช้ได้
tmp[0, i] = A[0, i];
tmp_money += tmp[0, i];
i++;
}
/*
* if (rbDateArray.Contains(mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"))))
* {
* ret = tmp_money / money - 1;
* }
* else {
* ret = tmp_money / ret - 1;
* }
*/
ret = (tmp_money / money) - 1;
money = tmp_money;
//if (jj == 1) test = tmp_money.ToString();
//test = ret.ToString();
sd = (ret + 1) * tmp_sd;
tmp_sd = sd;
retList.Add(ret);
test += tmp_sd.ToString() + "==";
} // end while
mySQLReader.Close();
//-------------Return
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_FindWorkingDay";
command.Parameters.Clear();
command.Parameters.AddWithValue("@dt", sDt.ToString("dd/MM/yyy", CultureInfo.CreateSpecificCulture("en-US")));
mySQLReader = command.ExecuteReader();
while (mySQLReader.Read())
{
sDt = mySQLReader.GetDateTime(mySQLReader.GetOrdinal("DT1"));
}
mySQLReader.Close();
//-------------Return
//yr = Convert.ToInt32(Math.Floor(eDt.Subtract(sDt).TotalDays / 365));
//yr = Math.Ceiling(eDt.Subtract(sDt).TotalDays / 365);
yr = eDt.Subtract(sDt).TotalDays / 365;
pow = 1 / yr;
//yr = eDt.ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"));
var statistics = new DescriptiveStatistics(retList);
/*
* WealthPCustomize wpc = new WealthPCustomize();
* wpc.FUND_CODE = mySQLReader.GetValue(mySQLReader.GetOrdinal("PORT_CODE")).Equals(DBNull.Value) ? null : mySQLReader.GetString(mySQLReader.GetOrdinal("PORT_CODE"));
* wpc.UPDATED_DATE = mySQLReader.GetValue(mySQLReader.GetOrdinal("VDATE")).Equals(DBNull.Value) ? null : mySQLReader.GetDateTime(mySQLReader.GetOrdinal("VDATE")).ToString("yyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US"));
* wpc.SD = cc;
* WealthPCustomizeArray.Add(wpc);
*/
/*
* Matrix<double> A = DenseMatrix.OfArray(new double[,] {
* {0.00011481,0.00106950,0.00049960,-0.00019555,0.00000019},
* {0.00106950,0.02540500,0.00812000,0.00248750,0.00000287},
* {0.00049960,0.00812000,0.00375600,0.00039700,0.00000110},
* {-0.00019555,0.00248750,0.00039700,0.00228025,0.00000086},
* {0.00000019,0.00000287,0.00000110,0.00000086,0.03240000}
* });
*/
/*
* Matrix<double> Bt = B.Transpose();
* Matrix<double> C = B.Multiply(A.Multiply(Bt));
* double sd = Math.Round(Math.Sqrt(C.Trace()), 2);
*/
//WealthPCustomize wpc = new WealthPCustomize();
//wpc.SD = A[0,0];
// 1/12 = 0.08333333333333333333333333333333
/*
* wpc.RET = Math.Round((double)(Math.Pow(sd, pow) - 1 ) * 100, 2);
* wpc.SD = Math.Round(statistics.StandardDeviation * Math.Sqrt(252) * 100, 2);
*/
//wpc.RET = (double)(Math.Pow(sd, pow) - 1) ;
wpc.RET = (double)(Math.Pow(sd, pow) - 1);
wpc.SD = statistics.StandardDeviation * Math.Sqrt(252);
//var statistics2 = retList.PopulationStandardDeviation();
//wpc.SD = GetStandardDev(retList);
//wpc.XX = yr.ToString();
wpc.STATUS = "Success";
//wpc.XX = test;
//wpc.XX = pow.ToString() + "__" + sd.ToString() + "__" + yr.ToString();
//wpc.XX = retList.Average().ToString() + (retList.Sum()/62).ToString() + "___" + retList.Count.ToString() + "___" + yr.ToString();
//(long)Math.Pow(value, power)
//wpc.RET = (double)Math.Pow(2.16412, 0.1)-1 ;
//wpc.RET = A[0, 0].ToString() + "**" + A[0, 1].ToString() + "**" + A[0, 2].ToString() + "**" + A[0, 3].ToString() + "**" + A[0, 4].ToString();//money.ToString();
//wpc.RET = String.Join(",",_port);
//wpc.RET = String.Join(",", _porttest);
//wpc.FUND_CODE = string.Join(",", _port);
//wpc.FUND_CODE = _port2;
WealthPCustomizeArray.Add(wpc);
//return WealthPCustomizeArray;
return(wpc);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
if (mySQLReader != null)
{
mySQLReader.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
