Excel. Парсинг банковских SMS

Кто регулярно ведет учет личных финансов, знает насколько бывает долго и утомительно вносить расходы в специализированную программу или в таблицу Excel. Доходы, конечно, тоже надо вносить, но они, увы, обычно реже, да и вносить их значительно приятнее.
И если, при оплате наличными тут сложно что-то автоматизировать, и приходится рассчитывать только на свою память или чеки, то при платежах картой, когда на телефон приходят SMS от банка, волей не волей задумаешься: «А нельзя ли что-то сделать, чтобы перестать быть роботом-колотилкой данных из сообщений в программу учета финансов?»
Конечно, я не открываю Америки — для Андроида существуют программы учета, которые умеют парсить SMS от банка и использовать их данные для внесения соответствующих проводок. Но вот пользователям iOS с этим повезло сильно меньше — дело в том, что политика iOS в отличие от Android, не позволяет сторонним программам читать сообщения в телефоне. Можно сколько угодно гадать, почему так, и ругать Apple, но факт остается фактом, и что-то с этим надо делать. Не буду вдаваться в подробности выкорчёвывания SMSок с айфона, дам всего лишь пару направлений для поиска, которые точно приведут вас к результату — это использование определенных файлов из бэкапа телефона, который делает iTunes, или использование программы iMazing (увы платной). Джейлбрейк и другие ломания телефона не рассматриваю, так как это уже совсем на любителя, коим я не являюсь. Да простят меня дорогие читатели, статья больше о парсинге, чем о копировании SMS с телефона, поэтому более подробно останавливаться не буду. Также у вас может возникнуть закономерный вопрос, почему я не говорю здесь о банковских выписках и отчетах, которые можно получить online у большинства крупных банков. Во-первых, логика описанная ниже будет одинакова, что для сообщений на телефоне, что для электронных выписок. Во-вторых, SMS в качестве источника данных мне нравится больше, т.к. они обычно приходят быстрее, чем соответствующая информация отображается в выписке по счету (минусы, конечно, тоже есть, но мне они не критичны).
И так, допустим, вы победили первый этап квеста и смогли заполучить список своих SMSок, примерно в таком виде:

Пример, самого файла вы можете скачать по  этой ссылке, в нем же, вы сможете увидеть конечный результат. Далее описание идет именно по этому файлу.
С одной стороны, в этом списке вроде бы все и просто, но и сложно одновременно — составляющие текста сообщения имеют определенный порядок: дата, название банка, номер карты, название магазина, сумма, валюта — то есть вроде бы есть все, для того чтобы внести эту информацию в финансовую программу. Но при всем при этом у данных нет ни четких разделителей, ни фиксированной длинны, а это значит, что нам лишь по содержимому, которое тоже постоянно меняется (разные магазины, разные суммы), надо суметь разделить текст на составляющие и записать в соответствующие столбцы Excel.
Для начала не плохо бы провести предварительный анализ полученных данных. На представленной выше картинке со списком сообщений, я отметил важные для дальнейшего рассмотрения составляющие. Первое что интересно и важно для записи проводки — это направление движения денежных средств: поступление (SMS содержит слово «Popolnenie» или «Vypiska») или списание (слово «Pokupka»). Заведем соответствующую таблицу «DebCred» на листе «Направление»:
Второе — это уникальные слова, например, название магазина, кафе и т.п., по которым мы можем опознать вид расхода/дохода (см. таблицу «Category» лист «Категории»):
И третье, самое важное, собственно ключевые слова, по которым мы можем выбрать сумму по проводке. У нас везде есть код валюты RUB (мы ее пропишем константой в формуле, поэтому обойдемся без дополнительных таблиц) и непосредственно слова, после которых идет необходимая сумма: «Nachisleno protsentov:», «Cashback:», «Summa» (таблица «KeyW» на листе «„).
После проведения такого анализа, нам остается только воспользоваться формулами, описанными в моих предыдущих статьях. Применив их, получим таблицу, которая автоматически по тексту SMS будет определять дату проводки, ее направление (увеличение или уменьшение денежных средств), отнесение к той или иной категории доходов или расходов, а также сумму этой проводки:
Такие подготовленные данные уже без труда могут быть использованы или в собственной Excel-таблице учета личных финансов, или в сторонней программе, поддерживающей импорт данных.
Я не буду подробно останавливаться на описании формул, позволяющих получить такой результат, т.к. их видно в файле примера, и приведу лишь ссылки на мои предыдущие статьи для каждого столбца, где такое описание приведено:

  • Дата — слишком тривиально, используется функция ПСТР (воспользуйтесь, пожалуйста, справкой по этой функции или Яндексом, если не пользовались)
    =ПСТР([@SMS];10;10)
  • Направление и Категория1 — используют одну и ту же формулу, описанную в статье ««ВПР» по частичному совпадению», с разными таблицами для ключевых слов
    {=ЕСЛИОШИБКА(ИНДЕКС(DebCred;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(DebCred[Word];[@SMS]));0);2);"")}
    {=ЕСЛИОШИБКА(ИНДЕКС(Category;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Category[Word];[@SMS]));0);2);"")}
  • Категория2 — тот же результат, что и в столбце Категория1, но используют другую более короткую формулу, информация по которой есть в комментарии к той же статье
    =ПРОСМОТР(2;1/ПОИСК(Category[Word];[@SMS]);Category[Category])
  • Сумма — соответствующая формула описана в статье «Excel. Сложение чисел из текстовой строки, следующих после ключевых слов»:
    {=СУММПРОИЗВ(ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР([@SMS];(ЕСЛИОШИБКА(ПОИСК(KeyW;[@SMS]);0)+ДЛСТР(KeyW)+1)*(--ЕЧИСЛО(ПОИСК(KeyW;[@SMS])));ЕСЛИОШИБКА(ПОИСК("RUB";[@SMS];ЕСЛИОШИБКА(ПОИСК(KeyW;[@SMS]);0))-1;0)-(ЕСЛИОШИБКА(ПОИСК(KeyW;[@SMS]);0)+ДЛСТР(KeyW)+1)*(--ЕЧИСЛО(ПОИСК(KeyW;[@SMS]))));".";",";1));0))}

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *