Profiles / Business & IT
AM

Accountant & manager

Invoices with VAT, margins, break-even, depreciation and payment due dates.

21 recipes on this page · open in the interactive view

Recipes
  1. Invoice totals with two VAT rates and the due date
  2. Break-even point and margin of safety
  3. Declining-balance depreciation schedule
  4. Late payment interest and recovery indemnity
  5. Bank reconciliation of account 512
  6. Benford test of expense claims
  7. Gaps and duplicates in the invoice numbering
  8. Aged receivables and doubtful debt allowance
  9. Reciprocal allocation of service department costs
  10. Cash rounding of tickets to 5 cents
  11. Invoice total, VAT, amount in words and due date
  12. Fichier des Écritures Comptables: write the FEC of the year and check it before the tax audit
  13. Electronic invoice: compute the totals, check the EN 16931 rules, export in CII and UBL
  14. Pay the suppliers: build the SEPA credit transfer file (pain.001) from the invoices to pay
  15. Collect the monthly fees: SEPA direct debit file (pain.008) with first and recurring debits
  16. Issue a Factur-X invoice from the invoicing data: readable PDF and embedded XML in one call
  17. Print a payment QR code on an invoice: the customer scans it and the transfer is filled in
  18. Reconcile a bank statement with the expected payments
  19. Spread a global discount over the lines of an invoice without losing a cent
  20. Compute due dates and delays in working days, with the holidays of La Réunion and of Bavaria
  21. Send the sales of the month as a LibreOffice Calc file with a summary sheet
executed on MentDB

Invoice totals with two VAT rates and the due date

An invoice dated 15 December 2026 has goods at 20% VAT and books at 5.5%. The script computes the VAT and the gross total, then the due date of a '30 days end of month' term, moved back to the last business day when it falls on a weekend.

Pipeline
Script
admin
#1. Invoice dated 2026-12-15, two VAT rates;
-> "[ht20]" (+ 1250 340);
-> "[ht55]" 180;
-> "[vat]" (math round (+ (math percent [ht20] 20) (math percent [ht55] 5.5)) 2);
-> "[ttc]" (math calc "[ht20]+[ht55]+[vat]");
#2. Term: 30 days end of month;
-> "[due]" (date last_day_of_month (date add "2026-12-15" "DAY" 30));
#3. A weekend due date moves back to the last business day;
-> "[paid]" (date add_business_days (date add_business_days [due] 1) -1);
concat "{\"net\": " (+ [ht20] [ht55]) ", \"vat\": " [vat] ", \"gross\": " [ttc] ", \"term_end\": \"" [due] "\", \"weekend\": " (date is_weekend [due]) ", \"due\": \"" [paid] "\"}";
Result
mentdb
{"net": 1770, "vat": 327.9, "gross": 2097.9, "term_end": "2027-01-31", "weekend": 1, "due": "2027-01-29"}
Go further
executed on MentDB

Break-even point and margin of safety

A product sells 25 with a variable cost of 13.40 and 48000 of fixed costs a year. The script computes the unit contribution margin, the break-even volume and revenue, and the margin of safety against a budget of 5600 units.

Pipeline
Script
admin
#1. Costs and prices of the product;
-> "[fixed]" 48000;
-> "[price]" 25;
-> "[var]" 13.4;
-> "[budget]" 5600;
#2. Contribution margin and break-even point;
-> "[cm]" (math calc "[price]-[var]");
-> "[be]" (math ceil (math calc "[fixed]/[cm]"));
#3. Margin of safety on the budgeted volume;
-> "[mos]" (math round (math calc "([budget]-[be])/[budget]*100") 1);
concat "Contribution margin " [cm] " per unit, " (math round (math calc "[cm]/[price]*100") 1) "% of the price. Break-even at " [be] " units = " (* [be] [price]) " in sales. Margin of safety " [mos] "%."
Result
mentdb
Contribution margin 11.6 per unit, 46.4% of the price. Break-even at 4138 units = 103450 in sales. Margin of safety 26.1%.
Go further
  • Operating leverage: total contribution divided by operating profit at the budget volume
  • Several products: weight the margins by the sales mix with stat weighted_mean
executed on MentDB

Declining-balance depreciation schedule

A machine bought 20000 is depreciated over 5 years with the declining coefficient 1.75 (rate 35%). The script compares the first-year charge with the straight-line method and tables the net book value at the end of each year.

Pipeline
Script
admin
#1. Asset bought 20000, 5 years, declining coefficient 1.75;
-> "[cost]" 20000;
-> "[rate]" (math calc "1.75/5");
#2. Year 1 charge, declining vs straight line;
-> "[y1]" (math calc "[cost]*[rate]");
-> "[sl]" (math calc "[cost]/5");
#3. Net book value at the end of each year;
-> "[nbv]" (num table (concat "round(" [cost] "*(1-" [rate] ")^n*100)/100") "n" 0 5 1);
concat "Year 1 charge: " [y1] " declining vs " [sl] " straight line. Net book value by year: " [nbv];
Result
mentdb
Year 1 charge: 7000 declining vs 4000 straight line. Net book value by year: [
  [0,20000],
  [1,13000],
  [2,8450],
  [3,5492.5],
  [4,3570.13],
  [5,2320.58]
]
Go further
  • Switch to straight line when the remaining value divided by the remaining years gives a higher charge
  • Prorata temporis for the first year: multiply by date nb_day from the purchase date to year end, over 360
executed on MentDB

Late payment interest and recovery indemnity

On 1 November the accountant prepares the reminders of overdue B2B invoices. French law sets the rate at the ECB rate plus 10 points (2.15 + 10 = 12.15 percent here) on the days late, plus a fixed 40 EUR recovery indemnity per invoice. Invoices not yet due are skipped; every amount is exact.

Pipeline
Script
admin
#1. Unpaid B2B invoices on 2026-11-01, legal rate = ECB rate 2.15 + 10 points;
-> "[today]" "2026-11-01";
-> "[rate]" (bd+ 2.15 10);
json load "rx_ar" "[{\"inv\": \"F-2026-071\", \"client\": \"Batiman SARL\", \"ttc\": \"4860.00\", \"due\": \"2026-07-31\"}, {\"inv\": \"F-2026-088\", \"client\": \"Run Distribution\", \"ttc\": \"1275.60\", \"due\": \"2026-09-15\"}, {\"inv\": \"F-2026-102\", \"client\": \"Pharmacie du Port\", \"ttc\": \"612.00\", \"due\": \"2026-11-10\"}]";
json load "rx_pen" "[]";
json load "rx_tot" "[]";
#2. Days late, exact interest = amount x rate x days / 365, plus the 40 EUR recovery indemnity;
for (-> "[i]" 0) (< [i] (json count "rx_ar" /)) (++ "[i]") {
	-> "[due]" (json select "rx_ar" (concat "/[" [i] "]/due"));
	if (date is_after [today] [due]) {
		-> "[days]" (date nb_day [due] [today]);
		-> "[int]" (bigdec round (bigdec div (bd* (bd* (json select "rx_ar" (concat "/[" [i] "]/ttc")) [rate]) [days]) 36500 20) 2);
		-> "[claim]" (bd+ [int] 40);
		json iarray "rx_tot" "/" [claim] NUM;
		json iarray "rx_pen" "/" (concat "{\"invoice\": \"" (json select "rx_ar" (concat "/[" [i] "]/inv")) "\", \"days_late\": " [days] ", \"interest\": " [int] ", \"claim\": " [claim] "}") OBJ;
	};
};
#3. Total to put on the reminder letters;
-> "[out]" (concat "{\"legal_rate_pct\": " [rate] ", \"penalties\": " (string regexp_replace (json doc "rx_pen") "\s" "") ", \"total_claim\": " (bigdec sum (json doc "rx_tot")) "}");
json unload "rx_ar";
json unload "rx_pen";
json unload "rx_tot";
[out]
Result
mentdb
{"legal_rate_pct": 12.15, "penalties": [{"interest":150.45,"days_late":93,"claim":190.45,"invoice":"F-2026-071"},{"interest":19.96,"days_late":47,"claim":59.96,"invoice":"F-2026-088"}], "total_claim": 250.41}
Go further
  • The rate changes each half-year: read it from a parameter instead of the code
  • Write the reminder letters from the penalties array with mswordx replace
executed on MentDB

Bank reconciliation of account 512

At month end the accountant matches the 512 bank account entries with the bank statement: by reference found in the bank label, then by exact amount. The script lists the matched entries, the entries in the books only (cheque not cashed), the amount gaps (typing error) and the bank lines without a reference (fees), and checks that the balances differ by these items only.

Pipeline
Script
admin
#1. October entries of account 512 and the bank statement lines;
json load "rx_gl" "[{\"ref\": \"VIR-4471\", \"amt\": \"-1250.00\"}, {\"ref\": \"CHQ-0918\", \"amt\": \"-380.40\"}, {\"ref\": \"CLI-2210\", \"amt\": \"2045.15\"}, {\"ref\": \"CB-7781\", \"amt\": \"-64.90\"}, {\"ref\": \"CLI-2214\", \"amt\": \"918.00\"}]";
json load "rx_bk" "[{\"label\": \"VIR SEPA VIR-4471 MARTIN\", \"amt\": \"-1250.00\"}, {\"label\": \"REM CLI-2210 HOARAU\", \"amt\": \"2045.15\"}, {\"label\": \"CB CB-7781 STATION\", \"amt\": \"-64.90\"}, {\"label\": \"FRAIS TENUE COMPTE\", \"amt\": \"-18.50\"}, {\"label\": \"REM CLI-2214 PAYET\", \"amt\": \"981.00\"}]";
json load "rx_rec" "{\"matched\": [], \"in_books_only\": [], \"amount_gap\": [], \"bank_only\": [], \"gl\": [], \"bank\": []}";
#2. Match each ledger entry on its reference in the bank label, then compare the exact amounts;
for (-> "[i]" 0) (< [i] (json count "rx_gl" /)) (++ "[i]") {
	-> "[ref]" (json select "rx_gl" (concat "/[" [i] "]/ref"));
	-> "[amt]" (json select "rx_gl" (concat "/[" [i] "]/amt"));
	json iarray "rx_rec" "/gl" [amt] NUM;
	-> "[found]" -1;
	for (-> "[j]" 0) (< [j] (json count "rx_bk" /)) (++ "[j]") {
		if (string contains (json select "rx_bk" (concat "/[" [j] "]/label")) [ref]) {-> "[found]" [j];};
	};
	if (== [found] -1) {json iarray "rx_rec" "/in_books_only" [ref] STR;} {
		-> "[bamt]" (json select "rx_bk" (concat "/[" [found] "]/amt"));
		if (== (bigdec compare [amt] [bamt]) 0) {json iarray "rx_rec" "/matched" [ref] STR;} {json iarray "rx_rec" "/amount_gap" (concat [ref] " books " [amt] " bank " [bamt] " gap " (bigdec abs (bd- [bamt] [amt]))) STR;};
	};
};
#3. Bank lines without reference in the books: fees, direct debits;
for (-> "[j]" 0) (< [j] (json count "rx_bk" /)) (++ "[j]") {
	json iarray "rx_rec" "/bank" (json select "rx_bk" (concat "/[" [j] "]/amt")) NUM;
	if (not (string regexp (json select "rx_bk" (concat "/[" [j] "]/label")) ".*(VIR|CHQ|CLI|CB)-[0-9]{4}.*")) {json iarray "rx_rec" "/bank_only" (json select "rx_bk" (concat "/[" [j] "]/label")) STR;};
};
#4. Balances must differ only by the unreconciled items;
-> "[gl_bal]" (bigdec sum (json select "rx_rec" "/gl"));
-> "[bk_bal]" (bigdec sum (json select "rx_rec" "/bank"));
json dobject "rx_rec" "/" "gl";
json dobject "rx_rec" "/" "bank";
-> "[out]" (concat "{\"books\": " [gl_bal] ", \"bank\": " [bk_bal] ", \"difference\": " (bd- [bk_bal] [gl_bal]) ", \"detail\": " (string regexp_replace (json doc "rx_rec") "\n *" "") "}");
json unload "rx_gl"; json unload "rx_bk"; json unload "rx_rec";
[out]
Result
mentdb
{"books": 1267.85, "bank": 1692.75, "difference": 424.90, "detail": {"in_books_only": ["CHQ-0918"],"amount_gap": ["CLI-2214 books 918.00 bank 981.00 gap 63.00"],"bank_only": ["FRAIS TENUE COMPTE"],"matched": ["VIR-4471","CLI-2210","CB-7781"]}}
Go further
  • Difference 424.90 = cheque 380.40 + typing gap 63.00 - fees 18.50: the reconciliation is complete
  • Book the fees and the correction, then run it again: the difference must be 0
executed on MentDB

Benford test of expense claims

The auditor checks the first digits of 30 expense claims against the Benford law (log10 of 1 + 1/d). The script counts the first digits, compares them with the expected counts in a chi-square test and counts the claims just below the 5000 EUR approval limit, a classic sign of split or tuned claims.

Pipeline
Script
admin
#1. Expense claims of the year to audit, the approval limit without a second signature is 5000 EUR;
json load "rx_exp" "[1240.50, 186.20, 4870.00, 312.75, 1980.00, 145.90, 4950.00, 2310.40, 118.60, 1460.00, 4795.00, 265.30, 1725.80, 3890.00, 132.40, 4920.00, 1090.00, 618.75, 4880.00, 2745.10, 174.00, 1312.60, 4990.00, 806.40, 1155.20, 4860.00, 3420.00, 97.80, 1570.30, 4935.00]";
json load "rx_dg" "[]"; -> "[digits]" ""; -> "[near]" 0;
json parse_array "rx_exp" "/" "[x]" {
	-> "[digits]" (concat [digits] (string left [x] 1));
	json iarray "rx_dg" "/" (string left [x] 1) INT;
	if (and (>= [x] 4500) (< [x] 5000)) {-> "[near]" (+ [near] 1);};
};
#2. Observed count of each first digit against the Benford law log10 of 1 + 1/d;
json load "rx_obs" "[]"; json load "rx_ben" "[]";
for (-> "[d]" 1) (<= [d] 9) (++ "[d]") {
	json iarray "rx_obs" "/" (string regexp_count [digits] [d]) INT;
	json iarray "rx_ben" "/" (math round (* (math log10 (+ 1 (/ 1 [d]))) 30) 1) NUM;
};
#3. Goodness of fit test, the most frequent first digit and the claims just below the limit;
-> "[out]" (concat "{\"n\": " (json count "rx_exp" "/") ", \"observed_1_to_9\": " (string regexp_replace (json doc "rx_obs") "\s" "") ", \"benford_expected\": " (string regexp_replace (json doc "rx_ben") "\s" "") ", \"mode_digit\": " (stat mode (json doc "rx_dg")) ", \"just_below_5000\": " [near] ", \"test\": " (stat chi2_test (json doc "rx_obs") (json doc "rx_ben")) "}");
json unload "rx_exp"; json unload "rx_dg"; json unload "rx_obs"; json unload "rx_ben";
[out]
Result
mentdb
{"n": 30, "observed_1_to_9": [13,3,3,8,0,1,0,1,1], "benford_expected": [9.0,5.3,3.7,2.9,2.4,2.0,1.7,1.5,1.4], "mode_digit": [1], "just_below_5000": 8, "test": {"chi2": 16.702047178231297, "df": 8, "p_value": 0.03336558198265083}}
Go further
  • p = 0.033: the first digits do not follow Benford, mostly because of eight claims of 4795 to 4990 EUR just below the limit
  • Use stat frequency on the first two digits for a finer test on a large ledger
executed on MentDB

Gaps and duplicates in the invoice numbering

Invoices must be numbered in one continuous sequence without gaps. The script extracts the number of each invoice of the till export, sorts them, finds the duplicated number with the mode and lists the missing numbers between two consecutive invoices.

Pipeline
Script
admin
#1. Sales invoice numbers exported from the till for October, French law requires a continuous sequence;
-> "[list]" "FA2026-0412,FA2026-0415,FA2026-0413,FA2026-0418,FA2026-0414,FA2026-0416,FA2026-0420,FA2026-0416,FA2026-0421,FA2026-0419";
json load "rx_num" "[]";
json load "rx_raw" (string split [list] "," -1);
json parse_array "rx_raw" "/" "[inv]" {
	json iarray "rx_num" "/" (string regexp_replace [inv] "^FA2026-0*" "") INT;
};
#2. Sort the numbers, duplicates are the most frequent values;
-> "[sorted]" (math order_int (json doc "rx_num"));
-> "[dup]" (stat mode [sorted]);
#3. Walk the sorted sequence and list the missing numbers;
json load "rx_s" [sorted]; -> "[gaps]" "";
for (-> "[i]" 1) (< [i] (json count "rx_s" "/")) (++ "[i]") {
	-> "[prev]" (json select "rx_s" (concat "/[" (- [i] 1) "]"));
	for (-> "[k]" (+ [prev] 1)) (< [k] (json select "rx_s" (concat "/[" [i] "]"))) (++ "[k]") {
		-> "[gaps]" (concat [gaps] "FA2026-" (string lpad [k] "0" 4) " ");
	};
};
json unload "rx_num"; json unload "rx_raw"; json unload "rx_s";
concat "sorted: " [sorted] " | duplicated: FA2026-" (string lpad (string regexp_replace [dup] "[^0-9]" "") "0" 4) " | missing: " (string trim [gaps])
Result
mentdb
sorted: [412,413,414,415,416,416,418,419,420,421] | duplicated: FA2026-0416 | missing: FA2026-0417
Go further
  • A missing number must be explained (cancelled invoice kept in the file), a duplicate is a till error to report
  • math order_long does the same for numbers above 2^31, such as a year and sequence joined: 20260000412
executed on MentDB

Aged receivables and doubtful debt allowance

At the closing date the accountant splits the open customer invoices into ageing buckets (not due, 1-30, 31-60, 61-90, over 90 days), computes the allowance for doubtful debts at 0, 10, 25 and 50 percent, the average days late and the age of the oldest invoice in weeks.

Pipeline
Script
admin
#1. Open customer invoices at the closing date 2026-10-31;
-> "[close]" "2026-10-31";
json load "rx_ar" "[{\"client\": \"Batiman\", \"due\": \"2026-10-15\", \"amt\": \"3200.00\"}, {\"client\": \"Run Distribution\", \"due\": \"2026-11-20\", \"amt\": \"1840.50\"}, {\"client\": \"Pharmacie du Port\", \"due\": \"2026-08-31\", \"amt\": \"612.00\"}, {\"client\": \"Hotel Lagon\", \"due\": \"2026-09-10\", \"amt\": \"5420.00\"}, {\"client\": \"Garage Hoarau\", \"due\": \"2026-05-31\", \"amt\": \"980.00\"}, {\"client\": \"Cafe Bourbon\", \"due\": \"2026-10-02\", \"amt\": \"415.80\"}]";
json load "rx_age" "{\"not_due\": 0, \"d1_30\": 0, \"d31_60\": 0, \"d61_90\": 0, \"over_90\": 0}";
json load "rx_days" "[]"; -> "[prov]" 0;
#2. Days past due, bucket and doubtful debt allowance rate of each invoice;
json parse_array "rx_ar" "/" "[inv]" {
	json load "rx_i" [inv];
	-> "[amt]" (json select "rx_i" "/amt");
	-> "[late]" (date nb_day (json select "rx_i" "/due") [close]);
	if (> [late] 0) {json iarray "rx_days" "/" [late] INT;};
	if (<= [late] 0) {-> "[b]" "not_due"; -> "[r]" 0;} {if (<= [late] 30) {-> "[b]" "d1_30"; -> "[r]" 0;} {if (<= [late] 60) {-> "[b]" "d31_60"; -> "[r]" 10;} {if (<= [late] 90) {-> "[b]" "d61_90"; -> "[r]" 25;} {-> "[b]" "over_90"; -> "[r]" 50;};};};};
	json uobject "rx_age" "/" [b] (bd+ (json select "rx_age" (concat "/" [b])) [amt]) NUM;
	-> "[prov]" (bd+ [prov] (bigdec round (bigdec div (bd* [amt] [r]) 100 10) 2));
};
#3. Ageing balance, average days late and weeks of the oldest invoice;
-> "[out]" (concat "{\"ageing\": " (string regexp_replace (json doc "rx_age") "\s" "") ", \"avg_days_late\": " (math round (math avg (json doc "rx_days")) 1) ", \"oldest_weeks\": " (math trunc (/ (stat max (json doc "rx_days")) 7)) ", \"allowance\": " [prov] "}");
json unload "rx_ar"; json unload "rx_i"; json unload "rx_age"; json unload "rx_days";
[out]
Result
mentdb
{"ageing": {"d61_90":612.0,"over_90":980.0,"d1_30":3615.8,"d31_60":5420.0,"not_due":1840.5}, "avg_days_late": 62, "oldest_weeks": 21, "allowance": 1185.00}
Go further
  • Book the allowance in 6817 / 491 and reverse last year's one
  • Weight the days late by the amounts with stat weighted_mean: a 5420 EUR invoice matters more than a 415 EUR one
executed on MentDB

Reciprocal allocation of service department costs

IT and Maintenance serve each other and two production shops. The reciprocal method solves the system IT = 120000 + 10 pct of Maintenance, Maintenance = 80000 + 20 pct of IT, then allocates the full costs to Assembly and Painting. The LU factorisation shows the system is well posed.

Pipeline
Script
admin
#1. Service departments IT and Maintenance serve each other and two production shops;
#IT = 120000 + 10 pct of Maintenance, Maintenance = 80000 + 20 pct of IT;
-> "[A]" "[[1, -0.10], [-0.20, 1]]";
-> "[direct]" "[120000, 80000]";
#2. LU factorisation of the reciprocal system, its determinant is the product of the U diagonal;
json load "rx_lu" (matrix lu [A]);
-> "[det]" (* (json select "rx_lu" "/U/[0]/[0]") (json select "rx_lu" "/U/[1]/[1]"));
#3. Total costs to allocate, then the shares of Assembly and Painting: IT 50 and 30 pct, Maintenance 40 and 50 pct;
-> "[total]" (matrix solve [A] [direct]);
-> "[alloc]" (matrix mul "[[0.50, 0.40], [0.30, 0.50]]" [total]);
json load "rx_al" [alloc];
-> "[out]" (concat "{\"det\": " (math round [det] 4) ", \"total_it_maint\": " [total] ", \"assembly\": " (math round (json select "rx_al" "/[0]") 2) ", \"painting\": " (math round (json select "rx_al" "/[1]") 2) ", \"check_sum\": " (math round (+ (json select "rx_al" "/[0]") (json select "rx_al" "/[1]")) 2) "}");
json unload "rx_lu"; json unload "rx_al";
[out]
Result
mentdb
{"det": 0.98, "total_it_maint": [130612.24489795919,106122.44897959183], "assembly": 107755.1, "painting": 92244.9, "check_sum": 200000}
Go further
  • The direct method ignores the services between IT and Maintenance: compare its result with this one
  • With n service departments the same code works with an n x n matrix
executed on MentDB

Cash rounding of tickets to 5 cents

A shop that does not give 1 and 2 cent coins rounds each cash total to the nearest 0.05, credit notes included. The script keeps the sign of each ticket, rounds its absolute value, shows the cents and books the net rounding difference to the gain (758) or loss (658) account.

Pipeline
Script
admin
#1. Cash sales of a shop that rounds the total to 5 cents, a credit note is negative;
json load "rx_t" "[12.42, 7.98, 23.47, -4.12, 18.03, 9.97, 31.26, -15.48]";
-> "[gain]" 0; -> "[lines]" "";
json parse_array "rx_t" "/" "[x]" {
	#2. Cents part, then nearest 0.05 on the absolute value, the sign of the ticket is kept;
	-> "[cents]" (math round (* (math frac_part [x]) 100) 0);
	-> "[paid]" (* (math sign [x]) (/ (math rint (* (math abs [x]) 20)) 20));
	-> "[diff]" (math round (- [paid] [x]) 2);
	-> "[gain]" (+ [gain] [diff]);
	-> "[lines]" (concat [lines] [x] " pays " (math round [paid] 2) " cents " [cents] ", ");
};
json unload "rx_t";
#3. The rounding differences go to 758 when positive, 658 when negative;
-> "[g]" (math round [gain] 2);
concat [lines] "net rounding " [g] " EUR to account " (if (>= (math signum [g]) 0) {758;} {658;})
Result
mentdb
12.42 pays 12.4 cents 42, 7.98 pays 8 cents 98, 23.47 pays 23.45 cents 47, -4.12 pays -4.1 cents -12, 18.03 pays 18.05 cents 3, 9.97 pays 9.95 cents 97, 31.26 pays 31.25 cents 26, -15.48 pays -15.5 cents -48, net rounding -0.03 EUR to account 658
Go further
  • math rint rounds a tie such as 2.5 to the even integer 2: for a ticket ending in 0.025 the shop must choose its rule
  • Card payments are not rounded: apply this only to the cash tickets
executed on MentDB

Invoice total, VAT, amount in words and due date

Compute the total of an invoice excluding tax, the VAT at 20 %, the amount including tax in words for the legal mention, and the due date 30 business days after the invoice date, French public holidays excluded.

Pipeline
Script
admin
#1. Invoice lines;
-> "[l]" "[{\"item\": \"Audit\", \"qty\": 3, \"price\": 450}, {\"item\": \"Training\", \"qty\": 2, \"price\": 380.5}, {\"item\": \"Travel\", \"qty\": 1, \"price\": 212.4}]";
json load "rx_l" (table window [l] "{\"functions\": {\"n\": [\"row_number\"]}}");
-> "[ht]" 0;
for (-> "[i]" 0) (< [i] (json count "rx_l" /)) (++ "[i]") {-> "[ht]" (+ [ht] (* (json select "rx_l" (concat "/[" [i] "]/qty")) (json select "rx_l" (concat "/[" [i] "]/price"))));};
json load "rx_v" (math vat [ht] 20 "ht");
-> "[res]" (concat "HT " (json select "rx_v" "/ht") " | VAT " (json select "rx_v" "/vat") " | TTC " (json select "rx_v" "/ttc") " = " (math amount_in_words (json select "rx_v" "/ttc") "fr" "EUR") " | due " (date add_business_days_fr "2026-12-15" 30));
json unload "rx_l"; json unload "rx_v";
[res]
Result
mentdb
HT 2323.4 | VAT 464.68 | TTC 2788.08 = deux-mille-sept-cent-quatre-vingt-huit euros et huit centimes | due 2027-01-28
Go further
  • math round_bank rounds half to even when many small amounts are rounded.
  • For Alsace-Moselle, add the region: date add_business_days_fr "2026-12-15" 30 "alsace".
executed on MentDB

Fichier des Écritures Comptables: write the FEC of the year and check it before the tax audit

The entries of the year come from the accounting tables. fec write produces the FEC in the legal format (18 columns, tab separator, dates AAAAMMJJ, amounts with a comma) and fec check controls it like the administration tools: header, mandatory fields, balance of each entry and of the file, dates inside the fiscal year, numbering per journal.

Pipeline
Script
admin
if (equal (file exist "tmp/tuto_etl") "0") {file mkdir "tmp/tuto_etl";};
-> "[entries]" "[{\"JournalCode\": \"VE\", \"JournalLib\": \"Ventes\", \"EcritureNum\": \"VE1\", \"EcritureDate\": \"2026-03-02\", \"CompteNum\": \"411000\", \"CompteLib\": \"Clients\", \"PieceRef\": \"F26-001\", \"PieceDate\": \"2026-03-02\", \"EcritureLib\": \"Clients F26-001\", \"Debit\": 1200, \"Credit\": 0, \"ValidDate\": \"2026-03-02\"}, {\"JournalCode\": \"VE\", \"JournalLib\": \"Ventes\", \"EcritureNum\": \"VE1\", \"EcritureDate\": \"2026-03-02\", \"CompteNum\": \"706000\", \"CompteLib\": \"Prestations\", \"PieceRef\": \"F26-001\", \"PieceDate\": \"2026-03-02\", \"EcritureLib\": \"Prestations F26-001\", \"Debit\": 0, \"Credit\": 1000, \"ValidDate\": \"2026-03-02\"}, {\"JournalCode\": \"VE\", \"JournalLib\": \"Ventes\", \"EcritureNum\": \"VE1\", \"EcritureDate\": \"2026-03-02\", \"CompteNum\": \"445710\", \"CompteLib\": \"TVA collectee\", \"PieceRef\": \"F26-001\", \"PieceDate\": \"2026-03-02\", \"EcritureLib\": \"TVA collectee F26-001\", \"Debit\": 0, \"Credit\": 200, \"ValidDate\": \"2026-03-02\"}, {\"JournalCode\": \"AC\", \"JournalLib\": \"Achats\", \"EcritureNum\": \"AC1\", \"EcritureDate\": \"2026-04-10\", \"CompteNum\": \"606000\", \"CompteLib\": \"Achats\", \"PieceRef\": \"FA-77\", \"PieceDate\": \"2026-04-10\", \"EcritureLib\": \"Achats FA-77\", \"Debit\": 450, \"Credit\": 0, \"ValidDate\": \"2026-04-10\"}, {\"JournalCode\": \"AC\", \"JournalLib\": \"Achats\", \"EcritureNum\": \"AC1\", \"EcritureDate\": \"2026-04-10\", \"CompteNum\": \"445660\", \"CompteLib\": \"TVA deductible\", \"PieceRef\": \"FA-77\", \"PieceDate\": \"2026-04-10\", \"EcritureLib\": \"TVA deductible FA-77\", \"Debit\": 90, \"Credit\": 0, \"ValidDate\": \"2026-04-10\"}, {\"JournalCode\": \"AC\", \"JournalLib\": \"Achats\", \"EcritureNum\": \"AC1\", \"EcritureDate\": \"2026-04-10\", \"CompteNum\": \"401000\", \"CompteLib\": \"Fournisseurs\", \"PieceRef\": \"FA-77\", \"PieceDate\": \"2026-04-10\", \"EcritureLib\": \"Fournisseurs FA-77\", \"Debit\": 0, \"Credit\": 540, \"ValidDate\": \"2026-04-10\"}, {\"JournalCode\": \"BQ\", \"JournalLib\": \"Banque\", \"EcritureNum\": \"BQ1\", \"EcritureDate\": \"2026-04-15\", \"CompteNum\": \"512000\", \"CompteLib\": \"Banque\", \"PieceRef\": \"REL-04\", \"PieceDate\": \"2026-04-15\", \"EcritureLib\": \"Banque REL-04\", \"Debit\": 1200, \"Credit\": 0, \"ValidDate\": \"2026-04-15\"}, {\"JournalCode\": \"BQ\", \"JournalLib\": \"Banque\", \"EcritureNum\": \"BQ1\", \"EcritureDate\": \"2026-04-15\", \"CompteNum\": \"411000\", \"CompteLib\": \"Clients\", \"PieceRef\": \"REL-04\", \"PieceDate\": \"2026-04-15\", \"EcritureLib\": \"Clients REL-04\", \"Debit\": 0, \"Credit\": 1200, \"ValidDate\": \"2026-04-15\"}, {\"JournalCode\": \"BQ\", \"JournalLib\": \"Banque\", \"EcritureNum\": \"BQ3\", \"EcritureDate\": \"2026-12-31\", \"CompteNum\": \"401000\", \"CompteLib\": \"Fournisseurs\", \"PieceRef\": \"REL-12\", \"PieceDate\": \"2026-12-31\", \"EcritureLib\": \"Fournisseurs REL-12\", \"Debit\": 540, \"Credit\": 0, \"ValidDate\": \"2026-12-31\"}, {\"JournalCode\": \"BQ\", \"JournalLib\": \"Banque\", \"EcritureNum\": \"BQ3\", \"EcritureDate\": \"2026-12-31\", \"CompteNum\": \"512000\", \"CompteLib\": \"Banque\", \"PieceRef\": \"REL-12\", \"PieceDate\": \"2026-12-31\", \"EcritureLib\": \"Banque REL-12\", \"Debit\": 0, \"Credit\": 504, \"ValidDate\": \"2026-12-31\"}]";
fec write "tmp/tuto_etl/732829320FEC20261231.txt" [entries];
json load "rx_f" (fec check "tmp/tuto_etl/732829320FEC20261231.txt" "{\"fiscal_year\": {\"start\": \"2026-01-01\", \"end\": \"2026-12-31\"}}");
-> "[res]" (concat "valid " (json select "rx_f" "/valid") ", " (json select "rx_f" "/entries") " entries, debit " (json select "rx_f" "/total_debit") " / credit " (json select "rx_f" "/total_credit") " | error: " (json select "rx_f" "/errors/[0]/message") " | warning: " (json select "rx_f" "/warnings/[0]/message"));
json unload "rx_f"; file delete "tmp/tuto_etl/732829320FEC20261231.txt";
[res]
Result
mentdb
valid false, 4 entries, debit 3480 / credit 3444 | error: the entry BQ3 of the journal BQ is not balanced (debit - credit = 36.00) | warning: journal BQ: 1 missing number(s) in EcritureNum BQ1 to BQ3 (first: BQ2)
Go further
  • The file name follows the rule <SIREN>FEC<closing date AAAAMMJJ>.txt.
  • The entry BQ3 has a typing error (504 instead of 540): it is not balanced, and the gap in the numbering (no BQ2) is reported as a warning.
  • fec read gives the lines as JSON rows to analyse them with the table functions (balance per account, per month...).
executed on MentDB

Electronic invoice: compute the totals, check the EN 16931 rules, export in CII and UBL

Since September 2026 every French company must be able to receive electronic invoices. From the lines of an invoice, invoice complete computes the line amounts, the VAT breakdown per rate and the totals with the EN 16931 rounding; invoice check controls the business rules and the French identifiers; the invoice is then exported in CII (the XML of Factur-X) and UBL, and read back to prove that nothing is lost.

Pipeline
Script
admin
-> "[inv]" (invoice complete "{\"number\": \"F2026-0101\", \"type_code\": \"380\", \"issue_date\": \"2026-11-03\", \"due_date\": \"2026-12-03\", \"currency\": \"EUR\", \"seller\": {\"name\": \"Atelier Payet SARL\", \"siren\": \"732829320\", \"vat_id\": \"FR44732829320\", \"address\": {\"line1\": \"12 rue de Paris\", \"postcode\": \"97400\", \"city\": \"Saint-Denis\", \"country\": \"FR\"}}, \"buyer\": {\"name\": \"Client SAS\", \"siren\": \"552100554\", \"address\": {\"postcode\": \"75008\", \"city\": \"Paris\", \"country\": \"FR\"}}, \"lines\": [{\"name\": \"Consulting\", \"quantity\": 3, \"unit\": \"HUR\", \"unit_price\": 95, \"vat_category\": \"S\", \"vat_rate\": 20}, {\"name\": \"Book\", \"quantity\": 2, \"unit\": \"C62\", \"unit_price\": 18.99, \"vat_category\": \"S\", \"vat_rate\": 5.5}, {\"name\": \"Travel\", \"quantity\": 1, \"unit\": \"C62\", \"unit_price\": 42.3, \"vat_category\": \"S\", \"vat_rate\": 20}], \"payment\": {\"means_code\": \"58\", \"iban\": \"FR7630006000011234567890189\"}}");
json load "rx_i" [inv];
json load "rx_c" (invoice check [inv]);
json load "rx_x" (invoice parse (invoice to_cii [inv]));
json load "rx_u" (invoice parse (invoice to_ubl [inv]));
-> "[res]" (concat "net " (json select "rx_i" "/totals/tax_basis") ", VAT " (json select "rx_i" "/totals/vat") " (" (json count "rx_i" "/vat_breakdown") " rates), total " (json select "rx_i" "/totals/grand_total") " | valid " (json select "rx_c" "/valid") " | read back from CII: " (json select "rx_x" "/totals/due") " " (json select "rx_x" "/syntax") ", from UBL: " (json select "rx_u" "/totals/due") " " (json select "rx_u" "/syntax"));
json unload "rx_i"; json unload "rx_c"; json unload "rx_x"; json unload "rx_u";
[res]
Result
mentdb
net 365.28, VAT 67.55 (2 rates), total 432.83 | valid true | read back from CII: 432.83 CII, from UBL: 432.83 UBL
Go further
  • invoice check covers a subset of the EN 16931 rules (totals, VAT breakdown, mandatory fields) and the French identifiers (SIREN, SIRET, VAT key, IBAN): the full Schematron of the platforms stays the reference.
  • To send a Factur-X, embed the CII in the PDF of the invoice with invoice facturx_create; invoice facturx_extract reads the invoices received.
executed on MentDB

Pay the suppliers: build the SEPA credit transfer file (pain.001) from the invoices to pay

The invoices to pay come from a table (supplier, IBAN, invoice, amount). A loop builds the payments, sepa pain001 writes the XML file accepted by the French banks (pain.001.001.03), with the control total, the number of transfers and the characters made SEPA compatible (no accents, no &).

Pipeline
Script
admin
-> "[pay]" "[{\"supplier\": \"Société Équipement & Fils\", \"iban\": \"FR7630004000031234567890143\", \"invoice\": \"FA-881\", \"amount\": 1250.5}, {\"supplier\": \"SCI du Port\", \"iban\": \"FR7610107001011234567890129\", \"invoice\": \"LOYER-11\", \"amount\": 980}, {\"supplier\": \"Imprimerie Hoarau\", \"iban\": \"FR7630006000011234567890189\", \"invoice\": \"FA-1203\", \"amount\": 312.4}]";
json load "rx_p" "{\"message_id\": \"VIR-2026-11-05\", \"created\": \"2026-11-05T08:00:00\", \"execution_date\": \"2026-11-06\", \"debtor\": {\"name\": \"Atelier Payet SARL\", \"iban\": \"FR7630006000011234567890189\", \"bic\": \"AGRIFRPP\"}, \"payments\": []}";
json load "rx_in" [pay];
for (-> "[i]" 0) (< [i] (json count "rx_in" "/")) (++ "[i]") {
    json iarray "rx_p" "/payments" (concat "{\"end_to_end_id\": \"" (json select "rx_in" (concat "/[" [i] "]/invoice")) "\", \"amount\": " (json select "rx_in" (concat "/[" [i] "]/amount")) ", \"creditor\": {\"name\": \"" (json select "rx_in" (concat "/[" [i] "]/supplier")) "\", \"iban\": \"" (json select "rx_in" (concat "/[" [i] "]/iban")) "\"}, \"remittance\": \"Invoice " (json select "rx_in" (concat "/[" [i] "]/invoice")) "\"}") "OBJ";
};
json load "rx_r" (sepa pain001 (json doc "rx_p") "{\"report\": true}");
-> "[res]" (concat (json select "rx_r" "/count") " transfers, total " (json select "rx_r" "/total") " | warnings: " (json select "rx_r" "/warnings") " | " (string length (json select "rx_r" "/xml")) " characters of XML");
json unload "rx_p"; json unload "rx_in"; json unload "rx_r";
[res]
Result
mentdb
3 transfers, total 2542.9 | warnings: [
  "payment 1 creditor name: \u0027Société Équipement \u0026 Fils\u0027 written \u0027Societe Equipement Fils\u0027 (SEPA character set)"
] | 2443 characters of XML
Go further
  • Write the XML with file create and upload it to the bank (EBICS, web banking).
  • The bank statement of the next day (camt.053) is read with sepa camt053 to reconcile the transfers by their end-to-end id.
executed on MentDB

Collect the monthly fees: SEPA direct debit file (pain.008) with first and recurring debits

An association collects the monthly fees of its members by SEPA direct debit. Each member has a mandate (id and signature date); the first debit of a mandate is FRST, the next ones RCUR. sepa pain008 groups the debits by sequence as the banks require, checks the creditor identifier (ICS), the IBANs and the amounts, and gives the totals to check before sending.

Pipeline
Script
admin
-> "[members]" "[{\"member\": \"Hélène Payet\", \"iban\": \"FR7630004000031234567890143\", \"mandate\": \"MDT-0001\", \"signed\": \"2025-09-01\", \"first\": false, \"fee\": 35}, {\"member\": \"Paul Hoarau\", \"iban\": \"FR7610107001011234567890129\", \"mandate\": \"MDT-0042\", \"signed\": \"2026-10-20\", \"first\": true, \"fee\": 35.5}, {\"member\": \"Lucie Grondin\", \"iban\": \"FR7630006000011234567890189\", \"mandate\": \"MDT-0007\", \"signed\": \"2024-01-15\", \"first\": false, \"fee\": 20}]";
json load "rx_m" [members];
json load "rx_p" "{\"message_id\": \"PRL-2026-11\", \"created\": \"2026-11-03T09:30:00\", \"collection_date\": \"2026-11-10\", \"creditor\": {\"name\": \"Club Sportif Saint-Pierre\", \"iban\": \"FR7630006000011234567890189\", \"bic\": \"AGRIFRPP\", \"creditor_id\": \"FR72ZZZ123456\"}, \"debits\": []}";
for (-> "[i]" 0) (< [i] (json count "rx_m" "/")) (++ "[i]") {
    -> "[seq]" "RCUR"; if (equal (json select "rx_m" (concat "/[" [i] "]/first")) "true") {-> "[seq]" "FRST";};
    json iarray "rx_p" "/debits" (concat "{\"end_to_end_id\": \"FEE-2026-11-" [i] "\", \"amount\": " (json select "rx_m" (concat "/[" [i] "]/fee")) ", \"mandate_id\": \"" (json select "rx_m" (concat "/[" [i] "]/mandate")) "\", \"mandate_date\": \"" (json select "rx_m" (concat "/[" [i] "]/signed")) "\", \"sequence\": \"" [seq] "\", \"debtor\": {\"name\": \"" (json select "rx_m" (concat "/[" [i] "]/member")) "\", \"iban\": \"" (json select "rx_m" (concat "/[" [i] "]/iban")) "\"}, \"remittance\": \"Fee November 2026\"}") "OBJ";
};
json load "rx_r" (sepa pain008 (json doc "rx_p") "{\"report\": true}");
-> "[res]" (concat (json select "rx_r" "/count") " debits, total " (json select "rx_r" "/total") " | batch " (json select "rx_r" "/batches/[0]/sequence") ": " (json select "rx_r" "/batches/[0]/count") " for " (json select "rx_r" "/batches/[0]/total") ", batch " (json select "rx_r" "/batches/[1]/sequence") ": " (json select "rx_r" "/batches/[1]/count") " for " (json select "rx_r" "/batches/[1]/total"));
json unload "rx_m"; json unload "rx_p"; json unload "rx_r";
[res]
Result
mentdb
3 debits, total 90.5 | batch FRST: 1 for 35.5, batch RCUR: 2 for 55
Go further
  • After a FRST debit is collected, store that the mandate is now recurring (RCUR) for the next month.
  • The returned debits (insufficient funds, closed account) arrive in the bank statement: read it with sepa camt053.
executed on MentDB

Issue a Factur-X invoice from the invoicing data: readable PDF and embedded XML in one call

From the invoice data (here a company of La Réunion, VAT 8.5 %), invoice to_pdf computes the totals, checks the rules, draws the invoice in French with an embedded font and, with facturx true, embeds the CII XML: the customer receives one PDF readable by a person and by its accounting software. The file is read back as the customer would.

Pipeline
Script
admin
if (equal (file exist "tmp/tuto_etl") "0") {file mkdir "tmp/tuto_etl";};
json load "rx_p" (invoice to_pdf "{\"number\": \"F2026-0150\", \"type_code\": \"380\", \"issue_date\": \"2026-11-03\", \"due_date\": \"2026-12-03\", \"currency\": \"EUR\", \"seller\": {\"name\": \"Atelier Payet SARL\", \"siren\": \"732829320\", \"vat_id\": \"FR44732829320\", \"address\": {\"line1\": \"12 rue de Paris\", \"postcode\": \"97400\", \"city\": \"Saint-Denis\", \"country\": \"FR\"}}, \"buyer\": {\"name\": \"Client SAS\", \"siren\": \"552100554\", \"address\": {\"line1\": \"8 avenue Montaigne\", \"postcode\": \"75008\", \"city\": \"Paris\", \"country\": \"FR\"}}, \"lines\": [{\"name\": \"Data integration audit\", \"quantity\": 2, \"unit\": \"DAY\", \"unit_price\": 650, \"vat_category\": \"S\", \"vat_rate\": 8.5}, {\"name\": \"ETL flows maintenance (November)\", \"quantity\": 1, \"unit\": \"C62\", \"unit_price\": 300, \"vat_category\": \"S\", \"vat_rate\": 8.5}], \"payment\": {\"means_code\": \"58\", \"iban\": \"FR7630006000011234567890189\", \"terms\": \"30 days\"}}" "tmp/tuto_etl/F2026-0150.pdf" "{\"lang\": \"fr\", \"facturx\": true, \"date\": \"2026-11-03T10:00:00\"}");
json load "rx_x" (invoice facturx_extract "tmp/tuto_etl/F2026-0150.pdf");
-> "[res]" (concat "pages " (json select "rx_p" "/pages") ", font embedded " (json select "rx_p" "/embedded") ", Factur-X " (json select "rx_p" "/facturx") " (" (json select "rx_p" "/conformance_level") ") | read back: " (json select "rx_x" "/invoice/number") ", net " (json select "rx_x" "/invoice/totals/tax_basis") ", VAT " (json select "rx_x" "/invoice/totals/vat") ", due " (json select "rx_x" "/invoice/totals/due") " " (json select "rx_x" "/invoice/currency"));
json unload "rx_p"; json unload "rx_x"; file delete "tmp/tuto_etl/F2026-0150.pdf";
[res]
Result
mentdb
pages 1, font embedded true, Factur-X true (EN 16931) | read back: F2026-0150, net 1600, VAT 136, due 1736 EUR
Go further
  • Give the legal mentions of your company with {"legal_mentions": [...]} (late payment penalties, fixed indemnity of 40 euros, no discount for early payment).
  • Validate the PDF/A-3 conformance with veraPDF before sending it to a platform (PDP).
executed on MentDB

Print a payment QR code on an invoice: the customer scans it and the transfer is filled in

A SEPA credit transfer QR code (EPC) holds the beneficiary, the IBAN, the amount and the reference. Banking apps read it directly, so there is no typing and no wrong reference. The image is read back to check what the customer will really scan.

Pipeline
Script
admin
if (equal (file exist "tmp/rec_qr") "0") {file mkdir "tmp/rec_qr";};
-> "[payment]" "{\"name\": \"ACME Reunion\", \"iban\": \"FR7630006000011234567890189\", \"bic\": \"BNPAFRPP\", \"amount\": 1250.4, \"text\": \"Invoice 2026-118\"}";
-> "[payload]" (qr epc_payload [payment]);
qr epc [payment] "tmp/rec_qr/invoice_118.png";
json load "rec_read" (qr read "tmp/rec_qr/invoice_118.png");
-> "[same]" (equal (json select "rec_read" "/text") [payload]);
json unload "rec_read";
file delete "tmp/rec_qr";
-> "[res]" (concat "the image holds the payload: " [same] " | " (string length [payload]) " characters, at most 331 bytes | " (string replace [payload] (string char 10) " / "));
[res]
Result
mentdb
the image holds the payload: 1 | 93 characters, at most 331 bytes | BCD / 002 / 1 / SCT / BNPAFRPP / ACME Reunion / FR7630006000011234567890189 / EUR1250.40 /  /  / Invoice 2026-118
Go further
  • qr epc checks the IBAN (modulo 97) and the amount, and refuses a text and a structured reference together.
  • Chain with pdf image add or docx fill ({{%qr}}) to place the code on the invoice.
executed on MentDB

Reconcile a bank statement with the expected payments

The bank sends an MT940 statement; the accounting system knows the invoices it expects. Matching by reference first, then by amount and date, leaves only the lines that a person must really look at. The amounts stay exact: no rounding by floating point numbers.

Pipeline
Script
admin
-> "[nl]" (string char 10);
-> "[mt940]" (concat ":20:STMT-2026-01" [nl] ":25:AGRIFRPP/FR7630006000011234567890189" [nl] ":28C:1/1" [nl] ":60F:C251231EUR1500,00" [nl] ":61:2601020102C337,16NTRFF2026-0042//BK2026A" [nl] ":86:VIREMENT CLIENT SAS F2026-0042" [nl] ":61:2601020102D120,00NDDTNONREF//BK2026B" [nl] ":86:PRLV EDF CONTRAT 884" [nl] ":61:2601050105C50,00NTRFNONREF//BK2026C" [nl] ":86:VIREMENT INCONNU" [nl] ":62F:C260105EUR1767,16" [nl] "-");
-> "[st]" (bank mt940 [mt940]);
-> "[rows]" (bank to_rows [st]);
-> "[expected]" "[{\"reference\": \"F2026-0042\", \"amount\": \"337.16\", \"date\": \"2026-01-02\"}, {\"reference\": \"EDF-884\", \"amount\": \"-120.00\", \"date\": \"2026-01-02\"}, {\"reference\": \"F2026-0099\", \"amount\": \"75.00\", \"date\": \"2026-01-03\"}]";
json load "rec_m" (bank reconcile [rows] [expected] "{\"date_window\": 3}");
-> "[res]" (concat "matched: " (json count "rec_m" "/matched") " | not expected: " (json count "rec_m" "/unmatched_statement") " | not paid: " (json count "rec_m" "/unmatched_expected"));
json unload "rec_m";
[res]
Result
mentdb
matched: 2 | not expected: 1 | not paid: 1
Go further
  • bank cfonb120, sepa camt053, bank camt052 and bank camt054 give the same shape: the same reconciliation works on every format.
  • A statement whose movements do not reach the closing balance is reported by balance_ok, so a truncated file is seen at once.
executed on MentDB

Spread a global discount over the lines of an invoice without losing a cent

A customer gets 15.00 EUR off an invoice of three lines. Each line must carry its share, for the VAT and the margin by product, and the shares must add up to exactly 15.00. Rounding each share alone can lose or create a cent; money allocate gives the cents left to the largest fractions, so the total is always right. The amounts stay exact decimals from start to end.

Pipeline
Script
admin
-> "[lines]" "[\"129.90\", \"45.50\", \"9.99\"]";
json load "rec_t" (money sum [lines] "{\"currency\": \"EUR\"}");
-> "[total]" (json select "rec_t" "/amount");
json unload "rec_t";
json load "rec_d" (money allocate "15.00" "EUR" [lines]);
-> "[d1]" (json select "rec_d" "/[0]");
-> "[d2]" (json select "rec_d" "/[1]");
-> "[d3]" (json select "rec_d" "/[2]");
json unload "rec_d";
json load "rec_c" (money sum (concat "[\"" [d1] "\", \"" [d2] "\", \"" [d3] "\"]") "{\"currency\": \"EUR\"}");
-> "[check]" (json select "rec_c" "/amount");
json unload "rec_c";
json load "rec_n" (money sum (concat "[\"" [total] "\", \"-15.00\"]") "{\"currency\": \"EUR\"}");
-> "[net]" (json select "rec_n" "/amount");
json unload "rec_n";
-> "[shown]" (money format [net] "EUR" "{\"locale\": \"fr-FR\", \"plain_spaces\": true}");
-> "[cents]" (money to_minor [net] "EUR");
-> "[res]" (concat "lines " [total] " | shares " [d1] " + " [d2] " + " [d3] " = " [check] " | to pay " [shown] " | for the payment API " [cents] " cents");
[res]
Result
mentdb
lines 185.39 | shares 10.51 + 3.68 + 0.81 = 15.00 | to pay 170,39 € | for the payment API 17039 cents
Go further
  • With the ratios [1, 1, 1] or the number of parts, money allocate splits equally: 100.00 in 3 gives 33.34, 33.33, 33.33.
  • money convert takes the rate given by the caller, never a rate from the network: a converted invoice can always be explained.
executed on MentDB

Compute due dates and delays in working days, with the holidays of La Réunion and of Bavaria

A payment term of 10 working days from an invoice of Monday 14 December 2026 crosses Christmas. In La Réunion the list of holidays also has 20 December (abolition of slavery, a Sunday in 2026), and the office closes on 24 and 31 December: the due date is Wednesday 30 December. The same delay for a supplier in Munich, with the holidays of Bavaria and no closing day, ends on 29 December; 6 January (Epiphany) is a holiday there. date workdays then measures the working days between an order and its delivery, the first day counted and the last one not.

Pipeline
Script
admin
-> "[office]" "{\"region\": \"RE\", \"extra_holidays\": [\"2026-12-24\", \"2026-12-31\"]}";
json load "rx_hol" (date holidays "FR" "2026" "RE");
-> "[due_re]" (date add_workdays "2026-12-14" "10" "FR" [office]);
-> "[due_by]" (date add_workdays "2026-12-14" "10" "DE" "{\"region\": \"BY\"}");
-> "[delay]" (date workdays "2026-12-14" "2027-01-08" "FR" [office]);
-> "[res]" (concat "holidays in RE 2026: " (json count "rx_hol" "/") ", last " (json select "rx_hol" "/[10]/date") " " (json select "rx_hol" "/[10]/name_fr") " | due in RE " [due_re] " | due in Bavaria " [due_by] " | working days 14 Dec to 8 Jan in RE: " [delay] " | 2027-01-06 holiday in BY: " (date is_holiday "2027-01-06" "DE" "BY"));
json unload "rx_hol";
[res]
Result
mentdb
holidays in RE 2026: 12, last 2026-12-20 Abolition de l'esclavage | due in RE 2026-12-30 | due in Bavaria 2026-12-29 | working days 14 Dec to 8 Jan in RE: 15 | 2027-01-06 holiday in BY: 1
Go further
  • The first date is counted and the last one is not, as in date business_days_fr: a delay of 0 means the same day.
  • A regional or municipal holiday that is not in the list (a patron saint, a local fair) goes in extra_holidays, with the closing days of the company.
executed on MentDB

Send the sales of the month as a LibreOffice Calc file with a summary sheet

The sales of March must go to a partner who works with LibreOffice. The rows (from a SQL query in real life) are grouped by shop with table group_by, then ods write puts the summary and the details in two sheets of one .ods file: the days become real dates, the amounts are in euros and the margins in percent, the header is bold and frozen. The file is read back to check it before it is sent (the summary sheet, then the text shown to the reader), then deleted here.

Pipeline
Script
admin
-> "[sales]" "[{\"shop\": \"Saint-Denis\", \"day\": \"2026-03-02\", \"amount\": 1250.5, \"margin\": 0.185}, {\"shop\": \"Saint-Pierre\", \"day\": \"2026-03-02\", \"amount\": 980, \"margin\": 0.12}, {\"shop\": \"Saint-Denis\", \"day\": \"2026-03-09\", \"amount\": 480, \"margin\": 0.2}, {\"shop\": \"Le Port\", \"day\": \"2026-03-10\", \"amount\": 455.25, \"margin\": 0.15}, {\"shop\": \"Saint-Pierre\", \"day\": \"2026-03-16\", \"amount\": 1020, \"margin\": 0.1}]";
-> "[summary]" (table group_by [sales] "[\"shop\"]" "{\"total\": [\"sum\", \"amount\"], \"sales\": [\"count\"]}");
-> "[book]" (concat "{\"Summary\": " [summary] ", \"Details\": " [sales] "}");
-> "[file]" "tmp/rec_ods/sales_2026_03.ods";
json load "rx_w" (ods write [file] [book] "{\"date_columns\": [\"day\"], \"currency_columns\": [\"amount\", \"total\"], \"percent_columns\": [\"margin\"]}");
json load "rx_s" (ods sheets [file]);
json load "rx_r" (ods read [file] "{\"sheet\": \"Summary\"}");
json load "rx_t" (ods read [file] "{\"sheet\": \"Details\", \"types\": \"string\", \"max_rows\": 1}");
-> "[res]" (concat "sheets: " (json select "rx_s" "/[0]/name") " (" (json select "rx_s" "/[0]/rows") " lines) and " (json select "rx_s" "/[1]/name") " (" (json select "rx_s" "/[1]/rows") " lines) | first shop: " (json select "rx_r" "/[0]/shop") " " (json select "rx_r" "/[0]/total") " in " (json select "rx_r" "/[0]/sales") " sales | shown: " (json select "rx_t" "/[0]/day") ", " (json select "rx_t" "/[0]/amount") ", margin " (json select "rx_t" "/[0]/margin"));
json unload "rx_w"; json unload "rx_s"; json unload "rx_r"; json unload "rx_t";
file delete [file];
[res]
Result
mentdb
sheets: Summary (4 lines) and Details (6 lines) | first shop: Saint-Denis 1730.5 in 2 sales | shown: 2026-03-02, 1250.50 €, margin 18.50%
Go further
  • ods write replaces an existing file; it writes a temporary file first and renames it at the end, so a failed call never leaves half a file.
  • An amount stays a number in the file (LibreOffice can sum it); only its format shows the euro sign. ods read gives back the number, and the text shown with types string.

Also in Business & IT

FA
Financial analyst
Loan schedules, NPV and IRR, portfolio optimisation: solve and minimise in one line.
14 recipes
AR
Actuary & risk analyst
Frequency and severity laws, simulated losses, quantiles and risk capital.
12 recipes
HP
HR & payroll
Ages, seniority, leave balances and salary statistics.
10 recipes
LS
Logistics & supply chain
Order quantities, safety stock, reorder points and delivery dates.
13 recipes
SM
Sales & marketing analyst
Growth trends, seasonality, A/B tests and forecasts.
16 recipes
WA
Web & API developer
Build JSON responses, encode URLs and headers, parse logs with regex.
16 recipes
SE
Security engineer
Password hashing, webhook signatures, password strength and entropy.
22 recipes
TL
Text & language processing
Count words, match misspellings, convert names between case conventions.
10 recipes