Easy home budget forecaster

forecaster.sql

--
-- PostgreSQL database dump
--

--
-- TOC entry 185 (class 1255 OID 5154394)
-- Name: in_month(date, date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION in_month(d date, month_start date) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT d >= month_start AND d < month_start + '1 month'::interval
$$;


--
-- TOC entry 201 (class 1255 OID 5154393)
-- Name: next_month(date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION next_month(d date) RETURNS date
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT (d + '1 month'::interval)::date
$$;


--
-- TOC entry 186 (class 1255 OID 5154395)
-- Name: normal_month(date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION normal_month(d date) RETURNS money
    LANGUAGE sql STABLE
    AS $$
SELECT COALESCE(SUM(amount), 0::money)
	FROM normal_month
	WHERE first_month <= next_month(d)
		AND (last_month IS NULL OR last_month >= next_month(d))
$$;


--
-- TOC entry 187 (class 1255 OID 5154397)
-- Name: specific_expenses(date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION specific_expenses(d date) RETURNS money
    LANGUAGE sql STABLE
    AS $$
SELECT COALESCE(SUM(COALESCE(actual, expected)), 0::money)
	FROM incidentals
	WHERE in_month(effective, next_month(d))
		AND COALESCE(actual, expected) < 0::money
$$;


--
-- TOC entry 188 (class 1255 OID 5154396)
-- Name: specific_income(date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION specific_income(d date) RETURNS money
    LANGUAGE sql STABLE
    AS $$
SELECT COALESCE(SUM(COALESCE(actual, expected)), 0::money)
	FROM incidentals
	WHERE in_month(effective, next_month(d))
		AND COALESCE(actual, expected) > 0::money
$$;


SET default_with_oids = false;

--
-- TOC entry 183 (class 1259 OID 5154293)
-- Name: actual_balances; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE actual_balances (
    effective date NOT NULL,
    amount money,
    notes text
);


--
-- TOC entry 182 (class 1259 OID 5154280)
-- Name: incidentals; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE incidentals (
    name text NOT NULL,
    expected money,
    actual money,
    effective date,
    notes text
);


--
-- TOC entry 2166 (class 0 OID 0)
-- Dependencies: 182
-- Name: TABLE incidentals; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE incidentals IS 'Income is positive, expenses are negative.';


--
-- TOC entry 181 (class 1259 OID 5154277)
-- Name: normal_month; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE normal_month (
    name text NOT NULL,
    amount money,
    first_month date,
    last_month date,
    notes text
);


--
-- TOC entry 2167 (class 0 OID 0)
-- Dependencies: 181
-- Name: TABLE normal_month; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE normal_month IS 'Income is positive, expenses are negative.';


--
-- TOC entry 184 (class 1259 OID 5154398)
-- Name: summary; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW summary AS
 WITH RECURSIVE months(d) AS (
         SELECT actual_balances.effective,
            (0)::money AS "Normal Monthly Delta",
            (0)::money AS "Specific Income",
            (0)::money AS "Specific Expenses",
            actual_balances.amount AS "Expected Balance",
            actual_balances.amount AS "Actual Balance",
            actual_balances.notes AS "Notes"
           FROM actual_balances
          WHERE (actual_balances.effective = ( SELECT min(actual_balances_1.effective) AS min
                   FROM actual_balances actual_balances_1))
        UNION ALL
         SELECT next_month(months_1.d) AS next_month,
            normal_month(months_1.d) AS normal_month,
            specific_income(months_1.d) AS specific_income,
            specific_expenses(months_1.d) AS specific_expenses,
            (((COALESCE(months_1."Actual Balance", months_1."Expected Balance") + normal_month(months_1.d)) + specific_income(months_1.d)) + specific_expenses(months_1.d)),
            ( SELECT actual_balances.amount
                   FROM actual_balances
                  WHERE (actual_balances.effective = ( SELECT max(actual_balances_1.effective) AS max
                           FROM actual_balances actual_balances_1
                          WHERE in_month(actual_balances_1.effective, next_month(months_1.d))))) AS amount,
            ( SELECT actual_balances.notes
                   FROM actual_balances
                  WHERE in_month(actual_balances.effective, next_month(months_1.d))) AS notes
           FROM months months_1
        )
 SELECT months.d,
    months."Normal Monthly Delta",
    months."Specific Income",
    months."Specific Expenses",
    months."Expected Balance",
    months."Actual Balance",
    months."Notes"
   FROM months
 LIMIT 30;


--
-- TOC entry 2042 (class 2606 OID 5154297)
-- Name: actual_balances_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY actual_balances
    ADD CONSTRAINT actual_balances_pkey PRIMARY KEY (effective);


--
-- TOC entry 2040 (class 2606 OID 5154292)
-- Name: incidentals_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY incidentals
    ADD CONSTRAINT incidentals_pkey PRIMARY KEY (name);


--
-- TOC entry 2038 (class 2606 OID 5154287)
-- Name: normal_month_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY normal_month
    ADD CONSTRAINT normal_month_pkey PRIMARY KEY (name);


--
-- PostgreSQL database dump complete
--

For those of you who prefer working with SQL over spreadsheets and want a simple, quick local home budgeting solution.

Put in your regular monthly income and expenses in normal_month, and any upcoming one-time transactions in incidentals. Whenever you check your bank balance, throw that in actual_balances.

Look at the summary view to see your monthly forecast up to 30 months after your earliest balance.