Auto-Generate Monthly Financial Reports with n8n + Gemini AI

SQL data → AI analysis → HTML report → Outlook email, automatically delivered monthly per Cost Center

Who

Teams needing automated monthly financial reports for multiple departments or cost centers, using ERPNext or MySQL/PostgreSQL databases

You get

Automatically triggers monthly to generate HTML reports with P&L, WIP, and employee stats for each Cost Center, analyzed by Gemini AI and sent via email

You need

n8n · mysql · outlook

Steps

1. Set up scheduled trigger and time calculation

   Configure the Schedule Trigger node in n8n to fire on the 5th of each month. Connect to the Date & Time node to capture the current date, then use the PreviousMonth Code node to calculate the previous month and year (outputs previousMonth and year for dynamic SQL filtering).

2. Fetch and loop over Cost Centers

   Use the Get Cost Centers with Budgets SQL node to query all cost centers with budget and GL data for the period. Optionally use the Filter node to limit to a specific Cost Center (e.g., 'AI DEPARTMENT') for testing. Then Loop Over Items iterates through each cost center to generate individual reports.

3. Query four financial data streams in parallel

   For each Cost Center, execute four SQL queries simultaneously: 1) YTD vs Previous Month1: budget vs actual revenue/expenses (YTD and prior month); 2) Departments: income, expenses, and profit by vertical (sub-division); 3) Projects: contract value, invoice %, cost %, WIP; 4) Employees: headcount, joined this year/month. All queries use dynamic month/year from PreviousMonth.

4. Merge data into HTML report

   Each query result passes through CostCenter, verticalPL, WIP1, and Employees1 Code nodes to convert to HTML tables. The Merge node combines all four tables, and the Code node assembles them into a single responsive HTML page with styled sections: Financial Overview – YTD & PM Summary, Vertical Profit & Loss, WIP Summary, and Employees.

5. AI Agent analysis and report refinement

   Feed the merged HTML into the Business Performance AI Agent node, which calls Google Gemini 2.5 Pro for structured financial analysis. The AI generates an executive summary, standard P&L analysis (with budget vs actual, gross margin, net margin), vertical performance summary, project progress, and per-employee revenue/profit metrics, outputting optimized HTML with CSS styling.

   Execution prompt · Source text。Business Performance AI Agent (Analyst) node text parameter (promptType=define)

   Copy prompt

   =You are a Business Performance Analyst Expert in Financial Management of Syncbricks LLC,  you willbe given an input to Analyze the Financial Performance of syncbricks which is an AI and Automation Company.
   The sections Given to you will be below
   
   1. Financial Overview – YTD & PM Summary (whis is income and expense of the Company, this data doesn't include the calculation of Gross Profit and Net Profit that you must do. This should be used together in one section only don't seperate them)
   2. Financial Overview – Vertical Profit & Loss (This containers the Verticials within the Cost Center, these are in fact the Sub Business Unit, you don't need to calculate anything but the information is for analysis to see how the Verticals within the Business Unit Perfored )
   3. Financial Overview – WIP Summary (This is the Projects Summary about how many projects are currently open and work is the WIP - Work in Process of the Project, the WIP figure should be added for Profit and Loss Statement Under Proejct Reveneu as expected WIP)
   4. Employees in the Business Unit (These will be the number of Employees who are directly working in this BU, which means these employees are only serving the Company, other staff which are in back office support are not included in this. )
   *** Your Role ***
   
   Your Role is to Prepare a comprehensive Report : 
   1. Executive Summary : Tell how was the overall performance of the Business Unit.
   2. Analayze the Financial Overview - YTD together with PM Summary (Which is Previous Month) which is previous month Income and Expenses you must use Proper Strcuture of Financial Statement with Budget Vs Actuals (follow same input already given). 
   
   You must use the Section that will be shared with you in detail that says "Financial Overview – YTD & PM Summary " and from there you must ensure to create sections based on best Practices of Performance Analysis that should include;
   
   Section: Revenue
   -Projects - Revenue
   -Trading - Revenue
   -Service - Revenue
   Net Sales (Total)
   Section: Project Work in Process
   WIP (Revenue to Book)
   Total: Gross Sale
   Section: Cost of Sale
   Projects - COGS
   Trading - COGS
   Service - COGS
   Total Cost of Sales
   Gross Profit
   Other Income
   Gross Income
   
   Section: Indirect Cost (here you must all the detail of expenses from "Financial Overview – YTD & PM Summary"
   Then you must calculate
   Profit / (Loss) before tax
   NP % to Revenue
   
   Use the calculator tool to ensure precise calculations.
   
   
   - Don't add decimals give format in currency but don't add symbol.
   Use the standard P&L Format where you should first use Sale, then Cost of Sale and GP, then use Indirect Expenses and then Net Profile. Ensure to calculate the Perentages as well.
   3. Provide the Summary of the Verticials Performance with their Profit and Loss and Percetage and Total of all Verticlals
   4. Current Project Progress.
   5. Employees Summary, Number of Employees, How many joined in this Year and what is per Eployee Revenue and Gross Profit 
   
   Analyze Overall Performance of the Company and provide the Business Managers an Overview of what should be done next. Calculate per employee profit as well and suggest what is the performance overall
   
   **Tools**
   Use calculator tool to do all calculations for accurate calculations
   
   ** Formatting and Output **
   
   Give output in html format fully responsive in with beauty added using CSS,  ready to be shared with all possible tables, in hightly attracrtive format, headings, add colors green for good, red for bad, organge for acceptable and so on, also add remarks to show how the performance was, enusre to response as if you were as an Expert in Analyzing the Bususiness Performance. Don't add any other infomration or symbols which are not part of html. Don't give additional message saying ok, I will do and so on.
   Align Text in table on left and numbers on right
   Number formatting : ##,###
   
   
   Here is the data
   
   Month : {{ $('PreviousMonth').first().json.previousMonth }}
   Year  : {{ $('PreviousMonth').first().json.year }}
   
   {{ $json.html }}

6. Send email report

   The Financial Performance Code node cleans markdown code block markers from the AI output. The Email Data node assembles subject, body, and month info. The Microsoft Outlook node sends the HTML email to the configured recipient (default placeholder [email protected] must be replaced).

7. Control loop interval

   The Wait node pauses for a configured duration (default: minutes) to prevent email overload or API rate limiting when processing multiple Cost Centers, then returns to Loop Over Items for the next iteration.

You are setting up an n8n automation workflow to auto-generate and send monthly financial reports. Core flow: Schedule Trigger (5th of each month) → SQL queries (YTD/prior month finances, departments, projects, employees) → Loop Over Items per Cost Center → merge to HTML → Gemini AI Agent analysis → Outlook email delivery. Confirm you have: n8n instance, database connection (ERPNext/MySQL/PostgreSQL), Gemini API Key, Outlook/Gmail credentials. To swap data sources, replace SQL nodes with Excel/Google Sheets/Airtable/API while maintaining the same output structure.