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