Build a Budget Tracker

// lib/budget-setup.ts
import { createWorkbook, type Workbook } from "@mog-sdk/node";

export interface BudgetCategory {
  name: string;
  budget: number;
}

const DEFAULT_CATEGORIES: BudgetCategory[] = [
  { name: "Housing", budget: 1500 },
  { name: "Food & Groceries", budget: 600 },
  { name: "Transportation", budget: 300 },
  { name: "Utilities", budget: 200 },
  { name: "Entertainment", budget: 150 },
  { name: "Healthcare", budget: 100 },
  { name: "Savings", budget: 500 },
];

export async function createBudgetWorkbook(
  categories = DEFAULT_CATEGORIES
): Promise<Workbook> {
  const wb = await createWorkbook();
  const ws = wb.getActiveSheet();
  ws.name = "Budget";

  // Headers
  const headers = ["Category", "Budget", "Actual", "Variance", "Status"];
  for (let col = 0; col < headers.length; col++) {
    await ws.setCellByIndex(0, col, headers[col]);
  }

  // Data rows with formulas
  for (let i = 0; i < categories.length; i++) {
    const row = i + 2; // 1-indexed, skip header
    await ws.setCell(`A${row}`, categories[i].name);
    await ws.setCell(`B${row}`, categories[i].budget);
    await ws.setCell(`C${row}`, 0); // Actual — user fills this in
    await ws.setCell(`D${row}`, `=B${row}-C${row}`);
    await ws.setCell(`E${row}`, `=IF(D${row}<0,"Over Budget","On Track")`);
  }

  // Totals row
  const lastRow = categories.length + 1;
  const totalRow = lastRow + 1;
  await ws.setCell(`A${totalRow}`, "TOTAL");
  await ws.setCell(`B${totalRow}`, `=SUM(B2:B${lastRow})`);
  await ws.setCell(`C${totalRow}`, `=SUM(C2:C${lastRow})`);
  await ws.setCell(`D${totalRow}`, `=SUM(D2:D${lastRow})`);
  await ws.setCell(
    `E${totalRow}`,
    `=IF(D${totalRow}<0,"Over Budget","On Track")`
  );

  await wb.calculate();
  return wb;
}

// lib/budget-formatting.ts
import type { Workbook } from "@mog-sdk/node";

export async function applyBudgetFormatting(wb: Workbook) {
  const ws = wb.getActiveSheet();
  const lastDataRow = (await ws.getUsedRange()).length;

  // Red background for negative variance (over budget)
  await ws.addConditionalFormat({
    range: `D2:D${lastDataRow}`,
    type: "cellValue",
    operator: "lessThan",
    value: 0,
    style: {
      background: "#fecaca",
      color: "#991b1b",
      bold: true,
    },
  });

  // Green background for positive variance (on track)
  await ws.addConditionalFormat({
    range: `D2:D${lastDataRow}`,
    type: "cellValue",
    operator: "greaterThanOrEqual",
    value: 0,
    style: {
      background: "#bbf7d0",
      color: "#166534",
    },
  });

  // Bold the header row
  await ws.setRowStyle(0, {
    bold: true,
    background: "#1e293b",
    color: "#f8fafc",
  });

  // Bold the totals row
  await ws.setRowStyle(lastDataRow - 1, { bold: true });
}

// lib/budget-chart.ts
import type { Workbook } from "@mog-sdk/node";

export async function addBudgetChart(wb: Workbook) {
  const ws = wb.getActiveSheet();
  const categoryCount = (await ws.getUsedRange()).length - 2; // minus header and total

  const chart = await ws.addChart({
    type: "bar",
    title: "Budget vs. Actual",
    position: { sheet: ws.name, cell: "G2", width: 500, height: 350 },
    series: [
      {
        name: "Budget",
        categories: `A2:A${categoryCount + 1}`,
        values: `B2:B${categoryCount + 1}`,
        color: "#6366f1",
      },
      {
        name: "Actual",
        categories: `A2:A${categoryCount + 1}`,
        values: `C2:C${categoryCount + 1}`,
        color: "#f97316",
      },
    ],
    legend: { position: "bottom" },
  });

  return chart;
}

"use client";

import { MogSheet } from "@mog-sdk/embed/react";
import type { CellChangeEvent } from "@mog-sdk/embed";
import { useCallback, useRef, useState } from "react";

export default function BudgetTracker() {
  const sheetRef = useRef<any>(null);
  const [totalVariance, setTotalVariance] = useState<number | null>(null);

  const handleReady = useCallback(async (api: any) => {
    sheetRef.current = api;

    // Read the initial total variance
    const variance = await api.getCellValue("D9");
    setTotalVariance(variance as number);
  }, []);

  const handleCellChange = useCallback(async (e: CellChangeEvent) => {
    // When actual spend changes, re-read the total variance
    if (e.ref.startsWith("C")) {
      const api = sheetRef.current;
      if (api) {
        const variance = await api.getCellValue("D9");
        setTotalVariance(variance as number);
      }
    }
  }, []);

  const handleExport = useCallback(async () => {
    const api = sheetRef.current;
    if (!api) return;

    const blob = await api.exportXlsx();
    const url = URL.createObjectURL(blob);
    const a = document.createElement("a");
    a.href = url;
    a.download = "budget-tracker.xlsx";
    a.click();
    URL.revokeObjectURL(url);
  }, []);

  return (
    <div className="mx-auto max-w-5xl p-8">
      <div className="mb-6 flex items-center justify-between">
        <div>
          <h1 className="text-2xl font-bold">Budget Tracker</h1>
          {totalVariance !== null && (
            <p
              className={`mt-1 text-sm font-medium ${
                totalVariance >= 0 ? "text-green-600" : "text-red-600"
              }`}
            >
              Total variance: ${totalVariance >= 0 ? "+" : ""}
              {totalVariance.toLocaleString()}
            </p>
          )}
        </div>
        <button
          onClick={handleExport}
          className="rounded-lg bg-indigo-600 px-4 py-2 text-sm font-medium text-white hover:bg-indigo-500"
        >
          Export XLSX
        </button>
      </div>

      <MogSheet
        width={1100}
        height={500}
        onReady={handleReady}
        onCellChange={handleCellChange}
        toolbar={{
          show: true,
          items: ["bold", "italic", "separator", "align", "separator", "undo", "redo"],
        }}
        features={{
          formulaBar: true,
          sheetTabs: false,
          contextMenu: true,
        }}
      />
    </div>
  );
}