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 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;
});
}