Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/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
You can’t perform that action at this time.