trend_analysis
Analyze time series data to identify trends, growth rates, seasonality patterns, and generate forecasts for sales and performance metrics.
Instructions
Analyze time series trends, growth rates, seasonality, and forecasting for sales and performance data
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| filePath | Yes | Path to the CSV or Excel file with time series data | |
| sheet | No | Sheet name for Excel files (optional) | |
| dateColumn | Yes | Column name or index containing date/time values | |
| valueColumn | Yes | Column name or index containing numeric values to analyze | |
| periods | No | Number of future periods to forecast (default: 12) |
Implementation Reference
- 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) }] }; } }
- 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 }; }
- 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 }; }
- 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; }
- 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; }