Creating a stock chart in Excel VBA

Stock Chart is used to represent stock prices or any data that includes open, high, low, and close values over a set period. In Excel, we can create a stock chart using VBA by arranging the data appropriately and then using Excel’s built-in charting features to create the chart.

Step-by-Step Explanation:

1. Data Preparation

   The data for a stock chart typically requires 4 columns:

   – Date (or Time Period)

   – Open Price

   – High Price

   – Low Price

   – Close Price

   The data must be structured properly, with each row representing one day of stock data.

2. Creating the Chart

   Excel has a built-in « Stock » chart type, which you can access through VBA. The stock chart supports several variations (Open-High-Low-Close, Volume-High-Low-Close, etc.), but we will focus on the Open-High-Low-Close version in this example.

3. VBA Code to Create the Stock Chart

Here’s a VBA code that demonstrates how to create a Stock Chart in Excel:

Sub CreateStockChart()
    Dim ws As Worksheet
    Dim rng As Range
    Dim chartObj As ChartObject
    ' Set reference to the current active sheet
    Set ws = ActiveSheet   
    ' Define the range containing the data
    ' Assuming the data is in columns A to E, with headers in row 1 and data starting from row 2
    Set rng = ws.Range("A1:E10") ' Adjust the range to your actual data   
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)   
    ' Set the chart data source
    chartObj.Chart.SetSourceData Source:=rng   
    ' Set the chart type to Stock Chart (OHLC)
    chartObj.Chart.ChartType = xlStockOHLC   
    ' Set chart title
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Stock Price Chart"   
    ' Set axis titles
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Date"   
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Price"   
    ' Format the chart (optional)
    With chartObj.Chart
        .Axes(xlCategory).CategoryNames = ws.Range("A2:A10") ' Set category names to dates
        .Axes(xlValue).MinimumScale = 0 ' Set minimum value for price axis
        .Axes(xlValue).MaximumScale = 100 ' Adjust as needed
    End With   
End Sub

 Explanation of the Code:

– Setting the Range:

  The code assumes that the data for the stock chart is in columns A to E, where column A contains the Date, and columns B, C, D, and E contain Open, High, Low, and Close values, respectively. You can adjust the range `ws.Range(« A1:E10 »)` to match your actual data.

– Creating the Chart:

  The `ws.ChartObjects.Add` method is used to create a new chart on the active sheet. The `Left`, `Width`, `Top`, and `Height` parameters specify the position and size of the chart.

– Setting the Chart Type:

  `chartObj.Chart.ChartType = xlStockOHLC` tells Excel to use the OHLC (Open-High-Low-Close) version of the stock chart.

– Chart Title and Axis Titles:

  Titles for the chart and axes are added using the `Chart.HasTitle` and `Axes.HasTitle` properties. You can modify the text for the titles according to your preference.

– Formatting the Axes:

  The `CategoryNames` property of the x-axis is set to the dates from column A (`ws.Range(« A2:A10 »)`). Additionally, you can adjust the minimum and maximum scale of the y-axis for better visualization of stock prices.

Customization:

– Data Range: You can adjust the range `A1:E10` to include more rows of stock data. Ensure the data is in the correct format.

– Chart Type: You can change the chart type to other stock variations (e.g., Volume-High-Low-Close) by setting `ChartType = xlStockVHLC`.

– Formatting: You can customize the chart appearance further, such as changing the colors of the price bars, adding gridlines, or adjusting the axis formatting.

How to Run the Code:

1. Open Excel and press `Alt + F11` to open the VBA editor.
2. Insert a new module by clicking `Insert > Module`.
3. Paste the above code into the module.
4. Press `F5` to run the macro or assign it to a button in your workbook.

This code will generate a stock chart based on the data you provide and display it in your worksheet.