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