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

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...

Copyright of this story solely belongs to hackernoon.com. To see the full text click HERE