public DataFrame GetPortfolioHPR(int?portfolioId, string userId)
{
List <Trade> allTrades = _repo.GetAllUserTrades(portfolioId, userId);
//Creating flows for each trade at a position level.
// Flows and Cash
if (allTrades.Count == 0)
{
return(null);
}
DataFrame flowFrame = CreateFlowTable(allTrades);
//distinct tickers;
string portfolioName = _repo.GetPortfolioName(portfolioId);
PortfolioData userPortfolio = GetPortfolioData(portfolioName, allTrades);
//#######
DataFrame portfolioValuation = userPortfolio.GetValuation();
int pvSize = portfolioValuation.Rows.Count();
PrimitiveDataFrameColumn <decimal> cashCol2 = new PrimitiveDataFrameColumn <decimal>("cash", pvSize);
PrimitiveDataFrameColumn <decimal> inflowCol2 = new PrimitiveDataFrameColumn <decimal>("inflow", pvSize);
portfolioValuation.Columns.Add(cashCol2);
portfolioValuation.Columns.Add(inflowCol2);
//populate with flowFrame data
int secondaryRow = 0;
int valueIndex = portfolioValuation.Columns.Count - 3;
int cashIndex = portfolioValuation.Columns.Count - 2;
int inflowIndex = portfolioValuation.Columns.Count - 1;
for (int row = 0; row < pvSize; row++)
{
if (secondaryRow == flowFrame.Rows.Count)
{
break;
}
if (secondaryRow < flowFrame.Rows.Count)
{
if (portfolioValuation[row, 0].Equals(flowFrame[secondaryRow, 0]))
{
// if the dates match
portfolioValuation[row, cashIndex] = flowFrame[secondaryRow, 1];
portfolioValuation[row, inflowIndex] = flowFrame[secondaryRow, 2];
secondaryRow++;
}
}
else
{
portfolioValuation[row, cashIndex] = Decimal.Zero;
portfolioValuation[row, inflowIndex] = Decimal.Zero;
}
}
//forwardfill cashcolumn then replace null with Decimal.Zero
bool toFill = false;
decimal prevCash = decimal.Zero;
for (int row = 0; row < pvSize; row++)
{
if (((portfolioValuation[row, cashIndex] != null && (decimal?)portfolioValuation[row, cashIndex] != Decimal.Zero) && !toFill) ||
((portfolioValuation[row, cashIndex] != null && (decimal?)portfolioValuation[row, cashIndex] != Decimal.Zero) && toFill))
{
toFill = true;
prevCash = (decimal)portfolioValuation[row, cashIndex];
}
else if ((portfolioValuation[row, cashIndex] == null || (decimal?)portfolioValuation[row, cashIndex] == Decimal.Zero) && toFill)
{
portfolioValuation[row, cashIndex] = prevCash;
}
}
portfolioValuation.Columns.GetPrimitiveColumn <decimal>("cash").FillNulls(Decimal.Zero, true);
portfolioValuation.Columns.GetPrimitiveColumn <decimal>("inflow").FillNulls(Decimal.Zero, true);
// get total portfolio value
//
PrimitiveDataFrameColumn <decimal> PortfolioVal = new PrimitiveDataFrameColumn <decimal>("PortfolioValue", pvSize);
portfolioValuation.Columns.Add(PortfolioVal);
int PortfolioValIndex = portfolioValuation.Columns.Count - 1;
for (int row = 0; row < pvSize; row++)
{
portfolioValuation[row, PortfolioValIndex] = (decimal)portfolioValuation[row, cashIndex] + (decimal)portfolioValuation[row, valueIndex];
}
PrimitiveDataFrameColumn <decimal> HPRcol = new PrimitiveDataFrameColumn <decimal>("Holding Period Return", pvSize);
portfolioValuation.Columns.Add(HPRcol);
int hprIndex = portfolioValuation.Columns.Count - 1;
for (int row = 1; row < pvSize; row++)
{
int prevRow = row - 1;
decimal HPR = (((decimal)portfolioValuation[row, PortfolioValIndex]) / ((decimal)portfolioValuation[prevRow, PortfolioValIndex] + (decimal)portfolioValuation[row, inflowIndex]) - 1) * 100;
portfolioValuation[row, hprIndex] = Math.Round(HPR, 3);
}
// This is HPR performance indexed
PrimitiveDataFrameColumn <decimal> HPRindexed = new PrimitiveDataFrameColumn <decimal>("Holding Period Return Indexed", pvSize);
portfolioValuation.Columns.Add(HPRindexed);
int HPRi = portfolioValuation.Columns.Count - 1;
portfolioValuation[0, HPRi] = 100m; //initial index.
for (int row = 1; row < pvSize; row++)
{
int prevRow = row - 1;
decimal HPRx = (decimal)portfolioValuation[prevRow, HPRi] * (((decimal)portfolioValuation[row, hprIndex] / 100) + 1);
portfolioValuation[row, HPRi] = Math.Round(HPRx, 3);
}
System.Diagnostics.Debug.WriteLine(portfolioValuation);
return(portfolioValuation);
}
