public List <string> Export(DateTime start, DateTime end, int interval, StrategyParameters parameters) { //Read the OHLC from database var connectionString = ConfigurationManager.ConnectionStrings["marketdata-local"].ConnectionString; List <OHLC> ohlcList = new List <OHLC>(); try { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("", connection)) { command.CommandType = System.Data.CommandType.Text; command.CommandText = "SELECT o.Id, o.[Open], o.High, o.Low, o.[Close], Start, Volume FROM dbo.OHLC o " + "WHERE o.[Start] >= @start AND o.[End] < @end ORDER BY o.Id ASC"; command.Parameters.Add(new SqlParameter("start", start)); command.Parameters.Add(new SqlParameter("end", end)); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var ohlc = new OHLC(); ohlc.Open = reader.GetDecimal(1); ohlc.High = reader.GetDecimal(2); ohlc.Low = reader.GetDecimal(3); ohlc.Close = reader.GetDecimal(4); ohlc.Start = reader.GetDateTime(5); var volume = reader.GetDecimal(6); ohlc.Volume = (volume * 1000); ohlcList.Add(ohlc); } } } } } catch (Exception ex) { throw ex; } //l h o c //Find the first trade //Find all trades within the next 5 minutes //If there are any trade //Calculate low, high, open, close if (ohlcList.Any()) { start = ohlcList.First().Start > start?ohlcList.First().Start : start; for (DateTime i = start; i < end; i = i.AddMinutes(interval)) { var windowStart = i; var windowEnd = windowStart.AddMinutes(interval); var ohlcInTheSameWindow = ohlcList.Where(o => o.Start >= windowStart && o.Start < windowEnd).ToList(); if (ohlcInTheSameWindow.Any()) { var low = Math.Round(ohlcInTheSameWindow.Select(t => t.Low).Min(), 2); var high = Math.Round(ohlcInTheSameWindow.Select(t => t.High).Max(), 2); var open = Math.Round(ohlcInTheSameWindow.First().Open, 2); var close = Math.Round(ohlcInTheSameWindow.Last().Close, 2); var volume = Math.Round(ohlcInTheSameWindow.Sum(t => t.Volume), 2); var ohlc = new OhlcIndicators(open, high, low, close) { Volume = volume, Start = windowStart, End = windowEnd, }; DataPoints.Add(ohlc); var prices = DataPoints.Select(d => d.Close).ToList(); ohlc.Mva10 = Math.Round(CalculateMovingAverage(prices, 10), 2); ohlc.Mva200 = Math.Round(CalculateMovingAverage(prices, 200), 2); ohlc.Rsi2 = Math.Round(CalculateRelativeStrengthIndex(prices, 3), 2); ohlc.Rsi14 = Math.Round(CalculateRelativeStrengthIndex(prices, 15), 2); var bb = CalculateBollingerBands(prices, 20, 2); ohlc.PercentB = Math.Round(bb.PercentB, 2); ohlc.Bandwidth = Math.Round(bb.Bandwidth, 2); } } } //Annotate //Calculate action - looking backward and forward //which is obviously not possible in real time trading CalculateAction(10, 4); //var balanced = OverSample(10); var normalized = Normalize(DataPoints); var exportLines = normalized.Select(d => $"{d.Start:M/d/yyyy H:mm},{d.Open},{d.High},{d.Low},{d.Close},{d.Volume},{d.Mva10},{d.Mva200},{d.Rsi2},{d.Rsi14},{d.PercentB},{d.Bandwidth},{d.Action}").ToList(); exportLines.Insert(0, "Date,Open,High,Low,Close,Volume,Mva10,Mva200,Rsi2,Rsi14,PercentB,Bandwidth,Action"); return(exportLines); }
private void CalculateAction(int dataPointCount, int profitThreshold) { //Set all actions to Hold DataPoints.ForEach(d => d.Action = "H"); var totalProfit = 0m; var lastProfit = 0m; OhlcIndicators lastSell = new OhlcIndicators(0, 0, 0, 0); for (int i = 0; i < DataPoints.Count - dataPointCount; i++) { var past = DataPoints.Skip(i).Take(dataPointCount).ToList(); var min = past.Min(d => d.Close); var future = DataPoints.Skip(i + dataPointCount).Take(dataPointCount).ToList(); var max = future.Max(d => d.Close); var profit = max - min; var profitRatio = profit / max * 100; if (profitRatio >= profitThreshold) { //Find the buy and sell dates var buy = past.First(d => d.Close == min); var sell = future.First(d => d.Close == max); //If sell is already detected then skip if (sell.Action == "S") { continue; } //If buy action is already detected //then revert the matching S back to H for a longer hold if (buy.Action == "B") { lastSell.Action = "H"; totalProfit -= lastProfit; } //If buy action is S //then it doesn't make sense to buy from the same price we sell //revert the S to H for holding longer if (buy.Action == "S") { buy.Action = "H"; } else { buy.Action = "B"; } sell.Action = "S"; lastSell = sell; lastProfit = profit; totalProfit += profit; } } Debug.WriteLine($"Total Profit: {totalProfit}"); Debug.WriteLine($"Buys: {DataPoints.Count(d => d.Action == "B")} Total: {DataPoints.Where(d => d.Action == "B").Sum(d => d.Close)}"); Debug.WriteLine($"Sells: {DataPoints.Count(d => d.Action == "S")} Total: {DataPoints.Where(d => d.Action == "S").Sum(d => d.Close)}"); var held = 0; var maxHeld = 0; var action = "B"; foreach (var dataPoint in DataPoints) { if (dataPoint.Action == "H") { continue; } if (dataPoint.Action == action) { held++; } else { held = 0; action = dataPoint.Action; } maxHeld = Math.Max(held, maxHeld); } Debug.WriteLine($"Max Held {maxHeld}"); }