A local-first personal finance app
import {
ArraySize,
CellError,
EmptyValue,
ErrorType,
FunctionArgumentType,
FunctionPlugin,
SimpleRangeValue,
} from 'hyperformula';
import type { InterpreterState } from 'hyperformula/typings/interpreter/InterpreterState';
import type { ProcedureAst } from 'hyperformula/typings/parser';
import { getCurrency } from '#shared/currencies';
import type { Currency } from '#shared/currencies';
import { integerToAmount } from '#shared/util';
import type { NumberFormats } from '#shared/util';
type CurrencySymbolPosition = 'before' | 'after';
export type BudgetQueryRequest = {
dimension: string;
categoryIds: string[];
startMonth: string;
endMonth: string;
};
export type FormulaQueryContext = {
queryNames?: Set<string>;
queryCountNames?: Set<string>;
queryExtractCategoryNames?: Set<string>;
queryExtractTimeframeStartNames?: Set<string>;
queryExtractTimeframeEndNames?: Set<string>;
balanceOfNames?: Set<string>;
budgetQueryRequests?: Map<string, BudgetQueryRequest>;
querySumPrefetch?: Map<string, number>;
queryCountPrefetch?: Map<string, number>;
queryExtractCategoriesPrefetch?: Map<string, string[]>;
queryExtractTimeframeStartPrefetch?: Map<string, string>;
queryExtractTimeframeEndPrefetch?: Map<string, string>;
balanceOfPrefetch?: Map<string, number>;
budgetQueryPrefetch?: Map<string, number>;
budgetQueryErrors?: Map<string, string>;
};
type CustomFunctionsContext = {
balanceOfPrefetch?: Map<string, number>;
formulaQuery?: FormulaQueryContext;
};
export type UserPreferences = {
currency: Currency;
numberFormat: NumberFormats;
decimalPlaces: number;
thousandsSeparator: string;
decimalSeparator: string;
locale: string;
currencySymbolPosition: CurrencySymbolPosition;
currencySpaceBetweenAmountAndSymbol: boolean;
};
let cachedUserPreferences: UserPreferences | null = null;
export function setCachedUserPreferences(prefs: UserPreferences): void {
cachedUserPreferences = prefs;
}
export function clearCachedUserPreferences(): void {
cachedUserPreferences = null;
}
function getUserPreferences(): UserPreferences {
if (!cachedUserPreferences) {
// If not loaded, use defaults
return {
currency: getCurrency('USD'),
numberFormat: 'comma-dot',
decimalPlaces: 2,
thousandsSeparator: ',',
decimalSeparator: '.',
locale: 'en-US',
currencySymbolPosition: 'before',
currencySpaceBetweenAmountAndSymbol: false,
};
}
return cachedUserPreferences;
}
export function createBudgetQueryPrefetchKey({
dimension,
categoryIds,
startMonth,
endMonth,
}: BudgetQueryRequest): string {
return JSON.stringify({
dimension: dimension.toLowerCase(),
categoryIds,
startMonth,
endMonth,
});
}
function categoryRangeToIds(categories: SimpleRangeValue): string[] {
return categories
.valuesFromTopLeftCorner()
.filter(
value =>
value !== EmptyValue &&
!(value instanceof CellError) &&
(typeof value === 'string' || typeof value === 'number'),
)
.flatMap(value => String(value).split(','))
.map(value => value.trim())
.filter(value => value.length > 0);
}
function categoryIdsToRange(categoryIds: string[]): SimpleRangeValue {
return SimpleRangeValue.onlyValues(
categoryIds.length > 0
? categoryIds.map(categoryId => [categoryId])
: [['']],
);
}
function formatCurrencyValue({
value,
currencySymbol,
decimals,
thousandsSeparator,
decimalSeparator,
currencySymbolPosition,
currencySpaceBetweenAmountAndSymbol,
}: {
value: number;
currencySymbol: string;
decimals: number;
thousandsSeparator: string;
decimalSeparator: string;
currencySymbolPosition: CurrencySymbolPosition;
currencySpaceBetweenAmountAndSymbol: boolean;
}): string {
const isNegative = value < 0;
const absNum = Math.abs(value);
const fixedNum = absNum.toFixed(decimals);
const [integerPart, decimalPart] = fixedNum.split('.');
const formattedInteger = integerPart.replace(
/\B(?=(\d{3})+(?!\d))/g,
thousandsSeparator,
);
const formattedAmount =
decimals > 0 && decimalPart
? `${formattedInteger}${decimalSeparator}${decimalPart}`
: formattedInteger;
const space = currencySpaceBetweenAmountAndSymbol ? '\u202F' : '';
const formattedCurrency =
currencySymbolPosition === 'after'
? `${formattedAmount}${space}${currencySymbol}`
: `${currencySymbol}${space}${formattedAmount}`;
return isNegative ? `-${formattedCurrency}` : formattedCurrency;
}
function getCurrencySymbolPositionFromArg(
value: string | undefined,
fallback: CurrencySymbolPosition,
): CurrencySymbolPosition {
return value === 'before' || value === 'after' ? value : fallback;
}
export class CustomFunctionsPlugin extends FunctionPlugin {
private getCustomFunctionsContext(): CustomFunctionsContext | undefined {
return this.config.context as CustomFunctionsContext | undefined;
}
private getFormulaQueryContext(): FormulaQueryContext | undefined {
return this.getCustomFunctionsContext()?.formulaQuery;
}
integerToAmount(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('INTEGER_TO_AMOUNT'),
(integerAmount: number, decimalPlaces: number = 2) => {
return integerToAmount(integerAmount, decimalPlaces);
},
);
}
fixed(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('FIXED'),
(number: number, decimals: number = 0) => {
return Number(number).toFixed(decimals);
},
);
}
balanceOf(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('BALANCE_OF'),
(accountKey: string) => {
const ctx = this.getCustomFunctionsContext();
if (ctx?.balanceOfPrefetch) {
// Rule formulas: prefetched relative to the transaction being evaluated.
return ctx.balanceOfPrefetch.get(accountKey) ?? 0;
}
// Report/query formulas: no single transaction to be "before", so this
// resolves to the account's current balance.
const formulaQueryCtx = this.getFormulaQueryContext();
formulaQueryCtx?.balanceOfNames?.add(accountKey);
return formulaQueryCtx?.balanceOfPrefetch?.get(accountKey) ?? 0;
},
);
}
query(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('QUERY'),
(queryName: string) => {
const ctx = this.getFormulaQueryContext();
ctx?.queryNames?.add(queryName);
return ctx?.querySumPrefetch?.get(queryName) ?? 0;
},
);
}
queryCount(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('QUERY_COUNT'),
(queryName: string) => {
const ctx = this.getFormulaQueryContext();
ctx?.queryCountNames?.add(queryName);
return ctx?.queryCountPrefetch?.get(queryName) ?? 0;
},
);
}
queryExtractCategories(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('QUERY_EXTRACT_CATEGORIES'),
(queryName: string) => {
const ctx = this.getFormulaQueryContext();
ctx?.queryExtractCategoryNames?.add(queryName);
return categoryIdsToRange(
ctx?.queryExtractCategoriesPrefetch?.get(queryName) ?? [],
);
},
);
}
queryExtractCategoriesSize(ast: ProcedureAst): ArraySize {
if (ast.args.length !== 1) {
return ArraySize.error();
}
const [queryNameAst] = ast.args;
const queryName =
queryNameAst.type === 'STRING' ? queryNameAst.value : undefined;
const categoryCount = queryName
? this.getFormulaQueryContext()?.queryExtractCategoriesPrefetch?.get(
queryName,
)?.length
: undefined;
return new ArraySize(1, Math.max(categoryCount ?? 1, 1));
}
queryExtractTimeframeStart(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('QUERY_EXTRACT_TIMEFRAME_START'),
(queryName: string) => {
const ctx = this.getFormulaQueryContext();
ctx?.queryExtractTimeframeStartNames?.add(queryName);
return ctx?.queryExtractTimeframeStartPrefetch?.get(queryName) ?? '';
},
);
}
queryExtractTimeframeEnd(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('QUERY_EXTRACT_TIMEFRAME_END'),
(queryName: string) => {
const ctx = this.getFormulaQueryContext();
ctx?.queryExtractTimeframeEndNames?.add(queryName);
return ctx?.queryExtractTimeframeEndPrefetch?.get(queryName) ?? '';
},
);
}
budgetQuery(ast: ProcedureAst, state: InterpreterState) {
return this.runFunction(
ast.args,
state,
this.metadata('BUDGET_QUERY'),
(
dimension: string,
categories: SimpleRangeValue,
startMonth: string,
endMonth: string,
) => {
const ctx = this.getFormulaQueryContext();
const request: BudgetQueryRequest = {
dimension: dimension.toLowerCase(),
categoryIds: categoryRangeToIds(categories),
startMonth,
endMonth,
};
const key = createBudgetQueryPrefetchKey(request);
ctx?.budgetQueryRequests?.set(key, request);
const error = ctx?.budgetQueryErrors?.get(key);
if (error) {
return new CellError(ErrorType.VALUE, error);
}
return ctx?.budgetQueryPrefetch?.get(key) ?? 0;
},
);
}
formatNumber(ast: ProcedureAst, state: InterpreterState) {
const hasDecimalsArg = ast.args.length > 1;
const hasThousandsSeparatorArg = ast.args.length > 2;
const hasDecimalSeparatorArg = ast.args.length > 3;
return this.runFunction(
ast.args,
state,
this.metadata('FORMATNUMBER'),
(
value: number,
decimals?: number,
thousandsSeparator?: string,
decimalSeparator?: string,
) => {
const num = Number(value);
if (isNaN(num)) {
return new CellError(ErrorType.VALUE);
}
const prefs = getUserPreferences();
const actualThousandsSeparator =
hasThousandsSeparatorArg && thousandsSeparator !== undefined
? thousandsSeparator
: prefs.thousandsSeparator;
const actualDecimalSeparator =
hasDecimalSeparatorArg && decimalSeparator !== undefined
? decimalSeparator
: prefs.decimalSeparator;
const actualDecimals = hasDecimalsArg
? (decimals ?? 2)
: prefs.decimalPlaces;
const fixedNum = num.toFixed(actualDecimals);
const [integerPart, decimalPart] = fixedNum.split('.');
const formattedInteger = integerPart.replace(
/\B(?=(\d{3})+(?!\d))/g,
actualThousandsSeparator,
);
if (actualDecimals > 0 && decimalPart) {
return `${formattedInteger}${actualDecimalSeparator}${decimalPart}`;
}
return formattedInteger;
},
);
}
formatCurrency(ast: ProcedureAst, state: InterpreterState) {
const hasCurrencySymbolArg = ast.args.length > 1;
const hasDecimalsArg = ast.args.length > 2;
const hasThousandsSeparatorArg = ast.args.length > 3;
const hasDecimalSeparatorArg = ast.args.length > 4;
const hasCurrencySymbolPositionArg = ast.args.length > 5;
const hasCurrencySpaceBetweenAmountAndSymbolArg = ast.args.length > 6;
return this.runFunction(
ast.args,
state,
this.metadata('FORMATCURRENCY'),
(
value: number,
currencySymbol?: string,
decimals?: number,
thousandsSeparator?: string,
decimalSeparator?: string,
currencySymbolPosition?: string,
currencySpaceBetweenAmountAndSymbol?: boolean,
) => {
const num = Number(value);
if (isNaN(num)) {
return new CellError(ErrorType.VALUE);
}
const prefs = getUserPreferences();
const actualCurrencySymbol = hasCurrencySymbolArg
? (currencySymbol ?? '')
: prefs.currency.symbol;
const actualDecimals = hasDecimalsArg
? (decimals ?? 2)
: prefs.currency.decimalPlaces;
const actualThousandsSeparator =
hasThousandsSeparatorArg && thousandsSeparator !== undefined
? thousandsSeparator
: prefs.thousandsSeparator;
const actualDecimalSeparator =
hasDecimalSeparatorArg && decimalSeparator !== undefined
? decimalSeparator
: prefs.decimalSeparator;
const actualCurrencySymbolPosition = hasCurrencySymbolPositionArg
? getCurrencySymbolPositionFromArg(
currencySymbolPosition,
prefs.currencySymbolPosition,
)
: prefs.currencySymbolPosition;
const actualCurrencySpaceBetweenAmountAndSymbol =
hasCurrencySpaceBetweenAmountAndSymbolArg &&
currencySpaceBetweenAmountAndSymbol !== undefined
? currencySpaceBetweenAmountAndSymbol
: prefs.currencySpaceBetweenAmountAndSymbol;
return formatCurrencyValue({
value: num,
currencySymbol: actualCurrencySymbol,
decimals: actualDecimals,
thousandsSeparator: actualThousandsSeparator,
decimalSeparator: actualDecimalSeparator,
currencySymbolPosition: actualCurrencySymbolPosition,
currencySpaceBetweenAmountAndSymbol:
actualCurrencySpaceBetweenAmountAndSymbol,
});
},
);
}
}
CustomFunctionsPlugin.implementedFunctions = {
BALANCE_OF: {
method: 'balanceOf',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
FIXED: {
method: 'fixed',
parameters: [
{ argumentType: FunctionArgumentType.NUMBER },
{
argumentType: FunctionArgumentType.NUMBER,
optionalArg: true,
defaultValue: 0,
},
],
},
INTEGER_TO_AMOUNT: {
method: 'integerToAmount',
parameters: [
{ argumentType: FunctionArgumentType.NUMBER },
{
argumentType: FunctionArgumentType.NUMBER,
optionalArg: true,
defaultValue: 2,
},
],
},
QUERY: {
method: 'query',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
QUERY_COUNT: {
method: 'queryCount',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
QUERY_EXTRACT_CATEGORIES: {
method: 'queryExtractCategories',
sizeOfResultArrayMethod: 'queryExtractCategoriesSize',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
QUERY_EXTRACT_TIMEFRAME_END: {
method: 'queryExtractTimeframeEnd',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
QUERY_EXTRACT_TIMEFRAME_START: {
method: 'queryExtractTimeframeStart',
parameters: [{ argumentType: FunctionArgumentType.STRING }],
},
BUDGET_QUERY: {
method: 'budgetQuery',
parameters: [
{ argumentType: FunctionArgumentType.STRING },
{ argumentType: FunctionArgumentType.RANGE },
{ argumentType: FunctionArgumentType.STRING },
{ argumentType: FunctionArgumentType.STRING },
],
},
FORMATNUMBER: {
method: 'formatNumber',
parameters: [
{ argumentType: FunctionArgumentType.NUMBER },
{
argumentType: FunctionArgumentType.NUMBER,
optionalArg: true,
defaultValue: 2,
},
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: ',',
},
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: '.',
},
],
},
FORMATCURRENCY: {
method: 'formatCurrency',
parameters: [
{ argumentType: FunctionArgumentType.NUMBER },
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: '$',
},
{
argumentType: FunctionArgumentType.NUMBER,
optionalArg: true,
defaultValue: 2,
},
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: ',',
},
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: '.',
},
{
argumentType: FunctionArgumentType.STRING,
optionalArg: true,
defaultValue: 'before',
},
{
argumentType: FunctionArgumentType.BOOLEAN,
optionalArg: true,
defaultValue: false,
},
],
},
};
export const customFunctionsTranslations = {
enUS: {
BALANCE_OF: 'BALANCE_OF',
FIXED: 'FIXED',
INTEGER_TO_AMOUNT: 'INTEGER_TO_AMOUNT',
QUERY: 'QUERY',
QUERY_COUNT: 'QUERY_COUNT',
QUERY_EXTRACT_CATEGORIES: 'QUERY_EXTRACT_CATEGORIES',
QUERY_EXTRACT_TIMEFRAME_END: 'QUERY_EXTRACT_TIMEFRAME_END',
QUERY_EXTRACT_TIMEFRAME_START: 'QUERY_EXTRACT_TIMEFRAME_START',
BUDGET_QUERY: 'BUDGET_QUERY',
FORMATNUMBER: 'FORMATNUMBER',
FORMATCURRENCY: 'FORMATCURRENCY',
},
};