Python for Excel

Python for Excel

by Felix Zumstein
Released March 2021
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492081005

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

While Excel remains ubiquitous in the business world, recent Microsoft feedback forums are full of requests to include Python as an Excel scripting language. In fact, it's the top feature requested. What makes this combination so compelling? In this hands-on guide, Felix Zumstein--creator of xlwings, a popular open source package for automating Excel with Python--shows experienced Excel users how to integrate these two worlds efficiently.

Excel has added quite a few new capabilities over the past couple of years, but its automation language, VBA, stopped evolving a long time ago. Many Excel power users have already adopted Python for daily automation tasks. This guide gets you started.

  • Use Python without extensive programming knowledge
  • Get started with modern tools, including Jupyter notebooks and Visual Studio code
  • Use pandas to acquire, clean, and analyze data and replace typical Excel calculations
  • Automate tedious tasks like consolidation of Excel workbooks and production of Excel reports
  • Use xlwings to build interactive Excel tools that use Python as a calculation engine
  • Connect Excel to databases and CSV files and fetch data from the internet using Python code
  • Use Python as a single tool to replace VBA, Power Query, and Power Pivot

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Why I Wrote This Book
    2. Who This Book Is For
    3. How This Book Is Organized
    4. Python and Excel Versions
    5. Conventions Used in This Book
    6. Using Code Examples
    7. O’Reilly Online Learning
    8. How to Contact Us
    9. Acknowledgments
  2. I. Introduction to Python
  3. 1. Why Python for Excel?
    1. Excel Is a Programming Language
      1. Excel in the News
      2. Programming Best Practices
      3. Modern Excel
    2. Python for Excel
      1. Readability and Maintainability
      2. Standard Library and Package Manager
      3. Scientific Computing
      4. Modern Language Features
      5. Cross-Platform Compatibility
    3. Conclusion
  4. 2. Development Environment
    1. The Anaconda Python Distribution
      1. Installation
      2. Anaconda Prompt
      3. Python REPL: An Interactive Python Session
      4. Package Managers: Conda and pip
      5. Conda Environments
    2. Jupyter Notebooks
      1. Running Jupyter Notebooks
      2. Notebook Cells
      3. Edit vs. Command Mode
      4. Run Order Matters
      5. Shutting Down Jupyter Notebooks
    3. Visual Studio Code
      1. Installation and Configuration
      2. Running a Python Script
    4. Conclusion
  5. 3. Getting Started with Python
    1. Data Types
      1. Objects
      2. Numeric Types
      3. Booleans
      4. Strings
    2. Indexing and Slicing
      1. Indexing
      2. Slicing
    3. Data Structures
      1. Lists
      2. Dictionaries
      3. Tuples
      4. Sets
    4. Control Flow
      1. Code Blocks and the pass Statement
      2. The if Statement and Conditional Expressions
      3. The for and while Loops
      4. List, Dictionary, and Set Comprehensions
    5. Code Organization
      1. Functions
      2. Modules and the import Statement
      3. The datetime Class
    6. PEP 8: Style Guide for Python Code
      1. PEP 8 and VS Code
      2. Type Hints
    7. Conclusion
  6. II. Introduction to pandas
  7. 4. NumPy Foundations
    1. Getting Started with NumPy
      1. NumPy Array
      2. Vectorization and Broadcasting
      3. Universal Functions (ufunc)
    2. Creating and Manipulating Arrays
      1. Getting and Setting Array Elements
      2. Useful Array Constructors
      3. View vs. Copy
    3. Conclusion
  8. 5. Data Analysis with pandas
    1. DataFrame and Series
      1. Index
      2. Columns
    2. Data Manipulation
      1. Selecting Data
      2. Setting Data
      3. Missing Data
      4. Duplicate Data
      5. Arithmetic Operations
      6. Working with Text Columns
      7. Applying a Function
      8. View vs. Copy
    3. Combining DataFrames
      1. Concatenating
      2. Joining and Merging
    4. Descriptive Statistics and Data Aggregation
      1. Descriptive Statistics
      2. Grouping
      3. Pivoting and Melting
    5. Plotting
      1. Matplotlib
      2. Plotly
    6. Importing and Exporting DataFrames
      1. Exporting CSV Files
      2. Importing CSV Files
    7. Conclusion
  9. 6. Time Series Analysis with pandas
    1. DatetimeIndex
      1. Creating a DatetimeIndex
      2. Filtering a DatetimeIndex
      3. Working with Time Zones
    2. Common Time Series Manipulations
      1. Shifting and Percentage Changes
      2. Rebasing and Correlation
      3. Resampling
      4. Rolling Windows
    3. Limitations with pandas
    4. Conclusion
  10. III. Reading and Writing Excel Files Without Excel
  11. 7. Excel File Manipulation with pandas
    1. Case Study: Excel Reporting
    2. Reading and Writing Excel Files with pandas
      1. The read_excel Function and ExcelFile Class
      2. The to_excel Method and ExcelWriter Class
    3. Limitations When Using pandas with Excel Files
    4. Conclusion
  12. 8. Excel File Manipulation with Reader and Writer Packages
    1. The Reader and Writer Packages
      1. When to Use Which Package
      2. The excel.py Module
      3. OpenPyXL
      4. XlsxWriter
      5. pyxlsb
      6. xlrd, xlwt, and xlutils
    2. Advanced Reader and Writer Topics
      1. Working with Big Excel Files
      2. Formatting DataFrames in Excel
      3. Case Study (Revisited): Excel Reporting
    3. Conclusion
  13. IV. Programming the Excel Application with xlwings
  14. 9. Excel Automation
    1. Getting Started with xlwings
      1. Using Excel as Data Viewer
      2. The Excel Object Model
      3. Running VBA Code
    2. Converters, Options, and Collections
      1. Working with DataFrames
      2. Converters and Options
      3. Charts, Pictures, and Defined Names
      4. Case Study (Re-Revisited): Excel Reporting
    3. Advanced xlwings Topics
      1. xlwings Foundations
      2. Improving Performance
      3. How to Work Around Missing Functionality
    4. Conclusion
  15. 10. Python-Powered Excel Tools
    1. Using Excel as Frontend with xlwings
      1. Excel Add-in
      2. Quickstart Command
      3. Run Main
      4. RunPython Function
    2. Deployment
      1. Python Dependency
      2. Standalone Workbooks: Getting Rid of the xlwings Add-in
      3. Configuration Hierarchy
      4. Settings
    3. Conclusion
  16. 11. The Python Package Tracker
    1. What We Will Build
    2. Core Functionality
      1. Web APIs
      2. Databases
      3. Exceptions
    3. Application Structure
      1. Frontend
      2. Backend
      3. Debugging
    4. Conclusion
  17. 12. User-Defined Functions (UDFs)
    1. Getting Started with UDFs
      1. UDF Quickstart
    2. Case Study: Google Trends
      1. Introduction to Google Trends
      2. Working with DataFrames and Dynamic Arrays
      3. Fetching Data from Google Trends
      4. Plotting with UDFs
      5. Debugging UDFs
    3. Advanced UDF Topics
      1. Basic Performance Optimization
      2. Caching
      3. The Sub Decorator
    4. Conclusion
  18. A. Conda Environments
    1. Create a New Conda Environment
    2. Disable Auto Activation
  19. B. Advanced VS Code Functionality
    1. Debugger
    2. Jupyter Notebooks in VS Code
      1. Run Jupyter Notebooks
      2. Python Scripts with Code Cells
  20. C. Advanced Python Concepts
    1. Classes and Objects
    2. Working with Time-Zone-Aware datetime Objects
    3. Mutable vs. Immutable Python Objects
      1. Calling Functions with Mutable Objects as Arguments
      2. Functions with Mutable Objects as Default Arguments
  21. Index
  22. About the Author

Product information

  • Title: Python for Excel
  • Author(s): Felix Zumstein
  • Release date: March 2021
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492081005