public void Stacked_Chart(bool change)
{
DateTime from_month = new DateTime();
DateTime to_month = new DateTime();
string selected_person = "";
Dispatcher.Invoke(() =>
{
from_month = new DateTime(Convert.ToDateTime(date_month_from.SelectedDate).Year, Convert.ToDateTime(date_month_from.SelectedDate).Month, 1);
to_month = Convert.ToDateTime(date_month_to.SelectedDate);
selected_person = HoldersItems.Where(r => r.IsChecked == true).Select(x => x.HolderName).First();
StackedSeriesCollection.Clear();
});
DataTable dt = BrokerageAsset.GetHistoricalInvestments(selected_person, null, null, from_month, to_month);
IEnumerable <string> selectcategories = InvestmentCategory.Getcategories();
Brush[] colors = { Brushes.DarkGreen, Brushes.ForestGreen, Brushes.Gainsboro, Brushes.DimGray, Brushes.Gold, Brushes.Orange, Brushes.DarkBlue };
Dispatcher.Invoke(() =>
{
int count = 0;
foreach (string category in selectcategories)
{
ChartValues <DateModel> chartvalues = new ChartValues <DateModel>(dt.AsEnumerable().Where(r => r["category"].ToString() == category)
.Select(r => new DateModel {
DateTime = r.Field <DateTime>("date"), Value = r.Field <double?>("ending_mkt_value") ?? 0
}));
if (chartvalues.Count != 0)
{
StackedSeriesCollection.Add(new StackedColumnSeries
{
Values = chartvalues,
StackMode = StackMode.Values,
Title = category,
Fill = colors[count],
DataLabels = true,
LabelPoint = point => (point.Y / 1000).ToString("C1") + "k",
LabelsPosition = BarLabelPosition.Perpendicular,
Foreground = Brushes.Black
});
count++;
}
}
});
if (!change)
{
Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.DataBind, (Action)(() =>
{
DataContext = this;
}));
}
}
public void FillDataGrid(string date, string asset = null)
{
//filling datagrid investments
string selected_person = "";
DateTime date_selected_to = new DateTime();
DateTime date_selected_from = new DateTime();
Dispatcher.Invoke(() =>
{
selected_person = HoldersItems.Where(r => r.IsChecked == true).Select(x => x.HolderName).First();
if (date == null)
{
date_selected_to = Convert.ToDateTime(date_month_to.SelectedDate);
date_selected_from = Convert.ToDateTime(date_month_to.SelectedDate).AddMonths(-3);
}
else
{
DateTime.TryParseExact(date, "MMM yy", new System.Globalization.CultureInfo("en-US"), System.Globalization.DateTimeStyles.None, out date_selected_to);
date_selected_from = Convert.ToDateTime(date_month_from.SelectedDate);
}
});
DataTable dt_investments = BrokerageAsset.GetMonthInvestments(selected_person, date_selected_to);
DataTable dt_transactions = BrokerageTransaction.GetHistoricalTransactions(asset, selected_person, date_selected_from, date_selected_to.AddMonths(1).AddDays(-1));
if (dt_transactions.Rows.Count > 0)
{
dt_transactions = dt_transactions.AsEnumerable().OrderByDescending(r => r["date"]).CopyToDataTable();
}
Dispatcher.Invoke(() =>
{
if (dt_investments.Rows.Count > 0)
{
date_grid.Text = "Date: " + dt_investments.Rows[0].Field <DateTime>("date").ToShortDateString();
}
else
{
date_grid.Text = "";
}
DataGridInvestments.CommitEdit();
DataGridInvestments.CommitEdit();
DataGridInvestments.DataContext = dt_investments;
DataGridTransactions.CommitEdit();
DataGridTransactions.CommitEdit();
DataGridTransactions.DataContext = dt_transactions;
});
}
public static void ImportFromExcel(string filename, ReportFormat bank, string holder, DateTime?date_statement)
{
int count_entries = 0;
using (var fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader sr = new StreamReader(fs))
{
var csv = new CsvReader(sr);
while (csv.Read())
{
DateTime dateField;
double amount = 0;
if (csv.GetField(0) != "" && csv.TryGetField(0, out dateField))
{
//asset
if (bank is StatementReportFormat)
{
if (((StatementReportFormat)bank).NeedsStatementDate == true)
{
if (date_statement == null)
{
MessageBox.Show("Please enter the date of the statement.", "Error: Date Missing on Upload");
return;
}
}
IAsset new_asset = new BrokerageAsset();
InvestmentChange investment_change = new InvestmentChange();
switch (bank.Account.FinancialInstitution.InstitutionName)
{
case "BrokerageSample":
investment_change.Date = Convert.ToDateTime(csv.GetField(0));
investment_change.Amount = Convert.ToDouble(csv.GetField(7));
new_asset = new BrokerageAsset(csv.GetField(2));
new_asset.AsOf = Convert.ToDateTime(csv.GetField(0));
new_asset.Description = csv.GetField(1);
new_asset.Value = Convert.ToDouble(csv.GetField(3));
break;
default:
break;
}
new_asset.Holder = holder;
new_asset.Bank = bank.Account.FinancialInstitution.ShortName;
new_asset.Save();
investment_change.Holder = holder;
investment_change.Bank = bank.Account.FinancialInstitution.ShortName;
investment_change.Save();
}
//transaction
else
{
ITransaction new_transaction = new BankTransaction();
string description = "";
if (bank.Account.AccountType == AccountType.Brokerage)
{
switch (bank.Account.FinancialInstitution.InstitutionName)
{
case "BrokerageSample":
string[] quantity_price = csv.GetField(4).ToString().Split(new string[] { " shares at�$", " share at�$" }, StringSplitOptions.None);
new_transaction = new BrokerageTransaction(Convert.ToDouble(quantity_price[1].Replace(",", "")), Convert.ToDouble(quantity_price[0]), csv.GetField(2));
description = csv.GetField(1);
break;
default:
break;
}
}
else
{
new_transaction = new BankTransaction(bank.Account.AccountType);
switch (bank.Account.FinancialInstitution.ShortName + " " + bank.FormatName)
{
case "B1 Default Format":
csv.TryGetField(1, out amount);
description = csv.GetField(2);
break;
default:
break;
}
new_transaction.Amount = amount;
}
new_transaction.Bank = bank.Account.FinancialInstitution.ShortName;
new_transaction.Description = description;
new_transaction.Holder = holder;
new_transaction.Date = dateField;
new_transaction.Save();
}
count_entries++;
}
}
}
}
MessageBox.Show("Detected " + count_entries + " entries and uploaded all non-duplicated entries to database.", "Upload Complete");
}
public void PriceAsset_Chart(bool change, string _asset)
{
string asset = _asset;
if (_asset == null)
{
asset = BrokerageAsset.GetFirstAsset();
}
DateTime from_month = new DateTime();
DateTime to_month = new DateTime();
string selected_person = "";
Dispatcher.Invoke(() =>
{
from_month = new DateTime(Convert.ToDateTime(date_month_from.SelectedDate).Year, Convert.ToDateTime(date_month_from.SelectedDate).Month, 1);
to_month = Convert.ToDateTime(date_month_to.SelectedDate);
selected_person = HoldersItems.Where(r => r.IsChecked == true).Select(x => x.HolderName).First();
PriceSeriesCollection.Clear();
});
//stock price API
ChartValues <DateModel> chartprices = new ChartValues <DateModel>();
ChartValues <DateModel> chartprices1 = new ChartValues <DateModel>();
ChartValues <DateModel> chartprices2 = new ChartValues <DateModel>();
ChartValues <DateModel> chartprices10 = new ChartValues <DateModel>();
if (!new [] { "Treasuries", "Bonds", "CDs" }.Contains(asset))
{
order_history_formatter = "C2";
string alphav_key = System.Web.Configuration.WebConfigurationManager.AppSettings["alphav_key"];
if (alphav_key != null)
{
string url_alphav_priceadj = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=" + asset + "&apikey=" + alphav_key;
string results_timeseries = "Time Series (Daily)";
if ((to_month - from_month).TotalDays > 95)
{
url_alphav_priceadj = "https://www.alphavantage.co/query?function=TIME_SERIES_WEEKLY_ADJUSTED&symbol=" + asset + "&apikey=" + alphav_key;
results_timeseries = "Weekly Adjusted Time Series";
}
JObject json_results = (JObject)JsonConvert.DeserializeObject(Helpers.APIGet.GetAPIdata(url_alphav_priceadj));
if (json_results.First.ToString().Contains("Error Message"))
{
Dispatcher.Invoke(() =>
{
alert_APIerror.Visibility = Visibility.Visible;
alert_APIerror.Text = "Security not found";
});
}
else if (json_results.First.ToString().Contains("Thank you for"))
{
Dispatcher.Invoke(() =>
{
alert_APIerror.Visibility = Visibility.Visible;
alert_APIerror.Text = "API call limit reached (5 per min)";
});
}
else
{
Dispatcher.Invoke(() =>
{
alert_APIerror.Visibility = Visibility.Hidden;
});
foreach (JProperty date in json_results[results_timeseries])
{
if (Convert.ToDateTime(date.Name) <= to_month && Convert.ToDateTime(date.Name) >= from_month)
{
chartprices.Add(new DateModel {
DateTime = Convert.ToDateTime(date.Name), Value = Convert.ToDouble(date.Value["5. adjusted close"])
});
}
}
}
}
}
else
{
order_history_formatter = "P2";
string url_yields = "https://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE)%20gt%20" + (from_month.Year - 1);
XmlDocument doc = new XmlDocument();
doc.LoadXml(Helpers.APIGet.GetAPIdata(url_yields));
var nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("a", "http://www.w3.org/2005/Atom");
nsmgr.AddNamespace("m", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata");
nsmgr.AddNamespace("d", "http://schemas.microsoft.com/ado/2007/08/dataservices");
foreach (XmlNode node in doc.DocumentElement.SelectNodes("a:entry/a:content", nsmgr))
{
DateTime entry_date = Convert.ToDateTime(node.SelectSingleNode("m:properties/d:NEW_DATE", nsmgr).InnerText);
if ((to_month - from_month).TotalDays > 95 && entry_date.DayOfWeek != DayOfWeek.Friday)
{
continue;
}
if (entry_date >= from_month && entry_date <= to_month)
{
chartprices.Add(new DateModel {
DateTime = entry_date, Value = Convert.ToDouble(node.SelectSingleNode("m:properties/d:BC_3MONTH", nsmgr).InnerText) / 100
});
chartprices1.Add(new DateModel {
DateTime = entry_date, Value = Convert.ToDouble(node.SelectSingleNode("m:properties/d:BC_1YEAR", nsmgr).InnerText) / 100
});
chartprices2.Add(new DateModel {
DateTime = entry_date, Value = Convert.ToDouble(node.SelectSingleNode("m:properties/d:BC_2YEAR", nsmgr).InnerText) / 100
});
chartprices10.Add(new DateModel {
DateTime = entry_date, Value = Convert.ToDouble(node.SelectSingleNode("m:properties/d:BC_10YEAR", nsmgr).InnerText) / 100
});
}
}
}
//database of past investment transactions
string variable_sql = (new [] { "Treasuries", "Bonds", "CDs" }.Contains(asset) ? "yield_to_maturity" : "price");
string variable_label = (new [] { "Treasuries", "Bonds", "CDs" }.Contains(asset) ? "Yield" : "Price");
Dispatcher.Invoke(() =>
{
gridstats.Children.Clear();
if (chartprices.Count != 0)
{
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Latest " + variable_label + " (" + chartprices.AsEnumerable().OrderBy(r => r.DateTime).Last().DateTime.ToShortDateString()
+ "):", 2, 0));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Average " + variable_label + ":", 3, 0));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Minimum " + variable_label + ":", 4, 0));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Maximum " + variable_label + ":", 5, 0));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices.AsEnumerable().OrderBy(r => r.DateTime).Last().Value.ToString(order_history_formatter), 2, 1));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices.AsEnumerable().Average(r => r.Value).ToString(order_history_formatter), 3, 1));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices.AsEnumerable().Min(r => r.Value).ToString(order_history_formatter), 4, 1));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices.AsEnumerable().Max(r => r.Value).ToString(order_history_formatter), 5, 1));
PriceSeriesCollection.Add(new LineSeries
{
Values = chartprices,
Title = (new string[] { "Treasuries", "Bonds", "CDs" }.Contains(asset) ? "3MO T-Bill" : asset),
Foreground = System.Windows.Media.Brushes.Black,
PointGeometrySize = 0
});
if (new string[] { "Treasuries", "Bonds", "CDs" }.Contains(asset))
{
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("3-Month:", 1, 1));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("1-Year:", 1, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices1.AsEnumerable().OrderBy(r => r.DateTime).Last().Value.ToString(order_history_formatter), 2, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices1.AsEnumerable().Average(r => r.Value).ToString(order_history_formatter), 3, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices1.AsEnumerable().Min(r => r.Value).ToString(order_history_formatter), 4, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices1.AsEnumerable().Max(r => r.Value).ToString(order_history_formatter), 5, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("2-Year:", 1, 3));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices2.AsEnumerable().OrderBy(r => r.DateTime).Last().Value.ToString(order_history_formatter), 2, 3));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices2.AsEnumerable().Average(r => r.Value).ToString(order_history_formatter), 3, 3));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices2.AsEnumerable().Min(r => r.Value).ToString(order_history_formatter), 4, 3));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices2.AsEnumerable().Max(r => r.Value).ToString(order_history_formatter), 5, 3));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("10-Year:", 1, 4));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices10.AsEnumerable().OrderBy(r => r.DateTime).Last().Value.ToString(order_history_formatter), 2, 4));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices10.AsEnumerable().Average(r => r.Value).ToString(order_history_formatter), 3, 4));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices10.AsEnumerable().Min(r => r.Value).ToString(order_history_formatter), 4, 4));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(chartprices10.AsEnumerable().Max(r => r.Value).ToString(order_history_formatter), 5, 4));
PriceSeriesCollection.Add(new LineSeries
{
Values = chartprices1,
Title = "1YR T-Bill",
Foreground = System.Windows.Media.Brushes.Black,
PointGeometrySize = 0
});
PriceSeriesCollection.Add(new LineSeries
{
Values = chartprices2,
Title = "2YR T-Bill",
Foreground = System.Windows.Media.Brushes.Black,
PointGeometrySize = 0
});
PriceSeriesCollection.Add(new LineSeries
{
Values = chartprices10,
Title = "10YR T-Bill",
Foreground = System.Windows.Media.Brushes.Black,
PointGeometrySize = 0
});
}
}
});
Dispatcher.Invoke(() =>
{
DataTable dt = BrokerageTransaction.GetHistoricalTransactions(asset, selected_person, from_month, to_month);
if (dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("bought", StringComparison.OrdinalIgnoreCase) >= 0).Count() != 0 ||
dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("buy", StringComparison.OrdinalIgnoreCase) >= 0).Count() != 0)
{
PriceSeriesCollection.Add(new ScatterSeries
{
Title = "Bought",
Values = new ChartValues <DateModel>(dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("bought", StringComparison.OrdinalIgnoreCase) >= 0 ||
r["transaction_description"].ToString().IndexOf("buy", StringComparison.OrdinalIgnoreCase) >= 0)
.Select(r => new DateModel {
DateTime = r.Field <DateTime>("date"), Value = r.Field <double?>(variable_sql) ?? 0
}))
});
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Average " + variable_label + " Bought:", 0, 0));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("bought", StringComparison.OrdinalIgnoreCase) >= 0 ||
r["transaction_description"].ToString().IndexOf("buy", StringComparison.OrdinalIgnoreCase) >= 0)
.Average(r => r.Field <double>(variable_sql)).ToString(order_history_formatter), 0, 1));
}
if (dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("sold", StringComparison.OrdinalIgnoreCase) >= 0).Count() != 0 ||
dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("sell", StringComparison.OrdinalIgnoreCase) >= 0).Count() != 0)
{
PriceSeriesCollection.Add(new ScatterSeries
{
Title = "Sold",
Values = new ChartValues <DateModel>(dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("sold", StringComparison.OrdinalIgnoreCase) >= 0 ||
r["transaction_description"].ToString().IndexOf("sell", StringComparison.OrdinalIgnoreCase) >= 0)
.Select(r => new DateModel {
DateTime = r.Field <DateTime>("date"), Value = r.Field <double?>(variable_sql) ?? 0
}))
});
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid("Average " + variable_label + " Sold:", 0, 2));
gridstats.Children.Add(Helpers.GridHelper.CreateTextInGrid(dt.AsEnumerable().Where(r => r["transaction_description"].ToString().IndexOf("sold", StringComparison.OrdinalIgnoreCase) >= 0 ||
r["transaction_description"].ToString().IndexOf("sell", StringComparison.OrdinalIgnoreCase) >= 0)
.Average(r => r.Field <double>(variable_sql)).ToString(order_history_formatter), 0, 3));
}
label_assetclassprice.Text = asset + ": Order History and " + (new [] { "Treasuries", "Bonds", "CDs" }.Contains(asset) ? "Yields" : "Price (Adjusted For Splits and Dividends)");
label_secstats.Text = "Statistics for " + asset;
PriceChart.DataContext = this;
});
}
public void StackedAsset_Chart(bool change, string category)
{
DateTime from_month = new DateTime();
DateTime to_month = new DateTime();
string selected_person = "";
Regex regex = new Regex(@"\d{1,2}\/\d{1,2}\/\d{4}");
Dispatcher.Invoke(() =>
{
label_assetclass.Text = category + " Allocation";
from_month = new DateTime(Convert.ToDateTime(date_month_from.SelectedDate).Year, Convert.ToDateTime(date_month_from.SelectedDate).Month, 1);
to_month = Convert.ToDateTime(date_month_to.SelectedDate);
selected_person = HoldersItems.Where(r => r.IsChecked == true).Select(x => x.HolderName).First();
StackedSeriesCollection2.Clear();
});
string asset_column = (new [] { "Treasuries", "Bonds", "CDs" }.Contains(category) ? "asset_description" : "asset_symbol");
DataTable dt = BrokerageAsset.GetHistoricalInvestments(selected_person, category, asset_column, from_month, to_month);
string[] assets = dt.AsEnumerable().Select(r => r[asset_column].ToString()).Distinct()
.OrderBy(s => (regex.IsMatch(s) ? Convert.ToDateTime(regex.Match(s).Value).ToString("yyyyMMdd") : s))
.ToArray();
Dispatcher.Invoke(() =>
{
if (assets.Length > 8)
{
chartStackedAsset.LegendLocation = LegendLocation.None;
}
else
{
chartStackedAsset.LegendLocation = LegendLocation.Top;
}
foreach (string asset in assets)
{
ChartValues <DateModel> chartvalues = new ChartValues <DateModel>(dt.AsEnumerable().Where(r => r[asset_column].ToString() == asset)
.Select(r => new DateModel {
DateTime = r.Field <DateTime>("date"), Value = r.Field <double?>("ending_mkt_value") ?? 0
}));
if (chartvalues.Count != 0)
{
StackedSeriesCollection2.Add(new StackedColumnSeries
{
Values = chartvalues,
StackMode = StackMode.Values,
Title = (regex.IsMatch(asset) ? regex.Match(asset).Value : asset),
DataLabels = (assets.Length > 8 ? false : true),
LabelPoint = point => (point.Y / 1000).ToString("C1") + "k",
LabelsPosition = BarLabelPosition.Perpendicular,
Foreground = System.Windows.Media.Brushes.Black
});
}
}
});
if (!change)
{
Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.DataBind, (Action)(() =>
{
DataContext = this;
}));
}
}
