Python for Excel: A Modern Environment for Automation and Data Analysis

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 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