Семейный бюджет, Google sheets и Python

Привет Хабр!

Сегодня расскажу еще одну историю из своей жизни - в этот раз не про свой чудо-сервер в двух частях, а про что-то более приземленное, но так или иначе связанное с технологиями. По моему субъективному мнению подсчет личных финансов крайне полезное занятие и я очень надеюсь, что данная статья замотивирует вас или поможет вам начать считать деньги и не влезать в кредиты и т.п.

Собственно речь пойдет о том, как мы с супругой ведем семейный бюджет, как к этому пришли и о планах на будущее.

Стоит сразу написать, что я не претендую на роль финансиста, программиста, бухгалтера и т.п., наш способ ведения семейного бюджета не является идеальным, вероятно для вас он будет не применим, но нам так удобно.

Немного предистории.

Первые мысли о том, что хорошо бы знать сколько денег на какие расходы уходит и понимать, где можно сократить расходы, а где не получится, появились в 2013 году. Цель была самая что ни наесть обычная, как у всех, накопить и приобрести собственное жилье.

Первым делом я определил требования к возможностям ПО:

1. Возможность заполнять расходы как с ПК, так и со смартфона.

2. Одновременное редактирование данных текущего месяца без блокировки файла.

3. Синхронизация данных через интернет (я хотел чтобы данные автоматически синхронизировались между 2мя ПК и 2мя смартфонами).

4. Возможность резервного копирования на привычный ПК/в Excel.

Некоторое время я искал именно программу которая будет соответствовать всем этим требованиям, или хотя бы их части, но как в итоге оказалось Google Spreadsheets лучше всего (на мой взгляд) подходил под мои желания.

В итоге 1 января 2014 года было положено начало учета семейных расходов и доходов:

Январь 2014 года

Таблица выглядит конечно жутко - ни нормального форматирования, ни выделения цветом, да и статьи расхода/дохода (каждый отдельный лист - это статья) не особенно информативны.

Сумма в 10 320 руб за месяц на еду на двоих сейчас кажется каким-то сюром, а в начале 2014 мы особо ни в чем себе не отказывали - и сыр, и колбаса на столе тогда бывали импортными

Кстати самым сложным этапом в подготовке к ведению семейного бюджета было уговорить супругу взять на себя часть этого процесса - помой, убери, приготовь, а теперь еще и чеки записывай, ух, долго мы общались на тему необходимости и полезности этого занятия.

Сложно было первые 2 месяца - очень не хотелось тратить время на заполнение таблицы, хоть я и старался какие-то единичные покупки (такие как обед на работе, или поход к врачу) записывать сразу с телефона, но вот перебить чек из магазина, раскидав товары по статьям, уже действительно напрягало.

Опять же, хоть Google Spreadsheets и поддерживает ввод данных оффлайн (например с телефона пока едешь в метро), но работало в 2014 году это так себе, с вылетами приложения и периодическими исчезновением заполненных строк, в итоге офлайн я старался не заполнять.

Но примерно через 2 месяца заполнение табличек вошло в привычку и стало даже нравится, особенно проводить ретроспективу расходов и обсуждение моментов для оптимизации.

К концу года папочка на Google Drive выглядела так:

Файлы в конце 2014 года

Сейчас сижу и думаю, почему в первый год я не создал сводную таблицу по всем месяцам, хм

Но как видно к 2015 году такая мысль меня уже посетила ровно как и то, что название файлов лучше делать по японской модели даты (YYYY.MM):

Полный 2015 год

Из интернетов:Наиболее часто используемый формат даты в Японии - год, месяц, день (день недели) , при этом японские символы, означающие год, месяц и день, вставляются после цифр . Пример: 2008 12 31 () для Среда, 31 декабря 2008 г..

В 2015 году внешний вид таблиц особо не изменился, но вот затраты на еду выросли в 2 раза, примерно как курс рубля к грязной зеленой бумажке:

Никакого больше пармезана, теперь кушаем российский сыр по цене дороже итальянского.

Деньги Деньги дребеденьги.

Переходим к откровенным скриншотам, особо впечатлительным и жадным до чужих денег просьба отползти от экранов:

Интересно, а за 30 лет можно накопить на квартиру в Москве?!

С 2014 года наши расходы выросли примерно в 2 раза (в рублевом выражении, в долларах даже немного упали), причин тому несколько.

У нас подрастал первый ребенок и появлялись новые расходы - начиная с памперсов, заканчивая платной медициной (что хотите пишите, но на наш с супругой взгляд в РФ медицина только платная, по ОМС лучше не ходить если есть такая возможность).

В Июне мы приобрели второй автомобиль Hyundai Getz 2008 года, оказалось с ребенком все же лучше иметь 2 авто в семье (здравствуй жизнь в заМКАДье, метро появится только через пару лет, хотя обещали аж в 1985 году). Не раз получалось, что уехав на работу я оставлял супругу с ребенком без возможности комфортно выехать хоть в магазин, хоть к врачу.

Ну и в Июле мы слетали в Турцию аж за 82 тысячи рублей + 20 тыс рублей брали с собой наличными (естественно переведя в доллары).

Итого за 2015 год мы отложили баснословные 135 рублей 65 копеек, кто-то скажет, что с жиру бесимся и так далее, но опять же по нашему убеждению наши траты ну никак не выходят за рамки разумного для семьи, где оба родителя имеют высшее образование и один из них работает в области ИТ в очень неплохой компании.

Опять же многие мои знакомые, которые не считали расход/доход, были очень удивлены, когда я все-таки убедил их заняться этим хотя бы на 2-3 месяца - в самом деле цифры на бумаге просто шокируют, когда не считаешь траты, всё воспринимается совсем иначе.

Шло время, таблица постепенно видоизменялась и модернизировалась, в отдельную папку были выведены расходы по автомобилям, с разбивкой на статьи:

Многовато файлов, надо автоматизировать!

Файл для учета расходов на автомобиль выглядит примерно так:

Но самое большое изменение было в итоговом файле, помимо раскрашивания цветом денежного потока и % отложенного на конец месяца, появился лист со сводной таблицей всех статей расхода и дохода из всех файлов, заполнять его конечно не просто, но делается это один раз в год и требует примерно 30 минут моего времени.

Тут без цифр, уж извините NDA действует 3 года, но зато покажу вам оставшийся % от общего дохода, как бы мы все же стараемся экономитьРазбивка по статьям.

Собственно лист расходов по статьям. Доходы закрыты привет NDA, единственное, на что хочу обратить ваше внимание, что их источников несколько. Наша логика такова, что нужно не налегать всеми силами на экономию, а стараться увеличивать доходы и диверсифицировать их источники.

В конце статьи будут ссылки на все шаблоны таблиц, а сейчас предлагаю перейти к части про Python и Телеграм.

Telegram-bot на Python

Собственно примерно в сентябре 2020 я решил, что надо бы вернуться к python, а то совсем забросил его, а задачки на leetcode и codewars уже надоели, хотелось сделать что-то более осязаемое. И тут пришла в голову мысль - а не сделать ли Телеграм-бота, который будет по сообщению заполнять расход (через гугл-таблицы на телефоне не всегда удобно да и вообще будет весело).

Опять же небольшой дисклеймер.

Результат моего труда скорее MVP, код УГ, автор как водится и так далее по тексту, но делал я его на коленке за 3-4 неполных дня из которых больше времени ушло на изучение документации api google spreadsheets и telegram.

Началось все с простого изучения интернетов Ок Google: Как написать ТГ-бота

Довольно быстро я понял, что почти везде делают ТГ-бота, который просматривает обновления в чате постоянным обращением к API (polling) - из плюсов можно начинать разрабатывать сразу у себя на ПК, без плясок с бубном, зарегистрировали бота и сразу подписываемся из IDE на его окно чата.

Но как истинный джедай я решил, что надо сразу делать на webhookах, т.к. записывать что-либо через бота я буду довольно редко, а супруга скорее всего вообще не будет им пользоваться, так что постоянно спамить запросы к API нецелесообразно.

Потратив некоторое время на поиск решения я нашел довольно элегантный путь всего в 3 шага. Я думаю многим знаком сервис ngrok - для чего-то серьезного он вряд ли подойдет, но быстренько сделать себе туннель для локальной разработки можно.

Денег за рекламу они не платили, так что вот несколько альтернатив:Localtunnel - https://localtunnel.github.io/www

Teleconsole - https://www.teleconsole.com/Pagekite - https://pagekite.net/

После запуска вы получаете адрес вида: http://1c0306c9372f.ngrok.io и редирект запросов с него на ваш localhost с указанным портом при запуске:

Консоль с запущенным ngrok

В итоге при обращении на указанный адрес наши запросы будут попадать к нашему локальному боту (запущенному хоть из IDE PyCharm/VSCode), который в свое время будет общаться с ТГ API по токену.

Данный URL необходимо зарегистрировать в Telegram API примерно следующим образом:https://api.telegram.org/bot{my_bot_token}/setWebhook?url={url_to_send_updates_to}

А вообще RTFM.https://core.telegram.org/bots/api#setwebhook

Для того чтобы бот сидел и слушал входящие запросы я решил использовать Flask.

Для работы с Telegram API я использую готовую библиотеку telebot (зачем изобретать велосипед если он уже есть и очень даже ничего).

Аналогично и с Google SpreadSheets - есть готовая библиотека gspread с очень широким функционалом.

Для обеспечения минимальной безопасности используется библиотека SSLify и импорт токенов из соседнего файла .env

Выглядит это так:

Начало нашего бота

Собственно в самом начале python-файла мы импортируем все необходимые библиотеки.

Далее функция load_dotenv() производит загрузку переменных из операционной системы (или лежащего рядом файлика .env) - сделано это для обеспечения минимальной безопасности.

Так как для работы с Google SpreadSheets нам необходим Service account с json-токеном, а для телеграм бота API-ключ, которые по сути обеспечивают полный доступ к нашим файлам (которые мы предварительно расшарили на этот сервисный аккаунт) и к нашему ТГ-боту, то следует скрыть их от посторонних глаз в переменных ОС или файле .env.

Сначала я хотел добавить в статью описание как получить API token для Telegram и Google API, но статья и так получилась большая, поэтому кусок с инструкциями я решил удалить.

Основное меню бота.

При вводе команды /Start или /Help бот выведет следующее меню:

Главное меню. Телеграм

Как видим здесь перечислены все команды, которые бот поддерживает на данный момент, генерируется это сообщение следующим кодом:

Главное меню. Код.

На 167 строке у нас расположился декоратор от библиотеки telebot - здесь происходит считывание команды Start или Help из входящего сообщения к боту.

Если команда распознана, то происходит запуск функции handle_start_help на 168 строке (да, я не особо заморачивался с названиями).

172 строка проверяет ID-пользователя (в данный момент я разрешил пользоваться им только себе). В идеале когда-нибудь конечно же стоит прикрутить к боту sql и проверку соответствия учетки ТГ в sql к нужному google service account и маске наименования файлов, правам чтения и записи и т.д. и т.п.

174 строка собственно отправляет в чат сообщение с текстом (для отправки мы снова обращаемся к библиотеке telebot).

На 184 строке задано условие, которое отправит вам в чат сообщение о запрете использования бота, если ваш ID отличается от моего.

Чаще всего я пользуюсь функцией добавления покупки в текущий месяц, для этого использую функцию add_current_month_expense:

Функция добавление покупки. Шаг первый.

Помимо уже известного нам декоратора, ожидающего теперь команду /AddExpenseToCurrentMonth или ее аббревиатуру /AECM и проверки на ID-пользователя, от которого эта команда была получена, теперь еще задействуется функционал Google Spreadsheets и библиотека gspread.

234 строка - открывает документ текущего месяца. Так как все файлы у меня сейчас называются однотипно по маске YYYY.MM Family budget, то легко сформировать название файла с помощью функции datem.today().strftime("%Y.%m") и добавить текст " Family budget".

235 строка - создает список из листов документа.

236 строка - убирает 2 последних элемент (ими являются лист с общим балансом за месяц и лист с другими доходами, такими как продажа вещей на авито и т.п.)

Строки 238-239 - это цикл, который создает будущее сообщение бота со списком категорий (листов) и присваивает им номера (см скрин ниже).

240 отправка сообщения от бота пользователю с разбивкой получившихся ранее строк через \n - с новой строки.

241 строка - это вывод сообщения пользователю о необходимости выбора категории затраты и ожидание ответа от пользователя.

242 запуск следующей функции с передачей ей полученного сообщения от пользователя и списка листов.

Шаг второй, выбор категории.

По какой-то причине я не сделал сразу ввод и парсинг категории и самой покупки в первом же сообщении, но уже как есть.

На 254 строке мы присваиваем переменной category_num цифру, полученную из текста сообщения пользователя, переводя ее в тип interger и отнимаем единицу т.к. список наш все же начинается с 0.

Дальше начинается блок try except - в случае некорректного ввода номера категории будет выведено сообщение об ошибке:'ERROR!\nCategory ' + str(message.text) + ' not found!\nTry once more!'

И повторный запрос ввода категории. Бот будет вас спрашивать пока не дождется правильного ввода (тут тоже надо подумать на предмет либо таймаута, либо количества попыток для возврата к изначальному меню, хотя можно просто ввести команду /start и начать ввод заново).

Вот так это выглядит в чате Телеграм

Если же категория была выбрана корректно то мы попадаем в условие if.

257 строка выводит нам сообщение с номером выбранной нами категории и именем листа, полученного из списка по индексу.

После чего на 259 строке бот ответит нам с просьбой ввести Название нашей покупки и стоимость в формате название:цена.

Знак : будет использован как разделитель для дальнейшего парсинга.

Введя название покупки, ее стоимость и отправив сообщение боту происходит запуск следующей функции add_current_month_expense_input_string, передав в нее сообщение и номер категории.

Шаг третий, заносим данные в таблицы.

При должном старании и потратив некоторое время подозреваю, что эту функцию можно сократить раза в два, но текущий вариант такой, как на скриншоте.

281 строка формирует список из 2х элементов определив их с помощью знака : .

Далее у нас следует проверка на длину получившегося списка - если в нем 2 элемента, то запускается процедура заполнения полей, если элементов меньше или больше 2х, то будет выведено сообщение об ошибке и предложено ввести покупку в корректном формате, по аналогии с предыдущей функцией.

В остальном начинается все также - открываем документ, берем текущую дату, переходим на нужный лист, но далее на 287 строке мы обращаемся к функции next_available_row, которая вернет нам следующую пустую строку на листе, где мы и будем заполнять наше значение.

Пробежимся быстро по ней:

31 строка - присваиваем переменной i общее количество колонок в документе.

32 строка - создаем пустой список.

Пока i больше 0 мы перечитываем все значения полученные из каждой колонки, отфильтровав None - что означает, что ячейка пустая, добавляем единицу и присваиваем получившееся число переменной result.

После чего на 35 строке наполняем список result_list получившимися значениями result.

36 строка - счетчик наших колонок, отнимаем 1 т.к. мы обработали 1 колонку, собственно это происходит до момента, пока не будут обработаны все имеющиеся колонки на листе.

Строка 37 - сортируем получившийся список от наименьшего к наибольшему числу.

Строка 38 - присваиваем значение последнего элемента списка (наибольшую цифру) переменной empty_row

На 39 строке мы присваиваем переменной last_row - значение предпоследней строки на листе.

А дальше проверяем, если last_row не пустой, то добавляем еще 5 строк или сразу присваиваем значение переменной empty_row номер последней строки с данными + 1.

После чего возвращаем значение переменной empty_row в данном случае в функцию add_current_month_expense_input_string. И все это ради того, чтобы узнать где же у нас там пустая строка на листе.

Строки 288-308 необходимы для предварительного форматирования ячеек в нужный формат, стоит кстати вынести их в отдельную функцию, надо записать себе.

309 строка - вносим значение первого элемента из списка input_list в ячейку А+номер пустой строки.

310 строка - проставляем сегодняшнюю дату в ячейку B+номер пустой строки.

311 строка - вносим значение второго элемента из списка input_list в ячейку C+номер пустой строки.

312 строка нужна для считывания строки, которая получилась после ввода данных и отправки пользователю сообщения, в качестве подтверждения результата ввода с содержимым строки.

Как то так.

На этом заканчивается путь ввода покупки для текущего месяца.

В рамках данной статьи мы не будем рассматривать все функции реализованные в коде на данный момент, я лишь кратко опишу до чего у меня дошли руки.

"/start or /help" - Отображение списка поддерживаемых команд.

"/CurrentMonthBalance or /CMB" - Показать баланс за текущий месяц, общий доход, расход и их разницу.

"/DefinedMonthBalance or /DMB" - Показать баланс за указанный месяц/год, общий доход, расход и их разницу.

"/CurrentMonthExpenseByCategory" - Показать детализацию расходов с разбивкой по категориям за текущий месяц.

"/ExactMonthExpenseByCategory" - Показать детализацию расходов с разбивкой по категориям за указанный месяц/год.

"/AddExpenseToCurrentMonth or /AECM" - Добавить покупку в текущий месяц.

"/AddExpenseToDefinedMonth or /AEDM" - Добавить покупку к указанному месяцу/году.

"/FormatDefinedFile or /FDF" - отформатировать весь указанный документ по заданному шаблону колонок содержащих цены, даты и т.п. Используется для исправления форматирования после копипастов, чтобы не делать это руками.

Что еще есть в задумках:

• Рефакторинг кода для уменьшения повторяющихся действий в функциях.

• Возможно руки дойдут до переработки в ООП.

• Функция создания файлов на весь год вперед с перелинковкой на итоговый документ (сейчас ссылки в сводной таблице я делаю руками, массовой заменой конечно, но все же).

• Прикрутить БД и проводить авторизацию IDшника там, может быть добавить пароль/кодовую фразу для обращения к боту раз в сутки или что-то подобное.

• Убрать хардкоды форматирования и т.п., заменив на поиск ключевых заголовков.

На этом я хотел бы закончить и без того длинную статью, очень надеюсь, что вы занимаетесь собственной финансовой грамотностью и избегаете неоправданных кредитов.

Как и обещал ссылки:

https://github.com/iliyakarin/TelegramExpensesBot

https://drive.google.com/drive/folders/1ZL3n6Oyyy5iJSoh88ObUT85HSWSrPYCD?usp=sharing