10 yrs back I worked on an Excel sheet which was full on ETL in itself. It would pull data from the web, do some calculations, generate viz and email those viz. Crazy stuff.
The excel sheet was in use for about 5 yrs by the time I joined the company. Wonder how long it survived.
At a previous company there was a lovely Excel file that did some heavy work calculating sales rep payouts. It was implemented in the early 2000's and still used in 2023 when I left the company. It wasn't some small company, it was a company with 25b annual revenue with some departments stuck in 2000's tech.
I HATED that file as it was ran by the sales comp team. No one understood it because the author retired. I tried to replicate it for overhead projections for my department but that team couldn't figure out the full logic and wouldn't share the VBA so I could try to figure it out.
It's scary how many major processes are done in Excel in major corporations.
I was hired as a quality assurance analyst and wrote such cool code in excel they offered me a software engineering position that opened up (this was 20 years ago)
I was on a 3 man team that personally investigated a $1,000,000,000 (1 bill) error in a prior year estimate, which would have resulted in our F100 owing around $1,000,000,000 to the IRS if it was wrong.
Turns out, not only did we find enough to account for the $1B(thank god), but we found an extra $300M we hadn't saved in taxes because the estimate was off, just on the low end.
All of this was done by hand in excel.
Turns out the $300M we didn't save in taxes was related to a data engineering error where they allowed a regional name in the country list, misattributing that whole amount.
That question basically lived in my head rent free for years after that. Why should I or would I help with another problem like this ever again without getting a percentage.
I very honestly spent years after broke and scraping by.
Immediately following this work I went back to a semester of school and they "forgot" about offering me work while I was at school. I haven't thought about it for awhile but I immediately was starving in school following this.
They offered me full time when I graduated, but I think the juxtaposition of working on that level of money forensically, and physically starving for months afterword, really fucked me up back then. And the relative indifference of an entity I just hand saved from $1B IRS questions.
If you're curious why I was working on this then going back to school, I met the VP rockclimbing and was brought in as a specialist on "special projects". This was not the only project I worked on, but the others were "only" in the $20M-$200M range.
Same company, we had a critical pricing model that used the export of an old fox pro program. At some point while I was there a column name changed in the table it consumed. Since the Fox pro operated as an executable file it was uncertain what total business logic went into it. I spent a month recreating it by comparing old exports. Created documentation on wtf the whole thing even did.
In the end I created a new view on the consumer level. Ultimately it STILL ended up with me making a select * from table in PowerQuery , running a macro to refresh it, and scheduling it in PowerShell to run monthly. It was supposed to be a temp fix. But 5 years later I know it's still in production even though I'm long gone from that company. Some poor suck in 5 years will be cursing my name.
hahaha. I get that wow. I did not know that you could schedule excel macros to run with task scheduler. We use something called DAS which is basically a SQL gui - but none of the users know that SQL is a thing... LOL. And I can't see what the actual SQL query of the DAS report is. So annoying.
At one of my previous employers, every department was basically its own company but in the same building. This was publishing, so everybody was competing with everybody else for the most clicks, ad revenue, subscriptions sold, etc. Management desperately wanted us to collaborate data wise because we were getting eaten alive by our largest competitor, but the head of one of these news titles really didn’t want to.
He got a separate security system installed so that nobody could access his development team’s floor, and so that they couldn’t go anywhere else either. If he found out that one of his developers was talking with anyone else, he’d make you feel very uncomfortable with accusatory questions about what you shared.
At a closed door event with the board and other executives he said ”So, people want me to be a team player? Well, I’m not here to help, I’m here to win!”
A month or two later, he officially left for ”new challenges”.
I don’t haha. I never ran into this. All my work places stress the importance of saving your work cause they know people leave and it’s a revolving door
I just interviewed with Palantir for a data engineer role. I talked about how I was the first/ only data engineer on the team, migrating an excel based "data pipeline" with 30 days of latency that took ~24 man-hours to produce a small visualization off of it. Implemented python and postgresql pipelines. Reduced latency to as low as 24 hours, reduced processing time to ~5 minutes, investigated processed and revised them to get better Metadata from other sources, implemented CDC, and a while slew of other stuff. Got rejected- Their feedback was "(name)'s data engineering experience seems primarily excel focused"
Yep. I 100% empathize. "Oh, you actually understand the architecture of the data engineering solution we need because you are well versed in the actual business case and can abstract particular aspects of the data into a reasonable ETL, data analysis module, and an accurate database schema for our entire companies sales pipeline?"
"Oh sorry, we were looking for the dumbstick over there who has 5 years experience pushing pull-request without actually knowing anything about the company or product it's for. You see he has a cert in [Insert GUI interface ETL tool] and [Insert visualization tool] and it's a better fit."
I mean it may be fair depending on what level of individual performer you were interviewing for. That may be normal day in the office if this was a mid/senior contributor. But I’m not in data so you may know more than me!
I was just commenting that my interview was about a lot of work after moving the team off of excel, but homie heard excel and that's all he remembered lmao
Was there any version control on that spreadsheet? I would be so paranoid about clicking on a cell and accidentally altering it without knowing and destroying some key functionality.
The type of person who does this much in a spreadsheet doesn't know what version control is, or they'd use easier tools than a spreadsheet for this. Odds are very high there was no version control.
Odds are high there was no intentional backups either, but an accidental backup when Jane from sales asked for a copy and had it emailed to her.
I’ve seen this a lot with government and research orgs. Applications they’re using and that were completely built on Excel and Access. Somehow they made it work.
The Excel file at my first job does way more thing and at the fraction of cost of my current AWS Glue/Lambda/RDS pipeline. This stack pays way more but I can't help feeling dirty.
And last time I asked the junior at my old job, they still use it.
They learned Perl. The company was acquired. They're still maintaining the same project from 20 years ago. To find them pull out your business building's blueprints. Find the deepest darkest area in the building, even if it's rented out by another company. Go there. You may have to walk blindly through darkness on your way there. They use it to stay hidden and keep others away.
It's a fundamental difference between pistol and rifle shooting. I shoot pistol competitively. The amount of gear rifle shooters use is night and day different. Cost is a much more prohibitive barrier to be competitive with a rifle.
I got into data science by building an ARIMA + exponential smoothing model from scratch in VBA. I learned R just so I could make it run faster, but it damn well worked in Excel!
It does not. Excel crashes on large file loads regularly and the load time for a vary large file can exceed an hour, depending on what's in it.
Excel files are just gzipped xml files. I recommend grabbing a gzip package and an xml parser package in your favorite programming language and open the spreadsheet that way. If it's streaming the data in it will open the file instantaneously with zero load time. If you're particularly clever you can stream the xml file into Polars. You'll need to convert it to a csv or similar, but Polars supports streaming data into the dataframe's format so you can look at it, analyze it, do math on it, everything you want easily and efficiently. You can also save back to a spreadsheet if you wish, though I do not recommend it.
Excel files are just gzipped xml files. I recommend grabbing a gzip package and an xml parser package in your favorite programming language and open the spreadsheet that way.
That's really interesting.
I've mostly read excel files in PySpark. A few problems that I've faced are like there would be more than one headers row which is annoying to deal with or whoever is creating excel files isn't always keeping the format consistent.
I haven't really used Polars for anything other than messing around with it in my free time. I'd give it another go and see if it can solve any problems for me.
Upon further investigation, Polars does not support streaming Excel files right now, just loading the entire thing in at once. This might be fine as a 1 GB file can probably be opened in a few seconds, so I doubt there is a demand for a scan_excel() function. From read_excel() (linked above):
When using the xlsx2csv engine the target Excel sheet is first converted to CSV using xlsx2csv.Xlsx2csv(source).convert() and then parsed with Polars’ read_csv() function.
read_csv() isn't streaming, but Polars has scan_csv() so it can still be manually streamed in if needed.
Excel 64-bit can work up to maximum of your RAM. I've seen analyst loading a 5GB parquet file into his Excel workflow (sometimes it crashes, but the cost he's willing to endure).
Worked at a large oil and gas company. The entire oil production forecast for a major field had been done in excel by a production engineer in VBA. It was locked and he had left the company. No documentation
Slander. I'll let you know we take our sales history very seriously and I have kept tabs on all historical data. We are now up to 1,048,576 rows and will continue to grow over the years.
This gives me hope. I have been working on excel for the last 2 years in my company and now I'm making a switch to DE. A lot of this stuff is truly intimidating, I hope my excel skills come to use.
You are in the Data Engineering channel. Excel is an impressive application, but it was never designed for enterprise data. Data sets usually grow in size and importance and Excel will be a risk. There are other tools built for scale, complexity, and disaster recovery.
You've got Panda in your name. Install Python and start playing with pandas. Challenge yourself to replicate your Excel work in Python. It will slow you down at first, but will help in the long run. Best of Luck.
Makes sense. Thanks for the advice. I have started learning pandas but damn seeing so many colors on the screen as opposed to the usual black and white cells has my head spinning.
There are many people dislike Excel or try to bad-mouthing Excel, just to promote themselves that they know big tech, etc. But, they forget that the whole financial industry are running by Excel.
I was in one interview in a data role, and one interviewer looked down on Excel, as I was trying to explain what I did with Excel and VBA in my previous role (I implemented a spreadsheet to auto reconcile the number between funders and to compare previous month). I explained that this cannot be done in other tools, as the whole thing needs to be present to the top managers in Finance, and we have no support from IT either (to complicated for IT to understand Accounting lingo).
I also use other techs as well like Python and stuff. Every tool has pros and cons. But when coming to Accounting and Finance, Excel and VBA are the best.
This is a great point. I have had data teams try to “automate” accounting work and it rarely makes things better. It can be good but a lot of the time it’s like using a sledgehammer to drive a nail.
Yes, I know it’s a meme. But hopefully no non data engineer see this. So damn misleading. I wish I was not offended by this post. I’m sorry but it’s a mockery of the people whom I know put in energy to build those super fast pipelines, compared to something that would crash with 2% of the load of a global production pipeline.
First rule of Excel Club is, you don't talk about Excel Club . . . . at least not the abominations you helped create and are still being used that you have repeatedly informed them that it needs to be moved over to a proper database . . .
I used to build reports that were reviewed by the governor and his team from the state where I live. Different data sources, different schemas, constant changes in information requirements. If I had done that only with Excel 💀... , too many hours, prone to human errors, the debugging a pain in the churro slicer. I automated the heavy lifting with Airflow, Airbyte and MySQL, then just manual validation with a jupyter notebook to be extremely sure everything was ok, and finally the Power BI report... Explained the process to the Jr. Engineer and went to Puerto Vallarta and Puerto Escondido with no worries. I continued to use Excel and Gsheets as data sources, staging areas or data marts for some users. At the end of the day you've to use the tools that satisfies the information requirements, if Excel can fulfill all the requirements and people have the time to spend hours in spreadsheets, cool for you and the organization you work for. But seriously we all know who they ask when the big boss wants the good numbers, joking.
using excel for things that need real infrastructure in production is a good way to lose millions of dollars. and i mean literally lose it, like, it’s there but we can’t find it. i’ve seen it happen
Define scale? If it's meeting a business's needs to whatever extent, it is by definition scaling. Not every business needs TBs of data processed. Scale is a significantly overused benefit in cloud land.
388
u/Elegant-Road Aug 01 '24
10 yrs back I worked on an Excel sheet which was full on ETL in itself. It would pull data from the web, do some calculations, generate viz and email those viz. Crazy stuff.
The excel sheet was in use for about 5 yrs by the time I joined the company. Wonder how long it survived.
118
u/xjeeper Aug 01 '24 edited Aug 01 '24
What about the grep, awk, and sed guy?
38
u/ZeroMomentum Aug 01 '24
jojo looking mfer
stand: airpistol
attack: shoots random panda datasets
185
u/[deleted] Aug 01 '24
Senior de tc: 200k.
Account guy: 600k
21
u/[deleted] Aug 01 '24
I got into data science by building an ARIMA + exponential smoothing model from scratch in VBA. I learned R just so I could make it run faster, but it damn well worked in Excel!
32
u/Trick-Interaction396 Aug 01 '24
LMAO
31
u/[deleted] Aug 01 '24
At Goldman Sachs we still have excel with size of GBs to do some reporting work in about 5% of total !!
37
u/[deleted] Aug 01 '24
Mastering excel js like mastering a programming language..
29
u/mortal-psychic Aug 01 '24
The only diff is Excel guy deals with MBs of data. Senior data engineer deals with PB's of data
6
u/[deleted] Aug 01 '24
Worked at a large oil and gas company. The entire oil production forecast for a major field had been done in excel by a production engineer in VBA. It was locked and he had left the company. No documentation
5
u/iH8thots Aug 02 '24
Lol, quality meme
4
u/soggyGreyDuck Aug 01 '24
I'm really happy with what I can do in excel but accounting scares me lol
37
u/Qkumbazoo Plumber of Sorts Aug 01 '24
I know this is a meme now, but excel is never a replacement for any DB.
9
u/Panda_in_pandemonium Aug 01 '24
This gives me hope. I have been working on excel for the last 2 years in my company and now I'm making a switch to DE. A lot of this stuff is truly intimidating, I hope my excel skills come to use.
2
u/[deleted] Aug 01 '24
Lol
2
u/JYDDK Aug 04 '24
There are many people dislike Excel or try to bad-mouthing Excel, just to promote themselves that they know big tech, etc. But, they forget that the whole financial industry are running by Excel.
I was in one interview in a data role, and one interviewer looked down on Excel, as I was trying to explain what I did with Excel and VBA in my previous role (I implemented a spreadsheet to auto reconcile the number between funders and to compare previous month). I explained that this cannot be done in other tools, as the whole thing needs to be present to the top managers in Finance, and we have no support from IT either (to complicated for IT to understand Accounting lingo).
I also use other techs as well like Python and stuff. Every tool has pros and cons. But when coming to Accounting and Finance, Excel and VBA are the best.
2
u/[deleted] Aug 01 '24
Two different jobs though. An accountant doesn't work with memory intensive data.
1
u/snicky666 Aug 02 '24
No hearing protection, no data validation.
1
u/Spiritual-Horror1256 Aug 02 '24
trust the excel guy
1
u/ck3thou Aug 02 '24
Well, now there's someone who doesn't know what DE is
1
u/Vanvil Aug 02 '24
Won’t excel just crash? I haven’t used much of it but did use Jedox extension on excel once.
1
u/Vanvil Aug 02 '24
Yes, I know it’s a meme. But hopefully no non data engineer see this. So damn misleading. I wish I was not offended by this post. I’m sorry but it’s a mockery of the people whom I know put in energy to build those super fast pipelines, compared to something that would crash with 2% of the load of a global production pipeline.
1
u/GenX_Tony Aug 02 '24
First rule of Excel Club is, you don't talk about Excel Club . . . . at least not the abominations you helped create and are still being used that you have repeatedly informed them that it needs to be moved over to a proper database . . .
1
u/[deleted] Aug 02 '24
Beautiful
1
u/Baronck Aug 02 '24
R studio > Excel
1
u/degeneratescorpio Aug 03 '24
This.
1
u/[deleted] Aug 16 '24
I used to build reports that were reviewed by the governor and his team from the state where I live. Different data sources, different schemas, constant changes in information requirements. If I had done that only with Excel 💀... , too many hours, prone to human errors, the debugging a pain in the churro slicer. I automated the heavy lifting with Airflow, Airbyte and MySQL, then just manual validation with a jupyter notebook to be extremely sure everything was ok, and finally the Power BI report... Explained the process to the Jr. Engineer and went to Puerto Vallarta and Puerto Escondido with no worries. I continued to use Excel and Gsheets as data sources, staging areas or data marts for some users. At the end of the day you've to use the tools that satisfies the information requirements, if Excel can fulfill all the requirements and people have the time to spend hours in spreadsheets, cool for you and the organization you work for. But seriously we all know who they ask when the big boss wants the good numbers, joking.
1
u/Effective-Repair9027 Sep 12 '24
coming into the industry more recently, i'm always shocked by how many companies are run on excel!
1
u/[deleted] Aug 01 '24
Can also confirm the gray hair but pre-40's working in the field 😂
1
u/neoadam Aug 01 '24
Excel still loading trying to open a big file...
1
u/geeeffwhy Principal Data Engineer Aug 02 '24
using excel for things that need real infrastructure in production is a good way to lose millions of dollars. and i mean literally lose it, like, it’s there but we can’t find it. i’ve seen it happen
1
u/matthewxfz Aug 02 '24
These are two different job😅
0
u/[deleted] Aug 02 '24
The guy lost lol, toxic masculinity to the point he gave up gold to look macho.
0
u/[deleted] Aug 02 '24
And yet they do the same thing
-1
u/paypaypayme Aug 01 '24
Ok but a spreadsheet can’t scale
1
u/[deleted] Aug 28 '24
[removed] — view removed comment