Personal finance tracking system for income and expenses with a dashboard and analytics in Google Sheets

Main project goals:

1. Automate the accounting of income and expenses.
2. Provide a dashboard with key financial metrics.
3. Simplify the addition of transactions through a single field.
4. Ensure a visual representation of financial data by categories, assets, and liabilities.

Functional requirements

1. "Transactions" tab

• Columns:
  • Date (added via calendar).
  • Transaction type (Income/Expense/Investment/Capital).
  • Category (for example, "Food", "Salary", "Investments").
  • Account (for example, "Cash", "Bank").
  • Account to (for transfers between accounts).
  • Amount.
  • Description.
  • "Add" button for automatic data entry.
• Functionality:
  • Data validation (date, transaction type, amount).
  • Automatic update of all related tabs.

2. "Income and Expenses" tab

• Data separation:
  • Categories of income and expenses.
  • Monthly and annual totals.
• Formulas:
  • Calculation of income and expenses by categories:

    =SUMIFS('Transactions'!$F$2:$F; 'Transactions'!$C$2:$C; Category; 'Transactions'!$B$2:$B; "Income/Expense"; 'Transactions'!$A$2:$A; ">="&DateStartOfMonth; 'Transactions'!$A$2:$A; "<="&DateEndOfMonth)
    

  • Monthly totals:

    =SUMIFS('Transactions'!$F$2:$F; 'Transactions'!$A$2:$A; ">="&DateStartOfMonth; 'Transactions'!$A$2:$A; "<="&DateEndOfMonth)
    

3. "Assets and Accounts" tab

• Columns:
  • Account (name).
  • Initial balance.
  • Current balance (automatically updated).
  • Account type (Cash, Bank, Cryptocurrency, Investments, etc.).
• Formulas:
  • Calculation of current balance:
  • =InitialBalance + SUMIFS('Transactions'!$F$2:$F; 'Transactions'!$D$2:$D; AccountName; 'Transactions'!$B$2:$B; "Income") - SUMIFS('Transactions'!$F$2:$F; 'Transactions'!$D$2:$D; AccountName; 'Transactions'!$B$2:$B; "Expense")


4. "Liabilities" tab

• Columns:
  • Type of liability (for example, loan, mortgage).
  • Balance at the beginning of the month.
  • Payments for the month (calculated automatically).
  • Balance at the end of the month.

5. Dashboard

• Main elements:
  • Current year and date.
  • Key indicators:
    • Assets.
    • Liabilities.
    • Net capital (assets - liabilities).
    • Cash flow (income - expenses).
    • Investments.
  • Time indicators:
    • Free time, time for sleep, work, etc.
  • Hourly rate.
  • Age, retirement goal, remaining years.
• Charts:
  • Pie chart of expenses by category.
  • Line chart of capital changes.
  • Bar chart of income and expenses for the year.
• Functionality:
  • Automatic updates based on data from the tabs.
  • Ability to set target values (for example, monthly budget).

Implementation tools

• Google Sheets:
  • Using formulas for automating calculations.
  • Integration with Google Apps Script for the "Add" button.
• Charts:
  • Built-in capabilities of Google Sheets for creating charts.

Desired Result

• Fully automated spreadsheet that meets the specified requirements.
• Dashboard for a clear display of current financial status.
• Interactive functionality for entering new transactions.