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:
- MCP Server (
mcp_server/): Exposes Excel operations as MCP tools - AI Agent (
agent/): Uses OpenAI to make intelligent decisions about employee classifications
How It Works
- The agent connects to the MCP server via stdio
- Scans ALL employee rows from Excel (comprehensive scanning)
- Calculates/updates Experience_Years from DOJ automatically
- 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
- Fills empty cells and updates outdated values automatically
- Applies decisions back to Excel with confidence scores and reasoning
📋 Prerequisites
- Python 3.8+
- OpenAI API key
- Excel file with employee data
🚀 Setup
-
Clone the repository
cd excel_mcp -
Install dependencies
pip install -r requirements.txt -
Configure environment
# Create .env file (if it doesn't exist) # Add your OPENAI_API_KEY: OPENAI_API_KEY=your_openai_api_key_here -
Configure settings (optional)
- Edit
config/config.yamlto customize:- Excel file path
- OpenAI model settings
- Processing batch size
- Logging level
- Decision rules
- Edit
-
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
- Place your Excel file at
💻 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:
fetch_unprocessed: Get all unprocessed employee rowsfetch_all_employees: Get ALL employee rows for comprehensive scanningapply_employee_update: Update employee data with AI decisionsupdate_experience: Recalculate experience from DOJ (MCP tool, not a script)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 IDName: Employee nameDOJ: Date of JoiningExperience_Years: Years of experience (auto-calculated)Role: Employee roleStatus: Active/InactiveDepartment: Department (filled by agent)Designation: Designation (filled by agent)Salary_Band: Salary band (filled by agent)Is_Processed: Yes/No flagAI_Decision_Reason: Reasoning for decisionsConfidence_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
.envfile exists withOPENAI_API_KEY - Check API key is valid and has credits
📄 License
[Add your license here]
🤝 Contributing
[Add contribution guidelines here]
