|
1
|
--- |
|
2
|
name: excel-generator |
|
3
|
description: Create Excel spreadsheets with formulas, charts, pivot summaries, and financial models. |
|
4
|
--- |
|
5
|
|
|
6
|
# Excel & Spreadsheet Generator |
|
7
|
|
|
8
|
Create .xlsx files, Google Sheets, and PDF exports with formulas, formatting, charts, data validation, pivot summaries, and financial models. |
|
9
|
|
|
10
|
Library Selection |
|
11
|
|
|
12
|
Need Use Install |
|
13
|
|
|
14
|
Create new .xlsx from scratch, fast, large files xlsxwriter pip install xlsxwriter |
|
15
|
|
|
16
|
Read/modify existing .xlsx, or round-trip edits openpyxl pip install openpyxl |
|
17
|
|
|
18
|
Read legacy .xls (Excel 97-2003) xlrd pip install xlrd |
|
19
|
|
|
20
|
Dump a DataFrame quickly df.to_excel() uses openpyxl/xlsxwriter as engine |
|
21
|
|
|
22
|
Generate Google Sheets gspread + google-auth pip install gspread google-auth |
|
23
|
|
|
24
|
Export to PDF LibreOffice CLI system dependency (see PDF section) |
|
25
|
|
|
26
|
Key gotchas: |
|
27
|
|
|
28
|
Neither openpyxl nor xlsxwriter can read .xls — only .xlsx. Use xlrd for .xls. |
|
29
|
|
|
30
|
xlsxwriter is write-only — it cannot open an existing file. Use openpyxl to edit. |
|
31
|
|
|
32
|
openpyxl uses ~50x the file size in RAM. For 100K+ rows, use xlsxwriter or openpyxl.Workbook(write_only=True). |
|
33
|
|
|
34
|
Formulas are stored as strings — Python does not evaluate them. Excel computes on open. openpyxl reading a formula cell gives you =SUM(A1:A10), not the result (unless you use data_only=True, which reads the last cached value). |
|
35
|
|
|
36
|
Core Recipe — openpyxl |
|
37
|
|
|
38
|
from openpyxl import Workbook |
|
39
|
|
|
40
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side |
|
41
|
|
|
42
|
from openpyxl.utils import get*column*letter |
|
43
|
|
|
44
|
from openpyxl.worksheet.table import Table, TableStyleInfo |
|
45
|
|
|
46
|
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule |
|
47
|
|
|
48
|
from openpyxl.worksheet.datavalidation import DataValidation |
|
49
|
|
|
50
|
wb = Workbook() |
|
51
|
|
|
52
|
ws = wb.active |
|
53
|
|
|
54
|
ws.title = "Sales" |
|
55
|
|
|
56
|
headers = ["Product", "Units", "Price", "Revenue"] |
|
57
|
|
|
58
|
ws.append(headers) |
|
59
|
|
|
60
|
rows = [("Widget", 120, 9.99), ("Gadget", 80, 14.50), ("Gizmo", 200, 4.25)] |
|
61
|
|
|
62
|
for r in rows: |
|
63
|
|
|
64
|
ws.append(r) |
|
65
|
|
|
66
|
for row in range(2, len(rows) + 2): |
|
67
|
|
|
68
|
ws[f"D{row}"] = f"=B{row}*C{row}" |
|
69
|
|
|
70
|
ws[f"D{len(rows)+2}"] = f"=SUM(D2:D{len(rows)+1})" |
|
71
|
|
|
72
|
header*fill = PatternFill(start*color="2F5496", fill_type="solid") |
|
73
|
|
|
74
|
for cell in ws[1]: |
|
75
|
|
|
76
|
cell.font = Font(bold=True, color="FFFFFF") |
|
77
|
|
|
78
|
cell.fill = header_fill |
|
79
|
|
|
80
|
cell.alignment = Alignment(horizontal="center") |
|
81
|
|
|
82
|
cell.border = Border(bottom=Side(border_style="medium")) |
|
83
|
|
|
84
|
for row in ws.iter*rows(min*row=2, min*col=3, max*col=4): |
|
85
|
|
|
86
|
for cell in row: |
|
87
|
|
|
88
|
cell.number_format = '"$"#,##0.00' |
|
89
|
|
|
90
|
for col in ws.columns: |
|
91
|
|
|
92
|
max_len = max(len(str(c.value or "")) for c in col) |
|
93
|
|
|
94
|
ws.column*dimensions[get*column*letter(col[0].column)].width = max*len + 3 |
|
95
|
|
|
96
|
ws.freeze_panes = "A2" |
|
97
|
|
|
98
|
tab = Table(displayName="SalesTable", ref=f"A1:D{len(rows)+1}") |
|
99
|
|
|
100
|
tab.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True) |
|
101
|
|
|
102
|
ws.add_table(tab) |
|
103
|
|
|
104
|
ws.conditional_formatting.add(f"D2:D{len(rows)+1}", |
|
105
|
|
|
106
|
DataBarRule(start*type="min", end*type="max", color="638EC6")) |
|
107
|
|
|
108
|
dv = DataValidation(type="list", formula1='"Active,Paused,Archived"', allow_blank=True) |
|
109
|
|
|
110
|
ws.add*data*validation(dv) |
|
111
|
|
|
112
|
dv.add("E2:E100") |
|
113
|
|
|
114
|
wb.save("output.xlsx") |
|
115
|
|
|
116
|
Charts (openpyxl) |
|
117
|
|
|
118
|
from openpyxl.chart import BarChart, LineChart, PieChart, Reference |
|
119
|
|
|
120
|
chart = BarChart() |
|
121
|
|
|
122
|
chart.title = "Revenue by Product" |
|
123
|
|
|
124
|
chart.y_axis.title = "Revenue ($)" |
|
125
|
|
|
126
|
data = Reference(ws, min*col=4, min*row=1, max_row=4) |
|
127
|
|
|
128
|
cats = Reference(ws, min*col=1, min*row=2, max_row=4) |
|
129
|
|
|
130
|
chart.add*data(data, titles*from_data=True) |
|
131
|
|
|
132
|
chart.set_categories(cats) |
|
133
|
|
|
134
|
ws.add_chart(chart, "F2") |
|
135
|
|
|
136
|
Chart gotchas: |
|
137
|
|
|
138
|
Reference uses 1-indexed rows/cols (not 0-indexed). |
|
139
|
|
|
140
|
titles*from*data=True consumes the first row of the data range as the series label — include the header row in data but NOT in cats. |
|
141
|
|
|
142
|
Supported: BarChart, LineChart, PieChart, ScatterChart, AreaChart, DoughnutChart, RadarChart. 3D variants exist but render inconsistently. |
|
143
|
|
|
144
|
Charts reference cells — if you later insert rows above, the chart range does NOT auto-adjust. |
|
145
|
|
|
146
|
xlsxwriter (faster, write-only, richer formatting) |
|
147
|
|
|
148
|
import xlsxwriter |
|
149
|
|
|
150
|
wb = xlsxwriter.Workbook("report.xlsx") |
|
151
|
|
|
152
|
ws = wb.add_worksheet("Data") |
|
153
|
|
|
154
|
header*fmt = wb.add*format({"bold": True, "bg*color": "#2F5496", "font*color": "white", "border": 1}) |
|
155
|
|
|
156
|
money*fmt = wb.add*format({"num_format": "$#,##0.00"}) |
|
157
|
|
|
158
|
ws.write*row(0, 0, ["Product", "Units", "Price", "Revenue"], header*fmt) |
|
159
|
|
|
160
|
data = [("Widget", 120, 9.99), ("Gadget", 80, 14.50)] |
|
161
|
|
|
162
|
for i, (p, u, pr) in enumerate(data, start=1): |
|
163
|
|
|
164
|
ws.write(i, 0, p) |
|
165
|
|
|
166
|
ws.write(i, 1, u) |
|
167
|
|
|
168
|
ws.write(i, 2, pr, money_fmt) |
|
169
|
|
|
170
|
ws.write*formula(i, 3, f"=B{i+1}*C{i+1}", money*fmt) |
|
171
|
|
|
172
|
ws.autofit() |
|
173
|
|
|
174
|
ws.freeze_panes(1, 0) |
|
175
|
|
|
176
|
wb.close() |
|
177
|
|
|
178
|
pandas Shortcut (multi-sheet with formatting) |
|
179
|
|
|
180
|
import pandas as pd |
|
181
|
|
|
182
|
with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as writer: |
|
183
|
|
|
184
|
df.to*excel(writer, sheet*name="Data", index=False) |
|
185
|
|
|
186
|
summary.to*excel(writer, sheet*name="Summary", index=False) |
|
187
|
|
|
188
|
wb, ws = writer.book, writer.sheets["Data"] |
|
189
|
|
|
190
|
ws.set_column("A:A", 20) |
|
191
|
|
|
192
|
ws.autofilter(0, 0, len(df), len(df.columns) - 1) |
|
193
|
|
|
194
|
Google Sheets Generation |
|
195
|
|
|
196
|
Use the gspread library with a service account or OAuth credentials. Requires the Google Sheets API and Google Drive API enabled. |
|
197
|
|
|
198
|
Setup: Check if a Google Sheets integration is available via Replit integrations first. If not, the user needs to provide a service account JSON key. |
|
199
|
|
|
200
|
import gspread |
|
201
|
|
|
202
|
from google.oauth2.service_account import Credentials |
|
203
|
|
|
204
|
scopes = [ |
|
205
|
|
|
206
|
"<https://www.googleapis.com/auth/spreadsheets>", |
|
207
|
|
|
208
|
"<https://www.googleapis.com/auth/drive>", |
|
209
|
|
|
210
|
] |
|
211
|
|
|
212
|
creds = Credentials.from*service*account*file("service*account.json", scopes=scopes) |
|
213
|
|
|
214
|
gc = gspread.authorize(creds) |
|
215
|
|
|
216
|
sh = gc.create("Sales Report Q1") |
|
217
|
|
|
218
|
ws = sh.sheet1 |
|
219
|
|
|
220
|
ws.update_title("Revenue") |
|
221
|
|
|
222
|
ws.update("A1:D1", [["Product", "Units", "Price", "Revenue"]]) |
|
223
|
|
|
224
|
data = [["Widget", 120, 9.99], ["Gadget", 80, 14.50], ["Gizmo", 200, 4.25]] |
|
225
|
|
|
226
|
ws.update(f"A2:C{len(data)+1}", data) |
|
227
|
|
|
228
|
for i in range(2, len(data) + 2): |
|
229
|
|
|
230
|
ws.update_acell(f"D{i}", f"=B{i}*C{i}") |
|
231
|
|
|
232
|
ws.format("A1:D1", { |
|
233
|
|
|
234
|
"backgroundColor": {"red": 0.18, "green": 0.33, "blue": 0.59}, |
|
235
|
|
|
236
|
"textFormat": {"bold": True, "foregroundColor": {"red": 1, "green": 1, "blue": 1}}, |
|
237
|
|
|
238
|
"horizontalAlignment": "CENTER" |
|
239
|
|
|
240
|
}) |
|
241
|
|
|
242
|
ws.format(f"C2:D{len(data)+1}", {"numberFormat": {"type": "CURRENCY", "pattern": "$#,##0.00"}}) |
|
243
|
|
|
244
|
ws.freeze(rows=1) |
|
245
|
|
|
246
|
sh.share("<user@example.com>", perm_type="user", role="writer") |
|
247
|
|
|
248
|
print(f"Spreadsheet URL: {sh.url}") |
|
249
|
|
|
250
|
Batch updates (faster for large writes): |
|
251
|
|
|
252
|
ws.batch_update([ |
|
253
|
|
|
254
|
{"range": "A1:D1", "values": [headers]}, |
|
255
|
|
|
256
|
{"range": f"A2:C{len(data)+1}", "values": data}, |
|
257
|
|
|
258
|
]) |
|
259
|
|
|
260
|
Google Sheets gotchas: |
|
261
|
|
|
262
|
API rate limits: 60 requests/min per user, 300 requests/min per project. Batch writes to stay under. |
|
263
|
|
|
264
|
update() overwrites — it does not append. Use append_rows() to add to the end. |
|
265
|
|
|
266
|
Formulas work the same as Excel (US-English function names). |
|
267
|
|
|
268
|
Charts must be created via the Sheets API v4 batchUpdate with addChart request — gspread does not have a chart helper. |
|
269
|
|
|
270
|
Conditional formatting requires gspread.utils or raw API calls. |
|
271
|
|
|
272
|
Alternative — upload .xlsx to Google Drive: |
|
273
|
|
|
274
|
from googleapiclient.discovery import build |
|
275
|
|
|
276
|
from googleapiclient.http import MediaFileUpload |
|
277
|
|
|
278
|
drive = build("drive", "v3", credentials=creds) |
|
279
|
|
|
280
|
file_metadata = {"name": "Report", "mimeType": "application/vnd.google-apps.spreadsheet"} |
|
281
|
|
|
282
|
media = MediaFileUpload("report.xlsx", |
|
283
|
|
|
284
|
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") |
|
285
|
|
|
286
|
file = drive.files().create(body=file*metadata, media*body=media, |
|
287
|
|
|
288
|
fields="id,webViewLink").execute() |
|
289
|
|
|
290
|
print(f"Google Sheets URL: {file['webViewLink']}") |
|
291
|
|
|
292
|
PDF Export & Print Layout |
|
293
|
|
|
294
|
Method 1: LibreOffice CLI (best fidelity) |
|
295
|
|
|
296
|
libreoffice --headless --calc --convert-to pdf --outdir ./output report.xlsx |
|
297
|
|
|
298
|
import subprocess |
|
299
|
|
|
300
|
def xlsx*to*pdf(input*path, output*dir="./output"): |
|
301
|
|
|
302
|
result = subprocess.run( |
|
303
|
|
|
304
|
["libreoffice", "--headless", "--calc", "--convert-to", "pdf", |
|
305
|
|
|
306
|
"--outdir", output*dir, input*path], |
|
307
|
|
|
308
|
capture_output=True, text=True, timeout=60 |
|
309
|
|
|
310
|
) |
|
311
|
|
|
312
|
if result.returncode != 0: |
|
313
|
|
|
314
|
raise RuntimeError(f"Conversion failed: {result.stderr}") |
|
315
|
|
|
316
|
return f"{output*dir}/{input*path.rsplit['/', 1](-1).replace('.xlsx', '.pdf')}" |
|
317
|
|
|
318
|
Method 2: Print Layout in openpyxl |
|
319
|
|
|
320
|
from openpyxl.worksheet.page import PageMargins, PrintPageSetup |
|
321
|
|
|
322
|
ws.page_setup.orientation = "landscape" |
|
323
|
|
|
324
|
ws.page*setup.paperSize = ws.PAPERSIZE*LETTER |
|
325
|
|
|
326
|
ws.page_setup.fitToWidth = 1 |
|
327
|
|
|
328
|
ws.page_setup.fitToHeight = 0 |
|
329
|
|
|
330
|
ws.sheet_properties.pageSetUpPr.fitToPage = True |
|
331
|
|
|
332
|
ws.page_margins = PageMargins(left=0.5, right=0.5, top=0.75, bottom=0.75, |
|
333
|
|
|
334
|
header=0.3, footer=0.3) |
|
335
|
|
|
336
|
ws.oddHeader.center.text = "Sales Report — Q1 2026" |
|
337
|
|
|
338
|
ws.oddHeader.center.font = "Arial,Bold" |
|
339
|
|
|
340
|
ws.oddHeader.center.size = 12 |
|
341
|
|
|
342
|
ws.oddFooter.center.text = "Page &P of &N" |
|
343
|
|
|
344
|
ws.oddFooter.right.text = "&D" |
|
345
|
|
|
346
|
ws.print*title*rows = "1:1" |
|
347
|
|
|
348
|
ws.print*area = f"A1:F{last*row}" |
|
349
|
|
|
350
|
ws.row_breaks.append(openpyxl.worksheet.pagebreak.Break(id=25)) |
|
351
|
|
|
352
|
Method 3: Print Layout in xlsxwriter |
|
353
|
|
|
354
|
ws.set_landscape() |
|
355
|
|
|
356
|
ws.set_paper(1) |
|
357
|
|
|
358
|
ws.fit*to*pages(1, 0) |
|
359
|
|
|
360
|
ws.set_margins(left=0.5, right=0.5, top=0.75, bottom=0.75) |
|
361
|
|
|
362
|
ws.set_header("&C&\\Arial,Bold\\&14 Sales Report") |
|
363
|
|
|
364
|
ws.set_footer("&CPage &P of &N") |
|
365
|
|
|
366
|
ws.repeat_rows(0) |
|
367
|
|
|
368
|
ws.print*area(0, 0, last*row, 5) |
|
369
|
|
|
370
|
ws.set*page*break(25, 0) |
|
371
|
|
|
372
|
Print layout tips: |
|
373
|
|
|
374
|
Always set fitToWidth=1 for wide tables — prevents columns from being cut off across pages. |
|
375
|
|
|
376
|
Use repeat_rows to repeat headers on every page — critical for multi-page tables. |
|
377
|
|
|
378
|
Set margins to 0.5" for data-heavy sheets, 0.75"+ for presentation sheets. |
|
379
|
|
|
380
|
Test with landscape for tables wider than 6 columns. |
|
381
|
|
|
382
|
For Excel-native PDF: the user can also do File > Export > PDF in Excel after opening. |
|
383
|
|
|
384
|
Financial Model Recipes |
|
385
|
|
|
386
|
See `financial-models.md` for complete recipes covering: |
|
387
|
|
|
388
|
3-Statement Model — Income Statement, Balance Sheet, Cash Flow linked together |
|
389
|
|
|
390
|
DCF (Discounted Cash Flow) — FCF projections, discount factors, terminal value, equity bridge |
|
391
|
|
|
392
|
LBO (Leveraged Buyout) — Sources & Uses, debt schedule, cash sweep, MOIC/IRR |
|
393
|
|
|
394
|
Comparable Company Analysis — Comp table with multiples, summary stats, implied valuation |
|
395
|
|
|
396
|
Merger Model (M&A) — Accretion/dilution analysis, pro forma EPS |
|
397
|
|
|
398
|
Common patterns across all financial models: |
|
399
|
|
|
400
|
Yellow-highlighted cells (#FFF2CC) = user inputs; everything else is formulas |
|
401
|
|
|
402
|
Use freeze_panes on row 1 (headers) and column A (labels) |
|
403
|
|
|
404
|
Currency format: '"$"#,##0.0' for millions, '"$"#,##0.00' for precise |
|
405
|
|
|
406
|
Multiple format: '0.0x' for EV/EBITDA, leverage ratios |
|
407
|
|
|
408
|
Totals use double-bottom border ("bottom": 6 in xlsxwriter) |
|
409
|
|
|
410
|
Use named ranges or cell references to the Assumptions sheet for all inputs — never hardcode values in formula sheets |
|
411
|
|
|
412
|
Always add a section_fmt for visual grouping (bold, light blue background, top+bottom border) |
|
413
|
|
|
414
|
Pivot-Table-Style Summaries |
|
415
|
|
|
416
|
Python cannot create native Excel PivotTables programmatically (they require cached data). Instead, build the equivalent summary using formulas or pandas aggregation. |
|
417
|
|
|
418
|
Pattern 1: pandas groupby to formatted Excel output |
|
419
|
|
|
420
|
import pandas as pd |
|
421
|
|
|
422
|
df = pd.DataFrame({ |
|
423
|
|
|
424
|
"Region": ["East", "East", "West", "West", "East", "West"], |
|
425
|
|
|
426
|
"Product": ["Widget", "Gadget", "Widget", "Gadget", "Widget", "Gadget"], |
|
427
|
|
|
428
|
"Revenue": [120, 80, 150, 90, 200, 110], |
|
429
|
|
|
430
|
"Units": [10, 5, 12, 6, 15, 8], |
|
431
|
|
|
432
|
}) |
|
433
|
|
|
434
|
pivot = df.pivot_table( |
|
435
|
|
|
436
|
values=["Revenue", "Units"], |
|
437
|
|
|
438
|
index="Region", |
|
439
|
|
|
440
|
columns="Product", |
|
441
|
|
|
442
|
aggfunc="sum", |
|
443
|
|
|
444
|
margins=True, |
|
445
|
|
|
446
|
margins_name="Total" |
|
447
|
|
|
448
|
) |
|
449
|
|
|
450
|
pivot.columns = [f"{val} - {prod}" for val, prod in pivot.columns] |
|
451
|
|
|
452
|
pivot = pivot.reset_index() |
|
453
|
|
|
454
|
with pd.ExcelWriter("pivot_report.xlsx", engine="xlsxwriter") as writer: |
|
455
|
|
|
456
|
df.to*excel(writer, sheet*name="Data", index=False) |
|
457
|
|
|
458
|
pivot.to*excel(writer, sheet*name="Summary", index=False) |
|
459
|
|
|
460
|
ws = writer.sheets["Summary"] |
|
461
|
|
|
462
|
ws.autofit() |
|
463
|
|
|
464
|
hdr*fmt = writer.book.add*format( |
|
465
|
|
|
466
|
{"bold": True, "bg*color": "#2F5496", "font*color": "white"}) |
|
467
|
|
|
468
|
for col_num, value in enumerate(pivot.columns): |
|
469
|
|
|
470
|
ws.write(0, col*num, value, hdr*fmt) |
|
471
|
|
|
472
|
Pattern 2: SUMIFS-based pivot using formulas (dynamic in Excel) |
|
473
|
|
|
474
|
import xlsxwriter |
|
475
|
|
|
476
|
wb = xlsxwriter.Workbook("formula_pivot.xlsx") |
|
477
|
|
|
478
|
ws*data = wb.add*worksheet("Data") |
|
479
|
|
|
480
|
ws*pivot = wb.add*worksheet("Pivot") |
|
481
|
|
|
482
|
headers = ["Region", "Product", "Revenue"] |
|
483
|
|
|
484
|
data_rows = [("East", "Widget", 120), ("East", "Gadget", 80), |
|
485
|
|
|
486
|
("West", "Widget", 150), ("West", "Gadget", 90), |
|
487
|
|
|
488
|
("East", "Widget", 200), ("West", "Gadget", 110)] |
|
489
|
|
|
490
|
ws*data.write*row(0, 0, headers) |
|
491
|
|
|
492
|
for i, row in enumerate(data_rows, 1): |
|
493
|
|
|
494
|
ws*data.write*row(i, 0, row) |
|
495
|
|
|
496
|
last*data = len(data*rows) |
|
497
|
|
|
498
|
regions = ["East", "West"] |
|
499
|
|
|
500
|
products = ["Widget", "Gadget"] |
|
501
|
|
|
502
|
ws_pivot.write(0, 0, "Region \\ Product") |
|
503
|
|
|
504
|
for j, prod in enumerate(products): |
|
505
|
|
|
506
|
ws_pivot.write(0, j + 1, prod) |
|
507
|
|
|
508
|
ws_pivot.write(0, len(products) + 1, "Total") |
|
509
|
|
|
510
|
for i, region in enumerate(regions): |
|
511
|
|
|
512
|
ws_pivot.write(i + 1, 0, region) |
|
513
|
|
|
514
|
for j, prod in enumerate(products): |
|
515
|
|
|
516
|
ws*pivot.write*formula(i + 1, j + 1, |
|
517
|
|
|
518
|
f'=SUMIFS(Data!C2:C{last*data+1}, Data!A2:A{last*data+1}, ' |
|
519
|
|
|
520
|
f'A{i+2}, Data!B2:B{last_data+1}, {chr(66+j)}1)') |
|
521
|
|
|
522
|
ws*pivot.write*formula(i + 1, len(products) + 1, |
|
523
|
|
|
524
|
f"=SUM(B{i+2}:{chr(65+len(products))}{i+2})") |
|
525
|
|
|
526
|
ws_pivot.write(len(regions) + 1, 0, "Total") |
|
527
|
|
|
528
|
for j in range(len(products) + 1): |
|
529
|
|
|
530
|
col_letter = chr(66 + j) |
|
531
|
|
|
532
|
ws*pivot.write*formula(len(regions) + 1, j + 1, |
|
533
|
|
|
534
|
f"=SUM({col*letter}2:{col*letter}{len(regions)+1})") |
|
535
|
|
|
536
|
wb.close() |
|
537
|
|
|
538
|
Pattern 3: Multi-dimension pivot with percentage columns |
|
539
|
|
|
540
|
## After building a pivot, add percentage-of-total columns |
|
541
|
|
|
542
|
## =Cell / ColumnTotal formatted as "0.0%" |
|
543
|
|
|
544
|
## =Cell / RowTotal formatted as "0.0%" |
|
545
|
|
|
546
|
## Use IFERROR to handle division by zero |
|
547
|
|
|
548
|
Multi-Workbook Linking |
|
549
|
|
|
550
|
Excel supports cross-workbook references using [filename.xlsx]SheetName!Cell syntax. Python can write these formulas as strings. |
|
551
|
|
|
552
|
Writing cross-workbook references |
|
553
|
|
|
554
|
import xlsxwriter |
|
555
|
|
|
556
|
wb = xlsxwriter.Workbook("master_report.xlsx") |
|
557
|
|
|
558
|
ws = wb.add_worksheet("Consolidated") |
|
559
|
|
|
560
|
ws.write(0, 0, "Region") |
|
561
|
|
|
562
|
ws.write(0, 1, "Revenue") |
|
563
|
|
|
564
|
ws.write(0, 2, "EBITDA") |
|
565
|
|
|
566
|
regions = [ |
|
567
|
|
|
568
|
("East", "east_data.xlsx"), |
|
569
|
|
|
570
|
("West", "west_data.xlsx"), |
|
571
|
|
|
572
|
("Central", "central_data.xlsx"), |
|
573
|
|
|
574
|
] |
|
575
|
|
|
576
|
for i, (region, filename) in enumerate(regions, 1): |
|
577
|
|
|
578
|
ws.write(i, 0, region) |
|
579
|
|
|
580
|
ws.write_formula(i, 1, f"='[{filename}]Summary'!B2") |
|
581
|
|
|
582
|
ws.write_formula(i, 2, f"='[{filename}]Summary'!B5") |
|
583
|
|
|
584
|
ws.write(len(regions) + 1, 0, "Total") |
|
585
|
|
|
586
|
ws.write_formula(len(regions) + 1, 1, f"=SUM(B2:B{len(regions)+1})") |
|
587
|
|
|
588
|
ws.write_formula(len(regions) + 1, 2, f"=SUM(C2:C{len(regions)+1})") |
|
589
|
|
|
590
|
wb.close() |
|
591
|
|
|
592
|
Multi-workbook gotchas: |
|
593
|
|
|
594
|
External references only resolve when both files are open in Excel or when Excel has cached values. |
|
595
|
|
|
596
|
Python writes the formula string only — no values are cached. On first open, Excel will show \#REF! until the linked file is also open or the link is updated. |
|
597
|
|
|
598
|
File paths in references can be relative ([file.xlsx]) or absolute ('C:\Reports\\file.xlsx]'). Prefer relative for portability. |
|
599
|
|
|
600
|
When generating a suite of workbooks, generate all data workbooks first, then the master. |
|
601
|
|
|
602
|
Google Sheets uses IMPORTRANGE("spreadsheet_url", "Sheet1!A1:B10") instead of bracket syntax. |
|
603
|
|
|
604
|
Generating a workbook suite |
|
605
|
|
|
606
|
import xlsxwriter |
|
607
|
|
|
608
|
regions = {"East": [100, 200, 150], "West": [180, 220, 190], |
|
609
|
|
|
610
|
"Central": [90, 110, 130]} |
|
611
|
|
|
612
|
for region, revenues in regions.items(): |
|
613
|
|
|
614
|
wb = xlsxwriter.Workbook(f"{region.lower()}_data.xlsx") |
|
615
|
|
|
616
|
ws = wb.add_worksheet("Summary") |
|
617
|
|
|
618
|
ws.write(0, 0, "Quarter") |
|
619
|
|
|
620
|
ws.write(0, 1, "Revenue") |
|
621
|
|
|
622
|
for i, rev in enumerate(revenues, 1): |
|
623
|
|
|
624
|
ws.write(i, 0, f"Q{i}") |
|
625
|
|
|
626
|
ws.write(i, 1, rev) |
|
627
|
|
|
628
|
ws.write(len(revenues) + 1, 0, "Total") |
|
629
|
|
|
630
|
ws.write_formula(len(revenues) + 1, 1, f"=SUM(B2:B{len(revenues)+1})") |
|
631
|
|
|
632
|
wb.close() |
|
633
|
|
|
634
|
Excel Data Tables (Sensitivity Analysis) |
|
635
|
|
|
636
|
Excel Data Tables (What-If Analysis > Data Table) let you vary one or two inputs and see how an output changes. Python cannot create native data tables, but you can build the equivalent grid of formulas. |
|
637
|
|
|
638
|
One-Way Data Table |
|
639
|
|
|
640
|
import xlsxwriter |
|
641
|
|
|
642
|
wb = xlsxwriter.Workbook("sensitivity_1way.xlsx") |
|
643
|
|
|
644
|
ws = wb.add_worksheet("Sensitivity") |
|
645
|
|
|
646
|
ws.write(0, 0, "Revenue") |
|
647
|
|
|
648
|
ws.write(0, 1, 1000) |
|
649
|
|
|
650
|
ws.write(1, 0, "Margin") |
|
651
|
|
|
652
|
ws.write(1, 1, 0.20) |
|
653
|
|
|
654
|
ws.write(2, 0, "EBITDA") |
|
655
|
|
|
656
|
ws.write_formula(2, 1, "=B1*B2") |
|
657
|
|
|
658
|
margins = [0.10, 0.15, 0.20, 0.25, 0.30] |
|
659
|
|
|
660
|
ws.write(4, 0, "Margin Sensitivity") |
|
661
|
|
|
662
|
ws.write(5, 0, "Margin") |
|
663
|
|
|
664
|
ws.write(5, 1, "EBITDA") |
|
665
|
|
|
666
|
pct*fmt = wb.add*format({"num_format": "0.0%"}) |
|
667
|
|
|
668
|
money*fmt = wb.add*format({"num_format": '"$"#,##0.0'}) |
|
669
|
|
|
670
|
for i, margin in enumerate(margins): |
|
671
|
|
|
672
|
ws.write(6 + i, 0, margin, pct_fmt) |
|
673
|
|
|
674
|
ws.write*formula(6 + i, 1, f"=B1*A{7+i}", money*fmt) |
|
675
|
|
|
676
|
wb.close() |
|
677
|
|
|
678
|
Two-Way Data Table (Sensitivity Grid) |
|
679
|
|
|
680
|
import xlsxwriter |
|
681
|
|
|
682
|
wb = xlsxwriter.Workbook("sensitivity_2way.xlsx") |
|
683
|
|
|
684
|
ws = wb.add_worksheet("IRR Sensitivity") |
|
685
|
|
|
686
|
hdr = wb.add*format({"bold": True, "bg*color": "#1B3A5C", |
|
687
|
|
|
688
|
"font_color": "white", "align": "center"}) |
|
689
|
|
|
690
|
corner = wb.add*format({"bold": True, "bg*color": "#1B3A5C", |
|
691
|
|
|
692
|
"font*color": "white", "align": "center", "text*wrap": True}) |
|
693
|
|
|
694
|
pct = wb.add*format({"num*format": "0.0%", "align": "center"}) |
|
695
|
|
|
696
|
x*fmt = wb.add*format({"num_format": "0.0x", "align": "center"}) |
|
697
|
|
|
698
|
cell*pct = wb.add*format({"num_format": "0.0%", "align": "center", |
|
699
|
|
|
700
|
"bg_color": "#F2F2F2"}) |
|
701
|
|
|
702
|
ws.write(0, 0, "Entry EBITDA") |
|
703
|
|
|
704
|
ws.write(0, 1, 100) |
|
705
|
|
|
706
|
ws.write(1, 0, "Equity Invested") |
|
707
|
|
|
708
|
ws.write(1, 1, 400) |
|
709
|
|
|
710
|
ws.write(2, 0, "Hold Period") |
|
711
|
|
|
712
|
ws.write(2, 1, 5) |
|
713
|
|
|
714
|
exit_multiples = [6.0, 7.0, 8.0, 9.0, 10.0] |
|
715
|
|
|
716
|
ebitda*growth*rates = [0.00, 0.03, 0.05, 0.08, 0.10, 0.12] |
|
717
|
|
|
718
|
start_row = 5 |
|
719
|
|
|
720
|
ws.write(start_row, 0, "EBITDA Growth \\ Exit Multiple", corner) |
|
721
|
|
|
722
|
for j, mult in enumerate(exit_multiples): |
|
723
|
|
|
724
|
ws.write(start*row, j + 1, mult, x*fmt) |
|
725
|
|
|
726
|
for i, growth in enumerate(ebitda*growth*rates): |
|
727
|
|
|
728
|
r = start_row + 1 + i |
|
729
|
|
|
730
|
ws.write(r, 0, growth, pct) |
|
731
|
|
|
732
|
for j, mult in enumerate(exit_multiples): |
|
733
|
|
|
734
|
ws.write_formula(r, j + 1, |
|
735
|
|
|
736
|
f"=IFERROR((B1*(1+A{r+1})^B3*{mult}/B2)^(1/B3)-1, 0)", |
|
737
|
|
|
738
|
cell_pct) |
|
739
|
|
|
740
|
ws.conditional*format(start*row+1, 1, |
|
741
|
|
|
742
|
start*row+len(ebitda*growth*rates), len(exit*multiples), { |
|
743
|
|
|
744
|
"type": "3*color*scale", |
|
745
|
|
|
746
|
"min_color": "#F8696B", |
|
747
|
|
|
748
|
"mid_color": "#FFEB84", |
|
749
|
|
|
750
|
"max_color": "#63BE7B", |
|
751
|
|
|
752
|
}) |
|
753
|
|
|
754
|
ws.autofit() |
|
755
|
|
|
756
|
wb.close() |
|
757
|
|
|
758
|
Data table tips: |
|
759
|
|
|
760
|
Always use IFERROR wrappers — edge cases (negative equity, zero denominators) will produce \#DIV/0! or \#NUM!. |
|
761
|
|
|
762
|
Apply 3-color-scale conditional formatting to make the grid scannable at a glance. |
|
763
|
|
|
764
|
Label the corner cell clearly (e.g., "Growth \\ Multiple") so users know which axis is which. |
|
765
|
|
|
766
|
For financial models, common sensitivity pairs: Entry Multiple vs Exit Multiple, Revenue Growth vs EBITDA Margin, WACC vs Terminal Growth. |
|
767
|
|
|
768
|
Large Dataset Handling |
|
769
|
|
|
770
|
For datasets exceeding 100K rows, standard openpyxl will consume excessive memory. Use these strategies: |
|
771
|
|
|
772
|
xlsxwriter (streaming by default) |
|
773
|
|
|
774
|
import xlsxwriter |
|
775
|
|
|
776
|
wb = xlsxwriter.Workbook("large*dataset.xlsx", {"constant*memory": True}) |
|
777
|
|
|
778
|
ws = wb.add_worksheet() |
|
779
|
|
|
780
|
ws.write_row(0, 0, ["ID", "Name", "Value", "Category", "Date"]) |
|
781
|
|
|
782
|
for i in range(1, 1*000*001): |
|
783
|
|
|
784
|
ws.write*row(i, 0, [i, f"Item*{i}", i * 1.5, f"Cat_{i % 10}", "2026-01-01"]) |
|
785
|
|
|
786
|
wb.close() |
|
787
|
|
|
788
|
constant_memory: True — reduces RAM usage further by flushing rows to disk. Trade-off: you cannot go back and edit earlier rows. |
|
789
|
|
|
790
|
openpyxl write-only mode |
|
791
|
|
|
792
|
from openpyxl import Workbook |
|
793
|
|
|
794
|
wb = Workbook(write_only=True) |
|
795
|
|
|
796
|
ws = wb.create_sheet("Data") |
|
797
|
|
|
798
|
ws.append(["ID", "Name", "Value"]) |
|
799
|
|
|
800
|
for i in range(1, 500_001): |
|
801
|
|
|
802
|
ws.append([i, f"Item_{i}", i * 1.5]) |
|
803
|
|
|
804
|
wb.save("large_openpyxl.xlsx") |
|
805
|
|
|
806
|
write_only gotchas: |
|
807
|
|
|
808
|
Cannot read or modify cells after writing — append-only. |
|
809
|
|
|
810
|
Cannot add tables, merged cells, or conditional formatting. |
|
811
|
|
|
812
|
Cannot set column widths (use xlsxwriter if you need formatting + large data). |
|
813
|
|
|
814
|
Chunked reading of large files |
|
815
|
|
|
816
|
from openpyxl import load_workbook |
|
817
|
|
|
818
|
wb = load*workbook("huge*file.xlsx", read*only=True, data*only=True) |
|
819
|
|
|
820
|
ws = wb.active |
|
821
|
|
|
822
|
batch = [] |
|
823
|
|
|
824
|
for i, row in enumerate(ws.iter*rows(min*row=2, values_only=True)): |
|
825
|
|
|
826
|
batch.append(row) |
|
827
|
|
|
828
|
if len(batch) >= 10_000: |
|
829
|
|
|
830
|
process_batch(batch) |
|
831
|
|
|
832
|
batch = [] |
|
833
|
|
|
834
|
if batch: |
|
835
|
|
|
836
|
process_batch(batch) |
|
837
|
|
|
838
|
wb.close() |
|
839
|
|
|
840
|
pandas chunked processing |
|
841
|
|
|
842
|
import pandas as pd |
|
843
|
|
|
844
|
chunks = pd.read*excel("huge*file.xlsx", sheet_name="Data", engine="openpyxl", |
|
845
|
|
|
846
|
dtype={"ID": int, "Value": float}, |
|
847
|
|
|
848
|
usecols=["ID", "Name", "Value"]) |
|
849
|
|
|
850
|
for chunk in pd.read*csv("data.csv", chunksize=50*000): |
|
851
|
|
|
852
|
process(chunk) |
|
853
|
|
|
854
|
Performance comparison |
|
855
|
|
|
856
|
Method Max Rows (practical) RAM for 1M rows Formatting |
|
857
|
|
|
858
|
xlsxwriter constant_memory 1M+ ~200MB Full |
|
859
|
|
|
860
|
xlsxwriter normal 1M+ ~500MB Full |
|
861
|
|
|
862
|
openpyxl write_only 500K ~1GB None |
|
863
|
|
|
864
|
openpyxl normal 100K ~5GB Full |
|
865
|
|
|
866
|
pandas to_excel (xlsxwriter) 1M+ ~800MB Via engine |
|
867
|
|
|
868
|
Decision tree: |
|
869
|
|
|
870
|
Need formatting + large data -> xlsxwriter with constant_memory: True |
|
871
|
|
|
872
|
Need to read + modify large file -> openpyxl read_only + process + xlsxwriter output |
|
873
|
|
|
874
|
Just need data dump -> pandas to_excel with xlsxwriter engine |
|
875
|
|
|
876
|
Over 1M rows -> split across multiple sheets (Excel limit is 1,048,576 rows per sheet) |
|
877
|
|
|
878
|
Common Formula Patterns |
|
879
|
|
|
880
|
Need Formula |
|
881
|
|
|
882
|
Running total =SUM($B$2:B2) (drag down) |
|
883
|
|
|
884
|
Lookup (modern) =XLOOKUP(A2, Data!A:A, Data!C:C, "Not found") |
|
885
|
|
|
886
|
Lookup (compat) =VLOOKUP(A2, Data!A:C, 3, FALSE) |
|
887
|
|
|
888
|
Conditional sum =SUMIFS(C:C, A:A, "Widget", B:B, ">100") |
|
889
|
|
|
890
|
Count matching =COUNTIFS(A:A, "Active") |
|
891
|
|
|
892
|
Percent of total =B2/SUM($B$2:$B$100) |
|
893
|
|
|
894
|
Safe division =IFERROR(A2/B2, 0) |
|
895
|
|
|
896
|
Gotcha: When writing formulas from Python, use US-English function names and comma separators regardless of the user's locale. Excel translates on open. |
|
897
|
|
|
898
|
Number Format Codes |
|
899
|
|
|
900
|
Format Code |
|
901
|
|
|
902
|
Currency "$"#,##0.00 |
|
903
|
|
|
904
|
Thousands \#,##0 |
|
905
|
|
|
906
|
Percent 0.0% |
|
907
|
|
|
908
|
Date yyyy-mm-dd |
|
909
|
|
|
910
|
Negative in red \#,##0;[Red]-#,##0 |
|
911
|
|
|
912
|
Multiple (e.g. 2.5x) 0.0x |
|
913
|
|
|
914
|
Millions shorthand \#,##0.0,,"M" |
|
915
|
|
|
916
|
Accounting (neg in parens) "$"#,##0.00_);[Red]("$"#,##0.00) |
|
917
|
|
|
918
|
Data Gathering — Use Web Search When Relevant |
|
919
|
|
|
920
|
Before building the spreadsheet, determine whether the data requires external research. If the user asks for a report, analysis, or dataset about a public company, industry, market, or any publicly available information, use webSearch and webFetch to gather real data first. |
|
921
|
|
|
922
|
Examples that require web search: |
|
923
|
|
|
924
|
"Build me a financial model for Tesla" -> search for Tesla's latest 10-K/10-Q, revenue, margins, guidance |
|
925
|
|
|
926
|
"Create a comp table for SaaS companies" -> search for revenue, ARR, multiples, headcount |
|
927
|
|
|
928
|
"Make a spreadsheet comparing EV manufacturers" -> search for production numbers, market cap, deliveries |
|
929
|
|
|
930
|
"Summarize Apple's last 5 quarters" -> search for quarterly earnings data |
|
931
|
|
|
932
|
Do not fabricate numbers. If you cannot find a specific data point, leave the cell blank or mark it as "N/A -- not found" rather than guessing. Always cite the source (e.g., "Source: Tesla 10-K FY2025") in a notes row or sheet. |
|
933
|
|
|
934
|
Output |
|
935
|
|
|
936
|
Always present key findings and recommendations as a plaintext summary in chat, even when also generating files. The user should be able to understand the results without opening any files. |
|
937
|
|
|
938
|
Limitations |
|
939
|
|
|
940
|
Cannot write VBA macros (.xlsm requires keep_vba=True in openpyxl to preserve existing macros, not create them) |
|
941
|
|
|
942
|
Formulas are not computed by Python -- open in Excel/LibreOffice to see results |
|
943
|
|
|
944
|
openpyxl auto-width is an approximation (no font metrics); xlsxwriter's autofit() is better |
|
945
|
|
|
946
|
Google Sheets import may drop some conditional formatting and chart styles |
|
947
|
|
|
948
|
Cannot create native Excel PivotTables -- use formula-based or pandas-based summaries instead |
|
949
|
|
|
950
|
Cannot create native Excel Data Tables (What-If) -- use formula grids as equivalent |
|
951
|
|
|
952
|
Cross-workbook references show \#REF! until linked files are opened in Excel |
|
953
|
|
|
954
|
Excel row limit is 1,048,576 per sheet -- split larger datasets across sheets |