P
AI for Financial Analyst (Corporate)

Automated Close Pipeline: Connect Your Data to AI Commentary on a Schedule

Tools:Power Automate, Claude, Excel
Time to build:2–3 hours
Difficulty:Intermediate-Advanced
Prerequisites:Comfortable using Claude for monthly close commentary — see Level 3 guide: "Set Up Your Monthly Close Commentary Assistant"

What This Builds

Instead of manually pulling your variance table and pasting it into Claude every month, this automation connects your Excel file (or SharePoint folder) to Claude on a schedule. When your close data lands in SharePoint, Power Automate triggers automatically, sends the data to Claude, and delivers a draft commentary document to your email, ready for editing before you've had your morning coffee. You go from "3 hours of manual close work" to "30 minutes of editing AI drafts."

Prerequisites

  • Comfortable using Claude Pro for monthly close commentary (Level 3)
  • Microsoft 365 account with Power Automate access (included in most M365 Business plans)
  • Your variance file saved to OneDrive or SharePoint (not a local drive)
  • A Claude API key (separate from the Claude.ai subscription; see Step 1)

The Concept

Think of this like setting up a conveyor belt in a factory. Right now, you're manually carrying boxes (data) from one end (ERP/Excel) to another (commentary document) by hand, every single month. This automation builds a conveyor belt: data lands in one place, moves automatically through the commentary-writing step, and arrives at the other end ready for your review.

The three pieces:

  1. Trigger: When your close Excel file is updated in SharePoint → start the process
  2. Process: Send the data to Claude via API → get commentary back
  3. Deliver: Claude's draft commentary arrives in your email inbox as a formatted document

Build It Step by Step

Part 1: Set Up Claude API Access

Claude's API is different from your Claude.ai subscription. You pay per use (charged by the amount of text), and it lets you connect Claude to other tools programmatically.

  1. Go to console.anthropic.com and sign in (or create an account)
  2. Click "API Keys" in the left sidebar
  3. Click "Create Key" and name it "Close Commentary Automation"
  4. Copy the API key and save it somewhere secure (a password manager, not a sticky note)

Cost: Claude API costs approximately $0.003 per 1,000 words processed. A typical monthly close commentary run (24 line items in, 800 words of commentary out) costs under $0.10 per month. Negligible.

What you should see: An API key that starts with "sk-ant-...". This is your credential for the automation.

Part 2: Prepare Your Excel File for Automation

Your variance file needs to be structured so Power Automate can read it reliably.

  1. Open your variance Excel file
  2. Ensure the data is in a named Excel table (not just a range): click in the data → Ctrl+T → name the table "VarianceTable"
  3. The table must have clear headers that match what you'll reference in your automation: Line Item, Budget, Actual, Variance $, Variance %
  4. Save the file to a SharePoint or OneDrive folder, such as "Finance/Monthly Close/[Year]/"

What you should see: A clean table with named headers. The file is accessible in SharePoint. Verify by opening it from the SharePoint web interface.

Part 3: Build the Power Automate Flow

Power Automate is Microsoft's automation tool. It connects different apps with triggers and actions.

  1. Go to make.powerautomate.com (or from the Microsoft 365 app launcher)
  2. Click "Create" → "Automated cloud flow"
  3. Name it "Monthly Close Commentary Generator"
  4. Set the trigger: Search for "SharePoint" → select "When a file is created or modified (properties only)"
  5. Configure the trigger:
    • Site Address: Your SharePoint site
    • Library Name: The document library where your close file lives
    • Folder: "Finance/Monthly Close/[Year]/"

Click "Next Step."

Part 4: Add the Excel Data Read Step

  1. Search for "Excel Online (Business)" action
  2. Select "List rows present in a table"
  3. Configure:
    • Location: SharePoint
    • Library: Your document library
    • File: Your variance Excel file (browse to select it)
    • Table: "VarianceTable"

This step reads all rows from your variance table when the trigger fires.

Click "New Step."

Part 5: Format the Data for Claude

You need to convert the Excel rows into a text table that Claude can read.

  1. Add a "Compose" action (Data Operations → Compose)
  2. In the "Inputs" field, build a text representation of your table:

Click in the Inputs field and use the dynamic content panel to build a message like:

Copy and paste this
Here is the monthly variance table. Draft commentary for each line item following this format: "[Line item] was $[amount]M, $[variance]M [favorable/unfavorable] to budget, primarily driven by [reason]." Under 40 words per line.

Line Item | Budget | Actual | Variance $ | Variance %

Then add a "Apply to each" loop that appends each row from the Excel table.

Note: This step requires some familiarity with Power Automate's expression editor. If you get stuck, ask ChatGPT: "How do I concatenate rows from an Excel table into a text string in Power Automate?" It will give you the exact expression syntax.

Part 6: Call the Claude API

  1. Add an "HTTP" action (search for "HTTP")
  2. Configure:
    • Method: POST
    • URI: https://api.anthropic.com/v1/messages
    • Headers:
      • x-api-key: Your Claude API key (store it in a Power Automate environment variable — don't hardcode it)
      • anthropic-version: 2023-06-01
      • content-type: application/json
    • Body:
Copy and paste this
{
  "model": "claude-3-5-sonnet-20241022",
  "max_tokens": 2000,
  "messages": [
    {
      "role": "user",
      "content": "@{outputs('Compose')}"
    }
  ]
}

Replace @{outputs('Compose')} with the dynamic content reference to your composed table text.

Part 7: Send the Output to Your Email

  1. Add an action: "Send an email (V2)" from Office 365 Outlook
  2. Configure:
    • To: Your email address
    • Subject: "Monthly Close Commentary Draft: [Month]"
    • Body: Parse the Claude API response to extract the text content

To extract Claude's response from the HTTP action, use this expression in the email body:

Copy and paste this
body('HTTP')?['content'][0]?['text']

This pulls the text content from Claude's JSON response.

Part 8: Test and Refine

  1. Click "Test" in the top right corner of Power Automate
  2. Choose "Manually trigger the flow" for your first test
  3. Open your Excel file, make a minor change (add a space, then delete it), and save to SharePoint
  4. Wait 2–3 minutes and check your email

What you should see: An email arrives with Claude's drafted commentary for all line items in your variance table.

Troubleshooting:

  • If the HTTP call fails with a 401 error: your API key is wrong or not stored correctly
  • If you get an empty body in the email: the expression for extracting Claude's response needs adjustment. Paste the HTTP response body into ChatGPT and ask "how do I extract the text content from this Claude API response?"
  • If the commentary doesn't match your format: update the prompt in the Compose step

Real Example: Month-End in the New World

Setup: Sarah, a senior FP&A analyst at a $400M manufacturing company, spent 3 hours every close cycle writing variance commentary for 30 line items. She built this automation over one Saturday morning.

Input: When Sarah saves the updated variance table to SharePoint on day 3 of close week, Power Automate triggers automatically.

Output: 8 minutes later, an email arrives in Sarah's inbox with:

  • 30 draft variance comments
  • Formatted per the standard management report structure
  • Flagged items where variance exceeds $1M for her attention

Time saved: 3 hours of manual work → 20 minutes of editing What Sarah does now: Reviews the flagged items, corrects 3–4 comments where she knows the business context better than the numbers suggest, and pastes the finalized commentary into the management deck. Done before noon on day 3.

What to Do When It Breaks

  • "The flow runs but the email is empty" → The expression parsing Claude's response is wrong. Go to the flow run history, click the failed step, and look at the raw HTTP response body. Paste it into ChatGPT and ask how to extract the text content.
  • "The flow doesn't trigger" → Check the SharePoint site and library names in the trigger configuration. They must match exactly. Also check that the file is actually in the folder you specified.
  • "Claude's commentary is wrong or missing lines" → The table formatting in the Compose step may not be reading correctly. Open the flow run history and check what the Compose step actually sent to Claude. You can see the full text it composed.
  • "I got charged too much on the API" → Check your max_tokens setting in the HTTP body (2000 is a reasonable limit). You can also check your usage at console.anthropic.com.

Variations

  • Simpler version: Instead of Power Automate, manually paste your variance table into Claude each month using your configured Project. This removes the automation layer but still saves 2+ hours. A good starting point before building the full pipeline.
  • Extended version: Add a second Claude API call that generates the executive summary after the line-item commentary, so both documents arrive in one email.

What to Do Next

  • This week: Set up Claude API access and verify your Excel file is in SharePoint
  • This month: Build and test the Power Automate flow in a test environment before using it on live close data
  • Advanced: Connect this flow to your FP&A planning tool (Adaptive Planning, Datarails) API instead of Excel, so the trigger is the close cycle completion rather than a file update

Advanced guide for Financial Analyst (Corporate) professionals. Power Automate and Claude API require organizational permissions. Check with your IT/M365 admin before building. API costs are minimal (under $2/month for typical use).