Всем привет!
Примерно год назад мне захотелось проанализировать доходы и расходы со всех своих банковских карт, количество которых начало разрастаться. После ресерча существующих приложений я поняла, что они либо платные, либо нужно ручками вбивать всю информацию. Плюс вопрос сохранения конфиденциальности данных. В этой статье я расскажу про свой мини-проект, как он мне помогает следить за личными финансами и как вы можете покрутить его сами.
![](https://habrastorage.org/getpro/habr/upload_files/3d1/e37/f93/3d1e37f932460deb4986d4b804af8735.png)
О чем
В качестве входных данных берутся банковские выписки из мобильных приложений и веб-версий. На текущий момент одна из болей - это сходить в несколько мест и выгрузить вручную. Если у вас есть идеи покруче или примеры подобных проектов - велком в комменты, буду очень благодарна. Проект лежит тут.
Парсинг выгрузок
Так как у каждого банка свой формат, то я написала отдельные обработки. Посмотрим, чем они отличаются:
1. Сбер
Выписка может быть либо по дебетовой карте, либо по счету.
![Операции по карте Операции по карте](https://habrastorage.org/getpro/habr/upload_files/f70/450/8fe/f704508fe3678e528c6fe3120b416a6d.png)
![Операции по счету Операции по счету](https://habrastorage.org/getpro/habr/upload_files/b56/28c/b22/b5628cb223796e73630c43f8617ab76a.png)
Посмотрев внимательно, можно увидеть, что поля "Категория" и "Название операции" расположены полярно противоположно. Также если в картах поступления идут с плюсом, а расходы без знака, то в счетах все наоборот: поступления без знака, а расходы с минусом.
Я попробовала несколько разных вариантов, но больше всех мне зашла библиотека PyMuPDF. Она точно определяла границы полей, и с ней было легко вытащить то, что нужно.
После предобработок мы получаем такой объект, который будет записываться в базу (об этом далее).
transaction = {
'bank': 'Sber',
'trans_datetime': datetime.strptime(' '.join((trans_date, trans_time)),
'%d.%m.%Y %H:%M'),
'transfer_datetime': datetime.strptime(transfer_date, '%d.%m.%Y'),
'auth_code': auth_code,
'category': category if is_debit_card else text,
'debit': debit,
'credit': credit,
'text': text if is_debit_card else category
}
2. Тинькофф
Сначала я попробовала использовать выписки из приложения, но там оказалось сильно мало данных по сравнению с веб-версией (актуально на начало 2023 года):
![Операции из приложения (на начало 2023 года) Операции из приложения (на начало 2023 года)](https://habrastorage.org/getpro/habr/upload_files/5c0/859/d4c/5c0859d4c245c54f394441990e3ec8e4.png)
![Операции из веба Операции из веба](https://habrastorage.org/getpro/habr/upload_files/9f9/72f/c6a/9f972fc6a6263a986e163d14c3397331.png)
Особенно печалило отсутствие категории, которой, кстати, нет и в текущей версии:
![Операции из приложения (на конец 2023 года) Операции из приложения (на конец 2023 года)](https://habrastorage.org/getpro/habr/upload_files/e6c/d88/78f/e6cd8878f29340c1c2640fd891f4b0de.png)
В этом случае я из веба выгружаю экселечку. Отчет читается в pandas датафрейм df = pd.read_excel(filename, sheet_name='Отчет по операциям', header=0)
, и все транзакции приводятся к виду:
transaction = {
'bank': 'Tinkoff',
'trans_datetime': datetime.strptime(trans_datetime, '%d.%m.%Y %H:%M:%S'),
'transfer_datetime': None if pd.isna(transfer_datetime)
else datetime.strptime(transfer_datetime, '%d.%m.%Y'),
'pan': pan,
'status': status,
'debit': trans_sum if trans_sum > 0 else 0,
'credit': -trans_sum if trans_sum < 0 else 0,
'trans_currency': trans_currency,
'pay_sum': pay_sum,
'pay_currency': pay_currency,
'cashback': cashback,
'category': category,
'mcc': mcc,
'text': text,
'bonus': float(bonus),
'rounding': float(rounding),
'sum_with_rounding': float(sum_with_rounding)
}
Идею с разделением на дебет и кредит я решила распространить на все банки.
3. Совкомбанк
Тут особая выгрузка в html формате.
![Выгрузка из СКБ Выгрузка из СКБ](https://habrastorage.org/getpro/habr/upload_files/0f1/651/e9a/0f1651e9ae478100155708d112b1b7b2.png)
Для работы с тегами использовала либу BeautifulSoup, в итоге получаем это:
transaction = {
'bank': 'Sovcom',
'trans_datetime': datetime.strptime(tds[0].find('p').get_text(), '%d.%m.%y'),
'account': tds[1].find('p').get_text(),
'income_balance': float(tds[2].find('p').get_text().replace(',', '')),
'debit': float(tds[4].find('p').get_text().replace(',', '')),
'credit': float(tds[3].find('p').get_text().replace(',', '')),
'text': tds[5].find('p').get_text()
}
4. ВТБ
Аналогично Сберу, использовала либу PyMuPDF:
transaction = {
'bank': 'VTB',
'trans_datetime': datetime.strptime(
' '.join((trans_date, trans_time)),
'%d.%m.%Y %H:%M:%S') if trans_date is not None else None,
'transfer_datetime': datetime.strptime(transfer_date, '%d.%m.%Y'),
'card_sum': float(card_sum.replace(' RUB', '')),
'debit': float(debit),
'credit': float(credit),
'text': text[1:].replace(' Спасибо, что Вы с нами! Всегда Ваш, Банк ВТБ (ПАО)', '').strip()
}
Загрузка данных
В проекте используется Docker, который поднимает PostgreSQL, pgAdmin и Metabase. Для работы с базой использовала SQLAlchemy ORM. Есть пара фишек, которые я открыла для себя:
Создание схемы и табличек в этой схеме
Передаем через метадату, предварительно проверяя, что такой схемы еще не существует:
db_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_user, db_pass, db_host, db_port, db_name)
engine = create_engine(db_string)
Session = sessionmaker(bind=engine)
if not engine.dialect.has_schema(engine, db_schema):
engine.execute(CreateSchema(db_schema))
metadata_obj = MetaData(schema=db_schema)
Base = declarative_base(metadata=metadata_obj)
UniqueConstraint - ограничения на уникальность полей
Во время анализа данных я обнаружила, что есть транзакции, которые совершились, но еще не обработались банком. У таких транзакций в поле transfer_datetime
отсутствует значение. При следующей выгрузке они уже будут идти с заполненной датой и временем обработки, что приведет к дублированию данных. Поэтому необходимо навесить ограничения на группу полей, которые в любом случае не изменятся:
__table_args__ = (
UniqueConstraint(
'bank',
'trans_datetime',
'category',
'debit',
'credit'
)
)
Соответственно, если мы натыкаемся на дубликат, скипаем его и идем дальше:
for _, (source_transactions, TransactionClass) in transactions.items():
for transaction in source_transactions:
logger.debug(transaction)
session.execute(insert(TransactionClass).values(transaction).on_conflict_do_nothing())
session.commit()
Про дашборды
Зачем нужно было разбираться со всеми этими данными? Чтобы строить дашборды, смотреть на категории расходов и делать свои выводы.
В качестве BI-инструмента я взяла Metabase, потому что ранее с ним работала и он удобен с точки зрения написания SQL-запросов, а не drag-n-drop UI-элементов.
![Пример дашборда по типам расходов Пример дашборда по типам расходов](https://habrastorage.org/getpro/habr/upload_files/213/3cd/6f9/2133cd6f953ebd95264497bc5ef8d645.png)
![Пример дашборда по месячным расходам Пример дашборда по месячным расходам](https://habrastorage.org/getpro/habr/upload_files/0a5/a8c/9cf/0a5a8c9cf8eafd6500870231cc646eba.png)
У меня есть очень частый кейс, когда я перевожу между своими картами. Много раз. Например:
Действие |
Первое изменение |
Второе изменение |
1 -> 2 - перевела 1000 рублей |
1: -1000 |
2: +1000 |
2 -> 3 - перевела 1000 рублей |
2: -1000 |
3: +1000 |
3 -> 4 - перевела 1000 рублей |
3: -1000 |
4: +1000 |
4 - потратила 1000 рублей |
4: -1000 |
Если брать общие обороты, то выходит, что я потратила 4000 и получила 3000. Поэтому имеет смысл исключать такие транзакции. Но как?
Так как потратить я могу любую сумму, то невозможно по ней определить источник: либо это часть перевода, либо деньги уже были. Поэтому я решила атрибуцировать транзакцию к первой операции - мы знаем, что сумма в переводах всегда одинаковая.
Как найти начало цепочки?
Если в текущем банке нет поступления на сумму перевода.
Как понять, что перевод не просто лежит на другой карте, а был использован?
Если количество операций в последующих банках после перевода четно: поступление-трата-поступление-трата...
Итак, считаем все реальные расходы:
t1.credit > 0
and t1.text != 'Перевод между счетами'
and t1.text not like '%VKLAD%'
--либо не перевод, либо перевод с доп. условиями
and (isTransfer = 0
or isTransfer = 1
--нет поступления в текущем банке на дату на ту же сумму, т.е. начало цепочки транзакций
and not exists (
select 1 from transactions t2
where t2.bank = t1.bank
and cast(t2.trans_datetime as date) = cast(t1.trans_datetime as date)
and t2.debit = t1.credit
and t2.isTransfer = 1
and t2.text not like '%VKLAD%'
)
--количество операций в других банках на дату на ту же сумму четно: - +- +- +-
and exists (
select 1 from transactions t2
where t2.bank != t1.bank
and cast(t2.trans_datetime as date) = cast(t1.trans_datetime as date)
and (t2.debit = t1.credit or t2.credit = t1.credit)
and t2.isTransfer = 1
having mod(case when count(*) != 0 then count(*) else 0 end, 2) = 0
)
)
Предыдущая таблица с другой стороны:
Номер карты |
Входящая операция |
Исходящая операция |
1 |
-1000 |
|
2 |
+1000 |
-1000 |
3 |
+1000 |
-1000 |
4 |
+1000 |
-1000 |
По факту: по картам 1, 2, 3 я ничего не потратила, с 4 - 1000 рублей.
На дэше: карта 1 - трата 1к, 2, 3, 4 - 0 (есть поступление на ту же сумму и четное количество операций).
Еще и у Metabase есть свои недостатки. Один из них - невозможность использовать одну переменную на нескольких таблицах. То есть, если я хочу поставить фильтр по месяцу (=переменная) на запрос, где я соединяю несколько таблиц, то это невозможно. Приходится изобретать костыли и джойнить каждый подобный запрос с таким сниппетом:
join sber
on extract(month from t1.trans_datetime) = extract(month from sber.trans_datetime)
and extract(year from t1.trans_datetime) = extract(year from sber.trans_datetime)
На этом все, спасибо за прочтение!
Комментарии (16)
FILINI
02.01.2024 15:39+1За материал спасибо, но не это ли случай из типового мема "миллениалы придумали..."? В данном случае придумали ДзенМани / ZenMoney с их 100+ интеграциями с банками (напрямую, из PDF, выгрузки, распознавая смс), всякими аналогами вебмани, бирж с криптой и просто налом.
retry
02.01.2024 15:39+1Есть причина не использовать дзенмани и тп: данные о ваших транзакциях не уходят в неизвестном направлении(ещё одном...)
Kenya
02.01.2024 15:39+1Не знаком с Python, но разве не было возможности как-то унифицировать список транзакций для всех банков через общие интерфейсы? Сейчас получается, что для каждого нового банка мне нужно кастомить свое решение, которое может не совпасть по типам/полям с ожидаемым приложением
dcooder
02.01.2024 15:39На текущий момент одна из болей - это сходить в несколько мест и выгрузить вручную. Если у вас есть идеи покруче или примеры подобных проектов - велком в комменты, буду очень благодарна.
Есть вариант перехватывать e-mail уведомления о движении средств по счетам. ZenMoney так делают как я понял. Минус в том, что не у всех банков есть уведомления по e-mail. Одноразовые коды банки на e-mail не присылают, поэтому способ более-менее безопасный.
По идее можно еще кассовые чеки из e-mail перехватывать, но не все шлют чеки по e-mail и там по чеку непонятно с какого счета прошла транзакция. Поэтому для формирования истории и балланса такой способ не подойдет, исключительно для детализации.
alexkuzko
02.01.2024 15:39Могу порекомендовать уже довольно древнюю программу: AbilityCash. Под Windows, но прекрасно работает через wine.
Там есть возможность плагины и интеграции делать, но я по старинке все руками делаю...
Тем не менее это софт, который проверен уже реально десятилетиями ;)
Sheti
Вот бы был единый машиночитаемый формат выгрузок по счетам.
aborouhin
Формат-то есть, текстовый от 1С, какой ни есть, но все банки его поддерживают. Но почему-то опции получить выписку в этом формате для физических лиц обычно нет.
А ещё можно про Open Banking вспомнить...
allex
https://en.wikipedia.org/wiki/Quicken_Interchange_Format
Sheti
А в хоть одном из личных кабинетов для частных лиц его можно получить?
P.S. В формате ощущается дух до XML эпохи
allex
Я к тому, что формат как бы есть. Но древний он как Трогонтериевый слон :) В эпоху MS Money поддержка от банков, как пишут в статье, была, но уже и MS давным давно перестала продавать Money.
Но я вот к MS Money прирос и импорт брокерский отчётов пришлось делать именно через QIF.
Sheti
Может на ROI инициативу выдвинуть, чтобы в ЛК банков была выгрузка отчета в формате QIF в обязательном порядке. Я бы подписался.
aborouhin
В России QIF, насколько мне известно, никогда и не внедрялся никем. Стандарт де-факто сейчас - упомянутый мной выше формат 1С (спецификация). Он кривенький, но его поддерживают вообще все (но не для физиков, увы). Перспективный стандарт, который и надо бы продвигать, - Open Banking (спецификация). Там и REST API, и модель данных нормальная, - но как в 2020 году стандарт опубликован, так внедрение особо не продвинулось (может, какие-то подковёрные процессы и идут, не в курсе).
Sheti
Как я понял с их сайта, там про API всё. Нет именно файла со списком расходов по счетам за период?
aborouhin
Так а чем отличается JSON со списком транзакций по счёту за период, прилетевший как ответ на REST-запрос, от этого же JSON, сохранённого в файл?
dcooder
И уведомления по e-mail во всех банках. ZenMoney например по е-мэйл уведомлениям синхронизируется.Проблема разных не-машиночитаемых форматов решается парсингом, а вот проблема безопасной синхронизации остается открытой, пока во всех банках нет уведомления по e-mail о движении по счетам.