Excel reporting analyst: MQL recipes

Recipes

Quarterly sales workbook with growth formulas
Loan amortization schedule computed by Excel formulas
Budget vs actual in the legacy XLS format
Bank CSV to a category summary with frozen values
Reorder list from a legacy XLS stock export
Investment appraisal: NPV, IRR and discounted payback
Sales ledger workbook queried with SQL
Fixed assets register: straight-line and declining balance
Clean a contact list: trim, validate emails, flag duplicates
Migrate a legacy XLS price list to XLSX, formulas included

executed on MentDB

Quarterly sales workbook with growth formulas

A sales analyst reports Q1 and Q2 sales of four regions. The script writes the data into an xlsx sheet, adds a growth formula per region in percent, a total row in bold on a yellow fill, saves the file, reopens it and reads the formulas and their computed values back.

Pipeline

01 · writeRows from JSONexcelx cell set→02 · formulasGrowth and totalsexcelx cell set→03 · stylePercent and bold totalexcelx cell format→04 · saveSave and reopenexcelx save→05 · readFormula vs valueexcelx cell eval

Script

admin

#1. Workbook, header and one row per region;
excelx load empty "rx_sales";
excelx sheet add "rx_sales" "Sales";
excelx cell set "rx_sales" "Sales" 0 0 "Region" STR;
excelx cell set "rx_sales" "Sales" 0 1 "Q1" STR;
excelx cell set "rx_sales" "Sales" 0 2 "Q2" STR;
excelx cell set "rx_sales" "Sales" 0 3 "Growth" STR;
json load "rx_data" "[[\"North\", 12500, 13980], [\"South\", 9800, 9420], [\"East\", 7300, 8150], [\"West\", 11200, 12010]]";
-> "[r]" 1;
json parse_array "rx_data" "/" "[row]" {
	json load "rx_row" [row];
	excelx cell set "rx_sales" "Sales" [r] 0 (json select "rx_row" "/[0]") STR;
	excelx cell set "rx_sales" "Sales" [r] 1 (json select "rx_row" "/[1]") NUM;
	excelx cell set "rx_sales" "Sales" [r] 2 (json select "rx_row" "/[2]") NUM;
	excelx cell set "rx_sales" "Sales" [r] 3 (concat "C" (+ [r] 1) "/B" (+ [r] 1) "-1") FORMULA;
	excelx cell format "rx_sales" "Sales" [r] 3 "0.0%" "{}";
	-> "[r]" (+ [r] 1);
};
#2. Total row: formulas, bold font on a yellow fill;
json load "rx_bold" "{\"Font\": {\"fontName\": \"Calibri\", \"size\": \"11\", \"color\": \"0,0,0\", \"bold\": \"1\", \"italic\": \"0\"}, \"FillPattern\": \"solid_foreground\", \"FillForegroundColor\": \"LIGHT_YELLOW\", \"BorderTop\": \"THIN\"}";
excelx cell set "rx_sales" "Sales" [r] 0 "Total" STR;
excelx cell set "rx_sales" "Sales" [r] 1 (concat "SUM(B2:B" [r] ")") FORMULA;
excelx cell set "rx_sales" "Sales" [r] 2 (concat "SUM(C2:C" [r] ")") FORMULA;
excelx cell set "rx_sales" "Sales" [r] 3 (concat "C" (+ [r] 1) "/B" (+ [r] 1) "-1") FORMULA;
excelx cell format "rx_sales" "Sales" [r] 1 "#,##0" (json doc "rx_bold");
excelx cell format "rx_sales" "Sales" [r] 2 "#,##0" (json doc "rx_bold");
excelx cell format "rx_sales" "Sales" [r] 3 "0.0%" (json doc "rx_bold");
#3. Save, close, reopen and read back;
excelx save "rx_sales" "tmp/rx_sales.xlsx";
excelx close "rx_sales";
excelx load "rx_chk" "tmp/rx_sales.xlsx";
-> "[last]" (excelx sheet max_row "rx_chk" "Sales");
concat "{\"sheets\": " (string regexp_replace (excelx sheet show "rx_chk") "\s" "") ", \"open\": " (string regexp_replace (excelx show) "\s" "") ", \"loaded\": " (excelx exist "rx_chk") ", \"last_row\": " [last] ", \"total_q2_formula\": \"" (excelx cell get "rx_chk" "Sales" [last] 2) "\", \"total_q2\": " (excelx cell eval "rx_chk" "Sales" [last] 2) ", \"total_growth_pct\": " (math round (* 100 (excelx cell eval "rx_chk" "Sales" [last] 3)) 2) ", \"south_growth_pct\": " (math round (* 100 (excelx cell eval "rx_chk" "Sales" 2 3)) 2) "}";

Result

mentdb

{"sheets": ["Sales"], "open": ["rx_chk"], "loaded": 1, "last_row": 5, "total_q2_formula": "SUM(C2:C5)", "total_q2": 43560.0, "total_growth_pct": 6.76, "south_growth_pct": -3.88}

Go further

Rows and columns are 0-based in excelx cell set, but formulas use the Excel names: row index 4 is row 5 in SUM(B2:B5)
Read a workbook received by mail with excelx load, then loop to excelx sheet max_row

executed on MentDB

Loan amortization schedule computed by Excel formulas

A finance analyst prepares a 12-month schedule for a 15 000 loan at 4.2 percent. The parameters go on one sheet, the schedule on another with PMT and chained balance formulas; the server evaluates the workbook and plots the remaining balance.

Pipeline

01 · paramsInputs and PMTexcelx cell set→02 · scheduleChained formulasexcelx cell set→03 · locateCell by its nameexcelx cell ref→04 · evaluateServer-side evaluationexcelx cell eval→05 · plotBalance per monthplot chart

Script

admin

#1. Parameters and the monthly payment;
excelx load empty "rx_loan";
excelx sheet add "rx_loan" "Params";
excelx sheet add "rx_loan" "Schedule";
excelx cell set "rx_loan" "Params" 0 0 "Principal" STR;
excelx cell set "rx_loan" "Params" 0 1 "15000" NUM;
excelx cell set "rx_loan" "Params" 1 0 "Annual rate" STR;
excelx cell set "rx_loan" "Params" 1 1 "0.042" NUM;
excelx cell set "rx_loan" "Params" 2 0 "Months" STR;
excelx cell set "rx_loan" "Params" 2 1 "12" NUM;
excelx cell set "rx_loan" "Params" 3 0 "Payment" STR;
excelx cell set "rx_loan" "Params" 3 1 "PMT(B2/12,B3,-B1)" FORMULA;
#2. One row per month: interest, principal, balance;
excelx cell set "rx_loan" "Schedule" 0 0 "Month" STR;
excelx cell set "rx_loan" "Schedule" 0 1 "Interest" STR;
excelx cell set "rx_loan" "Schedule" 0 2 "Principal" STR;
excelx cell set "rx_loan" "Schedule" 0 3 "Balance" STR;
for (-> "[m]" 1) (<= [m] 12) (++ "[m]") {
	if (== [m] 1) {-> "[prev]" "Params!B1";} {-> "[prev]" (concat "D" [m]);};
	excelx cell set "rx_loan" "Schedule" [m] 0 [m] NUM;
	excelx cell set "rx_loan" "Schedule" [m] 1 (concat "ROUND(" [prev] "*Params!B2/12,2)") FORMULA;
	excelx cell set "rx_loan" "Schedule" [m] 2 (concat "ROUND(Params!B4,2)-B" (+ [m] 1)) FORMULA;
	excelx cell set "rx_loan" "Schedule" [m] 3 (concat [prev] "-C" (+ [m] 1)) FORMULA;
};
#3. Evaluate the balances, the last one is located by its name D13;
json load "rx_bal" "[]";
for (-> "[m]" 1) (<= [m] 12) (++ "[m]") {
	json iarray "rx_bal" "/" (math round (excelx cell eval "rx_loan" "Schedule" [m] 3) 2) NUM;
};
json load "rx_ref" (excelx cell ref "D13");
-> "[pmt]" (math round (excelx cell eval "rx_loan" "Params" 3 1) 2);
-> "[last]" (math round (excelx cell eval "rx_loan" "Schedule" (json select "rx_ref" "/row") (json select "rx_ref" "/col")) 2);
excelx close_all;
#4. Plot the remaining balance;
plot chart (concat "{\"title\": \"Loan 15000 at 4.2%, payment " [pmt] ", last balance " [last] "\", \"x_label\": \"Month\", \"y_label\": \"Balance\", \"series\": [{\"name\": \"Balance\", \"type\": \"bars\", \"x\": [1,2,3,4,5,6,7,8,9,10,11,12], \"y\": " (json doc "rx_bal") "}]}");

Result

mentdb

The chart is shown in the Plot tab of the editor.

Go further

The last balance is not exactly 0 because each payment is rounded to the cent: adjust the last payment
Save it with excelx save and send it: the formulas stay live in Excel

executed on MentDB

Budget vs actual in the legacy XLS format

An old ERP still imports .xls files. The controller writes budget and actual per expense line, adds the variance and the variance in percent with a built-in Excel format, removes the notes sheet, saves the xls and checks it after reopening.

Pipeline

01 · writeBudget and actualexcel cell set→02 · formatBuilt-in percent formatexcel build_in_format→03 · locateCell E6 by its nameexcel cell ref→04 · cleanDrop the notes sheetexcel sheet delete→05 · checkReopen and evaluateexcel cell eval

Script

admin

#1. XLS workbook with the budget lines;
excel load empty "rx_bud";
excel sheet add "rx_bud" "Budget";
excel sheet add "rx_bud" "Notes";
excel cell set "rx_bud" "Budget" 0 0 "Line" STR;
excel cell set "rx_bud" "Budget" 0 1 "Budget" STR;
excel cell set "rx_bud" "Budget" 0 2 "Actual" STR;
excel cell set "rx_bud" "Budget" 0 3 "Variance" STR;
excel cell set "rx_bud" "Budget" 0 4 "Variance %" STR;
json load "rx_lines" "[[\"Rent\", 1200, 1200], [\"Energy\", 350, 412], [\"Travel\", 800, 645], [\"IT services\", 500, 580], [\"Training\", 300, 0]]";
#2. Index 10 of the built-in formats is the percent with 2 decimals;
json load "rx_fmt" (excel build_in_format);
-> "[pct]" (json select "rx_fmt" "/[10]");
-> "[r]" 1;
json parse_array "rx_lines" "/" "[l]" {
	json load "rx_l" [l];
	excel cell set "rx_bud" "Budget" [r] 0 (json select "rx_l" "/[0]") STR;
	excel cell set "rx_bud" "Budget" [r] 1 (json select "rx_l" "/[1]") NUM;
	excel cell set "rx_bud" "Budget" [r] 2 (json select "rx_l" "/[2]") NUM;
	excel cell set "rx_bud" "Budget" [r] 3 (concat "C" (+ [r] 1) "-B" (+ [r] 1)) FORMULA;
	excel cell set "rx_bud" "Budget" [r] 4 (concat "D" (+ [r] 1) "/B" (+ [r] 1)) FORMULA;
	excel cell format "rx_bud" "Budget" [r] 4 [pct] "{}";
	-> "[r]" (+ [r] 1);
};
#3. Remove the notes sheet, save, close and reopen;
excel sheet delete "rx_bud" "Notes";
excel save "rx_bud" "tmp/rx_budget.xls";
excel close "rx_bud";
excel load "rx_b2" "tmp/rx_budget.xls";
#4. Read the training line, cell E6, by its name;
json load "rx_ref" (excel cell ref "E6");
-> "[e6]" (excel cell eval "rx_b2" "Budget" (json select "rx_ref" "/row") (json select "rx_ref" "/col"));
-> "[res]" (concat "{\"format\": \"" [pct] "\", \"sheets\": " (string regexp_replace (excel sheet show "rx_b2") "\s" "") ", \"max_row\": " (excel sheet max_row "rx_b2" "Budget") ", \"loaded\": " (excel exist "rx_b2") ", \"open\": " (string regexp_replace (excel show) "\s" "") ", \"energy_formula\": \"" (excel cell get "rx_b2" "Budget" 2 3) "\", \"energy_variance\": " (excel cell eval "rx_b2" "Budget" 2 3) ", \"training_E6\": " [e6] "}");
excel close_all;
[res];

Result

mentdb

{"format": "0.00%", "sheets": ["Budget"], "max_row": 5, "loaded": 1, "open": ["rx_b2"], "energy_formula": "C3-B3", "energy_variance": 62.0, "training_E6": -1.0}

Go further

Use excelx for .xlsx files: the functions have the same names and parameters
Highlight the overruns: test the variance with > and apply a red fill with excel cell format

executed on MentDB

Bank CSV to a category summary with frozen values

A bookkeeper receives a bank export in CSV. The script loads every line into a Raw sheet, lets Excel total each category with SUMIF in a helper column, copies the results as plain numbers to a Summary sheet, deletes the Raw sheet so the client only gets the summary, and checks the saved file.

Pipeline

01 · parseRead the CSVcsv parse→02 · rawRaw sheetexcelx cell set→03 · sumifTotals per categoryexcelx cell eval→04 · freezeValues only, drop Rawexcelx sheet delete→05 · checkReopen the fileexcelx load

Script

admin

#1. Bank export, separator semicolon;
file create "tmp/rx_bank.csv" (concat "date;label;category;amount" [_n_] "2026-10-01;Salary ACME;Income;2480.00" [_n_] "2026-10-02;Rent October;Housing;-950.00" [_n_] "2026-10-04;Supermarket;Food;-86.40" [_n_] "2026-10-09;Train ticket;Transport;-42.00" [_n_] "2026-10-12;Market;Food;-23.75" [_n_] "2026-10-18;Fuel;Transport;-61.30" [_n_] "2026-10-25;Supermarket;Food;-104.15") "UTF-8";
#2. Every line goes into the Raw sheet;
excelx load empty "rx_bk";
excelx sheet add "rx_bk" "Raw";
excelx sheet add "rx_bk" "Summary";
-> "[r]" 0;
csv parse (mql "T") (mql "tmp/rx_bank.csv") (mql ";") (mql "\"") (mql "") {
	excelx cell set "rx_bk" "Raw" [r] 0 [T_date] STR;
	excelx cell set "rx_bk" "Raw" [r] 1 [T_category] STR;
	excelx cell set "rx_bk" "Raw" [r] 2 [T_amount] NUM;
	-> "[r]" (+ [r] 1);
};
#3. SUMIF per category in a helper column, its value goes to the summary;
json load "rx_cats" "[\"Income\", \"Housing\", \"Food\", \"Transport\"]";
-> "[i]" 0;
json parse_array "rx_cats" "/" "[c]" {
	excelx cell set "rx_bk" "Summary" [i] 0 [c] STR;
	excelx cell set "rx_bk" "Raw" [i] 4 (concat "SUMIF(B1:B" [r] ",\"" [c] "\",C1:C" [r] ")") FORMULA;
	excelx cell set "rx_bk" "Summary" [i] 1 (excelx cell eval "rx_bk" "Raw" [i] 4) NUM;
	-> "[i]" (+ [i] 1);
};
excelx sheet delete "rx_bk" "Raw";
excelx save "rx_bk" "tmp/rx_summary.xlsx";
excelx close "rx_bk";
#4. Reopen and build the JSON summary;
excelx load "rx_sum" "tmp/rx_summary.xlsx";
json load "rx_res" "{}";
for (-> "[k]" 0) (< [k] [i]) (++ "[k]") {
	json iobject "rx_res" "/" (excelx cell get "rx_sum" "Summary" [k] 0) (excelx cell get "rx_sum" "Summary" [k] 1) NUM;
};
json iobject "rx_res" "/" "lines_imported" [r] INT;
json iobject "rx_res" "/" "sheets" (excelx sheet show "rx_sum") ARRAY;
json doc "rx_res";

Result

mentdb

{
  "lines_imported": 7,
  "sheets": [
    "Summary"
  ],
  "Income": 2480.0,
  "Housing": -950.0,
  "Transport": -103.3,
  "Food": -214.3
}

Go further

Forced column names in csv parse replace the header names, the first line is still skipped
Keep the formulas instead of freezing them when the client must see how the totals are made

executed on MentDB

Reorder list from a legacy XLS stock export

The old ERP exports the stock as an .xls file. The script reopens the file, reads every row with excel cell get, computes the reorder point (daily use x lead time x 1.5 safety) and builds the purchase list for 30 days of use with its total cost.

Pipeline

01 · exportWrite and save the XLSexcel save→02 · reopenLoad the fileexcel load→03 · scanLast row and cellsexcel sheet max_row→04 · readCell valuesexcel cell get→05 · cleanupClose and deleteexcel close

Script

admin

#1. The old ERP exports the stock as an XLS file, simulated here;
excel load empty "rx_erp";
excel sheet add "rx_erp" "Stock";
json load "rx_rows" "[[\"SKU\", \"Item\", \"On hand\", \"Daily use\", \"Lead days\", \"Unit cost\"], [\"A-100\", \"Pallet wrap\", 140, 12, 7, 18.5], [\"A-205\", \"Carton 40x30\", 2600, 150, 10, 0.62], [\"B-310\", \"Label roll\", 35, 6, 9, 7.9], [\"C-020\", \"Tape 50mm\", 410, 22, 5, 1.35], [\"C-044\", \"Strapping band\", 18, 3, 12, 42.0]]";
for (-> "[r]" 0) (< [r] (json count "rx_rows" /)) (++ "[r]") {
	for (-> "[c]" 0) (< [c] 6) (++ "[c]") {
		-> "[v]" (json select "rx_rows" (concat "/[" [r] "][" [c] "]"));
		if (or (== [r] 0) (< [c] 2)) {excel cell set "rx_erp" "Stock" [r] [c] [v] STR;} {excel cell set "rx_erp" "Stock" [r] [c] [v] NUM;};
	};
};
excel save "rx_erp" "tmp/rx_stock.xls";
excel close "rx_erp";
#2. Reopen the file as received and scan every row;
excel load "rx_in" "tmp/rx_stock.xls";
-> "[last]" (excel sheet max_row "rx_in" "Stock");
json load "rx_po" "[]";
-> "[total]" 0;
for (-> "[r]" 1) (<= [r] [last]) (++ "[r]") {
	-> "[onhand]" (excel cell get "rx_in" "Stock" [r] 2);
	-> "[use]" (excel cell get "rx_in" "Stock" [r] 3);
	-> "[lead]" (excel cell get "rx_in" "Stock" [r] 4);
	#3. Reorder point = use x lead time x 1.5 safety, order 30 days of use;
	-> "[rop]" (math ceil (* (* [use] [lead]) 1.5));
	if (< [onhand] [rop]) {
		-> "[qty]" (math ceil (- (+ [rop] (* [use] 30)) [onhand]));
		-> "[cost]" (math round (* [qty] (excel cell get "rx_in" "Stock" [r] 5)) 2);
		-> "[total]" (+ [total] [cost]);
		json iarray "rx_po" "/" (concat "{\"sku\": \"" (excel cell get "rx_in" "Stock" [r] 0) "\", \"on_hand\": " [onhand] ", \"reorder_point\": " [rop] ", \"order_qty\": " [qty] ", \"cost\": " [cost] "}") OBJ;
	};
};
-> "[out]" (concat "{\"sheets\": " (string regexp_replace (excel sheet show "rx_in") "\s" "") ", \"loaded\": " (excel exist "rx_in") ", \"rows\": " [last] ", \"to_order\": " (json doc "rx_po") ", \"po_total\": " (math round [total] 2) "}");
excel close "rx_in";
file delete "tmp/rx_stock.xls";
json unload "rx_rows";
json unload "rx_po";
[out]

Result

mentdb

{"sheets": ["Stock"], "loaded": 1, "rows": 5, "to_order": [
  {
    "cost": 1785.4,
    "on_hand": 35.0,
    "order_qty": 226,
    "sku": "B-310",
    "reorder_point": 81
  },
  {
    "cost": 5292,
    "on_hand": 18.0,
    "order_qty": 126,
    "sku": "C-044",
    "reorder_point": 54
  }
], "po_total": 7077.4}

Go further

Numbers come back as decimals (35.0): round them with math round before printing on a purchase order
Write the purchase list into a new sheet with excel sheet add and send the same file back

executed on MentDB

Investment appraisal: NPV, IRR and discounted payback

Appraise a 185 000 EUR solar carport over 6 years at a 7 % discount rate: the workbook computes the discounted cash flows, the NPV and the IRR with Excel formulas, and MQL cross-checks the NPV and finds the discounted payback year.

Pipeline

01 · flowsCash flows year 0 to 6excelx cell set→02 · formulasDiscounted, cumulative, NPV, IRRexcelx cell eval→03 · checkSame NPV computed in MQLmath pow→04 · paybackFirst year with a positive cumulativeexcelx cell ref

Script

admin

#1. Rate and cash flows, year 0 is the investment;
excelx load empty "rx_p4i_capex";
excelx sheet add "rx_p4i_capex" "Project";
excelx cell set "rx_p4i_capex" "Project" 0 0 "Discount rate" STR;
excelx cell set "rx_p4i_capex" "Project" 0 1 "0.07" NUM;
excelx cell set "rx_p4i_capex" "Project" 1 0 "Year" STR;
excelx cell set "rx_p4i_capex" "Project" 1 1 "Cash flow" STR;
excelx cell set "rx_p4i_capex" "Project" 1 2 "Discounted" STR;
excelx cell set "rx_p4i_capex" "Project" 1 3 "Cumulative" STR;
json load "rx_p4i_cf" "[-185000, 36000, 42500, 45000, 45000, 44000, 42000]";
-> "[npv_mql]" 0;
for (-> "[y]" 0) (<= [y] 6) (++ "[y]") {
	-> "[r]" (+ [y] 2);
	-> "[cf]" (json select "rx_p4i_cf" (concat "/[" [y] "]"));
	excelx cell set "rx_p4i_capex" "Project" [r] 0 [y] NUM;
	excelx cell set "rx_p4i_capex" "Project" [r] 1 [cf] NUM;
	#2. Discounted flow and cumulative, Excel formulas;
	excelx cell set "rx_p4i_capex" "Project" [r] 2 (concat "B" (+ [r] 1) "/(1+$B$1)^A" (+ [r] 1)) FORMULA;
	if (== [y] 0) {excelx cell set "rx_p4i_capex" "Project" [r] 3 "C3" FORMULA;} {excelx cell set "rx_p4i_capex" "Project" [r] 3 (concat "D" [r] "+C" (+ [r] 1)) FORMULA;};
	-> "[npv_mql]" (+ [npv_mql] (/ [cf] (math pow 1.07 [y])));
};
excelx cell set "rx_p4i_capex" "Project" 10 0 "NPV" STR;
excelx cell set "rx_p4i_capex" "Project" 10 1 "NPV(B1,B4:B9)+B3" FORMULA;
excelx cell set "rx_p4i_capex" "Project" 11 0 "IRR" STR;
excelx cell set "rx_p4i_capex" "Project" 11 1 "IRR(B3:B9)" FORMULA;
excelx cell set "rx_p4i_capex" "Project" 12 0 "Profitability index" STR;
excelx cell set "rx_p4i_capex" "Project" 12 1 "1+B11/-B3" FORMULA;
#3. Discounted payback: first year whose cumulative is positive;
-> "[payback]" "none";
for (-> "[y]" 6) (>= [y] 0) (-- "[y]") {
	if (>= (excelx cell eval "rx_p4i_capex" "Project" (+ [y] 2) 3) 0) {-> "[payback]" [y];};
};
#4. Read the results by their cell names;
json load "rx_p4i_ref" (excelx cell ref "B11");
-> "[npv_xl]" (excelx cell eval "rx_p4i_capex" "Project" (json select "rx_p4i_ref" "/row") (json select "rx_p4i_ref" "/col"));
-> "[irr]" (excelx cell eval "rx_p4i_capex" "Project" 11 1);
-> "[pi]" (excelx cell eval "rx_p4i_capex" "Project" 12 1);
excelx save "rx_p4i_capex" "tmp/rx_p4i_capex.xlsx";
excelx close "rx_p4i_capex";
concat "NPV Excel: " (math round [npv_xl] 2) " EUR, NPV MQL: " (math round [npv_mql] 2) " EUR" [_n_] "IRR: " (math round (* 100 [irr]) 2) " %, profitability index: " (math round [pi] 3) [_n_] "Discounted payback: year " [payback] [_n_] "Decision: " (if (> [npv_xl] 0) {"invest"} {"reject"});

Result

mentdb

NPV Excel: 16187.46 EUR, NPV MQL: 16187.46 EUR
IRR: 9.72 %, profitability index: 1.087
Discounted payback: year 6
Decision: invest

Go further

Add a sensitivity table: recompute NPV for rates 5 to 10 % and draw it with plot chart.
Use MIRR with a reinvestment rate in another formula cell.
Load the cash flows from the budget workbook with excelx load and excelx cell get.

executed on MentDB

Sales ledger workbook queried with SQL

A sales ledger arrives as an Excel sheet. Load its rows into a local H2 database, run a GROUP BY region and product family, then write the pivot back as a new sheet with totals, so the analyst gets SQL power without leaving Excel.

Pipeline

01 · readLedger sheet, last rowexcelx sheet max_row→02 · loadRows into a local H2 tablecm set h2_embedded→03 · queryGROUP BY with SQLsql parse→04 · writePivot sheet with a SUM rowexcelx cell set

Script

admin

#1. The ledger as received, simulated here;
excelx load empty "rx_p4i_led";
excelx sheet add "rx_p4i_led" "Ledger";
json load "rx_p4i_rows" "[[\"Region\",\"Family\",\"Amount\"],[\"North\",\"Tools\",1250.5],[\"North\",\"Garden\",480],[\"South\",\"Tools\",2310],[\"North\",\"Tools\",760.25],[\"South\",\"Garden\",1190],[\"East\",\"Tools\",405.9],[\"South\",\"Tools\",88]]";
for (-> "[r]" 0) (< [r] (json count "rx_p4i_rows" /)) (++ "[r]") {
	excelx cell set "rx_p4i_led" "Ledger" [r] 0 (json select "rx_p4i_rows" (concat "/[" [r] "][0]")) STR;
	excelx cell set "rx_p4i_led" "Ledger" [r] 1 (json select "rx_p4i_rows" (concat "/[" [r] "][1]")) STR;
	if (== [r] 0) {excelx cell set "rx_p4i_led" "Ledger" [r] 2 "Amount" STR;} {excelx cell set "rx_p4i_led" "Ledger" [r] 2 (json select "rx_p4i_rows" (concat "/[" [r] "][2]")) NUM;};
};
#2. Local H2 database and a staging table;
cm set "tuto_p4i_xl_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/rx_p4i_h2xl/ledger" "[user]" "sa" "[password]" "sa";};
sql connect "rx_p4i_xl" {cm get "tuto_p4i_xl_db"};
sql dml "rx_p4i_xl" (concat "create table ledger (region varchar(20), family varchar(20), amount decimal(12,2))");
-> "[last]" (excelx sheet max_row "rx_p4i_led" "Ledger");
for (-> "[r]" 1) (<= [r] [last]) (++ "[r]") {
	sql dml "rx_p4i_xl" (concat "insert into ledger values (" (sql encode (excelx cell get "rx_p4i_led" "Ledger" [r] 0)) ", " (sql encode (excelx cell get "rx_p4i_led" "Ledger" [r] 1)) ", " (excelx cell get "rx_p4i_led" "Ledger" [r] 2) ")");
};
#3. The pivot by SQL, written into a new sheet;
excelx sheet add "rx_p4i_led" "Pivot";
excelx cell set "rx_p4i_led" "Pivot" 0 0 "Region" STR;
excelx cell set "rx_p4i_led" "Pivot" 0 1 "Family" STR;
excelx cell set "rx_p4i_led" "Pivot" 0 2 "Lines" STR;
excelx cell set "rx_p4i_led" "Pivot" 0 3 "Total" STR;
-> "[p]" 1;
sql parse "rx_p4i_xl" "T" (concat "select region, family, count(*) n, sum(amount) total from ledger group by region, family order by region, family") {
	excelx cell set "rx_p4i_led" "Pivot" [p] 0 [T_region] STR;
	excelx cell set "rx_p4i_led" "Pivot" [p] 1 [T_family] STR;
	excelx cell set "rx_p4i_led" "Pivot" [p] 2 [T_n] NUM;
	excelx cell set "rx_p4i_led" "Pivot" [p] 3 [T_total] NUM;
	++ "[p]";
};
excelx cell set "rx_p4i_led" "Pivot" [p] 0 "Total" STR;
excelx cell set "rx_p4i_led" "Pivot" [p] 3 (concat "SUM(D2:D" [p] ")") FORMULA;
-> "[grand_xl]" (excelx cell eval "rx_p4i_led" "Pivot" [p] 3);
-> "[grand_sql]" (sql value "rx_p4i_xl" (concat "select sum(amount) from ledger"));
-> "[csv]" (sql to csv "rx_p4i_xl" "pivot" (concat "select region, sum(amount) total from ledger group by region order by total desc") ";" "\"");
#4. Save, close, clean the database;
excelx save "rx_p4i_led" "tmp/rx_p4i_ledger.xlsx";
excelx close "rx_p4i_led";
sql disconnect "rx_p4i_xl";
cm remove "tuto_p4i_xl_db";
file delete "tmp/rx_p4i_h2xl";
concat "Ledger rows: " [last] ", pivot lines: " (- [p] 1) [_n_] "Grand total Excel " [grand_xl] " = SQL " [grand_sql] [_n_] "By region:" [_n_] [csv];

Result

mentdb

Ledger rows: 7, pivot lines: 5
Grand total Excel 6484.65 = SQL 6484.65
By region:
region;total
South;3588.00
North;2490.75
East;405.90

Go further

Read a real file with excelx load, the loop stays the same.
Export any SQL result to a new workbook in one call with sql to excelx.
Keep the H2 database between runs to build a history of monthly ledgers.

executed on MentDB

Fixed assets register: straight-line and declining balance

For a delivery van bought 36 000 EUR with a 6 000 EUR residual value over 5 years, compare the straight-line (SLN) and the double declining balance (DDB) depreciation, year by year, with the net book value, in an XLSX register.

Pipeline

01 · assetCost, salvage, lifeexcelx cell set→02 · deprStraight line and double decliningexcelx cell eval→03 · formatCurrency formatexcelx cell format→04 · plotNet book valuesplot chart

Script

admin

#1. The asset;
excelx load empty "rx_p4i_fa";
excelx sheet add "rx_p4i_fa" "Van";
excelx cell set "rx_p4i_fa" "Van" 0 0 "Cost" STR;
excelx cell set "rx_p4i_fa" "Van" 0 1 "36000" NUM;
excelx cell set "rx_p4i_fa" "Van" 1 0 "Salvage" STR;
excelx cell set "rx_p4i_fa" "Van" 1 1 "6000" NUM;
excelx cell set "rx_p4i_fa" "Van" 2 0 "Life" STR;
excelx cell set "rx_p4i_fa" "Van" 2 1 "5" NUM;
#2. One row per year: straight line, then double declining balance capped at the salvage value;
excelx cell set "rx_p4i_fa" "Van" 3 0 "Year" STR;
excelx cell set "rx_p4i_fa" "Van" 3 1 "SLN" STR;
excelx cell set "rx_p4i_fa" "Van" 3 2 "DDB" STR;
excelx cell set "rx_p4i_fa" "Van" 3 3 "NBV DDB" STR;
json load "rx_p4i_nbv_s" "[36000]";
json load "rx_p4i_nbv_d" "[36000]";
json load "rx_p4i_fmt" "{}";
for (-> "[y]" 1) (<= [y] 5) (++ "[y]") {
	-> "[r]" (+ [y] 3);
	if (== [y] 1) {-> "[prev]" "$B$1";} {-> "[prev]" (concat "D" [r]);};
	excelx cell set "rx_p4i_fa" "Van" [r] 0 [y] NUM;
	excelx cell set "rx_p4i_fa" "Van" [r] 1 "($B$1-$B$2)/$B$3" FORMULA;
	excelx cell set "rx_p4i_fa" "Van" [r] 2 (concat "MAX(0,MIN(" [prev] "*2/$B$3," [prev] "-$B$2))") FORMULA;
	excelx cell set "rx_p4i_fa" "Van" [r] 3 (concat [prev] "-C" (+ [r] 1)) FORMULA;
	excelx cell format "rx_p4i_fa" "Van" [r] 2 "#,##0.00" (json doc "rx_p4i_fmt");
	excelx cell format "rx_p4i_fa" "Van" [r] 3 "#,##0.00" (json doc "rx_p4i_fmt");
	json iarray "rx_p4i_nbv_s" "/" (- 36000 (* [y] (excelx cell eval "rx_p4i_fa" "Van" [r] 1))) NUM;
	json iarray "rx_p4i_nbv_d" "/" (math round (excelx cell eval "rx_p4i_fa" "Van" [r] 3) 2) NUM;
};
excelx save "rx_p4i_fa" "tmp/rx_p4i_assets.xlsx";
excelx close "rx_p4i_fa";
#3. Plot both net book values;
plot chart (concat "{\"title\": \"Delivery van, net book value - SLN vs DDB\", \"x_label\": \"Year\", \"y_label\": \"EUR\", \"series\": [{\"name\": \"Straight line\", \"type\": \"line\", \"x\": [0,1,2,3,4,5], \"y\": " (json doc "rx_p4i_nbv_s") "}, {\"name\": \"Double declining\", \"type\": \"line\", \"x\": [0,1,2,3,4,5], \"y\": " (json doc "rx_p4i_nbv_d") "}]}");

Result

mentdb

The chart is shown in the Plot tab of the editor.

Go further

The POI formula engine of the server does not evaluate DDB, DB or SYD: the DDB logic is written with MIN and MAX so the file still computes in any spreadsheet.
Switch from DDB to straight line when it gives more, as the tax rules allow, with an IF formula.
Loop over a register of assets loaded from a SQL table.

executed on MentDB

Clean a contact list: trim, validate emails, flag duplicates

A marketing contact list exported to Excel has stray spaces, mixed case and duplicates. Normalise each row, validate the email with a regular expression, flag duplicates with a COUNTIF formula and paint the invalid rows in red.

Pipeline

01 · normaliseTrim and casestring capitalize_words→02 · validateEmail regexstring regexp→03 · dupCOUNTIF duplicatesexcelx cell eval→04 · paintRed fill on invalid rowsexcelx cell format

Script

admin

#1. The raw export;
json load "rx_p4i_ct" "[[\"  marie LAURET \",\"Marie.Lauret@Example.com \"],[\"Paul hoarau\",\"paul.hoarau@example\"],[\"Lea  Payet\",\"lea.payet@example.re\"],[\"MARIE lauret\",\" marie.lauret@example.com\"],[\"Tom Grondin\",\"tom grondin@example.com\"]]";
excelx load empty "rx_p4i_cl";
excelx sheet add "rx_p4i_cl" "Contacts";
excelx cell set "rx_p4i_cl" "Contacts" 0 0 "Name" STR;
excelx cell set "rx_p4i_cl" "Contacts" 0 1 "Email" STR;
excelx cell set "rx_p4i_cl" "Contacts" 0 2 "Valid" STR;
excelx cell set "rx_p4i_cl" "Contacts" 0 3 "Occurrences" STR;
json load "rx_p4i_red" "{\"Font\": {\"fontName\": \"Calibri\", \"size\": \"11\", \"color\": \"156,0,6\", \"bold\": \"0\", \"italic\": \"0\"}, \"FillPattern\": \"solid_foreground\", \"FillForegroundColor\": \"ROSE\"}";
-> "[n]" (json count "rx_p4i_ct" /);
-> "[bad]" 0;
for (-> "[i]" 0) (< [i] [n]) (++ "[i]") {
	-> "[r]" (+ [i] 1);
	#2. Normalise: trimmed, capitalised name, lower case email;
	-> "[name]" (string trim (json select "rx_p4i_ct" (concat "/[" [i] "][0]")));
	-> "[name]" (string capitalize_words (string regexp_replace [name] "\s+" " "));
	-> "[mail]" (string lower (string trim (json select "rx_p4i_ct" (concat "/[" [i] "][1]"))));
	excelx cell set "rx_p4i_cl" "Contacts" [r] 0 [name] STR;
	excelx cell set "rx_p4i_cl" "Contacts" [r] 1 [mail] STR;
	#3. Validate and count duplicates by email;
	-> "[ok]" (string regexp [mail] "^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$");
	excelx cell set "rx_p4i_cl" "Contacts" [r] 2 [ok] BOOL;
	excelx cell set "rx_p4i_cl" "Contacts" [r] 3 (concat "COUNTIF($B$2:$B$" (+ [n] 1) ",B" (+ [r] 1) ")") FORMULA;
	if (not [ok]) {
		++ "[bad]";
		excelx cell format "rx_p4i_cl" "Contacts" [r] 0 "General" (json doc "rx_p4i_red");
		excelx cell format "rx_p4i_cl" "Contacts" [r] 1 "General" (json doc "rx_p4i_red");
	};
};
#4. Report;
-> "[report]" "";
for (-> "[r]" 1) (<= [r] [n]) (++ "[r]") {
	-> "[report]" (concat [report] (excelx cell get "rx_p4i_cl" "Contacts" [r] 0) " | " (excelx cell get "rx_p4i_cl" "Contacts" [r] 1) " | valid=" (excelx cell get "rx_p4i_cl" "Contacts" [r] 2) " | seen " (math round (excelx cell eval "rx_p4i_cl" "Contacts" [r] 3) 0) "x" [_n_]);
};
excelx save "rx_p4i_cl" "tmp/rx_p4i_contacts.xlsx";
excelx close "rx_p4i_cl";
concat [report] "Invalid rows painted: " [bad];

Result

mentdb

Marie Lauret | marie.lauret@example.com | valid=1 | seen 2x
Paul Hoarau | paul.hoarau@example | valid=0 | seen 1x
Lea Payet | lea.payet@example.re | valid=1 | seen 1x
Marie Lauret | marie.lauret@example.com | valid=1 | seen 2x
Tom Grondin | tom grondin@example.com | valid=0 | seen 1x
Invalid rows painted: 2

Go further

Delete the second occurrence of a duplicate before saving with a new sheet and excelx sheet delete of the old one.
Check the email domains with the data_quality functions.
Load the clean list into a mailing table with sql dml.

executed on MentDB

Migrate a legacy XLS price list to XLSX, formulas included

A 2003 price list in XLS must move to XLSX. Copy every cell, keep the formulas as formulas, and prove the migration by comparing the evaluated totals of both files.

Pipeline

01 · legacyLegacy XLS with formulasexcel cell set→02 · scanEvery row and columnexcel sheet max_row→03 · copyFormula or value into XLSXexcel cell get→04 · proveTotals of both filesexcelx cell eval

Script

admin

#1. The legacy price list: item, price excl. VAT, VAT rate, price incl. VAT;
excel load empty "rx_p4i_old";
excel sheet add "rx_p4i_old" "Prices";
json load "rx_p4i_pl" "[[\"Hammer 500g\",12.9,0.2],[\"Screwdriver set\",24.5,0.2],[\"Work gloves\",6.4,0.1],[\"Ladder 3m\",89,0.2]]";
excel cell set "rx_p4i_old" "Prices" 0 0 "Item" STR;
excel cell set "rx_p4i_old" "Prices" 0 1 "Excl. VAT" STR;
excel cell set "rx_p4i_old" "Prices" 0 2 "VAT" STR;
excel cell set "rx_p4i_old" "Prices" 0 3 "Incl. VAT" STR;
for (-> "[i]" 0) (< [i] 4) (++ "[i]") {
	-> "[r]" (+ [i] 1);
	excel cell set "rx_p4i_old" "Prices" [r] 0 (json select "rx_p4i_pl" (concat "/[" [i] "][0]")) STR;
	excel cell set "rx_p4i_old" "Prices" [r] 1 (json select "rx_p4i_pl" (concat "/[" [i] "][1]")) NUM;
	excel cell set "rx_p4i_old" "Prices" [r] 2 (json select "rx_p4i_pl" (concat "/[" [i] "][2]")) NUM;
	excel cell set "rx_p4i_old" "Prices" [r] 3 (concat "ROUND(B" (+ [r] 1) "*(1+C" (+ [r] 1) "),2)") FORMULA;
};
excel cell set "rx_p4i_old" "Prices" 5 0 "Total" STR;
excel cell set "rx_p4i_old" "Prices" 5 3 "SUM(D2:D5)" FORMULA;
excel save "rx_p4i_old" "tmp/rx_p4i_prices.xls";
excel close "rx_p4i_old";
#2. Reopen the XLS, create the XLSX;
excel load "rx_p4i_xls" "tmp/rx_p4i_prices.xls";
excelx load empty "rx_p4i_new";
excelx sheet add "rx_p4i_new" "Prices";
-> "[last]" (excel sheet max_row "rx_p4i_xls" "Prices");
-> "[cells]" 0;
-> "[formulas]" 0;
#3. Copy: a text that parses as a formula stays a formula, numbers stay numbers;
for (-> "[r]" 0) (<= [r] [last]) (++ "[r]") {
	for (-> "[c]" 0) (< [c] 4) (++ "[c]") {
		-> "[v]" (excel cell get "rx_p4i_xls" "Prices" [r] [c]);
		if (not (is null or empty [v])) {
			++ "[cells]";
			if (string regexp [v] "^[A-Z]+\(.*\)$") {excelx cell set "rx_p4i_new" "Prices" [r] [c] [v] FORMULA; ++ "[formulas]";} {
				if (type is_number [v]) {excelx cell set "rx_p4i_new" "Prices" [r] [c] [v] NUM;} {excelx cell set "rx_p4i_new" "Prices" [r] [c] [v] STR;};
			};
		};
	};
};
excelx save "rx_p4i_new" "tmp/rx_p4i_prices.xlsx";
#4. Prove it: same evaluated total in both formats;
-> "[t_old]" (excel cell eval "rx_p4i_xls" "Prices" 5 3);
-> "[t_new]" (excelx cell eval "rx_p4i_new" "Prices" 5 3);
excel close "rx_p4i_xls";
excelx close "rx_p4i_new";
concat "Copied cells: " [cells] ", formulas kept: " [formulas] [_n_] "Total XLS: " [t_old] ", total XLSX: " [t_new] ", identical: " (== [t_old] [t_new]) [_n_] "Size XLS " (file size "tmp/rx_p4i_prices.xls") " B, XLSX " (file size "tmp/rx_p4i_prices.xlsx") " B";

Result

mentdb

Copied cells: 22, formulas kept: 5
Total XLS: 158.72, total XLSX: 158.72, identical: 1
Size XLS 4608 B, XLSX 3538 B

Go further

Loop on every sheet with excel sheet show and json parse_array.
Copy the number formats too with excelx cell format.
Migrate a whole folder found with file dir_list_regex ".*\.xls".

Also in Documents & integration

PDF & document automation specialist

Generate invoices and reports, merge and split dossiers, watermark, protect, fill forms and extract text.

10 recipes

XML, HTML & Word integrator

Parse and transform XML with XPath, scrape HTML with selectors, convert to JSON and fill Word templates.

10 recipes

Files & archives manager

Write and read files with streams, rotate logs, archive in zip, tar.gz or bz2, convert encodings and images.

10 recipes

File transfer & remote systems engineer

Exchange files over SFTP, FTP, FTPS and Windows shares, run remote commands over SSH, call other MentDB servers and watch folders.

10 recipes

API & database integrator

Call REST and SOAP services, take payments, send and read mail, and move data between SQL and MongoDB.

10 recipes

Integrator & ETL developer

Expose APIs, import partner files, send reports, and reshape JSON and CSV in memory.

10 recipes