Финален проект

В последните две занятия (15 и 16) изграждате един цялостен процес, от вида, който сега правите в SAS или SQL: месечния отчет за портфейла. Той се пуска за всеки край на месеца от историята, от суровите данни до един Excel файл, и може да се пусне отново следващия месец без промени.

1. Зареждане на данните от файлове (CSV, Excel) и от база данни (SQLite).
2. Валидиране с номенклатури, написани като класове (занятие 12): кодове, които не съществуват, стойности извън диапазона, липсващи стойности.
3. Почистване и трансформиране с pandas: типове, дати, нови колони.
4. Агрегиране: групи, pivot tables, joins.
5. Отчет: Excel файл с няколко листа и графика.
6. Кодът се пази в Git, като скрипт, който може да се пусне отново върху нови данни.

Всяка лекция добавя част, която проектът използва: функции (4), dictionaries като таблици за търсене (6), почистване на текст и регулярни изрази (7, 8), четене на файлове и обработка на лоши записи (9), модули и Git (10), класове (11, 12), бази данни (13) и pandas (14 - 16). Домашното от занятие 16 е първата част от проекта.

Задачата

Напишете report.py. Пуснат като

python report.py 2026-09-30

той записва out/portfolio_report_2026-09-30.xlsx и out/portfolio_2026-09-30.png и отпечатва кратко обобщение с проверките. С 2026-08-31 (или всеки край на месеца от 2025-10-31 до 2026-09-30) прави отчета за този месец.

Данните

Всичко е в data/ на homework repository (примерните данни):

Източник	Какво вземате от него
portfolio.db (SQLite)	таблиците clients, loans (клиентът на всеки кредит, продукт, валута, дати), rating_scale, ratings_history - със SQL и параметри (занятие 13)
balances_monthly.csv	непогасената сума и дните просрочие на всеки кредит към всеки край на месеца
client_register.csv	имената, ЕИК, IBAN и градовете на клиентите (разхвърлян текст - занятия 7, 8)
fx_rates.xlsx	курсовете на еврото за всеки работен ден, по един лист на месец (занятия 9, 14)
regions_bg.csv	кодовете и имената на регионите (cp1251 - занятие 7)

Стъпките

1. Зареждане

• От базата данни: клиентите, кредитите, рейтинговата скала и рейтингите към датата на отчета и към предишния край на месеца - датата като параметър на заявката, никога вмъкната в текста на SQL.
• От balances_monthly.csv: редовете за датата на отчета (и всички месеци до нея, за тренда).
• От fx_rates.xlsx: курсът EUR / USD от последния работен ден на или преди датата на отчета (всички листа; n/a, ако месецът го няма във файла).
• От client_register.csv: регистърът, за проверките на качеството на данните.

2. Валидиране

Използвайте своя модул homework_nomenclature.py от занятие 12 (копирайте го до report.py). Най-малко:

• кодове: продукт, сегмент, регион и рейтинг - всяка стойност трябва да е в своята номенклатура;
• стойности извън диапазона: непогасена сума под 0 или над сумата на кредита, дни просрочие под 0, лихвен процент извън 0 - 30, дата на падеж преди началната дата;
• липсващи стойности: кредит без клиент или клиент без рейтинг към датата на отчета;
• регистърът (занятия 7, 8): номера, които ги няма в таблицата с клиенти след почистването, ЕИК и IBAN с грешна форма.

Всяка неуспешна проверка е един ред в таблица на отхвърлените (rejects): наборът от данни, номерът, колоната, стойността, причината. Ред, който не минава, се отчита, никога не се изпуска мълчаливо. Отчетът казва колко реда са отхвърлени; кредитите, които минават, продължават нататък.

Примерните данни са предимно чисти, така че повечето проверки не намират нищо. Тествайте всяка проверка: направете копие на таблица, развалете я нарочно (продукт leasing, отрицателен остатък, номер на клиент C9999) и вижте, че редът се появява сред отхвърлените.

3. Трансформиране

Един DataFrame с ред за всеки кредит към датата на отчета: кредитът, остатъкът и дните просрочие, сегментът и регионът на клиента, рейтингът и класът, PD (средата на интервала), EL (PD x LGD x непогасена сума, LGD 0.45 - параметър), групата по DPD (current, 1-30, 31-90, 90+) и възрастта на кредита в месеци.

4. Агрегиране

Таблиците на отчета:

Лист	Съдържание
summary	дата на отчета, брой кредити и клиенти, непогасена сума, EL, EL / непогасена сума, делът на 90+, курсът EUR / USD, броят отхвърлени редове
segment x dpd	непогасена сума по сегмент и група по DPD, с общи суми
segment x grade	непогасена сума по сегмент и клас, класовете в реда на скалата
migration	матрицата на миграция на рейтингите: предишен край на месеца -> дата на отчета, всички рейтинги
trend	кредити и непогасена сума за всеки край на месеца до датата на отчета, с промяната спрямо предишния месец
top clients	10-те най-големи клиента: номер, име (от регистъра), сегмент, клас, непогасена сума, дял от портфейла
rejects	таблицата от стъпка 2
data quality	по набор от данни и колона: редове, липсващи стойности, отхвърлени стойности

5. Отчет

• Един Excel файл с листата по-горе (pd.ExcelWriter, занятие 16): четими ширини, числов формат за сумите, ред със заглавие в обобщението.
• Една графика (PNG): месечната непогасена сума (тренд) или непогасената сума по сегмент и група по DPD.
• Накрая скриптът отпечатва проверките (следващия раздел).

6. Git

Проектът живее в папка project/ на вашето копие на homework repository (занятие 10): report.py, homework_nomenclature.py, всеки друг ваш модул и README.md (какво прави отчетът, как се пуска, какво означава всеки лист). out/ не се commit-ва. Направете commit след всяка стъпка, която работи - историята трябва да показва стъпките.

Проверките, които отчетът ви трябва да мине

За дата на отчета 2026-09-30 (числата от занятия 4, 6, 13 и 15):

• 400 кредита, 143 клиента с кредити, непогасена сума 131,370,708.60;
• EL с LGD 0.45: 1,140,106.56;
• непогасена сума по сегмент: corporate 93,087,049.89, retail 6,837,600.42, sme 31,446,058.29;
• матрицата на миграция август -> септември: 150 клиента, 10 понижения;
• общите суми на всяка таблица = общата сума на данните (няма загубени или удвоени редове при join).

За 2026-08-31: непогасена сума 133,492,549.54 и EL 1,093,088.84.

Списък за проверка

• [ ] python report.py 2026-09-30 се изпълнява от нов терминал, във виртуалната среда, без грешки.
• [ ] Друг край на месеца работи без промяна в кода.
• [ ] Във функциите не е написана дата, път или LGD: те са параметри (или константи в началото на файла).
• [ ] SQL-ът използва параметри (? или :name).
• [ ] Номенклатурите са обекти на вашите класове от занятие 12.
• [ ] Всеки отхвърлен ред е в листа rejects, с причината си.
• [ ] Проверките по-горе се отпечатват и съвпадат.
• [ ] Всяка функция има docstring; стъпките са функции (load, validate, transform, aggregate, write), извиквани от main().
• [ ] README.md обяснява как се пуска.
• [ ] Историята в Git показва стъпките (няколко commits, ясни съобщения).

Идеи за още

• Листовете с отхвърлените и с качеството на данните, оцветени (Self-study на занятие 16).
• Контролните цифри на ЕИК и IBAN (Self-study на занятие 8).
• Отчетът за всички краища на месеца с едно пускане (цикъл по pd.date_range(freq="ME")) и един лист, който ги сравнява.
• Кредитите в USD, показани в EUR с курса към датата на отчета (приемете сумите в примерните данни за суми във валутата на кредита).
• Опция от командния ред за LGD (argparse, Self-study на занятие 10).