zrosenfield/quarterly-expense-report

Quarterly Expense Report

Reads the Expenses library from the current SharePoint site, filters to the requested fiscal quarter, and generates a single self-contained interactive HTML file that serves as an executive expense summary.

---

Step 1: Determine the Fiscal Quarter

Check the site context (SHAREPOINT.md) for the fiscal year start month. If it is not specified, default to January.

Map the requested quarter label (Q1, Q2, Q3, Q4) to the three calendar months it covers based on that fiscal year start. For example, if the fiscal year starts in July:

| Quarter | Months | |---------|--------| | Q1 | July, August, September | | Q2 | October, November, December | | Q3 | January, February, March | | Q4 | April, May, June |

Determine the fiscal year label for the report heading (e.g., "FY2026 Q1" for July–September 2025 in a July fiscal year).

---

Step 2: Read and Filter Expense Data

Read all items from the Expenses library. Collect these columns: Vendor Name, Transaction Date, Total Spend.

Filter to items where Transaction Date falls within the three calendar months of the target quarter. If no items are found for the period, note that the quarter has no recorded expenses and still produce the HTML file with the zero-state layout.

---

Step 3: Compute Summary Metrics

From the filtered items, calculate:

• Total Spend — sum of all Total Spend values in the quarter
• Vendor Count — number of distinct Vendor Name values
• Transaction Count — total number of line items
• Largest Vendor — the vendor with the highest summed Total Spend, and their total

Also compute:

• Spend by vendor — sum of Total Spend grouped by Vendor Name, sorted descending
• Monthly spend — sum of Total Spend grouped by each of the three months in the quarter

---

Step 4: Generate the HTML Report

Produce a single, completely self-contained HTML file with all CSS and JavaScript embedded inline. Load Chart.js (version 4) from CDN — the only external dependency.

Styling

Use a neutral corporate palette:

| Token | Value | |---|---| | Primary (navy) | #1E3A5F | | Accent (blue) | #2563EB | | Success (green) | #059669 | | Warning (amber) | #D97706 | | Neutral (gray) | #6B7280 | | Page background | #F1F5F9 | | Card background | #FFFFFF | | Font stack | system-ui, -apple-system, "Segoe UI", sans-serif |

Page Layout

Header — full-width navy bar. Left side: "EXPENSE REPORT" in white bold tracked caps, subtitle with the fiscal quarter label (e.g., "FY2026 Q1 · July – September 2025"). Right side: report generation date.

Metrics bar — four cards in a row: Total Spend (green), Vendor Count (blue), Transaction Count (gray), Largest Vendor with amount (amber). Each card shows the primary value large, with a label below and a colored left-border accent.

Spend by vendor chart — titled "Spend by Vendor." Horizontal bar chart using Chart.js. One bar per vendor, sorted largest to smallest. Bars in the accent blue color. Show currency-formatted values as labels at the end of each bar. Minimum chart height scales with vendor count (40px per bar, minimum 200px).

Monthly spend chart — titled "Monthly Spend." Vertical bar chart using Chart.js showing total spend for each of the three months in the quarter. Use a gradient from lighter to darker blue (or a single accent color). Include a dotted average-line annotation if month-to-month variance is greater than 20%.

Transaction table — titled "All Transactions." Columns: Vendor Name, Transaction Date (formatted as Month D, YYYY), Total Spend (currency formatted), and a Notes column if the Expenses library includes one. Sort by Transaction Date ascending. Use alternating row shading. Make the table horizontally scrollable on narrow viewports.

Footer — site URL, "Generated by SharePoint AI," and the generation timestamp in small gray text centered at the bottom.

Design Standards

Use CSS Grid for the page layout and Flexbox within cards. Cards should have box-shadow: 0 1px 3px rgba(0,0,0,0.08) and border-radius: 8px. Include a @media print block that removes the header background and sets text to black. Minimum body font size 14px. Apply a 0.25-second fadeIn animation on all cards.

If Chart.js fails to load from CDN, fall back to a plain <table> summary rather than showing a broken chart area.

---

Output

Deliver a single HTML file named Expense-Report-[Quarter]-[FY].html (e.g., Expense-Report-Q1-FY2026.html). Do not include any explanatory prose outside the file — the entire response is the file content.

If the site context specifies a folder for saving reports, save the file there after generating it.