Excel MCP Server & Employee Classification Agent

An MCP (Model Context Protocol) server with an AI agent that automatically classifies employees in Excel files based on their experience, role, and other attributes.

🏗️ Architecture

This project consists of two main components:

1. MCP Server (mcp_server/): Exposes Excel operations as MCP tools
2. AI Agent (agent/): Uses OpenAI to make intelligent decisions about employee classifications

How It Works

1. The agent connects to the MCP server via stdio
2. Scans ALL employee rows from Excel (comprehensive scanning)
3. Calculates/updates Experience_Years from DOJ automatically
4. Uses GPT-4o-mini to analyze each employee and decide:
   • Department: Web, AI, HR, Finance, or Operations
   • Designation: Intern, Junior, Senior, or Lead (auto-updates as experience grows)
   • Salary_Band: L1, L2, or L3
5. Fills empty cells and updates outdated values automatically
6. Applies decisions back to Excel with confidence scores and reasoning

📋 Prerequisites

• Python 3.8+
• OpenAI API key
• Excel file with employee data

🚀 Setup

1. Clone the repository

   cd excel_mcp
   

2. Install dependencies

   pip install -r requirements.txt
   

3. Configure environment

   # Create .env file (if it doesn't exist)
   # Add your OPENAI_API_KEY:
   OPENAI_API_KEY=your_openai_api_key_here
   

4. Configure settings (optional)

   • Edit config/config.yaml to customize:
     • Excel file path
     • OpenAI model settings
     • Processing batch size
     • Logging level
     • Decision rules

5. Prepare Excel file

   • Place your Excel file at data/employees_mcp.xlsx (or path in config)
   • Ensure it has required columns (see mcp_server/schema.py)
   • The system will create backups automatically before updates

💻 Usage

Run the Agent

Scan and process all employees (fills empty cells, updates outdated values):

python main.py

The agent will:

• Scan ALL employees (not just unprocessed)
• Calculate/update Experience_Years from DOJ
• Fill empty cells in any column
• Auto-update designations when experience changes (e.g., Junior → Senior)
• Update any outdated values

Run MCP Server Standalone

For testing or integration with other MCP clients:

python -m mcp_server.server

📊 Decision Rules

The agent follows these rules:

Designation (based on Experience_Years)

• Intern: < 2 years
• Junior: 2-4 years
• Senior: 5-7 years
• Lead: 8+ years

Salary Band

• L1: Entry level (Intern, Junior with <3 years)
• L2: Mid level (Junior with 3-4 years, Senior)
• L3: Senior level (Lead, Senior with 7+ years)

Department

• Inferred from employee's Role or existing Department
• Options: Web, AI, HR, Finance, Operations

🛠️ MCP Tools

The server exposes these tools:

1. fetch_unprocessed: Get all unprocessed employee rows
2. fetch_all_employees: Get ALL employee rows for comprehensive scanning
3. apply_employee_update: Update employee data with AI decisions
4. update_experience: Recalculate experience from DOJ (MCP tool, not a script)
5. reset_processed_flag: Reset processing flags for reprocessing

📁 Project Structure

excel_mcp/
├── mcp_server/          # MCP server implementation
│   ├── server.py       # MCP server with tool definitions
│   ├── tools.py        # Excel operations (read/write)
│   └── schema.py       # Data validation schemas
├── agent/              # AI agent (MCP client)
│   └── employee_agent.py  # Decision-making logic
├── utils/              # Utility modules
│   ├── logger.py       # Logging configuration
│   ├── config_loader.py # Configuration management
│   └── backup.py       # Backup functionality
├── config/             # Configuration files
│   └── config.yaml     # Main configuration
├── data/               # Excel data files
│   └── employees_mcp.xlsx
├── backups/            # Automatic backups (created automatically)
├── logs/               # Log files (created automatically)
├── main.py             # Entry point (runs agent)
└── requirements.txt    # Python dependencies

🔧 Configuration

Configuration File

Edit config/config.yaml to customize:

• Excel file path: excel_path
• OpenAI settings: Model, temperature, retry settings
• Processing settings: Batch size, backup options
• Logging: Log level, file path
• Decision rules: Experience thresholds, salary band rules

Environment Variables

• OPENAI_API_KEY: Your OpenAI API key (required)
• EXCEL_PATH: Override Excel file path (optional)
• OPENAI_MODEL: Override OpenAI model (optional)

Example Configuration

excel_path: "data/employees_mcp.xlsx"
openai:
  model: "gpt-4o-mini"
  temperature: 0
  max_retries: 3
processing:
  backup_before_update: true
  backup_directory: "backups"
logging:
  level: "INFO"
  file: "logs/mcp_server.log"

📝 Excel File Format

Required columns:

• Emp_ID: Employee ID
• Name: Employee name
• DOJ: Date of Joining
• Experience_Years: Years of experience (auto-calculated)
• Role: Employee role
• Status: Active/Inactive
• Department: Department (filled by agent)
• Designation: Designation (filled by agent)
• Salary_Band: Salary band (filled by agent)
• Is_Processed: Yes/No flag
• AI_Decision_Reason: Reasoning for decisions
• Confidence_Score: Confidence (0.0-1.0)
• Last_Processed_On: Timestamp

✨ Features

• ✅ Automatic Backups: Creates timestamped backups before updates
• ✅ Comprehensive Logging: File and console logging with rotation
• ✅ Error Handling: Retry logic for API calls, graceful error recovery
• ✅ Progress Reporting: Real-time progress and summary statistics
• ✅ Configuration Management: YAML-based configuration
• ✅ Data Validation: Enforces dropdown constraints
• ✅ Unit Tests: Test suite for core functionality

⚠️ Notes

• The agent scans ALL employees and updates any empty or outdated fields
• Automatically updates Experience_Years from DOJ on every run
• Auto-updates designations when experience changes (e.g., Junior → Senior)
• Automatic backups are created before updates (configurable)
• Data validation (dropdowns) is preserved after updates
• Experience is calculated as: (Today - DOJ) / 365.25
• Logs are written to logs/ directory
• Backups are stored in backups/ directory

🐛 Troubleshooting

"Invalid row_id" error

• Ensure Excel file hasn't been manually edited
• Row IDs are positional (0-indexed)

"Invalid value" error

• Check that values match allowed dropdown values in schema.py
• Agent should only use allowed values, but manual edits might cause issues

API Key errors

• Ensure .env file exists with OPENAI_API_KEY
• Check API key is valid and has credits

📄 License

[Add your license here]

🤝 Contributing

[Add contribution guidelines here]