Excel. От парсинга SMS до загрузки в GnuCash

Эта статья является логическим завершением цикла, посвященного автоматизации загрузки данных из SMS в программу учета личных финансов GnuCash.
Судя по нескончаемому (на самом деле — нет) потоку комментариев к предыдущей статье, тема, действительно, интересна не только мне, поэтому было бы безответственно не довести описание до конца.
Напомню, в статье «Excel. Парсинг банковских SMS» мы научились переводить текст SMSок к формализованному виду, где каждый блок полезной информации из сообщения помещается в свой отдельный столбец, что открывает возможность для аналитики, которой не дает обычный неподготовленный текст. Затем в статье «Импорт проводок в GnuCash» мы научились массово загружать данные в GnuCash, но для этого данные должны иметь свой строго определенный вид — в таблице для загрузки для каждой проводки должно быть две отдельных строки — одна под другой для дебета и кредита. Как же при помощи Excel перевести одну строку с данными, полученными из SMS, в две строки, которые можно будет скормить нашей программе?
Сегодня мы с вами с этим и разберемся.
Начнем с конца. По  этой ссылке вы можете скачать итоговое решение, а ниже рассмотрим, как все это работает.

Логика работы формул в столбцах:

  • A «SMS»
  • B «ДатаSMS»
  • C «Направление»
  • F «Категория»
  • G «Сумма»

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

    • D «Счет1»

      В столбце задаем как постоянное значение название счета, с которого мы осуществляем покупки и на который приходят средства, например, от з.п. Мне его удобнее всего обозначать как карта какого-то определенного банка. Этот столбец нам нужен, чтобы тем счетам, которые мы определили в результате парсинга SMS, было с чем корреспондировать (т.е. мы должны указать не только то, на что мы потратили, но и откуда мы на это взяли средства, или не только что у нас появились деньги на карте, но и откуда они у нас — проценты с депозитов, з.п. или еще что-то)

    • E «Счет2»

      На основе типов расходов/доходов мы получаем обычным ВПРом названия соответствующих им счетов:

      =ЕСЛИ(ЕНД(ВПР([@Категория];Счета;2;0));"";ВПР([@Категория];Счета;2;0))
      
    • Переходим к новому. Как мы с вами уже обсудили, на каждую имеющуюся строку необходимо получить две строки — берем данные исходной строки, группируем их на две части и одну из этих частей помещаем под другую. Как это сделать?
      В Excel есть функция АДРЕС, которая (что не удивительно) возвращает адрес ячейки, номер строки и столбца из которой мы зададим в этой функции. Однако адрес ячейки — это не тоже самое, что и ее значение, поэтому нам понадобиться еще одна функция ДВССЫЛ, аргументом которой как раз будет адрес, а возвращать она нам будет необходимое значение из заданной ячейки. Соответственно, нам необходимо в первых двух новых строках подряд ссылаться на данные первой строки, в следующих третьей и четвертой строке сослаться на данные второй исходной строки и так далее. В этом нам поможет пара вспомогательных столбцов:   

      • H «Строка» с формулой:

        =СТРОКА([@Строка])

        Возвращает порядковый номер текущей строки

      • I «Строка источника»:

        =ОКРУГЛВНИЗ([@Строка]/2+1;0)

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

    • J «Столбец источника для счета»
      Помня о том, что в табличке для импорта в GnuCash корреспондирующие счета одной проводки находятся в одном столбце один под другим в двух разных строках, а в исходном состоянии наоборот — в одной строке, но в разных столбцах, нам необходима формула, которая позволит для итоговых четных строк брать информацию из одного столбца, а для нечетных из другого:

      =ЕСЛИ(ЕЧЁТН([@Строка]);4;5)

      Здесь 4 и 5 — порядковые номера столбцов с корреспондирующими счетами.

    • K «Дата»

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

      =ДВССЫЛ(АДРЕС([@[Строка источника]];2;1))

      Как видно, здесь мы, зная номер строки источника, который у нас для новых строк дублируется, и задав константой номер столбца, в котором лежит дата получения SMS, получаем дату проводки как для строки дебета, так и для строки кредита

    • L «Номер»

      =ЕСЛИ(ЕЧЁТН([@Строка]);[@[Строка источника]];"")

      Задаем номер проводки, который должен стоять в первой из двух строк этой проводки. Этот номер необходим для правильного порядка проводок созданных в одну дату

    • M «Описание»

      Как и номер, описание указывается только для первой строки проводки:

      =ЕСЛИ(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];6;1));"")

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

    • N «Название счёта»

      Для названия счета будем использовать уже рассчитанные номера строки и столбца источника:

      =ДВССЫЛ(АДРЕС([@[Строка источника]];[@[Столбец источника для счета]];1))
    • O «Дебет»

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

      =ЕСЛИ(И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));ЕСЛИ(И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));""))

      В Excel для большей читаемости я также написал эту формулу так, чтобы было легче понять, какая часть формулы за что отвечает:

      Рассмотрим эту формулу по блокам:

      Первое условие:

      И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание")

      Если первое условие верно, возвращаем сумму проводки:

      ДВССЫЛ(АДРЕС([@[Строка источника]];7;1))

      Второе условие:

      И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление")

      Если второе условие верно, возвращаем сумму проводки:

      ДВССЫЛ(АДРЕС([@[Строка источника]];7;1))

      Если оба условия не выполняются — пустое значение.

      И первое, и второе условие возвращают нам сумму проводки, которая и в том, и другом случае определяется одинаково.
      Первое условие проверяет:  

      •  является ли номер текущей строки нечетным (в нашем случае это каждая вторая строка проводок, где чаще всего находится счет расхода),

      •  а рассматриваемая исходная проводка — проводкой списания,

      если это так, то именно в такой строке в столбце дебета указывается сумма.
      Второе условие проверяет:

      •  является ли текущая строка, наоборот, четной (здесь у нас всегда стоит счет банковской карты),

      •  а рассматриваемая исходная проводка — проводкой поступления,

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

    • P «Кредит»

      =ЕСЛИ(И(ЕЧЁТН([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Списание");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));ЕСЛИ(И(ЕНЕЧЁТ([@Строка]);ДВССЫЛ(АДРЕС([@[Строка источника]];3;1))="Поступление");ДВССЫЛ(АДРЕС([@[Строка источника]];7;1));""))

      Здесь формула практически полностью повторяет формулу для дебета, кроме того, что здесь нам надо поменять местами функции ЕЧЁТН и ЕНЕЧЁТ, а цепочка рассуждений выстраивается полностью по аналогии с описанием к предыдущему столбцу, таким образом, мы получили все возможные комбинации условий, которые дают возможность понять, в какой строке и в каком столбце Дебета или Кредита указывать сумму проводки.
    • Подробно останавливаться на том, что за аброкадабра в столбце Q, не буду, т.к. уже говорил об этом в статье об импорте проводок, скажу лишь, что достаточно скопировать значения этого столбца в текстовый файл, и приступить, собственно, к импорту проводок.

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

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

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