Data Source

Data is sourced from MAX.gov SF 133 Reports on Budget Execution and Budgetary Resources covering fiscal years 2018-2025.

The SF 133 report contains monthly budget execution data for all federal agencies. Our system processes complete fiscal years, automatically detecting available months for each year.

Multi-Year Coverage

• Historical Data (FY2018-2023): Complete fiscal year datasets with all available months
• Current Years (FY2024-2025): Updated as new monthly data becomes available
• Data Format Evolution: Handles different Excel formats (.xls/.xlsx) across years

Understanding Federal Accounts

Excel File Structure & Processing Method

Each agency provides Excel files containing multiple sheets. We use the Raw Data sheet method for maximum compatibility across years.

Why Raw Data Sheets?

• Cross-Year Compatibility: Raw Data sheets maintain consistent structure across different fiscal years
• Reliability: More stable than TAFS detail sheets which vary in format
• Comprehensive Coverage: Contains all months and quarters in standardized columns

Finding the Header Row

We search for a row containing "LINENO", "BUREAU", or "OMB_ACCT" within the first 30 rows of the Raw Data sheet.

Data Extraction - Raw Data Sheet Method

All agencies are processed using the standardized Raw Data sheet format:

Auto-Detection of Available Months

The system automatically detects which months have data for each fiscal year:

• Individual Months: Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep
• Quarterly Data: Dec (1Q), Mar (2Q), Jun (3Q), Sep (4Q)
• Latest Month Selection: Automatically uses the most recent month with valid data

Specific Lines Extracted

• Line 2490: Unobligated Balance, end of period
• Line 2500: Total budgetary resources

Processing Steps

1. Load Raw Data sheet and identify header row containing "LINENO", "BUREAU", "OMB_ACCT"
2. Forward fill key columns (BUREAU, OMB_ACCT, TAFS) down the spreadsheet
3. Convert LINENO to numeric and filter for lines 2490.0 and 2500.0
4. Auto-detect latest available month with valid data (non-zero, non-null values)
5. Extract values from the latest month column
6. Merge Line 2490 and Line 2500 data on matching Agency + TAFS combinations
7. Extract account name from TAFS (text after " - ")
8. Convert values from dollars to millions
9. Calculate percentage: (Unobligated Balance / Budget Authority) × 100

Data Validation Tests

The system runs specific validation tests before deploying data. Each test must pass:

test_year_data_completeness()

• Checks each year CSV file has all 29 expected agencies (or valid alternatives for "Other Independent Agencies")
• Requires minimum 1,000 records per year file
• Historical years (before current FY) must have ≥8,000 records for comprehensive data
• Current fiscal year accepts partial data
• Returns list of years that pass all checks

test_csv_summary_files()

• Verifies CSV files exist and are readable
• Checks for required columns: Agency, Bureau, Account
• Ensures files are not empty
• Reports structure and record counts

test_data_consistency()

• Validates main summary file has ≥1,000 records
• Requires ≥20 unique agencies
• Checks Budget Authority values start with "$" and are properly formatted
• Tests currency format parsing ("$1,234.5M" format)

test_data_reasonableness()

• Parses Budget Authority and Unobligated Balance values by removing "$", ",", "M"
• Calculates total Budget Authority, must be between $1 trillion and $50 trillion
• Checks percentage values are not extremely out of range (-1000% to +1000%)
• Reports if extreme percentages found (may be valid for negative Budget Authority)

test_cross_year_consistency()

• Calculates year-over-year Budget Authority changes between consecutive fiscal years
• Flags (but doesn't fail) if total Budget Authority changes >50% between years
• Requires at least 2 years of data to run
• Note: Agency coverage validation is handled by test_year_data_completeness()

Website Structure Tests

• test_csv_structure(): Validates 10 required columns exist, ≥100 records, ≥15 agencies per file
• test_numerical_data(): Tests Budget Authority, Unobligated Balance, and Percentage values are parseable by JavaScript parseFloat()
• test_required_fields(): Ensures Agency, Bureau, Account, Account_Number have no missing/empty values
• test_data_consistency(): Verifies main file exists with ≥1,000 records and ≥20 agencies

TAFS Parsing

The Treasury Account Fund Symbol (TAFS) contains account and year information:

Standard Agency TAFS Format

• 018-45-0243: Account number (agency-bureau-account)
• /25: Expiration year (2025)
• Federal Direct Student Loan Program Account: Account name

Other Independent Agencies TAFS Format

• 95-2300: Account number
• 24/25: Multi-year (FY2024-FY2025)
• Salaries and Expenses: Account name

Special Cases

• /X = No year accounts
• 21/25 = Multi-year accounts (FY2021-FY2025)

Data Files Used by the Application

The web application reads the following data file:

all_agencies_obligation_summary.csv - Account-level budget execution data

Code and Issues

View code and report issues: github.com/abigailhaddad/apportionment