public Form1() { InitializeComponent(); ConnectionStringSettingsCollection connection = ConfigurationManager.ConnectionStrings; for (int i = 0; i < connection.Count; i++) { if (connection[i].ProviderName != "") { comboBoxEnviroment.Items.Add(connection[i].Name); if (connection[i].Name == "EXANTE_Entities") { comboBoxEnviroment.Text = "EXANTE_Entities"; } } } _currentConnection = comboBoxEnviroment.Text; var db = new EXANTE_Entities(_currentConnection); List<DBBORecon_mapping> brockerlist = (from rec in db.DBBORecon_mapping where rec.valid == 1 select rec).ToList(); foreach (DBBORecon_mapping t in brockerlist) { BrockerComboBox.Items.Add(t.NameProcess); if (t.NameProcess == "ADSS-ADSS") { BrockerComboBox.Text = "ADSS-ADSS"; _currentAcc = "ADSS-ADSS"; } } db.Dispose(); }
// public int Method(Bar bar, string propertyName) // var prop = typeof(Bar).GetProperty(propertyName); // int value = (int)prop.GetPage(bar,null); private void button3_Click(object sender, EventArgs e) { DateTime reportdate = ABNDate.Value; //todo Get report date from xml Processing date TradesParserStatus.Text = "Processing"; if (!noparsingCheckbox.Checked) { DateTime TimeStart = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start ADSS trades uploading"); reportdate = Adssparsing(); DateTime TimeEnd = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "ADSS trades uploading completed." + (TimeEnd - TimeStart).ToString()); } RecProcess(reportdate, "ADSS"); var db = new EXANTE_Entities(_currentConnection); // db.Database.ExecuteSqlCommand("UPDATE CpTrades Set value = -Qty*Price WHERE BrokerId LIKE '%adss%'"); db.Dispose(); TradesParserStatus.Text = "Done"; Console.WriteLine(""); // <-- For debugging use. */ /* var nextdate = reportDate; var startdate = new DateTime(minDate.Year, minDate.Month, minDate.Day, 0, 0, 0); var queryable = from ct in db.Ctrades where ct.Date >= startdate && ct.cp_id == "ADSS" select new { ct.ExchangeOrderId, ct.tradeNumber, ct.qty, ct.price, ct.symbol_id, ct.fullid, ct.RecStatus }; var botrades = new Dictionary<string, List<BOtrade>>(); var n = queryable.Count(); foreach (var ctrade in queryable) { var Ctrade_id = ctrade.symbol_id.Replace(".EXANTE", "") + ctrade.qty.ToString() + ctrade.price.ToString(); Ctrade_id = Ctrade_id.Replace("/", ""); var tempBotrade = new BOtrade { TradeNumber = (long)ctrade.tradeNumber, Qty = (double)ctrade.qty, Price = (double)ctrade.price, symbol = ctrade.symbol_id, ctradeid = ctrade.fullid, RecStatus = ctrade.RecStatus }; if (botrades.ContainsKey(Ctrade_id)) { botrades[Ctrade_id].Add(tempBotrade); } else botrades.Add(Ctrade_id, new List<BOtrade> { tempBotrade }); //tempBotrade}); } var recon = Reconciliation(allfromfile, botrades, "exchangeOrderId", "2"); foreach (var botrade in botrades) { foreach (var botradeItemlist in botrade.Value) { if (botradeItemlist.RecStatus) { using (var data = new EXANTE_Entities(_currentConnection)) { data.Database.ExecuteSqlCommand( "UPDATE Ctrades Set RecStatus ={0} WHERE fullid = {1}", true, botradeItemlist.ctradeid); } } } } foreach (CpTrade tradeIndex in allfromfile) { db.CpTrades.Add(tradeIndex); } db.SaveChanges(); foreach (Reconcilation reconitem in recon) { reconitem.CpTrade_id = allfromfile[(int)reconitem.CpTrade_id].FullId; db.Reconcilations.Add(reconitem); } db.SaveChanges();*/ }
private void button2_Click(object sender, EventArgs e) { if (openFileDialog1.ShowDialog() == DialogResult.OK) { //Создаём приложение. TradesParserStatus.Text = "Processing"; Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); //Открываем книгу. Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog1.FileName, 0, false, 5, "", "", false, Microsoft.Office.Interop .Excel.XlPlatform .xlWindows, "", true, false, 0, true, false, false); //Выбираем таблицу(лист). Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet; ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ObjWorkBook.Sheets["Derivative Trades_Деривативы"]; Microsoft.Office.Interop.Excel.Range xlRange = ObjWorkSheet.UsedRange; int rowCount = xlRange.Rows.Count+1; int colCount = xlRange.Columns.Count; DateTime reportdate = DateTime.FromOADate(xlRange.Cells[3, 8].value2); // reportdate = reportdate.AddDays(-1); var testexample = new EXANTE_Entities(); var nextdate = AtonDate.Value.AddDays(1); var queryable = from ct in testexample.Ctrades where ct.Date >= reportdate && ct.Date < (nextdate) && ct.cp_id == "FORTS_TR" select new {ct.trade_id, ct.tradeNumber,ct.qty, ct.price, ct.symbol_id, ct.fullid, ct.RecStatus}; var botrades = new Dictionary<string, List<BOtrade>>(); var n = queryable.Count(); foreach (var ctrade in queryable) { var ctrade_id = ctrade.trade_id.Replace("DC:F:", ""); var tempBotrade = new BOtrade { TradeNumber = (long) ctrade.tradeNumber, Qty = (double) ctrade.qty, Price = (double) ctrade.price, symbol = ctrade.symbol_id, ctradeid = ctrade.fullid, RecStatus = ctrade.RecStatus }; if (botrades.ContainsKey(ctrade_id)) { botrades[ctrade_id].Add(tempBotrade); } else botrades.Add(ctrade_id, new List<BOtrade> {tempBotrade}); //tempBotrade}); } var allfromfile = new List<CpTrade>(); for (int i = 10; i < rowCount; i++) { if (xlRange.Cells[i, 4].value2 != null) { var tradeDate = DateTime.FromOADate(xlRange.Cells[i, 4].value2); if (tradeDate.Date==reportdate.Date) { var time = DateTime.FromOADate(xlRange.Cells[i, 5].value2); var ts = new TimeSpan(time.Hour, time.Minute, time.Second); tradeDate = tradeDate.Date + ts; allfromfile.Add(new CpTrade { ReportDate = reportdate, TradeDate = tradeDate, BrokerId = "Aton", Symbol = xlRange.Cells[i, 10].value2, Type = "FUTURES", Qty = xlRange.Cells[i, 6].value2.IndexOf("Buy") == -1 ? Convert.ToInt64(xlRange.Cells[i, 11].value2)*(-1) : Convert.ToInt64(xlRange.Cells[i, 11].value2), Price = xlRange.Cells[i, 12].value2, ValueDate = null, cp_id = 2, ExchangeFees = xlRange.Cells[i, 19].value2 - xlRange.Cells[i, 16].value2, Fee = 0, Id = null, BOSymbol = null, BOTradeNumber = null, value = xlRange.Cells[i, 16].value2, Timestamp = DateTime.UtcNow, valid = 1, username = "******", // FullId = null, BOcp = null, exchangeOrderId = Convert.ToString(xlRange.Cells[i, 2].value2) }); } } } var recon = Reconciliation(allfromfile, botrades, "exchangeOrderId", "2"); foreach (var botrade in botrades){ foreach (var botradeItemlist in botrade.Value){ if (botradeItemlist.RecStatus){ using (var data = new EXANTE_Entities()){ data.Database.ExecuteSqlCommand("UPDATE Ctrades Set RecStatus ={0} WHERE fullid = {1}", true, botradeItemlist.ctradeid); } } } } foreach (CpTrade tradeIndex in allfromfile) { testexample.CpTrades.Add(tradeIndex); } testexample.SaveChanges(); foreach (Reconcilation reconitem in recon) { reconitem.CpTrade_id = allfromfile[(int) reconitem.CpTrade_id].FullId; testexample.Reconcilations.Add(reconitem); } testexample.SaveChanges(); testexample.Dispose(); ObjExcel.Quit(); TradesParserStatus.Text = "Done:"+openFileDialog1.FileName; } }
private List<FullTrade> udpateVMforaccount(List<FullTrade> listofaccountpositions, DateTime VMDate, string Brocker) { int i = 0; var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = VMDate.AddDays(1); IQueryable<FT> listtodelete = from recon in db.FT where recon.ReportDate >= VMDate.Date && recon.ReportDate < nextdate.Date && recon.Type.Contains("VM") && recon.cp.Contains(Brocker) select recon; db.FT.RemoveRange(listtodelete); SaveDBChanges(ref db); while (i < listofaccountpositions.Count) { FullTrade fullTrade = listofaccountpositions[i]; double valueccy = 0; if (fullTrade.Value == 0) { double currentAtomOfVM = getatomofVM(fullTrade.Symbol, VMDate); double priceFromDb = GetPrice(VMDate, fullTrade.Symbol); double closeAtomOfVM = Math.Round(Math.Round(currentAtomOfVM*priceFromDb, 5), 2, MidpointRounding.AwayFromZero); fullTrade.Value = Math.Round( Math.Round( fullTrade.Qty* (closeAtomOfVM - Math.Round(Math.Round(currentAtomOfVM*fullTrade.Price, 5), 2, MidpointRounding.AwayFromZero)), 5), 2, MidpointRounding.AwayFromZero); int j = i + 1; while (j < listofaccountpositions.Count) { if ((listofaccountpositions[j].Value == 0) && (listofaccountpositions[j].Symbol == fullTrade.Symbol)) { double t0 = currentAtomOfVM*listofaccountpositions[j].Price; double t1 = Math.Round(currentAtomOfVM*listofaccountpositions[j].Price, 2, MidpointRounding.AwayFromZero); double t2 = closeAtomOfVM - t1; double t3 = listofaccountpositions[j].Qty*t2; double t4 = Math.Round(t3, 2); listofaccountpositions[j].Value = Math.Round( Math.Round( listofaccountpositions[j].Qty* Math.Round( Math.Round( (closeAtomOfVM - Math.Round( Math.Round(currentAtomOfVM*listofaccountpositions[j].Price, 5), 2, MidpointRounding.AwayFromZero)), 5), 2, MidpointRounding.AwayFromZero), 5), 2, MidpointRounding.AwayFromZero); } j++; } } i++; valueccy = GetValueccy(VMDate, fullTrade.Symbol); db.FT.Add(new FT { cp = Brocker, brocker = Brocker, ReportDate = VMDate, account_id = fullTrade.Account, timestamp = DateTime.Now, symbol = fullTrade.Symbol, ccy = "RUB", value = fullTrade.Value, valid = 1, Type = "VM", User = "******", Comment = " ", Reference = null, ValueDate = VMDate, TradeDate = VMDate, BOSymbol = fullTrade.Symbol, GrossPositionIndicator = null, JOURNALACCOUNTCODE = null, ValueCCY = -Math.Round(fullTrade.Value*valueccy, 2, MidpointRounding.AwayFromZero) }); } // db.SaveChanges(); SaveDBChanges(ref db); db.Dispose(); return listofaccountpositions; }
private void TradesParser_Click(object sender, EventArgs e) { DialogResult result = openFileDialog2.ShowDialog(); if (result == DialogResult.OK) // Test result. { var reportdate = new DateTime(2011, 01, 01); var db = new EXANTE_Entities(_currentConnection); var reader = new StreamReader(openFileDialog2.FileName); var allfromFile = new List<Ctrade>(); const int GMToffset = 4; //gmt offset from BO const int nextdaystarthour = 20; //start new day for FORTS const string template = "FORTS"; DateTime nextdayvalueform = Fortsnextday.Value; string lineFromFile = reader.ReadLine(); TradesParserStatus.Text = "Processing"; DateTime TimeStart = DateTime.Now; LogTextBox.AppendText(TimeStart.ToLongTimeString() + ": " + "start BO trades uploading"); int index = 1; bool checkMalta = checkBoxMalta.Checked; if (lineFromFile != null) { string[] rowstring = lineFromFile.Split(Delimiter); int idDate = -1, idSymbol = -1, idAccount = -1, idqty = -1, idprice = -1, idside = -1, idfees = -1, iduser = -1, idcurrency = -1, idorderid = -1, idbrokerTimeDelta = -1, idexchangeOrderId = -1, idcontractMultiplier = -1, idtradeNumber = -1, idcounterparty = -1, idgateway = -1, idtradeType = -1, idSettlementCp = -1, idtradedVolume = -1, idcptime = -1, idorderPos = -1, idvalueDate = -1; for (int i = 0; i < rowstring.Length; i++) { switch (rowstring[i]) { case "gwTime": idDate = i; break; case "counterpartyTime": idcptime = i; break; case "symbolId": idSymbol = i; break; case "accountId": idAccount = i; break; case "quantity": idqty = i; break; case "price": idprice = i; break; case "side": idside = i; break; case "commission": idfees = i; break; case "userId": iduser = i; break; case "currency": idcurrency = i; break; case "tradeType": idtradeType = i; break; case "orderId": idorderid = i; break; case "brokerTimeDelta": idbrokerTimeDelta = i; break; case "orderPos": idorderPos = i; break; case "exchangeOrderId": idexchangeOrderId = i; break; case "contractMultiplier": idcontractMultiplier = i; break; case "executionCounterparty": idcounterparty = i; break; case "gatewayId": idgateway = i; break; case "valueDate": idvalueDate = i; break; case "settlementCounterparty": idSettlementCp = i; break; case "tradedVolume": idtradedVolume = i; break; default: Console.WriteLine("Additional fields in the tr.file!"); break; } } string stringindex = Convert.ToString(reportdate.Year); if (reportdate.Month < 10) stringindex = string.Concat(stringindex, "0"); stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Month)); if (reportdate.Day < 10) stringindex = string.Concat(stringindex, "0"); stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Day)); long initialindex = Convert.ToInt64(stringindex); IQueryable<Contract> contractrow = from ct in db.Contracts where ct.valid == 1 select ct; Dictionary<string, DateTime?> contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate); string currntmonth = reportdate.Year + "-" + reportdate.Month; Dictionary<string, long> checkId = (from ct in db.Ctrades where ct.BOtradeTimestamp.ToString().Contains("2016-02-12") select ct).ToDictionary(k => (k.order_id.ToString() + k.orderPos.ToString()), k => k.fullid); ; while (!reader.EndOfStream) { lineFromFile = reader.ReadLine(); if (lineFromFile == null) continue; rowstring = lineFromFile.Split(Delimiter); string id = string.Concat(rowstring[idorderid], rowstring[idorderPos]); if (!checkId.ContainsKey(id)) { DateTime? valuedate; if (!contractdetails.TryGetValue(rowstring[idSymbol], out valuedate)) { valuedate = new DateTime(2011, 01, 01); //todo fill correct value date from file var test = new Contract { id = rowstring[idSymbol], Contract1 = rowstring[idSymbol], Exchange = "Needtoupdate", Type = "Needtoupdate", Leverage = (idcontractMultiplier > (rowstring.Length - 1)) || (rowstring[idcontractMultiplier] == "") ? 1 : double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture), ValueDate = valuedate, //Convert.ToDateTime(rowstring[idvalueDate]), Currency = idcontractMultiplier > (rowstring.Length - 1) ? "USD" : rowstring[idcurrency], Margin = 0, FlatMargin = 0, Canbesettled = true, UpdateDate = DateTime.UtcNow, commission = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture)/ double.Parse(rowstring[idqty], CultureInfo.InvariantCulture), Timestamp = DateTime.UtcNow, valid = 1, username = "******" }; db.Contracts.Add(test); SaveDBChanges(ref db); contractrow = from ct in db.Contracts where ct.valid == 1 select ct; contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate); } int side = 1; if (rowstring[idside] == "sell") side = -1; DateTime vBOtradeTimestamp = Convert.ToDateTime(rowstring[idDate]); if (rowstring[idSymbol].IndexOf(template) > 0) { DateTime fortscurrentDate = Convert.ToDateTime(rowstring[idDate]); string initialdate = fortscurrentDate.ToShortDateString(); fortscurrentDate = fortscurrentDate.AddHours(24 - nextdaystarthour + GMToffset); if (initialdate != fortscurrentDate.ToShortDateString()) fortscurrentDate = nextdayvalueform; rowstring[idDate] = fortscurrentDate.ToShortDateString(); } index++; if (index > 0) { /* var ExchangeOrderId = rowstring[idexchangeOrderId]; var account_id = rowstring[idAccount]; var Date = Convert.ToDateTime(rowstring[idDate]); var symbol_id = rowstring[idSymbol]; var qty = rowstring[idqty].IndexOf(".") == -1 ? Convert.ToInt64(rowstring[idqty])*side : double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side; var price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture); var cp_id = rowstring[idcounterparty]; var fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture); var value_date = valuedate; //Convert.ToDateTime(rowstring[idvalueDate]), var currency = idcontractMultiplier > (rowstring.Length - 1) ? "USD" : rowstring[idcurrency]; var Timestamp = DateTime.UtcNow; var username = rowstring[iduser]; var order_id = rowstring[idorderid]; // var gatewayId = rowstring[idgateway]; var BOtradeTimestamp = vBOtradeTimestamp; var mty = double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture); var SettlementCp = rowstring[idSettlementCp]; var Value = double.Parse(rowstring[idtradedVolume], CultureInfo.InvariantCulture); /* var cptimestamp = rowstring[idcptime]=="" ? null : Convert.ToDateTime(rowstring[idcptime]);*/ db.Ctrades.Add(new Ctrade { ExchangeOrderId = rowstring[idexchangeOrderId], account_id = rowstring[idAccount], Date = Convert.ToDateTime(rowstring[idDate]), symbol_id = rowstring[idSymbol], qty = rowstring[idqty].IndexOf(".") == -1 ? Convert.ToInt64(rowstring[idqty])*side : double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side, price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture), cp_id = rowstring[idcounterparty], fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture), value_date = valuedate, currency = idcontractMultiplier > (rowstring.Length - 1) ? "USD" : rowstring[idcurrency], orderPos = Convert.ToInt32(rowstring[idorderPos]), Timestamp = DateTime.UtcNow, valid = 1, username = rowstring[iduser], order_id = rowstring[idorderid], // gatewayId = rowstring[idgateway], BOtradeTimestamp = vBOtradeTimestamp, tradeType = rowstring[idtradeType], SettlementCp = rowstring[idSettlementCp], Value = -side* Math.Abs(double.Parse(rowstring[idtradedVolume], CultureInfo.InvariantCulture)), mty = (Int64) double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture), deliveryDate = rowstring[idvalueDate] == "" ? Convert.ToDateTime(rowstring[idDate]) : Convert.ToDateTime(rowstring[idvalueDate]), EntityLegalMalta = checkMalta }); if (index%100 == 0) SaveDBChanges(ref db); } } else { LogTextBox.AppendText("\r\n" + "Same Id exists in BO: " + id); } } } TradesParserStatus.Text = "DB updating"; try { db.SaveChanges(); } catch (DbEntityValidationException dbEx) { foreach (DbEntityValidationResult validationErrors in dbEx.EntityValidationErrors) { foreach (DbValidationError validationError in validationErrors.ValidationErrors) { Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage); } } } db.Database.ExecuteSqlCommand("CALL updateTradeNumbers()"); db.Dispose(); TradesParserStatus.Text = "Done"; DateTime TimeEnd = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "BO trades uploading completed." + (TimeEnd - TimeStart).ToString()); LogTextBox.AppendText("\r\n" + index.ToString() + " trades have been added."); } Console.WriteLine(result); // <-- For debugging use. }
private void postTradesforDate(BOaccount acc, DateTime reportdate, bool sendFee, bool sendPL, string token, string conStr, string account, string Broker) { var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = reportdate.AddDays(1); IQueryable<CpTrade> cptradefromDb = from Cptrade in db.CpTrades where Cptrade.valid == 1 && Cptrade.BrokerId == Broker && Cptrade.ReportDate >= reportdate.Date && Cptrade.ReportDate < (nextdate.Date) //&& Cptrade.ReconAccount == null select Cptrade; List<CpTrade> cptradeitem = cptradefromDb.ToList(); int tradesqty = 0; foreach (CpTrade cpTrade in cptradeitem) { if (cpTrade.ReconAccount == null) { tradesqty = BoReconPostTrade(cpTrade, acc, conStr, token, tradesqty); if (sendFee) { BoReconPostFee(cpTrade, conStr, acc, token); } } } //json = FeeJsonfromCpTrade(cpTrade, accountnumber, "60002000000 - Exante Trading Account"); if (sendPL) { IQueryable<FT> FTfromDb = from ft in db.FT where ft.valid == 1 && ft.brocker == acc.DBcpName && ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date) && ft.account_id == acc.BOaccountId && ft.Type == "PL" select ft; List<FT> FTfromDbeitem = FTfromDb.ToList(); foreach (FT ft in FTfromDbeitem) { BoReconPostPnL(ft, conStr, acc, token); } } if (tradesqty > 0) { db.SaveChanges(); db.Dispose(); LogTextBox.AppendText("\r\n Uploaded trades for " + reportdate.ToShortDateString() + ": " + tradesqty.ToString() + "/" + cptradeitem.Count); } }
private void ParseBrockerCsvToEmir(string filename, Dictionary<string, Emir_Mapping> cMapping) { var tradescounter = new Dictionary<DateTime, int>(); var lInitTrades = new List<Emir>(); 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; int i = 1; Emir_Mapping parameters = cMapping.First().Value; while ((i < parameters.cLineStart) && (!reader.EndOfStream)) { lineFromFile = reader.ReadLine(); i++; } while (!reader.EndOfStream) { lineFromFile = reader.ReadLine(); string[] rowstring = lineFromFile.Split(Convert.ToChar(parameters.Delimeter)); DateTime cpValueDate; if (rowstring[6].Length == 4) { cpValueDate = DateTime.ParseExact(rowstring[6], "yyMM", CultureInfo.CurrentCulture); } else { cpValueDate = DateTime.ParseExact(rowstring[6], "yyyyMMdd", CultureInfo.CurrentCulture); } string map_id = rowstring[5]; if (rowstring[7] == "O") { map_id = map_id + "OP"; } map_id = map_id + cpValueDate.ToShortDateString(); Emir_Mapping map = cMapping[map_id]; var timedifference = new TimeSpan((int) map.TimeDifference, 0, 0); string Buy___Sell_Indicator = rowstring[parameters.cBuySell]; string Instrument_ID_Taxonomy = map.InstrumentIDTaxonomy; string Instrument_ID = map.InstrumentID; string Instrument_Classification = map.InstrumentClassification; string Underlying_Instrument_ID = map.InstrumentType; string Notional_Currency_1 = map.NotionalCurrency1; string Deliverable_Currency = map.DeliverableCurrency; string UTI = rowstring[24] + rowstring[25]; string MiFID_Transaction_Reference_Number = rowstring[28]; string Venue_ID = map.VenueId; double? Price___Rate = (Convert.ToDouble(rowstring[13]) + Convert.ToDouble(rowstring[12]))* map.CpMtyPrice; string Price_Notation = map.PriceNotation; string Price_Multiplier = map.PriceMultiplier.ToString(); string Notional = (map.CpMtyPrice*map.PriceMultiplier*Convert.ToDouble(rowstring[11])* (Convert.ToDouble(rowstring[12]) + Convert.ToDouble(rowstring[13]))).ToString(); string Quantity = rowstring[11]; string Delivery_Type = map.DeliveryType; DateTime Execution_Timestamp = Convert.ToDateTime(rowstring[27]) - timedifference; DateTime Effective_Date = Convert.ToDateTime(rowstring[0]); DateTime? Maturity_Date = map.MaturityDate; DateTime Confirmation_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference; DateTime Clearing_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference; string CCP_ID = parameters.cp; string Floating_Rate_Payment_Frequency = map.FloatingRatePaymentFrequency; string Floating_Rate_Reset_Frequency = map.FloatingRateResetFrequency; string Floating_Rate_Leg_2 = map.FloatingRateLeg2; string Currency_2 = map.Currency2; string Exchange_Rate_Basis = map.ExchangeRateBasis; string Commodity_Base = map.CommodityBase; string Commodity_Details = map.CommodityDetails; string Put_Call = null; string Option_Exercise_Type = null; string Strike_Price = null; string ForwardExchangeRate = null; if (map.ForwardExchangeRateMty != null) { ForwardExchangeRate = (map.ForwardExchangeRateMty*Price___Rate).ToString(); } if (map.cPutCall != null) { Put_Call = rowstring[(int) map.cPutCall]; // Option_Exercise_Type =map. Strike_Price = Convert.ToDouble(rowstring[(int) map.cStrikePrice]).ToString(); ForwardExchangeRate = (Convert.ToDouble(rowstring[(int) map.cStrikePrice])*map.ForwardExchangeRateMty).ToString(); } lInitTrades.Add(new Emir { ReportDate = Effective_Date, cp = map.Brocker, Timestamp = DateTime.Now, Common_Data_Delegated = "N", Reporting_Firm_ID = "635400MMGYK7HLRQGV31", Other_Counterparty_ID = parameters.cp, Other_Counterparty_ID_Type = "L", Reporting_Firm_Country_Code_of_Branch = "MT", Reporting_Firm_Corporate_Sector = "F", Reporting_Firm_Financial_Status = "F", Beneficiary_ID = "635400MMGYK7HLRQGV31", Beneficiary_ID_Type = "L", Trading_Capacity = "P", Buy___Sell_Indicator = rowstring[parameters.cBuySell], Counterparty_EEA_Status = "N", Instrument_ID_Taxonomy = map.InstrumentIDTaxonomy, Instrument_ID = map.InstrumentID, Instrument_Classification = map.InstrumentClassification, Underlying_Instrument_ID = map.UnderlyingInstrumentID, Underlying_Instrument_ID_Type = map.UnderlyingInstrumentIDType, Notional_Currency_1 = map.NotionalCurrency1, Deliverable_Currency = map.DeliverableCurrency, UTI = rowstring[24] + rowstring[25], MiFID_Transaction_Reference_Number = rowstring[28], Venue_ID = map.VenueId, Compression_Exercise = "N", Price___Rate = Price___Rate.ToString(), Price_Notation = map.PriceNotation, Price_Multiplier = map.PriceMultiplier.ToString(), Notional = (map.PriceMultiplier*Convert.ToDouble(rowstring[11])*Price___Rate).ToString(), Quantity = Convert.ToDouble(rowstring[11]).ToString(), Delivery_Type = map.DeliveryType, Execution_Timestamp = Convert.ToDateTime(rowstring[27]) - timedifference, Effective_Date = Convert.ToDateTime(rowstring[0]), Maturity_Date = map.MaturityDate, Confirmation_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference, Confirmation_Type = "E", Clearing_Obligation = "Y", Cleared = "Y", Clearing_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference, CCP_ID = parameters.cp, CCP_ID_Type = "L", Intragroup = "N", Floating_Rate_Payment_Frequency = map.FloatingRatePaymentFrequency, Floating_Rate_Reset_Frequency = map.FloatingRateResetFrequency, Floating_Rate_Leg_2 = map.FloatingRateLeg2, Currency_2 = map.Currency2, Forward_Exchange_Rate = ForwardExchangeRate, Exchange_Rate_Basis = map.ExchangeRateBasis, Commodity_Base = map.CommodityBase, Commodity_Details = map.CommodityDetails, Put___Call = Put_Call, Option_Exercise_Type = map.OptionExerciseType, Strike_Price = Strike_Price, Action_Type = "N", Message_Type = "T", Instrument_Description = map.InstrumentDescription, Fixed_Rate_Leg_1 = map.FixedRateLeg1.ToString(), Fixed_Rate_Day_Count = map.FixedRateDayCount, Fixed_Leg_Payment_Frequency = map.FixedLegPaymentFrequency }); if (tradescounter.ContainsKey(Effective_Date)) { tradescounter[Effective_Date] = tradescounter[Effective_Date] + 1; } else { tradescounter.Add(Effective_Date, 1); } } foreach (Emir emir in lInitTrades) { db.Emir.Add(emir); } db.SaveChanges(); db.Dispose(); LogTextBox.AppendText("\r\nTrades uploaded:"); foreach (var pair in tradescounter) { LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value); } }
private List<CpTrade> OpenConverting(List<InitialTrade> lInitTrades, string cp) { DateTime TimeStartConvert = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStartConvert.ToLongTimeString() + ": " + "start " + cp + " trades Converting"); var db = new EXANTE_Entities(_currentConnection); Dictionary<string, Map> symbolmap = getMapping(cp); Dictionary<string, string> typemap = (from ct in db.Mappings where ct.valid == 1 && ct.Brocker == cp && ct.Type == "Type" select ct).ToDictionary(k => k.BrockerSymbol, k => k.BOSymbol); var lCpTrade = new List<CpTrade>(); foreach (InitialTrade initTrade in lInitTrades) { string type = initTrade.Type; if (typemap.ContainsKey(initTrade.Type)) type = typemap[initTrade.Type]; if (initTrade.Comment != null && initTrade.Comment.Contains("REPO")) type = "REPO"; double? Price = initTrade.Price; double? Qty = initTrade.Qty; double? value = initTrade.value; DateTime? ValueDate = initTrade.ValueDate; String BOSymbol = null; if (symbolmap.ContainsKey(initTrade.Symbol + type)) { Map map = symbolmap[initTrade.Symbol + type]; BOSymbol = map.BOSymbol; Price = Price*map.MtyPrice; Qty = Qty*map.MtyVolume; value = value*map.Leverage; if (type != "FX") ValueDate = map.ValueDate; type = map.Type; } if ((Qty > 0) && (value != null)) value = -Math.Abs((double) value); double? fee = null; if (initTrade.Fee != null) fee = -Math.Abs((double) initTrade.Fee); lCpTrade.Add(new CpTrade { ReportDate = initTrade.ReportDate, TradeDate = initTrade.TradeDate, BrokerId = initTrade.BrokerId, Symbol = initTrade.Symbol, Type = type, Qty = Qty, Price = Price, ValueDate = ValueDate, cp_id = initTrade.cp_id, ExchangeFees = initTrade.ExchangeFees, Fee = fee, BOSymbol = BOSymbol, //?BOTradeNumber = value = value, Timestamp = DateTime.UtcNow, valid = 1, username = "******", //?BOcp = exchangeOrderId = initTrade.exchangeOrderId, // TypeOfTrade = initTrade.Comment.Contains("REPO")?"REPO": initTrade.TypeOfTrade, TypeOfTrade = initTrade.TypeOfTrade, Comment = initTrade.Comment, ExchFeeCcy = initTrade.ExchFeeCcy, ClearingFeeCcy = initTrade.ClearingFeeCcy, ccy = initTrade.ccy, Fee2 = initTrade.Fee2, Fee3 = initTrade.Fee3, Interest = initTrade.AccruedInterest, account = initTrade.Account } ); } db.Dispose(); DateTime TimeEnd = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + cp + " trades converting completed." + (TimeEnd - TimeStartConvert).ToString()); return lCpTrade; }
private void DEXParsing(object sender, EventArgs e) { DialogResult result = openFileDialog2.ShowDialog(); if (result == DialogResult.OK) // Test result. { var db = new EXANTE_Entities(_currentConnection); var reader = new StreamReader(openFileDialog2.FileName); var allfromfile = new List<CpTrade>(); string lineFromFile = reader.ReadLine(); if (lineFromFile != null) { while (!reader.EndOfStream && !lineFromFile.Contains("F U T U R E S / O P T I O N S C O N F I R M A T I O N S")) { lineFromFile = reader.ReadLine(); } if (!reader.EndOfStream) { lineFromFile = reader.ReadLine(); lineFromFile = reader.ReadLine(); if (lineFromFile.Contains("The following option positions have expired.")) lineFromFile = reader.ReadLine(); while (!reader.EndOfStream && !lineFromFile.Contains("Recap Of Confirm Activity") && !lineFromFile.Contains("Total Value in Base Currency") && !lineFromFile.Contains("F U T U R E S / O P T I O N S O P E N P O S I T I O N S")) { DateTime tradedate = DateTime.ParseExact(lineFromFile.Substring(0, 8).Replace(" ", "0"), "dd/MM/yy", CultureInfo.CurrentCulture); double qty = OSLExtractQty(lineFromFile); string symbol = lineFromFile.Substring(33, 32).TrimStart().TrimEnd(); string OptionType = lineFromFile.Substring(55, 1).Trim(); string OptionStrike = lineFromFile.Substring(57, 9).Trim(); string ccy = lineFromFile.Substring(94, 3); double price = Convert.ToDouble(lineFromFile.Substring(72, 6).Trim()); DateTime valuedate = DateTime.ParseExact(lineFromFile.Substring(33, 5), "MMMyy", CultureInfo.CurrentCulture); string ExchFeeCcy = ""; double ExchangeFees = 0; string ClearingFeeCcy = ""; double Fee = 0; lineFromFile = reader.ReadLine(); string vt = lineFromFile.Substring(2, 1); while (!reader.EndOfStream && !lineFromFile.Contains("COMMISSION") && !lineFromFile.Contains("TOTAL FEES") && lineFromFile.Substring(2, 1) != "/" && !lineFromFile.Contains( "F U T U R E S / O P T I O N S O P E N P O S I T I O N S")) { lineFromFile = reader.ReadLine(); } if (lineFromFile.Contains("COMMISSION")) { ExchFeeCcy = lineFromFile.Substring(94, 3).Trim(); ExchangeFees = -Convert.ToDouble(lineFromFile.Substring(103, 12).Trim()); } lineFromFile = reader.ReadLine(); while (!reader.EndOfStream && !lineFromFile.Contains("COMMISSION") && !lineFromFile.Contains("TOTAL FEES") && lineFromFile.Substring(2, 1) != "/" && !lineFromFile.Contains( "F U T U R E S / O P T I O N S O P E N P O S I T I O N S")) { lineFromFile = reader.ReadLine(); } if (lineFromFile.Contains("TOTAL FEES")) { ClearingFeeCcy = lineFromFile.Substring(94, 3).Trim(); Fee = -Convert.ToDouble(lineFromFile.Substring(103, 12).Trim()); } allfromfile.Add(new CpTrade { ReportDate = ABNDate.Value.Date, account = "DEX2565", BrokerId = "OPEN", Symbol = symbol, Qty = qty, Price = price, ccy = ccy, ValueDate = valuedate, TradeDate = tradedate, Type = (OptionType == "") ? "FU" : "OP", ExchFeeCcy = ExchFeeCcy, ExchangeFees = ExchangeFees, ClearingFeeCcy = ClearingFeeCcy, Fee = Fee, Timestamp = DateTime.Now, valid = 1, username = "******" }); if (lineFromFile.Substring(2, 1) != "/" && !lineFromFile.Contains("F U T U R E S / O P T I O N S O P E N P O S I T I O N S")) { lineFromFile = reader.ReadLine(); } } } foreach (CpTrade cpTrade in allfromfile) { db.CpTrades.Add(cpTrade); } SaveDBChanges(ref db); db.Dispose(); } } }
private void cpCostToolStripMenuItem_Click(object sender, EventArgs e) { var db = new EXANTE_Entities(_currentConnection); /*var ctradeslist = (from r in db.Ctrades where r.BOtradeTimestamp.ToString().Contains("2015-12") && r.valid == 1 select r).ToList();*/ List<cpCost_cTrade> ctradeslist = (from r in db.Ctrades where r.BOtradeTimestamp.ToString().Contains("2016-08") && r.valid == 1 select new cpCost_cTrade { symbol_id = r.symbol_id, cp_id = r.cp_id, account_id = r.account_id, fees = r.fees, currency = r.currency, qty = r.qty, tradeNumber = r.tradeNumber }).ToList(); var dCpCost = new Dictionary<string, CpCost>(); int i = 0; List<cpCost_cpTrade> allcptrades = (from cp in db.CpTrades where cp.TradeDate.ToString().Contains("2016-08") && cp.valid == 1 && cp.BOTradeNumber != null select new cpCost_cpTrade { Symbol = cp.Symbol, BrokerId = cp.BrokerId, ccy = cp.ccy, ExchFeeCcy = cp.ExchFeeCcy, ExchangeFees = cp.ExchangeFees, Fee = cp.Fee, Fee2 = cp.Fee2, Fee3 = cp.Fee3, Qty = cp.Qty, BOTradeNumber = cp.BOTradeNumber }).ToList(); int n = ctradeslist.Count; foreach (cpCost_cTrade ctrade in ctradeslist) { i++; string trnumber = ctrade.tradeNumber.ToString(); /* if (trnumber == "30123135") { var t = 1; }*/ IEnumerable<cpCost_cpTrade> cptrades = allcptrades.Where(cp => cp.BOTradeNumber.Contains(trnumber)); //.ToList(); List<cpCost_cpTrade> listcptrades = cptrades.ToList(); /*(from cp in allcptrades where cp.BOTradeNumber.Contains(ctrade.tradeNumber.ToString()) select cp).ToList(); /* allcptrades.Where() (from cp in db.CpTrades where cp.TradeDate.ToString().Contains("2015-12") && cp.BOTradeNumber.Contains(ctrade.tradeNumber.ToString()) && cp.valid == 1 select cp).ToList();*/ double ExchFee = 0, cpFee = 0, sumQty = 0; cpCost_cpTrade item = null; if (listcptrades.Count > 0) { foreach (cpCost_cpTrade trade in listcptrades) { if (trade.ExchangeFees != null) ExchFee = Math.Abs(ExchFee) + Math.Abs((double) trade.ExchangeFees); if (trade.Fee != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee); if (trade.Fee2 != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee2); if (trade.Fee3 != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee3); sumQty = sumQty + Math.Abs((double) trade.Qty); } if (sumQty != 0) { ExchFee = -(ExchFee*Math.Abs((double) ctrade.qty)/sumQty); cpFee = -(cpFee*Math.Abs((double) ctrade.qty)/sumQty); } else { ExchFee = -(ExchFee); cpFee = -(cpFee); } item = listcptrades[0]; } string id = ctrade.account_id + ctrade.symbol_id + ctrade.cp_id; CpCost ElementCpcost; if (dCpCost.TryGetValue(id, out ElementCpcost)) { ElementCpcost.BOFee = Math.Round((double) (ElementCpcost.BOFee + Math.Abs((double) ctrade.fees)), 2); ElementCpcost.CpFee = Math.Round((double) (ElementCpcost.CpFee + cpFee), 2); ElementCpcost.ExchFee = Math.Round((double) (ElementCpcost.ExchFee + ExchFee), 2); ElementCpcost.SumQty = ElementCpcost.SumQty + sumQty; ElementCpcost.NumberOfTrades = ElementCpcost.NumberOfTrades + listcptrades.Count(); if ((item != null) && (ElementCpcost.CPsymbol != null)) { ElementCpcost.CP = item.BrokerId; ElementCpcost.CpClearingCCY = item.ccy; ElementCpcost.CpExchCcy = item.ExchFeeCcy; ElementCpcost.CPsymbol = item.Symbol; ElementCpcost.NumberOfTrades = listcptrades.Count(); } } else { dCpCost.Add(id, new CpCost { Date = new DateTime(2016, 01, 1), account = ctrade.account_id, BOCcy = ctrade.currency, BOCp = ctrade.cp_id, BOFee = Math.Round(Math.Abs((double) ctrade.fees), 2), BOsymbol = ctrade.symbol_id, CP = item == null ? null : item.BrokerId, CpClearingCCY = item == null ? null : item.ccy, CpExchCcy = item == null ? null : item.ExchFeeCcy, CpFee = Math.Round(cpFee, 2), ExchFee = Math.Round(ExchFee, 2), CPsymbol = item == null ? null : item.Symbol, SumQty = sumQty, NumberOfTrades = item == null ? 0 : listcptrades.Count() }); } } foreach (var pair in dCpCost) { db.CpCost.Add(pair.Value); SaveDBChanges(ref db); } // SaveDBChanges(ref db); db.Dispose(); }
private void button8_Click_1(object sender, EventArgs e) { FORTSReconciliation("ITInvest", null); var db = new EXANTE_Entities(_currentConnection); db.Database.ExecuteSqlCommand( "UPDATE CpTrades AS cp INNER JOIN Contracts AS c ON c.id = cp.BOSymbol SET cp.value = - cp.Qty*cp.Price*c.Leverage WHERE cp.BrokerId LIKE '%ITInvest' AND ReportDate > '2016-06-01'"); db.Dispose(); }
private void button6_Click(object sender, EventArgs e) { TradesParserStatus.Text = "Processing"; updateFORTSccyrates(); calcualteVM(ABNDate.Value, "M&L"); calcualteVM(ABNDate.Value, "MOEX"); calcualteVM(ABNDate.Value, "EXANTE"); calcualteVM(ABNDate.Value, "MOEX-SPECTRA"); calcualteVM(ABNDate.Value, "OPEN"); calcualteVM(ABNDate.Value, "INSTANT"); var db = new EXANTE_Entities(_currentConnection); db.Database.ExecuteSqlCommand( "UPDATE FT Set Account_id = {0} WHERE Account_id LIKE {1} AND ReportDate = {2}", "UJL5180.INV", "UJL5180.001%", ABNDate.Value.Date); db.Dispose(); }
private void button5_Click(object sender, EventArgs e) { // const string conStr = "https://backoffice-recon.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade"; // var token = GetToken("https://authdb-recon.exante.eu/api/1.0/auth/session", "backoffice"); const string conStr = "https://backoffice.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade"; string token = GetToken("https://authdb.exante.eu/api/1.0/auth/session", "backoffice"); DateTime reportdate = ABNDate.Value; var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = reportdate.AddDays(1); var cptradefromDb = (from ft in db.FT where ft.valid == 1 && ft.brocker == "OPEN" && ft.Type == "AF" && ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date) && ft.ValueCCY != 0 && ft.Reference == null group ft by new {ft.account_id, ft.symbol, ft.ccy} into g select new { g.Key.account_id, g.Key.symbol, BOSymbol = g.Key.symbol, value = g.Sum(t => t.value), g.Key.ccy, ValueCCY = g.Sum(t => t.ValueCCY) }).ToList(); int tradesqty = 0; foreach (var VARIABLE in cptradefromDb) { var p = new FTjson(); p.operationType = "COMMISSION"; p.comment = "Additional fees from cp: " + VARIABLE.BOSymbol + " for " + reportdate.ToShortDateString(); p.asset = VARIABLE.ccy; p.symbolId = VARIABLE.BOSymbol; // p.asset = VARIABLE.counterccy; p.accountId = VARIABLE.account_id; p.amount = Math.Round((double) VARIABLE.value, 2).ToString(); p.timestamp = reportdate.ToString("yyyy-MM-dd HH:mm:ss"); string requestFTload = JsonConvert.SerializeObject(p); if (!SendJson(requestFTload, conStr + VARIABLE.account_id + "/transaction", token)) // if (!SendJson(requestFTload, conStr + "TST1149.TST" + "/transaction", token)) // if (!SendJson(requestFTload, conStr + "ZAM1452.001" + "/transaction", token)) { LogTextBox.AppendText("\r\n Error in sending Left side VM to BO for : " + VARIABLE.account_id + " " + VARIABLE.symbol); } } if (tradesqty > 0) { db.SaveChanges(); db.Dispose(); LogTextBox.AppendText("\r\n Uploaded trades for " + reportdate.ToShortDateString() + ": " + tradesqty.ToString() + "/" + cptradefromDb.Count); } }
private static string GetAccountIdFromTradeNumber(int? tradenumber) { var db = new EXANTE_Entities(_currentConnection); string accountnumber = (from ctrade in db.Ctrades where ctrade.valid == 1 && ctrade.tradeNumber == tradenumber select ctrade.account_id).ToList()[0]; db.Dispose(); return accountnumber; }
private void button3_Click_1(object sender, EventArgs e) { TradesParserStatus.Text = "Processing"; var db = new EXANTE_Entities(_currentConnection); DialogResult result = openFileDialog2.ShowDialog(); DateTime reportDate = ABNDate.Value; if (result == DialogResult.OK) { foreach (string oFilename in openFileDialog2.FileNames) { DateTime TimeUpdateBalanceStart = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeUpdateBalanceStart + ": " + "start FT Balance uploading for "); var ObjExcel = new Application(); //Открываем книгу. Workbook ObjWorkBook = ObjExcel.Workbooks.Open(oFilename, 0, false, 5, "", "", false, XlPlatform .xlWindows, "", true, false, 0, true, false, false); //Выбираем таблицу(лист). Worksheet ObjWorkSheet; ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Sheet1"]; Range xlRange = ObjWorkSheet.UsedRange; IFormatProvider theCultureInfo = new CultureInfo("en-GB", true); int jj = 1; dynamic account = xlRange.Cells[5 + jj, 2].value2.ToString(); int idReportDate = 1, idLabel = 2, idPrice = 3, idOpType = 4, idDebit = 5, idCredit = 6; string ccy = ""; ccy = xlRange.Cells[8 + jj, 2].value2; LogTextBox.AppendText(ccy); int i = 13 + jj; int index = 0; dynamic tempreportdate = xlRange.Cells[i, idReportDate].value2; if (tempreportdate != null) { reportDate = DateTime.ParseExact(xlRange.Cells[i, idReportDate].value2.ToString(), "dd/MM/yyyy", theCultureInfo); } else { reportDate = ABNDate.Value.Date; } /* var listtodelete = from ft in db.FT where ft.ccy == ccy && ft.cp == "ADSS" && reportDate.Date == ft.ReportDate select ft; db.FT.RemoveRange(listtodelete); db.SaveChanges();*/ CleanOldValue(db, ccy, "ADSS", reportDate.Date); while (xlRange.Cells[i, 1].value2 != null) { string type = ""; if (xlRange.Cells[i, idOpType].value2 == "Comm.") { type = "Commission"; } else { if (xlRange.Cells[i, idOpType].value2 == "Cash") { type = "Cash"; } else { type = xlRange.Cells[i, idLabel].value2; type = type.Substring(type.IndexOf('/') + 1, 4); if (type == "ESWP") type = "Swap"; if (type == "ADSS" && xlRange.Cells[i, idOpType].value2 == "Trade") type = "Trade"; } } // reportDate = DateTime.ParseExact(xlRange.Cells[i, idReportDate].value2.ToString(), "dd/MM/yyyy", // theCultureInfo); /* var t = xlRange.Cells[i, idCredit].Text.ToString(); t = xlRange.Cells[i, idCredit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idCredit].Text.ToString().Replace(" ", "")) : 0; var t3 = xlRange.Cells[i, idDebit].Text.ToString(); t3=t3.Replace(" ", ""); var t2 = xlRange.Cells[i, idDebit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idDebit].Text.ToString().Replace(" ", "")) : 0; t = t - t2;*/ db.FT.Add(new FT { ReportDate = reportDate.Date, cp = "ADSS", account_id = account, ccy = ccy, Type = "FT", symbol = type, value = (xlRange.Cells[i, idCredit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idCredit].Text.ToString().Replace(" ", "")) : 0) - (xlRange.Cells[i, idDebit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idDebit].Text.ToString().Replace(" ", "")) : 0), Comment = xlRange.Cells[i, idLabel].value2 + ";" + xlRange.Cells[i, idPrice].value2, timestamp = DateTime.UtcNow, valid = 1, User = "******" }); i++; SaveDBChanges(ref db); index++; } dynamic OpenCash = Convert.ToDouble(xlRange.Cells[10 + jj, 2].value2); dynamic CloseCash = Convert.ToDouble(xlRange.Cells[i + 1, 2].value2); double? OpenCashFromDb = GetCloseCashFromPrevDate(db, ccy, "ADSS"); string comment = ""; if (Math.Abs(OpenCash - OpenCashFromDb) > 0.01) { LogTextBox.AppendText("\r\n" + "Inccorect open cash for " + ccy + " " + reportDate.ToShortDateString()); comment = "Discrepancy in open cash and close cash of previous day"; } var movements = (from ft in db.FT where ft.ccy == ccy && ft.cp == "ADSS" && reportDate.Date == ft.ReportDate group ft by new {ft.symbol} into g select new { type = g.Key.symbol, Sum = g.Sum(t => t.value) }).ToList(); double sum = 0; double sumswap = 0; double sumtrade = 0; double sumfee = 0; double sumcash = 0; foreach (var movement in movements) { sum = sum + movement.Sum.Value; switch (movement.type) { case "Swap": sumswap = movement.Sum.Value; break; case "Trade": sumtrade = movement.Sum.Value; break; case "Commission": sumfee = movement.Sum.Value; break; case "Cash": sumcash = movement.Sum.Value; break; } } if (Math.Abs(CloseCash - OpenCash - sum) > 0.01) { LogTextBox.AppendText("\r\n" + "Inccorect difference between open and close cash for " + ccy + " " + reportDate.ToShortDateString()); comment = comment + ";Inccorect difference between open and close cash"; } IQueryable<ADSSCashGroupped> todelete = from ft in db.ADSSCashGroupped where ft.Currency == ccy && reportDate.Date == ft.ReportDate && ft.Cp == "ADSS" select ft; db.ADSSCashGroupped.RemoveRange(todelete); db.SaveChanges(); db.ADSSCashGroupped.Add(new ADSSCashGroupped { ClosingCash = Math.Round(CloseCash, 2), Commission = Math.Round(sumfee, 2), Currency = ccy, Deposit = Math.Round(sumcash, 2), OpeningCash = OpenCash, ReportDate = reportDate.Date, SWAPs = Math.Round(sumswap, 2), Trades = Math.Round(sumtrade, 2), comment = comment, Cp = "ADSS" }); SaveDBChanges(ref db); DateTime TimeFutureParsing = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeFutureParsing.ToLongTimeString() + ": " + "FT parsing completed for " + ccy + ":" + oFilename + "." + "\r\n" + index + " items have been uploaded. Time: " + (TimeFutureParsing - TimeUpdateBalanceStart).ToString() + "s"); ObjWorkBook.Close(); ObjExcel.Quit(); Marshal.FinalReleaseComObject(ObjWorkBook); Marshal.FinalReleaseComObject(ObjExcel); } } AddCcyFromPreviousReports(db, "ADSS"); SaveDBChanges(ref db); db.Dispose(); }
private double? GetVM(DateTime vmDate, string brocker) { var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = vmDate.AddDays(1); double? sum = db.FT.Where( o => (o.ReportDate >= vmDate.Date && o.ReportDate < nextdate.Date && o.valid == 1 && o.brocker == brocker)) .Sum(o => o.value); db.Dispose(); return sum; }
private List<CpTrade> InitTradesConverting(List<InitialTrade> lInitTrades, string cp, bool checkIdflag = false, string checkIdCp = "") { DateTime TimeStartConvert = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStartConvert.ToLongTimeString() + ": " + "start " + cp + " trades Converting"); var db = new EXANTE_Entities(_currentConnection); Dictionary<string, Map> symbolmap = getMapping(cp); var lCpTrade = new List<CpTrade>(); Dictionary<string, long> checkId = null; if (checkIdflag) { checkId = (from ct in db.CpTrades where ct.TradeDate.ToString().Contains("2016-0") && ct.BrokerId == checkIdCp select ct).ToDictionary(k => k.exchangeOrderId, k => k.FullId); } foreach (InitialTrade initTrade in lInitTrades) { string type = "FU"; if (initTrade.Type == "O") type = "OP"; double? Price = initTrade.Price; double? Qty = initTrade.Qty; double? value = initTrade.value; DateTime? ValueDate = initTrade.ValueDate; if (ValueDate == null) ValueDate = new DateTime(2011, 01, 01); String BOSymbol = null; string key = initTrade.Symbol + type; // +ValueDate.Value.ToShortDateString(); if (symbolmap.ContainsKey(key)) { Map map = symbolmap[key]; BOSymbol = map.BOSymbol; Price = Price*map.MtyPrice; Qty = Qty*map.MtyVolume; value = value*map.Leverage; if (type == "OP") { BOSymbol = BOSymbol + "."; if (map.UseDayInTicker == true) { BOSymbol = BOSymbol + initTrade.ValueDate.Value.Day.ToString(); } if (map.MtyStrike == null) map.MtyStrike = 1; BOSymbol = BOSymbol + getLetterOfMonth(initTrade.ValueDate.Value.Month) + initTrade.ValueDate.Value.Year + "." + initTrade.OptionType + (initTrade.Strike*map.MtyStrike).ToString(); } else { if (map.calendar == 1) { BOSymbol = BOSymbol + "." + getLetterOfMonth(initTrade.ValueDate.Value.Month) + initTrade.ValueDate.Value.Year; } } } if (checkIdflag) { if (!checkId.ContainsKey(initTrade.exchangeOrderId)) { lCpTrade.Add(new CpTrade { ReportDate = initTrade.ReportDate, TradeDate = initTrade.TradeDate, BrokerId = initTrade.BrokerId, Symbol = initTrade.Symbol, Type = type, Qty = Qty, Price = Price, ValueDate = ValueDate, cp_id = initTrade.cp_id, ExchangeFees = initTrade.ExchangeFees, Fee = initTrade.Fee, BOSymbol = BOSymbol, value = value, Timestamp = DateTime.UtcNow, valid = 1, username = "******", exchangeOrderId = initTrade.exchangeOrderId, TypeOfTrade = initTrade.TypeOfTrade, Comment = initTrade.Comment, ExchFeeCcy = initTrade.ExchFeeCcy, ClearingFeeCcy = initTrade.ClearingFeeCcy, ccy = initTrade.ccy, account = initTrade.Account, TradeId = initTrade.TradeId }); } } else { lCpTrade.Add(new CpTrade { ReportDate = initTrade.ReportDate, TradeDate = initTrade.TradeDate, BrokerId = initTrade.BrokerId, Symbol = initTrade.Symbol, Type = type, Qty = Qty, Price = Price, ValueDate = ValueDate, cp_id = initTrade.cp_id, ExchangeFees = initTrade.ExchangeFees, Fee = initTrade.Fee, BOSymbol = BOSymbol, value = value, Timestamp = DateTime.UtcNow, valid = 1, username = "******", exchangeOrderId = initTrade.exchangeOrderId, TypeOfTrade = initTrade.TypeOfTrade, Comment = initTrade.Comment, ExchFeeCcy = initTrade.ExchFeeCcy, ClearingFeeCcy = initTrade.ClearingFeeCcy, ccy = initTrade.ccy, account = initTrade.Account, TradeId = initTrade.TradeId }); } } db.Dispose(); DateTime TimeEnd = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + cp + " trades converting completed." + (TimeEnd - TimeStartConvert).ToString()); return lCpTrade; }
private static Dictionary<string, int> Getlistofccy_modified(string txt, ref List<string> ccy) { int lastindexofstar = txt.IndexOf('*'); var listofccy = new Dictionary<string, int>(); while (lastindexofstar > -1) { int endstar = txt.IndexOf("*", lastindexofstar + 1); string cnt_ccy = txt.Substring(lastindexofstar + 1, endstar - lastindexofstar - 1).TrimStart().TrimEnd(); listofccy.Add(txt.Substring(lastindexofstar + 1, endstar - lastindexofstar - 1).TrimStart().TrimEnd(), endstar + 1); string match = ccy.FirstOrDefault(stringToCheck => stringToCheck.Contains(cnt_ccy)); if (match == null) { ccy.Add(cnt_ccy); var db = new EXANTE_Entities(_currentConnection); db.RJO_listccy.Add(new RJO_listccy {Ccy = cnt_ccy, valid = 1}); SaveDBChanges(ref db); db.Dispose(); } lastindexofstar = txt.IndexOf("*", endstar + 1); } return listofccy; }
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; }
private double getatomofVM(string symbol, DateTime VMDate) { var db = new EXANTE_Entities(_currentConnection); double atomvalue = 0; int indexofOption = CustomIndexOf(symbol, '.', 3); string key = symbol; if (indexofOption > 0) { key = symbol.Substring(0, indexofOption + 1); } List<Mapping> map = (from ct in db.Mappings where ct.valid == 1 && ct.Brocker == "OPEN" && ct.Type == "FORTS" && ct.BOSymbol == key select ct).ToList(); if (map.Count == 1) { atomvalue = (double) (map[0].MtyPrice/map[0].MtyVolume); if (map[0].Round == 1) { IQueryable<Price> ccyrateFromDblinq = (from ct in db.Prices where ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") && ct.Date == VMDate.Date select ct); double ccyrateFromDb = 0; if (!ccyrateFromDblinq.Any()) { // updateFORTSccyrates(VMDate.ToString("dd.MM.yyyy")); ccyrateFromDb = (double) (from ct in db.Prices where ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") && ct.Date == VMDate.Date select ct).ToList()[0].Price1; } else { ccyrateFromDb = (double) ccyrateFromDblinq.ToList()[0].Price1; } atomvalue = Math.Round((atomvalue*ccyrateFromDb), 5, MidpointRounding.AwayFromZero); } } db.Dispose(); return atomvalue; }
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<FullTrade> Getlistofaccountposition(DateTime fortsDate, string Brocker) { var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = fortsDate.AddDays(1); IQueryable<FullTrade> positionbefore = from ct in db.Ctrades where ct.valid == 1 && ct.Date < fortsDate.Date && ct.symbol_id.Contains("FORTS") && ct.cp_id == Brocker group ct by new { ct.account_id, ct.symbol_id } into g where g.Sum(x => x.qty) != 0 select new FullTrade { Account = g.Key.account_id, Symbol = g.Key.symbol_id, Qty = (double) g.Sum(x => x.qty), Price = 0, Value = 0 }; // var listofTrades = positionbefore.ToList(); var listofTrades = new List<FullTrade>(positionbefore.ToList()); foreach (FullTrade listofTrade in listofTrades) { listofTrade.Price = GetFortsPrices(fortsDate, listofTrade.Symbol); } IQueryable<FullTrade> tradesToday = from ct in db.Ctrades where ct.valid == 1 && ct.Date < nextdate.Date && ct.Date >= fortsDate.Date && ct.symbol_id.Contains("FORTS") && ct.cp_id == Brocker select new FullTrade { Account = ct.account_id, Symbol = ct.symbol_id, Qty = (double) ct.qty, Price = (double) ct.price, Value = 0 }; listofTrades.AddRange(tradesToday.ToList()); db.Dispose(); return listofTrades; }
private void RecProcess(DateTime reportdate, string ccp) { DateTime TimeStart = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStart + ": " + "start " + ccp + " reconciliation"); var db = new EXANTE_Entities(_currentConnection); Dictionary<string, Map> symbolmap = getMap(ccp); DateTime nextdate = reportdate.AddDays(1); IQueryable<CpTrade> cptradefromDb = from cptrade in db.CpTrades where cptrade.valid == 1 && cptrade.BrokerId == ccp && cptrade.ReportDate >= reportdate.Date && cptrade.ReportDate < (nextdate.Date) && cptrade.BOTradeNumber == null select cptrade; if (ccp == "ABN") cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "01"); //.Contains(o.StatusCode)) if (ccp == "Mac") cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "A"); if (ccp == "CFH") cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "OnlineTrade"); //var filteredOrders = orders.Order.Where(o => allowedStatus.Contains(o.StatusCode)); List<CpTrade> cptradelist = cptradefromDb.ToList(); foreach (CpTrade cpTrade in cptradelist) { if (cpTrade.BOSymbol == null) { Map symbolvalue; string key = cpTrade.Symbol + cpTrade.Type; if (cpTrade.Type == "FU") { if (cpTrade.ValueDate != null) key = key + cpTrade.ValueDate.Value.ToShortDateString(); } if (symbolmap.TryGetValue(key, out symbolvalue)) { cpTrade.BOSymbol = symbolvalue.BOSymbol; cpTrade.Qty = cpTrade.Qty*symbolvalue.MtyVolume; cpTrade.Price = cpTrade.Price*symbolvalue.MtyPrice; } db.CpTrades.Attach(cpTrade); db.Entry(cpTrade).State = (EntityState)System.Data.Entity.EntityState.Modified; } } SaveDBChanges(ref db); db.Dispose(); DateTime TimeStartReconciliation = DateTime.Now; AbnRecon(reportdate, cptradelist, ccp); DateTime TimeEndReconciliation = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEndReconciliation.ToLongTimeString() + ": " + "Reconciliation completed. Time:" + (TimeStartReconciliation - TimeEndReconciliation).ToString() + "s"); }
private double GetPrice(DateTime VMDate, string symbol) { var db = new EXANTE_Entities(_currentConnection); IQueryable<Price> pricelinq = from ct in db.Prices where ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker == symbol && ct.Date == VMDate.Date select ct; if (pricelinq.Any()) { var returnvalue = (double) pricelinq.ToList()[0].Price1; db.Dispose(); return returnvalue; } else { db.Dispose(); return UpdateFortsPrices(VMDate, symbol); } }
private static double GetValueccy(DateTime VMDate, string symbol) { var db = new EXANTE_Entities(_currentConnection); int indexofOption = CustomIndexOf(symbol, '.', 3); string key = ""; if (indexofOption > 0) { key = symbol.Substring(0, indexofOption) + "."; } else key = symbol; List<int?> map = (from ct in db.Mappings where ct.valid == 1 && ct.Brocker == "OPEN" && ct.Type == "FORTS" && ct.BOSymbol == key select ct.Round).ToList(); if ((map.Count > 0) && (map[0] == 1)) { double? ccyrateFromDblinq = (from ct in db.Prices where ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") && ct.Date == VMDate.Date select ct.Price1).ToList()[0]; db.Dispose(); return (double) (1/ccyrateFromDblinq); } else { db.Dispose(); return 0; } }
private void GetRowBalance() { DialogResult result = openFileDialog2.ShowDialog(); if (result == DialogResult.OK) // Test result. { DateTime TimeStart = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start OPEN Balance uploading"); var db = new EXANTE_Entities(_currentConnection); var ObjExcel = new Application(); //Открываем книгу. Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog2.FileName, 0, false, 5, "", "", false, XlPlatform .xlWindows, "", true, false, 0, true, false, false); //Выбираем таблицу(лист). Worksheet ObjWorkSheet; ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Portfolio evaluation"]; Range xlRange = ObjWorkSheet.UsedRange; string account = xlRange.Cells[11, 8].value2; if (account == null) account = xlRange.Cells[12, 7].value2; dynamic ccy = xlRange.Cells[14, 8].value2; if (ccy == null) ccy = xlRange.Cells[15, 7].value2; ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Cash flow"]; xlRange = ObjWorkSheet.UsedRange; var reportdate = (DateTime) DateFromExcelCell(xlRange.Cells[3, 1].value2, "dd.MM.yyyy"); RemoveRecordFromRowBalance(db, reportdate, "Open", account); GetCashFlowOSL(ObjWorkBook, db, account, reportdate); GetPortfolioOSL(ObjWorkBook, db, reportdate, account, ccy); GetOSLBalanceData("Gross amount of non-settled trades", ObjWorkBook, ref xlRange, db, ccy, reportdate, account); GetOSLBalanceData("Planned brokerage commission", ObjWorkBook, ref xlRange, db, ccy, reportdate, account); GetOSLBalanceData("Other planned fees", ObjWorkBook, ref xlRange, db, ccy, reportdate, account); PutNAVOSL(ObjWorkBook, ref xlRange, db, ccy, reportdate, account); // ObjectParameter qty = new ObjectParameter("Name", typeof(Int16)); /* var idParam = new SqlParameter {ParameterName = "cp",Value = "OPEN"}; var CountParam = new SqlParameter { ParameterName = "number", Value = 0, Direction = ParameterDirection.Output }; mSqlCmdInsertCustomers.Parameters.Clear(); mSqlCmdInsertCustomers.Parameters.AddWithValue("param1", "value1"); mSqlCmdInsertCustomers.Parameters.AddWithValue("param2", "value2"); . . . mSqlCmdInsertCustomers.Parameters.AddWithValue("paramN", "valueN");*/ //var t= db. // var results = db.Database.SqlQuery<int>("exec CheckMappingBalance @cp, @number out", idParam, CountParam); // db.call // remove comments var results = db.Database.ExecuteSqlCommand("exec CheckMappingBalance @cp, @number out", idParam, CountParam); //var person = results; // remove comments var votes = (int)CountParam.Value; /* var date = new SqlParameter("@date", _msg.MDate); var subject = new SqlParameter("@subject", _msg.MSubject); var body = new SqlParameter("@body", _msg.MBody); var fid = new SqlParameter("@fid", _msg.FID); this.Database.ExecuteSqlCommand("exec messageinsert @Date , @Subject , @Body , @Fid", date, subject, body, fid); */ // db.Database.SqlQuery<int>("CheckMappingBalance", name).SingleOrDefault(); //to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset //var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault(); //context.GetDepartmentName(, name); // Console.WriteLine(name.Value); SaveDBChanges(ref db); db.Dispose(); ObjWorkBook.Close(); ObjExcel.Quit(); Marshal.FinalReleaseComObject(ObjWorkBook); Marshal.FinalReleaseComObject(ObjExcel); DateTime TimeEnd = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "OPEN Balance Completed for+" + reportdate.ToShortDateString() + openFileDialog2.FileName); } }
private void updateFORTSccyrates() { DateTime TimeStart = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeStart + ": " + "Getting ccy prices from MOEX"); string Date = ABNDate.Value.ToString("yyyy-MM-dd"); // const string initialstring = "http://moex.com/ru/derivatives/currency-rate.aspx?currency="; const string initialstring = "http://moex.com/export/derivatives/currency-rate.aspx?language=ru¤cy="; // http://moex.com/export/derivatives/currency-rate.aspx?language=ru¤cy=USD/RUB&moment_start=2014-07-24&moment_end=2014-07-24 var listccy = new List<string>(); listccy.Add("USD/RUB"); listccy.Add("EUR/RUB"); var db = new EXANTE_Entities(_currentConnection); foreach (string ccy in listccy) { string ccystring = initialstring + ccy + "&moment_start=" + Date + "&Date&moment_end=" + Date; var doc = new XmlDocument(); doc.Load(ccystring); XmlNode upnode = doc.SelectSingleNode("rtsdata"); string temp = ""; if (upnode != null) { temp = upnode.SelectSingleNode("rates").FirstChild.Attributes[1].Value; } db.Prices.Add(new Price { Ticker = ccy.Replace("/", ""), Tenor = DateTime.ParseExact(Date, "yyyy-MM-dd", CultureInfo.InvariantCulture), Price1 = Convert.ToDouble(temp), Date = DateTime.ParseExact(Date, "yyyy-MM-dd", CultureInfo.InvariantCulture), Type = "FORTS", Timestamp = DateTime.Now, Valid = 1, Username = "******" }); } SaveDBChanges(ref db); db.Dispose(); DateTime TimeEndUpdating = DateTime.Now; LogTextBox.AppendText("\r\n" + TimeEndUpdating + ": " + "CCY FORTS rates for " + Date + " uploaded. Time:" + (TimeEndUpdating - TimeStart).ToString()); }
private string GetSymbolRJO(Dictionary<string, Map> symbolmap, string symbol_id, ref double? MtyVolume, Dictionary<string, Contract> contractdetails, ref double? MtyPrice, ref DateTime valuedate, ref double? Leverage, ref string typeoftrade) { Map symbolvalue; int round; string BoSymbol = null; string key = symbol_id; string type = ""; string strike = ""; typeoftrade = "FU"; if (symbol_id.Contains("CALL") || symbol_id.Contains("PUT")) { type = symbol_id.Substring(0, symbol_id.IndexOf(" ")).Substring(0, 1); key = key.Substring(symbol_id.IndexOf(" ") + 1); typeoftrade = "OP"; } int nextspace = key.IndexOf(" "); string month = key.Substring(0, nextspace); key = key.Substring(nextspace + 1); nextspace = key.IndexOf(" "); string year = "20" + key.Substring(0, nextspace); key = key.Substring(nextspace + 1); if (type != "") { strike = key.Substring(key.LastIndexOf(" ") + 1); key = key.Substring(0, key.LastIndexOf(" ")).TrimEnd(); } key = key + typeoftrade; if (symbolmap.TryGetValue(key, out symbolvalue)) { MtyVolume = symbolvalue.MtyVolume; MtyPrice = symbolvalue.MtyPrice; BoSymbol = symbolvalue.BOSymbol; round = (int) symbolvalue.Round; key = BoSymbol + "." + GetMonthLetter(month) + year; if (type != "") { if (symbolvalue.MtyStrike != null) strike = (Math.Round((decimal) (Convert.ToInt32(strike)*symbolvalue.MtyStrike), 5)).ToString() .Replace('.', '_'); key = key + "." + type + strike; } Contract mapContract; if (contractdetails.TryGetValue(key, out mapContract)) { int Digitmonth = GetMonthFromLetter(GetMonthLetter(month)); if (Digitmonth < 10) month = "0" + Digitmonth; var db = new EXANTE_Entities(_currentConnection); string t = "update Ctrades SET value_date= '" + year + "-" + month + "-01' where symbol_id='" + key + "'"; // db.Database.ExecuteSqlCommand("CALL updatecontract('" + key + "','" + year + "-" + month + "-01')"); // db.Database.ExecuteSqlCommand("update Ctrades SET value_date= '" + year + "-" + month + "-01' where symbol_id='"+key+"'"); // db.Database.ExecuteSqlCommand("update Contracts SET ValueDate= '" + year + "-" + month + "-01' where id='" + key + "'"); db.Dispose(); // valuedate = (DateTime) mapContract.ValueDate; Leverage = mapContract.Leverage; BoSymbol = key; } else { LogTextBox.AppendText("\r\n" + "Lek: No Map in Contracts for " + key); } } else { LogTextBox.AppendText("\r\n" + "Lek: No Map in Mapping table for " + symbol_id); } return BoSymbol; }
private static object getSymbolMap() { var testexample = new EXANTE_Entities(); var Mapping = from m in testexample.Mappings where m.valid == 1 && m.Brocker=="ABN" select m; var result = Mapping.ToList(); testexample.Dispose(); return result; }
private void button29_Click(object sender, EventArgs e) { const string conStr = "https://backoffice.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade"; string token = GetToken("https://authdb.exante.eu/api/1.0/auth/session", "backoffice"); DateTime reportdate = ABNDate.Value; var db = new EXANTE_Entities(_currentConnection); DateTime nextdate = reportdate.AddDays(1); var cptradefromDb = (from ft in db.FT where ft.valid == 1 && ft.cp == "Manual" && ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date) && ft.Posted == null select new { ft.account_id, ft.symbol, BOSymbol = ft.symbol, ft.value, type = ft.Type, ft.ccy, ft.counterccy, ft.ValueCCY, ft.Comment, tradeDate = ft.TradeDate, id = ft.fullid }).ToList(); int tradesqty = 0; foreach (var VARIABLE in cptradefromDb) { var p = new FTjson(); p.operationType = VARIABLE.type; p.comment = VARIABLE.Comment; p.asset = VARIABLE.ccy; p.symbolId = VARIABLE.BOSymbol; p.accountId = VARIABLE.account_id; p.amount = Math.Round((double) VARIABLE.value, 2).ToString(); p.timestamp = VARIABLE.tradeDate.Value.ToString("yyyy-MM-dd HH:mm:ss"); string requestFTload = JsonConvert.SerializeObject(p); if (!SendJson(requestFTload, conStr + VARIABLE.account_id + "/transaction", token)) { LogTextBox.AppendText("\r\n Error in sending FT for : " + VARIABLE.id); } else { db.Database.ExecuteSqlCommand("update FT SET Posted= NOW() where fullid=" + VARIABLE.id); } } if (tradesqty > 0) { db.SaveChanges(); db.Dispose(); LogTextBox.AppendText("\r\n Uploaded FT for " + reportdate.ToShortDateString() + ": " + tradesqty.ToString() + "/" + cptradefromDb.Count); } }