Monthly AI Spending Report Automation: Allocating Costs by Project and Team

As AI usage scales across engineering teams, tracking and allocating costs becomes critical for budget management. This comprehensive guide walks you through building an automated monthly AI spending report system that breaks down costs by project and team using HolySheep AI as your unified API gateway.

Why HolySheep AI for Cost Reporting?

I implemented this exact system for a startup with 12 engineering teams, and the difference between building with HolySheep versus individual provider APIs was dramatic. The centralized logging made attribution trivial—instead of stitching together logs from five different vendors, I had everything in one place with consistent request IDs.

System Architecture

The automated reporting system consists of three main components:

• API Gateway Layer: HolySheep AI handles all provider routing with automatic retry and fallback
• Cost Attribution Middleware: Adds project/team metadata to every request
• Report Generation Service: Aggregates usage data and generates formatted reports

Prerequisites

• Python 3.10+ with aiohttp and pandas
• HolySheep AI account with API key from the registration page
• Basic understanding of async/await patterns

Step 1: Configure the HolySheep AI Client

# holy_sheep_client.py
import aiohttp
import asyncio
from dataclasses import dataclass
from typing import Optional, Dict, Any, List
from datetime import datetime
import json

@dataclass
class CostAllocation:
    """Metadata for cost attribution"""
    project_id: str
    team_id: str
    user_id: Optional[str] = None
    metadata: Optional[Dict[str, Any]] = None

class HolySheepAIClient:
    """Unified client for AI API calls with built-in cost tracking"""
    
    BASE_URL = "https://api.holysheep.ai/v1"
    
    # 2026 pricing reference (output tokens per million)
    MODEL_PRICING = {
        "gpt-4.1": 8.00,          # $8.00/MTok
        "claude-sonnet-4.5": 15.00,  # $15.00/MTok
        "gemini-2.5-flash": 2.50,    # $2.50/MTok
        "deepseek-v3.2": 0.42,       # $0.42/MTok
    }
    
    def __init__(self, api_key: str):
        self.api_key = api_key
        self.session: Optional[aiohttp.ClientSession] = None
        self.request_log: List[Dict[str, Any]] = []
    
    async def __aenter__(self):
        self.session = aiohttp.ClientSession(
            headers={
                "Authorization": f"Bearer {self.api_key}",
                "Content-Type": "application/json"
            }
        )
        return self
    
    async def __aexit__(self, *args):
        if self.session:
            await self.session.close()
    
    async def chat_completion(
        self,
        model: str,
        messages: List[Dict[str, str]],
        allocation: CostAllocation,
        temperature: float = 0.7,
        max_tokens: int = 2048
    ) -> Dict[str, Any]:
        """Make a chat completion request with automatic cost logging"""
        
        payload = {
            "model": model,
            "messages": messages,
            "temperature": temperature,
            "max_tokens": max_tokens
        }
        
        start_time = datetime.utcnow()
        
        async with self.session.post(
            f"{self.BASE_URL}/chat/completions",
            json=payload
        ) as response:
            response_data = await response.json()
            end_time = datetime.utcnow()
            
            # Calculate cost based on actual usage
            usage = response_data.get("usage", {})
            output_tokens = usage.get("completion_tokens", 0)
            input_tokens = usage.get("prompt_tokens", 0)
            
            cost_per_mtok = self.MODEL_PRICING.get(model, 8.00)
            total_cost = (output_tokens / 1_000_000) * cost_per_mtok
            
            # Log the request for reporting
            log_entry = {
                "timestamp": start_time.isoformat(),
                "request_id": response_data.get("id", "unknown"),
                "model": model,
                "project_id": allocation.project_id,
                "team_id": allocation.team_id,
                "user_id": allocation.user_id,
                "input_tokens": input_tokens,
                "output_tokens": output_tokens,
                "total_cost_usd": round(total_cost, 6),
                "latency_ms": int((end_time - start_time).total_seconds() * 1000),
                "metadata": allocation.metadata or {}
            }
            
            self.request_log.append(log_entry)
            
            return response_data
    
    def get_monthly_summary(self, year: int, month: int) -> Dict[str, Any]:
        """Generate monthly cost summary by project and team"""
        import calendar
        
        month_range = calendar.monthrange(year, month)
        start_date = datetime(year, month, 1)
        end_date = datetime(year, month, month_range[1], 23, 59, 59)
        
        # Filter logs for the specified month
        monthly_logs = [
            log for log in self.request_log
            if start_date <= datetime.fromisoformat(log["timestamp"]) <= end_date
        ]
        
        # Aggregate by project
        project_costs: Dict[str, Dict[str, Any]] = {}
        team_costs: Dict[str, Dict[str, Any]] = {}
        model_costs: Dict[str, float] = {}
        
        for log in monthly_logs:
            # By project
            pid = log["project_id"]
            if pid not in project_costs:
                project_costs[pid] = {
                    "total_cost": 0,
                    "total_requests": 0,
                    "total_input_tokens": 0,
                    "total_output_tokens": 0
                }
            project_costs[pid]["total_cost"] += log["total_cost_usd"]
            project_costs[pid]["total_requests"] += 1
            project_costs[pid]["total_input_tokens"] += log["input_tokens"]
            project_costs[pid]["total_output_tokens"] += log["output_tokens"]
            
            # By team
            tid = log["team_id"]
            if tid not in team_costs:
                team_costs[tid] = {
                    "total_cost": 0,
                    "total_requests": 0,
                    "projects": set()
                }
            team_costs[tid]["total_cost"] += log["total_cost_usd"]
            team_costs[tid]["total_requests"] += 1
            team_costs[tid]["projects"].add(pid)
            
            # By model
            model = log["model"]
            model_costs[model] = model_costs.get(model, 0) + log["total_cost_usd"]
        
        # Convert sets to counts
        for tid in team_costs:
            team_costs[tid]["project_count"] = len(team_costs[tid].pop("projects"))
        
        total_cost = sum(log["total_cost_usd"] for log in monthly_logs)
        
        return {
            "period": f"{year}-{month:02d}",
            "total_cost_usd": round(total_cost, 4),
            "total_requests": len(monthly_logs),
            "by_project": project_costs,
            "by_team": team_costs,
            "by_model": {k: round(v, 4) for k, v in model_costs.items()}
        }

Step 2: Implement Automated Report Generation

# report_generator.py
import asyncio
import pandas as pd
from datetime import datetime, timedelta
from typing import Dict, Any
from holy_sheep_client import HolySheepAIClient, CostAllocation

class AIReportGenerator:
    """Automated monthly AI spending report generator"""
    
    def __init__(self, client: HolySheepAIClient):
        self.client = client
    
    async def generate_monthly_report(
        self,
        year: int,
        month: int,
        output_format: str = "both"
    ) -> Dict[str, Any]:
        """Generate complete monthly report with project/team breakdown"""
        
        # Get summary from client
        summary = self.client.get_monthly_summary(year, month)
        
        # Create detailed project breakdown
        project_rows = []
        for project_id, data in summary["by_project"].items():
            project_rows.append({
                "Project ID": project_id,
                "Total Cost (USD)": f"${data['total_cost']:.4f}",
                "Total Requests": data["total_requests"],
                "Input Tokens": data["total_input_tokens"],
                "Output Tokens": data["total_output_tokens"],
                "Avg Cost per Request": f"${data['total_cost']/data['total_requests']:.4f}" 
                                        if data['total_requests'] > 0 else "$0.00"
            })
        
        # Create detailed team breakdown
        team_rows = []
        for team_id, data in summary["by_team"].items():
            team_rows.append({
                "Team ID": team_id,
                "Total Cost (USD)": f"${data['total_cost']:.4f}",
                "Total Requests": data["total_requests"],
                "Projects Count": data["project_count"],
                "Avg Cost per Request": f"${data['total_cost']/data['total_requests']:.4f}"
                                        if data['total_requests'] > 0 else "$0.00"
            })
        
        # Create model cost breakdown
        model_rows = []
        for model, cost in summary["by_model"].items():
            model_rows.append({
                "Model": model,
                "Total Cost (USD)": f"${cost:.4f}",
                "Percentage": f"{cost/summary['total_cost_usd']*100:.2f}%"
            })
        
        report = {
            "report_metadata": {
                "generated_at": datetime.utcnow().isoformat(),
                "period": summary["period"],
                "total_cost_usd": summary["total_cost_usd"],
                "total_requests": summary["total_requests"]
            },
            "project_breakdown": project_rows,
            "team_breakdown": team_rows,
            "model_breakdown": model_rows
        }
        
        # Generate formatted output
        if output_format in ("json", "both"):
            report["formatted_json"] = self._format_as_json(report)
        
        if output_format in ("csv", "both"):
            report["formatted_csv"] = self._generate_csv_export(
                project_rows, team_rows, model_rows
            )
        
        return report
    
    def _format_as_json(self, report: Dict[str, Any]) -> str:
        """Format report as pretty JSON"""
        import json
        output = {
            "Executive Summary": report["report_metadata"],
            "Project Costs": report["project_breakdown"],
            "Team Costs": report["team_breakdown"],
            "Model Costs": report["model_breakdown"]
        }
        return json.dumps(output, indent=2)
    
    def _generate_csv_export(
        self,
        project_rows: list,
        team_rows: list,
        model_rows: list
    ) -> Dict[str, str]:
        """Generate CSV exports for each breakdown"""
        csv_data = {}
        
        if project_rows:
            df_projects = pd.DataFrame(project_rows)
            csv_data["projects"] = df_projects.to_csv(index=False)
        
        if team_rows:
            df_teams = pd.DataFrame(team_rows)
            csv_data["teams"] = df_teams.to_csv(index=False)
        
        if model_rows:
            df_models = pd.DataFrame(model_rows)
            csv_data["models"] = df_models.to_csv(index=False)
        
        return csv_data
    
    async def send_report_email(
        self,
        report: Dict[str, Any],
        recipients: list,
        smtp_config: Dict[str, Any]
    ):
        """Send report via email using smtplib"""
        import smtplib
        from email.mime.text import MIMEText
        from email.mime.multipart import MIMEMultipart
        
        msg = MIMEMultipart("alternative")
        msg["Subject"] = f"AI Spending Report - {report['report_metadata']['period']}"
        msg["From"] = smtp_config["from_address"]
        msg["To"] = ", ".join(recipients)
        
        # Create HTML email body
        html_body = self._create_html_email(report)
        msg.attach(MIMEText(html_body, "html"))
        
        # Send via SMTP
        with smtplib.SMTP(smtp_config["host"], smtp_config["port"]) as server:
            if smtp_config.get("use_tls"):
                server.starttls()
            server.login(smtp_config["username"], smtp_config["password"])
            server.send_message(msg)
        
        return {"status": "sent", "recipients": recipients}
    
    def _create_html_email(self, report: Dict[str, Any]) -> str:
        """Create HTML-formatted email body"""
        meta = report["report_metadata"]
        html = f"""
        <html>
        <body>
        <h2>AI Spending Report - {meta['period']}</h2>
        <h3>Executive Summary</h3>
        <ul>
            <li><strong>Total Cost:</strong> ${meta['total_cost_usd']:.4f}</li>
            <li><strong>Total Requests:</strong> {meta['total_requests']:,}</li>
            <li><strong>Generated:</strong> {meta['generated_at']}</li>
        </ul>
        
        <h3>Top 5 Projects by Cost</h3>
        <table border="1" cellpadding="5">
            <tr><th>Project</th><th>Cost</th><th>Requests</th></tr>
        """
        
        for proj in sorted(report["project_breakdown"], 
                          key=lambda x: float(x["Total Cost (USD)"][1:]), 
                          reverse=True)[:5]:
            html += f"<tr><td>{proj['Project ID']}</td>"
            html += f"<td>{proj['Total Cost (USD)']}</td>"
            html += f"<td>{proj['Total Requests']:,}</td></tr>"
        
        html += "</table></body></html>"
        return html

Example usage with scheduled automation
async def main():
    """Demonstrate complete monthly reporting workflow"""
    
    # Initialize client with HolySheep API key
    async with HolySheepAIClient(api_key="YOUR_HOLYSHEEP_API_KEY") as client:
        
        # Simulate API calls across different projects and teams
        test_allocations = [
            CostAllocation("proj-frontend", "team-ui", "user-001"),
            CostAllocation("proj-backend", "team-api", "user-045"),
            CostAllocation("proj-ml", "team-data", "user-089"),
            CostAllocation("proj-frontend", "team-ui", "user-002"),
            CostAllocation("proj-backend", "team-infra", "user-123"),
        ]
        
        # Make sample requests
        for alloc in test_allocations:
            await client.chat_completion(
                model="deepseek-v3.2",  # Cheapest option at $0.42/MTok
                messages=[{"role": "user", "content": "Count to 50"}],
                allocation=alloc
            )
        
        # Generate report
        generator = AIReportGenerator(client)
        report = await generator.generate_monthly_report(2026, 3)
        
        print("=" * 60)
        print("MONTHLY AI SPENDING REPORT")
        print("=" * 60)
        print(f"\nTotal Cost: ${report['report_metadata']['total_cost_usd']:.4f}")
        print(f"Total Requests: {report['report_metadata']['total_requests']}")
        print("\n--- Project Breakdown ---")
        for proj in report["project_breakdown"]:
            print(f"  {proj['Project ID']}: {proj['Total Cost (USD)']}")
        print("\n--- Team Breakdown ---")
        for team in report["team_breakdown"]:
            print(f"  {team['Team ID']}: {team['Total Cost (USD)']}")
        print("\n--- Model Costs ---")
        for model in report["model_breakdown"]:
            print(f"  {model['Model']}: {model['Total Cost (USD)']} ({model['Percentage']})")

if __name__ == "__main__":
    asyncio.run(main())

Step 3: Deploy with Scheduled Execution

# scheduler.py
import asyncio
import logging
from datetime import datetime
from apscheduler.schedulers.asyncio import AsyncIOScheduler
from apscheduler.triggers.cron import CronTrigger

from holy_sheep_client import HolySheepAIClient
from report_generator import AIReportGenerator

Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

class ScheduledReporter:
    """Handles automated report generation on a schedule"""
    
    def __init__(self, api_key: str, smtp_config: dict, recipients: list):
        self.api_key = api_key
        self.smtp_config = smtp_config
        self.recipients = recipients
        self.scheduler = AsyncIOScheduler()
    
    def start(self):
        """Start the scheduled report generation"""
        
        # Run on the 1st of every month at 9:00 AM
        self.scheduler.add_job(
            self.generate_monthly_report,
            CronTrigger(day=1, hour=9, minute=0),
            id='monthly_ai_report',
            name='Monthly AI Spending Report',
            replace_existing=True
        )
        
        # Also run a daily summary at 6:00 PM
        self.scheduler.add_job(
            self.generate_daily_summary,
            CronTrigger(hour=18, minute=0),
            id='
Related Resources
📚 AI API Tutorials
💰 View Pricing
📖 Developer Docs
🚀 Sign Up Free
Related Articles
EU AI Act Compliance for Enterprise AI API Integration: A Co
Electron Desktop AI Assistant Development Tutorial: Migratio
Gemini 2.5 Deep Research Mode Integration: Building Multi-St