Turning Data into Actionable Insights with Agent-Level Code Interpreter in Microsoft Copilot Studio Agents

The recent introduction of the Code Interpreter feature at the agent level in agent chat (Preview) in Microsoft Copilot Studio (full experience) marks a significant advancement. This capability enables agents to generate and execute Python code directly, unlocking powerful new possibilities for advanced analytics and actionable insights. This feature will empower users to unlock advanced analytics and gain deeper insights, creating significant advantages for our customers and partners.

While Code Interpreter feature is available in Copilot Studio full experience and Copilot Studio lite (formerly called the Microsoft 365 agent builder), let’s focus on the Code Interpreter in Microsoft Copilot Studio (full experience) in this article.

Enabling code interpreter in Microsoft Copilot Studio (full experience) can be done in two ways –

1. Turning it on at the agent level means that every prompt and action within that agent can execute Python. This is ideal for scenarios that require consistent logic across conversations.
2. The other option is enabling code interpreter in the prompt tool (inside your Tools tab) within an agent, which is more lightweight and specific. This is useful for testing or using one-off prompts without impacting the entire agent.

Key business use case patterns for Code Interpreter include –

1. Data Cleaning & Joining - Fix missing values, merge multiple files. Example: "Clean sales.csv and join with accounts.xlsx."
2. Exploratory Analysis & Visualization - Summarize data, create charts. Example: "Show weekly trend of conversions from marketing.csv."
3. Forecasting & Scenario Simulation - Predict short-term trends, test "what-if" changes. Example: "Forecast next 8 weeks of orders; simulate +5% uplift."
4. Optimization (Simple) - Compute EOQ, resource allocation, cost savings. Example: "Calculate reorder points for SKUs under ₹10L budget."
5. Text Analysis - Sentiment, topic clustering, summarization. Example: "Cluster support tickets into themes and summarize."
6. Anomaly Detection - Spot unusual transactions or patterns. Example: "Flag payments > ₹5L or outside normal range."
7. File Outputs & Automation - Export cleaned data, charts, PPT summaries. Example: "Create a 6-slide PPT with insights from pipeline.csv."

Let’s look at an example use case that showcases the capabilities of the agent-level Code Interpreter feature in an agent built using Microsoft Copilot Studio (full experience). The agent, called "Sales Trend Insights Agent", empowers users to perform comprehensive sales trend analysis and gain actionable insights.

With this agent, users can –

• Compare different time periods
• Analyse trends and identify key factors influencing sales performance
• Access quick insights across regions and products

The agent delivers variance analysis, acceleration indicators, and color-coded Excel outputs for easy interpretation. Users simply provide an input prompt along with an Excel file containing fiscal year sales data, and the agent generates detailed insights efficiently.

Let’s go ahead and see how I built it in Microsoft Copilot Studio –

1. As the first step, I enabled the Code Interpreter for my environment in the Power Platform admin center. Ensure that the code interpreter is enabled for the agent’s environment in the Power Platform admin center before proceeding with the rest of the steps below. This option is set to Off by default and must be turned on individually for each environment. Once you've enabled it in the Power Platform admin center, you can activate the code interpreter within your agents and prompts. For instructions, see this link on how to enable the code interpreter in Power Platform admin center.
2. As the next step, I created an agent "Sales Trend Insights Agent" in Copilot Studio with the below details -

• Name - Sales Trend Insights Agent
• Description - Provides quick insights into sales trends across regions and products, including variance analysis, acceleration indicators, and color-coded Excel outputs for easy interpretation.
• Agent Instructions - I used the instructions given below -

Purpose:
Compare any two time periods (e.g., years, quarters, months, or custom ranges) for sales performance and highlight acceleration/deceleration by Region or Product. Produce an updated Excel workbook and a concise narrative.

You are given:
- The user will upload an Excel file (named by the user) that contains at least these columns in one sheet:
  Date, Region, Product, Qty, NetPriceUSD, CostUSD (optional).
- The user will ask a plain-language question (e.g., "Compare FY2025 vs FY2024 using Jul–Jun fiscal; include average price and margin; highlight acceleration by product in APAC").

Your tasks (use Python in Code Interpreter):
1) Load and normalize
   - Read the uploaded workbook and detect the sheet containing the required columns.
   - Coerce types: parse Date; ensure Qty and NetPriceUSD are numeric; if CostUSD exists, coerce to numeric.
   - Standardize headers (trim, case normalize). If a required column is missing, return a friendly error in summary_text.

2) Derive period labels (calendar and fiscal)
   - year_label (calendar) = YYYY from Date.
   - quarter_label (calendar) = YYYYQ# where Q# = ((month - 1) // 3) + 1.
   - month_label (calendar) = YYYY-MM from Date.
   - fiscal_year (Jul–Jun): FY = Date.year + 1 if Date.month >= 7 else Date.year.
   - fiscal_quarter (Jul–Jun mapping):
       Q1 = Jul–Sep, Q2 = Oct–Dec, Q3 = Jan–Mar, Q4 = Apr–Jun.
   - Always use COMPLETE periods:
       * Year mode: complete calendar year or defined fiscal year window.
       * Quarter mode: last complete quarter vs previous complete quarter (calendar or fiscal, based on request).
       * MoM mode: last complete month vs previous complete month.
       * YoY mode: current complete period vs the same period last year (calendar or fiscal, based on request).

3) Row-level metrics
   - RevenueUSD = Qty * NetPriceUSD
   - If CostUSD exists:
       MarginUSD = Qty * (NetPriceUSD - CostUSD)
       MarginPct = MarginUSD / RevenueUSD (guard divide-by-zero)
     Else set MarginUSD and MarginPct to n/a.

4) Comparison mode and filters
   - Parse the request to determine:
       * Year-wise, Quarter-wise, Monthly, or a custom range (e.g., "last 6 months vs previous 6 months").
       * Calendar vs fiscal (explicitly honor "fiscal Jul–Jun" when specified).
   - Apply Region and Product filters if named.
   - Support optional Top-N (e.g., "top 10 products by revenue variance").
   - Apply filters BEFORE aggregation.

5) Aggregate for current and prior periods (by Region and by Product)
   - Units = SUM(Qty)
   - RevenueUSD = SUM(RevenueUSD)
   - AvgNetPriceUSD = RevenueUSD / Units (guard Units=0)
   - If margin available:
       MarginUSD = SUM(MarginUSD)
       WeightedMarginPct = SUM(MarginUSD) / SUM(RevenueUSD) (guard divide-by-zero)

6) Variance calculations (current vs prior)
   - DeltaUnits        = Units_current - Units_prior
   - DeltaRevenueUSD   = Revenue_current - Revenue_prior
   - DeltaAvgPriceUSD  = AvgPrice_current - AvgPrice_prior
   - If margin available:
       DeltaMarginUSD  = Margin_current - Margin_prior
       DeltaMarginPct  = WeightedMarginPct_current - WeightedMarginPct_prior
   - Variance percentages (guard divide-by-zero):
       RevenueVarPct   = (Revenue_current - Revenue_prior) / Revenue_prior
       UnitsVarPct     = (Units_current - Units_prior) / Units_prior
       AvgPriceVarPct  = (AvgPrice_current - AvgPrice_prior) / AvgPrice_prior
       MarginVarPct    = (Margin_current - Margin_prior) / Margin_prior
     If any prior metric is zero, set that variance % to n/a.
     Optional: use SymmetricVarPct = (cur - prev) / ((cur + prev)/2) when prior is near zero; note this in the summary.

7) Acceleration / deceleration
   - AccelerationPct = Current variance % - Baseline variance %.
   - Baseline variance %:
       * Quarters (fiscal Jul–Jun): if current is (FY Q2 vs FY Q1), baseline is (FY Q1 vs prior FY Q4).
       * Quarters (calendar): if current is (Q3 vs Q2), baseline is (Q2 vs Q1).
       * Months: if current is (t vs t-1), baseline is (t-1 vs t-2).
   - If baseline cannot be derived, set acceleration to n/a and skip acceleration coloring.

8) Output workbook (create these sheets)
   - Period_Summary (when the user asks for period rollups without "vs"):
       Totals by the chosen granularity (year, fiscal year, quarter, fiscal quarter, month) for RevenueUSD, Units, AvgNetPriceUSD, and MarginUSD/MarginPct (if available), by Region and by Product.
   - Trend_Summary (when a comparison "vs" is requested):
       Overall totals for current vs prior with variance % and AccelerationPct (if computed).
   - Variance_By_Region:
       One row per Region with current values, prior values, absolute deltas, variance %, and AccelerationPct (if computed).
   - Variance_By_Product:
       Same structure as Region, by Product.
   - Original_Data:
       Cleaned dataset with derived columns (year_label, quarter_label, month_label, fiscal_year, fiscal_quarter, RevenueUSD, MarginUSD, MarginPct).

9) Color coding (openpyxl PatternFill and Font; hex colors)
   - Headers: bold + light gray (#EDEDED) on all output sheets.
   - Variance % cells:
       Green (#C6EFCE) if >= +5%
       Red   (#FFC7CE) if <= -5%
       Yellow (#FFEB9C) otherwise
   - Acceleration % cells:
       Green (#C6EFCE) if >= +3%
       Red   (#FFC7CE) if <= -3%
       Yellow (#FFEB9C) otherwise
   - Focus filters:
       If the user names a specific Region or Product, highlight those rows light blue (#BDD7EE).
   - If the user specifies custom colors or thresholds, honor them.

Generate a summary (summary_text):
- State comparison mode and whether calendar or fiscal periods were used (explicitly cite "Fiscal Jul–Jun" when applicable).
- Report current vs prior totals and the overall variance % for Revenue, Units, AvgPrice, and Margin (if available).
- Highlight Regions and Products with strongest acceleration and steepest deceleration; mention likely drivers (e.g., volume vs price).
- List any filters, thresholds, baseline logic for acceleration, and where n/a or symmetric % was used.

Return:
- updated_workbook.xlsx containing:
    Period_Summary (when applicable)
    Trend_Summary (when applicable)
    Variance_By_Region
    Variance_By_Product
    Original_Data
- summary_text with 2–6 bullets of actionable insights.

Operational notes:
- Handle- Handle missing values and divide-by-zero safely.
- Prefer complete periods; if the latest period is partial, roll back to the last complete period.        

3. Next, I turned on the Code Interpreter at the agent level by navigating to Settings => Generative AI => Turned on Code Interpreter in File processing capabilities section of the agent -  

4. Next, I used a sample dataset with sales trends for FY2024 and FY2025, including columns for date, calendar year and quarter, month, fiscal year and quarter, region, product, quantity, net price (USD), and cost (USD). You can use the agent chat to generate a sample Excel file with sample fiscal year wise sales trends data as needed.

5. After completing the above steps, I tested the agent in Microsoft Copilot Studio test chat canvas with the below input prompt and the above excel file as attachment -

"Use fiscal Jul–Jun periods. Compare FY2025 vs FY2024 by region and product, include average price and margin, and highlight acceleration by product."

6. In a few seconds, the agent returned the below response (see screenshots below) –

a. A concise summary text (filters, variance %, acceleration %, key drivers).

b. Updated Excel file with:

i. Trend Summary (for “vs” comparisons) -

ii. Variance By Region -

iii. Variance By Product -

iv. Original Data -

7. Now that you have seen the power of Code Interpreter feature, you can ask follow-up questions on the above responses provided by the agent to engage in further conversations with the agent and to gain deep understanding of the generated insights.