Using Python in Excel

Using Python in Excel

This course is designed to take excel users to the next level by enabling python programming in Excel. Python is an important piece of Microsoft’s future in the cloud, being one of the essential languages for services and teams to support, as well as the most popular choice for the rapidly growing field of data science and analytics both inside and outside of the company.

Over the last ten years, the change has been dramatic. Many Microsoft products now include Python support, and some of the newest only support Python. Therefore if you want to have a future with Microsoft products, python programming is an absolute must!

Learning Outcome

The possible learning outcome of this course is:

• Learn Python from scratch
• Learn xlwings library
• Write and use Dynamic Arrays with xlwings
• Ability to write Excel tools with Python instead of VBA
• Replace VBA macros with clean and powerful Python code
• Call your python code directly from within Excel
• Create powerful Dashboard Apps with Excel (frontend) and Python (backend)
• Write UDFs (user defined functions) and use Numpy, Pandas and Machine Learning Libraries directly in Excel
• Excel task automation with python
• Load data from Web APIs directly into Excel

Prerequisites

• Basic excel usage experience
• Understanding of windows’ file structure
• Windows 10 or higher
• Full installation of MS Excel
• Full admin access in windows (in order to install different python libraries and environments)
• Internet access.
• Webcam (for OLL)
• Basic high-school mathematics
• Willingness to learn

Course Outline

This is a HIGH INTENSITY three-day course.

1. Python Basics
   1. Introduction
      1. Brief History of Python
      2. Python Versions
      3. Installing Python
      4. IDEs
      5. Environment Variables
      6. Python Documentation
      7. Hello world with python
      8. Modes of Programming
   2. Variables and collections
      1. Numbers
      2. Python Lists
      3. Python Tuples
      4. Python Dictionaries
      5. Python Sets
      6. Copying
      7. Python Strings
      8. String formatting
      9. Regular Expressions
   3. Operations
      1. Arithmetic Operators
      2. Comparison (Relational) Operators
      3. Assignment Operators
      4. Logical Operators
      5. Membership Operators
      6. Operators Precedence
   4. Decisions and Loops
      1. The if Statement
      2. The if else Statement
      3. For Loop
      4. While Loop
      5. Break And Continue
   5. Functions
      1. Defining Your Own Functions
      2. Parameters
      3. Function Documentation
      4. Passing Collections to a Function
      5. Variable Number of Arguments
      6. Scope
      7. Map
      8. Filter
      9. Lambda
2. Matplotlib, Numpy, Pandas, Seaborn
   1. Introduction to Pandas
   2. Series
   3. DataFrames
   4. Missing Data
   5. Group By with Pandas
   6. Merging, Joining, and Concatenating DataFrames
   7. Pandas Common Operations
   8. Data Input and Output
   9. Introduction to Visualization in Python
   10. Matplotlib Basics
   11. Matplotlib Introduction
   12. Line Plots
   13. Scatter Plots
   14. Seaborn Visualizations
   15. Pandas Visualization Overview
   16. Pandas Time Series Visualization
3. Xlwings
   1. How to connect to an Excel Workbook
   2. How to read and write single Values
   3. How to assign a name
   4. How to write Excel Functions with Python
   5. Range Shortcuts
   6. One-dimensional Data Structures
   7. How to write Values vertically
   8. Rows and Columns
   9. How to read two-dimensional Data Structures
   10. Advanced Reading with expand
   11. How to write two-dimensional Data Structures
   12. Range Indexing and Slicing
   13. Efficiency
4. Integration Essentials
   1. Running Python Scripts with "Run main"
   2. VBA Macros essentials
   3. Running Python Scripts with "RunPython"
   4. Run main vs RunPython
   5. Excursus
5. Visualizations
   1. How to write a Matplotlib Plot into Excel
   2. How to update the Plot
   3. How to change Size and Position
   4. How write a Seaborn Plot into Excel
   5. How to create Excel Charts with Python
6. User Defined Functions
   1. Preparations and your first UDF
   2. How to change the Name and Location of the Python Module
   3. Troubleshooting (UDF)
   4. UDFs - Behind the Scenes
   5. More complex UDFs and the @xw.arg Decorator
   6. How to create Numpy UDFs
   7. UDFs and Array Formulas
   8. How to create Dynamic Arrays with xlwings UDFs
   9. How to create Pandas UDFs
   10. How to add Docstrings
7. Use case and hands-on
   1. Case-study
   2. Assessment