финансовый учет в гугл таблицах
Собираем свою гугл-таблицу для ведения бюджета
Без знания экселя, смс и регистрации
Меня зовут Антон, и я продолжаю жить в экселе.
Расскажу, как пользоваться новой версией таблицы и настроить ее под себя.
Почему таблицу пришлось переделать
Чтобы пользоваться предыдущей версией таблицы и адаптировать ее под себя, требовалось хорошее знание экселя. А еще я переносил таблицу в гугл из обычной эксельки, поэтому были и банальные косяки форматирования. В итоге у многих читателей не получалось разобраться с таблицей: непонятно было, для чего нужны некоторые колонки.
Я проанализировал обратную связь читателей, за которую вам большое спасибо, и оптимизировал таблицы под людей с минимальным знанием экселя. Итак, разберемся, как пользоваться таблицей и настроить ее под себя.
Перейдите по ссылке ниже, и на вашем гугл-диске автоматически создастся копия таблицы для учета расходов. Чтобы воспользоваться таблицей, понадобится почта на gmail.com.
Если вы хотите посмотреть, как будет выглядеть таблица после нескольких месяцев учета, то по ссылке ниже доступна версия с демо-данными и всеми техническими колонками. Эта версия подходит пользователям с хорошим знанием экселя, которые для начала хотели бы разобраться, по какому принципу работает таблица.
Самое важное и в то же время самое сложное в учете расходов — это начать вносить данные в таблицу и делать это регулярно.
Для внесения расходов мы будем использовать следующие листы в таблице:
Зачем разбивать учет расходов на несколько листов
Для анализа и оптимизации важно учитывать именно повседневные расходы. Они часто скрывают в себе мелкие траты, которые незаметны в течение дня, но в итоге из них складывается существенная статья расходов за месяц или более крупный период.
Крупные разовые траты могут сильно повлиять на всю картину, поэтому их мы ведем отдельно. Расходы на квартиру, например на ремонт, покупку мебели, досрочные платежи по ипотеке, также обычно имеют нерегулярный характер.
Повседневные расходы вносятся так:
Аналогично можно вносить расходы на вкладках «Крупные» и «Квартира».
Что делать, если нет нужных категорий
В копии вашей таблицы уже есть преднастроенные категории, но их можно менять. Для этого нужно перейти на лист «Справочники». Там есть списки категорий для повседневных расходов, крупных расходов и расходов на квартиру.
А еще можно добавлять новые категории в пустые строчки — просто напечатайте их названия внутри очерченной области справочника. Для повседневных расходов это колонка B, для расходов на квартиру — E, для крупных — G.
Лучше настроить все категории сразу, потому что если в дальнейшем вы захотите переименовать существующую категорию, то расходы, внесенные в колонку со старым названием, будут учитываться некорректно. Например, вы записывали расходы в категорию «Авто», а потом решили переименовать ее в «Автомобиль». Расходы из категории «Авто» в переименованную категорию не подтянутся.
Если у вас нет расходов, связанных с квартирой, можно использовать лист «Квартира» для учета другой группы расходов, например на автомобиль. Для удобства можно переименовать лист и заголовок справочника на вкладке «Справочники». Для справочника расходов на квартиру это ячейка E1.
По моему опыту для формирования более-менее устойчивой картины трат нужно регулярно вносить расходы хотя бы два-три месяца, а в идеале полгода. После этого можно приступать к анализу трат: для этого есть вкладки «Дашборд» и «Динамика».
Вкладка «Дашборд» — это графики, сводные таблицы и индикаторы, которые визуализируют ваши расходы и помогают их оптимизировать. Вкладка разбита на логические блоки, у каждого блока свои функции.
Первый блок — шапка. Вот что там происходит:
Второй блок — это сводная таблица расходов в разбивке по месяцам. Она собирает информацию по расходам в каждом из месяцев. В колонке «В день» считается средний расход на повседневные траты за день.
По этой сводной таблице строится общий график расходов в месяц с разделением на повседневные, крупные и на квартиру. Если в каком-то месяце расходы сильно выбиваются на фоне остальных, сначала я смотрю, в какой из групп расходов произошло сильное отклонение, а потом уже перехожу на соответствующую вкладку и разбираюсь, почему так.
Для себя я вывел золотое правило: расходы в выходные не должны превышать 30% от всех расходов.
Четвертый блок — диаграмма повседневных расходов по категориям. Этот блок показывает, на какие повседневные расходы и сколько вы потратили за все время.
Тут все достаточно наглядно. Смотрите на график и анализируете, сколько денег сэкономили бы за все время, если бы вы:
Первый график отражает динамику среднего расхода. Тут соль в том, что рассчитывается она за последние полгода: сумма всех ваших расходов за последние полгода, поделенная на 6.
Такой показатель более правилен с точки зрения анализа. Поясню. Например, обычно вы тратите 70 тысяч рублей в месяц, но хотите снизить расходы до 50 тысяч. В одном из месяцев вам удается потратить только 50 тысяч, и кажется, что цель достигнута. Но вполне вероятно, что повседневные расходы снизились разово: например, большую часть месяца вы провели в деревне, где не на что было тратить. А когда вернетесь в привычные условия, расходы снова будут 70 тысяч.
В этом случае полезно убедиться, что вы закрепили результат — продержались на заданном уровне расходов полгода. Например, если 5 месяцев вы тратили по 70 тысяч, а в последнем — 50, средний расход за полгода составит:
(70 000 × 5 + 50 000) / 6 = 66 666 рублей
Чтобы средний расход стал 50 тысяч рублей, вам необходимо удерживать текущий результат еще 5 месяцев подряд. Окно в шестом месяце я выбрал исходя из личного опыта, эта величина зашита в формулах таблицы.
Еще на графике есть светло-голубая линия тренда. Она показывает, в каком направлении движутся ваши траты, какова тенденция. Если из месяца в месяц траты увеличиваются, то линия тренда будет восходящей. Это сигнал, что пора бы начать оптимизацию расходов.
Следующая таблица — это сводная таблица повседневных расходов в разрезе по месяцам и категориям. Где тратите много — красненькое, где мало — зелененькое. Все просто и наглядно. Таблица сама увеличивается вправо по мере накопления информации.
Эта таблица удобна тем, что позволяет делать выборки в разрезе «месяц — категория». Например, вы видите: в апреле 2018 года были большие расходы на подарки. Надо разобраться, на что было потрачено столько денег. Выделите ячейку, находящуюся на пересечении нужного месяца « 04.18 » и категории «Подарки». Дважды кликните левой кнопкой мыши на ячейку — и на новом листе гугл-таблицы сформируется нужная выборка. Потом можно удалить эту страницу.
В итоге
Хорошая таблица, но не хватает очень важной вкладки (ДОХОДЫ)
На выходе я точно знаю в каком году и ±месяце я смогу досрочно закрыть ипотеку (не разом, пока не закрыл все свободные средства идут на ее погашение). Я точно знаю в каком году и в каком месяце у меня заканчивается гарантия на авто и следовательно понимаю могу ли я купить авто того же уровня, или взять авто классом повыше, и самое главное — как это решение отразиться на накоплениях в динамике с учетом потери дохода от инвестирования из-за изъятия средств.
d1mmmk, привет из 2020, бюджетный план ± сходится. Коронавирус внёс свои корректировки: ипотеку закрыл на 2 месяца раньше версии плана от ноября 2019 т.к. из-за удалёнки и закрытия всего тратить деньги не на что, отпуск тоже прошёл немного скромнее. До встречи в 2021.
Полный набор классических проблем ведения бюджета. В аналитике смысла нет почти нигде.
Примеры:
— на квартиру я потратил
Считают почасовой доход и хвалят за старательность: как гугл-таблицы облегчают мне жизнь
У меня двое маленьких детей, поэтому пришлось научиться планировать бюджет и грамотно расходовать свободное время.
В этом мне помогают гугл-таблицы. Я не изучала «Эксель» углубленно и составляла формулы, руководствуясь подсказками справочного центра. Но если вы еще не пробовали самостоятельно создавать шаблоны, вам могут пригодиться мои: под каждым блоком есть ссылки на таблицы — создавайте копию и примеряйте на себя.
Планировать расходы
Что умеет таблица: вычислять сумму, которую мы с мужем можем потратить на «хотелки».
В чем фишка: с помощью сложных условий с функцией «Если» я создала пересчитываемые ячейки. Таблица сама выясняет, есть ли у нас свободные деньги, и распределяет их по категориям.
В ней пять основных столбцов: обязательно, необязательно, накопления, минималка и реальный доход.
Вот что они значат.
Обязательно — это деньги, которые мы тратим на еду, жилье, детей, транспорт и здоровье. Раньше для учета расходов мы пользовались приложением «Дзен-мани», так что суммы взяли из его отчетов.
Сюда же входят регулярные платежи за связь и зарплата няне. В этом столбце числа меняются редко. В основном — когда мы замечаем, что стали больше тратить на продукты, или появляется новый ежемесячный платеж, например кредит.
Необязательно — это те категории трат, благодаря которым жизнь становится комфортнее: новая одежда, парикмахерская, походы в кино, подарки. Суммы здесь пишем примерные, исходя из потребностей в текущем месяце. Если заработали маловато — в ячейках ставим нули.
Пересчитываемые категории в этом столбце — это развлечения и личные траты. Они заполняются по остаточному принципу, когда таблица посчитает, сколько требуется на все запланированные обязательные, необязательные расходы и накопления.
Накопления. Случается так, что мы выходим за рамки бюджета. Поэтому 15% от зарплаты обязательно откладываем в стабфонд.
Формулы в таблице настроены так, что если свободные деньги есть, но они составляют, к примеру, только 7% от зарплаты, то вся эта сумма уйдет в стабфонд, а в другие пересчитываемые категории ничего не прибавится. Если мы хотим еще на что-то накопить, то добавляем категорию в этот столбец.
Минималка считает сумму обязательных и необязательных расходов без учета личных трат и развлечений. Столбец также показывает, сколько нужно зарабатывать, чтобы иметь возможность копить.
Реальный доход. Столбец суммирует все то, что мы с мужем заработали за месяц. Если получается меньше минималки — значит, нужно либо идти еще поработать, либо пересмотреть расходы.
Как пользоваться. Записываем в таблицу общий доход за месяц и прикидываем траты. Затем настраиваем параметры во вспомогательной таблице:
Потом происходит магия. Меняя числа реального дохода и процента свободных денег, можно наблюдать, как меняется количество «бонусов». Полученные суммы мы распределяем по счетам в интернет-банке и стараемся не тратить больше, чем запланировали.
Параметры легко подстроить под себя, так как они вынесены в отдельную таблицу. Названия категорий и значения можно менять везде, кроме ячеек с формулами.
Рассчитать зарплату няне
Что умеет таблица: считать зарплату няни за день.
В чем фишка: гугл-таблицы умеют считать разницу во времени с помощью функций «Час» и «Минуты». Если привязать к часам ставку, можно получить сумму за отработанное время.
Я вношу время, в которое пришла и ушла няня, а таблица считает дневной заработок. По пятницам я суммирую зарплату за неделю и перевожу ее няне.
В столбце «Итог» есть подсказки: оранжевым цветом и словом «расчет!» отмечен конец недели. Я нахожу цветные ячейки, записываю в них формулу «Сумма» и указываю, какие числа нужно сложить.
Если няня работает в выходной или праздничный день, таблица рассчитает зарплату по повышенной ставке. Чтобы отметить такие дни, нужно поставить галочку в столбце рядом с датой. По умолчанию это суббота и воскресенье.
Когда наступает новый месяц, я просто меняю в шаблоне даты, и таблица подсказывает, когда наступают выходные и расчетные дни. Если у вас почасовая оплата труда, можете использовать этот шаблон для контроля дневного и еженедельного заработка.
Посчитать траты на машину
Что умеет таблица: проверяет, укладываемся ли мы в бюджет, совершая покупку.
В чем фишка: такую таблицу можно использовать для подсчета расходов на любую крупную покупку — как калькулятор расходов или как шаблон для составления сметы.
В таблице четыре основных столбца: сумма, назначение, бюджет и остаток. Рекомендую также добавить столбец для даты — это поможет зафиксировать время, которое займет процесс покупки.
Вот как заполнять эти столбцы:
Обычно я копирую шаблон на два листа: план и факт. На первом листе составляю смету, а на втором записываю фактические расходы. Как правило, ожидания редко совпадают с реальностью.
Покупка машины включает в себя кучу дополнительных расходов: страховки, пошлина, бензин и масло. Когда все траты перед глазами, легче принять решение о том, стоит ли еще вкладываться в тачку или уже пора остановиться.
А еще ссылкой на гугл-таблицы можно делиться: это выручает, когда нужно объяснить «почему так дорого» тому, кто финансово участвует в покупке, или поделиться опытом с друзьями. У меня есть такие мини-отчеты о свадьбе, ремонте и покупке техники — иногда возвращаюсь к ним, когда кто-то просит совета.
Похвалить за старательность
Что умеет таблица: запоминает дни, когда я не тратила время зря.
В чем фишка: в гугл-таблицах можно использовать флажки и устанавливать им значения. Я применила эту возможность для создания системы оценки. Приятно кликнуть на флажок и получить смайлик в качестве похвалы — работает как дополнительная мотивация к учебе.
Когда нашему первому ребенку исполнилось 10 месяцев, мне захотелось выйти на работу. К тому времени я уже окончила курс разработки интерфейсов и понимала, что если не продолжу начатое, то все забуду. Поэтому я придумала несложную мотивирующую таблицу.
Сначала я записала важные темы, которыми желательно заниматься ежедневно: решать задачки по программированию, работать над проектом для портфолио и изучать следующий курс. Их я отметила значком 🔁.
После них идут дополнительные: чтение учебников, перевод английских статей и любые другие занятия, касающиеся программирования. Над этими столбцами значок 💰.
Принцип работы с таблицей простой: нужно поставить галочки под той темой, которой я посвятила время. За выполнение основных упражнений я получаю 5 баллов, дополнительных — 1 балл. Чем больше баллов, тем больше смайликов в столбце «Зачет».
Для меня было важно не то, как быстро я выйду на работу, а сам факт, что я нахожу время учиться. Когда зеленых ячеек становится много, появляется повод себя похвалить.
Мой шаблон подойдет для любых занятий — например, для тренировок, изучения языка или уборки.
Vladimir, Создайте гугл-таблицу по учету, актуализации, анализу и использованию гугл-таблиц.
Evgeniy, я вот себе такую уже создала в альфа версии
Vladimir, тебе сюда https://www.reddit.com/r/dataisbeautiful/
Denis, Залип на пару часов
Vladimir, а есть таблица всех таблиц?
Vladimir, не обижайтесь, кажется вам к врачу
Vladimir, «учет пройденных видеоигр» — я знал, что не я один такой маньяк! 🙂
Vladimir, я тоже перешёл с локальных файлов на гугл-таблицы чтобы не зависеть от установленного софта.
Vladimir, а я всех героев в Лиге Легенд перевёл в таблицу, отметил какие сложные, какие по-проще, сколькими из них и как часто я играл, чтобы определиться с приоритетами. Теперь буду ещё и такой учёт вести, крутая привычка.
Вообще жизнь идёт к тому, чтобы создать log собственной жизни. Сделать big data по себе любимому, анализировать, декомпозировать, постоянно улучшать и даже иногда ностальгировать.
Круто, не знал про чекбоксы, прокачал себе таблицу для закупки в магазине. На скрине общий список для редактирования, на втором листе сводная таблица для печати перед походом в магазин. Индекс соответствует траектории движения по магазину, чтобы пройти все змейкой один раз и все закупить, работает только если закупаешься в одном и том же магазине. Спасибо!
Леонид, ой, а можно копию на вашу таблицу? 😊
Леонид, а я для походов в магазин использую заметки на айфоне. Там тоже можно установить чекбоксы. Удобно, что отмеченный товар спускается в конец списка. Плюс можно расшарить заметку на всех членов семьи.
А уж если табличку еще и распечатать, так вообще красота! Мне кажется, что для ответстенной закупки в поход или объемной на праздник табличка будет удобнее.
Леонид, просим о возможности сделать копию! 🙂
Веду свой бюджет в Google Sheets с 06.07.2018. Приходится фиксировать каждую трату — это неудобно, но дисциплинирует.
В таблице у меня есть свободный остаток — количество денег, которое я могу свободно тратить. Есть суммарный остаток — общее количество денег которыми я обладаю. В суммарном остатке учитываются все отложенные деньги, вклады и валюта. Валюта автоматически конвертируется в рубли по курсу.
Есть небольшая детализация по каждому счёту. Ещё есть возможность видеть траты на конкретные категории по годам и категориям. Например, за время учёта финансов на видеоигры я потратил 64 516 ₽.
Поскольку я фиксирую каждую трату, я легко могу узнать, когда совершил ту или иную покупку.
По такому же принципу веду учёт финансов своего проекта.
С прошлого года начал фиксирвать свою футольную статистику: когда была тренировка, где она проходила и сколько мячей я забил (если забил).
В отдельной таблице веду рабочий дневник. Просто описываю, что сделал за день, чтобы потом быстро вспомнить, если в этом будет необходимость.
Семеон, у меня подобный семеный бюджет, но я его заполняю в конце месяца на основе csv-выписок по счетам из разных банков, главное не забывать записывать траты наличными)
В гуглотаблице написал макрос который категорирует повторяющие операции (исходя из данных получателя (счета или имени)), что сильно облегчило рутинность заполнения. Данные «журнала» оформляются по месяцам и категориям через сводные таблицы.
Но в целом энтузиазм пропадает со временем, если не ставить какие-то аналитические вопросы к бюджету.
Давыд Васильцов, [22.07.20 09:07]
300 быт телефон
BabloBot, [22.07.20 09:07]
Занесено 300 р. в категорию «Быт» с пометкой «телефон».
Бот парсит сообщения и по кодовым словам (их находит сам в таблице) раскидывает по категориям, прописывая в комментариях указанную конкретику по покупкам.
Плюс, сделал пару команд для выводу данных, чтобы прямо в телеге посмотреть, сколько потрачено (в целом по категориям за месяц или траты по конкретной категории за месяц), сколько денег осталось, или чтобы отменить последнюю введенную покупку (на случай, если неправильно внес).
Давыд Васильцов, [24.07.20 13:34]
/0 неизб
BabloBot, [24.07.20 13:34]
2440 р. по категории «Неизб» за этот месяц.
10.07.20: 200 р. = телмаме
17.07.20: 240 р. = комиссия за перевод
18.07.20: 1000 р. = багаж
20.07.20: 1000 р. = багаж
А уже в таблицу изредка заглядываем, чтобы проанализировать более детально или на более длительных периодах (ибо гуглшитс ванлав).
Главное, чтоб автору нравилось 🙂
Со смайлами прикольная тема, не знал о такой функции!
Сделаю себе такое для занятия спортом 🙂 и алкоголем 🙁
Как я слежу за акциями в гугл-таблице: три простых способа
Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.
Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.
Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.
Что за АПИ
В большинстве случаев данные с бирж передаются через программный интерфейс, называемый API — application programming interface. Грубо говоря, это инструмент, благодаря которому одна программа начинает говорить на одном языке с другой. В нашем случае мы используем API финансовых бирж для «дружбы» с гугл-таблицами.
API Московской биржи
Для чего. Получить информацию о российских акциях и облигациях.
Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл-таблиц. Например, можно получать имена инструментов, цены закрытий, даты оферт и другие параметры.
Работа этой функции полностью зависит от сервера Московской биржи. Если биржа поменяет формат выдачи, организует какие-то другие режимы торгов или изменит что-то в существующей системе, то для корректной работы формул потребуется их доработка.
Еще таблица пригодится, если вас не устраивают существующие сервисы учета — Intelinvest и Investing.com.
В таблице-примере к этой статье я привел несколько полезных параметров ценных бумаг. Вот что она умеет:
Список далеко не полный, ведь на Мосбирже огромное количество параметров, включая срочный рынок и валюты.
Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк — чтобы каждый смог воспроизвести эту конструкцию в своей таблице.
Googlefinance внутри гугл-таблиц
Для чего. Получить информацию об американских акциях, которые торгуются на Санкт-Петербургской бирже.
Как это работает. Googlefinance — это встроенная функция гугл-таблиц. Она помогает получить текущие или архивные данные о ценных бумагах из сервиса «Гугл-финансы».
Российские акции в сервисе тоже есть. Чтобы их увидеть, надо указывать тикер — то есть код из нескольких букв, который принадлежит определенному финансовому инструменту, — с приставкой «MCX:» так, чтобы получилось «MCX:SBER».
Эта функция позволяет получать название, текущую цену акций и еще 18 параметров — например, максимальную и минимальную цену за 52 недели, количество акций в обращении. Все это есть в моем шаблоне.
Главное преимущество в том, что формулы для получения этих параметров предельно просты: например, имя бумаги — name, самая высокая цена на текущий день — high. Описание всех формул и атрибутов есть в инструкции от Гугла.
В первом столбце я оставил идентификаторы двух акций — Сбербанка (MCX;SBER) и «Нетфликс» (NFLX) — и фонда Van Eck (RSX). При изменении тикеров во втором столбце будет подтягиваться название акций и актуальные цены.
А чтобы получить подробную информацию об акции или фонде, используйте ячейки В13 и В35: в них нужно занести тикер и в таблицах ниже сразу же обновится информация.
Например, вы хотите отслеживать акции американского производителя сетевого оборудования Ubiquiti Inc (UI) и высокодивидендного фонда Highland Small-Cap Equity Y (HSZYX). В ячейку В13 заносите тикер акции UI, а в ячейку В35 — тикер фонда HSZYX. Получите 19 параметров каждой бумаги.
Смотрите, как это работает:
«Яху-финанс»
Для чего. Отслеживать любые финансовые данные со всего мира.
Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.
Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.
Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования: