Skip to content

Instantly share code, notes, and snippets.

@peterkeen
Created June 17, 2015 19:17
Show Gist options
  • Save peterkeen/ff1c0afb9f7c9a7d10fb to your computer and use it in GitHub Desktop.
Save peterkeen/ff1c0afb9f7c9a7d10fb to your computer and use it in GitHub Desktop.
#!/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 ""
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment