Excel MCP Server

Instructions

Implementation Reference

• src/handlers/financial-analysis.ts:409-515 (handler)

  The primary handler function for the 'trend_analysis' tool. It reads spreadsheet data, extracts time series, performs linear regression, calculates growth rates and volatility, analyzes seasonality, generates forecasts, and provides actionable insights via JSON response.

  async trendAnalysis(args: ToolArgs): Promise<ToolResponse> {
    const { filePath, sheet, dateColumn, valueColumn, periods = 12 } = args;
  
    try {
      const data = await readFileContent(filePath, sheet);
  
      if (data.length <= 1) {
        throw new Error('File has insufficient data rows for trend analysis');
      }
  
      const headers = data[0];
      const dateIdx = isNaN(Number(dateColumn)) ? headers.indexOf(dateColumn) : Number(dateColumn);
      const valueIdx = isNaN(Number(valueColumn)) ? headers.indexOf(valueColumn) : Number(valueColumn);
  
      if (dateIdx === -1 || valueIdx === -1) {
        throw new Error('Date or value column not found');
      }
  
      // Extract time series data (skip header row)
      const timeSeries: Array<{ date: string, value: number }> = [];
  
      for (let i = 1; i < data.length; i++) {
        const dateValue = data[i][dateIdx];
        const numericValue = Number(data[i][valueIdx]);
  
        if (dateValue && !isNaN(numericValue) && numericValue > 0) {
          timeSeries.push({
            date: String(dateValue),
            value: numericValue
          });
        }
      }
  
      if (timeSeries.length < 3) {
        throw new Error('Need at least 3 data points for trend analysis');
      }
  
      // Calculate trend metrics
      const values = timeSeries.map(d => d.value);
      const n = values.length;
  
      // Linear regression for trend line
      const trendMetrics = this.calculateLinearRegression(values);
  
      // Calculate growth rates
      const growthRates = [];
      for (let i = 1; i < values.length; i++) {
        const growth = (values[i] - values[i-1]) / values[i-1];
        growthRates.push(growth);
      }
  
      const avgGrowthRate = growthRates.reduce((sum, rate) => sum + rate, 0) / growthRates.length;
  
      // Seasonal analysis (simplified quarterly)
      const seasonalAnalysis = this.analyzeSeasonality(values, periods);
  
      // Forecast next periods
      const forecast = this.generateForecast(values, periods, trendMetrics);
  
      return {
        content: [{
          type: 'text',
          text: JSON.stringify({
            success: true,
            analysis: 'Trend Analysis',
            dataPoints: n,
            timeRange: {
              start: timeSeries[0].date,
              end: timeSeries[n-1].date
            },
            trend: {
              slope: Math.round(trendMetrics.slope * 10000) / 10000,
              intercept: Math.round(trendMetrics.intercept * 10000) / 10000,
              rSquared: Math.round(trendMetrics.rSquared * 10000) / 10000,
              direction: trendMetrics.slope > 0 ? 'Increasing' : trendMetrics.slope < 0 ? 'Decreasing' : 'Flat',
              strength: Math.abs(trendMetrics.slope) > avgGrowthRate * 2 ? 'Strong' : Math.abs(trendMetrics.slope) > avgGrowthRate ? 'Moderate' : 'Weak'
            },
            growth: {
              averageGrowthRate: Math.round(avgGrowthRate * 10000) / 100,
              volatility: Math.round(this.calculateVolatility(growthRates) * 10000) / 100,
              maxGrowth: Math.round(Math.max(...growthRates) * 10000) / 100,
              minGrowth: Math.round(Math.min(...growthRates) * 10000) / 100
            },
            seasonality: seasonalAnalysis,
            forecast: forecast.slice(0, periods).map((value, index) => ({
              period: index + 1,
              predictedValue: Math.round(value * 100) / 100,
              confidence: index < 3 ? 'High' : index < 6 ? 'Medium' : 'Low'
            })),
            insights: this.generateTrendInsights(trendMetrics, avgGrowthRate, seasonalAnalysis)
          }, null, 2)
        }]
      };
  
    } catch (error) {
      return {
        content: [{
          type: 'text',
          text: JSON.stringify({
            success: false,
            error: error instanceof Error ? error.message : 'Unknown error',
            operation: 'trend_analysis'
          }, null, 2)
        }]
      };
    }
  }

• src/handlers/financial-analysis.ts:517-538 (helper)

  Core linear regression calculation used by trendAnalysis to determine trend slope, intercept, and fit quality (R²).

  private calculateLinearRegression(values: number[]): { slope: number, intercept: number, rSquared: number } {
    const n = values.length;
    const sumX = (n * (n - 1)) / 2; // Sum of 0 to n-1
    const sumY = values.reduce((sum, val) => sum + val, 0);
    const sumXY = values.reduce((sum, val, index) => sum + val * index, 0);
    const sumXX = (n * (n - 1) * (2 * n - 1)) / 6; // Sum of squares 0 to n-1
    const sumYY = values.reduce((sum, val) => sum + val * val, 0);
  
    const slope = (n * sumXY - sumX * sumY) / (n * sumXX - sumX * sumX);
    const intercept = (sumY - slope * sumX) / n;
  
    // Calculate R-squared
    const yMean = sumY / n;
    const ssRes = values.reduce((sum, val, index) => {
      const predicted = slope * index + intercept;
      return sum + Math.pow(val - predicted, 2);
    }, 0);
    const ssTot = values.reduce((sum, val) => sum + Math.pow(val - yMean, 2), 0);
    const rSquared = 1 - (ssRes / ssTot);
  
    return { slope, intercept, rSquared: isNaN(rSquared) ? 0 : rSquared };
  }

• src/handlers/financial-analysis.ts:546-577 (helper)

  Analyzes seasonal patterns in the data series by computing period averages and variation from overall mean.

  private analyzeSeasonality(values: number[], periods: number): any {
    // Simplified seasonality analysis
    const n = values.length;
    if (n < periods * 2) {
      return { detected: false, note: 'Insufficient data for seasonality analysis' };
    }
  
    // Calculate average by period (simplified)
    const periodAverages: Record<number, number[]> = {};
  
    for (let i = 0; i < n; i++) {
      const period = i % periods;
      if (!periodAverages[period]) periodAverages[period] = [];
      periodAverages[period].push(values[i]);
    }
  
    const seasonalIndices = Object.entries(periodAverages).map(([period, vals]) => ({
      period: parseInt(period),
      average: vals.reduce((sum, val) => sum + val, 0) / vals.length,
      count: vals.length
    }));
  
    const overallAverage = values.reduce((sum, val) => sum + val, 0) / n;
    const maxVariation = Math.max(...seasonalIndices.map(si => Math.abs(si.average - overallAverage))) / overallAverage;
  
    return {
      detected: maxVariation > 0.1, // 10% variation threshold
      strength: maxVariation > 0.2 ? 'Strong' : maxVariation > 0.1 ? 'Moderate' : 'Weak',
      periods: seasonalIndices,
      maxVariationPercent: Math.round(maxVariation * 10000) / 100
    };
  }

• src/handlers/financial-analysis.ts:579-591 (helper)

  Generates forecasted values for specified periods using the computed trend line, applying damping to avoid unrealistic long-term growth.

  private generateForecast(values: number[], periods: number, trend: any): number[] {
    const forecast = [];
    const n = values.length;
  
    for (let i = 0; i < periods; i++) {
      const predicted = trend.slope * (n + i) + trend.intercept;
      // Add some damping for long-term forecasts
      const dampingFactor = Math.max(0.3, 1 - (i * 0.1)); // Reduce growth over time
      forecast.push(predicted * dampingFactor);
    }
  
    return forecast;
  }

• src/handlers/financial-analysis.ts:593-620 (helper)

  Compiles human-readable insights from all trend analysis metrics for the final response.

  private generateTrendInsights(trend: any, avgGrowth: number, seasonality: any): string[] {
    const insights = [];
  
    if (Math.abs(trend.slope) > Math.abs(avgGrowth)) {
      insights.push(`Strong ${trend.direction?.toLowerCase() || 'unknown'} trend detected (${trend.strength || 'unknown'} strength)`);
    } else {
      insights.push(`Trend is ${trend.direction?.toLowerCase() || 'unknown'} but relatively weak`);
    }
  
    if (trend.rSquared > 0.8) {
      insights.push('High confidence in trend prediction (R² > 0.8)');
    } else if (trend.rSquared > 0.5) {
      insights.push('Moderate confidence in trend prediction (R² > 0.5)');
    } else {
      insights.push('Low confidence in trend prediction - consider more data');
    }
  
    if (seasonality.detected) {
      insights.push(`${seasonality.strength} seasonal patterns detected (${seasonality.maxVariationPercent}% variation)`);
    }
  
    const volatility = this.calculateVolatility([avgGrowth]); // Simplified
    if (volatility > 0.2) {
      insights.push('High volatility detected - consider risk mitigation strategies');
    }
  
    return insights;
  }