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