Plotting stock charts in excel sheet using xlsxwriter module in python

Factors such as data analysis and growth rate monitoring are very important when it comes to plotting stock charts. For any business to flourish and expand, the right strategy is needed. These strategies are built on the back of a deep fundamental research. Python programming helps us to create and compare data which in turn can be used to study a business model. Python offers several methods and functions through which we can plot graphs, analyze growth and introspect the sudden changes.

In this article we will be discussing about one such operation where we will plot a stock chart in excel sheet using python programming. We'll explore the xlsxwriter module and demonstrate how to create professional stock charts directly in Excel files.

What is xlsxwriter module?

It is an interactive python module that helps the coder to work on excel sheets using python programming. We can append, delete and perform several manipulative operations without actually using the spreadsheet itself. We can install this module by passing the following statement on the command line ?

pip install XlsxWriter

Once the module is installed, we can import it on the IDE and start working on the project.

Creating an Excel Document

We will use Workbook() method to create an excel document. We will pass a non-optional argument that will serve as the name of the document. We need to pass the correct extension along with the filename in order to prevent any error ?

import xlsxwriter

# Create a new workbook and worksheet
workbook = xlsxwriter.Workbook('stock_chart.xlsx')
worksheet = workbook.add_worksheet()

print("Excel workbook created successfully!")

Excel workbook created successfully!

Key Methods for Stock Charts

The essential methods we will use are ?

add_format() ? This method specifies the format for a particular object such as boldness, filters, date, font etc.

add_chart() ? This method helps us create charts for spreadsheets. We specify the type for the chart with the help of "type" parameter ?

chart = workbook.add_chart({'type': 'stock'})

add_series() ? This method creates an entire series for the data to be stored in the cells. We can specify the formatting and data ranges using Excel notation.

Complete Stock Chart Example

The following example creates an excel sheet with a comprehensive stock chart showing employee package progression ?

import xlsxwriter

# Create workbook and worksheet
workbook = xlsxwriter.Workbook('employee_stock_chart.xlsx')
worksheet = workbook.add_worksheet()

# Add formatting
bold_format = workbook.add_format({'bold': True})

# Create stock chart
chart = workbook.add_chart({'type': 'stock'})

# Define headers
headers = ["Name", "Starting Package", "Current Package", "Expected Growth Points"]

# Employee data
data = [
    ['Rajesh', 'Ravi', 'Arun', 'Neha', 'Devi'],
    [25000, 28000, 36000, 22000, 40000],
    [28000, 30000, 40000, 25000, 50000],
    [12, 10, 15, 8, 20]
]

# Write headers
worksheet.write_row("A1", headers, bold_format)

# Write data to worksheet
for row in range(5):
    worksheet.write(row + 1, 0, data[0][row])  # Names
    worksheet.write(row + 1, 1, data[1][row])  # Starting package
    worksheet.write(row + 1, 2, data[2][row])  # Current package
    worksheet.write(row + 1, 3, data[3][row])  # Growth points

# Set column width
worksheet.set_column("A:D", 15)

# Add data series to chart
chart.add_series({
    'categories': '=Sheet1!$A$2:$A$6',
    'values': '=Sheet1!$B$2:$B$6',
    'name': 'Starting Package'
})

chart.add_series({
    'categories': '=Sheet1!$A$2:$A$6', 
    'values': '=Sheet1!$C$2:$C$6',
    'name': 'Current Package'
})

chart.add_series({
    'categories': '=Sheet1!$A$2:$A$6',
    'values': '=Sheet1!$D$2:$D$6',
    'name': 'Growth Points'
})

# Configure chart appearance
chart.set_title({'name': 'Employee Package Analysis'})
chart.set_x_axis({'name': 'Employee Names'})
chart.set_y_axis({'name': 'Package Value / Growth Points'})

# Insert chart into worksheet
worksheet.insert_chart("F2", chart)

# Close workbook
workbook.close()

print("Stock chart created successfully in 'employee_stock_chart.xlsx'")

Stock chart created successfully in 'employee_stock_chart.xlsx'

How the Chart Works

The code above performs these key operations:

• Data Setup ? We create headers and organize employee salary data in lists

• Worksheet Population ? Data is written to specific cells using proper indexing

• Chart Configuration ? Three data series are added representing different salary metrics

• Chart Formatting ? Titles and axis labels are set for better readability

• Chart Insertion ? The chart is placed at cell F2 for optimal viewing

Important Parameters

Conclusion

The xlsxwriter module provides powerful capabilities for creating stock charts in Excel files programmatically. This approach enables automated report generation and data visualization without manually creating spreadsheets. Use this method when you need to generate multiple charts or automate financial reporting processes.