Automate Excel using Python - Xlwings | Series 1
- Self-paced with Life Time Access
- Certificate on Completion
- Access on Android and iOS App
Course Preview Video
-
Author
-
Categories
-
Duration
Master yourself to automate Industry-level datasets in Excel with hands-on experience on "2 Case Studies" using Python
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
- Python Fundamentals
- Excel (version 2007 or later)
- Beginner developers who want to make their career on Data Science
- People working as an Analyst, using Excel mostly
Students learn to:
- 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
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
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
Python
Video
ch_0_tut_2_python.mp4
Download Code
https://github.com/abhi3700/dummy_data_DONT_DELETE/blob/master/ch_0_tut_2_python.py
Download
countries.csv
https://github.com/abhi3700/dummydataDONT_DELETE/blob/master/countries.csv
Code:
https://github.com/abhi3700/dummy_data_DONT_DELETE/blob/master/ch_0_tut_3_pandas.py
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
