/// <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 }