Create a candlestick chart in Excel VBA

1. Open the VBA Editor

To add this VBA code in Excel:

• Open Excel.
• Press Alt + F11 to open the VBA editor.
• Go to Insert > Module to add a new module.
• Paste the code below into the module window.

2. VBA Code to Create a Candlestick Chart

Sub CreateCandlestickChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Reference to the active sheet
    Set ws = ActiveSheet   
    ' Define the range of data to use for the chart
    ' Example data: Columns A (Date), B (Open), C (High), D (Low), E (Close)
    Set dataRange = ws.Range("A1:E10") ' Adjust this range according to your data   
    ' Create the chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Set the chart type to candlestick chart (OHLC)
    chartObj.Chart.ChartType = xlStockOHLC ' Using OHLC chart type for candlestick   
    ' Add titles for each axis and the chart
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Candlestick Chart"   
    ' Customize the X-axis (date axis)
    With chartObj.Chart.Axes(xlCategory)
        .CategoryNames = ws.Range("A2:A10") ' Date range
        .TickLabelPosition = xlLow
    End With   
    ' Customize the Y-axis (value axis)
    With chartObj.Chart.Axes(xlValue)
        .MinimumScale = 0 ' Minimum value (adjust based on your data)
        .MaximumScale = 100 ' Maximum value (adjust based on your data)
    End With   
    ' Customize the colors of the candlesticks
    With chartObj.Chart.SeriesCollection(1)
        .UpFill.ForeColor.RGB = RGB(0, 255, 0) ' Green for bullish candles
        .DownFill.ForeColor.RGB = RGB(255, 0, 0) ' Red for bearish candles
        .Border.Color = RGB(0, 0, 0) ' Black border
    End With   
    ' Disable the legend (optional)
    chartObj.Chart.HasLegend = False
End Sub

3. Explanation of the Code

Variable Declarations:

• • ws is a reference to the active worksheet.
  • chartObj is the object that will hold the created chart.
  • dataRange is the range of data that will be used to create the chart.

Data Range:

• • The candlestick chart requires four types of data:
    • Open
    • High
    • Low
    • Close
  • In this example, the data is in columns A to E, from row 1 to row 10 (A1:E10). You can adjust the range according to your dataset.
• Creating the Chart:
  • The chart is created using the ChartObjects.Add method. This adds a chart to the active worksheet.
  • SetSourceData Source:=dataRange sets the data range for the chart.
• Chart Type:
  • The chart is set to be a candlestick chart using the xlStockOHLC chart type.
• Customizing the Axes:
  • The category axis (X-axis) represents the dates. The CategoryNames property sets the dates from column A (A2:A10).
  • The value axis (Y-axis) is configured with minimum and maximum values. You can adjust the minimum and maximum scale values based on your data.
• Customizing Candlestick Colors:
  • Bullish candles (closing price > opening price) are colored green, while bearish candles (closing price < opening price) are colored red.
  • The border of the candles is set to black.
• Disabling the Legend:
  • The legend is turned off with HasLegend = False. You can enable it if you prefer.

4. How to Use the Code

• After pasting the code, you can run it by pressing F5 in the VBA editor, or you can create a button on your worksheet and assign this macro to the button.
• Once the macro is executed, a candlestick chart will be generated on the active sheet using the specified data range.

5. Sample Data

Here is an example of the data you can use to test the code:

Don’t forget to adjust the data range in the code to match your own dataset.

Conclusion

This code creates a simple and customizable candlestick chart. You can adjust the data range, colors, and other settings to fit your specific needs.