dashboards.yaml

dashboards:
- name: Overview
  panels:
  - title: Assets 💰
    width: 50%
    height: 80px
    link: /beancount/balance_sheet/
    queries:
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Assets:'
    type: html
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const value = panel.queries[0].result[0]?.value[ledger.ccy];
      const valueFmt = currencyFormatter(value ?? 0);
      return `<div style="font-size: 40px; font-weight: bold; color: #3daf46; text-align: center;">${valueFmt}</div>`;

  - title: Liabilities 💳
    width: 50%
    height: 80px
    link: /beancount/balance_sheet/
    queries:
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Liabilities:'
    type: html
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const value = panel.queries[0].result[0]?.value[ledger.ccy];
      const valueFmt = currencyFormatter(value ? -value : 0);
      return `<div style="font-size: 40px; font-weight: bold; color: #af3d3d; text-align: center;">${valueFmt}</div>`;

  - title: Income/Expenses 💸
    height: 520px
    link: /beancount/income_statement/
    queries:
    - name: Income
      stack: income
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Income:'
        GROUP BY year, month
      link: /beancount/account/Income/?time={time}
    - name: Housing
      stack: expenses
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:Housing:' AND NOT 'travel' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses:Housing/?filter=-#travel&time={time}
    - name: Food
      stack: expenses
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:Food:' AND NOT 'travel' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses:Food/?filter=-#travel&time={time}
    - name: Shopping
      stack: expenses
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:Shopping:' AND NOT 'travel' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses:Shopping/?filter=-#travel&time={time}
    - name: Travel
      stack: expenses
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND 'travel' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses/?filter=#travel&time={time}
    - name: Other
      stack: expenses
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND NOT account ~ '^Expenses:(Housing|Food|Shopping):' AND NOT 'travel' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses/?filter=all(-account:"^Expenses:(Housing|Food|Shopping)") -#travel&time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      const amounts = {};
      for (let query of panel.queries) {
        amounts[query.name] = {};
        for (let row of query.result) {
          amounts[query.name][`${row.month}/${row.year}`] =
            query.stack == "income" ? -row.value[ledger.ccy] : row.value[ledger.ccy];
        }
      }

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: panel.queries.map((query) => ({
          type: "bar",
          name: query.name,
          stack: query.stack,
          data: months.map((month) => amounts[query.name][month] ?? 0),
        })),
        onClick: (event) => {
          const query = panel.queries.find((q) => q.name === event.seriesName);
          if (query) {
            const [month, year] = event.name.split("/");
            const link = query.link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
            window.open(helpers.urlFor(link));
          }
        },
      };

- name: Assets
  panels:
  - title: Assets 🏦
    width: 50%
    queries:
    - bql: |
        SELECT currency, CONVERT(SUM(position), '{{ledger.ccy}}') as market_value
        WHERE account_sortkey(account) ~ '^[01]'
        GROUP BY currency
        ORDER BY market_value
      link: /beancount/account/{account}/?time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);

      const data = panel.queries[0].result
        .filter((row) => row.market_value[ledger.ccy])
        .map((row) => ({ name: row.currency, value: row.market_value[ledger.ccy] }));

      return {
        tooltip: {
          formatter: (params) =>
            `${params.marker} ${
              ledger.commodities[params.name]?.meta.name ?? params.name
            } <span style="padding-left: 15px; font-weight: bold;">${currencyFormatter(
              params.value,
            )}</span> (${params.percent.toFixed(0)}%)`,
        },
        series: [
          {
            type: "pie",
            data,
          },
        ],
      };

  - title: Net Worth 💰
    width: 50%
    link: /beancount/income_statement/
    queries:
    # Workaround for getting the last day of a given month:
    # YMONTH(date) returns a date instance of 1st of the given month and year
    # adding 31 days gives us the next month (unless it's e.g. Jan 31... this case should be avoided by using FIRST(date))
    # YMONTH() gives 1st of the (next) month
    # minus 1 day gives us the last day of the month
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account_sortkey(account) ~ '^[01]'
        GROUP BY year, month
      link: /beancount/balance_sheet/?time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);
      const amounts = {};

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      for (let row of panel.queries[0].result) {
        amounts[`${row.month}/${row.year}`] = row.value[ledger.ccy];
      }

      return {
        tooltip: {
          trigger: "axis",
          valueFormatter: currencyFormatter,
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            smooth: true,
            connectNulls: true,
            data: months.map((month) => amounts[month]),
          },
        ],
        onClick: (event) => {
          const [month, year] = event.name.split("/");
          const link = panel.queries[0].link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Portfolio 📈
    width: 50%
    queries:
    - bql: &portfolio_bql |
        SELECT year, month,
        CONVERT(LAST(balance),       '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS market_value,
        CONVERT(COST(LAST(balance)), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS book_value
        WHERE account ~ '^Assets:' AND currency != '{{ledger.ccy}}'
        GROUP BY year, month
      link: /beancount/balance_sheet/?time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);
      const amounts = {};

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      for (let row of panel.queries[0].result) {
        amounts[`${row.month}/${row.year}`] = {
          market_value: row.market_value[ledger.ccy],
          book_value: row.book_value[ledger.ccy],
        };
      }

      return {
        tooltip: {
          trigger: "axis",
          valueFormatter: currencyFormatter,
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            name: "Market Value",
            smooth: true,
            connectNulls: true,
            data: months.map((month) => amounts[month]?.market_value),
          },
          {
            type: "line",
            name: "Book Value",
            smooth: true,
            connectNulls: true,
            data: months.map((month) => amounts[month]?.book_value),
          },
        ],
        onClick: (event) => {
          const [month, year] = event.name.split("/");
          const link = panel.queries[0].link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Portfolio Gains ✨
    width: 50%
    queries:
    - bql: *portfolio_bql
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);
      const amounts = {};

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      for (let row of panel.queries[0].result) {
        amounts[`${row.month}/${row.year}`] = row.market_value[ledger.ccy] - row.book_value[ledger.ccy];
      }

      return {
        tooltip: {
          trigger: "axis",
          valueFormatter: currencyFormatter,
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            smooth: true,
            connectNulls: true,
            data: months.map((month) => amounts[month]),
          },
        ],
      };

  - title: Asset Classes Year-over-Year 🏦
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const years = utils.iterateYears(ledger.dateFirst, ledger.dateLast);

      // This chart requires the balances grouped by year and currency.
      // Unfortunately the `balance` column does not support GROUP BY
      // (see https://groups.google.com/g/beancount/c/TfZJswxuIDA/m/psc2BkrBAAAJ)
      // therefore we need to run a separate query per year.
      const filterParams = Object.fromEntries(new URL(window.location.href).searchParams);
      const queries = await Promise.all(
        years.map((year) =>
          ext.api.get("query", {
            bql: `SELECT currency,
                  CONVERT(SUM(position), '${ledger.ccy}') as market_value
                  FROM CLOSE ON ${year + 1}-01-01
                  WHERE account_sortkey(account) ~ '^[01]'
                  GROUP BY currency`,
            ...filterParams,
          }),
        ),
      );

      const amounts = {};
      const balances = {};
      for (let i = 0; i < years.length; i++) {
        const year = years[i];
        const query = queries[i];

        amounts[year] = {};
        for (let row of query.data.result) {
          if (!row.market_value[ledger.ccy]) continue;

          const value = row.market_value[ledger.ccy];
          const assetClass = ledger.commodities[row.currency]?.meta.asset_class ?? "unknown";
          amounts[year][assetClass] = (amounts[year][assetClass] ?? 0) + value;
          balances[assetClass] = (balances[assetClass] ?? 0) + value;
        }
      }

      const assetClasses = Object.entries(balances)
        .sort(([, a], [, b]) => b - a)
        .map(([name]) => name);

      return {
        tooltip: {
          formatter: (params) => {
            const sum = Object.values(amounts[params.name]).reduce((prev, cur) => prev + cur, 0);
            return `${params.marker} ${params.seriesName} <span style="padding-left: 15px; font-weight: bold;">${currencyFormatter(
              params.value,
            )}</span> (${((params.value / sum) * 100).toFixed(0)}%)`;
          },
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          data: years,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: assetClasses.map((assetClass) => ({
          type: "bar",
          name: assetClass,
          stack: "assets",
          data: years.map((year) => amounts[year][assetClass] ?? 0),
        })),
      };

  - title: Asset Classes 🏦
    width: 50%
    queries:
    - bql: &assets_bql |
        SELECT currency, CONVERT(SUM(position), '{{ledger.ccy}}') as market_value
        WHERE account_sortkey(account) ~ '^[01]'
        GROUP BY currency
        ORDER BY market_value
    type: echarts
    script: &asset_classes |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);

      let totalValue = 0;
      const assetClasses = {};
      for (let row of panel.queries[0].result) {
        if (!row.market_value[ledger.ccy]) continue;

        const ccy = row.currency;
        const value = row.market_value[ledger.ccy];
        const assetName = ledger.commodities[ccy]?.meta.name ?? ccy;
        const assetClass = ledger.commodities[ccy]?.meta.asset_class ?? "unknown";
        if (!(assetClass in assetClasses)) {
          assetClasses[assetClass] = { name: assetClass, children: [] };
        }
        assetClasses[assetClass].children.push({ name: assetName, value });
        totalValue += value;
      }

      return {
        tooltip: {
          formatter: (params) =>
            `${params.marker} ${params.name} <span style="padding-left: 15px; font-weight: bold;">${currencyFormatter(
              params.value,
            )}</span> (${((params.value / totalValue) * 100).toFixed(0)}%)`,
        },
        series: [
          {
            type: "sunburst",
            radius: "100%",
            label: {
              minAngle: 3,
              width: 170,
              overflow: "truncate",
            },
            labelLayout: {
              hideOverlap: true,
            },
            data: Object.values(assetClasses),
          },
        ],
      };

  - title: Investment Classes 🏦
    width: 50%
    queries:
    - bql: &investments_bql |
        SELECT currency, CONVERT(SUM(position), '{{ledger.ccy}}') as market_value
        WHERE account_sortkey(account) ~ '^[01]' AND currency != '{{ledger.ccy}}'
        GROUP BY currency
        ORDER BY market_value
    type: echarts
    script: *asset_classes

  - title: Assets Allocation 🏦
    width: 50%
    queries:
    - bql: *assets_bql
    type: echarts
    script: &assets_allocation |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);

      let totalValue = 0;
      const root = { children: [] };
      for (let row of panel.queries[0].result) {
        if (!row.market_value[ledger.ccy]) continue;

        const allocations = Object.entries(ledger.commodities[row.currency]?.meta ?? {}).filter(([k, v]) =>
          k.startsWith("asset_allocation_"),
        );
        if (allocations.length === 0) {
          allocations.push(["asset_allocation_Unknown", 100]);
        }

        for (let [allocation, percentage] of allocations) {
          const parts = allocation.substr("asset_allocation_".length).split("_");
          let node = root;
          for (const part of parts) {
            let child = node.children.find((c) => c.name == part);
            if (!child) {
              child = { name: part, children: [] };
              node.children.push(child);
            }
            node = child;
          }

          const value = (percentage / 100) * row.market_value[ledger.ccy];
          node.value = (node.value ?? 0) + value;
          totalValue += value;
        }
      }

      return {
        tooltip: {
          formatter: (params) =>
            `${params.marker} ${params.name} <span style="padding-left: 15px; font-weight: bold;">${currencyFormatter(
              params.value,
            )}</span> (${((params.value / totalValue) * 100).toFixed(0)}%)`,
        },
        series: [
          {
            type: "sunburst",
            radius: "100%",
            label: {
              rotate: "tangential",
              minAngle: 20,
            },
            labelLayout: {
              hideOverlap: true,
            },
            data: root.children,
          },
        ],
      };

  - title: Investments Allocation 🏦
    width: 50%
    queries:
    - bql: *investments_bql
    type: echarts
    script: *assets_allocation

- name: Income and Expenses
  panels:
  - title: Avg. Income per Month 💰
    width: 33.3%
    height: 80px
    link: /beancount/account/Income/?r=changes
    queries:
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Income:'
    type: html
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const months = days / (365 / 12);
      const value = currencyFormatter(-panel.queries[0].result[0].value[ledger.ccy] / months);
      return `<div style="font-size: 40px; font-weight: bold; color: #3daf46; text-align: center;">${value}</div>`;

  - title: Avg. Expenses per Month 💸
    width: 33.3%
    height: 80px
    link: /beancount/account/Expenses/?r=changes
    queries:
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Expenses:'
    type: html
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const months = days / (365 / 12);
      const value = currencyFormatter(panel.queries[0].result[0].value[ledger.ccy] / months);
      return `<div style="font-size: 40px; font-weight: bold; color: #af3d3d; text-align: center;">${value}</div>`;

  - title: Avg. Savings per Month ✨
    width: 33.3%
    height: 80px
    link: /beancount/income_statement/
    queries:
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Income:'
    - bql: SELECT CONVERT(SUM(position), '{{ledger.ccy}}') AS value WHERE account ~ '^Expenses:'
    type: html
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const percentFormat = new Intl.NumberFormat(undefined, {
        style: "percent",
        maximumFractionDigits: 0,
      });
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const months = days / (365 / 12);
      const income = -panel.queries[0].result[0].value[ledger.ccy];
      const expenses = panel.queries[1].result[0].value[ledger.ccy];
      const rate = (income - expenses) / months;
      const ratePercent = 1 - expenses / income;
      const value = `${currencyFormatter(rate)} (${percentFormat.format(ratePercent)})`;
      return `<div style="font-size: 40px; font-weight: bold; color: #3daf46; text-align: center;">${value}</div>`;

  - title: Income Categories (per month) 💸
    width: 50%
    link: /beancount/account/Income/?r=changes
    queries:
    - bql: |
        SELECT root(account, 4) AS account, CONVERT(SUM(position), '{{ledger.ccy}}') AS value
        WHERE account ~ '^Income:'
        GROUP BY account
      link: /beancount/account/{account}/?r=changes
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const divisor = days / (365 / 12);
      const accountTree = utils.buildAccountTree(
        panel.queries[0].result,
        (row) => -row.value[ledger.ccy] / divisor,
        (parts, i) => parts[i],
      );
      // use click event on desktop, dblclick on mobile
      const clickEvt = window.screen.width < 800 ? "onDblClick" : "onClick";

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        series: [
          {
            type: "sunburst",
            radius: "100%",
            label: {
              minAngle: 20,
            },
            nodeClick: false,
            data: accountTree.children[0].children,
          },
        ],
        [clickEvt]: (event) => {
          const account = "Income" + event.treePathInfo.map((i) => i.name).join(":");
          const link = panel.queries[0].link.replace("{account}", account);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Expenses Categories (per month) 💸
    width: 50%
    link: /beancount/account/Expenses/?r=changes
    queries:
    - bql: |
        SELECT root(account, 3) AS account, CONVERT(SUM(position), '{{ledger.ccy}}') AS value
        WHERE account ~ '^Expenses:'
        GROUP BY account
      link: /beancount/account/{account}/?r=changes
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const divisor = days / (365 / 12);
      const accountTree = utils.buildAccountTree(
        panel.queries[0].result,
        (row) => row.value[ledger.ccy] / divisor,
        (parts, i) => parts[i],
      );
      // use click event on desktop, dblclick on mobile
      const clickEvt = window.screen.width < 800 ? "onDblClick" : "onClick";

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        series: [
          {
            type: "sunburst",
            radius: "100%",
            label: {
              minAngle: 20,
            },
            nodeClick: false,
            data: accountTree.children[0].children,
          },
        ],
        [clickEvt]: (event) => {
          const account = "Expenses" + event.treePathInfo.map((i) => i.name).join(":");
          const link = panel.queries[0].link.replace("{account}", account);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Recurring, Regular and Irregular Expenses 🔁
    width: 50%
    link: /beancount/income_statement/
    queries:
    - name: Recurring
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND 'recurring' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses/?filter=#recurring&time={time}
    - name: Regular
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND NOT 'recurring' IN tags AND NOT 'irregular' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses/?filter=-#recurring -#irregular&time={time}
    - name: Irregular
      bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND 'irregular' IN tags
        GROUP BY year, month
      link: /beancount/account/Expenses/?filter=#irregular&time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      const amounts = {};
      for (let query of panel.queries) {
        amounts[query.name] = {};
        for (let row of query.result) {
          amounts[query.name][`${row.month}/${row.year}`] = row.value[ledger.ccy];
        }
      }

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        yAxis: {
          data: months,
        },
        series: panel.queries.map((query) => ({
          type: "bar",
          name: query.name,
          stack: "expenses",
          data: months.map((month) => amounts[query.name][month] ?? 0),
        })),
        onClick: (event) => {
          const query = panel.queries.find((q) => q.name === event.seriesName);
          if (query) {
            const [month, year] = event.name.split("/");
            const link = query.link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
            window.open(helpers.urlFor(link));
          }
        },
      };

  - title: Food Expenses 🥐
    width: 50%
    link: /beancount/account/Expenses:Food/
    queries:
    - bql: |
        SELECT year, month, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:Food:'
        GROUP BY year, month
      link: /beancount/account/Expenses:Food/?time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const months = utils.iterateMonths(ledger.dateFirst, ledger.dateLast).map((m) => `${m.month}/${m.year}`);
      const amounts = {};

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      for (let row of panel.queries[0].result) {
        amounts[`${row.month}/${row.year}`] = row.value[ledger.ccy];
      }

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            smooth: true,
            data: months.map((month) => amounts[month] ?? 0),
          },
        ],
        onClick: (event) => {
          const [month, year] = event.name.split("/");
          const link = panel.queries[0].link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Income Year-Over-Year 💰
    width: 50%
    height: 700px
    queries:
    - bql: |
        SELECT year, root(account, 3) AS account, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ "^Income:"
        GROUP BY account, year
      link: /beancount/account/{account}/?time={time}
    type: echarts
    script: &year_over_year |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const years = utils.iterateYears(ledger.dateFirst, ledger.dateLast);
      const maxAccounts = 7; // number of accounts to show, sorted by sum

      const accountSums = {};
      const amounts = {};
      for (let row of panel.queries[0].result) {
        if (!(row.account in accountSums)) {
          accountSums[row.account] = 0;
        }
        const value = row.account.startsWith("Income:") ? -row.value[ledger.ccy] : row.value[ledger.ccy];
        amounts[`${row.year}/${row.account}`] = value;
        accountSums[row.account] += value;
      }

      const accounts = Object.entries(accountSums)
        .sort(([, a], [, b]) => b - a)
        .map(([name]) => name)
        .slice(0, maxAccounts)
        .reverse();
      return {
        legend: {
          top: "bottom",
        },
        tooltip: {
          formatter: "{a}",
        },
        xAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        yAxis: {
          data: accounts.map((account) => account.split(":").slice(1).join(":")),
        },
        grid: {
          containLabel: true,
          left: 0,
        },
        series: years.map((year) => ({
          type: "bar",
          name: year,
          data: accounts.map((account) => amounts[`${year}/${account}`] ?? 0),
          label: {
            show: true,
            position: "right",
            formatter: (params) => currencyFormatter(params.value),
          },
        })),
        onClick: (event) => {
          const link = panel.queries[0].link
            .replace("{account}", accounts[event.dataIndex])
            .replace("{time}", event.seriesName);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Expenses Year-Over-Year 💸
    width: 50%
    height: 700px
    queries:
    - bql: |
        SELECT year, root(account, 2) AS account, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ "^Expenses:"
        GROUP BY account, year
      link: /beancount/account/{account}/?time={time}
    type: echarts
    script: *year_over_year

  - title: Top 10 biggest expenses
    queries:
    - bql: SELECT date, payee, narration, position WHERE account ~ "^Expenses:" ORDER BY position DESC LIMIT 10
    type: jinja2
    template: |
      {% import "_query_table.html" as querytable %}
      {{ querytable.querytable(favaledger, None, panel.queries[0].result_types, panel.queries[0].result) }}

- name: Travelling
  panels:
  - title: Travel Costs per Year 📅
    # Note: Holidays over New Year's Eve are counted in both years aliquot.
    link: /beancount/income_statement/?filter=#travel
    queries:
    - bql: |
        SELECT year, CONVERT(SUM(position), '{{ledger.ccy}}', LAST(date)) AS value
        WHERE account ~ '^Expenses:' AND 'travel' IN tags
        GROUP BY year
      link: /beancount/account/Expenses/?filter=#travel&time={time}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const years = utils.iterateYears(ledger.dateFirst, ledger.dateLast);
      const amounts = {};

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by year
      for (let row of panel.queries[0].result) {
        amounts[row.year] = row.value[ledger.ccy];
      }

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        xAxis: {
          data: years,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            smooth: true,
            data: years.map((year) => amounts[year] ?? 0),
          },
        ],
        onClick: (event) => {
          const link = panel.queries[0].link.replace("{time}", event.name);
          window.open(helpers.urlFor(link));
        },
      };

  - title: Destinations ✈️
    height: 300px
    link: /beancount/income_statement/?filter=#travel
    queries:
    - bql: |
        SELECT tags, CONVERT(position, '{{ledger.ccy}}') AS value
        WHERE account ~ '^Expenses:' AND 'travel' IN tags
        ORDER BY date, tags DESC
      link: /beancount/account/Expenses/?filter=#{travel}
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const travels = [];
      const amounts = {};

      for (let row of panel.queries[0].result) {
        const tag = row.tags.find((tag) => tag.match(/\-\d{4}/));
        if (!(tag in amounts)) {
          travels.push(tag);
          amounts[tag] = 0;
        }
        amounts[tag] += row.value.number;
      }

      return {
        tooltip: {
          valueFormatter: currencyFormatter,
        },
        grid: {
          containLabel: true,
          left: 0,
        },
        xAxis: {
          type: "value",
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        yAxis: {
          type: "category",
          data: travels,
        },
        series: [
          {
            type: "bar",
            data: travels.map((travel) => amounts[travel]),
            label: {
              show: true,
              position: "right",
              formatter: (params) => currencyFormatter(params.value),
            },
          },
        ],
        onClick: (event) => {
          const link = panel.queries[0].link.replace("{travel}", event.name);
          window.open(helpers.urlFor(link));
        },
      };

- name: Sankey
  panels:
  - title: Sankey (per month) 💸
    height: 800px
    link: /beancount/income_statement/
    queries:
    - bql: |
        SELECT account, CONVERT(SUM(position), '{{ledger.ccy}}') AS value
        WHERE account ~ '^(Income|Expenses):'
        GROUP BY account
      link: /beancount/account/{account}/
    type: d3_sankey
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const days = (new Date(ledger.dateLast) - new Date(ledger.dateFirst)) / (1000 * 60 * 60 * 24) + 1;
      const divisor = days / (365 / 12); // monthly
      const valueThreshold = 10; // skip nodes below this value

      const nodes = [{ name: "Income" }];
      const links = [];
      function addNode(root) {
        for (let node of root.children) {
          let label = node.name.split(":").pop();

          // skip over pass-through accounts
          while (node.children.length === 1) {
            node = node.children[0];
            label += ":" + node.name.split(":").pop();
          }

          // skip nodes below the threshold
          if (Math.abs(node.value / divisor) < valueThreshold) continue;

          nodes.push({ name: node.name, label });
          if (node.name.startsWith("Income:")) {
            links.push({ source: node.name, target: root.name, value: -node.value / divisor });
          } else {
            links.push({
              source: root.name == "Expenses" ? "Income" : root.name,
              target: node.name,
              value: node.value / divisor,
            });
          }
          addNode(node);
        }
      }

      const accountTree = utils.buildAccountTree(panel.queries[0].result, (row) => row.value[ledger.ccy]);
      addNode(accountTree.children[0]);
      addNode(accountTree.children[1]);

      const savings =
        accountTree.children[0].name === "Income"
          ? -accountTree.children[0].value - accountTree.children[1].value
          : -accountTree.children[1].value - accountTree.children[0].value;
      if (savings > 0) {
        nodes.push({ name: "Savings" });
        links.push({ source: "Income", target: "Savings", value: savings / divisor });
      }

      return {
        align: "left",
        valueFormatter: currencyFormatter,
        data: {
          nodes,
          links,
        },
        onClick: (event, node) => {
          if (node.name === "Savings") return;
          const link = panel.queries[0].link.replace("{account}", node.name);
          window.open(helpers.urlFor(link));
        },
      };

- name: Projection
  panels:
  - title: Net Worth 💰
    link: /beancount/income_statement/
    queries:
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account_sortkey(account) ~ '^[01]'
        GROUP BY year, month
      link: /beancount/balance_sheet/?time={time}
    # ignore onetime income and expenses, for example winning the lottery or wedding expenses
    - bql: |
        SELECT year, month,
        CONVERT(LAST(balance), '{{ledger.ccy}}', DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS value
        WHERE account_sortkey(account) ~ '^[01]' AND NOT 'wedding' IN tags AND NOT 'weddinggift' IN tags
        GROUP BY year, month
    type: echarts
    script: |
      const currencyFormatter = utils.currencyFormatter(ledger.ccy);
      const projectYears = 2; // number of years to project

      // the beancount query only returns months where there was at least one matching transaction, therefore we group by month
      const amounts = {};
      const amountsEx = {};
      for (let row of panel.queries[0].result) {
        amounts[`${row.month}/${row.year}`] = row.value[ledger.ccy];
      }
      for (let row of panel.queries[1].result) {
        amountsEx[`${row.month}/${row.year}`] = row.value[ledger.ccy];
      }

      const results = panel.queries[0].result;
      const resultsEx = panel.queries[1].result;
      const resultsExLast = resultsEx[resultsEx.length - 1];

      const finalAmount = results[results.length - 1].value[ledger.ccy];
      const dateFirst = new Date(resultsEx[0].year, resultsEx[0].month - 1, 1);
      const dateLast = new Date(new Date(resultsExLast.year, resultsExLast.month, 1).getTime() - 1);
      const days = (dateLast - dateFirst) / (1000 * 60 * 60 * 24) + 1;
      const totalDiff = resultsExLast.value[ledger.ccy] - resultsEx[0].value[ledger.ccy];
      const monthlyDiff = (totalDiff / days) * (365 / 12);

      const dateLastYear = dateLast.getFullYear();
      const dateLastMonth = dateLast.getMonth() + 1;
      const dateFirstStr = `${dateFirst.getFullYear()}-${dateFirst.getMonth() + 1}-1`;
      const dateProjectUntilStr = `${dateLastYear + projectYears}-${dateLastMonth}-1`;
      const months = utils.iterateMonths(dateFirstStr, dateProjectUntilStr).map((m) => `${m.month}/${m.year}`);
      const lastMonthIdx = months.findIndex((m) => m === `${dateLastMonth}/${dateLastYear}`);

      const projection = [];
      let sum = finalAmount;
      for (let i = lastMonthIdx; i < months.length; i++) {
        projection[months[i]] = sum;
        sum += monthlyDiff;
      }

      return {
        tooltip: {
          trigger: "axis",
          valueFormatter: currencyFormatter,
        },
        legend: {
          top: "bottom",
        },
        xAxis: {
          data: months,
        },
        yAxis: {
          axisLabel: {
            formatter: currencyFormatter,
          },
        },
        series: [
          {
            type: "line",
            name: "Net Worth",
            smooth: true,
            connectNulls: true,
            showSymbol: false,
            data: months.map((month) => amounts[month]),
          },
          {
            type: "line",
            name: "Excluding onetime txns",
            smooth: true,
            connectNulls: true,
            showSymbol: false,
            data: months.map((month) => amountsEx[month]),
          },
          {
            type: "line",
            name: "Projection",
            lineStyle: {
              type: "dashed",
            },
            showSymbol: false,
            data: months.map((month) => projection[month]),
          },
        ],
        onClick: (event) => {
          if (event.seriesName === "Projection") return;
          const [month, year] = event.name.split("/");
          const link = panel.queries[0].link.replace("{time}", `${year}-${month.padStart(2, "0")}`);
          window.open(helpers.urlFor(link));
        },
      };

utils:
  inline: |
    function iterateMonths(dateFirst, dateLast) {
      const months = [];
      let [year, month] = dateFirst.split("-").map((x) => parseInt(x));
      let [lastYear, lastMonth] = dateLast.split("-").map((x) => parseInt(x));

      while (year < lastYear || (year === lastYear && month <= lastMonth)) {
        months.push({ year, month });
        if (month == 12) {
          year++;
          month = 1;
        } else {
          month++;
        }
      }
      return months;
    }

    function iterateYears(dateFirst, dateLast) {
      const years = [];
      let year = parseInt(dateFirst.split("-")[0]);
      let lastYear = parseInt(dateLast.split("-")[0]);

      for (; year <= lastYear; year++) {
        years.push(year);
      }
      return years;
    }

    function buildAccountTree(rows, valueFn, nameFn) {
      nameFn = nameFn ?? ((parts, i) => parts.slice(0, i + 1).join(":"));

      const accountTree = { children: [] };
      for (let row of rows) {
        const accountParts = row.account.split(":");
        let node = accountTree;
        for (let i = 0; i < accountParts.length; i++) {
          const account = nameFn(accountParts, i);
          let child = node.children.find((c) => c.name == account);
          if (!child) {
            child = { name: account, children: [], value: 0 };
            node.children.push(child);
          }

          child.value += valueFn(row);
          node = child;
        }
      }
      return accountTree;
    }

    function currencyFormatter(currency) {
      const currencyFormat = new Intl.NumberFormat(undefined, {
        style: "currency",
        currency,
        maximumFractionDigits: 0,
      });
      return (val) => (val !== undefined ? currencyFormat.format(val) : "");
    }

    return {
      iterateMonths,
      iterateYears,
      buildAccountTree,
      currencyFormatter,
    };