{"id":2719,"date":"2022-05-26T22:38:56","date_gmt":"2022-05-26T17:08:56","guid":{"rendered":"https:\/\/sheetstips.com\/?p=2719"},"modified":"2022-06-17T11:05:52","modified_gmt":"2022-06-17T05:35:52","slug":"python-plotting-stock-charts-in-excel-sheet-using-xlsxwriter-module","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/python-plotting-stock-charts-in-excel-sheet-using-xlsxwriter-module\/","title":{"rendered":"Python | Plotting Stock charts in excel sheet using XlsxWriter module"},"content":{"rendered":"<p><strong>Automate Excel Stock Charts with Python:<\/strong> lsxwriter is a Python module that allows users to perform multiple operations, such as creating, writing, executing, arithmetic operations, and plotting graphs on excel files using Python. Like other operations, the lsxwriter in Python can also create a stock chart in Excel. A stock chart is basically a price chart that displays the price of a stock plotted over time, which includes the stock symbol, exchange price, chart period, price charge, last charge, volume, etc.,<\/p>\n<p>Including all these parameters in a stock chart are quite tricky from a Python standpoint. Thus, to help you with it, here is a detailed tutorial that explains how to plot a stock chart in Excel using Python. This page contains detailed instructions and examples for creating a stock chart in an excel file using Python libraries. Scroll down to learn more.<\/p>\n<h2>Plot Stock Chart in Excel using Python<\/h2>\n<p>Charts are made up of a series of one or more data points. The series are made up of references to cell ranges. To plot the charts on an excel sheet, first create a chart object of the desired chart type ( i.e Stock chart, etc.). After generating chart objects, insert data into them before adding them to the sheet object.<\/p>\n<p>Install the xlsxwriter using the below code<\/p>\n<div>\n<pre>pip\u00a0install\u00a0xlsxwriter<\/pre>\n<\/div>\n<p>Looking in indexes: https:\/\/pypi.org\/simple, https:\/\/us-python.pkg.dev\/colab-wheels\/public\/simple\/<br \/>\nCollecting xlsxwriter<br \/>\nDownloading XlsxWriter-3.0.3-py3-none-any.whl (149 kB)<br \/>\n|\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 149 kB 5.0 MB\/s<br \/>\nInstalling collected packages: xlsxwriter<br \/>\nSuccessfully installed xlsxwriter-3.0.3<\/p>\n<h2>Plotting Stock charts in excel sheet using XlsxWriter module in Python<\/h2>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import the datetime function from the datetime library.<\/li>\n<li>Import the xlsxwriter library using the import function.<\/li>\n<li>Pass the excel file path to the Workbook() function of xlsxwriter which is the file we want to create and store in a variable.<\/li>\n<li>Create the new Worksheet using the add_worsheet() function and apply it to the above workbook object and store it in a variable.<\/li>\n<li>Create a new Format object to format cells in spreadsheets using the add_format() method and apply it to the above workbook object.<\/li>\n<li>Here, we created a bold format object.<\/li>\n<li>Using the add_chart() method, create a chart object that can be added to a worksheet.<\/li>\n<li>Pass the type of chart as an argument to the add_chart() function and apply it to the above workbook object<\/li>\n<li>Add the worksheet data that the charts will use.<\/li>\n<li>Write a row of heading data starting with &#8216;A1&#8217; in the bold format using the write_row() function and apply to the above worksheet object by passing the argument row number, headings list, and format.<\/li>\n<li>Writing the above data in the worksheet.<\/li>\n<li>Iterate till the number of rows of the data(Here it is 5).<\/li>\n<li>Convert a string type date to a date in the correct format using the strptime() function.<\/li>\n<li>Apply the dateformat object on the first Cell and write it to the worksheet using the write() function and apply it to the above created Worksheet<\/li>\n<li>Write the worksheetData on the cells by passing the data with the corresponding rownumber and write() function.<\/li>\n<li>Write worksheetData in the respective cells.<\/li>\n<li>Set the width of columns(Here we have 4 columns) A to D column set to some random value say 12.<\/li>\n<li>Add the data from A2 to A6 and B2 to B6 using the add_series function and apply to the chartObject data take from A2 to A6 and B2 to B6 respectively.<\/li>\n<li>Add the data from A2 to A6 and C2 to C6 using the add_series function and apply to the chartObject.<\/li>\n<li>Add the data from A2 to A6 and D2 to D6 using the add_series function and apply to the chartObject.<\/li>\n<li>Set the title to the chartObject using the set_title() function by passing the name as value and key as &#8220;name (Here the arguments are object).<\/li>\n<li>Label the x axis using the set_x_axis() function by passing the Date as value and key as &#8220;name&#8221; (Here the arguments are object)<\/li>\n<li>Label the y axis using the set_x_axis() function by passing the Share price as value and key as &#8220;name&#8221; (Here the arguments are object)<\/li>\n<li>Add a chartObject to the worksheet with the provided offset values in the top-left corner of a chartObject that is anchored to cell E9.<\/li>\n<li>Close the workbook Object using the close() function.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># Import the datetime function from the datetime library\r\nfrom datetime import datetime\r\n\r\n# Import the xlsxwriter library using the import function\r\nimport xlsxwriter\r\n\r\n# Pass the excel file path to the Workbook() function of xlsxwriter \r\n# which is the file we want to create and store it in a variable\r\nworkbookObj = xlsxwriter.Workbook('chartStock.xlsx')\r\n\r\n# Create the new Worksheet using the add_worsheet() function and \r\n# apply it to the above workbook object and store it in a variable\r\nnewWorksheet = workbookObj.add_worksheet()\r\n\r\n# Create a new Format object to format cells in spreadsheets using the add_format() method and apply it to the above workbook object.\r\n# Here, we created a bold format object. .\r\nboldFormat = workbookObj.add_format({'bold': 1})\r\n\r\n# Using the add_chart() method, create a chart object that can be added to a worksheet.\r\n# Pass the type of chart as an argument to the add_chart() function and apply it to the above workbook objec\r\nchartObject = workbookObj.add_chart({'type': 'stock'})\r\n\r\n# Add the worksheet data that the charts will use.\r\nheadingsData = ['Date', 'HighValue', 'lowValue', 'closeValue']\r\nworksheetData = [\r\n    ['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05'],\r\n    [23.4, 24.12, 15.15, 22.54, 26.35],\r\n    [20.19, 18.5, 12.3, 16.34, 19.45],\r\n    [22.45, 23.19, 13.32, 20.17, 21.34],\r\n]\r\n\r\n# Write a row of heading data starting with 'A1' in bold format using the write_row() function\r\n# and apply on the above worksheet object\r\n# by passing the argument rowNumber,headings list,and format.\r\nnewWorksheet.write_row('A1', headingsData, boldFormat)\r\n\r\n# Writing the above data in the worksheet\r\n#Iterate till the number of rows of the data(Here it is 5)\r\nfor rowNumb in range(5):\r\n\r\n    #Convert a string type date to a date in the correct format using the strptime() function\r\n    date = datetime.strptime(worksheetData[0][rowNumb], \"%Y-%m-%d\")\r\n\r\n    # Apply the dateformat object on the first Cell and write it to the\r\n  # worksheet using the write() function and apply on the above created Worksheet\r\n    newWorksheet.write(rowNumb + 1, 0, worksheetData[0][rowNumb])\r\n  # Write the worksheetData on the cells by passing the data with the corresponding rownumber and write() function\r\n    # write worksheetData in the respective cells\r\n    newWorksheet.write(rowNumb + 1, 1, worksheetData[1][rowNumb])\r\n    newWorksheet.write(rowNumb + 1, 2, worksheetData[2][rowNumb])\r\n    newWorksheet.write(rowNumb + 1, 3, worksheetData[3][rowNumb])\r\n\r\n# Set the width of columns(Here we have 4 columns) A to D column set to some random value say 12.\r\nnewWorksheet.set_column('A:D', 12)\r\n\r\n# Add the data from A2 to A6 and B2 to B6 using the add_series function and apply on the chartObject\r\nchartObject.add_series({\r\n    'categories': '= Sheet1 !$A$2:$A$6',\r\n    'values': '= Sheet1 !$B$2:$B$6',\r\n})\r\n# Add the data from A2 to A6 and C2 to C6 using the add_series function and apply on the chartObject\r\n\r\nchartObject.add_series({\r\n    'categories': '= Sheet1 !$A$2:$A$6',\r\n    'values':\t '= Sheet1 !$C$2:$C$6',\r\n})\r\n#Add the data from A2 to A6 and D2 to D6 using the add_series function and apply on the chartObject\r\nchartObject.add_series({\r\n    'categories': '= Sheet1 !$A$2:$A$6',\r\n    'values': '= Sheet1 !$D$2:$D$6',\r\n})\r\n\r\n# Set the title to the chartObject using the set_title() function by passing the name as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_title ({'name': 'High-Low-Close'})\r\n\r\n# Label the x axis using the set_x_axis() function by passing the Date as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_x_axis({'name': 'Date'})\r\n\r\n# Label the y axis using the set_x_axis() function by passing the Share price as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_y_axis({'name': 'Share price'})\r\n\r\n# Add a chartObject to the worksheet with the provided offset values in the top-left corner of a chartObject that is anchored to cell E9.\r\nnewWorksheet.insert_chart('E9', chartObject)\r\n\r\n# Close the workbook Object using the close() function.\r\nworkbookObj.close()\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2720\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/stock-chart.jpg\" alt=\"stock chart\" width=\"1246\" height=\"669\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/stock-chart.jpg 1246w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/stock-chart-300x161.jpg 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/stock-chart-1024x550.jpg 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/stock-chart-768x412.jpg 768w\" sizes=\"(max-width: 1246px) 100vw, 1246px\" \/><\/p>\n<p>Now that you are provided with all the necessary information on how to plot a stock chart in Excel using Python, when you are creating chart objects, make sure you insert data into them and add the chart to the sheet object. Missing this step might result in a major error. Just ensure your code is error free to create the charts hassle free.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Automate Excel Stock Charts with Python: lsxwriter is a Python module that allows users to perform multiple operations, such as creating, writing, executing, arithmetic operations, and plotting graphs on excel files using Python. Like other operations, the lsxwriter in Python can also create a stock chart in Excel. A stock chart is basically a price &#8230; <a title=\"Python | Plotting Stock charts in excel sheet using XlsxWriter module\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/python-plotting-stock-charts-in-excel-sheet-using-xlsxwriter-module\/\" aria-label=\"More on Python | Plotting Stock charts in excel sheet using XlsxWriter module\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":2723,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182],"tags":[],"jetpack_featured_media_url":"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Plotting-Stock-charts-in-excel-sheet-using-XlsxWriter-module.png","jetpack-related-posts":[{"id":2974,"url":"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-3\/","url_meta":{"origin":2719,"position":0},"title":"Python Program Plotting Charts in Excel Sheet using openpyxl module | Set 3","author":"David","date":"June 1, 2022","format":false,"excerpt":"One of the most significant Python packages, Openpyxl, enables users to automate a variety of Excel tasks, including reading, writing, mathematical calculations, creating graphs, and more. Let's talk about how to use Openpyxl to make charts on an Excel sheet without launching the Excel sheet in this article. Before plotting\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program Plotting Charts in Excel Sheet using openpyxl module Set 3","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-Plotting-Charts-in-Excel-Sheet-using-openpyxl-module-Set-3.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2971,"url":"https:\/\/sheetstips.com\/python-plotting-line-charts-in-excel-sheet-using-xlsxwriter-module\/","url_meta":{"origin":2719,"position":1},"title":"Python Program to Plot Line Charts in Excel Sheet using XlsxWriter module","author":"David","date":"June 1, 2022","format":false,"excerpt":"The Xlsx module of the Python programming language is one of the most important libraries for helping us plot line\u00a0charts in an Excel worksheet. The Python Xlsx library can do mathematical operations, display charts in Excel, import data, write data, and much more. It is important for users to determine\u00a0a\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program to Plot Line Charts in Excel Sheet using XlsxWriter module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-to-Plot-Line-Charts-in-Excel-Sheet-using-XlsxWriter-module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2981,"url":"https:\/\/sheetstips.com\/python-plotting-charts-in-excel-sheet-using-openpyxl-module-set-2\/","url_meta":{"origin":2719,"position":2},"title":"Python Program Plotting Charts in Excel Sheet using Openpyxl module | Set 2","author":"David","date":"June 1, 2022","format":false,"excerpt":"Users can automate a range of Excel functions, including reading, writing, performing computations, making graphs, and more, using one of the most important Python programs, Openpyxl. In this article, we will discuss how to create charts in an Excel sheet using Openpyxl without actually opening the Excel sheet. In order\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program Plotting Charts in Excel Sheet using openpyxl module Set 2","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-Plotting-Charts-in-Excel-Sheet-using-openpyxl-module-Set-2.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2822,"url":"https:\/\/sheetstips.com\/python-plotting-different-types-of-style-charts-in-excel-sheet-using-xlsxwriter-module\/","url_meta":{"origin":2719,"position":3},"title":"Python Program to Plot Different types of Style Charts in Excel Sheet using XlsxWriter module","author":"David","date":"December 2, 2022","format":false,"excerpt":"Below are the ways to plot the different types of style charts in an excel sheet using the XlsxWriter module in Python. Charts are made up of a series of one or more data points. The series are made up of references to cell ranges. To plot the charts on\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program to Plot Different types of Style Charts in Excel Sheet using XlsxWriter module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-to-Plot-Different-types-of-Style-Charts-in-Excel-Sheet-using-XlsxWriter-module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2873,"url":"https:\/\/sheetstips.com\/python-program-to-plot-combined-charts-in-excel-sheet-using-xlsxwriter-module\/","url_meta":{"origin":2719,"position":4},"title":"Python Program to Plot Combined Charts in Excel Sheet using XlsxWriter module","author":"David","date":"May 31, 2022","format":false,"excerpt":"If you are looking for information on plotting combined graphs in Excel using Python, you have landed on the right page. Python has a library named XlsxWriter which is used to perform various operations on the Excel file, such as creating, writing, arithmetic calculations, plotting graphs, and much more. Using\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program to Plot Combined Charts in Excel Sheet using XlsxWriter module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-to-Plot-Combined-Charts-in-Excel-Sheet-using-XlsxWriter-module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2969,"url":"https:\/\/sheetstips.com\/python-plotting-pie-charts-in-excel-sheet-using-xlsxwriter-module\/","url_meta":{"origin":2719,"position":5},"title":"Python Program to Plot Pie Charts in Excel Sheet using XlsxWriter module","author":"David","date":"June 1, 2022","format":false,"excerpt":"One of the most important Python libraries for helping us plot pie charts in the Excel worksheet is the Xlsx module. The Python Xlsx package can import data, write data, execute mathematical operations, display charts in Excel, and much more. To create a pie chart in Excel using Python, it\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program to Plot Pie Charts in Excel Sheet using XlsxWriter module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-to-Plot-Pie-Charts-in-Excel-Sheet-using-XlsxWriter-module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2719"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=2719"}],"version-history":[{"count":4,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2719\/revisions"}],"predecessor-version":[{"id":3462,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2719\/revisions\/3462"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media\/2723"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=2719"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=2719"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=2719"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}