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, analyse 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. Before we dive deep into the topic, let’s quickly go through the overview of this article. This article is divided into two sections −

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.

How to create an excel document?

We will use “workbook()” method to create an excel document. We will pass a nonoptional 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
newwork = xlsxwriter.Workbook('new_document.xlsx')
newsheet = newwork.add_worksheet()

Here, we imported the “XlsxWriter” module and used the workbook() function to generate a new document. We also used “add_worksheet()” to create a new worksheet for the operation.

There are several other workbook objects that can used to add details to the excel sheet. We will only discuss the methods that are necessary for stock chart plotting.

Methods Used

The methods that we will use are −

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

add_chart() − This method will help us to create charts for spreadsheets. We will specify the type for the cart with the help of “type” command.

{ ‘type’ : ‘stock’ } 

add_series() − This method will create an entire series for the data to be stored in the cells. We can even specify the formatting here. Here, the sheet information is passed through “! $” commands.

Example

The Following example prints an excel sheet with a stock chart. Now we will understand the details of this stock chart and how we used the workbook function and its methods.

Here,

• We imported the “xlsxwriter module” and and created an excel document named “new_document”.

• We added a new worksheet in the form of “newssheet”.

• We added a bold format with the help of “add_format” method.

• We added a stock chart with the help of “add_chart” method.

• We initialised the titles or headings for the excel document.

• We passed the data that needs to be uploaded on the document consisting of information.

• We set the column range for the document and added series for the chart.

• The add_series method helps us to add a data of series in the sheet. We pass the information related the each sheet in the form of “$ !” signs.

• For example if we want to given information for cell A2 to A6, we pass the command “!$A$2 : $A$6”.

• After this, we added information related to the chart. We set the chart title name with the help of “set_title()” method.

• We set the X-axis and Y-AXIS information for plotting the stock chart. In X-axis we passed the names and in the Y-Axis we passed the growth points

• Plotting is done with the help of “set_x_axis()” and set_y_axis() methods

• At last, we close the workbook by passing the command “newwork.close()”.

import xlsxwriter
newwork = xlsxwriter.Workbook('new_document.xlsx')
newsheet = newwork.add_worksheet()
Fbold = newwork.add_format({"bold" : 2})
Schart = newwork.add_chart({"type" : "stock"})
titles = ["Name", "starting package", "current package", "expected growth
points"]
entry_data = [['Rajesh', 'ravi', 'arun', 'neha', 'Devi'], [25000, 28000, 36000, 22000, 40000], [28000, 30000, 40000, 25000, 50000],[12, 10, 15, 8, 20 ]]
newsheet.write_row("A1", titles, Fbold)
for eachrow in range(5):
   newsheet.write(eachrow+1, 0, entry_data[0], [eachrow])
   newsheet.write(eachrow+1, 1, entry_data[1], [eachrow])
   newsheet.write(eachrow+1, 2, entry_data[2], [eachrow])
   newsheet.write(eachrow+1, 3, entry_data[3], [eachrow])
newsheet.set_column("A:D", 12)
Schart.add_series({"class": "= sheet1 !$A$2: $A$6", "data": "= sheet1 !$B$2: $B$6"})
Schart.add_series({"class": "= sheet1 !$A$2: $A$6", "data": "= sheet1 !$C$2: $C$6"})
Schart.add_series({"class": "= sheet1 !$A$2: $A$6", "data": "= sheet1 !$D$2: $D$6"})
Schart.set_title({'name': 'package details'})
Schart.set_x_axis({'name': 'Name'})
Schart.set_y_axis({'name': 'Growth'})
newsheet.insert_chart("F10", Schart)
newwork.close()

Conclusion

In this article we learned about the importance of “xlsxwriter module” and its use in plotting stock charts. We discussed the various methods involved and their application. We discussed the details of excel sheet data handling using python programming.