Домашняя аналитика: использование QlikView для контроля личных финансов

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

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

• Контроль личного финансового потока.
• Выявление случаев неэффективного расходования капитала.
• Бюджетирование.
• Поддержка принятия решений в реализации инвестиционной стратегии.
• Анализ динамики стоимости инвестиционного портфеля.

Теперь отдельно расскажу о каждом пункте и его реализации.

Начало: источник данных

Первое, с чем сталкиваешься при разработке – откуда взять данные? К началу разработки я не занимался учетом своих расходов, поэтому в качестве источника был волен выбирать сервис. Основные требования:

• доступ со смартфона при наличии интернета,
• наличие приложения для смартфона,
• стабильность,
• возможность подключения QlikView.

Как Вы уже наверно догадались, мой выбор пал на Google Sheets. Этот сервис в достаточной мере удовлетворил всем требованиям, за исключением одной особенности: при загрузке данных в QlikView он отдает не более 100 строк. Эта проблема решилась инкрементальной загрузкой новых данных в QVD файл. Источник получился со следующими полями:

Хочу прокомментировать назначение некоторых из них:

• Приход, расход – для записи сумм прихода или расхода соответственно.
• Хран – номер счета хранения средств (наличные, банковская карта и т.п.).
• Номер строки – Id записи для реализации инкрементальной загрузки.

Личный cashflow

Финансовый поток – это разница между доходами и расходами. Для прироста капитала нам требуется положительный финансовый поток. Ну а чтобы cashflow был со знаком «+», нужно уделять ему особое внимание.

Итак, для контроля финансового потока я хотел видеть следующие показатели:

• Процент израсходованных средств – исторический.
• Процент израсходованных средств – за текущий период (месяц).
• Помесячная динамика доходов, расходов, остатков.
• Динамика прироста/уменьшения капитала.
• Справочные показатели в численном выражении.

Кроме того, данные показатели должны сигнализировать мне при пересечении критического значения, а именно – когда расходы превышают 90% дохода.

Мои требования вылились в дашборд следующего вида:

Левый верхний показатель отображает исторический процент израсходованных средств, баланс и распределение средств по счетам. Правый – то же самое, но за текущий период. На скриншоте – данные для демонстрации, которые я подобрал таким образом, будто расходы в текущем месяце уже превысили критическое значение в 90%.

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

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

Расходы в деталях

Во время анализа часто возникают вопросы – что же случилось в том месяце, когда не получилось остаться в рамках? Лист подробного анализа расходов отвечает на эти вопросы:

На нем отображаются:

• расходы в разрезе месяцев,
• детальная таблица расходов,
• распределение расходов по категориям (диаграмма справа).

Бюджет

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

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

Функционал планирования реализован следующим образом: мне необходимо заполнить поля планируемого дохода и расходов (которые не превышают 90% дохода), после чего, по нажатию кнопки, файл бюджета сохраняется в хранилище посредством макроса. После следующей перезагрузки данные о бюджете загружаются в приложение – и в текущем месяце я могу контролировать исполнение личного бюджета в разрезе категорий. В реализованном виде это выглядит следующим образом:

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

Инвестиции

Теперь пришло время поговорить о вложении финансов – ведь, к сожалению, инфляция со временем обесценивает наши средства.

На начальных этапах формирования капитала нам доступны следующие инструменты:

• Банковские вклады
• ПИФы
• Акции

Я не буду подробно останавливаться на каждом из них, скажу лишь, что для себя я выбрал ПИФы и Акции. Объяснение здесь простое: средняя ставка доходности по банковским вкладам  редко превышает значение инфляции, таким образом – банковские вклады помогают сохранить накопленные средства, но не преумножить.

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

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

В первую очередь как всегда – источники данных. В случае ПИФов меня выручил сайт Национальной Лиги Управляющих – там есть данные по ПИФам и показателям деятельности. Он позволяет выгрузить их в формате csv. Данные по стоимости акций позволяют загружать большое количество сайтов, в том числе ММВБ.

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

После такого анализа остается только обратиться в Управляющую Компанию за покупкой паев или к своему брокеру – за акциями.

Управление инвестиционным портфелем

После завершения сделок мне, естественно,интересно отслеживать динамику стоимости портфеля. К сожалению, функционал, предоставляемый управляющими компаниями достаточно скуден. Более того, отдельно смотреть стоимость портфеля акций, ПИФов, а затем их суммировать – достаточно затратно по времени. Да и просто скучно, когда у нас есть QlikView!  🙂

Поэтому очередная задача приложения – отображать стоимость портфеля на каждый день и в динамике, тем более что все данные у нас для этого уже есть – мы их загрузили на этапе анализа перед инвестированием. Не хватает лишь одного – данных о составе портфеля. Для них я завёл еще одну таблицу на GoogleSheets и вот какую визуализацию создал:

Здесь я отслеживаю динамику прироста/падения стоимости портфеля в % и в рублях, доли участников портфеля и динамику стоимости их долей.

Модель данных

Читатели блога попросили разместить модель приложения. Итак, вот что у меня получилось:

В заключение – пару слов об использовании приложения и его эффекте:

• Частота использования: Приложение я использую ежедневно для анализа текущих показателей. Раз в год – для пересмотра состава инвестиционного портфеля.
• Самая полезная «фишка»: Сильный эффект вызвало внедрение листа бюджетирования – анализ показал, что в первый месяц использования бюджетирования затраты оказались минимальными в сравнении со всеми предыдущими месяцами использования приложения.
• Трудозатраты: Первичное накопление данных и разработка приложения заняли несколько месяцев, а потенциальный период полезного использования – жизнь.

Поделитесь, как вы используете QlikView в личных целях?