3 ms·

A while ago, there was a job offer in my area to automate Excel tasks using Python. The thing was posted time after time by several recruitment agencies, for al

by mkl95 5y ago

A while ago, there was a job offer in my area to automate Excel tasks using Python. The thing was posted time after time by several recruitment agencies, for almost a year. Just thinking how expensive that process must have been answers the question.

analog31 5y ago
I've done this kind of conversion, not as a job, but on a case by case basis within my own work. An example would be taking a simple computation that I would share with people (mostly engineers) in the form of a spreadsheet, and instead turning it into a Python program, even replete with GUI. Here are some of the pitfalls that I've experienced. If I share a computation as a spreadsheet, people already know how to work with the GUI, which is actually quite sophisticated, even if it doesn't prevent you from screwing up. People could extend my spreadsheets, e.g., adding columns to do the same computation on multiple input sets, etc. They could easily extract the output in text format and paste it into something else. And so forth. A Python program with GUI created the expectation that I was writing commercial quality "software," and that if it wasn't 100% intuitive, I would hand-hold each colleague, and make changes on demand, including converters to multiple file formats (often, so they could put the data back into a spreadsheet). And of course I also had to help each person install Python on their computer. Of course I'm not a full fledged software developer, just a "scientific" programmer, and of course the lesson I learned is common knowledge: Writing and supporting real software is orders of magnitude more costly than just writing a one-off program to solve a problem, to the point where the conversion to "proper code" could be a net liability to the business. You have to assess whether it will actually add value. In one sibling comment, it sounds like the answer was yes, so I acknowledge that. The difference was not so much the technology, but the cultural expectations associated with Excel versus "software." Today, I do all of my work in Python, but when I share a simple computation with colleagues, I will often convert it back into Excel for them.
- benhurmarcel 5y ago
  I’m doing this kind of task, and I tend to avoid any GUI and just have input and output files as simple text, CSV, or Excel spreadsheet. It seems to me that it’s the best way to have a satisfactory UX without too much headache.
  - lanstin 5y ago
    Or just have a download the source data for the graph/table in Excel button on every page/for every data source. Sometimes it is nice to do your own cool D3 chart but still have a no-work-for-me answer to requests for slightly different graphs.
jmkni 5y ago
I spent a big part of my early career automating Excel, initially via VBA, then Interop (.net), then later via the OpenXML libraries. I actually really enjoyed it, there's something a bit magical about taking a task that takes somebody hours to do, and putting it under a single button press, or even running it overnight and having everything ready for them when they start work the next day.
- Martyrred 5y ago
  That's what I'm doing right now. The company I'm at had this process for doing salespersons commissions that would take days to do. It was painful to watch the process the first time I was trained on it. They were sorting rows manually in excel, exporting csv's from the ERP software. Over a couple of months I worked automation into the project using SQL queries and vba/python, I even automated sending out all the personalized reports to each salesperson. Showed it to my boss last week (he had to run the reports this month) and he was blown away by how much time and energy it saved. It felt so good to reduce a process that took days to do down to a couple of button clicks.
  - jmkni 5y ago
    Class :)

by mkl95 5y ago

analog31 5y ago

I've done this kind of conversion, not as a job, but on a case by case basis within my own work. An example would be taking a simple computation that I would share with people (mostly engineers) in the form of a spreadsheet, and instead turning it into a Python program, even replete with GUI. Here are some of the pitfalls that I've experienced. If I share a computation as a spreadsheet, people already know how to work with the GUI, which is actually quite sophisticated, even if it doesn't prevent you from screwing up. People could extend my spreadsheets, e.g., adding columns to do the same computation on multiple input sets, etc. They could easily extract the output in text format and paste it into something else. And so forth. A Python program with GUI created the expectation that I was writing commercial quality "software," and that if it wasn't 100% intuitive, I would hand-hold each colleague, and make changes on demand, including converters to multiple file formats (often, so they could put the data back into a spreadsheet). And of course I also had to help each person install Python on their computer. Of course I'm not a full fledged software developer, just a "scientific" programmer, and of course the lesson I learned is common knowledge: Writing and supporting real software is orders of magnitude more costly than just writing a one-off program to solve a problem, to the point where the conversion to "proper code" could be a net liability to the business. You have to assess whether it will actually add value. In one sibling comment, it sounds like the answer was yes, so I acknowledge that. The difference was not so much the technology, but the cultural expectations associated with Excel versus "software." Today, I do all of my work in Python, but when I share a simple computation with colleagues, I will often convert it back into Excel for them.

benhurmarcel 5y ago
I’m doing this kind of task, and I tend to avoid any GUI and just have input and output files as simple text, CSV, or Excel spreadsheet. It seems to me that it’s the best way to have a satisfactory UX without too much headache.
- lanstin 5y ago
  Or just have a download the source data for the graph/table in Excel button on every page/for every data source. Sometimes it is nice to do your own cool D3 chart but still have a no-work-for-me answer to requests for slightly different graphs.

jmkni 5y ago

I spent a big part of my early career automating Excel, initially via VBA, then Interop (.net), then later via the OpenXML libraries. I actually really enjoyed it, there's something a bit magical about taking a task that takes somebody hours to do, and putting it under a single button press, or even running it overnight and having everything ready for them when they start work the next day.

Martyrred 5y ago
That's what I'm doing right now. The company I'm at had this process for doing salespersons commissions that would take days to do. It was painful to watch the process the first time I was trained on it. They were sorting rows manually in excel, exporting csv's from the ERP software. Over a couple of months I worked automation into the project using SQL queries and vba/python, I even automated sending out all the personalized reports to each salesperson. Showed it to my boss last week (he had to run the reports this month) and he was blown away by how much time and energy it saved. It felt so good to reduce a process that took days to do down to a couple of button clicks.
- jmkni 5y ago
  Class :)