build_savings.rb

#!/usr/bin/env ruby

require 'pg'
require 'sequel'
require 'date'

DB = Sequel.connect('postgres://ledger:password@kodos.zrail.net')

RULES = [
  { 'Emergency'      => { min: 16000, max: 17000, weight: 10 } },
  { 'Medical'        => { min:  1500, max:  4000, weight:  8 } },
  { 'Utils:Electric' => { min:    50, max:   400, weight:  8 } },
  { 'Utils:Gas'      => { min:   100, max:   200, weight:  8 } },
  { 'Utils:Water'    => { min:   100, max:   200, weight:  8 } },
  { 'Insurance:Car'  => { min:   200, max:   500, weight:  8 } },
  { 'Insurance:Life' => { min:   400, max:   700, weight:  8 } },
  { 'House'          => { min:  3000, max: 15000, weight:  8 } },
  { 'Car'            => { min:   100, max:  1000, weight:  1 } },
  { 'Bike'           => { min:    50, max:   100, weight:  1 } },
  { 'Entertainment'  => { min:   100, max:   200, weight:  2 } },
  { 'Clothes'        => { min:   100, max:   200, weight:  1 } },
  { 'Furniture'      => { min:   200, max:  4000, weight:  4 } },
  { 'Travel'         => { min:  2000, max: 20000, weight:  4 } },
]

month = ARGV[0]
override_income = ARGV[1] ? ARGV[1].to_f : nil
override_expenses = ARGV[2] ? ARGV[2].to_f : nil

unless month
  raise "Usage: build_savings.rb 2015-05-01 [override_income] [override_expenses]"
end

month_end = (Date.parse(month) >> 1) - 1

income_query = <<HERE
  with inc as (
    select
      'Salary (after taxes and retirement)' as account,
      sum(amount) as amount
    from
      ledger
    where
      xtn_month = '#{month}'
      and account ~ 'Assets:.*:(Checking|Joint)'
      and note ~ 'Salary'
    union all
    select
      *
    from (
      select
        account,
        -1 * sum(amount) as amount
      from
        ledger
      where
        account ~ '^Income'
        and account !~ '^Income:Salary'
        and xtn_month = '#{month}'
        and account !~ 'Retirement'
      group by
        account
      order by
        account
    ) x
    union all
    select
      'retirement',
      sum(amount) as amount
    from
      ledger
    where
      account ~ 'Assets:.*:Checking'
      and note ~ 'Roth Buy'
      and xtn_month = '#{month}'
  )
  select sum(amount) as amount from inc
HERE

expenses_query = <<HERE
  select
    sum(amount) as amount
  from
    ledger
  where
    account ~ '(Expenses|Liabilities:Loans|Assets:House:Escrow)'
    and note !~ 'Salary'
    and (jtags->>'nobudget') is null
    and xtn_month = '#{month}'
    and xtn_id not in (select distinct xtn_id from ledger where account ~ '(Assets:Funds|Equity)' and xtn_month = '#{month}')
HERE

funds_query = <<HERE
  select
    account,
    sum(amount) as amount
  from
    ledger
  where
    account ~ 'Assets:Funds'
    and xtn_date <= '#{month_end}'
  group by
    account
HERE

total_income = override_income || DB.fetch(income_query).all.first[:amount].to_f
total_expenses = override_expenses || DB.fetch(expenses_query).all.first[:amount].to_f
remaining_income = total_income - total_expenses

if remaining_income <= 0
  STDERR.puts "Income after expenses: #{remaining_income}. Spend less money."
  exit 1
end

fund_balances = {}
DB.fetch(funds_query).all.each do |row|
  fund_balances[row[:account].gsub('Assets:Funds:', '')] = row[:amount]
end

account_weights = {}
total_weight = 0
RULES.each do |rule|
  account = rule.keys.first
  rules = rule.values.first
  weight = rules[:weight]

  if (fund_balances[account] || 0) < rules[:min]
    weight = weight * 4
  elsif fund_balances[account] >= rules[:max]
    weight = 0
  end

  total_weight += weight
  account_weights[account] = weight
end


STDERR.puts "; Allocating #{remaining_income} = #{total_income} - #{total_expenses}"

xtns = {}
counted_income = 0
RULES.each do |rule|
  account = rule.keys.first
  rules = rule.values.first
  weight = account_weights[account]
  balance = fund_balances[account] || 0
  share = weight.to_f / total_weight.to_f
  STDERR.puts "; #{account} => #{remaining_income.to_f} #{weight.to_f} #{total_weight.to_f} #{share.to_f} #{rules[:max]} #{balance.to_f}"

  deposit_amount = [remaining_income * share, rules[:max] - balance].min

  next if deposit_amount.round == 0

  total_weight -= weight
  remaining_income -= deposit_amount
  xtns[account] = deposit_amount
  counted_income += deposit_amount
end

STDERR.puts "; counted_income: #{counted_income.to_f}"

lines = []
xtns.each do |acct, amount|
  asset_amount = sprintf("$%0.2f", amount)
  liability_amount = sprintf("$%0.2f", amount * -1)
  lines << "    Assets:Funds:#{acct}       #{asset_amount}"
  lines << "    Liabilities:Funds:#{acct}  #{liability_amount}"
end

deposit_date = month_end.strftime("%Y/%m/%d")

puts "#{deposit_date} * Savings Deposits"
puts lines.join("\n")
puts ""