r/learnpython • u/Whatever-it-takes322 • Nov 12 '22
Help with Python automation at work to free up time as single Dad.
Hi all,
Quite embarrassed about this and unclear how best to word it - I’m just going to be authentic and honest and see where it gets me—
The ask is about python and help in work task automation.
Here goes… Discovered Wife has been hiding serioud drug addiction problem. We have 3 kids. She became violent and eratic. She’s got to get better and is going to an inpatient treatment ctr. I will be Dad and Mom for forseeable future.
— I need guidance on how I can automate tasks at work (python), allowing me to complete those tasks more quickly. Trying to get my work done more quickly so I can be home and present as Dad to my kids. —
Details: I am a PM in an org that uses Excel for planning, roadmaps, risk/issue tracking, etc (I don’t know why). Its copy rows from one sheet, move to another sheet or report, change formats to match whatever new sheet format is. Creating ppt slides with inputs from excel sheets, modify to whatever ppt layout is used. Send status updates in email/slack, reports, etc. Hours of time counting / shading in cells based on lead time and take type. This is done for ~300 rows and the lead times are different depending on the item. I feel like these could all be automated or at least much of the manual input reduced.
(I can’t change what tools we use or how we use them. I’ve tried.)
Despite the tedious stuff it is truly a good job that I must at least try to maintain performance even with the current situation It is what supports us and what has helped put away enough money to cover all 3 kids’ college tuition, if they choose to go to go someday.
I do have some help from my Mom and Dad. I’m looking into daycare, etc.
Hoping this type of post is okay. If necessary, I am happy to direct message the mods and prove that my situation is real. I’ll also answer any questions asked in this thread aside from personal info.
Again, I appreciate this community and any help would be fantastic.
226
u/ireadyourmedrecord Nov 12 '22
That sounds... awful. Automate the Boring Stuff is where you want to go. https://automatetheboringstuff.com/
3
u/Whatever-it-takes322 Nov 13 '22
Okay, thank you I will check it out. I’ve seen it posted a lot on the sub. I started learning python about 4 months ago w/ Python for Everybody.
It’s for beginners but once I started to get into it, I was googling different / more advanced things python was capable of. That’s what led me here to ask about automation. I can see what can be done but I’m not at a level of understanding to write scripts that take data from one source, manipulate and output to another source. And I hate saying this because it sounds like an excuse, I just don’t know if I have the time to learn it all right now.
18
Nov 13 '22 edited Nov 13 '22
I don't think this sounds like a python problem IMHO.
You are doing most of your work in Excel as a data store and management then moving into another MS product PPT. Stay in the family IMHO.
VB scripting is what you need and it is simple to pickup since you are moving stuff around sheets in a workbook and style changes.
Can build interaction and forms then copy and paste right into PPT if you built all your visuals from Excel charts. You can even do all the styling into prebuilt layouts sheets then copy and paste into PPT. No more fussing once sized into the PPT template.
Macros. Two of my favorite sources:
TrumpExcel
Mrexcel
Glad your family is moving in the right direction and I hope it all works out. Hope she gets counseling as well past the addiction, since it sounds like she is self medicating for deeper issues.
3
u/darthnugget Nov 13 '22
Python is overkill, and the OP may have little time for scripting as well. I would look into Robotic Process Automation (RPA). UIPath has the free studio to get started.
2
Nov 14 '22
Robotic Process Automation
Interesting read, and I learned something new. Thanks for the share!
7
u/ireadyourmedrecord Nov 13 '22
I started learning python to automate stupid job busy work, too. It took me more than a year to get it dialed in, adding bits here and there, but it was always improving and ended up becoming the standard process for the entire company. Now I automate anything I can think of and it's been a lot of fun.
So don't try to do it all at once. Start with a small part and work out from there as you develop your skills. While you work through the book you'll start to make connections between what you're learning and what you want to do with it.
5
u/MidniteMustard Nov 13 '22 edited Nov 13 '22
I think /u/tojiy is correct that VBA is probably the better answer here, unless you are dead set on python for some reason.
The only limitation is that you may not be able to automate the e-mails/slack messages in VBA.
I’m not at a level of understanding to write scripts that take data from one source, manipulate and output to another source.
The key to programming is to break things up into micro tasks. Taking data from a source, manipulating it, and outputting it elsewhere are three different projects. Tackle them separately and THEN combine the skills.
Don't jump into this trying to manipulate actual work files; start with very basic spreadsheets.
And I hate saying this because it sounds like an excuse, I just don’t know if I have the time to learn it all right now.
You might not. And that's fine. Take care of yourself and your family first.
93
u/Luiginono Nov 12 '22
I did this as a PM for less authentic reasons than OP. Automate the boring stuff was the gateway into the whole process and I highly recommend.
One issue I had was that my work laptop was locked down by a draconian IT department. I was able to circumvent by developing on my home PC and then installing Python environment on USB and running off that. Just dont tell the help desk.
16
u/richpaul6806 Nov 13 '22
You can usually install python to your specific user rather than the whole computer which doesn’t require admin rights depending on how locked down they made it.
37
u/razzrazz- Nov 13 '22
Cant believe based on the OP's situation you guys are recommending to just "learn Python", especially given his circumstances and lack of time.
Sometimes the answer is 'hire someone' because he is going to up the stress of his life.
6
u/SE_WA_VT_FL_MN Nov 13 '22
Yeah, trading money for time is the way of the game here it seems. Trading time (learning) for time (automation savings) works, but it is a time value crisis. ....... I sound drunk and am not editing it. :)
Sorry for the rough situation. A freelancer is probably the place to look.
2
u/mewithoutMaverick Nov 13 '22
Not sure where he could hire someone for this? If he's a project (or even program) manager you often can't just hire someone to help you do your job. And he probably wants to be the one that is the always there constant for his kids - not bring a stranger into the house while mommy is in rehab.
1
u/razzrazz- Nov 13 '22
Not sure where he could hire someone for this?
Umm, anywhere online?
If he's a project (or even program) manager you often can't just hire someone to help you do your job.
Okay, hold on, do you understand the context of OP's question? This isn't something he's making his managers aware of anyway, he's not telling them he's going to automate anything, they almost likely wouldn't allow it and it would just automate him out of a job. Bringing a stranger to design it for him is probably not out of the ordinary.
And he probably wants to be the one that is the always there constant for his kids - not bring a stranger into the house while mommy is in rehab.
Wait, what do you mean "in his house"? You're telling this guy to learn a programming language which is no easy feat opposed to hiring someone to build what he needs so he can move on to the next thing.
1
u/mewithoutMaverick Nov 14 '22
I was referring to hiring someone to work at his company to help do house job. If you meant hire someone from the internet to do it for you personally and then you take that code to work...
Then my bad, I 100% agree lol.
I had worked for the government for too long so the idea of telling someone on the outside what you do so they can help code something never even crossed my mind.
53
u/kingpin_fisk Nov 12 '22
Would like to point out that disabling the running of unknown .exes, scripts and applications is an extremely common and effective security practice. So, quite the opposite to 'draconian'.
Though any IT department worth its salt would also block the USB stick
9
u/Luiginono Nov 12 '22
My use of the word draconian was more due to my demeanor towards the department and engagements on project.
Just wanted to point out some issues in companies with using python as a scripting lanuage. Especially if your goal is to be covert about it. Might have better luck with VBA.
12
Nov 13 '22
VBA is probably the way to go here. Also MS Office products all have lots of plugins that can be super useful and are unknown to most. They can be really useful for lots of project management tasks
4
u/Luiginono Nov 13 '22
Wish there was an Automate The Boring Stuff for VBA. It really was great, engaging content at the beginner level.
2
5
3
u/DangerWizzle Nov 12 '22
... Or just use Google Colab and push everything to Google Drive - that's what I'd do OP... Unless Google Colab is blocked
9
Nov 12 '22
No time for that I would pay someone to go check your stuff and assess if there is room for meaningful automation, then implement if so. Pay for time as time is what you need now.
2
2
u/spidertonic Nov 12 '22
Is there a version of this for R?
3
u/Crypt0Nihilist Nov 13 '22
Have a look at the No Starch Press, they publish AtBS and have a couple of R books. I've not read them myself, but generally publishers seem to maintain a standard. Obviously it's not going to be about automation due to R's focus on stats, but you can do anything in AtBS in R, so there's nothing to prevent you from learning the syntax elsewhere, then working through the chapters in R.
/r/rstats is a friendly place.
1
u/spidertonic Nov 13 '22
Thanks. I guess I’ll just look into doing it with python, I basically want to automate a bunch of excel stuff at work but they prefer if we use R because that’s what they know
2
u/Crypt0Nihilist Nov 13 '22
R has a few good packages for working with Excel. To be honest, I prefer working with R for data because that's what it's built for. Pandas in Python is great, but everything feels bolted on and bumpy. Visualisation in R is much better than Python in my opinion.
1
u/spidertonic Nov 13 '22
We mostly use R at work so I’d like to keep it in there if I can. So far my plans are importing excel to R, manipulating, exporting back to excel for coworkers who don’t code. This will save me a lot of time.
3
u/Crypt0Nihilist Nov 13 '22
That's dead easy. The hard part is the way people format things in Excel usually isn't programming friendly. To get you started, here's a tip. Check out the "janitor" package, it's got a nice function for turning column names into R-friendly column names that is better than the built in version. Remember to save the current ones as a variable so you can drop them back in before saving the results.
You may need to lock down their spreadsheets so they can't make small changes which makes sense to them, but screw up automation like changing headings, adding columns, moving everything down by a row or two etc.
4
u/TigerBloodWinning Nov 12 '22
Do you have any experience with python before this? What about Excel VBA (macros)? Are you asking how do you get started from 0 knowledge?
I do stuff in excel using the win32com library. Stuff very similar to your case.
3
u/Whatever-it-takes322 Nov 13 '22
Python: Completed Python for Everyone. It’s been 4 months.
VBA / Macros: Yes. No formal training but I’ve used it to do things within workbooks for years. VBA I do use but truthfully, I’m not writing it. It’s more like…. I want to do something w/in Excel faster so I’ll Google it. And, if there is a VBA code / snippet out there that fits I’ll copy it and paste it into the workbook module.
4
u/nimkeenator Nov 13 '22
I'm a little confused by this post - do you already know how to program in Python?
If you don't, learning to program is a time commitment you might not be able to meet. I remember that there are some automation tools available in Microsoft.
https://powerautomate.microsoft.com/en-us/
https://www.youtube.com/watch?v=IQ_KpBC8fwo&t=
Maybe these will help you. Good luck!
3
u/Whatever-it-takes322 Nov 13 '22
4 months of Python prior to this. Python for Everybody. So, I saw what could be done via python. Just do not have the time all of the sudden to get to a level where I can code it.
1
u/nimkeenator Nov 13 '22
Ah okay, that makes some sense. I don't know how far you've gotten but if you take a look at the automation text post up above that I saw, and it seems too complicated for you, then you probably need to put more time into it.
Hopefully the other two links I shared above can be of some help to you for at least some of the work you have to do.
47
u/publicfinance Nov 12 '22
Shading cells sounds like a conditional formatting issue. Should be very easy to have Excel shade everything for you. Happy to help.
56
u/alifone Nov 12 '22
Everyone is recommending Python for this guy when in reality he should just hire someone to create something for him, if he doesn't have time for his job he's going to have a tough time learning Python to any meaningful degree.
16
u/jonnytechno Nov 12 '22
I agree, OP should look at something simpler like automation tools such as https://zapier.com/ it has tons of prebuilt automation designed for apps he's using (slack, Excel)
4
u/Rj_LM Nov 13 '22
He's asking for help and someone to do it. Not if it's possible.
3
u/Whatever-it-takes322 Nov 13 '22
It’s not that I want someone to do it, it’s more that… I’m a beginner at Python. And, maybe I’ve just not searched for the right thing, but most videos/tutorials on automation are either very specific use cases or quite broad.
3
u/Silvus314 Nov 13 '22
so ATBS is all free and online, hop to the chapter about excel. It cover specifics and broader actions.
18
u/madmoneymcgee Nov 13 '22
I’d first check to see if you can do the excel stuff using excel macros and functions.
Python can do it too probably but keeping it in excel might be easier. So if something gets shaded red because it’s more than a week delayed there’s probably a way to evaluate that condition in excel.
That said, if you get stuck on something PM me.
5
1
u/baubleglue Nov 13 '22
same effort, depends on what you know better. To organize the code outside of VBA should be simpler.
14
u/daedalusesq Nov 12 '22
Automate the boring stuff for sure. I think just about everything you describe can be handled with Openpyxl
2
u/livetolearn1 Nov 14 '22
Glad you mentioned OpenPyXL.
Hi u/Whatever-it-takes322 - Recently I saw Automate Excel With Python - Python Excel Tutorial (OpenPyXL) Tech with Tim https://www.youtube.com/watch?v=7YS6YDQKFh0
I'm also trying to do some of this; although under better circumstance. I also tried Power Query with some data cleaning/remove irrelevant data & transformation/change data types, etc. since I know more about Excel and I'm just a Python newbie.
Like others mentioned, break down the steps/logic then start simple and build up from there. Wish you the best with family. Grateful for Reddit.
11
u/HorusTheSonOfOsiris Nov 13 '22
I’m an automation engineer and can help by writing scripts for you. Send me a dm if you like.
1
u/Tigerslovecows Mar 28 '24
Hi, would you have any resources you recommend like say YouTube videos, books or any documentation on automation? I have experience with Python and other programming languages but I can use some examples to help me get started. I work with excel often and I have a lot of repetitive tasks.
I’m currently working in a non-dev role. I’m looking to automate some of my tasks so that I can focus on sharpening my programming skills.
Any advice will be greatly appreciated.
8
u/chrispurcell Nov 12 '22
There are various libraries for interacting with CSV or spreadsheets, as well as for mail, and I've written a couple slack bits in Python. This should all be very achievable, even if you have to manually shade cells, automating the bulk of things should free up considerable time for you. As someone who recently went through a change to being responsible for everything, due to my wife's medical issues, I truly hope it all works out and you get to present in your kid's lives while still accomplishing your work goals.
8
u/annierockaway Nov 12 '22
Based on the Excel sheets and Slack updates, PowerAutomate could be a good solution for you without having to learn a new language.
20
6
u/Chip-San Nov 13 '22
This is the most emotional post I’ve ever seen on this sub. This is what code is used for, making people’s lives better. Although I can’t offer much help since I am still a beginner, I wish you the best of luck
5
u/fjzappa Nov 13 '22
Excel VBA can also help. Years ago I had a similar job and just got tired of the repetitive stuff. Ended up writing some VBA that would pull in data from 2 separate web-based databases, and merge it into a spreadsheet with color coding for thing that had changed in the past week and for items that hadn't changed in a month.
Got to where I could run my report in half an hour once a week. Everyone else on my team spent days keeping their similar reports up to date.
Once the boss found out what I was doing, I got to teach everyone else and then we took on a bunch more work, which helped us to survive a number of rounds of layoffs.
5
u/Msn_kr Nov 13 '22
Personally what I would do in this situation, I would google and learn one thing at a time here. As in, I would first google how to copy row and move to another with excel for everything on your checklist. How do you know what to report? If it’s a sequence you could manually type all sequences into a list, iterate over all objects and see if your current cell has any of those phrases or numbers etc. I don’t know about the rest, I’ve never worked with excel. Google won’t give you an answer to problems that require logic, so you need to sort the logic out first and ask the basic questions.
I know what it’s like to have a family member whose been addicted to drugs. Good luck with everything and I hope you can understand my logic.
6
u/jonnytechno Nov 12 '22
Try Zapier (automation tool) https://zapier.com/
It has pre-built tools for excel, slack & more & is much faster to build something with than Pyhon (if leaning Python is something you're new to)
3
u/Different_Suspect_30 Nov 13 '22
As a lot of people have mentioned it, It can be done but you need to know python and it can take time
My best advice would be to look for a freelancer who can do it for you, check out upwork you can hire a freelancer there
3
u/RhinoRhys Nov 13 '22
I'm sorry your family is going through this but honestly, even with 4 months of experience already, you're probably looking at a significant amount of time investment to learn, research, practice and write something that's going to do what you need. Unexpectedly becoming a single father is going to be struggle enough already in my opinion. You are already taking on all on the tasks your wife did, whilst still completing your own tasks, and then you want to add in development time on top.
Another concern I would raise is that if your productivity suddenly increases, how likely is it that your employer would just dump a load of extra tasks into your lap to fill up your freed time? Is the return on investment actually going to benefit you or will you have taken all this time to create something that only ends up benefiting the company?
On the other side of that coin, is this situation something that your employer could help with without you having to invest development time? Would it be worth talking to your employer about the possibility of going part time or reducing your workload, even if only for a month or two. There may be circumstances that you are not aware of that means someone else is available to give you a hand for a while. Certainly don't go in with demands or expectations but it can't hurt to ask and make them aware that your home life is suddenly much more complicated.
On the topic of hiring a freelancer, my concern would be that they would not understand the nuances of your job like you do. They might be able to create something a lot faster than you, but you would still have to invest time in making sure that what they are making is actually going to do what you need and not their interpretation of what they think you said. As a PM you're probably quite good at expressing your expectations and needs but it's still going to be a whole other project that you'll need to manage on the side.
And if I haven't dissuaded you yet and python is really the direction you want to take this, then the Pandas module will most certainly be your friend for excel manipulation and as other have said, automate the boring stuff is pretty much a course on doing exactly this sort of thing.
3
u/November-Wind Nov 13 '22
I wanted to do some basic automation stuff with Excel, too. Learned Python & openpyxl (module for Python). Did what I needed it to do.
In hindsight, there are better ways to do what I did. But honestly, that approach ain’t bad. Openpyxl is a straightforward module; basically 1:1 excel transactions automated via Python, which was convenient to organize my thinking at the beginning. There are easy/good training modules out there that can be purchased for cheap. (I found the openpyxl one taught by a Finnish guy named Connie to be very approachable).
Honestly, the biggest challenge for me was just installing Python with the free Visual Studio Code. Worth it in the end, but took some getting used to.
3
u/baubleglue Nov 13 '22
you need to estimate:
automatability
development maintenance effort
time saved (manual effort)
Its copy rows from one sheet, move to another sheet or report, [automatable]
change formats to match whatever new sheet format is. [automatable, if you can define what does it mean "new sheet format" in deterministic way or target Excel is formatted already]
Creating ppt slides with inputs from excel sheets, [not sure, the part which calculates input from Excel probably - yes, PPT file is depends on specifics]
modify to whatever ppt layout is used. [same a change format for Excel]
Send status updates in email/slack, reports, etc. [automatable, but only if you are completely confident in the results of the automation]
Hours of time counting / shading in cells based on lead time and take type. [automatable]
This is done for ~300 rows and the lead times ...
Besides the coding techniques, you need be able to organize it as a project and make reasonable design, for example I wouldn't design it as "automate manual process". I would consider reading data from input Excel (maybe into SQLite DB), then generate output Excel/PPT based on requirements.
data = read_data(excel_file)
excel_output_format = get_format(excel_file)
ppt_output_format = get_format(ppt_file)
excel_generate_results(data, excel_output_format)
ppt_generate_results(data, ppt_output_format)Using MS Office templates is another option to avoid dealing with formatting.
But again, I would start by evaluating ((dev effort) *stability) / (time saving) ratio. Reading raw data and making calculations is the easiest part (if the input format is consistent).
5
u/gravspeed Nov 13 '22
Hey man, single dad doing 40+ here too. Thank God for grandma and grandpa right?
You definitely want to be looking at visual basic for this, or maybe even autohotkey
4
u/Flaky_Ad_3217 Nov 13 '22
Hi OP,
The minutes I saw excel moving from one sheet to another, in my head VBA, there's nothing more simple and efficient in managing excel then VBA because VBA is build in with the whole Microsoft office suite, it's so much simpler when it come to copy data from excel to word to power point or any combination of it
2
2
u/notislant Nov 13 '22 edited Nov 13 '22
So you need some serious time investment, ive spent hours on scripts that may have only saved me an hour of time in total. Because it was more fun than the repitive task.
Then take into account all the time learning the language itself and modules etc.
If you have time to learn then it wont be too bad. I would watch basic python tutorials on youtube, constantly google and take notes so you can quickly jog your memory.
Then maybe start looking at 'python beginner excel automation' videos or pages. If you get stuck on certain things ive found 'reddit eli5 (term here)' really helps. You could likely skip object oriented for now at least if you just want to automate spreadsheets.
Couple very important things to note:
-People who successfully do this usually WFH.
-If people can see you do your work in half the time or lets say run a script for 5mins a day, they may look into just buying software or giving you more work (usually the case, nobody should know its automated if possible).
-if at work you would have to convince IT or management to allow python/IDE and other permissions which they nay be against.
If you need time much more than you need money, you could try making a post on fiverr or some freelance sites and see if you can get someone to do something simple like automate spreadsheets for a cheap price.
Though this wouldn't be as good if anything changed, you likely wouldnt know how to tweak it yourself.
You'd need to replace sensitive data with placeholder text or just write a template sheet with template values before handing it off.
2
u/ClimberMel Nov 13 '22
I have thousands of lines of VBA code as I has so much financial data in Excel. As others have mentioned, I you have no programming experience, that is probably best. If going the python route, pyexcel module make easy work of dealing with excel sheets from python. I have switched most of my programming from VBA to python since I can do it all without even being in excel to run it. If you have questions on specific problems let me know and I'll try to help.
2
u/anh86 Nov 13 '22
Python (and computer programming in general) is excellent for this type of task. The problem will be learning how to do it. It will take at least a few months to become proficient at Python scripting. It might be hard to find the time since your schedule just got so much busier. If you go for it, I wish you the best.
2
u/ZenCoding Nov 13 '22
Sorry to hear that story. I am too a father of three and I only can imagine what a stressful situation this is for you. Keep it up pal. Your kids will be thankful for all the sleepless nights you have these days. Some people already had good advices about automation. While it’s honorable that you want to keep the pace at work, it could be that this is not possible or not the most important priority. Have you talked to your manager about reducing work time? If this is a great job, I assume it’s not only because of the money but hopefully also because of the team and the management. My advice here would be to talk to the management, that you need more time for your family - you don’t need to tell all the facts. If the management is good, it knows that granding you less working hours is the only way keeping your performance and loyalty high. I don’t know how the working conditions and philosophy is in your company or country but I can tell it was the best decision in my life going part time, having much more time for the kids and also supporting my wife. If that is possible, do it.
I really wish you all the best. If you need personal advice just write a PM.
2
Nov 12 '22
As others said, Automate the Boring stuff. Or alternatively, break things down into smaller chunks and start from there. Google "How to get data from Excel sheet in python", then "how to make a PowerPoint presentation in python" then work your way up.
If you need some help, I'm quite free right now and my DMs are open.
1
1
Nov 13 '22
Okay so Python is a great tool for this. However, you might not have enough time to learn it for what you’re trying to do. There is a solution to your problem. From what you are saying you need, you might want to learn how to write VBA macros for your excel. It comes built into excel. Also, there is a record macro function with excel that will record all your key strokes and mouse clicks (It codes it for you). Python is hands down better than VBA, but this will do more than what you need in a reasonable time frame.
1
u/shartfuggins Nov 13 '22
Sorry to hear your story.
Your explanation of the tedium, this is begging for an overhaul in code.
My daily work has been mostly automation and workflow. I would be happy to talk over options and help you get a plan together. I can also lend some time on a limited basis. In my free time I usually spend a chunk of time on side projects anyway. Feel free to drop me a line.
1
u/RadAdam1989 Nov 13 '22
Nothing worse than a bunch of cunts that use excel for everything imaginable and refuse to even look at other options. Best of luck.
1
u/brettisstoked Nov 13 '22
If you use a lot of excel then just learn pandas.excel that’s what I use at work to do a lot of transforms
1
Nov 13 '22
Honestly the best way to "automate" is to find an assistant. Hire someone off of Fiverr in Bangladesh who will do a good job with little training for $3/hour.
1
u/neovegeto Nov 13 '22
Comming from a beginner data analyst side. The task sounds a lot like data cleaning.
So in order to do that, with Python, I would also recommend the use of SQL. Some manipulation is maybe easier in python, some in SQL.
As further work flow check out keywords like:
pandas and dataframe
read excel file into pandas
python and SQL
jupiter notebook, maybe online, to write and test cose
Github copilot, as additional help
As a data analyst, I would read the file into python. Create my tables, push the data to SQL. Do the table manipulation, come back to python for a savefile.
1
1
1
u/Wilson1981h Nov 13 '22
Have you got access to Microsoft power automate as I found that a lot quicker and simpler to get to grips with than Python
1
u/Metallfrosch Nov 13 '22
Hi fellow internet person:
Reading and Writing Excel: There are a billion tools for this to start. Pick one and see if it fits your needs. One starting point could be a tutorial like this: https://realpython.com/openpyxl-excel-spreadsheets-python/.
Changing formatting in text: Thats a two step process. 1. Understand the old format 2. Write into the new format. One tool to understand the old format are Regex. Here is a online tool to create regex expression: https://regexr.com/ . When you have it use the python regex library to use this on your excel text. For writing into new formats your could just use "f-string".
1
u/asterik-x Nov 13 '22
Shouldnt people be working in 8 hour job( time limit) Vs unlimited time till tasks are done?
1
u/Deep-Cow640 Nov 13 '22
I think the best way to automate this would be to use Python to create a script that can read in the Excel files and then perform the necessary actions based on the data. This would likely involve creating a series of functions that can read in the data, format it correctly, and then output it to the correct destination. Depending on the complexity of the Excel files and the desired output, this could be a relatively simple process or it could be quite complex. However, it would definitely save you a lot of time in the long run. And I could help with the project.
1
u/fasttoad_17 Nov 13 '22
Have you tried using VBA? A simple record macro to move and reformat your data may help improve that process without spending countless hours to learn python. Happy to help you if you need an example or two of what I’m talking about
1
u/BlakeBarnes00 Nov 13 '22
Automate the Boring Stuff is one that I've been recommended, but haven't put the time into using.
On another note: I hope your wife gets better, I'm a recovering addict to opiates, cocaine, and benzodiazepines. It was a wild ride getting sober, literally almost died doing so. The biggest advice I can say as an addict myself is that having people that care about me around, and constant stimulation from college and video games have saved me.
Best of luck to you and your family!
1
u/RaidZ3ro Nov 13 '22
If you're locked into MS Office, consider Power Automate. It'll fit nicely into your current suite of tools and you'll be more familiar with the terminology out of the box.
1
u/jdnewmil Nov 13 '22
Sounds rough. But you should be aware:
Automation rarely leads to more free time. Only negotiating fewer work hours will do that. Automation replaces manual workload with development and iterative improvement time, and fewer human errors in the workflow... but "work expands to fill the time available". This approach you are contemplating is far more likely to benefit your career than your family.
... it may indirectly benefit your family... if through showing good results you get leverage in reducing hours... but that is a long shot.
1
Nov 13 '22
I’d be happy to freelance automation for you. I’m a software engineer who uses python daily. I’ll work pretty cheaply, let me know!
1
u/dwpj65 I don't always test my code, but when I do, it's in production. Nov 13 '22
A reasonable degree of this sounds like a lower hanging fruit might be via Excel’s PowerQuery.
1
u/brigitvanloggem Nov 13 '22
Excel, like the other MS Office applications, has a full-fledged scripting language built in, called Visual Basic for Applications (VBA for short). Nothing to install, it’s already there. Buy yourself a good book on Excel VBA and off you go!
1
u/juancitoburritos Nov 13 '22
Sorry to hear about the tough situation you are in, but what a relief to hear your wife is getting some help.
There are a ton of great suggestions here already, but I think the main question comes back to this: Do you want to learn the programming yourself? It is a great skill to have, but now is not the best time to take on this learning given how busy you are with work and the family obligations. There were a few suggestions for hiring such tasks out on fiverr (using dummy data), or perhaps your organization already has a few people that could help you with automation scripts. Ask around to your data or programming teams.
Hope things get better for your wife soon, and hang in there.
1
u/leknarf52 Nov 13 '22
Sorry for the home life troubles. That sucks.
Python sure, but you’re a PM using excel. Have you thought about JIRA instead of sheets? That alone would be able to help you get more work done in a more meaningful way.
1
u/smjsmok Nov 13 '22
There are Python libs to achieve what you want, but if all the work is in Excel/MS Office, then maybe VBA (the built-in scripting language in MS Office) would be an easier choice. But I would have to see the exact tasks to offer some informed advice.
1
u/BerndiSterdi Nov 13 '22
You could also try MS Power Automate It basically requires no upfront knowledge but is a subscription
1
u/pconwell pconwell Nov 13 '22
You have a challenging road ahead (for many reasons), but once you get a feel for the basics of python, look into openpyxl. It's a library for working with Excel and over had great success with it.
Also, a good place to stay is probably sololearn. It's not the best resource, but it's probably the most accessible. You can do the exercises on your phone while you are doing whatever.
1
u/RamenJunkie Nov 13 '22
You might actually have some luck learning Microsoft's Power Tools if you have access. They are made to make reports from Excel files.
Thinks like Powerr BI Dashboards may be more suited to what you need.
1
u/troyboltonislife Nov 13 '22
i would use vba honestly. unless you are already approved to use python vba will be easier to set up
1
u/RonanPa Nov 14 '22
Python can certainly help you with those tasks. However, if you're starting and need results very fast, I'd first focus on the low-hanging fruit. Based on your description of the tasks, first things I'd do (directly in Excel):
Copy rows from one sheet and move to another with different formatting: format everything as a table (Insert->Table), filter the data you want, copy and "Paste values" in the other table. Instead of hand-formatting every time, you can use your previous reports' spreadsheet, delete the previous data and paste the new values. If needed, adjust formatting by copying the first row and pasting only the formatting into the rest of the data.
Creating ppt slides: same thing. Have the table pre-formatted and paste it directly into PowerPoint. Try the "Paste As" options. Maybe one of them retains the formatting the way you want (hopefully!). If that doesn't work, try playing with table styles and pasting the table into an existing file (copied from the previous report).
Send status updates in email: you can do this with Python and win32com, by automating Outlook. You can also do it from inside Excel with VBA (you ought to know a little VBA, even if doing everything in Python - you'll need to know something to port VBA examples to Python). If your needs are simple enough, VBA might be enough, though I'd start by having a simple text file with the email wording and manually copy/pasting it and attaching the files.
Hours of counting/shading: really? The best way to do that is directly inside Excel with conditional formatting. The trick is doing the formula in the last (new) column of the first row, check your absolute references ($A$1) are properly converted to relative (A1) and make a formula that outputs TRUE or FALSE depending on the shading you want. Copy the formula and make a new formula-based conditional format. This won't work with array/matrix formulae, but you can put those in a column and then use the result in the conditional formatting. You can the sort based on color, or use a new column to sort, or use a pivot table.
If that is not enough, then go to Python. It'll take a lot of time to learn the COM tricks, as well as the interfacing tricks. For that, the MSDN Excel VBA reference is your best tool. You can do the same for Outlook and PowerPoint. Also, Pandas and XlWings help if you are familiar with database-like joining and pivoting, but those are "advanced" techniques that take a lot of effort and mind-bending to master. I also recommend using the Spyder IDE - you can use F9 to execute lines or selections, which makes it faster to create code - just parameterize your variables and start trying in the interactive console. When the line does what you want, then copy it and paste in the editor, or make the line in the editor and run it with F9 after altering it, until it works.
As others have pointed out, make sure you're working in a clean directory, with copies of your original files, until you are confident you're not overwriting anything. I advise to not overwrite even when you are confident - I prefer to prefix my filenames with "YYYY-MM-DD" timestamps, so I keep the history of the works done.
Hope you are successful with recovering your wife and educating your kids. When you get to the hardest parts, remember that your family is more important than your job or any college. Trust God: keep carrying on and everything will turn out fine in the end.
1
u/lolercoptercrash Nov 16 '22
A lot of what you described you can actually do in Excel already. Conditional formatting, vlookups across sheets, making graphs that you can just screenshot and put into a presentation.
Since this is /r/learnpython, I bet you could do lots of this after a few months of practice.
1
u/InternationalMany6 Nov 18 '22 edited Apr 14 '24
Bruh, all I'm saying is precision beats guesswork any day. If you've nailed down every step, you'd know that VBA in Excel cuts through tedious tasks like butter. Ain't nothing like seeing live action in your cells while debugging.
And why Python for this simple task? Seems like using a chainsaw when you need a scalpel. Share the deets, screenshots or whatever, so we can dig into it more. Let's get it right, no fluff.
1
u/authorsuraj Dec 04 '22
I can relate to your automation task.
I have recently been developing tools to automate such stuff. Just to be clear, you might not need python.
Excel macro or power query can be sometimes sufficient.
If you know what you are doing and what you need, give it a try. All the best.
But as someone said, never and never work on your original files. Do it in separate folder.
But what if your company finds you what you are doing?
May be the best option would be to upgrade your skills and look for a better job?
1
Nov 12 '22
You should look into VBA not Python : https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office
Advantages:
already built into Office Suite (excel included) so no need to install anything
faster execution -> again already built into Excel
custom functions and classes for Excel workflow (Python has no idea what a Cell is, VBA does): https://learn.microsoft.com/en-us/office/vba/api/overview/excel
Hope this helps somehow
5
u/OptionEcstatic6579 Nov 13 '22
I'll also add Microsoft's Power Automate (Assuming you have access to Office 365.) But even if not, fully agree that VBA maybe a better integrated solution that's going to not run foul of the IT team (speaking as someone who made a courageous move to get Anaconda Python in an approved software list for.... 2 years.) Lots of macro recording options as well, so given a choice for time, this maybe the way forward.
Man, there's nothing to be embarrassed about. We all have to start somewhere irrespective of our situation. You've done the hard work of figuring out how to focus on a specific problem around which to learn. It's always better to start imperfectly today, than perfectly tomorrow.
1
u/OptionEcstatic6579 Nov 13 '22
Since you have such limited time with various commitments, I would strongly recommend something like Udemy to get started. If you sign in new, they tend to almost always have a discount on the course. For < $50, you have a good crowd-verified set of instructions to follow through.
The key for me on Udemy was always having the discipline to spend an hour every day or other day to train your mind to make it a habit.
You got this!
-2
u/13ass13ass Nov 13 '22
Learning python takes time even with all the resources available.
But you can cut that time WAY down by using gpt3 from open ai to write your scripts. Definitely incorporate it into your workflow. It will make your life much easier.
0
0
u/joek68130 Nov 13 '22
I know this is a python sub and I fully believe python to be better but look into R. It has libraries that can automate the development of the PowerPoint as well as transforming excel spreadsheets. Both are good options though
0
u/oakteaphone Nov 13 '22
It sounds like your job is to replace both MS Project and PowerBI or something...lmao
Hope you find out how to do what you're looking for...
0
u/Feinberg Nov 13 '22
You may want to look at autohotkey rather than python. I have found it faster and more flexible than python for automating gui based tasks. As a general rule, if you can write down a series of keystrokes to accomplish a task, AHK is the best tool. Frankly, I have yet to find a Windows task that can't be accelerated with AHK.
Unfortunately the language is just a bit clunky and weird, but once you get the hang of the assignment operators, the fiddly comparators, and the arbitrary feeling variable resolution, it's pretty quick and easy. And, to be fair, python has probably spoiled me a bit where all of that is concerned.
AutoHotKey isn't as good as python/selenium at handling browser-based operations, but it doesn't sound like that's what you're doing.
-1
Nov 13 '22 edited Nov 13 '22
Sorry you're going through this.
As far as the automation, I had similar tasks that I found easier to automate using VBA instead of Python. I'm probably in the minority, but manipulating things in excel is easier for me in VBA.
If you have any trouble with Python, maybe give VBA a shot.
-21
u/Se7enLC Nov 12 '22 edited Nov 13 '22
Soooo there's some ethical issues here. I'm all about using Python to make tasks more efficient. But this isn't about using Python to skip out on work early.
If you want to work 30 hours a week instead of 40 that's a conversation to have with your supervisor, not Reddit.
Edit: Wow, y'all really hate the idea of talking to your supervisors. /r/antiwork is leaking
2
u/Whatever-it-takes322 Nov 13 '22
I’m sorry but you couldn’t be more wrong. I’m not at all trying to be unethical or mislead my employer.
I’m on salary. It’s not skipping out on work. My goal is to remain effective at work while also maximizing the time I am present with my kids.
My performance is tied to the outcome of my work. Not hours logged. I manage large-scale programs that have tasks, deliverables, releases, etc. It doesn’t matter if it takes a person 100 hours per week or 30 hours per week to be effective. It’s about results.
Still, it is a lot of work and (as mentioned ) some of it is repetitive /admin related that could be optimized with automation. That’s is all.
1
5
u/ConqueefStador Nov 12 '22
Jesus Bootlicking Christ.
0
u/schyler523 Nov 13 '22
Went past licking the boot and now he’s throating the damn thing.
1
u/Se7enLC Nov 13 '22
I'm just glad my coworkers don't have the work ethic of the people in this sub, yikes.
-10
u/Se7enLC Nov 12 '22 edited Nov 12 '22
Seriously?
Python isn't a tool to generate free vacation hours.
There's a certain level of professional respect. If somebody says a task takes a certain amount of time, you should be able to assume they aren't lying and saying it took a week when really they secretly took half the week off.
2
u/mr_cesar Nov 13 '22 edited Nov 13 '22
Seriously?
Have you considered the option the OP is given an amount of work that forces him to work unpaid overtime or else he’ll lose his job, and what he wants to do is being able to reduce it? Why assume he wants to work less hours than what he agreed to in his contract?
0
u/Se7enLC Nov 13 '22
Why assume he wants to work less hours than what he agreed to in his contract?
Because he literally says "allowing me to complete those tasks more quickly. Trying to get my work done more quickly so I can be home and present as Dad to my kids."
Why would you assume forced unpaid overtime? Given the amount of detail in this post, that would be a real doozy of a thing to skip...
And actually, if that is the case, that makes my suggestion even MORE important. Discussing expectations of working hours with a supervisor is crucial. If there is too much work to do in the expected hours, being more efficient using Python Magic is just one option for addressing it. Adjusting workload is a very normal thing to do.
2
u/mr_cesar Nov 13 '22
"Allowing me to complete those tasks more quickly. Trying to get my work done more quickly so I can be home and present as Dad to my kids" does not equate to "working less time than what was agreed upon."
And why would I assume forced unpaid overtime? I don't know... perhaps more than 20 years of experience in the software development industry seeing how many colleagues have had to work like this because upper management usually set unrealistic deadlines and the team is still expected to deliver? I myself have also been a victim of this to a degree.
And given the OP "can’t change what tools we use or how we use them. I’ve tried," work overload seems to be a more likely scenario here (which is a far more common thing than what people tend to assume).
2
u/Se7enLC Nov 13 '22
Well if OP feels like chiming in to back up your assumption I'll eat my words.
If there's a problem with management setting unrealistic expectations that is not something Python can fix.
0
Nov 12 '22
[deleted]
-7
u/Se7enLC Nov 12 '22 edited Nov 12 '22
That's not how salary works.
And if they aren't being paid with an expectation of number of hours worked, the second part of my comment still applies. There should be no problem talking with a supervisor about working fewer hours.
Also, if they didn't want us talking about their business they wouldn't be posting about it on Reddit...
0
Nov 12 '22
[deleted]
-5
u/Se7enLC Nov 13 '22
Cool, I also have an anecdote.
Salary still comes with an expectation of how many hours you will be working. Just because you're not filling out a time card doesn't change that. Yeah it might be a little heavy when there's a deadline and a little light after to balance it out. But you can't just decide to work fewer hours on the regular without a conversation with a supervisor.
To be perfectly clear (and I thought my message WAS pretty clear), the important thing is to talk to your supervisor about the situation. If the employer really is just fine with fewer hours so long as the job gets done, there should be no problem getting their buy-in for that change in expectation, right?
I just object to the subtext of trying to automate your job so you can take the extra hours as free vacation. That's not the message this sub should be supporting. And it's not a realistic expectation that people should have going into careers in this field. And it's definitely not the kind of work ethic I would want to see in the people in interviewing.
3
u/Whatever-it-takes322 Nov 13 '22
I’m not sure if you have children and/or have dealt with a spouse that has developed a drug addiction. Either way, it’s not a “vacation”. It’s a nightmare right now.
Honestly man, I wouldn’t want this shit to happen to anyone. But, if it does ever happen to you, I hope people offer you help instead of coming to the bizarre conclusion that you’re simply looking for a “vacation”.
1
u/Se7enLC Nov 13 '22
I only called it "vacation" because that's what an employer calls time off with pay. Maybe your employer has sick time, family leave, whatever they name the bins.
Did not in any way mean to imply what you're going through is a vacation!
I still think it could be a good idea to talk with your supervisor. They don't need details, but no job is so important that it can't take a backseat for a while. Better to get ahead of it. Python probably won't be the magic you're hoping for, but hopefully it helps some.
1
Nov 13 '22
Not filling out a time card protects the company from paying overtime when shit hits the fan and you work 60 hours +. But the company also wants to be protected when there is less work to do and people have to stay there? I got an idea for the second case. "Get fucking time cards", otherwise deal with it. The "balancing out" almost never works out. And because of that a lot of companies don't have time cards. They calculate that the slackers cost them less, than paying the people the moment they would stamp in.
Finished the one project up to the next. "Why can't you work on the weekend? You did last project!"And how will this "talk" with the supervisor go?
"Can I work less hours, if I get the tasks done in less time?"
"Are you telling me that you were slacking all the time?"
"No I want to automize my tasks"
"Good thinking automize your work but I still need you here"
"Nah not worth it to spend my freetime to learn how to automize my tasks at work"
"I'm sorry we might have to let you go, this is not the work ethic we want to see around here"0
u/Se7enLC Nov 13 '22
If you're starting from the assumption that all employers are evil and they are just there to abuse their own employees, I guess?
That seems to be a common theme on Reddit. Right up there with "all landlords are evil" and "Netflix account sharing isn't the same as piracy".
I think your mock-conversation with the supervisor is kind of exactly my point. People on here pretending like it would be fine but they know full well it's not. They ARE expected to put in 40 hours. There is no standard pre-defined list of tasks that everyone completes and if you do it faster you go home early. We don't work in widget factories. Automating something tedious doesn't mean working fewer hours -- it means doing something less tedious with your time instead.
Some people see that as a punishment. The "reward" for being efficient is more work, right? It just never seemed like something that was a decision in the places I've worked. Of course I'm going to be efficient. That's part of what they are paying me for. I'm not just here to push buttons. I take pride in my work and wouldn't be happy just half-assing it.
1
Nov 13 '22
"They ARE expected to put in 40 hours." More often then not they are expected to stay until THE WORK IS DONE, even if it's not their fault that something takes longer. They can't have it both ways. There is no stay until it's done but when you are finished earlier you still have to stay.
When something takes longer: "We pay you to do this"
When it is finished earlier: " We don't pay you for the task, we pay you for the hour".
Why should someone get more efficient when there is no upside for him?
I learned coding to automise my work. I asked for more money because I'm more productive now. They said no, I turned in my notice of resignation and took my free days. Now they are bitching about it, and nobody of them can maintain the code because they can't code.1
u/Se7enLC Nov 13 '22
Why should someone get more efficient when there is no upside for him?
I guess there are just two kinds of people. Some people take pride in their work and are driven to do it well. Just phoning it in every day isn't an option for them. If the job isn't challenging they lose motivation and look for a different job. And similarly, if they feel like their work isn't appropriately appreciated/compensated they will look elsewhere.
I think the real question is, "who wants to work for a company that doesn't reward better work?"
The other kind of person does, I guess. The people that prefer the comfort of the expected. The job is the same every day. They know what's expected of them and they have their established process.
I think my perception of what employers expect might only be true where engineering/programming IS the job. Innovation like scripting things to be efficient is encouraged. Really it's just expected. If I were doing something manually that could have been scripted I would have to answer for why I was wasting time doing it wrong. They want my time spent learning new skills and improving rather than doing busy work. And that's what I'd much rather be doing, too!
But it sounds like the type of job you described doesn't work that way. Maybe employers for jobs like that don't have the same expectations. Instead of wanting their employees to grow they just want them to stay in their box and churn out more Dinglehoppers.
Congratulations on getting out and good luck finding an employer that values skill development.
1
Nov 13 '22
Thanks,
already got a new one. Also pays way better.
Scripting and automation is fun, and good for you when you get time to improve/learn while you are on the job. Most people don't get this opportunity, because time of learning isn't meassurable. I don't knnow where you are from. In Germany I get default 24 paid days off , in my new company it is 35, also when you are sick you get full pay for up to six weeks. I heard in USA you might not even get paid time off at all, or you get it after several years. Also the social benefits might be better in the company with the unpleasent jobs. All stuff you need to consider if you want to change employers. Especially when you have a family you have to support ...
0
Nov 12 '22
[deleted]
3
u/Flat-Speaker566 Nov 12 '22
VBA might be a better shout for you, you can make the macros global and add them as buttons on your ribbon in excel so that you can run them on any sheet.
This forum is pretty good if you decide to go down the VBA route:
238
u/jimtk IdontChat Nov 12 '22
The problem you have is that learning python will consume a ton of time.
Yes a lot of what you described can be automated. Rapidly, for someone who knows what he's doing but getting there will require a fair amount of work (read time!).
VERY IMPORTANT: Create a development environment with A COPY OF THE FILES you need for coding and testing you programs. NEVER, EVER WORK on the files used at the office, make your own copy. Breaking an important file will set you back a days, weeks or months and everybody will hate you.
Good luck, on all fronts!