Built during a Shell internship as a Data Analyst & Visualization Expert to fully automate the daily oil field production reporting workflow for West Kuwait operations.
What previously required manual Excel work, copy-paste into dashboards, and hand-crafted emails is now a single command. Drop a report file in, run the agent, review the outputs, send.
The agent ingests a daily production Excel workbook, validates it, extracts KPIs, generates an enterprise-grade dashboard image and structured report, and prepares a complete email package — all without human involvement in the pipeline itself.
Human oversight is preserved by design. Every run produces draft outputs for review. No email is ever sent automatically unless a human explicitly approves it.
Daily Production Report (.xlsx)
│
▼
┌───────────────────┐
│ 1. Validate │ File, sheet structure, data integrity
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 2. Extract KPIs │ Deterministic extraction + Claude AI fallback
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 3. Validate KPIs │ Consistency checks, reconciliation, range rules
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 4. Dashboard │ Jinja2 → HTML/CSS → Playwright → dashboard.png
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 5. Report │ Claude AI narrative + structured Markdown
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 6. Email Draft │ AI-generated body + metadata for human review
└────────┬──────────┘
│
▼
┌───────────────────┐
│ 7. Audit Trail │ run_manifest.json — full provenance record
└───────────────────┘
- AI-powered extraction — Uses the Claude API (Anthropic) with structured tool-use to locate KPIs in Excel workbooks regardless of minor formatting changes. Falls back to deterministic keyword search if the API is unavailable.
- AI-generated reports — Claude generates executive-ready narrative reports from validated metrics, following strict reporting standards defined in
templates/CLAUDE.md. - Pixel-perfect dashboards — Jinja2 HTML templates + embedded CSS + Playwright Chromium screenshot produce a reproducible 1600×900 enterprise dashboard. Same metrics always produce the same image.
- Gmail integration — OAuth2-authenticated inbox watcher automatically fetches the daily report attachment, eliminating the manual download step.
- Windows Task Scheduler automation —
setup_scheduler.ps1registers the agent as a scheduled task that runs at a configured time each morning. - Two-layer validation — Workbook structure is validated before extraction; extracted metrics are validated for completeness, type correctness, and internal consistency before any output is generated.
- Full audit trail — Every run writes a
run_manifest.jsoncapturing inputs, outputs, timestamps, validation results, and LLM token usage. - Human-in-the-loop safety — Auto-send requires both a config flag (
agent.auto_send: true) and a CLI flag (--send). Omitting either keeps the agent in draft mode.
| Layer | Technology |
|---|---|
| Language | Python 3.10+ |
| AI / LLM | Anthropic Claude API (tool-use, prompt caching) |
| Dashboard rendering | Jinja2 + Playwright (Chromium) |
| Excel parsing | openpyxl, pandas |
| Email automation | Gmail API (OAuth2), pywin32 (Outlook COM) |
| File watching | watchdog |
| Scheduling | Windows Task Scheduler |
| Config | PyYAML |
| Testing | pytest |
Shell/
├── docs/
│ ├── KPI_DEFINITIONS.md # Metric definitions (BOPD, FT Oil, variance, etc.)
│ ├── DATA_DICTIONARY.md # Field-level data dictionary (UWI, rig, dates, etc.)
│ └── REPORTING_STANDARDS.md # Data integrity and visualization standards
│
├── workflow/
│ ├── agent/
│ │ ├── run_daily_agent.py # CLI entry point
│ │ ├── orchestrator.py # Autonomous entry point (Gmail + scheduler)
│ │ ├── config.py # YAML config loader and validator
│ │ ├── paths.py # Path resolution and run folder creation
│ │ ├── validator.py # Excel workbook validation
│ │ ├── extract_metrics.py # Deterministic KPI extraction
│ │ ├── llm_extract.py # Claude API KPI extraction (tool-use)
│ │ ├── validate_metrics.py # Metric consistency and reconciliation checks
│ │ ├── render_dashboard.py # HTML → Playwright screenshot pipeline
│ │ ├── render_report.py # Markdown report generator
│ │ ├── llm_narrative.py # Claude API narrative generation
│ │ ├── report_builder.py # Pipeline orchestrator
│ │ ├── emailer.py # Email draft / Outlook draft
│ │ ├── gmail_fetcher.py # Gmail OAuth2 attachment downloader
│ │ ├── inbox_watcher.py # File system inbox watcher
│ │ ├── auto_send_gate.py # Time-gated auto-send logic
│ │ ├── failure_notifier.py # Error notification handler
│ │ ├── manifest.py # Run audit manifest
│ │ └── retry.py # API call retry with backoff
│ │
│ ├── templates/
│ │ ├── dashboard.html # Enterprise dashboard template (Jinja2)
│ │ ├── dashboard.css # 16:9 layout stylesheet
│ │ ├── report_prompt.md # LLM prompt template for narrative generation
│ │ └── CLAUDE.md # Report generation rules and standards
│ │
│ ├── config/
│ │ ├── agent_config.example.yaml # Annotated config reference
│ │ └── workflow_config.example.yaml # Full workflow config reference
│ │
│ ├── tests/
│ │ ├── test_extract_metrics.py
│ │ ├── test_validate_metrics.py
│ │ └── test_validator.py
│ │
│ ├── inbox/ # Drop daily Excel reports here (gitignored)
│ ├── outputs/ # Generated run folders (gitignored)
│ ├── logs/ # Agent logs (gitignored)
│ ├── run.ps1 # Unified PowerShell launcher
│ └── setup_scheduler.ps1 # Windows Task Scheduler registration
│
└── archive/ # Legacy workflows (read-only reference)
├── daily-reports-analysis/
└── oil-gain-analysis/
Each run produces a fully self-contained timestamped output folder:
outputs/2026-05-17_083459/
├── dashboard/
│ ├── dashboard.html # Inspectable pre-screenshot HTML
│ └── dashboard.png # Final 1600×900 dashboard image
├── report/
│ └── report.md # Executive narrative report
├── metrics/
│ └── metrics.json # Validated KPI values (source of truth for all outputs)
├── email/
│ ├── email_body.txt # Ready-to-send email body (AI-generated)
│ └── email_metadata.json # Recipients, subject, attachments
├── logs/
│ ├── validation_report.json
│ └── metrics_validation_report.json
└── manifest/
└── run_manifest.json # Full audit trail
- Python 3.10+
- Windows (for Outlook COM and Task Scheduler integration; core pipeline runs cross-platform)
- Anthropic API key (for AI features — gracefully disabled if absent)
cd workflow
python -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
playwright install chromiumcopy config\workflow_config.example.yaml config\workflow_config.yamlKey settings in workflow_config.yaml:
reporting:
region_name: "West Kuwait" # Displayed in dashboard header
email:
recipients:
- "supervisor@example.com"
llm:
extraction: true # Use Claude for KPI extraction
narrative: true # Use Claude for report generation
api_key_env: ANTHROPIC_API_KEY # Set this in your environment
gmail:
enabled: false # Set to true after OAuth setupSet your Anthropic API key:
$env:ANTHROPIC_API_KEY = "sk-ant-..."Full pipeline (validate → extract → dashboard → report → email draft):
.\run.ps1 -Input "inbox\17-May-2026_Daily_Production_Report.xlsx"Dry run (validation only, no outputs written):
.\run.ps1 -Input "inbox\report.xlsx" -DryRunSkip dashboard (report and email draft only, faster):
.\run.ps1 -Input "inbox\report.xlsx" -SkipDashboardAutonomous mode (Gmail fetch + full pipeline, for scheduled use):
python agent/orchestrator.py --config config/workflow_config.yamlRegister the agent to run automatically each morning:
.\setup_scheduler.ps1This creates a Windows Task Scheduler job that runs the orchestrator at the configured time using the SYSTEM account — no stored password required.
The pipeline runs two sequential validation layers before generating any output:
| Layer | Checks |
|---|---|
| Workbook validation | File exists, correct extension, readable, expected sheet present, non-empty |
| Metrics validation | All required KPIs extracted, correct numeric types, MTD consistency, range plausibility |
If workbook validation fails, the pipeline halts immediately — no dashboard, report, or email is generated. Metrics validation failures are recorded as warnings in the manifest but do not stop the pipeline.
When llm.extraction: true, Claude is called with a structured tool-use schema to locate KPI values in the workbook. The model returns null for any value it cannot find with confidence rather than guessing. The deterministic keyword-based extractor runs as a fallback if the API call fails or the key is missing.
When llm.narrative: true, Claude generates the full executive report from metrics.json following the rules in templates/CLAUDE.md and the prompt in templates/report_prompt.md. This includes operational commentary, variance analysis, and risk flags — language appropriate for management distribution.
When llm.email_body: true, Claude generates a concise, professional email body from the validated metrics. Falls back to a static template if generation fails.
All LLM calls use prompt caching where applicable. Token usage (input, output, cache reads, cache writes) is recorded in the run manifest for cost tracking.
This project follows formal reporting standards defined in docs/REPORTING_STANDARDS.md:
- No fabrication — all values and insights must map directly to source data
- Validation before visualization — no chart is generated from unvalidated data
- Raw data immutability — source files are never modified
- Decision-support first — dashboards are operational tools, not presentation artifacts
| File | Purpose |
|---|---|
| docs/KPI_DEFINITIONS.md | Definitions for all tracked metrics (BOPD, FT Oil, MTD, variance, etc.) |
| docs/DATA_DICTIONARY.md | Field-level dictionary for workbook columns (UWI, rig, dates, production series) |
| docs/REPORTING_STANDARDS.md | Data integrity rules, chart standards, naming conventions |
| workflow/README.md | Agent architecture, module reference, configuration details |
| workflow/docs/GMAIL_SETUP.md | Step-by-step Gmail OAuth2 setup guide |
Legacy workflows preserved for reference in archive/:
| Directory | Description |
|---|---|
archive/daily-reports-analysis/ |
Historical date-stamped report outputs from the pre-agent workflow |
archive/oil-gain-analysis/ |
WIDE Export pipeline for oil gain analysis (superseded by this agent) |