Hey everyone! I'm currently facing a challenging task as an intern where I need to automate a pricing sheet process in Excel using Python. I have a template sheet with multiple tabs, and I need to replicate the formulas and data into around 1800 different pricing files. Each of these sheets has a different structure, with varying numbers of tabs and formatting.
I'm working with a complex formula that I want to apply to rows underneath a specific "YTD" marker, but I'm running into issues with dates when trying to insert it through my Python script. I'm using xlwings and have made a decent amount of progress, but I'm stuck on some parts. I've been coding for three years at Boston University and even using AI to assist me.
I really appreciate any insights, suggestions, or strategies you might have to help me navigate this challenge!
2 Answers
I'm impressed with how much you're trying to automate! If the formula works manually but fails when inserted programmatically, maybe the issue lies in how it's being flattened or formatted before insertion. Try to construct the formula part by part to see where it fails. Also, make sure you’re setting the cell formats correctly in Python as mismatched formats can cause errors too.
This sounds like a tough situation! It’s clear you’ve set up a solid framework for your automation. Handling 1800 files must be overwhelming! Since you mentioned that the date criteria is causing issues, I’d recommend isolating that part of your formula first to troubleshoot. Maybe test with a smaller dataset or create a simple version of your data to see where it breaks down. Also, writing unit tests for your script could help ensure everything works as expected as you make changes.
Definitely agree with testing your dates separately! Sometimes, the formatting in Excel can throw off Python scripts. It might also help to make sure that the date values are in the format that Excel expects before inserting them.
Thanks for the tips! I’ll definitely check how I'm formatting and inserting the formulas. It’s tricky, but I appreciate the help!