Home Economy Livebook

Mix.install([
  {:kino, "~> 0.12.3"},
  {:kino_db, "~> 0.2.7"},
  {:exqlite, "~> 0.11"}
])

Home Economy

This is a simple Home Economy Livebook based on Bennedetto. Here is a more detailed explanation of the ideas. CIV Budget.

Database Creation

We start with a simple empty sqlite3 database. An empty file can be created using the command:

sqlite3 facts.db "VACUUM;"

Check https://www.sqlite.org/lang_vacuum.html for more info.

opts = [database: Kino.FS.file_path("facts.db")]
{:ok, conn} = Kino.start_child({Exqlite, opts})

Exqlite.query!(conn, "PRAGMA table_list", [])

%Exqlite.Result{command: :execute, columns: ["schema", "name", "type", "ncol", "wr", "strict"], rows: [["main", "sqlite_schema", "table", 5, 0, 0], ["temp", "sqlite_temp_schema", "table", 5, 0, 0]], num_rows: 2}

Tables

We will store data in two tables. One for constant transactions and one for daily transactions.

_ =
  Exqlite.query!(
    conn,
    """
    CREATE TABLE IF NOT EXISTS periodic_txs (
      id INTEGER PRIMARY KEY,
      label TEXT NOT NULL,
      amount INTEGER NOT NULL, 
      type INTEGER NOT NULL, -- 0 income, 1 expense
      days INTEGER NOT NULL DEFAULT 1,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

      UNIQUE(label)
    );
    """,
    []
  )

%Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}

Daily transaction will be stored in another table. This table will be updated every day.

_ =
  Exqlite.query!(
    conn,
    """
    CREATE TABLE IF NOT EXISTS txs (
      id INTEGER PRIMARY KEY,
      amount INTEGER NOT NULL,
      type INTEGER NOT NULL, -- 0 income, 1 expense
      description TEXT,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    """,
    []
  )

%Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}

Data Insertion

Here we insert data to our tables. First with the periodic transaction table and then the daily transaction table.

Periodic Expenses

Example of expenses such as food, internet and related. Measured in days. Example Video Stream service is $ 10 USD every 30 days. The amount is measured in the integer of the currency. In this case every USD is 100 cents. So the Example Video Stream is 1000 cents every 30 days.

The SQL for insertion is the following.

INSERT OR IGNORE INTO periodic_txs (label, amount, type, days, applied_at)
  VALUES ('Video Stream Service', 1000, 1, 30, '2024-04-01 00:00:00')

If you want a specifc date must be ISO 8601 (yyyy-MM-dd hh:mm:ss)

Name	Amount (USD)	Amount (Cents)	Days	Applied At
Video Stream Service	10	1000	30	2024-04-01 00:00:00
Basic Services (Water, Electricity, Gas)	50	5000	30	2024-04-01 00:00:00
Internet + Phone	40	4000	30	2024-04-01 00:00:00
Food	100	10000	30	2024-04-01 00:00:00
Transport	30	3000	30	2024-04-01 00:00:00

[
  %{
    label: "Video Stream Service",
    amount: 10 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  },
  %{
    label: "Basic Services",
    amount: 50 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  },
  %{
    label: "Internet + Phone",
    amount: 40 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  },
  %{
    label: "Food",
    amount: 100 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  },
  %{
    label: "Transport",
    amount: 30 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  }
]
|> Enum.map(fn item ->
  Exqlite.query!(
    conn,
    """
    INSERT OR IGNORE INTO periodic_txs (label, amount, type, days, applied_at)
      VALUES (?, ?, 1, ?, ?)
    """,
    [item.label, item.amount, item.days, item.applied_at]
  )
end)

[
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0},
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0},
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0},
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0},
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}
]

Periodic Income

Example periodic incomes such as the "monthly salary" of a day job (2000 USD) or 200.000 cents.

The example SQL is the following:

INSERT OR IGNORE INTO periodic_txs (label, amount, type, days, applied_at)
  VALUES ('Salary', 200000, 0, 30, '2024-05-01 00:00:00')

If you want a specifc date must be ISO 8601 (yyyy-MM-dd hh:mm:ss)

[
  %{
    label: "Salary",
    amount: 2000 * 100,
    days: 30,
    applied_at: "2024-04-01 00:00:00"
  }
]
|> Enum.map(fn item ->
  Exqlite.query!(
    conn,
    """
    INSERT OR IGNORE INTO periodic_txs (label, amount, type, days, applied_at)
      VALUES (?, ?, 0, ?, ?)
    """,
    [item.label, item.amount, item.days, item.applied_at]
  )
end)

[%Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}]

Daily Transactions

First we insert into daily transaction table all the periodic transactions that have expired the amount of days since the last applied at.

The amount of days between now and the last applied date must be greater or equal than the amount of days the periodic tx must be applied again.

SELECT * FROM periodic_txs WHERE
  julianday('now') - julianday(applied_at) >= days
  AND type = 0

This is the same process for the income (type 0) and the expenses (type 1).

# Insert Periodic Income into Daily Transactions
Exqlite.query!(conn, """
  SELECT * FROM periodic_txs WHERE
  julianday('now') - julianday(applied_at) >= days
  AND type = 0
""")
|> then(& &1.rows)
|> Enum.each(fn [id, label, amount, _type, _days, _created_at, _applied_at] ->
  Exqlite.query!(
    conn,
    """
      INSERT OR IGNORE INTO txs (amount, type, description) VALUES (?, 0, ?)
    """,
    [amount, label]
  )

  Exqlite.query!(
    conn,
    """
      UPDATE periodic_txs SET applied_at = CURRENT_TIMESTAMP WHERE id = ?
    """,
    [id]
  )
end)

# Insert Periodic Expenses into Daily Transactions
Exqlite.query!(conn, """
SELECT * FROM periodic_txs WHERE
  julianday('now') - julianday(applied_at) >= days
  AND type = 1
""")
|> then(& &1.rows)
|> Enum.each(fn [id, label, amount, _type, _days, _created_at, _applied_at] ->
  Exqlite.query!(
    conn,
    """
      INSERT OR IGNORE INTO txs (amount, type, description) VALUES (?, 1, ?)
    """,
    [amount, label]
  )

  Exqlite.query!(
    conn,
    """
      UPDATE periodic_txs SET applied_at = CURRENT_TIMESTAMP WHERE id = ?
    """,
    [id]
  )
end)

:ok

Now we can add our daily transactions. Example SQL

expense

INSERT OR IGNORE INTO txs (amount, type, description)
  VALUES (500, 1, 'A cup of coffee from a mermaid')

income

INSERT OR IGNORE INTO txs (amount, type, description)
  VALUES (1000, 0, 'Found 10 Bucks inside the sofa')

[
  %{
    description: "A cup of coffe from a mermaid",
    # expense
    type: 1,
    amount: 5 * 100
  },
  %{
    description: "Found 10 Bucks inside the sofa",
    # income
    type: 0,
    amount: 10 * 100
  }
]
|> Enum.map(fn item ->
  Exqlite.query!(
    conn,
    """
    INSERT OR IGNORE INTO txs (amount, type, description)
      VALUES (?, ?, ?)
    """,
    [item.amount, item.type, item.description]
  )
end)

[
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0},
  %Exqlite.Result{command: :execute, columns: [], rows: [], num_rows: 0}
]

Queries and Reports

Now we can generate reports based on the data.

Periodic Incomes

Let's check how many periodic incomes we have

_ = Exqlite.query!(conn, "SELECT * FROM periodic_txs WHERE type = 0;", [])

%Exqlite.Result{command: :execute, columns: ["id", "label", "amount", "type", "days", "created_at", "applied_at"], rows: [[6, "Salary", 200000, 0, 30, "2024-05-13 03:58:34", "2024-05-13 03:59:50"]], num_rows: 1}

How much do we have from periodic incomes (In USD)?

_ =
  Exqlite.query!(
    conn,
    "SELECT SUM(amount) / 100 AS periodic_incomes FROM periodic_txs WHERE type = 0;",
    []
  )

%Exqlite.Result{command: :execute, columns: ["periodic_incomes"], rows: [[2000]], num_rows: 1}

Let's check which periodic incomes were applied now

_ =
  Exqlite.query!(
    conn,
    """
    SELECT * FROM periodic_txs WHERE 
    julianday('now') - julianday(applied_at) >= days AND type = 0;
    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["id", "label", "amount", "type", "days", "created_at", "applied_at"], rows: [], num_rows: 0}

Periodic Expenses

Lets check all our periodic expenses

_ = Exqlite.query!(conn, "SELECT * FROM periodic_txs WHERE type = 1;", [])

%Exqlite.Result{command: :execute, columns: ["id", "label", "amount", "type", "days", "created_at", "applied_at"], rows: [[1, "Video Stream Service", 1000, 1, 30, "2024-05-13 03:58:28", "2024-05-13 03:59:50"], [2, "Basic Services", 5000, 1, 30, "2024-05-13 03:58:28", "2024-05-13 03:59:50"], [3, "Internet + Phone", 4000, 1, 30, "2024-05-13 03:58:28", "2024-05-13 03:59:50"], [4, "Food", 10000, 1, 30, "2024-05-13 03:58:28", "2024-05-13 03:59:50"], [5, "Transport", 3000, 1, 30, "2024-05-13 03:58:28", "2024-05-13 03:59:50"]], num_rows: 5}

How much do we need to cover all our expenses? (In USD)

_ =
  Exqlite.query!(
    conn,
    "SELECT SUM(amount) / 100 AS periodic_expenses FROM periodic_txs WHERE type = 1;",
    []
  )

%Exqlite.Result{command: :execute, columns: ["periodic_expenses"], rows: [[230]], num_rows: 1}

Let's check which expenses has been applied now

_ =
  Exqlite.query!(
    conn,
    """
    SELECT * FROM periodic_txs WHERE 
    julianday('now') - julianday(applied_at) >= days AND type = 1;
    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["id", "label", "amount", "type", "days", "created_at", "applied_at"], rows: [], num_rows: 0}

Daily Transactions

Let's see all transactions so far in our database

_ = Exqlite.query!(conn, "SELECT * FROM txs ORDER BY created_at DESC", [])

%Exqlite.Result{command: :execute, columns: ["id", "amount", "type", "description", "created_at"], rows: [[7, 500, 1, "A cup of coffe from a mermaid", "2024-05-13 03:59:54"], [8, 1000, 0, "Found 10 Bucks inside the sofa", "2024-05-13 03:59:54"], [1, 200000, 0, "Salary", "2024-05-13 03:59:50"], [2, 1000, 1, "Video Stream Service", "2024-05-13 03:59:50"], [3, 5000, 1, "Basic Services", "2024-05-13 03:59:50"], [4, 4000, 1, "Internet + Phone", "2024-05-13 03:59:50"], [5, 10000, 1, "Food", "2024-05-13 03:59:50"], [6, 3000, 1, "Transport", "2024-05-13 03:59:50"]], num_rows: 8}

How much income do we have so far?

_ =
  Exqlite.query!(
    conn,
    "SELECT SUM(amount) / 100 AS income_total FROM txs WHERE type = 0;",
    []
  )

%Exqlite.Result{command: :execute, columns: ["income_total"], rows: [[2010]], num_rows: 1}

How much expenses do we have so far?

_ =
  Exqlite.query!(
    conn,
    "SELECT SUM(amount) / 100 AS expenses_total FROM txs WHERE type = 1;",
    []
  )

%Exqlite.Result{command: :execute, columns: ["expenses_total"], rows: [[235]], num_rows: 1}

Ok let's check only this month transactions

_ =
  Exqlite.query!(
    conn,
    """
    SELECT * FROM txs WHERE 
      created_at BETWEEN 
        datetime('now', 'start of month') 
        AND datetime('now', 'start of month', '+1 month', '-1 day')
    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["id", "amount", "type", "description", "created_at"], rows: [[1, 200000, 0, "Salary", "2024-05-13 03:59:50"], [2, 1000, 1, "Video Stream Service", "2024-05-13 03:59:50"], [3, 5000, 1, "Basic Services", "2024-05-13 03:59:50"], [4, 4000, 1, "Internet + Phone", "2024-05-13 03:59:50"], [5, 10000, 1, "Food", "2024-05-13 03:59:50"], [6, 3000, 1, "Transport", "2024-05-13 03:59:50"], [7, 500, 1, "A cup of coffe from a mermaid", "2024-05-13 03:59:54"], [8, 1000, 0, "Found 10 Bucks inside the sofa", "2024-05-13 03:59:54"]], num_rows: 8}

Check for Today, Week, Month and Year stats

Today

Today Current Income

today_incomes =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 0
                   AND created_at BETWEEN datetime('now', 'start of day') AND
                                          datetime('now', 'start of day', '+1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[201000, 2010]], num_rows: 1}

Today Current Expenses

today_expenses =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 1
                   AND created_at BETWEEN datetime('now', 'start of day') AND
                                          datetime('now', 'start of day', '+1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[23500, 235]], num_rows: 1}

Today Available Amount

[[today_incomes, _]] = today_incomes.rows
[[today_expenses, _]] = today_expenses.rows

today_amount =
  ((today_incomes - today_expenses) / 100)
  |> ceil()

Weekly

Weekly Incomes

weekly_incomes =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 0
                   AND created_at BETWEEN datetime('now', 'weekday 0', '-7 days') AND
                                          datetime('now', 'weekday 0', '+7 days')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[201000, 2010]], num_rows: 1}

Weekly Expenses

weekly_expenses =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 1
                   AND created_at BETWEEN datetime('now', 'weekday 0', '-7 days') AND
                                          datetime('now', 'weekday 0', '+7 days')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[23500, 235]], num_rows: 1}

Weekly Amount

[[weekly_incomes, _]] = weekly_incomes.rows
[[weekly_expenses, _]] = weekly_expenses.rows

weekly_amount =
  ((weekly_incomes - weekly_expenses) / 100)
  |> ceil()

Monthly

Monthly Incomes

monthly_incomes =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 0
                   AND created_at BETWEEN datetime('now', 'start of month') AND
                                          datetime('now', 'start of month', '+1 month', '-1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[201000, 2010]], num_rows: 1}

Monthly Expenses

monthly_expenses =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 1
                   AND created_at BETWEEN datetime('now', 'start of month') AND
                                          datetime('now', 'start of month', '+1 month', '-1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[23500, 235]], num_rows: 1}

Monthly Amount and Daily Rate.

You can expend this amount daily to be in inside the budget

[[monthly_incomes, _]] = monthly_incomes.rows
[[monthly_expenses, _]] = monthly_expenses.rows

monthly_amount =
  ((monthly_incomes - monthly_expenses) / 100)
  |> ceil()

daily_rate =
  ((monthly_incomes - monthly_expenses) / 30 / 100)
  |> ceil()

%{monthly_amount: monthly_amount, daily_rate: daily_rate}

%{monthly_amount: 1775, daily_rate: 60}

Yearly

Yearly Incomes

yearly_incomes =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 0
                   AND created_at BETWEEN datetime('now', 'start of year') AND
                                          datetime('now', 'start of year', '+1 year', '-1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[201000, 2010]], num_rows: 1}

Yearly Expenses

yearly_expenses =
  Exqlite.query!(
    conn,
    """
    SELECT SUM(amount) AS cents, SUM(amount) / 100 AS usd
            FROM   txs
            WHERE  type = 1
                   AND created_at BETWEEN datetime('now', 'start of year') AND
                                          datetime('now', 'start of year', '+1 year', '-1 day')

    """,
    []
  )

%Exqlite.Result{command: :execute, columns: ["cents", "usd"], rows: [[23500, 235]], num_rows: 1}

Yearly Amount

[[yearly_incomes, _]] = yearly_incomes.rows
[[yearly_expenses, _]] = yearly_expenses.rows

yearly_amount =
  ((yearly_incomes - yearly_expenses) / 100)
  |> ceil()

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

home-economy.livemd

home-economy.livemd

Home Economy Livebook

Home Economy

Database Creation

Tables

Data Insertion

Periodic Expenses

Periodic Income

Daily Transactions

Queries and Reports

Periodic Incomes

Periodic Expenses

Daily Transactions

Today

Weekly

Monthly

Yearly

Files

home-economy.livemd

Latest commit

History

home-economy.livemd

File metadata and controls

Home Economy Livebook

Home Economy

Database Creation

Tables

Data Insertion

Periodic Expenses

Periodic Income

Daily Transactions

Queries and Reports

Periodic Incomes

Periodic Expenses

Daily Transactions

Today

Weekly

Monthly

Yearly