Hi, I recently started using Silverbullet (v2) and I have a feeling I can (finally) do away with the spreadsheet I’m using the calculate our disposable income of the next month. However, neither myself and ChatGPT can create working code to make the necessary calculations. I hope it’s allowed to ask this question here and that this is indeed possible to do in Silverbullet!
Rules in my spreadsheet:
Only costs and income with an x in the count column are counted.
Monthly credit card costs are part of the monthly credit card statement present in the one-off costs table. To prevent double counting the monthly credit card costs (only those marked with an x) are removed from the value present in the one-off costs table when calculating the one-off costs. The minimum value counted is 0 to prevent an incomplete (/negative) counting of the monthly costs.
So I guess my questions are:
How do I sum (for each table) all “Value” columns from rows with an “x” in the “Count” column?
How do I save these values in variables for further calculation?
How do I implement the if statement for the credit card costs?
The structure I have setup:
Relevant stats
Disposable income: total income - total costs
Total costs (sum of monthly costs + one-off costs):
Total income (sum of monthly income + one-off income):
As long as all of the last 4 present column headers (value, include, type & role) are included, you could also spread the transactions over multiple tables and add additional headers.
The / chars are apparently necessary to prevent silverbullet from just ignoring the cell:
-- (disposable) income calculator
ic = ic or {}
function ic.fi(table)
return table[1]
end
function ic.transactions()
return query [[from index.tag "table" where page == editor.getCurrentPage() and include == "x"]]
end
function ic.totalCosts()
local total_costs = 0
local transactions = ic.transactions()
local costs = query [[from transactions where type == "mc" or type == "oc"]]
-- credit card row
local cc = ic.fi(query [[from transactions where role == "cc"]])
-- rows that are subtracted from the credit card
local cc_costs = query [[from costs where role == "oncc"]]
for cc_t in cc_costs do
-- don't let credit card balance go below 0
cc.value = math.max(cc.value - cc_t.value, 0)
end
total_costs = total_costs + cc.value
for t in costs do
total_costs = total_costs + tonumber(t.value)
end
return total_costs
end
function ic.totalIncome()
local total_income = 0
local transactions = ic.transactions()
local incomes = query [[from transactions where type == "mi" or type == "oi"]]
for t in incomes do
total_income = total_income + tonumber(t.value)
end
return total_income
end
function ic.disposableIncome()
return ic.totalIncome() - ic.totalCosts()
end
I hope I understood your requirements correctly ;D
Based on your work (thanks again) I was able to create a working replacement for my sheet, including a working creditcard calculation. My code for everyone to enjoy:
-- (disposable) income calculator
ic = ic or {}
function ic.fi(table)
return table[1]
end
function ic.transactions()
return query [[from index.tag "table" where page == editor.getCurrentPage() and include == "x"]]
end
-- Function that calculates total costs
function ic.totalCosts()
local total_costs = 0
local transactions = ic.transactions()
local costs = query [[from transactions where type == "oc"]]
for t in costs do
total_costs = total_costs + tonumber(t.value)
end
total_costs = total_costs + ic.monthlyCosts() + ic.ccCosts()
return tonumber(string.format("%.2f", total_costs))
end
-- Function that calculates total income
function ic.totalIncome()
local total_income = 0
local transactions = ic.transactions()
local incomes = query [[from transactions where type == "mi" or type == "oi"]]
for t in incomes do
total_income = total_income + tonumber(t.value)
end
return tonumber(string.format("%.2f", total_income))
end
-- Function that calculates total costs
function ic.monthlyCosts()
local monthly_costs = 0
local transactions = ic.transactions()
for t in transactions do
if t.type and string.find(t.type, "mc") then
monthly_costs = monthly_costs + tonumber(t.value)
end
end
return tonumber(string.format("%.2f", monthly_costs))
end
function ic.monthbudgetIncome()
local monthbudget_income = ic.totalIncome() - ic.monthlyCosts() -ic.ccCosts()
return tonumber(string.format("%.2f", monthbudget_income))
end
-- Function that calculates (one-off) credit card costs
function ic.ccCosts()
local current_cc_costs = 0
local monthly_cc_costs = 0
local transactions = ic.transactions()
local cc_current = query [[from transactions where type == "oc-cc"]]
for t in cc_current do
current_cc_costs = current_cc_costs + tonumber(t.value)
end
local cc_monthly = query [[from transactions where type == "mc-cc"]]
for t in cc_monthly do
monthly_cc_costs = monthly_cc_costs + tonumber(t.value)
end
local cc_extra = math.max(current_cc_costs - monthly_cc_costs, 0)
return tonumber(string.format("%.2f", cc_extra))
end
-- Function that calculates disposable income
function ic.disposableIncome()
local disp_income = ic.totalIncome() - ic.totalCosts()
return tonumber(string.format("%.2f", disp_income))
end
