fx Automation

Python for Excel

90 min6 lessons18 quiz questions

Lessons

1. openpyxl vs pandas vs xlwings — which one when15 min 2. pandas: read_excel, filtering, groupby, merge15 min 3. to_excel with multiple sheets and ExcelWriter15 min 4. openpyxl — formatting, column width, conditional formatting, charts15 min 5. xlwings — controlling live Excel from Python15 min 6. Mini project: monthly report generator script15 min

Practice file

⬇ Download automation-practice.zip

Automation practice bundle

One ZIP serves all seven modules. It contains a generated, macro-free automation-practice.xlsx, bilingual solutions, three intentionally messy CSVs, Python report/email/pipeline scripts, requirements, a Windows launcher, a sample GitHub Actions workflow, and a README. No credentials or executable VBA are embedded.

The workbook contains Read Me, ROI, Data, Output, Sales, Products, Customers, Invoices, Statement, Orders, Rates, and Log. Data has 30 stock rows (15 reorder, 3 out of stock, total shortfall 45). Sales has 30 rows: North/South 8 each and East/West 7 each; three salespeople have 10 rows each. Five customer balances are 1200, 0, 800, 0, 500. Statement is an exercise template, not a completed statement.

The separate CSV project has 10 input rows: 7 valid unique, 2 rejected and 1 duplicate. Regional totals: South 96000, North 92000, West 61000; overall 249000. Rejected rows preserve original field values, including the literal NA amount.

Extract the ZIP and follow README.md to install the Python requirements and run the examples. Start with python pipeline.py --no-email using copies of the sample data. Desktop VBA, Outlook and xlwings exercises require the applications listed in their respective lessons. The bundle includes no credentials or enabled schedules.

Module quiz (18)

1. Which library needs Excel installed?

xlwings

2. Best library to group and total 5 lakh rows?

pandas

3. Does openpyxl calculate formulas?

No — Excel does when the file is opened

4. AND operator in pandas filters?

5. Excel equivalent of groupby().sum()?

SUMIFS or a pivot table

6. Which how keeps all rows of the left table?

"left"

7. How do you write 3 sheets into one file?

pd.ExcelWriter with a with-block

8. Maximum sheet name length?

31 characters

9. Which options add a sheet to an existing file and replace it if it exists?

mode="a", if_sheet_exists="replace"

10. How do you keep row 1 visible?

ws.freeze_panes = "A2"

11. Does openpyxl have AutoFit?

No — set widths from the longest value

12. Which rule class highlights values below a number?

CellIsRule

13. Which library function connects to the workbook that clicked a button?

xw.Book.caller()

14. What does expand="table" do?

Reads the whole connected block of data

15. Can xlwings run on GitHub Actions?

No — it needs Excel installed

16. Why keep a Rejected Rows sheet?

So bad data is visible and can be fixed, not silently lost

17. Which argument stops Indian dates being read as month/day?

format="%d/%m/%Y"

18. How do you run the script for a different month without editing it?

Pass a different --input/--output on the command line