Build 4.0.0.86 of xlsgen adds a new trend line to the list of existing trend lines : moving median.



Moving median (N=5 values in the data window)

The moving median, as the name suggests, builds on a median computed inside a moving data window (subset of the series of data). The series of the median values as the window moves makes the trend line. The median is a measure by which half of the data is below it, and the other half is above it. Wikipedia article. Compared to a moving average, the moving median reflects a lot less brutal changes, which are sometimes viewed, depending on the business using it, as skewed or useless values.

And here is how to create it :

C/C++ code

xlsgen::IXlsWorksheetPtr wksht001 = workbook->AddWorksheet( L"Sheet1" ); // data wksht001->Cell[2][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">50</font>"; wksht001->Cell[3][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">30</font>"; wksht001->Cell[4][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">100</font>"; wksht001->Cell[5][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">40</font>"; wksht001->Cell[6][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">200</font>"; wksht001->Cell[7][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">70</font>"; wksht001->Cell[8][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">100</font>"; wksht001->Cell[9][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">10</font>"; wksht001->Cell[10][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">150</font>"; wksht001->Cell[11][2]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">250</font>"; // chart xlsgen::IXlsChartPtr chart001s0 = wksht001->NewChart(xlsgen::charttype_bar2D, 5, 1, 20, 8); xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = chart001s0->DynamicDataSource->AddSerie(); serie001s0ss0->SeriesValuesFormula = L"Sheet1!$B$2:$B$11"; chart001s0->SeriesByIndex[1]->TrendLines[1]->Show = xlsgen::charttrendline_movingmedian; // moving median trendline chart001s0->SeriesByIndex[1]->TrendLines[1]->MovingAveragePeriods = 5; // N=5 wksht001->Export->ExportAsPDF(L"moving_median.pdf"); // ask xlsgen to compute and render it workbook->Close();

Build 4.0.0.85 of xlsgen makes it possible to add more than one trendline to a series of data. Before this build, for 3 series of data there could be at most 3 trendlines attached to them, so up to 6 lines in total.


One series of data and 4 trendlines attached to it (moving averages with different parameters)

This works for XLS and XLSX/XLSB files.

And here is how to code it :

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"input\\EURUSD1.csv", L"multiple_trendlines.xlsx" );

xlsgen::IXlsChartPtr chart = wbk->WorksheetByIndex[1]->NewChart(xlsgen::charttype_line2D,
					     1, //row1
					     2, //col1 
					     28, //row2
					     15 //col2
					     );

xlsgen::IXlsChartDynamicDataSourcePtr datasource001s0 = chart->DynamicDataSource;

xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = datasource001s0->AddSerie();
serie001s0ss0->SeriesValuesFormula = L"=R1C3:R100C3";
chart->SeriesByIndex[1]->TrendLines[1]->Show = xlsgen::charttrendline_movingaverage;
chart->SeriesByIndex[1]->TrendLines[1]->MovingAveragePeriods = 5;
chart->SeriesByIndex[1]->TrendLines[1]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[1]->Options->Color = 0x4040C0;
chart->SeriesByIndex[1]->TrendLines[2]->Show = xlsgen::charttrendline_movingaverage;
chart->SeriesByIndex[1]->TrendLines[2]->MovingAveragePeriods = 10;
chart->SeriesByIndex[1]->TrendLines[2]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[2]->Options->Color = 0xC04040;
chart->SeriesByIndex[1]->TrendLines[3]->Show = xlsgen::charttrendline_movingaverage;
chart->SeriesByIndex[1]->TrendLines[3]->MovingAveragePeriods = 20;
chart->SeriesByIndex[1]->TrendLines[3]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[3]->Options->Color = 0x40C040;
chart->SeriesByIndex[1]->TrendLines[4]->Show = xlsgen::charttrendline_movingaverage;
chart->SeriesByIndex[1]->TrendLines[4]->MovingAveragePeriods = 40;
chart->SeriesByIndex[1]->TrendLines[4]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[4]->Options->Color = 0xA040A0;


chart->XAxis[xlsgen::chartaxis_primary]->Scale->ItemsBetweenTickMarkLabels = 10;

wbk->Close();

Build 4.0.0.84 of xlsgen is a fix pack for improving the rendering of charts and other objects. The improvements include :

- charts : support for item spacing on the X-axis. This property allows to avoid drawing a label next to each tick, which is useful when the chart in question has a large data source attached to it.

- charts : border line weight takes into account the line weight inferred from the chart style (48 chart styles) whenever there isn't a custom setting.

- better preserve and render border styles in text boxes, vector shapes, pictures and chart elements. There was a discrepancy between chart border styles, MSO border styles and XLSX/XLSB border styles.