Как внедрить в excel формирование QR кода на квитанциях

Частой задачей для УК, ТСЖ, ТСН, ООО или ИП оказывающих коммунальные услуги, услуги связи, домофона или парковки является формирование квитанций для сбора платежей. Современные технологии позволяют принимать платежи по QR коду с автоматическим заполнением реквизитов. Но зачастую начисления и квитанции готовятся в самом excel. Квитанции уже привычны плательщикам и бухгалтеру и стоит задача внедрить в уже существующие квитанции и начисления QR код для оплаты через Сбербанк-Онлайн, Тинькофф, ВТБ, Альфа и другие банка.

На примере диалога между заказчиком и разработчиком ниже рассмотрим один из вариантов решения этой проблемы. А также приведём пример работы excel с макросом, который формирует QR код.

Заказчик:
Нашей организации нужно сделать автоматическое формирование QR кода для оплаты коммунальных услуг. Ведём начисления в excel. Формат квитанции мы не хотим менять, так как он уже привычен жильцам и содержит все нужные данные. Можете предложить решение?

Разработчик:
Да, можно такое сделать. Для этого разрабатывается VBA макрос для excel.
Если можете пришлите, пожалуйста, пример начислений в файле excel, по которым хотите формировать код. Я посмотрю, как лучше автоматизировать и сориентирую по цене и срокам реализации.

Заказчик:
Мне нужно именно формирование квитанции целиком, вместе с QR кодом и данными в ней.

Разработчик:
Да, можно по начислениям сделать формирование квитанций для каждой строки. Каждая строка начислений будет соответствовать отдельной квитанции с QR кодом для оплаты.

Заказчик:
Еще вопрос по макросу, правильно я понимаю, что в дальнейшем мы просто меняем данные за месяц и автоматически формируются все квитанции?

Разработчик:
Да, верно.

Заказчик
В каком формате формируются квитанции? пдф, эксель? jpeg?

Разработчик:
Excel. Его потом можно при необходимости сохранить в pdf.

Заказчик
Также может у вас реализована возможность начисления пеней в квитанции?

Разработчик:
Пени обычно считает сам поставщик услуги, если это поле есть в отдельной колонке в excel, то можно его добавить на квитанцию. В QR коде тег под пеню есть, но его никто не использует. Обычно пеню добавляют и считают в «сумму итого» к оплате, которую и помещают в QR код.

Заказчик:
Можно ли будет менять формат квитанции? Например, добавить раздел или изменить форматирование? Можно будет менять формат строк начислений?

Разработчик:
Да, формат квитанции можно будет менять. Но стоит учитывать, что позиция на которую будет помещаться QR код фиксированная, это место лучше оставлять без изменения. Остальное форматирование можно будет менять.
Насчёт начислений колонки в начислениях из которых берутся ключевые реквизиты ( ФИО, Лицевой счёт, Адрес, Сумма) макрос их отслеживает, они заданы через параметры. При крайней необходимости можно будет поменять.

Заказчик:
Что у нас останется в итоге после вашей работы?

Разработчик:
В результате работы у Вас будет привычный файл excel с вашими начислениями. Всё полностью сохраниться, будет добавлена одна кнопка «Штрихкодировать» с макросом, при нажатие на которую по начислениям будут сформированы квитанции и сгенерированы QR коды. Макросом можно будет пользоваться сколько угодно раз. Оплата за разработку единоразовая, а использовать можно будет бесконечно.

Заказчик:
Потребуется ли Ваша поддержка для этого макроса в дальнейшем? Что если мы отроем счёт в другом банке и реквизиты изменяться?

Разработчик:
Поддержка не потребуется. Будет инструкция, в которой будет всё описано. Вот пример.
В случае добавления новых счетов изменения старых — всё сможете сделать самостоятельно. Также если поменяются реквизиты организации, например смените расчётный счёт, то тоже сможете сделать самостоятельно.

В результате этого заказа был разработан максрос VBA для excel под уже существующий файл начислений, который ведёт бухгалтерия. Основные принципы работы заключаются в одной кнопке. Смотрите демонстрацию в видео ниже.

Если Вам тоже нужно добавить в свой файл excel формирование платёжного QR кода, то обращайтесь на почту analiticexpertpay@gmail.com. По цене индивидуальной разработки макроса VBA для excel от 3500 руб., в зависимости от сложности задачи.

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

Как подготовить группу квитанций с QR кодами для оплаты

Часто у небольшой управляющей компании, ТСЖ или СНТ стоит задача формировать квитанции по их клиентской базе на ежемесячной основе. При этом хочется, чтобы на каждой квитанции был нанесён платёжный QR код, который позволит в автоматическом режиме оплатить выставленный счёт через мобильное приложение (например Сбербанк-Онлайн, Тинькофф, Альфа, ВТБ) или терминал самообслуживания.
Разберём один простой способ сделать QR коды на основе списка начислений по клиентской базе.

Примечание: Если Вам нужно формировать по одной квитанции, то можно здесь сделать квитанций с QR кодом бесплатно.

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

Чтобы получить группу квитанций с QR кодами для оплаты можно создать сервис по этой ссылке. Для этого достаточно ввести банковские реквизиты вашей организации (ООО, УК, ТСЖ, СНТ и т.п.) или ИП. На почту будет выслана ссылка на личный кабинет. Далее перейдя в сервис загрузить начисления в текстом виде и нажать на кнопку «скачать» (в формате PDF).

Рассмотрим более подробно формирование группы квитанций с QR кодом по шагам

1) Заполняем банковские реквизиты здесь:
https://sbqr.ru/simpleQR/simpleQR.html

2) Переходим по ссылке, высланной на почту
Пример сервиса:
Пример сервиса группового создания QR кода для оплаты

3) Подгружаем начисления в формате txt или csv.
Для реквизитов ФИО, Назначение и сумма будет выглядеть вот так:
Иванов П.А.;Алупкинсое Шоссе, д. 1А,кв.1 ком.услуги за ноябрь 2020;200.00
Сидоров Виталий;Алупкинсое Шоссе, д. 1А,кв.2 ком.услуги за ноябрь 2020;300.00
Петров Семён;Алупкинсое Шоссе, д. 1А,кв.3 ком.услуги за ноябрь 2020;500.00

Скачать пример начислений

Примечание: иногда сценарий оплаты в мобильном банке (например Сбербанк-Онлайн) не предусматривает специальных полей Лицевой счёт, Период и т.п.. Выходом может быть — объединение нужной информации в одно поле «Назначение платежа». В excel это можно сделать через формулу, подтянув нужные ячейки.

4) Нажимаем на кнопку – Скачать в PDF
В результате у нас получается за одно нажатие клавиши подготовлен весь список квитанций с QR кодами для оплаты.
Скачать пример группы квитанций с QR кодами

Возможность группового формирования квитанций с QR кодами несёт ряд выгод владельцу бизнеса и бухгалтеру:
— экономия сил заключается в том, что не нужно каждый раз формировать по одной квитанции и одному QR коду, можно сделать сразу группу;
— это удобно для плательщиков, сам процесс проходит быстрее, исключаются ошибки в реквизитах;
— платежи принятые по QR коду содержат структурированную информацию и поэтому проще и быстрее по ним разносить платежи, когда деньги приходят на расчётный счёт.

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

А Вы уже пробовали готовить квитанции с QR кодами сразу группой? Оставляйте Ваши вопросы и комментарии ниже, будем рады ответить.

Как сделать QR код для сбора пожертвований

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

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

Сделать такой QR код для пожертвований можно в этом сервисе подготовки QR кодов и квитанций с QR кодом для оплаты.

В самом QR коде обычно поле ФИО оставляют пустым, при этом фиксируют статическую фразу для назначения платежа. Наиболее частым назначением платежа может быть:
1) Для благотворительных фондов — «добровольное пожертвование»
2) Для религиозных организаций — «пожертвование на уставные цели»
Сумму платежа тоже в QR коде оставляют пустой, чтобы была возможность человеку указать любую ему комфортную сумму во время самого платежа.
Подготовленный в приведенном выше сервисе QR код для пожертвований можно использовать при оплате в различных приложениях: Сбербанк-Онлайн, Тинькофф, ВТБ-Онлайн, Юmoney (Яндекс деньги), Альфа банка, Юникредит и других коммерческих банках.

QR код можно распечатать и повесить на видном месте, где дополнительно текстом написать что можно делать с этим QR кодом. Для религиозной организации, например будет актуальным следующий текст — «Дорогие братья и сестры! Теперь вы можете внести пожертвования по QR коду на расчётный счёт храма (церкви, прихода).»

См. также
Как оплатить по QR коду через приложение Сбербанк-Онлайн
Как оплатить по QR коду через мобильное приложение Тинькофф
Как оплатить по QR коду через приложение ВТБ-онлайн
Как оплатить по QR коду через мобильное приложение Юmoney (Яндекс.Деньги)

Описание API по генерации QR кода для оплаты

QR код формируется по ГОСТ Р 56042-2014 . Большинство банков придерживаются данного ГОСТа (Сбербанк, ВТБ, Альфа, Тинькофф, Почта России, Яндекс.Деньги, Райфайзен и т.д.).

Инструкция по подключению API по генерации QR кода для оплаты

1) Для использования API по генерации QR кодов для оплаты предварительно нужно создать личный кабинет для вашего юр.лица, перейдя по ссылке и заполнив данные юр.лица
2) После создания личного кабинета получение QR кода осуществляет GET или POST запросом с параметрами

Пример запроса для организации с ИНН 212907189988.
url с параметрами будет выглядеть так:
https://sbqr.ru/OVO/api_png_QR.php?PayeeINN=212907189988&lastName=%D0%98%D0%B2%D0%B0%D0%BD%D0%BE%D0%B2&Purpose=%D0%B7%D0%B0%20%D1%83%D1%81%D0%BB%D1%83%D0%B3%D0%B8&Sum=220
Здесь
PayeeINN — ИНН вашей организации.
LastName — передаётся ФИО плательщика (где символы сразу закодированы через функцию urlencode ).
Purpose – назначение (где символы сразу закодированы через функцию urlencode )
Sum — сумма оплаты в рублях.
sumKop — сумма в копейках (Опционально. Если используются копейки, если не используются то можно не передавать)

Возможны и другие параметры предусмотренные ГОСТом. Их добавление осуществляется при обращение на почту analiticexpertpay@gmail.com

Примечание: чтобы посмотреть пример можно скопировать запрос и вставить в строку браузера и далее отсканировать полученный QR код в Сбербанк-Онлайн.

Как вставить полученный QR код в СMS

Если будите формировать QR код у себя в CMS, то там это можно обернуть в соответствующий тег img.

Для примера вставки в СMS в виде изображения
<img src="https://sbqr.ru/OVO/api_png_QR.php?PayeeINN=212907189988&lastName=%D0%98%D0%B2%D0%B0%D0%BD%D0%BE%D0%B2&Purpose=%D0%B7%D0%B0%20%D1%83%D1%81%D0%BB%D1%83%D0%B3%D0%B8&Sum=220" />

Соответственно содержимое переменной src, а именно наполнение тегов LastName, Purpose, Sum нужно будет динамически подготовить во время заказа на вашем сайте.

Пример 1, реализации на php

<?php
###### выше может быть код вашей целевой страницы вашего сайта ######
        $data['PayeeINN'] = '212907189988'; # ИНН юр.лица
        $data['lastName'] = 'Иванов'; # плательщик 
        $data['Purpose'] = 'Оплата заказа № 1 с сайта';  # назначение
        $data['Sum'] = 200; # в рублях
        # 1 Формирование url запроса к API
        $url='https://sbqr.ru/OVO/api_png_QR.php?PayeeINN=' . $data['PayeeINN'] . '&lastName=' . $data['lastName'] . '&Purpose=' . $data['Purpose'] . '&Sum' . $data['Sum'];
        # 2 Вывод полученного изображение в документ
        echo '<img src="' . $url . '" />';      
###### ниже может быть код вашей целевой страницы вашего сайта ######        
  
?>

Пример 2, реализации на php (более сложный)

<?php
        $data['PayeeINN']= '212907189988'; # ИНН юр.лица
        $data['lastName']= 'Иванов';
        $data['Purpose']= 'Оплата заказа № 1 с сайта'; 
        $data['Sum']=200; # в рублях
        
        $url='https://sbqr.ru/OVO/api_png_QR.php';
 
        $ch = curl_init();
        # 2. указываем параметры, включая url
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        # указываем, что у нас POST запрос
        curl_setopt($ch, CURLOPT_POST, 1);
        # добавляем переменные
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        # 3. получаем HTML в качестве результата
        $result = curl_exec($ch);
        # 4. закрываем соединение
        curl_close($ch);
        $result=str_replace("src='", "src='https://www.sbqr.ru", $result);
        # 5. выводим контент в браузер
        header ("Content-type: image/png");  
        echo $result;  
?>

Если у Вас есть вопросы по API по генерации QR кодов для оплаты, то оставляйте их ниже.