Chart trend lines

xlsgen > overview > Chart trend lines

Customizing chart trend lines

The chart trend lines are optional lines attached to an arbitrary series of values which help identify a trend.

A trend line is accessed from a series object thanks to the TrendLine property. Here are the following properties of trend line interface :

Common options.
Type of regression algorithm. While the linear regression is the most obvious, all other regression algorithms are exposed.
Ability to customize the title of the associated legend entry.
Change trend line options including backward/forward forecast, intercept value, display equation and so on.

More than one trendline can be attached to a series of data :

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

Additional trendline types

xlsgen exposes a number of additional trendlines that are not currently supported by Excel.

the moving median.
the exponential moving average.
support and resistance

Moving median

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.

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

And here is how to create it :

Java code

XlsWorksheet wksht001 = workbook.AddWorksheet( "Sheet1" );

// data
wksht001.getCell(2,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">50</font>");
wksht001.getCell(3,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">30</font>");
wksht001.getCell(4,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">100</font>");
wksht001.getCell(5,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">40</font>");
wksht001.getCell(6,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">200</font>");
wksht001.getCell(7,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">70</font>");
wksht001.getCell(8,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">100</font>");
wksht001.getCell(9,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">10</font>");
wksht001.getCell(10,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">150</font>");
wksht001.getCell(11,2).putHtmlFloat("<font color=#000000 size=11 name=\"Calibri\">250</font>");

// chart
XlsChart chart001s0 = wksht001.NewChart(charttype_bar2D, 5, 1, 20, 8);

XlsChartDynamicDataSourceSeries serie001s0ss0 = chart001s0.getDynamicDataSource().AddSerie();
serie001s0ss0.putSeriesValuesFormula("Sheet1!$B$2:$B$11");
chart001s0.getSeriesByIndex(1).getTrendLines(1).putShow(charttrendline_movingmedian); // moving median trendline
chart001s0.getSeriesByIndex(1).getTrendLines(1).putMovingAveragePeriods(5); // N=5

wksht001.getExport().ExportAsPDF("moving_median.pdf"); // ask xlsgen to compute and render it

workbook.Close();

VB code

Dim wksht001 As IXlsWorksheet
Set wksht001 = workbook.AddWorksheet( "Sheet1" )

' data
wksht001.Cell(2,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">50</font>"
wksht001.Cell(3,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">30</font>"
wksht001.Cell(4,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">100</font>"
wksht001.Cell(5,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">40</font>"
wksht001.Cell(6,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">200</font>"
wksht001.Cell(7,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">70</font>"
wksht001.Cell(8,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">100</font>"
wksht001.Cell(9,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">10</font>"
wksht001.Cell(10,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">150</font>"
wksht001.Cell(11,2).HtmlFloat = "<font color=#000000 size=11 name=""Calibri"">250</font>"

' chart
Dim chart001s0 As IXlsChart 
Set chart001s0 = wksht001.NewChart(charttype_bar2D, 5, 1, 20, 8)

Dim serie001s0ss0 As IXlsChartDynamicDataSourceSeries 
Set serie001s0ss0 = chart001s0.DynamicDataSource.AddSerie
serie001s0ss0.SeriesValuesFormula = "Sheet1!$B$2:$B$11"
chart001s0.SeriesByIndex(1).TrendLines(1).Show = charttrendline_movingmedian ' moving median trendline
chart001s0.SeriesByIndex(1).TrendLines(1).MovingAveragePeriods = 5 ' N=5

wksht001.Export.ExportAsPDF("moving_median.pdf") ' ask xlsgen to compute and render it

workbook.Close

C# code

IXlsWorksheet wksht001 = workbook.AddWorksheet( "Sheet1" );

// data
wksht001.get_Cell(2,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">50</font>";
wksht001.get_Cell(3,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">30</font>";
wksht001.get_Cell(4,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">100</font>";
wksht001.get_Cell(5,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">40</font>";
wksht001.get_Cell(6,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">200</font>";
wksht001.get_Cell(7,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">70</font>";
wksht001.get_Cell(8,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">100</font>";
wksht001.get_Cell(9,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">10</font>";
wksht001.get_Cell(10,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">150</font>";
wksht001.get_Cell(11,2).HtmlFloat = "<font color=#000000 size=11 name=\"Calibri\">250</font>";

// chart
IXlsChart chart001s0 = wksht001.NewChart(charttype_bar2D, 5, 1, 20, 8);

IXlsChartDynamicDataSourceSeries serie001s0ss0 = chart001s0.DynamicDataSource.AddSerie();
serie001s0ss0.SeriesValuesFormula = "Sheet1!$B$2:$B$11";
chart001s0.SeriesByIndex(1).TrendLines(1).Show = charttrendline_movingmedian; // moving median trendline
chart001s0.SeriesByIndex(1).TrendLines(1).MovingAveragePeriods = 5; // N=5

wksht001.Export.ExportAsPDF("moving_median.pdf"); // ask xlsgen to compute and render it

workbook.Close();

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

Exponential moving average

The exponential moving average, as the name suggests, is an exponentially decreasing moving average which means recent data in the moving window has more weight than older data.

The exponential moving average is closer visually to the series data than the regular moving average and reveals trends like the moving average. It can improve the decision based on the trend analysis.

Exponential moving average (N=20 values in the data window) versus moving average

Creating the trendline is straight forward :

C/C++ code

xlsgen::IXlsWorkbookPtr workbook = engine->Open( L"EURUSD1.csv", L"exponential_moving_average.xls" );

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

xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = chart->DynamicDataSource->AddSerie();
serie001s0ss0->SeriesValuesFormula = L"=R1C3:R100C3";

chart->SeriesByIndex[1]->TrendLines[1]->Show = xlsgen::charttrendline_exponentialmovingaverage; // exponential moving average trendline
chart->SeriesByIndex[1]->TrendLines[1]->MovingAveragePeriods = 20; // N=20

workbook->WorksheetByIndex[1]->Export->ExportAsPDF(L"exponential_moving_average.pdf"); // ask xlsgen to compute and render it

workbook->Close();

Support and resistance

Support and resistance trendlines are used in stock trading to delimit peer pressure for, respectively, selling and buying stocks. Any time the stock data crosses the support line or the resistance line, this acts as a trade signal. Support and resistance play an opposite role.

The support line marks the signal for selling stocks, or for shorting stocks (i.e. buying on the prediction that stocks will go lower). The resistance line marks the signal for buying stocks, or for longing stocks (i.e. buying on the prediction that stocks will go higher).

Support (red) and resistance (green) trend lines

xlsgen completely hides the underlying complexity of calculating support and resistance trendlines. Those lines are calculated based on 10-period smoothed data and the calculation of local minimum and maximum spots which are then interpolated together.

C/C++ code

xlsgen::IXlsWorkbookPtr workbook = engine->Open( L"EURUSD1.csv", L"support_resistance.xlsx" );

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

xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = chart->DynamicDataSource->AddSerie();
serie001s0ss0->SeriesValuesFormula = L"=R1C3:R100C3";

chart->SeriesByIndex[1]->TrendLines[1]->Show = xlsgen::charttrendline_support; // Support trendline

chart->SeriesByIndex[1]->TrendLines[1]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[1]->Options->Style = xlsgen::chartborderstyle_dot;
chart->SeriesByIndex[1]->TrendLines[1]->Options->Weight = xlsgen::chartborderweight_single;
chart->SeriesByIndex[1]->TrendLines[1]->Options->Color = 0xFF0000;

chart->SeriesByIndex[1]->TrendLines[2]->Show = xlsgen::charttrendline_resistance; // Resistance trendline

chart->SeriesByIndex[1]->TrendLines[2]->Options->Type = xlsgen::chartbordertype_custom;
chart->SeriesByIndex[1]->TrendLines[2]->Options->Style = xlsgen::chartborderstyle_dot;
chart->SeriesByIndex[1]->TrendLines[2]->Options->Weight = xlsgen::chartborderweight_single;
chart->SeriesByIndex[1]->TrendLines[2]->Options->Color = 0x00FF00;

workbook->WorksheetByIndex[1]->Export->ExportAsPDF(L"support_resistance.pdf"); // ask xlsgen to compute and render it

workbook->Close();