Net worth by account type

[Aaron Axvig]

I would like to see (fava graph, ideally) the evolution of my asset allocations over time. Quicken had a Net Worth by Account Type report that did this pretty nicely but I am unable to find a screenshot to link to. It is easy to describe:

Time running across the X axis. Y is $. There is a bar for each time period, let's say each year to start with. The overall height of the bar shows the total value of all Assets. The bar has segments colored according the Assets subaccounts--Assets:Investing, Assets:Real-Estate, Assets:Banking, Assets:Retirement, etc. It could be a stacked area graph instead which would be functionally the same.

Stretch goals would be to have Liabilities extending down from the X axis (since they are drags on the net worth). And overlaid across the top (typically above the X axis if you have positive net worth) would be a line tracing out the combined Assets-Liabilities, which would of course be the actual Net Worth value.

The Balance Sheet -> Net Worth page in fava has potential but doesn't break down by Asset subaccounts. Balance Sheet -> Assets does break down by subaccount but doesn't have a time component.

Using a query of balances has been the

closest path but still far off.

SELECT

year,

month,

root(account, 2) as Category,

convert(last(balance), 'USD', LAST(date)) as Market_Value

WHERE

root(account, 2) IN (

'Assets:Retirement'

)

AND date <= 2026-02-22

GROUP BY 1,2,3

If I got this query working perfectly I would turn it into a simple fava extension I think.

Any advice of where to spend my efforts?

[Aaron Axvig]

> --
> You received this message because you are subscribed to the Google
> Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to beancount+...@googlegroups.com.
> To view this discussion visit
> https://groups.google.com/d/msgid/beancount/c40488c961687fba9151953f85d605d3e814b80d.camel%40axvig.com
> [1].
>
>
> Links:
> ------
> [1]
> https://groups.google.com/d/msgid/beancount/c40488c961687fba9151953f85d605d3e814b80d.camel%40axvig.com?utm_medium=email&utm_source=footer

Aha! The report "Net Worth by Assets and Liabilities" is the more
common variation in Quicken, and an image is available on this page:
https://www.quicken.com/blog/how-calculate-your-net-worth/

[Vasily M]

Hi Aaron,

I've just recently updated one of the dashboards to do that:

https://github.com/Evernight/lazy-beancount/blob/main/example_data/dashboards.tsx#L1519

You can either try it out with https://github.com/Evernight/lazy-beancount/, or just grab that config file and use with recent version of https://github.com/andreasgerstmayr/fava-dashboards/ Fava plugin.

There is a caveat though. In order for this to work you'll also need to add this to your ledger (https://github.com/Evernight/lazy-beancount/blob/main/example_data/regular_postings_fix.bean) together with the beancount_interpolate.recur plugin: 

2023-12-03 A "Regular transaction for summaries" #auxiliary
    recur: " / 6 days"
  Equity:RegularTransacionForSummariesFrom 0 USD
  Equity:RegularTransacionForSummariesTo 0 USD

This is needed because beanquery iterates over postings in transactions but we need more data points than that for the historical net worth graph. This is the simplest workaround I found so far. Once beanquery supports JOIN operations or something else appears similar to that, it may become not required.

[Vasily M]

Forgot to attach a screenshot

[Aaron Axvig]

Very nice.  Yours performs pretty good.  I get waves/spikes because I don't have the "regularly occurring transactions" as mentioned in the note.

I worked on this a bit later that day.  I started with the Asset Classes Year-over-Year panel from here.  An LLM was proficient at changing it around (compared to me, who would have never figured it out).

In monthly mode for 15 years of data and 18k transactions mine takes about 20 seconds to render the whole deal.  I am looping through every year (or month) and running a query for each one.  Not sure if the CLOSE ON strategy in that query is the best one but seems to give me results that make sense.

Bar mode:

Area mode:

Probably sketchy code:

{

name: "Net worth",

variables: [currencyVariable],

panels:

[

{

title: "Net worth by assets and liabilities",

width: "100%",

height: "600px",

variables: [

{

name: "display",

label: "Display",

display: "toggle",

options: () => ["bars", "area"],

},

{

name: "granularity",

label: "Granularity",

display: "toggle",

options: () => ["yearly", "monthly"],

},

],

kind: "echarts",

spec: async ({ ledger, variables }: SpecParams): Promise<EChartsSpec> => {

const currencyFormatter = getCurrencyFormatter(variables.currency);

const isMonthly = variables.granularity === "monthly";

const isArea = variables.display === "area";

// Build list of periods with their snapshot dates.

// One query per period is needed because `balance` doesn't support GROUP BY.

const periods: { key: string; closeOn: string; priceDate: string }[] = isMonthly

? iterateMonths(ledger.dateFirst, ledger.dateLast).map(({ year, month }) => {

const ny = month === 12 ? year + 1 : year;

const nm = month === 12 ? 1 : month + 1;

const closeOn = `${ny}-${String(nm).padStart(2, "0")}-01`;

return { key: `${year}-${String(month).padStart(2, "0")}`, closeOn, priceDate: closeOn };

})

: iterateYears(ledger.dateFirst, ledger.dateLast).map((year) => ({

key: String(year),

closeOn: `${year + 1}-01-01`,

priceDate: `${year}-12-31`,

}));

const queries = await Promise.all(

periods.map(({ closeOn, priceDate }) =>

ledger.query(`SELECT account,

CONVERT(SUM(position), '${variables.currency}', ${priceDate}) as market_value

FROM CLOSE ON ${closeOn}

WHERE account ~ '^(Assets|Liabilities):'

GROUP BY account`),

),

);

const amounts: Record<string, Record<string, number>> = {};

const balances: Record<string, number> = {};

for (let i = 0; i < periods.length; i++) {

const key = periods[i].key;

amounts[key] = {};

for (const row of queries[i]) {

if (!row.market_value[variables.currency]) continue;

const value = row.market_value[variables.currency];

const parts = (row.account as string).split(":");

const subAccount = parts.slice(0, 2).join(":");

amounts[key][subAccount] = (amounts[key][subAccount] ?? 0) + value;

balances[subAccount] = (balances[subAccount] ?? 0) + value;

}

}

// Sort descending: positive asset accounts first, negative liability accounts last

const assetAccounts = Object.entries(balances)

.filter(([name]) => name.startsWith("Assets:"))

.sort(([, a], [, b]) => b - a)

.map(([name]) => name);

const liabilityAccounts = Object.entries(balances)

.filter(([name]) => name.startsWith("Liabilities:"))

.sort(([, a], [, b]) => a - b)

.map(([name]) => name);

const subAccounts = [...assetAccounts, ...liabilityAccounts];

// Assign palette colors so assets take from the front and liabilities from

// the back — guaranteeing the first asset and first liability never share a color.

const PALETTE = ["#5470c6", "#91cc75", "#fac858", "#ee6666", "#73c0de", "#3ba272", "#fc8452", "#9a60b4", "#ea7ccc"];

const colorOf = (subAccount: string) => {

if (subAccount.startsWith("Assets:")) {

return PALETTE[assetAccounts.indexOf(subAccount) % PALETTE.length];

} else {

return PALETTE[(PALETTE.length - 1 - liabilityAccounts.indexOf(subAccount) % PALETTE.length + PALETTE.length) % PALETTE.length];

}

};

const periodKeys = periods.map((p) => p.key);

// In area mode, clamp values to their expected sign to prevent visual chaos

// when an account briefly crosses zero.

const areaValue = (subAccount: string, raw: number) => {

if (!isArea) return raw;

return subAccount.startsWith("Assets:") ? Math.max(0, raw) : Math.min(0, raw);

};

return {

tooltip: {

trigger: "axis",

axisPointer: { type: isArea ? "line" : "shadow" },

formatter: (paramsList: any) => {

const lines = paramsList.map((params: any) =>

`${params.marker} ${params.seriesName}: <b>${currencyFormatter(params.seriesName === "Net Worth" ? params.value : params.value)}</b>`,

);

return paramsList[0]?.name + "<br/>" + lines.join("<br/>");

},

},

legend: {

bottom: 0,

},

xAxis: {

data: periodKeys,

axisLabel: {

rotate: isMonthly ? 45 : 0,

},

},

yAxis: {

axisLabel: {

formatter: (v: number) => currencyFormatter(v),

},

},

series: [

...subAccounts.map((subAccount) => {

const isLiability = subAccount.startsWith("Liabilities:");

// In area mode use separate stacks so assets build up from 0 and

// liabilities build down from 0 independently — prevents clamped

// liability series from riding on top of the asset area.

const stack = isArea ? (isLiability ? "liabilities" : "assets") : "total";

return isArea

? {

type: "line" as const,

name: subAccount,

stack,

smooth: false,

symbol: "none",

color: colorOf(subAccount),

areaStyle: {},

data: periodKeys.map((key) => areaValue(subAccount, amounts[key][subAccount] ?? 0)),

}

: ({

type: "bar",

name: subAccount,

stack,

color: colorOf(subAccount),

data: periodKeys.map((key) => amounts[key][subAccount] ?? 0),

} as BarSeriesOption);

}),

{

type: "line",

name: "Net Worth",

z: 10,

// In monthly mode don't show dots or labels — too many data points

symbol: isArea && !isMonthly ? "circle" : "none",

symbolSize: isArea && !isMonthly ? 8 : 0,

lineStyle: { width: isArea ? 3 : 2, color: "#3daf46" },

itemStyle: { color: "#3daf46", borderColor: "#fff", borderWidth: isArea && !isMonthly ? 2 : 0 },

label: isArea && !isMonthly ? { show: true, formatter: (p: any) => currencyFormatter(p.value), color: "#3daf46", fontWeight: "bold", textBorderColor: "#fff", textBorderWidth: 2 } : { show: false },

data: periodKeys.map((key) => Object.values(amounts[key] ?? {}).reduce((s, v) => s + v, 0)),

},

],

};

},

}

]

},

To view this discussion visit https://groups.google.com/d/msgid/beancount/d8ec2bc6-f8b3-4bd2-b17a-244d8126ef97n%40googlegroups.com.