12 KiB
Как устроены текущие Excel-файлы ДРРК
Разобрано по файлам «Отчет по портфелю Андеррайтинга_2026_19.02.2026.xlsm» (UW) и «Отчет по портфелю Продаж_2026_19.02.2026.xlsm» (SALES), состояние на дату отчёта 17.02.2026. Макросы в обоих файлах идентичны (исходники — docs/vba/). Формулы отличаются в деталях, отмеченных ниже.
Это описание текущей реализации, а не требования. Требования — в ТЗ аналитика; где они расходятся с Excel — см. открытые вопросы в project.md.
Листы
| Лист | Назначение |
|---|---|
| SUMMARY | Итог портфеля на дату отчёта: блок «на дату размещения», блок «на дату отчёта», итоги |
| INPUTS | Сделки (копия выгрузки QUIK + ручные строки: купоны, перенос позиции на 01.01) |
| SECURITIES INFO | Параметры бумаг + рыночные данные на дату отчёта |
| CALC | Расчёт: строки сделок (кол. B–M) и сводка по бумаге (кол. P–AD) |
| FUNDING | Календарь накопленного фондирования на 1 бумагу по дням года + таблицы ставок |
| FX_RATES | Курсы CNY по датам (ЦБ) |
| SUMMARY_T-1 | Копия SUMMARY значениями на прошлую дату — для «изм. за день» |
| RES_HISTORY | Архив итогов по дням с 2020 года |
| DEALS | Сделки, сгруппированные для отчёта |
| moex_tmp | Технический буфер загрузки |
| control | Список праздников (до 12.06.2023!), пример формата даты |
| РЕПО (только SALES) | Отдельный расчёт овернайт-РЕПО, в итог не входит — не переносим |
Дата отчёта
REP_DT = WORKDAY.INTL(TODAY(), -1, 1, control!U2:U100) — предыдущий рабочий день. Список праздников заканчивается 12.06.2023, поэтому после праздников 2024–2026 дата считается неверно.
Макросы
«Добавить бумагу» (Module2, Button2_Click)
InputBox по очереди: краткое название, ISIN, объём покупки (шт), цена покупки, дата размещения, валюта (RUB/CNY), номинал, ставка купона, доходность выпуска. Пишет:
- SECURITIES INFO: A=ISIN, B=название, C=цена покупки, I=номинал, J=валюта, L=дата размещения, M=кол-во, N=купон, O=доходность покупки;
- INPUTS: строку Buy (дата = дата размещения, J и K = цена × кол-во × номинал / 100);
- FUNDING: строку бумаги с копией формул;
- SUMMARY: строку с копией формул.
«Обновить котировки» (Module1, upd_quotes)
Для каждой строки SECURITIES INFO, где ISIN в колонке A не пуст и дата отчёта > даты размещения (L):
GET https://iss.moex.com/iss/history/engines/stock/markets/bonds/securities/{ISIN}/?from={REP_DT}&limit=1 (XML, без указания борда — берётся первая строка ответа).
- D (дюрация, лет) = DURATION / 365
- E (доходность) = YIELDATWAP, если пусто — YIELDCLOSE
- F (чистая цена) = WAPRICE, если пусто — MARKETPRICE3
- H (НКД) = ACCINT
Иначе (ISIN пуст или бумага не размещена): D = (L − сегодня)/365, E = O (доходность покупки), F = 100, H = 0.
Практика пользователей: у закрытых позиций ISIN переносят из колонки A в колонку P — так бумага перестаёт обновляться.
Затем вызывает сортировку SUMMARY и обновление курсов.
Курсы (Module4, UpdFxRates)
HTML-страница cbr.ru/currency_base/dynamics по R01375 (CNY) с (последняя дата в FX_RATES + 1) по REP_DT. Курс = значение / номинал. Дописывает строки в FX_RATES. Колонка USD есть, но не заполняется.
«Создать отчёт» (Module3, Button3_Click)
Копирует SUMMARY значениями + DEALS в новую книгу, применяет тему оформления с сетевого диска F:\_DCM\03 - GENERAL\CORP STYLE\RSHB.xml.
«Архивировать» (Module3, Button4_Click)
SUMMARY → SUMMARY_T-1 значениями; в RES_HISTORY дописывает строку из ячеек SUMMARY B4, B6, B7, B9, B10, B11, B13. Баг: после изменения вёрстки SUMMARY эти адреса указывают не туда. В последних строках: колонка «Переоценка» содержит MTM, «Фондирование» — дюрацию, «Купонный доход» и «Итого объём» пустые, само фондирование не архивируется. История в RES_HISTORY за последние периоды семантически неверна.
«Купон» (Module5, Button5_Click)
Недописан: открывает выбор бумаги и ничего не делает. Купоны вводятся вручную строкой cpn в INPUTS.
CALC — строки сделок (кол. B–M, по одной на строку INPUTS)
Обозначения: FX = 1 для RUB, курс CNY на дату сделки (VLOOKUP по FX_RATES, приблизительный поиск) для CNY.
| Кол. | Смысл | Формула |
|---|---|---|
| B–I | Дата, бумага, операция, валюта, кол-во, цена, сумма без НКД, сумма с НКД | из INPUTS |
| J | НКД/КД | Buy: −(с НКД − без НКД) × FX; иначе (с НКД − без НКД) × FX |
| K | Реализованная переоценка | Buy или Cpn: 0; иначе (цена − цена покупки) × 10 × FX × кол-во — номинал 1000 захардкожен |
| L | Фондирование | Sell или Val: −кол-во × накопленное фондирование на 1 бумагу из FUNDING на дату сделки − 1; иначе 0 |
| M | Cash flow | Buy: −сумма с НКД; иначе +сумма с НКД |
Операции, встречающиеся в данных и формулах: Buy, Sell, cpn, nom, Val. В данных 2026 года — только Buy, Sell, cpn.
CALC — сводка по бумаге (кол. P–AD, по одной на бумагу)
| Кол. | Смысл | Формула |
|---|---|---|
| R | Куплено | SECURITIES INFO M |
| S | Остаток | Σ кол-во Buy − Σ кол-во Sell |
| T, U, V | Чистая цена, грязная цена, цена покупки | SECURITIES INFO F, G, C; грязная G = F + H / (номинал / 100) |
| Z | Реализованный (без фондирования) | Σ J(sell) + Σ K(sell) + Σ J(cpn) + Σ J(Buy) × (R − S) / R. Только в первой строке (РЭО-02) добавлено + Σ K(nom) |
| AB | Нереализованная переоценка | (T − V) × S × номинал / 100 × FX_последний |
| AC | Нереализованный КД | (U − T) × S × FX_последний × номинал / 100 + Σ J(Buy) × S / R |
| AD | Нереализованное фондирование | −S × накопленное фондирование на 1 бумагу на REP_DT |
| AA | Нереализованный | AB + AC |
| W, X, Y | Переоценка, КД, фондирование итого | Σ по сделкам + соответствующий нереализованный |
Номинал в AB/AC берётся из SECURITIES INFO (фактический), в K — 1000. Для амортизируемых бумаг (РЭО-02, номинал 750,02 в феврале, 645,19 в октябре) это расходится — открытый вопрос.
FUNDING
- Строка 1: даты с 01.01 года REP_DT по 31.12 (366 колонок C…ND).
- Строки 2–4: ставка RUB / CNY / USD на дату — VLOOKUP по таблице ставок B77:E… (дата начала действия, RUB, CNY, USD; вводится вручную).
- Строка 5: индивидуальная ставка РЭО-02 из таблицы L88:O… = ключевая ставка (вводится вручную) + спред КУАП 2,42.
- Строки 7+: по бумаге — накопленное фондирование на 1 бумагу: в каждый день, когда дата < REP_DT и до этой даты была хотя бы одна покупка, прибавляется номинал × ставка валюты / 100 / 365. Для РЭО-02 вместо ставки валюты — строка 5.
- Фондирование считается от номинала, а не от цены покупки.
- Календарь начинается с 01.01 — поэтому каждый год новый файл, а переходящие позиции заводятся покупкой 01.01 (РЭО-02: 01.01.2026, Buy, 2 834 360 шт по 97,97, комментарий
/trde; в сводном файле пометка «цена входа в 2026: 102,57 → 97,97»).
SUMMARY (по бумаге)
- Блок «на дату размещения»: дата размещения, валюта, объём покупки, купон, цена покупки, YTW покупки — из SECURITIES INFO.
- Блок «на дату отчёта»: дюрация; объём, млн р = остаток × FX × номинал / 1000; YTW; чистая цена; P/L реализ = Z / 10⁶; P/L нереализ = AA / 10⁶; стоимость фондирования = Y / 10⁶; P/L (Net) = P/L + фондирование; «изм. за день» — разница с SUMMARY_T-1 (для YTW × 100, в б.п.).
- «Продать до»: UW = дата размещения + 729 − 365 (= +364); SALES = дата размещения + 28.
- Итоги: ЧОД, реализованный, нереализованный, фондирование, MTM, дюрация (средневзвешенная по объёму), «Комиссии МБ» = Σ INPUTS!R («Комиссия ТС») / 10⁶.
Сводный файл (DCM_сводый …_17.02.26)
Собирается вручную из итогов обоих портфелей. Содержит историю ЧОД по дням, трансфертную ставку, ставку ЦБ, спред, разбивку ПИБО на флоатер/фикс и третий портфель «AFS Портфель из ДУ» (скоуп не определён).
Качество данных в файлах
- Числа текстом с запятой в SECURITIES INFO («18,97»).
- НКД T-1/T0 у РЭО-02 — формулы, вбитые руками с номиналом 750,02.
- Формулы в разных строках CALC различаются (Z у РЭО-02).