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