Treasury Data for AI

pip install pandas numpy matplotlib jupyter prophet scikit-learn openpyxl pytest
jupyter notebook

import pandas as pd
print('Setup complete. Pandas version:', pd.__version__)

-- 1. SELECT + WHERE: cash flows for one entity from January 2025 onward
SELECT date, amount, type
FROM cash_flows
WHERE entity = 'Entity_NL'
  AND date >= '2025-01-01';

-- 2. GROUP BY + SUM: daily transactions into weekly totals
SELECT week_number, SUM(amount) AS total
FROM cash_flows
GROUP BY week_number
ORDER BY week_number;

-- 3. JOIN: bank lines matched to ERP entries by reference
SELECT b.date, b.amount, e.gl_account
FROM bank_statements b
JOIN erp_ledger e ON b.reference = e.reference;

-- 3b. LEFT JOIN: the bank lines with NO ERP match (your reconciliation breaks)
SELECT b.date, b.amount, b.reference
FROM bank_statements b
LEFT JOIN erp_ledger e ON b.reference = e.reference
WHERE e.reference IS NULL;

-- 4. CASE WHEN: categorize by description (LOWER makes it case-insensitive everywhere)
SELECT date, amount,
  CASE
    WHEN LOWER(description) LIKE '%payroll%' THEN 'Payroll'
    WHEN LOWER(description) LIKE '%supplier%' THEN 'Supplier'
    ELSE 'Other'
  END AS category
FROM cash_flows;

-- 5. LAG: previous-day balance and the daily change
SELECT date, amount,
  LAG(amount, 1) OVER (ORDER BY date) AS prev_day,
  amount - LAG(amount, 1) OVER (ORDER BY date) AS daily_change
FROM cash_positions;

df = pd.read_csv('cash_flows.csv')      # 1. load (pd.read_excel for .xlsx)
df.head()                                # 2. preview the first 5 rows
df.info()                                # 3. column types and non-null counts
df.describe()                            # 4. min, max, mean, std per column
df.isnull().sum()                        # 5. missing values per column
df = df.ffill()                          # 6. forward fill (or df.dropna())
df = df.drop_duplicates()                # 7. remove exact duplicate rows
df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y')   # 8. fix dates
weekly = df.groupby('week')['amount'].sum()                  # 9. aggregate
df['prev_week'] = df['amount'].shift(1)                      # 10. lag feature

I have uploaded treasury_cash_flows_messy.csv: 52 weeks of weekly cash flows in EUR. Columns: week_number, date, receipts, supplier_payments, payroll, tax, intercompany, other, net_flow, currency. Outflows are negative.

Use Python (pandas) to run these 6 data quality checks and show the output of each. Do not fix anything yet.
1. Completeness: df.isnull().sum(). Which columns and weeks have missing values?
2. Uniqueness: df.duplicated().sum(). Which rows are duplicated?
3. Consistency: what type is the date column, which date formats appear, and what does df['currency'].unique() show?
4. Accuracy: add up the six flow columns per week and compare with net_flow. List the weeks that do not reconcile.
5. Outliers: flag any value more than 3 standard deviations from its column mean. List week and column.
6. Timeliness: what is the date range? Any gaps or repeated weeks in the weekly sequence?

Now fix the issues. Follow these rules exactly:
1. Duplicates: drop exact duplicate rows, keep the first.
2. Dates: the file mixes DD/MM/YYYY and YYYY-MM-DD. Parse each format explicitly (format='%d/%m/%Y' and format='%Y-%m-%d'). Never let pandas guess day versus month. Confirm every date parsed and the weeks are exactly 7 days apart.
3. Missing receipts (weeks 15-17): net_flow comes from the bank statement, so rebuild receipts = net_flow minus the other five flow columns. Do not interpolate.
4. Currency: standardize to 'EUR'.
5. Supplier payments are outflows: make every value negative.
6. Week 38 holds a EUR 5.2M acquisition payment and its EUR 5.0M intercompany funding. Add non_recurring=True for that week. Flag it, do not delete it.
Then prove it: every week must now satisfy sum of flows = net_flow. Show df.info() and df.describe().

Now add 12 features for a forecasting model:
1. Time: month, quarter, week_of_year, contains_month_end, contains_quarter_end (does the week, Monday to Sunday, include a month end or quarter end?)
2. Lag: prev_week_net (shift 1), prev_4week_net (shift 4), rolling_4week_avg
3. Business: is_payroll_week (payroll is not zero), is_tax_week (tax is not zero)
4. Derived: cumulative_balance (starting from EUR 2,450,000), inflow_outflow_ratio
Show the final DataFrame and save it as treasury_cash_flows_clean.csv.