// Drastic action needed to be taken due to now the csv line for Yahoo is down.
// Now both Yahoo and Google do not work. There is only one left. But this means
// longer time for it it calculate all
public static void GetCurrentPerformance(ref PortfolioViewModel portfolio)
{
decimal[] stats = new decimal[2];
foreach (var itemPortfolio in portfolio.Buys)
{
string csvData = null;
try
{
using (WebClient web = new WebClient())
{
csvData = web.DownloadString("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=" + itemPortfolio.TickerSymbol + "&apikey=HWSKUZE5XRJUIFA1&datatype=csv");
//csvData = web.DownloadString("https://www.google.com.au/search?source=hp&ei=J9P7WfGJAoSZ8wWJxKDADw&q=" + company.Symbol+ " + stock + quote & oq ="+ company.Symbol + "+ stock + &gs_l = psy - ab.1.1.0l10.4103.15196.0.18018.28.20.6.0.0.0.442.3194.2 - 6j4j1.11.0....0...1.1.64.psy - ab..13.15.2718.0..0i131k1j0i10k1j0i22i30k1j0i22i10i30k1.0.a_b25pvD5F8");
//csvData = web.DownloadString("https://finance.yahoo.com/quote/" + company.Symbol + "?p=" + company.Symbol + "?p=");
stats = CompletePortfolioPerformance(portfolio, csvData, itemPortfolio.TickerSymbol);
itemPortfolio.Open = stats[0];
itemPortfolio.Close = stats[1];
itemPortfolio.Performance = (itemPortfolio.Close - itemPortfolio.PurchasePrice) * itemPortfolio.Quantity;
}
}
catch
{
}
}
}
private static decimal[] CompletePortfolioPerformance(PortfolioViewModel portfolio, string csvData, string symbol)
{
string path = HostingEnvironment.MapPath("~/Error Log File/Error.txt");
char[] delimiter = { ',', '\t', '\r', '\n', '"', '/', '{', '}', '!', '<', '>' };
string[] rows = csvData.Replace("\r", "").Split('\n');
SailiRepository repository = new SailiRepository();
bool pass = false;
decimal[] stats = new decimal[2];
foreach (string row in rows)
{
if (!pass)
{
pass = true;
continue;
}
if (string.IsNullOrEmpty(row))
{
continue;
}
string[] cols = row.Split(delimiter, StringSplitOptions.RemoveEmptyEntries);
decimal convert = 0;
bool result = Decimal.TryParse(cols[1].ToString(), out convert);
if (!result)
{
SaveErrorInRequest(path, cols);
continue;
}
result = Decimal.TryParse(cols[4].ToString(), out convert);
if (!result)
{
SaveErrorInRequest(path, cols);
continue;
}
stats[0] = Convert.ToDecimal(cols[1].ToString());
stats[1] = Convert.ToDecimal(cols[4].ToString());
break;
}
return(stats);
}
public List <SortWinner> GetTopThree(ref List <TraderAccount> traders, ref List <Owner> owners, ref List <SortWinner> winners)
{
Owner owner = null;
TraderAccount trader = null;
List <Buy> buys = new List <Buy>();
List <Sell> sells = new List <Sell>();
List <OlympianBoard> board = new List <OlympianBoard>();
Portfolio portfolio = null;
List <Portfolio> portfolios = new List <Portfolio>();
List <PortfolioViewModel> portfolioView = new List <PortfolioViewModel>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardOwner", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
owner = new Owner();
owner.OwnerID = sqlReader["OwnerID"].ToString();
owner.UserID = sqlReader["UserID"].ToString();
owner.FirstName = sqlReader["FirstName"].ToString();
owner.MiddleName = sqlReader["MiddleName"].ToString();
owner.LastName = sqlReader["LastName"].ToString();
owner.DOB = sqlReader["DOB"].ToString();
owner.AddressNumber = sqlReader["AddressNumber"].ToString();
owner.AddressName = sqlReader["AddressName"].ToString();
owner.PostcodeID = sqlReader["PostcodeID"].ToString();
owners.Add(owner);
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardPortfolio", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
portfolio = new Portfolio();
portfolio.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolio.TradingAccountID = sqlReader["TradingAccountID"].ToString();
portfolios.Add(portfolio);
}
}
con.Close();
}
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spWildcardTraderAccount", con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
trader = new TraderAccount();
trader.TradingAccountID = sqlReader["TradingAccountID"].ToString();
trader.OwnerID = sqlReader["OwnerID"].ToString();
trader.CreationDate = Convert.ToDateTime(sqlReader["CreationDate"].ToString());
trader.Balance = Convert.ToDecimal(sqlReader["Balance"].ToString());
traders.Add(trader);
}
}
con.Close();
}
List <PortfolioViewModel> PortfolioModelList = new List <PortfolioViewModel>();
PortfolioViewModel portfolioViewModel = null;
foreach (var trade in traders)
{
portfolioViewModel = null;
portfolioViewModel = GetPortfolioVodelModel(trade.TradingAccountID);
DataSerializer <string> .GetCurrentPerformance(ref portfolioViewModel);
PortfolioModelList.Add(portfolioViewModel);
}
foreach (var item in PortfolioModelList)
{
SortWinner winner = new SortWinner();
winner.TradingAcountID = item.TradingAccountID;
winner.PortfolioID = item.PortfolioID;
winners.Add(winner);
}
foreach (var winner in winners)
{
foreach (var item in PortfolioModelList)
{
foreach (var items in item.Buys)
{
if (winner.PortfolioID == items.PortfolioId)
{
winner.Amount += items.Performance;
winner.Amount += items.TransactionAmount;
winner.Amount = winner.Amount - items.TransactionCost;
winner.Amount = Math.Round(winner.Amount, 2);
items.PortfolioId = 0;
}
}
}
}
foreach (var winner in winners)
{
foreach (var trade in traders)
{
if (trade.TradingAccountID.Equals(winner.TradingAcountID))
{
winner.OwnerID = trade.OwnerID;
winner.Amount += trade.Balance;
break;
}
}
}
foreach (var itemOwner in owners)
{
foreach (var itemWinner in winners)
{
if (itemWinner.OwnerID.Equals(itemOwner.OwnerID))
{
itemWinner.Name = itemOwner.FirstName + " " + itemOwner.LastName;
break;
}
}
}
for (int i = 0; i < winners.Count; i++)
{
for (int j = 0; j < winners.Count; j++)
{
if (winners[i].Amount > winners[j].Amount)
{
decimal tempAmount = winners[i].Amount;
string tradeIdTemp = winners[i].TradingAcountID;
int portIdTemp = winners[i].PortfolioID;
string ownerIdTemp = winners[i].OwnerID;
string nameTemp = winners[i].Name;
winners[i].Amount = winners[j].Amount;
winners[i].TradingAcountID = winners[j].TradingAcountID;
winners[i].PortfolioID = winners[j].PortfolioID;
winners[i].OwnerID = winners[j].OwnerID;
winners[i].Name = winners[j].Name;
winners[j].Amount = tempAmount;
winners[j].TradingAcountID = tradeIdTemp;
winners[j].PortfolioID = portIdTemp;
winners[j].OwnerID = ownerIdTemp;
winners[j].Name = nameTemp;
}
}
}
return(winners);
}
public PortfolioViewModel GetPortfolioVodelModel(string tradingAccountID)
{
PortfolioViewModel portfolioModel = new PortfolioViewModel();
BuyViewModel buy = null;
SellViewModel sell = null;
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetPortfolio", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("TradingAccountID", tradingAccountID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
portfolioModel.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
portfolioModel.TradingAccountID = sqlReader["TradingAccountID"].ToString();
}
}
con.Close();
}
portfolioModel.Buys = new List <BuyViewModel>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetBuys", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PortfolioID", portfolioModel.PortfolioID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
if (Convert.ToInt32(sqlReader["Quantity"].ToString()) > 0)
{
buy = new BuyViewModel();
buy.BuyID = Convert.ToInt32(sqlReader["BuyID"].ToString());
buy.TradeDate = Convert.ToDateTime(sqlReader["TradeDate"].ToString());
buy.PortfolioId = Convert.ToInt32(sqlReader["PortfolioId"].ToString());
buy.TickerSymbol = sqlReader["TickerSymbol"].ToString();
buy.Quantity = Convert.ToInt32(sqlReader["Quantity"].ToString());
buy.PurchasePrice = Convert.ToDecimal(sqlReader["PurchasePrice"].ToString());
buy.TransactionAmount = Convert.ToDecimal(sqlReader["TransactionAmount"].ToString());
buy.TransactionCost = Convert.ToDecimal(sqlReader["TransactionCost"].ToString());
buy.CompanyName = GetCompanyName(buy.TickerSymbol);
portfolioModel.Buys.Add(buy);
}
}
}
con.Close();
}
portfolioModel.Sells = new List <SellViewModel>();
using (SqlConnection con = new SqlConnection(databaseConnection))
{
using (SqlCommand cmd = new SqlCommand("spGetSells", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PortfolioID", portfolioModel.PortfolioID));
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
sell = new SellViewModel();
sell.SellID = Convert.ToInt32(sqlReader["SellID"].ToString());
sell.TradeDate = Convert.ToDateTime(sqlReader["TradeDate"].ToString());
sell.BuyID = Convert.ToInt32(sqlReader["BuyID"].ToString());
sell.TickerSymbol = sqlReader["TickerSymbol"].ToString();
sell.Quantity = Convert.ToInt32(sqlReader["Quantity"].ToString());
sell.PurchasePrice = Convert.ToDecimal(sqlReader["PurchasePrice"].ToString());
sell.SoldPrice = Convert.ToDecimal(sqlReader["SoldPrice"].ToString());
sell.TransactionAmount = Convert.ToDecimal(sqlReader["TransactionAmount"].ToString());
sell.TransactionCost = Convert.ToDecimal(sqlReader["TransactionCost"].ToString());
sell.PortfolioID = Convert.ToInt32(sqlReader["PortfolioID"].ToString());
sell.CompanyName = GetCompanyName(sell.TickerSymbol);
portfolioModel.Sells.Add(sell);
}
}
con.Close();
}
return(portfolioModel);
}
