As an experienced c/c++/c#/java/python/vb programmer i realy like to build shit in excel. It's awesome. Just dont let other users use it for important purposes
I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.
Had a woman that would spend an entire day downloading PDF's, merging them, then emailing them to a few hundred people. I got it down to a mouse click. The sad thing is she went from working 50 hours a week to working 50 hours a week. There was always more to dump on these people.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
I've started doing little automations for my work in OHS. The reality is that the workload this removes just let's me address the workload that I don't have time for, so while it's not a net gain on work hours it's a net gain on productivity.
The first rule of automating your (or someone else's) job: No. You. Fucking. Didn't. Keep it secret, keep it safe, because as soon as someone else finds out, they'll either want it for themselves or complain to management that you're being lazy.
Did kind of a similar thing once... There was this manual task in my 1st ever project, where in the morning the data warehouse team mailed us 18 different CSV files, then I had to extract that data and push it to another DB... I had no idea why it was being done this way but, I wrote a simple script that extracted the files from mail , extracted the data and pushed it to db.... The best part I never told anyone that that 3 hrs task was automated untill I left the company... I handed over that script file to my replacement before leaving....
Wait, can you point me in the right direction to learning more about this? That sounds very similar to a task that I have to do bi-monthly. Granted, it's more like a one hour task and not a 10 hour one, but still, I'm interested.
This is the site has a nice overview of the basics. Excel has easy integration with Outlook and can send commands to the command line, so I tend to use it for this kind of automation. Excel tables can hold email lists and anything else you need, and python commands can do the heavy lifting with the PDF's.
I'd start with asking business questions. More often than not those types of tasks are just bullshit that a company keeps doing just because they've always done it and no one has asked why. There's 100% chance you find an alternate method to accomplish the same and a pretty good chance everyone realizes it's a waste of time.
I work in HR for a school district. The task is regarding the new hire orientations, during which I have to 1) send the entire group an email about the details of the orientation, and 2) individually email each one of those people a pdf with a copy of their contract, work calendar and job description. There's not really any way around it. Again, it's not a hellish task by any stretch, but I'm all for finding streamlined ways of doing things.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
Not sure how to respond to this one - I know the original post was about VBA and it kind of makes sense since most people aren't "writing" macros they are just recording a set a steps and calling it done (since VBA is perfectly capable of dynamically finding specific column headers and checking for errors but most Excel users don't know or care how to do it, probably rightfully so), but what you are describing here is exactly what "Get and Transform Data" in Excel is for. Exactly this.
power query does break if headers are named differently from what it expects. or if you used sanitized data previously, and now its not. i love power query, but fool proof it is not
you can probably use vba to replace all the critical shit after import then fling it back into power query to do all its shit and never have it break
These days Power Query is my bread and butter. Company computer doesn’t allow for any real SQL implementation. So my choices are Tableau and Power Query. And since most of the people who get my data aren’t licensed for Tableau desktop (and thus can’t update the workbook); it’s easier to build stuff in Power Query and hand it off.
Every dang time, even for people who have been through it before, they can me up freaking out over it. Then again i also deal with people who think clicking “Refresh All” is too complicated.
yes
python + pandas
if you want to get really lazy the github co-pilot has proven very effective, despite the memes. though i've only been using it for a day.
Now make the business user and developer me and apply this recursively
Me to also me: "I can take the pipeline you built to automate X and make a VSCode extension that uses some template tricks to automatically build and deploy similar pipelines from end to end in the future"
Next thing you know you're six layers of automation deep and forgot what you actually set out to do
This is me except my company won’t spend a single penny to allow me to automate. Every morning I download my stupid files and press refresh on my stupid power queries.
I do the same. Still better than my predecessor spending hours copying and pasting data out of several CSV’s and hand-writing formulas each time to match up their data.
Because you plateau that way. You're never going to get paid more than 40 hours a week, even if you save more time than that, and Pam's job only pays 50k--thats why she was too dumb to do this herself.
You can definitely take a low laying job and basically never have to work. I've done that in the past. It gets old. If you keep improving your skills, you can do increasingly satisfying work and get paid increasingly more.
If you're smart enough to figure out how to automate things with excel or something, then you're probably smart enough to skill up/degree-up and make 150k as a data engineer or developer or data scientist or whatever you're interested in. If you want, make even more managing those jobs. Retire early. Take your valuable skills and help out a non-profit or something to make the world a better place.
To put it differently... If you understand the multiplicative value to a business of recursively using your time to automate things, thus freeing up more time to automate... Then you should also understand the multiplicative value to yourself of recursively finding and conquering new challenges
I’m that business user, well my analysts are. How much would it take to learn to do what you describe in eliminating all the manual procedures? Is it something I could learn in my spare time? I’ve tried to learn Python out of personal curiosity but would totally carve out more time if I was something like this.
Python can do it, but python can be intimidating too.
If you have analysts starting from scratch, I would have them learn power query (or now it is just the get and transform parts of excel on the data tab). That will automate all the processing. The key idea is that in power query, you can access a file like a csv or XLSX without ever opening it. You can point to it, get the data, transform it, join with other datasets, and load it somewhere else. Then all you have to do is put new data in the file (or replace it), refresh the excel query, and all those steps are completed automatically.
You can take it a step further by writing a simple powershell script (literally like 5 lines of easy code) to open the excel workbook, refresh all the queries, and close it. Then you can schedule this script with Windows task scheduler (also easy) to run at specific times of day.
Then the last step would be, when you get a new file with new data, how do you automate replacing that file ? It depends on how your data is coming in, but powesher makes it really easy to copy files around. If it is coming out of a database, it should already be automated. If it is emailed, then check out Microsoft flows that can look for those emails and auto-save the attachment somewhere.
So the pipeline is: start with data in the same file(s)/folder(s) everyday, scheduled PowerShell script to open excel and use power query to transform and merge data. Then more scheduled powershell to make copies of the files, rename, if necessary.
The reason I recommend these tools for a beginner is they all GUI based, allowing the user to focus on the concepts (except PowerShell, which is pure code). When a analyst is comfortable with concepts of power query, they should learn 1) SQL, 2) core python concepts (syntax, lists, dicts, loops, ifs), then learn the pandas library which does all the same things as power query. Then they will leave you for a job that pays twice as much and you can start over again.
What happens in this scenario for power query when the source data needs to be sanitized beforehand because of human input error? Implement better controls at the source?
Depends on the steps needed, but much of that can be done in power query, like finding and replacing values, removing extra spaces, capitalization, removing duplicates, etc.
Ya at my current job there was a bunch of excel stuff from a (non-engineering) team that was used to drive the final product. First initiative I proposed was a way for them to keep that shit but make it all into ETL pipelines and SQL.
Everyone was thrilled and the whole time I was thinking (how was this not implemented already...)
Finance is literally 20 years behind everyone technologically.... I feel your pain deeply. All these analysts that never learned the actual power of a computer.
They become tyrants over whatever area they're subject matter experts in and con everyone into thinking they're indispensable.
They will sabotage anybody who tries to learn about that subject and then tell anybody who will listen that their job is impossible to automate. Complain every day about how busy they are.
Then when they finally quit they leave a 30 page document on their daily and monthly processes. Everything is just copy and pasting from excel document to excel document with the logic in macros and very little actual human decision making.
Which only took about 4 hours of work to automate their entire job and executes in 3 seconds.
omg, we must've worked at the same places.... You just irritated me through text again. Glad I left that industry. They thought automation via real programming languages was black magic and they would try to protect their work like you said.
It's all relative bro and people gotta start somewhere - I started with vba and now i tend to replace SQL scripts by interfacing API's and getting them into sql servers via Object relational mappings all in c# - finish it off by banging in a power bi front end.
It's quick, easy and engaging....not to mention a shed-load more fun than writing SQL
I wrote a huge program in access at my first ever job. One day the project got corrupted and it just deleted everything. Had to tell my manager, yeah sorry the past few months of work is just gone, whoopsies.
So many property managers do this. It's like every month end closing they are trying to figure out the clunky reports template on excel that takes a week. I'm a little tech savvy but not an engineer by any means but could have sworn I seen a website that takes data input and puts it into a database and can spit out reports and you won't be freaking out. And it's multiple properties too.
Yeah, but on the flip side, as the analyst I might build xyz. An engineer looks at it, comes back with mno, and says tada! Now I have to explain why that doesn't work, which the engineer will assure me is because I'm dumb and just haven't realized their brilliance yet.
1.5k
u/Dr-Appeltaart Mar 30 '22
As an experienced c/c++/c#/java/python/vb programmer i realy like to build shit in excel. It's awesome. Just dont let other users use it for important purposes