Automate Excel using Python - Xlwings | Series 1

Name: Automate Excel using Python - Xlwings | Series 1
Rating: 2.8 (20 reviews)

Master yourself to automate Industry-level datasets in Excel with hands-on experience on "2 Case Studies" using Python

Created byBlock.Hub Academy

Last updated 5/2020

English

English [Auto],

What you'll learn

Writing macros (in python) for their Excel sheets
Automate Excel without any VBA code (installed by default)
Build your own Formulas by coding User-defined Functions (UDFs) in Python
Practical hands-on experience with Industry level datasets
Automate, streamline and completely revolutionize your Excel workflow with Python macros
Master unique concepts, tools and case studies, which you won't find ANY other course, guaranteed
Get LIFETIME access to course materials and a 1-on-1 expert support
Continuous course updation when python packages updated with new features

Course content

5 sections • 28 lectures • 2h 28m total length

About Instructor4:47
Academy's Courses (All)8:49
Explore how the academy teaches automating Excel with Python, focusing on data science, plotting with matplotlib, and C++ libraries like Boost, with courses in object oriented programming and databases.
Course Outline5:43
Announcement: Series-2 Course launched!!!2:19
Udemy Course link: https://www.udemy.com/course/automate-excel-using-python-xlwings-series-2/
Course Outline on Youtube: https://www.youtube.com/watch?v=jmTSLjWCJ7U
Communication: Doubts, Request feature2:51

Tools4:03
Tools

Video
ch_0_tut_1_tools.mp4

Description
* Anaconda
- Setup: https://www.anaconda.com/download/
* Sublime Text 3
- Setup: https://www.sublimetext.com/
- Packages: https://packagecontrol.io/
- More: https://github.com/abhi3700/my_coding_toolkit/blob/master/sublime_all.md
Tools part 22:05
Tools

Video
ch_0_tut_1_tools.mp4

Description
* Anaconda
- Setup: https://www.anaconda.com/download/
* Sublime Text 3
- Setup: https://www.sublimetext.com/
- Packages: https://packagecontrol.io/
- More: https://github.com/abhi3700/my_coding_toolkit/blob/master/sublime_all.md
Python4:15
Python

Video
ch_0_tut_2_python.mp4

Code
ch_0_tut_2_python.py
Pandas4:23
Learn pandas fundamentals in Python: install pandas, import as pd, work with a data frame containing country and population, and filter to display China’s population and last five rows.
Xlwings10:44
Xlwings

Steps
1. Open "Command Prompt", type `conda list`
2. If `xlwings` is not installed, then install using this - `conda install -c anaconda xlwings`
3. Install **addin** in Excel using this - `xlwings addin install` on the terminal.
4. Choose a directory and open a project there: `xlwings quickstart demo`
5. macro settings in the MS Excel
- enable <kbd>Enable all macros</kbd> button in the `Trust Center >> Macro Settings >> Macro Settings`
- enable <kbd>Trust access to the VBA project object model</kbd> button in the `Trust Center >> Macro Settings >> Developer Macro Settings`

5. Print "Hello Xlwings!"
6. Add a UDF - `sumtwice()`

References
* Official website - https://www.xlwings.org/
* Documentation - https://docs.xlwings.org/en/stable/
* More - https://github.com/abhi3700/My_learning-Python/blob/master/xlwings_commands.md

Infographic2:23
This infographic presents a semiconductor industry case study, visualizing equipment data, particle contamination metrics (delta, BCD, delta sigma), and control limits (UCL/LCL) to monitor processes.
View Data6:58
View and prepare Excel data for analysis with xlwings in Python, loading the table into a dataframe and plotting date on the x-axis with density and UCL markers.
Prepare Code8:42
Customize the default Excel project by preparing a macro-ready setup and defining functions. Use spaces for Python indentation and add a button via the developer tab to run the module.
Define Sheets2:39
Learn to define and reference sheets in an xlwings workbook, using wb.sheets and named sheet variables to prepare data for plotting and later adding a picture.
View Code History3:09
Sheet_1 to DataFrame to Sheet_211:36
Figure, Axes6:34
Create figures and axes in Python, plot a date-based Delta City barometer, and format the x-axis with weekly ticks, rotated labels, and a dotted line with 15 percent transparency.
Show Plot7:12
Add Legends5:49
Create a custom legend for a 2d plot in xlwings, configuring colors, line width, and font size, and place the legend at the upper right using the legend function.

Infographic2:26
View Data3:53
View data: copy to a new sheet, create the first sheet, build a data frame, and log and segregate data across columns for analysis.
Prepare Code2:49
Prepare code by adding a new named sheet, inserting a button, and copying two lines, then rename modules and load the code to execute and view the output.
Define Sheets1:29
Define and name sheets in Xlwings workflow, assign the first sheet as data, and reference sheets via variables to automate Excel tasks with Python.
Sheet_1 to DataFrame to Sheet_29:55
Ch-A Plot8:36
Learn to plot time-series data with dates in Python, configure figure size, axes, date formats, major ticks, grid, transparency, and legends using plotting libraries.
Ch-B Plot6:33
Ch-B Plot Pt21:36
Master plotting in xlwings by building and refining a new chart, dragging data down, zooming out, aligning dates, and saving the workbook to finalize the plot.

Requirements

Python Fundamentals
Excel (version 2007 or later)

Description

This course teaches how to automate Industry-level datasets maintained in Excel sheets using Python programming language.

MS Excel is a very helpful tool for record-keeping. But the language that comes by default for macro is VBA, which is dated. And in the field of Data Analysis, Python has a lot of interesting packages which makes a job easy. Package like xlwings links any Excel with Python macros. Packages like Pandas, takes data into tabular format and also has customized filtering of rows or columns for complex data analysis. Packages like Matplotlib, Plotly enables to create different plots - line plot, Scatter plot, Heat map for finding the correlation b/w different parameters.

In the Series, 2 Case studies has been picked from Industry process line. And correspondingly, Python macros are created to:

reduce time in analysis
enable customization using python packages
reduce macros code lines in VBA codebase.
create customized User-defined modules by python functions.

The Series-2 is also available now in the Instructor's profile.

Who this course is for:

Beginner developers who want to make their career on Data Science
People working as an Analyst, using Excel mostly

Automate Excel using Python - Xlwings | Series 1

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 24min

Chapter 0: Basics5 lectures • 26min

Chapter-1 - Case study I9 lectures • 55min

Chapter-2 - Case Study II8 lectures • 37min

Conclusion1 lecture • 7min

Requirements

Description

Who this course is for: