#!/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