How to: Create a Stock Chart
- 9 minutes to read
This topic demonstrates how to create stock charts using the continuous and noncontiguous cell ranges as data sources.
Use a Continuous Source Range to Create a Stock Chart
Depending on the stock chart type, arrange columns or rows in the source range in the same order as the stock chart name:
- High-Low-Close
- Open-High-Low-Close
- Volume-High-Low-Close
- Volume-Open-High-Low-Close
For example, to create an Open-High-Low-Close stock chart, organize columns containing the stock data in the following order: open, high, low and close (see the image below).
After you arrange data in the source range, call the ChartCollection.Add method and pass the specified range as a parameter to create a chart. Refer to the How to: Create and Modify a Chart example for details on how to create a chart in code and adjust its settings.
Worksheet worksheet = workbook.Worksheets["chartStock"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.StockOpenHighLowClose, worksheet["B2:F7"]);
chart.TopLeftCell = worksheet.Cells["H2"];
chart.BottomRightCell = worksheet.Cells["N15"];
// Display the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("NASDAQ:MSFT");
// Hide the legend.
chart.Legend.Visible = false;
// Access the value axis by its index in the primary axis collection.
Axis axis = chart.PrimaryAxes[1];
// Add a title to the value axis.
axis.Title.Visible = true;
axis.Title.SetValue("Price in USD");
Use a Noncontiguous Source Range to Create a Stock Chart
You can use the SeriesCollection.Add method to add a separate data series to a chart if a stock chart’s data is in non-adjacent columns. The number and order of series depend on the stock chart type:
High-Low-Close Stock Chart
To create this chart, add three series to the SeriesCollection collection in the following order: high, low, and close.
If you save the created chart and open it in Microsoft Excel, the chart is not shown as a standard High-Low-Close chart. Instead, it displays three lines for each stock price and high-low lines connecting the high and low price values.
To make the chart display correctly in Microsoft Excel, adjust the series’ appearance as described below:
- Use the Series.Outline.SetNoFill method (ShapeOutlineFill.SetNoFill) to hide lines for the high, low and close data series.
- Set the Series.Marker.Symbol (Marker.Symbol) property to MarkerStyle.None to hide the high and low data series’ markers.
- Use the Series.Marker options to change the data markers’ appearance for the close data series. In this example, the marker style (Marker.Symbol) is set to MarkerStyle.Dash and the marker color is black.
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.StockHighLowClose);
chart.TopLeftCell = worksheet.Cells["K2"];
chart.BottomRightCell = worksheet.Cells["Q16"];
// Add a series of high values.
Series highSeries = chart.Series.Add(worksheet["F2"], worksheet["B3:B7"], worksheet["F3:F7"]);
// Hide the series line.
highSeries.Outline.SetNoFill();
// Hide the series markers.
highSeries.Marker.Symbol = MarkerStyle.None;
// Add a series of low values.
Series lowSeries = chart.Series.Add(worksheet["G2"], worksheet["B3:B7"], worksheet["G3:G7"]);
// Hide the series line.
lowSeries.Outline.SetNoFill();
// Hide the series markers.
lowSeries.Marker.Symbol = MarkerStyle.None;
// Add a series of close values.
Series closeSeries = chart.Series.Add(worksheet["D2"], worksheet["B3:B7"], worksheet["D3:D7"]);
// Hide the series line.
closeSeries.Outline.SetNoFill();
// Change the data marker style for the series.
closeSeries.Marker.Symbol = MarkerStyle.Dash;
// Set the data marker color to black.
closeSeries.Marker.Outline.SetSolidFill(System.Drawing.Color.Black);
// Display the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("NASDAQ:MSFT");
// Hide the legend.
chart.Legend.Visible = false;
// Access the value axis by its index in the primary axis collection.
Axis axis = chart.PrimaryAxes[1];
// Add a title to the value axis.
axis.Title.Visible = true;
axis.Title.SetValue("Price in USD");
Open-High-Low-Close Stock Chart
Add four series to the SeriesCollection collection in the following order: open, high, low, and close to create an Open-High-Low-Close stock chart.
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.StockOpenHighLowClose);
chart.TopLeftCell = worksheet.Cells["K2"];
chart.BottomRightCell = worksheet.Cells["Q16"];
// Add a series of open values.
chart.Series.Add(worksheet["C2"], worksheet["B3:B7"], worksheet["C3:C7"]);
// Add a series of high values.
chart.Series.Add(worksheet["F2"], worksheet["B3:B7"], worksheet["F3:F7"]);
// Add a series of low values.
chart.Series.Add(worksheet["G2"], worksheet["B3:B7"], worksheet["G3:G7"]);
// Add a series of close values.
chart.Series.Add(worksheet["D2"], worksheet["B3:B7"], worksheet["D3:D7"]);
// Display the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("NASDAQ:MSFT");
// Hide the legend.
chart.Legend.Visible = false;
// Access the value axis by its index in the primary axis collection.
Axis axis = chart.PrimaryAxes[1];
// Add a title to the value axis.
axis.Title.Visible = true;
axis.Title.SetValue("Price in USD");
Volume-High-Low-Close Stock Chart
A Volume-High-Low-Close chart uses two ChartView objects of the following types: the ChartType.ColumnClustered type to display trading volume and the ChartType.StockHighLowClose type to show stock prices. Use the ChartView.Series.Add method to add data series to each chart view. The ChartType.StockHighLowClose view requires three data series in the following order: high, low, and close.
To make the chart display correctly in Microsoft Excel, adjust the high, low and close series’ appearance as described in the High-Low-Close Stock Chart section.
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.StockVolumeHighLowClose);
chart.TopLeftCell = worksheet.Cells["K2"];
chart.BottomRightCell = worksheet.Cells["Q16"];
// Add a series of volume values.
chart.Views[0].Series.Add(worksheet["I2"], worksheet["B3:B7"], worksheet["I3:I7"]);
// Add a series of high values.
Series highSeries = chart.Views[1].Series.Add(worksheet["F2"], worksheet["B3:B7"], worksheet["F3:F7"]);
// Hide the series line.
highSeries.Outline.SetNoFill();
// Hide the series markers.
highSeries.Marker.Symbol = MarkerStyle.None;
// Add a series of low values.
Series lowSeries = chart.Views[1].Series.Add(worksheet["G2"], worksheet["B3:B7"], worksheet["G3:G7"]);
// Hide the series line.
lowSeries.Outline.SetNoFill();
// Hide the series markers.
lowSeries.Marker.Symbol = MarkerStyle.None;
// Add a series of close values.
Series closeSeries = chart.Views[1].Series.Add(worksheet["D2"], worksheet["B3:B7"], worksheet["D3:D7"]);
// Hide the series line.
closeSeries.Outline.SetNoFill();
// Change the data marker style for the series.
closeSeries.Marker.Symbol = MarkerStyle.Dash;
// Set the data marker color to black.
closeSeries.Marker.Outline.SetSolidFill(System.Drawing.Color.Black);
// Display the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("NASDAQ:MSFT");
// Hide the legend.
chart.Legend.Visible = false;
// Access the value axis by its index in the secondary axis collection.
Axis axis = chart.SecondaryAxes[1];
// Add a title to the value axis.
axis.Title.Visible = true;
axis.Title.SetValue("Price in USD");
Volume-Open-High-Low-Close Stock Chart
A Volume-Open-High-Low-Close chart uses two ChartView objects of the following types: the ChartType.ColumnClustered type to display trading volume and the ChartType.StockOpenHighLowClose type to show stock prices. Use the ChartView.Series.Add method to add data series to each chart view. The ChartType.StockOpenHighLowClose view requires four data series in the following order: open, high, low, and close.
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.StockVolumeOpenHighLowClose);
chart.TopLeftCell = worksheet.Cells["K2"];
chart.BottomRightCell = worksheet.Cells["Q16"];
// Add a series of volume values.
chart.Views[0].Series.Add(worksheet["I2"], worksheet["B3:B7"], worksheet["I3:I7"]);
// Add a series of open values.
chart.Views[1].Series.Add(worksheet["C2"], worksheet["B3:B7"], worksheet["C3:C7"]);
// Add a series of high values.
chart.Views[1].Series.Add(worksheet["F2"], worksheet["B3:B7"], worksheet["F3:F7"]);
// Add a series of low values.
chart.Views[1].Series.Add(worksheet["G2"], worksheet["B3:B7"], worksheet["G3:G7"]);
// Add a series of close values.
chart.Views[1].Series.Add(worksheet["D2"], worksheet["B3:B7"], worksheet["D3:D7"]);
// Display the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("NASDAQ:MSFT");
// Hide the legend.
chart.Legend.Visible = false;
// Access the value axis by its index in the secondary axis collection.
Axis axis = chart.SecondaryAxes[1];
// Add a title to the value axis.
axis.Title.Visible = true;
axis.Title.SetValue("Price in USD");