private List<InitialTrade> ParseBrockerExcelToCpTrade(string filename, ColumnMapping cMapping, int startline = 0) { var ObjExcel = new Application(); //Открываем книгу. Workbook ObjWorkBook = ObjExcel.Workbooks.Open(filename, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Выбираетам таблицу(лист). Worksheet ObjWorkSheet; if (cMapping.cTabName != null) { ObjWorkSheet = ObjWorkBook.Worksheets.Cast<Worksheet>() .FirstOrDefault(worksheet => worksheet.Name == cMapping.cTabName); } else { ObjWorkSheet = ObjWorkBook.Worksheets[1]; // .Cast<Worksheet>().FirstOrDefault(worksheet => worksheet.Name == cMapping.cTabName) } if (ObjWorkSheet != null) { // ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ObjWorkBook.Sheets[cMapping.cTabName]; Range xlRange = ObjWorkSheet.UsedRange; var tradescounter = new Dictionary<DateTime, int>(); int i = startline; if (startline == 0) i = (int) cMapping.cLineStart; var lInitTrades = new List<InitialTrade>(); int n = xlRange.Rows.Count; int add = 0; if (i != 1) { var curr = (string) xlRange.Cells[i - 1, 12].value2; if ((curr != null) && (curr.IndexOf("Place of Settlement") > -1)) add = 1; } while (i <= n) { if (xlRange.Cells[i, cMapping.cTradeDate].value2 != null) { DateTime tradeDate = getDate(cMapping.DateFormat, xlRange.Cells[i, cMapping.cTradeDate].value2); dynamic reportdate = cMapping.cReportDate != null ? getDate(cMapping.ReportDateFormat, xlRange.Cells[i, cMapping.cReportDate].value2) : tradeDate.Date; dynamic valueDate = cMapping.cValuedate != null ? getDate(cMapping.ValueDateFormat, xlRange.Cells[i, cMapping.cValuedate].value2) : null; if (cMapping.cTradeTime != null) { string crtFormat = "HH:mm:ss"; dynamic crtValue = xlRange.Cells[i, cMapping.cTradeTime].value2; if (cMapping.TimeFormat != null) { crtFormat = cMapping.TimeFormat; } if (crtFormat.Length == 6) { dynamic diffdigit = crtFormat.Length - crtValue.ToString().Length; if (diffdigit > 0) crtValue = "0" + crtValue; } dynamic time = DateFromExcelCell(crtValue, crtFormat); // : DateFromExcelCell(xlRange.Cells[i, cMapping.cTradeTime].value2, "HH:mm:ss"); var ts = new TimeSpan(time.Hour, time.Minute, time.Second); tradeDate = tradeDate.Date + ts; } double qty; if (cMapping.cQtySell == null) { qty = xlRange.Cells[i, cMapping.cQty].value2; if (cMapping.cSide != null) { dynamic side = xlRange.Cells[i, cMapping.cSide].value2; if (side != null) { side = side.ToUpper(); if ((side == "SELL") || (side == "S") || (side.Contains("ПРОДАЖА"))) qty = -Math.Abs(qty); } } } else { double qtybuy = 0; if (xlRange.Cells[i, cMapping.cQty].value2 != null) qtybuy = xlRange.Cells[i, cMapping.cQty].value2; double qtysell = 0; if (xlRange.Cells[i, cMapping.cQtySell].value2 != null) qtysell = xlRange.Cells[i, cMapping.cQtySell].value2; qty = qtybuy - qtysell; } dynamic ReportDate = reportdate; DateTime TradeDate = tradeDate; dynamic BrokerId = cMapping.cBrokerId != null ? xlRange.Cells[i, cMapping.cBrokerId].value2 : cMapping.Brocker; dynamic Symbol = Convert.ToString(xlRange.Cells[i, cMapping.cSymbol].value2); dynamic Type = cMapping.cType != null ? xlRange.Cells[i, cMapping.cType].value2 : cMapping.Type; double Qty = qty; dynamic Price = Math.Round(xlRange.Cells[i, cMapping.cPrice + add].value2, 10); dynamic ValueDate = valueDate; dynamic ExchangeFees = cMapping.cExchangeFees != null ? xlRange.Cells[i, cMapping.cExchangeFees + add].value2 : null; dynamic Fee = cMapping.cFee != null ? xlRange.Cells[i, cMapping.cFee + add].value2 : null; dynamic Fee2 = cMapping.cFee2 != null ? xlRange.Cells[i, cMapping.cFee2 + add].value2 : null; dynamic Fee3 = cMapping.cFee3 != null ? xlRange.Cells[i, cMapping.cFee3 + add].value2 : null; dynamic value = cMapping.cValue != null ? xlRange.Cells[i, cMapping.cValue + add].value2 : null; DateTime Timestamp = DateTime.UtcNow; dynamic exchangeOrderId = cMapping.cExchangeOrderId != null ? Convert.ToString(xlRange.Cells[i, cMapping.cExchangeOrderId].value2) : null; dynamic ClearingFeeCcy = cMapping.cClearingFeeCcy != null ? xlRange.Cells[i, cMapping.cClearingFeeCcy + add].value2 : null; dynamic ccy = cMapping.cCcy != null ? xlRange.Cells[i, cMapping.cCcy + add].value2 : null; dynamic ExchFeeCcy = cMapping.cExchFeeCcy != null ? xlRange.Cells[i, cMapping.cExchFeeCcy + add].value2 : null; dynamic TypeOfTrade = cMapping.cTypeOfTrade != null ? xlRange.Cells[i, cMapping.cTypeOfTrade].value2 : null; dynamic Comment = cMapping.cComment != null ? xlRange.Cells[i, cMapping.cComment].value2 : null; dynamic Strike = cMapping.cStrike != null ? xlRange.Cells[i, cMapping.cStrike].value2 : null; dynamic AccruedInterest = cMapping.cInterest != null ? xlRange.Cells[i, cMapping.cInterest].value2 : null; dynamic Account = cMapping.cAccount != null ? xlRange.Cells[i, cMapping.cAccount + add].value2 : null; dynamic TradeId = cMapping.cTradeId != null ? Convert.ToString(xlRange.Cells[i, cMapping.cTradeId + add].value2) : null; lInitTrades.Add(new InitialTrade { ReportDate = reportdate, TradeDate = tradeDate, BrokerId = cMapping.cBrokerId != null ? xlRange.Cells[i, cMapping.cBrokerId].value2 : cMapping.Brocker, Symbol = Convert.ToString(xlRange.Cells[i, cMapping.cSymbol].value2), Type = cMapping.cType != null ? xlRange.Cells[i, cMapping.cType].value2 : cMapping.Type, Qty = qty, Price = Math.Round(xlRange.Cells[i, cMapping.cPrice + add].value2, 10), ValueDate = valueDate, ExchangeFees = cMapping.cExchangeFees != null ? xlRange.Cells[i, cMapping.cExchangeFees + add].value2 : null, Fee = cMapping.cFee != null ? xlRange.Cells[i, cMapping.cFee + add].value2 : null, Fee2 = cMapping.cFee2 != null ? xlRange.Cells[i, cMapping.cFee2 + add].value2 : null, Fee3 = cMapping.cFee3 != null ? xlRange.Cells[i, cMapping.cFee3 + add].value2 : null, value = cMapping.cValue != null ? xlRange.Cells[i, cMapping.cValue + add].value2 : null, Timestamp = DateTime.UtcNow, exchangeOrderId = cMapping.cExchangeOrderId != null ? Convert.ToString(xlRange.Cells[i, cMapping.cExchangeOrderId].value2) : null, ClearingFeeCcy = cMapping.cClearingFeeCcy != null ? xlRange.Cells[i, cMapping.cClearingFeeCcy + add].value2 : null, ccy = cMapping.cCcy != null ? xlRange.Cells[i, cMapping.cCcy + add].value2 : null, ExchFeeCcy = cMapping.cExchFeeCcy != null ? xlRange.Cells[i, cMapping.cExchFeeCcy + add].value2 : null, TypeOfTrade = cMapping.cTypeOfTrade != null ? xlRange.Cells[i, cMapping.cTypeOfTrade].value2 : null, Comment = cMapping.cComment != null ? xlRange.Cells[i, cMapping.cComment].value2 : null, Strike = cMapping.cStrike != null ? xlRange.Cells[i, cMapping.cStrike].value2 : null, AccruedInterest = cMapping.cInterest != null ? xlRange.Cells[i, cMapping.cInterest].value2 : null, Account = cMapping.cAccount != null ? xlRange.Cells[i, cMapping.cAccount + add].value2 : null, TradeId = cMapping.cTradeId != null ? Convert.ToString(xlRange.Cells[i, cMapping.cTradeId + add].value2) : null }); if (tradescounter.ContainsKey(reportdate)) { tradescounter[reportdate] = tradescounter[reportdate] + 1; } else { tradescounter.Add(reportdate, 1); } } i++; } var db = new EXANTE_Entities(_currentConnection); foreach (InitialTrade initialTrade in lInitTrades) { db.InitialTrades.Add(initialTrade); } db.SaveChanges(); db.Dispose(); ObjWorkBook.Close(); ObjExcel.Quit(); Marshal.FinalReleaseComObject(ObjWorkBook); Marshal.FinalReleaseComObject(ObjExcel); LogTextBox.AppendText("\r\nTrades uploaded:"); foreach (var pair in tradescounter) { LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value); } //reportdate = tradescounter.FirstOrDefault().Key return lInitTrades; } else { ObjExcel.Quit(); Marshal.FinalReleaseComObject(ObjWorkBook); Marshal.FinalReleaseComObject(ObjExcel); return null; } }
private List<InitialTrade> ParseBrockerCsvToCpTrade(string filename, ColumnMapping cMapping) { var tradescounter = new Dictionary<DateTime, int>(); var lInitTrades = new List<InitialTrade>(); var db = new EXANTE_Entities(_currentConnection); IQueryable<counterparty> cpfromDb = from cp in db.counterparties select cp; Dictionary<string, int> cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id); var reader = new StreamReader(openFileDialog2.FileName); string lineFromFile; IQueryable<Contract> contractrow = from ct in db.Contracts where ct.valid == 1 select ct; // var contractdetails = contractrow.ToDictionary(k => k.id, k => k); int i = 1; while ((i < cMapping.cLineStart) && (!reader.EndOfStream)) { lineFromFile = reader.ReadLine(); i++; } while (!reader.EndOfStream) { lineFromFile = reader.ReadLine(); if (cMapping.Replacesymbols == "ST") { lineFromFile = lineFromFile.Replace("\"", ""); } else { lineFromFile = lineFromFile.Replace(cMapping.Replacesymbols, ""); } string[] rowstring = lineFromFile.Split(Convert.ToChar(cMapping.Delimeter)); DateTime tradeDate = cMapping.cTradeDate != null ? DateTime.ParseExact(rowstring[(int) cMapping.cTradeDate], cMapping.DateFormat, CultureInfo.CurrentCulture) : new DateTime(2011, 01, 01); DateTime reportdate = cMapping.cReportDate != null ? DateTime.ParseExact(rowstring[(int) cMapping.cReportDate], cMapping.ReportDateFormat, CultureInfo.CurrentCulture) : tradeDate; // var reportdate = DateTime.ParseExact(rowstring[(int)cMapping.cReportDate], cMapping.DateFormat, CultureInfo.CurrentCulture); if (cMapping.cTradeTime != null) { DateTime time = DateTime.ParseExact(rowstring[(int) cMapping.cTradeTime], "HH:mm:ss", CultureInfo.CurrentCulture); var ts = new TimeSpan(time.Hour, time.Minute, time.Second); tradeDate = tradeDate.Date + ts; } double qty; if (cMapping.cQtySell == null) { qty = Convert.ToDouble(rowstring[(int) cMapping.cQty]); } else { qty = Convert.ToDouble(rowstring[(int) cMapping.cQty]) - Convert.ToDouble(rowstring[(int) cMapping.cQtySell]); } if (cMapping.cSide != null) { if (rowstring[(int) cMapping.cSide] == "2") qty = -qty; if (rowstring[(int) cMapping.cSide].ToUpper() == "SELL") qty = -qty; if (rowstring[(int) cMapping.cSide].ToUpper() == "SLD") qty = -qty; if (rowstring[(int) cMapping.cSide].ToUpper() == "S") qty = -qty; } string symbol_id = rowstring[(int) cMapping.cSymbol].TrimEnd(); double price = 0; if (cMapping.cPriceSell == null) { price = Math.Round(double.Parse(rowstring[(int) cMapping.cPrice], CultureInfo.InvariantCulture), 7); } else { if (qty < 0) { price = Math.Round( double.Parse(rowstring[(int) cMapping.cPriceSell], CultureInfo.InvariantCulture), 7); } else { price = Math.Round( double.Parse(rowstring[(int) cMapping.cPrice], CultureInfo.InvariantCulture), 7); } } double? Fee; if (cMapping.cFee != null) { Fee = double.Parse(rowstring[(int) cMapping.cFee], CultureInfo.InvariantCulture); if (cMapping.cClearingFee != null) { Fee = Math.Round( (double) (Fee + double.Parse(rowstring[(int) cMapping.cClearingFee], CultureInfo.InvariantCulture)), 2); } } else { if (cMapping.cClearingFee != null) { Fee = Math.Round( double.Parse(rowstring[(int) cMapping.cClearingFee], CultureInfo.InvariantCulture), 2); } else Fee = null; } double? value; if (cMapping.cValue != null) { value = Math.Abs(double.Parse(rowstring[(int) cMapping.cValue], CultureInfo.InvariantCulture)); if (qty > 0) value = -value; } else { value = -price*qty; if (cMapping.Mty != null) { value = value*double.Parse(rowstring[(int) cMapping.Mty], CultureInfo.InvariantCulture); } value = Math.Round((double) value, 2); } //? double.Parse(rowstring[(int)cMapping.cValue], CultureInfo.InvariantCulture) * double.Parse(rowstring[(int)cMapping.Mty], CultureInfo.InvariantCulture) //: null; // var cp_id = getCPid(rowstring[idcp].Trim(), cpdic); /* if (symbol_id.Contains("PUT") || symbol_id.Contains("CALL")) { typeofInstrument = "OP"; }*/ DateTime ReportDate = reportdate; DateTime TradeDate = tradeDate; string BrokerId = cMapping.cBrokerId != null ? rowstring[(int) cMapping.cBrokerId] : cMapping.Brocker; string Symbol = symbol_id; double Qty = qty; double Price = price; DateTime? ValueDate = cMapping.cValuedate != null ? DateTime.ParseExact(rowstring[(int) cMapping.cValuedate], cMapping.ValueDateFormat, CultureInfo.CurrentCulture) : (DateTime?) null; double? ExchangeFees = cMapping.cExchangeFees != null ? double.Parse(rowstring[(int) cMapping.cExchangeFees], CultureInfo.InvariantCulture) : (double?) null; double? Fee22 = Fee; string TypeOfTrade = cMapping.cTypeOfTrade != null ? rowstring[(int) cMapping.cTypeOfTrade] : null; string Type = cMapping.cType != null ? rowstring[(int) cMapping.cType] : cMapping.Type; double? value2 = value; DateTime Timestamp = DateTime.UtcNow; string exchangeOrderId = cMapping.cExchangeOrderId != null ? Convert.ToString(rowstring[(int) cMapping.cExchangeOrderId]) : null; string Comment = cMapping.cComment != null ? rowstring[(int) cMapping.cComment] : null; string ExchFeeCcy = cMapping.cExchFeeCcy != null ? rowstring[(int) cMapping.cExchFeeCcy].TrimEnd() : null; string ClearingFeeCcy = cMapping.cClearingFeeCcy != null ? rowstring[(int) cMapping.cClearingFeeCcy].TrimEnd() : null; string ccy = cMapping.cCcy != null ? rowstring[(int) cMapping.cCcy].TrimEnd() : null; double? Strike = cMapping.cStrike != null ? double.Parse(rowstring[(int) cMapping.cStrike], CultureInfo.InvariantCulture) : (double?) null; string OptionType = cMapping.cOptionType != null ? rowstring[(int) cMapping.cOptionType].TrimEnd() : null; double? Fee2 = cMapping.cFee2 != null ? double.Parse(rowstring[(int) cMapping.cFee2], CultureInfo.InvariantCulture) : (double?) null; double? Fee3 = cMapping.cFee3 != null ? double.Parse(rowstring[(int) cMapping.cFee3], CultureInfo.InvariantCulture) : (double?) null; string test = cMapping.cAccount != null ? rowstring[(int) cMapping.cAccount] : null; lInitTrades.Add(new InitialTrade { ReportDate = reportdate, TradeDate = tradeDate, BrokerId = cMapping.cBrokerId != null ? rowstring[(int) cMapping.cBrokerId] : cMapping.Brocker, Symbol = symbol_id, Qty = qty, Price = price, ValueDate = cMapping.cValuedate != null ? DateTime.ParseExact(rowstring[(int) cMapping.cValuedate], cMapping.ValueDateFormat, CultureInfo.CurrentCulture) : (DateTime?) null, ExchangeFees = cMapping.cExchangeFees != null ? double.Parse(rowstring[(int) cMapping.cExchangeFees], CultureInfo.InvariantCulture) : (double?) null, Fee = Fee, TypeOfTrade = cMapping.cTypeOfTrade != null ? rowstring[(int) cMapping.cTypeOfTrade] : null, Type = cMapping.cType != null ? rowstring[(int) cMapping.cType] : cMapping.Type, value = value, Timestamp = DateTime.UtcNow, exchangeOrderId = cMapping.cExchangeOrderId != null ? Convert.ToString(rowstring[(int) cMapping.cExchangeOrderId]) : null, Comment = cMapping.cComment != null ? rowstring[(int) cMapping.cComment] : null, ExchFeeCcy = cMapping.cExchFeeCcy != null ? rowstring[(int) cMapping.cExchFeeCcy].TrimEnd() : null, ClearingFeeCcy = cMapping.cClearingFeeCcy != null ? rowstring[(int) cMapping.cClearingFeeCcy].TrimEnd() : null, ccy = cMapping.cCcy != null ? rowstring[(int) cMapping.cCcy].TrimEnd() : null, Strike = cMapping.cStrike != null ? double.Parse(rowstring[(int) cMapping.cStrike], CultureInfo.InvariantCulture) : (double?) null, OptionType = cMapping.cOptionType != null ? rowstring[(int) cMapping.cOptionType].TrimEnd() : null, Fee2 = cMapping.cFee2 != null ? double.Parse(rowstring[(int) cMapping.cFee2], CultureInfo.InvariantCulture) : (double?) null, Fee3 = cMapping.cFee3 != null ? double.Parse(rowstring[(int) cMapping.cFee3], CultureInfo.InvariantCulture) : (double?) null, Account = cMapping.cAccount != null ? rowstring[(int) cMapping.cAccount] : null, TradeId = cMapping.cTradeId != null ? rowstring[(int) cMapping.cTradeId] : null }); if (tradescounter.ContainsKey(reportdate)) { tradescounter[reportdate] = tradescounter[reportdate] + 1; } else { tradescounter.Add(reportdate, 1); } i++; } foreach (InitialTrade initialTrade in lInitTrades) { db.InitialTrades.Add(initialTrade); } db.SaveChanges(); db.Dispose(); LogTextBox.AppendText("\r\nTrades uploaded:"); foreach (var pair in tradescounter) { LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value); } return lInitTrades; }