/// <summary>
/// Called on each bar update event (incoming tick)
/// </summary>
/// protected override void OnStartUp()
///
protected override void OnStartUp()
{
dbConn = new MySqlConnection("server=localhost;database=algo;uid=root;password=Password1;");
try
{
dbConn.Open();
Print("Database connection established successfully...");
}
catch (MySqlException ex)
{
Print(ex.ToString());
}
MySqlCommand deleteEodTrades = new MySqlCommand("delete from eodTrades", dbConn);
deleteEodTrades.ExecuteNonQuery();
MySqlCommand deletefromOffsetPNL = new MySqlCommand("delete from offsetPNL", dbConn);
deleteEodTrades.ExecuteNonQuery();
StreamReader sr = new StreamReader(@"C:\Users\Karunyan\Documents\Reports\nn.csv");
string data = sr.ReadLine();
while (data != null)
{
//Print(data);
string[] dataArray = data.Split(',');
if (dataArray[0] != "Trade-#")
{
csvData = new TradeCsvReader()
{
csvTradeNum = Int32.Parse(dataArray[0]),
csvInstrument = (dataArray[1]),
csvAccount = (dataArray[2]),
csvStrategy = (dataArray[3]),
csvMarketPos = (dataArray[4]),
csvQuantity = Int32.Parse(dataArray[5]),
csvEntryPrice = Double.Parse(dataArray[6]),
csvExitPrice = Double.Parse(dataArray[7]),
csvEntryTime = DateTime.Parse(dataArray[8]),
csvExitTime = DateTime.Parse(dataArray[9]),
csvEntryName = (dataArray[10]),
csvExitName = (dataArray[11]),
csvProfit = Double.Parse(dataArray[12]),
csvCumProfit = Double.Parse(dataArray[13]),
csvCommission = Double.Parse(dataArray[14]),
csvMAE = Double.Parse(dataArray[15]),
csvMFE = Double.Parse(dataArray[16]),
csvETD = Double.Parse(dataArray[17]),
csvBars = Int32.Parse(dataArray[18])
};
string insertOffSetPNLQuery = (String.Format("Insert into csvtrades values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}');",
csvData.csvTradeNum, csvData.csvInstrument, csvData.csvAccount, csvData.csvStrategy, csvData.csvMarketPos, csvData.csvQuantity, csvData.csvEntryPrice, csvData.csvExitPrice, csvData.csvEntryTime,
csvData.csvExitTime, csvData.csvEntryName, csvData.csvExitName, csvData.csvProfit, csvData.csvCumProfit, csvData.csvCommission, csvData.csvMAE, csvData.csvMFE, csvData.csvETD, csvData.csvBars));
dictTradeEntries.Add(tradeIndex, insertOffSetPNLQuery);
tradeIndex++;
}
data = sr.ReadLine();
}
foreach (KeyValuePair <int, string> element in dictTradeEntries)
{
try
{
MySqlCommand insertOffsetPNLtoDB = new MySqlCommand(element.Value, dbConn);
insertOffsetPNLtoDB.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Print(ex.ToString());
}
}
using (MySqlCommand commandQueryTradeDate = new MySqlCommand(queryTradeDate, dbConn))
{
using (MySqlDataReader reader = commandQueryTradeDate.ExecuteReader())
{
while (reader.Read())
{
tradeDateStat.Add(reader.GetDateTime(0), reader.GetInt16(1));
}
}
}
foreach (KeyValuePair <DateTime, Int16> dateElement in tradeDateStat)
{
winningTradeCount = lossingTradeCount = 0;
lossingInDollar = winningInDollar = 0.00;
winningTime = lossingTime = null;
queryString = ("select left(entryTime,10),profit from csvtrades where left(entryTime,10) ='" + dateElement.Key.ToShortDateString() + "'");
Print(queryString);
using (MySqlCommand command = new MySqlCommand(queryString, dbConn))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetDouble(1) >= 0)
{
winningTradeCount++;
winningInDollar = winningInDollar + reader.GetDouble(1);
}
else
{
lossingTradeCount++;
lossingInDollar = lossingInDollar + reader.GetDouble(1);
}
pnlDate = reader.GetDateTime(0);
pnlSummation = pnlSummation + reader.GetDouble(1);
if (pnlSummation < pnlMin)
{
pnlMin = pnlSummation;
}
if (pnlSummation > pnlMax)
{
pnlMax = pnlSummation;
}
pnlClose = pnlSummation;
}
todayPNLRange = new pnlRange()
{
High = pnlMax,
Low = pnlMin,
Open = pnlOpen,
Close = pnlClose,
Trades = dateElement.Value,
WinTrades = winningTradeCount,
WinGross = Math.Round(winningInDollar, 2),
//WinAvgTime = winningTime,
LossTrades = lossingTradeCount,
LossGross = Math.Round(lossingInDollar, 2)
//LossAvgTime = lossingTime
};
}
}
string insertPNL = (String.Format("Insert into eodTrades values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
pnlDate.ToShortDateString(), todayPNLRange.High, todayPNLRange.Low, todayPNLRange.Open, todayPNLRange.Close, dateElement.Value, winningTradeCount, lossingTradeCount, winningInDollar, lossingInDollar));
insertEODPNL.Add(insertPNL);
dailyPNLRange.Add(pnlDate, todayPNLRange);
pnlOpen = pnlClose = 0;
pnlSummation = pnlMax = pnlMin = 0;
}
foreach (KeyValuePair <DateTime, pnlRange> offsetDict in dailyPNLRange)
{
if (offsetIndex == 0)
{
string insertOffSetPNLQuery = (String.Format("Insert into offsetPNL values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
offsetDict.Key.ToShortDateString(), offsetDict.Value.High, offsetDict.Value.Low, offsetDict.Value.Open, offsetDict.Value.Close, offsetDict.Value.Trades,
offsetDict.Value.WinTrades, offsetDict.Value.LossTrades, offsetDict.Value.WinGross, offsetDict.Value.LossGross));
Print(insertOffSetPNLQuery);
firstClose = offsetDict.Value.Close;
insertOffsetPNL.Add(insertOffSetPNLQuery);
}
else
{
double newOpen = offsetDict.Value.Open + firstClose;
double newHigh = offsetDict.Value.High + firstClose;
double newLow = offsetDict.Value.Low + firstClose;
double newClose = offsetDict.Value.Close + firstClose;
firstClose = newClose;
string insertOffSetPNLQuery = (String.Format("Insert into offsetPNL values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
offsetDict.Key.ToShortDateString(), newHigh, newLow, newOpen, newClose, offsetDict.Value.Trades,
offsetDict.Value.WinTrades, offsetDict.Value.LossTrades, offsetDict.Value.WinGross, offsetDict.Value.LossGross));
insertOffsetPNL.Add(insertOffSetPNLQuery);
}
offsetIndex++;
}
foreach (String element in insertOffsetPNL)
{
try
{
MySqlCommand insertOffsetPNLtoDB = new MySqlCommand(element, dbConn);
insertOffsetPNLtoDB.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Print(ex.ToString());
}
}
}
/// <summary>
/// This method is used to configure the strategy and is called once before any strategy method is called.
/// </summary>
protected override void OnStartUp()
{
#region parse csv to trade table in mysql
//===============================================================================================================
//CVS TO SQL: This section includes the program to save new csv files into the database table for processing.
//===============================================================================================================
moveFile = true;
dbConn = new MySqlConnection("server=localhost;database=algo;uid=root;password=Password1;");
dbConn.Open();
//I want to loop through a source directory, grap any new filenames and extra the csv
//trade data and save it to the database
string[] fileEntries = Directory.GetFiles(csvTradeDir);
foreach (string fileName in fileEntries)
{
//Print("Saving the following file to sql: " + fileName);
StreamReader sr = new StreamReader(Path.Combine(csvTradeDir, fileName));
string data = sr.ReadLine();
while (data != null)
{
string[] dataArray = data.Split(',');
if (dataArray[0] != "Trade-#")
{
string insertCSVData = (String.Format("Insert into kDailyPNL_csvData values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}');",
(Int32.Parse(dataArray[0]) + "." + dataArray[8] + "." + dataArray[9]), Int32.Parse(dataArray[0]), (dataArray[1]), (dataArray[2]), (dataArray[3]), (dataArray[4]), Int32.Parse(dataArray[5]), Double.Parse(dataArray[6]), Double.Parse(dataArray[7]), DateTime.Parse(dataArray[8]),
DateTime.Parse(dataArray[9]), (dataArray[10]), (dataArray[11]), Double.Parse(dataArray[12]), Double.Parse(dataArray[13]), Double.Parse(dataArray[14]), Double.Parse(dataArray[15]),
Double.Parse(dataArray[16]), Double.Parse(dataArray[17]), Int32.Parse(dataArray[18])));
//I would like to insert this part of the code into a thread so that it can save to the database in the background
//Print(insertCSVData);
try
{
MySqlCommand insertData = new MySqlCommand(insertCSVData, dbConn);
insertData.ExecuteNonQuery();
}
catch (Exception ex)
{
moveFile = false;
//Insead of not moving file, output the conflicting statement to text file.
Print(insertCSVData.ToString());
Print(ex.ToString());
}
}
data = sr.ReadLine();
}
sr.Close();
if (moveFile)
{
File.Move(fileName, Path.Combine(processedCsvTradeDir, Path.GetFileName(fileName)));
}
}
#endregion
#region calculate the daily trade range
//===============================================================================================================
//GRADE DAILY TRADES TO GENERATE THE DAYS RANGE
//===============================================================================================================
using (MySqlCommand commandQueryTradeDate = new MySqlCommand(selectTradeDates, dbConn))
{
using (MySqlDataReader reader = commandQueryTradeDate.ExecuteReader())
while (reader.Read())
{
tradeDate.Add(reader.GetDateTime(0));
}
}
foreach (DateTime data in tradeDate)
{
if (isFirstDataElement)
{
string clearOldTradeRange = "delete from kDailyPNL_dayRange";
MySqlCommand deleteOldData = new MySqlCommand(clearOldTradeRange, dbConn);
deleteOldData.ExecuteNonQuery();
string clearOldCummMData = "delete from kDailyPNL_cumm";
MySqlCommand deleteOldCummData = new MySqlCommand(clearOldCummMData, dbConn);
deleteOldData.ExecuteNonQuery();
//Print(clearOldTradeRange);
Print("Starting the daily range inserts...");
}
DateTime pnlDate;
int winningTradeCount = 0;
int lossingTradeCount = 0;
double pnlSummation = 0;
double lossingInDollar = 0;
double winningInDollar = 0;
double pnlMax = 0;
double pnlMin = 0;
double pnlOpen = 0;
double pnlClose = 0;
string insertPNL = "";
//DateTime? winningTime, lossingTime = null;
string queryString = ("select left(entryTime,10),profit from kDailyPNL_csvData where left(entryTime,10) ='" + data.ToShortDateString() + "'");
//Print(queryString);
using (MySqlCommand command = new MySqlCommand(queryString, dbConn))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetDouble(1) >= 0)
{
winningTradeCount++;
winningInDollar = winningInDollar + reader.GetDouble(1);
}
else
{
lossingTradeCount++;
lossingInDollar = lossingInDollar + reader.GetDouble(1);
}
pnlDate = reader.GetDateTime(0);
pnlSummation = pnlSummation + reader.GetDouble(1);
if (pnlSummation < pnlMin)
{
pnlMin = pnlSummation;
}
if (pnlSummation > pnlMax)
{
pnlMax = pnlSummation;
}
pnlClose = pnlSummation;
winningInDollar = Math.Round(winningInDollar, 2);
lossingInDollar = Math.Round(lossingInDollar, 2);
insertPNL = (String.Format("Insert into kDailyPNL_dayRange values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');",
pnlDate.ToShortDateString(), pnlMax, pnlMin, pnlOpen, pnlClose, winningTradeCount + lossingTradeCount,
winningTradeCount, lossingTradeCount, winningInDollar, lossingInDollar));
dailyRanges = new pnlRange()
{
High = pnlMax,
Low = pnlMin,
Open = pnlOpen,
Close = pnlClose,
WinGross = winningInDollar,
LossGross = lossingInDollar,
WinTrades = winningTradeCount,
LossTrades = lossingTradeCount,
Trades = winningTradeCount + lossingTradeCount
};
//Print(insertPNL);
}
}
}
cummPNLOffsetInput.Add(data, dailyRanges);
try
{
MySqlCommand insertDayRangeData = new MySqlCommand(insertPNL, dbConn);
insertDayRangeData.ExecuteNonQuery();
}
catch (Exception ex)
{
Print(insertPNL);
Print(ex.ToString());
}
isFirstDataElement = false;
}
#endregion
//===============================================================================================================
//GENERATE THE CUMMALATIVE PNL
//===============================================================================================================
#region calculate the cummalative pnl offset using the daily range data
//string selectTradeRangeData = "select tradedate,high,low,open,close from kDailyPNL_dayRange";
double firstClose = 0.00;
bool isFirstTradeDate = true;
string insertOffSetPNLQuery;
foreach (KeyValuePair <DateTime, pnlRange> dictData in cummPNLOffsetInput)
{
if (isFirstTradeDate)
{
Print("Inserting Cumm. Stats...");
insertOffSetPNLQuery = (String.Format("Insert into kDailyPNL_cumm values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
dictData.Key.ToShortDateString(), dictData.Value.High, dictData.Value.Low, dictData.Value.Open, dictData.Value.Close, dictData.Value.Trades,
dictData.Value.WinTrades, dictData.Value.LossTrades, dictData.Value.WinGross, dictData.Value.LossGross));
firstClose = dictData.Value.Close;
isFirstTradeDate = false;
}
else
{
double newOpen = dictData.Value.Open + firstClose;
double newHigh = dictData.Value.High + firstClose;
double newLow = dictData.Value.Low + firstClose;
double newClose = dictData.Value.Close + firstClose;
firstClose = newClose;
insertOffSetPNLQuery = (String.Format("Insert into kDailyPNL_cumm values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
dictData.Key.ToShortDateString(), newHigh, newLow, newOpen, newClose, dictData.Value.Trades,
dictData.Value.WinTrades, dictData.Value.LossTrades, dictData.Value.WinGross, dictData.Value.LossGross));
}
try
{
MySqlCommand insertDayRangeData = new MySqlCommand(insertOffSetPNLQuery, dbConn);
insertDayRangeData.ExecuteNonQuery();
}
catch (Exception ex)
{
Print(insertOffSetPNLQuery);
Print(ex.ToString());
}
}
#endregion
}