Python for Excel - Helion

ebook

Autor: Felix Zumstein
ISBN: 9781492080954
stron: 338, Format: ebook
Data wydania: 2021-03-04
Księgarnia: Helion

Cena książki: 186,15 zł (poprzednio: 216,45 zł)
Oszczędzasz: 14% (-30,30 zł)

Osoby, które kupiły tę książkę, wybierały także »

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

Osoby które kupowały "Python for Excel", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

Python for Excel eBook -- 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