Python for Excel - Felix Zumstein Mikołów

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 knowledgeGet started with modern tools, including Jupyter notebooks and Visual Studio codeUse pandas to acquire, clean, and analyze data and replace typical Excel calculationsAutomate tedious tasks like consolidation of Excel workbooks and production of Excel reportsUse xlwings to build interactive Excel tools that use Python as a calculation engineConnect Excel to databases and CSV files and fetch data from the internet using Python codeUse Python as a single tool to replace VBA, Power Query, and Power Pivot Spis treści: 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 OReilly Online Learning How to Contact Us Acknowledgments I. Introduction to Python 1. Why Python for Excel? Excel Is a Programming Language Excel in the News Programming Best Practices Separation of concerns DRY principle Testing Version control Modern Excel Power Query and Power Pivot Power BI Python for Excel Readability and Maintainability Standard Library and Package Manager Scientific Computing Modern Language Features Cross-Platform Compatibility Conclusion 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 3. Getting Started with Python Data Types Objects Variables Functions Attributes and methods Numeric Types Mathematical operators 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 Defining functions Calling functions Modules and the import Statement The datetime Class PEP 8: Style Guide for Python Code PEP 8 and VS Code Type Hints Conclusion II. Introduction to pandas 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 5. Data Analysis with pandas DataFrame and Series Index Columns Data Manipulation Selecting Data Selecting by label Selecting by position Selecting by boolean indexing Selecting by using a MultiIndex Setting Data Setting data by label or position Setting data by boolean indexing Setting data by replacing values Setting data by adding a new column 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 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 III. Reading and Writing Excel Files Without Excel 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 8. Excel File Manipulation with Reader and Writer Packages The Reader and Writer Packages When to Use Which Package The excel.py Module OpenPyXL Reading with OpenPyXL Writing with OpenPyXL Editing with OpenPyXL XlsxWriter pyxlsb xlrd, xlwt, and xlutils Reading with xlrd Writing with xlwt Editing with xlutils Advanced Reader and Writer Topics Working with Big Excel Files Writing with OpenPyXL Writing with XlsxWriter Reading with xlrd Reading with OpenPyXL Reading sheets in parallel Formatting DataFrames in Excel Formatting a DataFrames index and headers Formatting a DataFrames data part Case Study (Revisited): Excel Reporting Conclusion IV. Programming the Excel Application with xlwings 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 Excel charts Pictures: Matplotlib plots Defined names Case Study (Re-Revisited): Excel Reporting Advanced xlwings Topics xlwings Foundations Improving Performance Minimize cross-application calls Raw values App properties How to Work Around Missing Functionality Conclusion 10. Python-Powered Excel Tools Using Excel as Frontend with xlwings Excel Add-in Quickstart Command Run Main RunPython Function RunPython without quickstart command Deployment Python Dependency Standalone Workbooks: Getting Rid of the xlwings Add-in Configuration Hierarchy Settings Conclusion 11. The Python Package Tracker What We Will Build Core Functionality Web APIs Databases The Package Tracker database Database connections SQL queries SQL injection Exceptions Application Structure Frontend Backend Debugging Conclusion 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 Minimize cross-application calls Using raw values Caching The Sub Decorator Conclusion A. Conda Environments Create a New Conda Environment Disable Auto Activation B. Advanced VS Code Functionality Debugger Jupyter Notebooks in VS Code Run Jupyter Notebooks Python Scripts with Code Cells 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