• Go to xlwings Practice Lab
• Download PDF Guide

This HTML page was converted from the original PDF. Some images and complex layouts may not appear correctly. For the best experience, please download the PDF above.

Python in Excel

A Field Guide

Full Campaign Build with xlwings Lite & AI

Module 03

• Waterfall
• Segmentation
• Stratified Test & Control
• Statistical Tests
• Segment Profiles & Audit Reports

Where the Rubber Hits the Road

Lessons from 25+ years in the trenches
analyzing data, building ML models and
executing campaigns

Campaign + Simple Rules = Money
Campaign + Model = More Money
Campaign + Model + List Error = Disaster

Do the Math

This is business reality

The Campaign Build

1. Run Waterfall

• Identify eligible pop
• Review waterfall and audit reports

2. Segment Leads

• Create tiered segments
• Review segment profile report

3. Set up Control Group

• Setup control group - stratified sampling
• Run statistical tests for consistency of cuts
• Review profile and audit reports

4. Setup Test Cells

• Setup test cells for offer or A/B testing
• Review profile and test reports

This is the core process I've used for numerous successful campaigns. Earlier hand-coded, now AI-assisted. And very recently used for an outbound campaign for a SMB: response model built in a Notebook, and the campaign set up in xlwings Lite.

xlwings Lite for Campaigns

![Screenshot of xlwings Lite interface showing Python code editor with imports and a green button labeled "apply_exclusions".]

• Python code, logs, criteria, output - everything in one place
• Data set for each critical step as separate sheet
• Profile reports and statistical tests

The result: a single workbook that serves as both a workspace and a permanent, auditable trail

My Protocol for AI Code Generation

Show what you see

Provide full context - images, sample rows, schemas,
business rules, examples, documentation, gotchas

Tell What You Want

And how you want it done. One thing at a time. In
real life, one instruction not enough - iterate

Inspect What you Expect

Validations are sacrosanct. AI or no AI
AI needs guidance. It makes mistakes

Getting Started

Overview of Process. Details in following pages

Step 1: Download Module 03 Kit
[app.tigzig.com])(undefined) -> xlwings Practice Lab

1. AI Coder Instruction Markdown File
2. Practice Workbook & Completed Workbook
3. Guide : This document
   All AI instructions available in the workbooks

Step 2 : Go to [aistudio.google.com])(undefined)

Step 3 : Upload AI Instructions into chat box

• Upload AI Coder instructions
• Copy Paste Base Instruction and hit run
  AI is now ready for xlwings Lite code generation

Step 4 Onwards

• Copy paste instructions for code generation
• Check AI understanding & Run code
• Validate results and iterate

Download Module-03 Kit

Go to [app.tigzig.com])(undefined)

xlwings Lite: Practice Lab
Learn xlwings Lite by practical examples

1. AI Coder Instructions

For AI
Download the complete markdown file. Upload to your AI at start of every session.
[⬇ Download]

2. Workbooks and Guide

What’s AI Coder Instruction File

• This is my personal, 1,855-line rulebook for xlwings Lite that I feed to the AI right at the start of every session
• Covers non-negotiable rules, error patterns, tool limitations, and best practices from my client projects
• Helps in delivering clean, reliable xlwings Lite code - reducing debugging and iterations.

3. AI Coder Checklist: Core Directives for All Scripts

Golden Rules: These 20 directives are non-negotiable and MUST be applied in every script.

• Working with Web APIs
• Connecting to Databases via an API Layer
• Python Dependencies Management
• Env. Variables / CORS
• Limitations

Example Scripts

• Starter Examples
• Database Integration
• Web Scraping with LLM Processing
• EDA and Schema Analysis with LLMs
• XGBoost Response Model
• Credit Card Segment Analysis

Putting the Tools to Work

Go to [aistudio.google.com])(undefined)

Welcome to AI Studio

I'm working with xlwings Lite, the browser-based Excel Python add-in released in Jan 2025. It builds on the classic xlwings API but has key differences and limitations. I've attached a detailed reference document that should be treated as the primary source - it includes instructions, known API gaps, workarounds, troubleshooting notes, and working code examples specific to xlwings Lite (scripts, custom functions, FastAPI integrations, etc.). When helping me with code generation or debugging, always refer to this document first. If something seems unsupported or behaves differently from regular xlwings, assume the difference is documented - search the file carefully before answering.

AI_CODER_INSTRUCTIONS.md    15,503 tokens

1. Upload AI Coder Instructions File
2. Enter Instruction -01 : Base Instruction
   (Available in downloaded workbooks)
3. Hit Run.

AI is now calibrated and ready for xlwings Lite coding work

Data - Sample Rows

Data set: 25000 rows

Note: This is a synthetic dataset, purpose-built to test this workflow.
Complex variable correlations may not hold as they would in a live portfolio.

Part 1 : Running the Waterfall

The waterfall criteria / exclusion logic

Example Criteria: Outbound Personal Loan Campaign on a Credit Card Portfolio (Loan against OTB)

Instruct AI to run waterfall

Instruction 02: Run waterfall and extract eligible leads

Objective is to run a waterfall and get the number of eligible leads and waterfall report.

Exclusion criteria shared below. Apply criteria in that exact sequence only. Data is in table = CUST.

• Add a new field called REASON_CODE from the criteria tables and tag remaining customers with reason code 7.ELIGIBLES.
• Put this data into a table called CUST2 in a new sheet called CUST2.

Extract all the eligibles and put the output into a new sheet and table, both named LEADS. Delete existing sheet and table, if any.

Create a Waterfall report: The waterfall report is to be in the format shared below. Use the LEADS table.

Put that into a new sheet called WF_REP. Delete existing, if any, before updating.

Share your understanding of the criteria, overall requirement as well as the pseudo code.

Share runnable script with the function named as A_RUN_WATERFALL

Instruct what you want done and how you want it done - be specific

ALWAYS ask for AI understanding and pseudo code

Sharing below sample records, waterfall criteria and

Power User Tip : Dictate

Your edge is in expressing your requirements and business logic to AI and validating results - not your typing speed. Voice is the fastest way to transfer that logic to AI.

The Instructions in This Guide Are Sanitized
The instructions here are edited for clarity. They don't reflect how I actually work day to day.

In the Real World, I Dictate - Not Type
Some of my instructions are long and technically complex. For anything longer than a single sentences, I use voice typing. It’s faster and frictionless.
Windows: Win + H
macOS: Press Fn key twice

Focus on Logic, Not Grammar
Dictated instructions often have typos and ah, ooh and pauses That’s fine. AI understands intent

Send out the instruction

Copy paste instruction into aistudio and hit run

Chat Prompt

I am calibrated and ready to assist you as your specialized xlwings Lite AI Coder. Please share your first instruction or code for review.

Objective is to run a waterfall and get the number of eligible leads and waterfall report.
[Exclusion criteria shared below. Apply criteria...]

AI will share 3 things with you ->

1: AI Confirms its Understanding

Understanding of the Criteria

First, here is my understanding of the waterfall exclusion logic based on the image and your sample data. The criteria will be applied in the exact sequence provided, and a customer will be tagged with the first reason they are excluded for:

1. 1. BLOCKED: Exclude any customer where the BLOCK_CODE is not empty.
2. 2. DPD_CURR: Of the remaining customers, exclude those where DPD_CURRENT is 3 or more.
3. 3. DPD_EVER: Of the remaining, exclude those where EVER_30P_P12M (ever 30+ Days Past Due) is 1 or more.
4. 4. BSCORE: Of the remaining, exclude those where BEHAVIOUR_SCORE is less than [text cut off].

This is your first validation checkpoint. AI translates your request into plain English. Does this perfectly match your business logic? If there are any differences, correct AI now.

2: AI Outlines the Plan

Pseudo-code

Here is the high-level plan for the script:

1. Setup:

   • Load the data from the CUST Excel table into a pandas DataFrame.
   • Delete the CUST2 and LEADS sheets if they already exist to ensure a clean run.
   • Add a new REASON_CODE column to the DataFrame, initially empty.

2. Waterfall Exclusion:

   • Iterate through the exclusion rules in sequence.
   • For each rule, create a boolean mask to identify customers who meet the exclusion criteria and have not yet been assigned a REASON_CODE.
   • Update the REASON_CODE column for the newly excluded customers.
   • Print the count of customers excluded at each step.

3. Tag Eligible Leads:

This is a logic review. Pseudocode isn't code; it's a step-by-step plan in English. This is where you catch potential flaws before they become bugs in the script. Especially critical to review if you are new to Python.

3: AI Generates the Script

Python Script for xlwings Lite

Please copy and paste the code below into the main.py tab of your xlwings Lite task pane.

Generated python

import xlwings as xw
from xlwings import script
import pandas as pd

# THIS HELPER FUNCTION IS MANDATORY FOR ALL SCRIPTS ACCESSING TABLES
def find_table_in_workbook(book: xw.Book, table_name: str):
    pass

This is the final deliverable for this step. Because you've already validated the understanding and the logic, you can have high confidence on the script. Copy this into main.py tab on xlwings Lite and run the code.

Run Code

Ribbon menu items visible:

• Sort & Filter
• Find & Select
• Add-ins
• Analyze Data
• Copilot
• Excel Labs
• xlwings Lite

xlwings Lite

import xlwings as xw
from xlwings import script
import pandas as pd

# THIS HELPER FUNCTION IS MANDATORY FOR ALL SCRIPTS ACCESSED
def find_table_in_workbook(book: xw.Book, table_name: str):
    """
    Searches all sheets for a table and returns both the
    Returns: (xw.Sheet, xw.Table) or (None, None)
    """

Want to dig deeper into the code? Have AI walk you through each code element, step-by-step.

Review Logs & Errors

Review logs, output or xlwings Lite windows for any errors. Copy paste the error log, output or screenshot to AI - it will fix it for you.

def clean_phone_numbers(book: xw.Book):
    """
    and adds a new 'CLEANED_PHONE' column with the results
    """
    print("🚀 Starting phone number cleaning script...")
    try:
        # writing cleaned data back to Excel...
        # Success! A 'CLEANED_PHONE' column has been added to the PHONES table
        # Starting phone number cleaning script...
        # Searching for table 'PHONES' in all sheets...
        # Found table 'PHONES' on sheet 'DATA'
        # Reading data from the 'PHONES' table into DataFrame
        # Processing phone numbers...
        # Writing cleaned data back to Excel...
        # Success! A 'CLEANED_PHONE' column has been added to the PHONES table
        # Starting phone number cleaning script...
        # Searching for table 'PHONESS' in all sheets...
        # Table 'PHONESS' not found in the workbook.
        # An error occurred: Could not proceed because table 'PHONES' was not found
        # Starting phone number cleaning script...
        # Searching for table 'PHONESS' in all sheets...
        # Table 'PHONESS' not found in the workbook.
        # An error occurred: Could not proceed because table 'PHONES' was not found

xlwings Lite
InvalidArgument: The argument is invalid or missing or has an incorrect format.

raise NotImplementedError()
NotImplementedError

Some errors might take more than one iteration to fix.
Check output and logs and share feedback with AI.

Lists & waterfall report are ready

1. CUST2: Full list with additional fields for validations
2. LEADS: Eligibles list for further processing

Review list with filters and pivots, in addition to waterfall audit report

Review Waterfall Report

1. Review as you usually do.

2. Are these in line with what you expected?

3. Compare against previous reports. In case of major changes, investigate.

Instruct AI for Audit Reports

Instruction 03: Audit Reports

Need to create two reports to validate Waterfall rules

1. Criteria QA Report

Take final eligibles from LEADS table. Show following metrics:

• counts by block code, ever 30, current dpd, DNC
• counts by following OTB grouping: Less than 10000, 10000, Greater than 10000
• Compute new variable OTB_UTIL (CURR_BAL + 10000) as % of Credit Limit. Show counts by following grouping: <90%, exactly 90%, >90%
• count by BScore with this grouping: <600, 600, >600

Report Format: Stack the distribution summaries for each specified field vertically. This final table must include a 'Metric' column that identifies the source of each summary. Show all the categories, even if a category does not have value.

2. Data Quality Report

For the following variables: BScore, Credit Limit, OTB, OTB_UTIL, Current DPD, Ever 30 DPD, limit, curr_bal, cash propensity decile, current util & propensity score

Single table with the following statistics (each a column):

• Zero_Count: Count of explicit 0 values
• Missing_Count: Count of blank/null / NaN
• COUNT / MIN / MAX / Quartiles

Format both as tables, and place them one below the other.

Run ungrouped, or specify custom groupings
Create calculated fields on the fly.
Report format.
Category to be shown even if no value
Create additional custom reports
Get pseudo code, new script

Repeat same process

Follow same process for all subsequent steps

1. Confirm understanding with AI
2. Review pseudo code
3. Copy script into main.py and run it

With one difference: we requested for new script

1. Copy it below the last one
2. Dropdown will now show 2 scripts.
3. Select the new script and hit run

xlwings Lite

Dropdown showing two scripts:

• apply_exclusions
• generate_waterfall_report

Previous code

def apply_exclusions(bc
    if not leads_df.empty:
        except Exception
    else:
        print("No eligible leads found, so the 'LEADS' ta
        print("\n🎉 Script finished successfully!")

New code

import xlwings as xw
from xlwings import script
import pandas as pd

# THIS HELPER FUNCTION IS MANDATORY FOR ALL SCRIPTS ACCESSING
# (Include if not already in your main.py from the previous s
def find_table_in_workbook(book: xw.Book, table_name: str):
    """
    Searches all sheets for a table and returns both the she
    Returns: (xw.Sheet, xw.Table) or (None, None)

Review Audit Reports

Have any non-compliant creeped in?

Data Quality Report: Pay special attention to zeros and missing values. While this report validates the waterfall output, a full process also includes a preliminary DQ check on the input data before the waterfall begins.

Part 2 : Segment Leads

Objective: Determine outbound calling prioritization
Top Tier to be called first

Many ways to segment - from rules based to ML based, and for multiple purposes. This example shows a rule-based segmentation (based on past analysis) for prioritizing high value outbound calls

Instruct for Segmentation

Segmentation + Profile Report

Instruction 04 : Segment Leads

Categorize leads based on predefined criteria shared below and create lead profile summary report

Use table named LEADS.

• Add a new field called LEADS_CAT, populated with the Tier value Tier 1 - TOP etc. Do this in a waterfall/sequential manner. Create new table called LEADS_CAT

• My deciles range from 1 to 10, with 1 being top or best. Blanks / NaN in decile and propensity score replace with respective medians

• Enquiry variable has blanks - replace with zero

Leads Profile Summary
Group by the new leads category tier field and show:

• Counts
• Average balance
• Average credit limit
• Average of util_current
• Average of OTB
• Average Beh.Score
• Average for number of inquiries
• Min, Max, Average of cash propensity decile
• Min, Max, Average of propensity score

Report format: Tiers would be in columns and the metrics above would be in rows. Show all tiers. Put this leads

ALWAYS double check and specify how your deciles are setup
Manage Missing/Blanks. Necessary for stratified sampling in next steps. Ideally do it right at start
Describe how you would like the profile summary report to be setup

Review Lead Segmentation

Part 1 - The Summary

1. Review summary metrics: Are counts and averages in line with the segmentation criteria?
2. Compare against previous profiles: Any major, unexpected shifts from prior runs?
3. The summary provides the 'all clear' at 10,000 feet. But the critical work is in the details.

Review Lead Segmentation

Part 2 - The Raw Data Audit

A summary can hide issues. A raw data scan is mandatory. It's the only way to find details like this:-

This 445.5 value is not an error. It's the correct output from a median imputation that returned a float.

This is an important checkpoint. The next step isn't automatic correction; it's a judgment call based on downstream impact.

In this case, the impact on tertile-based stratification is negligible, so it could be left. For data integrity, my preference is to cast to an integer. I've left it as-is to highlight the importance of running multiple validations and especially raw data scans

Pre-Sampling Checklist

The next two sections - control group setup and test cells setup use stratified sampling. I have consolidated the instructions for demo purposes – in actual practice best to do it one component at a time, validating as you go.

1. Define your methodology: Methodologies can differ based on use case, business constraints, population size & distributions. Define the methodology you want to follow for your use case - from sampling techniques to the tests you want to run.

2. Be prepared to iterate: A single instruction might not get the result you want. Review output, assumptions and iterate.

3. Decide about random seed: the sample splits use random_state. If you want to test different splits, you'll need to remove the random_state.

4. Execute missing value imputations first. Carry out any missing value imputations before this stage, else AI might use its own judgment (typically median) - which might not be what you want.

Part 3: Setup Control Group

Instruction 04 - Control Group & Reports
Split the final eligible leads into a Test and Control group and run profile validation reports

1: Control Group with Stratified Sampling

• Take the final eligible leads from the LEADS_CAT table
• Create a stratified random control group of 15%
• Stratified by CARD_TYPE & tertiles of - propensity score, current util, beh score, credit limit
• If a strata has a single record, assign that to TEST group.
• NO missing value imputation to be carried out. They have been carried out during lead categorization stage.
• In the same Table, add a new field called GROUP - 'TEST' or 'CONTROL'.

Specify how you want the sampling done
Specify any custom treatments for data and sampling

2: Test vs. Control Profile Report

Create two separate profile reports, one for numeric and one for categorical. Put them on a new sheet named CONTROL_AUDIT.

2a Numeric Profile

Variables: limit, util_current, beh score, propensity score
Create a long form vertically stacked single table with the following columns:

1. Variable Name
2. Metric - Compute MIN / MAX / MEAN / COUNT / STD_DEV and show in separate rows
   3 & 4. Test group and Control Values for the metric
   5 & 6. P-values: For MEAN row, include:
   • T-Test p-value (assume equal variance)
   • Mann-Whitney U p-value (two-sided)
   • Effect Size: For MEAN row, include Cohen's d.

Specify format for your profile and audit reports
Specify statistical tests you want to run

Report 2b: Categorical profile

For CARD_TYPE
Format: Vertically stacked summary table with the following

Review control audit reports

Evaluate profile and statistical test values for consistency of splits. Tweak parameters and iterate.
Always check AI understanding & pseudo-code. Have AI walk you through code if you want to dig deeper.

Part 4: Setup Test Cells for Offers

Instruction 06: Create Test Cells for Offers

Part 1: Test Cell Assignment

• Take the LEADS_CAT TABLE and ONLY the TEST group
• I have two offers to test - OFFER_9.99 / OFFER_10.99
• Create stratified cut of 50% each.
• Stratify by CARD_TYPE, & tertiles of - propensity score, current util, beh score, credit limit
• NO missing value imputation to be carried out. They have been carried out during lead categorization stage.
• If a strata has a single record, assign that to OFFER_10.99 group.
• Add a new column 'OFFER'. Update existing LEADS_CAT table only.

Part 2: Test Cell Profile Report

• In a new sheet titled OFFER_PROFILE
• Create exactly the same reports that you did for the test and control group - instead of test and control this time it would be these 2 offer groups now.
• In addition, create the same set of reports for the 3 Tiers and put them below the overall report from the previous step.
• Share your understanding of the requirement as well as the pseudo code.
• Create a new, runnable script. Name the script E_TEST_CELLS.
• Follow AI Coder Instructions carefully - this is an even trickier piece.

Specify details of how you want the test cells to be set up
Profile and audit report formats can remain same as in control group audit report

Review test cell profile reports

Evaluate, assess and iterate

Final Campaign Setup & Measurement Plan

Primary Measurement Plan

• Overall Lift: Test vs. Control performance.
• Segment Performance: Tier 1 vs. Tier 2 vs. Tier 3 analysis.
• Offer Performance: 10.99% vs. 9.99% analysis.

Secondary Analysis & Finer Cuts

• Offer vs. Tier / Card Type / Tier Level Lifts / sub-segments
• Depending on response rate, smaller cuts could lead to insufficient sample size for sub-segments
• If direct comparison not statistically valid - use regression models to isolate variable impact

Setup campaign with measurement in mind

Campaigns: Mandatory Rules

Never release a list unless validated. Ever.
You are the Pilot – the flight doesn’t take off till you are confident. No matter the pressure. It is not worth it.

1. Initial runs are almost always wrong. Dig. Iterate. Rerun.
2. Audit reports aren't enough. use filters, pivots, direct queries
3. Automated codes + wrong parameters = Garbage. Double-check params before every run.
4. Automated codes + missed errors = Land mine. Check console logs.
5. Blanks and Missing create havoc – identify and treat
6. Inverted decile = Disaster. Verify the range (1-10 or 0-9) and which end is ‘top’. Double-check, even if you built the model.
7. Beware of row shifts. Cust A's name against Cust B's info
8. Final CSV - Open in Notepad++ first. Look for truncation & file format errors.
9. Campaign workbook is a permanent audit trail. Must contain everything: exclusions, segments, audits, approvals.
10. Business constraints can override statistical purity. The goal is a robust, practical solution, not a perfect one.
11. Something feels off? It probably is. Trust your gut. Dig.

From Workflows to Standalone Tools

The apps below are examples of standalone xlwings Lite tools built using exactly the same protocols. These are not tutorials, but fully functioning, open-source applications that demonstrate advanced use cases like database connections, web scraping and API calls.
Live app + full code on my site to use as a reference for your own projects.

Explore Live Apps

Web Scraper

Transform Excel into a powerful web scraping platform using Jina AI and Google Gemini

• Uses Jina AI API for webpage rendering to markdown
• Google Gemini analyzes content for intelligent data extraction
• Process batches of URLs with custom column specifications

Technical Analysis

Create professional technical analysis pattern recognition

• Pulls live data from Yahoo Finance via custom API
• Gemini Vision API analyzes charts for technical patterns
• Generates PDF/HTML reports with embedded charts

Database & ML

Connect to any database with custom FastAPI layer for data analysis and machine learning

• Connects to PostgreSQL/MySQL via web API layer
• Pull random records; run custom queries; explore data

AI Workflows

Automate Excel workflows with AI scheduler Flash or GPT-4o

• Automatic schema detection with structured data
• Identify categorical and numerical variables in datasets

https://app.tigzig.com/xlwings-starter

TIGZIG: Micro-Apps for Analytics

25+ apps: Database AI / xlwings Lite / Automation / Quants

![Open Source]

Get the full module and workbooks:
[app.tigzig.com])(undefined)
Path: xlwings Lite: Practice Lab

https://app.tigzig.com/landing
TIGZIG: Co-Analyst ![OSS] [More Apps]

Free. No Sign-Up Required.

What do you want to do today?
Search tools...

FEATURED
xlwings Lite: Practice Lab [NEW] ⭐
Learn xlwings Lite with Practical Examples
A Hands-on guide to get up and running fast with xlwings Lite

• AI-ready instructions & examples
• Step-by-step learning guides
• Interactive workbooks

Amar Harolikar
Specialist - Decision Sciences & Applied Gen AI
Builder of app.tigzig.com