public async Task SaveProfitAndLoss(ProfitAndLoss pnl)
{
_log.LogInformation($"Adding pnl for {pnl.Pair} to database");
var contextProfitAndLosses = _context.ProfitAndLosses;
await contextProfitAndLosses.AddAsync(pnl);
await _context.SaveChangesAsync();
}
public static ProfitAndLoss GetProfitAndLossForPair(IEnumerable <Trade> trades, Currency currency)
{
var tradeList = trades.ToList();
decimal totalBought = 0;
decimal totalSold = 0;
decimal totalBuyCost = 0;
decimal totalSellCost = 0;
decimal commisssion = 0;
foreach (var trade in tradeList)
{
if (trade.Side == TradeSide.Buy)
{
totalBought = totalBought + trade.QuantityOfTrade;
totalBuyCost = totalBuyCost + trade.Cost;
}
if (trade.Side == TradeSide.Sell)
{
totalSold = totalSold + trade.QuantityOfTrade;
totalSellCost = totalSellCost + trade.Cost;
}
commisssion = commisssion + trade.Commission;
}
decimal averageBuy = 0;
decimal averageSell = 0;
try
{
averageBuy = totalBuyCost / totalBought;
averageSell = totalSellCost / totalSold;
}
catch (Exception)
{
// TODO: Should log a could not divide by 0;
}
var remaining = totalBought - totalSold;
var profitAndLoss = new ProfitAndLoss
{
AverageBuyPrice = averageBuy,
AverageSellPrice = averageSell,
UnrealisedProfit = totalSellCost - totalBuyCost,
Profit = (totalSellCost - totalBuyCost) - (averageBuy * remaining),
Base = currency.Base,
Terms = currency.Terms,
QuantitySold = totalSold,
QuantityBought = totalBought,
CommissionPaid = commisssion
};
return(profitAndLoss);
}
public static ProfitAndLoss GetProfitAndLossForPair(IEnumerable <Trade> trades, string ccy1, string ccy2)
{
decimal totalBought = 0;
decimal totalSold = 0;
decimal totalBuyCost = 0;
decimal totalSellCost = 0;
decimal commisssion = 0;
foreach (var trade in trades)
{
if (trade.Side == TradeSide.Buy)
{
totalBought = totalBought + trade.QuantityOfTrade;
totalBuyCost = totalBuyCost + trade.Cost;
}
if (trade.Side == TradeSide.Sell)
{
if (trade.Side == TradeSide.Sell)
{
totalSold = totalSold + trade.QuantityOfTrade;
totalSellCost = totalSellCost + trade.Cost;
}
}
commisssion = commisssion + trade.Commission;
}
decimal averageBuy = 0;
decimal averageSell = 0;
try
{
averageBuy = totalBuyCost / totalBought;
averageSell = totalSellCost / totalSold;
}
catch (Exception)
{
// TODO: Should log a could not divide by 0;
}
var profitAndLoss = new ProfitAndLoss
{
AverageBuyPrice = averageBuy,
AverageSellPrice = averageSell,
Profit = totalBuyCost - totalSellCost,
Base = ccy1,
Terms = ccy2,
QuantitySold = totalSold,
QuantityBought = totalBought,
CommissionPaid = commisssion
};
return(profitAndLoss);
}
private static void Chapater2()
{
Chapter_02.ProfitAndLoss chapter2 = new ProfitAndLoss();
//Chapter2.ProfitAndLossMadebyUser();
//Chapter2.FindEvenAndOdNumber();
//chapter2.IsGivenYearisLeapyear();
//chapter2.ReverseOfFiveDigitNumberisSame();
//chapter2.FindYongestOfthree();
//chapter2.FindTheThreeAnglesOftriangel();
//chapter2.FindtheAbsoluteValueofANumber();
//chapter2.Areaoftherectangleisgreaterthanitsperimeter();
//chapter2.ThreePointsFallOnOneStraightLine();
//chapter2.CharactersASCIIValue();
//chapter2.rulesforinsurence();
//chapter2.FindTheGradeOfSteel();
//chapter2.LateReturnChargeofBook();
//chapter2.FindifTriangleIsvalid();
//chapter2.TriangleBasedonAngles();
chapter2.FindWorkerEfficiency();
}
public void SaveProfitAndLoss(ProfitAndLoss pnl)
{
_log.LogInformation($"Adding pnl for {pnl.Pair} to database");
_db.Upsert(pnl);
}
public async Task <FileInfo> GetTradeExport()
{
using (var xlPackage = new ExcelPackage())
{
var pnlDict = new Dictionary <string, List <ProfitAndLoss> >();
var pnlWorksheet = xlPackage.Workbook.Worksheets.Add("Pair PnL");
var totalPnlWorksheet = xlPackage.Workbook.Worksheets.Add("Total PnL");
var allPairs = await _databaseService.GetAllPairs();
totalPnlWorksheet.Cells["A1"].Value = "Coin";
totalPnlWorksheet.Cells["B1"].Value = "Unrealised Profit";
totalPnlWorksheet.Cells["C1"].Value = "Realised Profit";
pnlWorksheet.Cells["A1"].Value = "Base";
pnlWorksheet.Cells["B1"].Value = "Terms";
pnlWorksheet.Cells["C1"].Value = "Unrealised Profit in Base CCY";
pnlWorksheet.Cells["D1"].Value = "Realised Profit in Base CCY";
pnlWorksheet.Cells["E1"].Value = "Average Buy Price";
pnlWorksheet.Cells["F1"].Value = "Average Sell Price";
pnlWorksheet.Cells["G1"].Value = "Commission Paid";
pnlWorksheet.Cells["H1"].Value = "Quantity Bought";
pnlWorksheet.Cells["I1"].Value = "Quantity Sold";
pnlWorksheet.Cells["J1"].Value = "Quantity Remaining";
int lastPairProfitRow = 2;
foreach (var currency in allPairs)
{
var excelWorksheet = xlPackage.Workbook.Worksheets.Add(currency.ToString());
var allTradesForCurrency = _databaseService.GetAllTradesFor(currency);
excelWorksheet.Cells["A1"].Value = "Id";
excelWorksheet.Cells["B1"].Value = "Timestamp";
excelWorksheet.Cells["C1"].Value = "Base";
excelWorksheet.Cells["D1"].Value = "Terms";
excelWorksheet.Cells["E1"].Value = "Side";
excelWorksheet.Cells["F1"].Value = "Limit";
excelWorksheet.Cells["G1"].Value = "Quantity";
excelWorksheet.Cells["H1"].Value = "Quantity Remaining";
excelWorksheet.Cells["I1"].Value = "Abs Quantity";
excelWorksheet.Cells["J1"].Value = "Cost";
excelWorksheet.Cells["K1"].Value = "Abs Cost";
excelWorksheet.Cells["L1"].Value = "Commission";
excelWorksheet.Cells["M1"].Value = "Exchange";
var i = 2;
foreach (var trade in allTradesForCurrency)
{
var costAbs = trade.Cost;
if (trade.Side == TradeSide.Buy)
{
costAbs = -costAbs;
}
excelWorksheet.Cells["A" + i].Value = trade.ExchangeId;
excelWorksheet.Cells["B" + i].Value = trade.Timestamp;
excelWorksheet.Cells["C" + i].Value = trade.Base;
excelWorksheet.Cells["D" + i].Value = trade.Terms;
excelWorksheet.Cells["E" + i].Value = trade.Side;
excelWorksheet.Cells["F" + i].Value = trade.Limit;
excelWorksheet.Cells["G" + i].Value = trade.Quantity;
excelWorksheet.Cells["H" + i].Value = trade.QuantityRemaining;
excelWorksheet.Cells["I" + i].Value = trade.QuantityOfTrade;
excelWorksheet.Cells["J" + i].Value = trade.Cost;
excelWorksheet.Cells["K" + i].Value = costAbs;
excelWorksheet.Cells["L" + i].Value = trade.Commission;
excelWorksheet.Cells["M" + i].Value = trade.Exchange;
i++;
}
ProfitAndLoss pnl = ProfitCalculator.GetProfitAndLossForPair(allTradesForCurrency, currency);
pnlWorksheet.Cells["A" + lastPairProfitRow].Value = pnl.Base;
pnlWorksheet.Cells["B" + lastPairProfitRow].Value = pnl.Terms;
pnlWorksheet.Cells["C" + lastPairProfitRow].Value = pnl.UnrealisedProfit;
pnlWorksheet.Cells["D" + lastPairProfitRow].Value = pnl.Profit;
pnlWorksheet.Cells["E" + lastPairProfitRow].Value = pnl.AverageBuyPrice;
pnlWorksheet.Cells["F" + lastPairProfitRow].Value = pnl.AverageSellPrice;
pnlWorksheet.Cells["G" + lastPairProfitRow].Value = pnl.CommissionPaid;
pnlWorksheet.Cells["H" + lastPairProfitRow].Value = pnl.QuantityBought;
pnlWorksheet.Cells["I" + lastPairProfitRow].Value = pnl.QuantitySold;
pnlWorksheet.Cells["J" + lastPairProfitRow].Value = pnl.Remaining;
if (pnlDict.Keys.Contains(pnl.Base))
{
pnlDict[pnl.Base].Add(pnl);
}
else
{
var list = new List <ProfitAndLoss> {
pnl
};
pnlDict[pnl.Base] = list;
}
lastPairProfitRow++;
}
int rowNumber = 2;
foreach (var pnlPair in pnlDict)
{
decimal realisedPnl = 0m;
decimal unrelaisedPnl = 0m;
foreach (var profitAndLoss in pnlPair.Value)
{
realisedPnl = realisedPnl + profitAndLoss.Profit;
unrelaisedPnl = realisedPnl + profitAndLoss.UnrealisedProfit;
}
totalPnlWorksheet.Cells["A" + rowNumber].Value = pnlPair.Key;
totalPnlWorksheet.Cells["B" + rowNumber].Value = unrelaisedPnl;
totalPnlWorksheet.Cells["C" + rowNumber].Value = realisedPnl;
rowNumber++;
}
var path = Directory.GetCurrentDirectory() + @"\temp_trade_export.xlsx";
var fileInfo = new FileInfo(path);
xlPackage.SaveAs(fileInfo);
return(fileInfo);
}
}
public FileInfo GetTradeExport()
{
using (var xlPackage = new ExcelPackage())
{
var pnlWorksheet = xlPackage.Workbook.Worksheets.Add("Profit and Loss");
var allPairs = _databaseService.GetAllPairs();
pnlWorksheet.Cells["A1"].Value = "Base";
pnlWorksheet.Cells["B1"].Value = "Terms";
pnlWorksheet.Cells["C1"].Value = "Unrealised Profit in Base CCY";
pnlWorksheet.Cells["D1"].Value = "Realised Profit in Base CCY";
pnlWorksheet.Cells["E1"].Value = "Average Buy Price";
pnlWorksheet.Cells["F1"].Value = "Average Sell Price";
pnlWorksheet.Cells["G1"].Value = "Commission Paid";
pnlWorksheet.Cells["H1"].Value = "Quantity Bought";
pnlWorksheet.Cells["I1"].Value = "Quantity Sold";
pnlWorksheet.Cells["J1"].Value = "Quantity Remaining";
int lastPairProfitRow = 2;
foreach (var currency in allPairs)
{
var excelWorksheet = xlPackage.Workbook.Worksheets.Add(currency.ToString());
var allTradesForCurrency = _databaseService.GetAllTradesFor(currency);
excelWorksheet.Cells["A1"].Value = "Id";
excelWorksheet.Cells["B1"].Value = "Timestamp";
excelWorksheet.Cells["C1"].Value = "Base";
excelWorksheet.Cells["D1"].Value = "Terms";
excelWorksheet.Cells["E1"].Value = "Side";
excelWorksheet.Cells["F1"].Value = "Limit";
excelWorksheet.Cells["G1"].Value = "Quantity";
excelWorksheet.Cells["H1"].Value = "Quantity Remaining";
excelWorksheet.Cells["I1"].Value = "Abs Quantity";
excelWorksheet.Cells["J1"].Value = "Cost";
excelWorksheet.Cells["K1"].Value = "Abs Cost";
excelWorksheet.Cells["L1"].Value = "Commission";
excelWorksheet.Cells["M1"].Value = "Exchange";
var i = 2;
foreach (var trade in allTradesForCurrency)
{
var costAbs = trade.Cost;
if (trade.Side == TradeSide.Buy)
{
costAbs = -costAbs;
}
excelWorksheet.Cells["A" + i].Value = trade.ExchangeId;
excelWorksheet.Cells["B" + i].Value = trade.TimeStamp;
excelWorksheet.Cells["C" + i].Value = trade.Base;
excelWorksheet.Cells["D" + i].Value = trade.Terms;
excelWorksheet.Cells["E" + i].Value = trade.Side;
excelWorksheet.Cells["F" + i].Value = trade.Limit;
excelWorksheet.Cells["G" + i].Value = trade.Quantity;
excelWorksheet.Cells["H" + i].Value = trade.QuantityRemaining;
excelWorksheet.Cells["I" + i].Value = trade.QuantityOfTrade;
excelWorksheet.Cells["J" + i].Value = trade.Cost;
excelWorksheet.Cells["K" + i].Value = costAbs;
excelWorksheet.Cells["L" + i].Value = trade.Commission;
excelWorksheet.Cells["M" + i].Value = trade.Exchange;
i++;
}
ProfitAndLoss pnl = ProfitCalculator.GetProfitAndLossForPair(allTradesForCurrency, currency);
pnlWorksheet.Cells["A" + lastPairProfitRow].Value = pnl.Base;
pnlWorksheet.Cells["B" + lastPairProfitRow].Value = pnl.Terms;
pnlWorksheet.Cells["C" + lastPairProfitRow].Value = pnl.UnrealisedProfit;
pnlWorksheet.Cells["D" + lastPairProfitRow].Value = pnl.Profit;
pnlWorksheet.Cells["E" + lastPairProfitRow].Value = pnl.AverageBuyPrice;
pnlWorksheet.Cells["F" + lastPairProfitRow].Value = pnl.AverageSellPrice;
pnlWorksheet.Cells["G" + lastPairProfitRow].Value = pnl.CommissionPaid;
pnlWorksheet.Cells["H" + lastPairProfitRow].Value = pnl.QuantityBought;
pnlWorksheet.Cells["I" + lastPairProfitRow].Value = pnl.QuantitySold;
pnlWorksheet.Cells["J" + lastPairProfitRow].Value = pnl.Remaining;
lastPairProfitRow++;
}
var path = Directory.GetCurrentDirectory() + @"\temp_trade_export.xlsx";
var fileInfo = new FileInfo(path);
xlPackage.SaveAs(fileInfo);
return(fileInfo);
}
}
