Excel MCP Server

Instructions

Implementation Reference

• src/handlers/analytics.ts:5-93 (handler)

  Core handler function that reads file data, extracts numeric values from specified column, computes comprehensive statistics (mean, median, mode, std dev, quartiles, skewness, etc.), and returns formatted JSON response.

  async statisticalAnalysis(args: ToolArgs): Promise<ToolResponse> {
    const { filePath, column, sheet } = args;
    const data = await readFileContent(filePath, sheet);
  
    if (data.length <= 1) {
      throw new Error('File has no data rows');
    }
  
    const colIndex = isNaN(Number(column))
      ? data[0].indexOf(column)
      : Number(column);
  
    if (colIndex === -1 || colIndex >= (data[0]?.length || 0)) {
      throw new Error(`Column "${column}" not found`);
    }
  
    const values = [];
    for (let i = 1; i < data.length; i++) {
      const val = Number(data[i][colIndex]);
      if (!isNaN(val)) {
        values.push(val);
      }
    }
  
    if (values.length === 0) {
      throw new Error('No numeric values found in column');
    }
  
    // Calculate statistics
    const n = values.length;
    const sum = values.reduce((a, b) => a + b, 0);
    const mean = sum / n;
  
    // Sort for median and quartiles
    const sorted = [...values].sort((a, b) => a - b);
    const median = n % 2 === 0
      ? (sorted[n / 2 - 1] + sorted[n / 2]) / 2
      : sorted[Math.floor(n / 2)];
  
    // Mode calculation
    const frequency: Record<number, number> = {};
    values.forEach(val => frequency[val] = (frequency[val] || 0) + 1);
    const maxFreq = Math.max(...Object.values(frequency));
    const modes = Object.keys(frequency).filter(val => frequency[+val] === maxFreq).map(Number);
  
    // Variance and standard deviation
    const variance = values.reduce((acc, val) => acc + Math.pow(val - mean, 2), 0) / (n - 1);
    const stdDev = Math.sqrt(variance);
  
    // Quartiles
    const q1 = sorted[Math.floor(n * 0.25)];
    const q3 = sorted[Math.floor(n * 0.75)];
    const iqr = q3 - q1;
  
    // Skewness (simplified Pearson's method)
    const skewness = 3 * (mean - median) / stdDev;
  
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify({
            success: true,
            column: data[0][colIndex],
            statistics: {
              count: n,
              sum,
              mean: Math.round(mean * 10000) / 10000,
              median,
              mode: modes.length === 1 ? modes[0] : modes,
              min: Math.min(...values),
              max: Math.max(...values),
              range: Math.max(...values) - Math.min(...values),
              variance: Math.round(variance * 10000) / 10000,
              standardDeviation: Math.round(stdDev * 10000) / 10000,
              quartiles: {
                q1,
                q2: median,
                q3,
                iqr
              },
              skewness: Math.round(skewness * 10000) / 10000,
              coefficientOfVariation: Math.round((stdDev / mean) * 100 * 100) / 100
            }
          }, null, 2),
        },
      ],
    };
  }

• src/index.ts:297-316 (schema)

  Input schema definition for the statistical_analysis tool, specifying parameters filePath (required), column (required), and optional sheet.

  name: 'statistical_analysis',
  description: 'Perform comprehensive statistical analysis on a column',
  inputSchema: {
    type: 'object',
    properties: {
      filePath: {
        type: 'string',
        description: 'Path to the CSV or Excel file',
      },
      column: {
        type: 'string',
        description: 'Column name or index (0-based)',
      },
      sheet: {
        type: 'string',
        description: 'Sheet name for Excel files (optional)',
      },
    },
    required: ['filePath', 'column'],
  },

• src/index.ts:1221-1228 (registration)

  Dispatch/registration in the main tool call handler switch statement, mapping 'statistical_analysis' tool calls to the analyticsHandler.statisticalAnalysis method.

  case 'statistical_analysis':
    return await this.analyticsHandler.statisticalAnalysis(toolArgs);
  case 'correlation_analysis':
    return await this.analyticsHandler.correlationAnalysis(toolArgs);
  case 'data_profile':
    return await this.analyticsHandler.dataProfile(toolArgs);
  case 'pivot_table':
    return await this.analyticsHandler.pivotTable(toolArgs);