Menu

How to Generate, Recalculate, and Validate Excel Workbooks With Python
📰

How to Generate, Recalculate, and Validate Excel Workbooks With Python

#hackernoon#technology#name#detail#summary
Reading 0:00
15s threshold

The fastest way to lose a finance team is to send them a number they can't click on.

A dashboard shows a total. Finance wants to see how the total was built: which rows, which filter, which formula. If they can't see it, they export the data and rebuild it in Excel, and now there are two numbers and a meeting about why they differ.

So a lot of what I automate for finance ships as a workbook. The detail rows sit on one sheet. The summary cells are formulas that point at them. Click a customer's revenue and you see =SUMIFS(detail!$D$2:$D$7,detail!$B$2:$B$7,A2) . Anyone can audit it in the tool they already trust, without asking me a thing.

Generating those workbooks from Python is easy. Generating them so they're actually right takes a few extra steps that nobody mentions until something breaks.

Writing formulas with openpyxl

openpyxl writes any string that starts with = as a formula.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.