public static void Export(filterReportDS ds) { using (ExcelPackage xlPackage = new ExcelPackage()) { var ws = xlPackage.Workbook.Worksheets[0]; var valuesRange = ws.Names[""]; //ws.Cells[valuesRange.Address].LoadFromDataTable(); //xlPackage.SaveAs(""); } }
/// <summary> /// /// </summary> /// <param name="ds"></param> /// <param name="options"></param> /// <returns>The path of the exported file. Empty if not saved.</returns> public string Export(filterReportDS ds, ExportOptions options) { _ds = ds; //initialize the package using the template from the resources var assembly = Assembly.GetExecutingAssembly(); using (Stream templateStream = assembly.GetManifestResourceStream("QPAS.Resources.ExportTemplate.xlsx")) using (var packageStream = new MemoryStream()) using (var xlPackage = new ExcelPackage(packageStream, templateStream)) { var wb = xlPackage.Workbook; //Loop through the options. If they're enable, call the exporting function //assigned to it through the _exportMethodMap dictionary var sheetNames = wb.Worksheets.Select(x => x.Name).ToList(); foreach (string sheetName in sheetNames) { if (options.SelectedItems.Contains(sheetName)) { InsertData(sheetName, wb); } else { //not enabled -- delete the worksheet //wb.Worksheets.Delete(wb.Worksheets[sheetName].Index); } } //save to file var dialog = new Microsoft.Win32.SaveFileDialog(); dialog.Filter = "Excel files (.xlxs)|*.xlsx"; if (dialog.ShowDialog() == true) { using (var fileStream = new FileStream(dialog.FileName, FileMode.Create, FileAccess.Write)) { xlPackage.SaveAs(fileStream); } return(dialog.FileName); } } return(""); }
public PerformanceReportWindow(filterReportDS data, ReportSettings settings) { InitializeComponent(); //hiding the tab headers Style s = new Style(); s.Setters.Add(new Setter(VisibilityProperty, Visibility.Collapsed)); MainTabCtrl.ItemContainerStyle = s; ViewModel = new PerformanceReportViewModel(data, settings, DialogCoordinator.Instance); DataContext = ViewModel; //give instrument pnl chart 30 pixels height for every bar, there's no better way of fitting it to the contents RealizedPLByInstrumentChart.Height = 50 + ViewModel.Data.pnlByInstrument.Rows.Count * 30; ROACByInstrumentChart.Height = 50 + ViewModel.Data.instrumentROAC.Rows.Count * 30; TotalPLByTagChart.Height = 50 + ViewModel.Data.PLByTag.Rows.Count * 30; AvgPLByTagChart.Height = 50 + ViewModel.Data.PLByTag.Rows.Count * 30; }
/// <summary> /// /// </summary> /// <param name="ds"></param> /// <param name="options"></param> /// <returns>The path of the exported file. Empty if not saved.</returns> public string Export(filterReportDS ds, ExportOptions options) { _ds = ds; //initialize the package using the template from the resources var assembly = Assembly.GetExecutingAssembly(); using (Stream templateStream = assembly.GetManifestResourceStream("QPAS.Resources.ExportTemplate.xlsx")) using (var packageStream = new MemoryStream()) using (var xlPackage = new ExcelPackage(packageStream, templateStream)) { var wb = xlPackage.Workbook; //Loop through the options. If they're enable, call the exporting function //assigned to it through the _exportMethodMap dictionary var sheetNames = wb.Worksheets.Select(x => x.Name).ToList(); foreach(string sheetName in sheetNames) { if (options.SelectedItems.Contains(sheetName)) { InsertData(sheetName, wb); } else { //not enabled -- delete the worksheet //wb.Worksheets.Delete(wb.Worksheets[sheetName].Index); } } //save to file var dialog = new Microsoft.Win32.SaveFileDialog(); dialog.Filter = "Excel files (.xlxs)|*.xlsx"; if (dialog.ShowDialog() == true) { using (var fileStream = new FileStream(dialog.FileName, FileMode.Create, FileAccess.Write)) { xlPackage.SaveAs(fileStream); } return dialog.FileName; } } return ""; }
public PerformanceReportViewModel(filterReportDS data, ReportSettings settings, IDialogCoordinator dialogService) : base(null) { Data = data; Settings = settings; CopyChart = new RelayCommand <PlotView>(x => x.CopyToClipboard()); SaveChart = new RelayCommand <PlotView>(x => { try { x.SaveAsPNG(); } catch (Exception ex) { dialogService.ShowMessageAsync(this, "Error saving image", ex.Message); } }); //Calculate best fit line for the return vs size scatter chart double rsq; double[] b; MathUtils.MLR( Data.positionSizesVsReturns.Select(x => x.size).ToList(), Data.positionSizesVsReturns.Select(x => x.ret).ToList(), out b, out rsq); _retVsSizeBestFitLineConstant = b[0]; _retVsSizeBestFitLineSlope = b[1]; //Calculate best fit line for the return vs length scatter chart MathUtils.MLR( Data.tradeLengthsVsReturns.Select(x => x.ret).ToList(), Data.tradeLengthsVsReturns.Select(x => x.length).ToList(), out b, out rsq); _retVsLengthBestFitLineConstant = b[0]; _retVsLengthBestFitLineSlope = b[1]; //Histograms try { var sharpeHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.Sharpe), 20); MCSharpeHistogramBuckets = sharpeHistogram.GetBuckets(); var marHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.MAR), 20); MCMARHistogramBuckets = marHistogram.GetBuckets(); var kRatioHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.KRatio), 20); MCKRatioHistogramBuckets = kRatioHistogram.GetBuckets("0"); } catch { } //Yeah this is bad, there's a ton of stupid errors that are not easy to check for...re-do this in the future //Benchmark stats if (Settings.Benchmark != null) { BenchmarkAlpha = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).alpha; BenchmarkBeta = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).beta; BenchmarkCorrelation = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).correlation; BenchmarkRSquare = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).rsquare; BenchmarkInformationRatio = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).informationRatio; BenchmarkActiveReturn = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).activeReturn; BenchmarkTrackingError = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).trackingError; BenchmarkInstrument = Settings.Benchmark.Name; } //avg cumulative winner/loser returns by day in trade AvgCumulativeWinnerRets = Enumerable.Select(data .AverageDailyRets .Where(x => !x.IswinnersRetsNull()), x => new KeyValuePair <int, double>(x.day, x.winnersRets)) .ToList(); AvgCumulativeLoserRets = Enumerable.Select(data .AverageDailyRets .Where(x => !x.IslosersRetsNull()), x => new KeyValuePair <int, double>(x.day, x.losersRets)) .ToList(); //build plot models CreatePLByStrategyChartModel(); CreateCapitalUsageByStrategyChartModel(); CreateRelativeCapitalUsageByStrategyChartModel(); CreateRoacByStrategyChartModel(); CreateMdsChartModel(); CreateTradeRetsByDayAndHourChartModel(); }
public PerformanceReportViewModel(filterReportDS data, ReportSettings settings, IDialogCoordinator dialogService) : base(null) { Data = data; Settings = settings; CopyChart = new RelayCommand<PlotView>(x => x.CopyToClipboard()); SaveChart = new RelayCommand<PlotView>(x => { try { x.SaveAsPNG(); } catch (Exception ex) { dialogService.ShowMessageAsync(this, "Error saving image", ex.Message); } }); //Calculate best fit line for the return vs size scatter chart double rsq; double[] b; MathUtils.MLR( Data.positionSizesVsReturns.Select(x => x.size).ToList(), Data.positionSizesVsReturns.Select(x => x.ret).ToList(), out b, out rsq); _retVsSizeBestFitLineConstant = b[0]; _retVsSizeBestFitLineSlope = b[1]; //Calculate best fit line for the return vs length scatter chart MathUtils.MLR( Data.tradeLengthsVsReturns.Select(x => x.ret).ToList(), Data.tradeLengthsVsReturns.Select(x => x.length).ToList(), out b, out rsq); _retVsLengthBestFitLineConstant = b[0]; _retVsLengthBestFitLineSlope = b[1]; //Histograms try { var sharpeHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.Sharpe), 20); MCSharpeHistogramBuckets = sharpeHistogram.GetBuckets(); var marHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.MAR), 20); MCMARHistogramBuckets = marHistogram.GetBuckets(); var kRatioHistogram = new Histogram(Enumerable.Select(Data.MCRatios, x => x.KRatio), 20); MCKRatioHistogramBuckets = kRatioHistogram.GetBuckets("0"); } catch { } //Yeah this is bad, there's a ton of stupid errors that are not easy to check for...re-do this in the future //Benchmark stats if (Settings.Benchmark != null) { BenchmarkAlpha = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).alpha; BenchmarkBeta = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).beta; BenchmarkCorrelation = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).correlation; BenchmarkRSquare = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).rsquare; BenchmarkInformationRatio = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).informationRatio; BenchmarkActiveReturn = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).activeReturn; BenchmarkTrackingError = ((filterReportDS.benchmarkStatsRow)Data.benchmarkStats.Rows[0]).trackingError; BenchmarkInstrument = Settings.Benchmark.Name; } //avg cumulative winner/loser returns by day in trade AvgCumulativeWinnerRets = Enumerable.Select(data .AverageDailyRets .Where(x => !x.IswinnersRetsNull()), x => new KeyValuePair<int, double>(x.day, x.winnersRets)) .ToList(); AvgCumulativeLoserRets = Enumerable.Select(data .AverageDailyRets .Where(x => !x.IslosersRetsNull()), x => new KeyValuePair<int, double>(x.day, x.losersRets)) .ToList(); //build plot models CreatePLByStrategyChartModel(); CreateCapitalUsageByStrategyChartModel(); CreateRelativeCapitalUsageByStrategyChartModel(); CreateRoacByStrategyChartModel(); CreateMdsChartModel(); CreateTradeRetsByDayAndHourChartModel(); }