I Will Dropkick You If You Use A Spreadsheet
Вставка
- Опубліковано 29 сер 2024
- Recorded live on twitch, GET IN
Article
ludic.mataroa....
By: Nikhil Suresh
My Stream
/ theprimeagen
Best Way To Support Me
Become a backend engineer. Its my favorite site
boot.dev/?prom...
This is also the best way to support me is to support yourself becoming a better backend engineer.
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-K...
Get production ready SQLite with Turso: turso.tech/dee...
"Programming is just Excel without the boxes" - Real Civil Engineer
Goated RCE 😂😂
And they'll guard the term "engineer" frothing at the mouth. Lol
@@gregroyclark That's because Civil Engineering is proper engineering, unlike 95% of "IT" engineering which is amateur.
@@malcolmstonebridge7933 not the "real" or "proper" engineering 🤣
@@malcolmstonebridge7933 Well, it's true but you didn't have to say it out loud, right?
Spreadsheets DO have a use-case.
Just not as a back end or as a stand-in database.
Correct. Great for quick mock up. Show me roughly what you want and the calcs.. Then burn it.
It's fine for accounting. Manual accounting.
Maybe as a prototyping tool to be used by non-programmers who understand the process to be automated..
@@Deschutron I use it to solve the most basic linear equations once I'm done deriving. Just when I need some quick numbers. Much quicker than typing them out in a calculator, but also less hassle to set up than matlab.
@@233kosta Manual input at least, setting up equation shortcuts is simple once you understand it.
I remember watching a manager at work, enter a bunch of values into a column. Then get out a calculator and calculate the total and put that number on the final cell.
this hurt my organs to read
That is literally one of the first things you learn in excel university lol.
This is the way
@@littlecoldhands I just think it explains how spreadsheets end up being the database... Especially when those people sign your pay cheque and no longer want to hear about novel ideas that might make them change their workflow.
LMAO!
"Excel is not a database"
Hank Hill: "If those governments could read they'd be very upset
You mean Principal Moss.
@@TatharNuar Kinda, except criticising the government is pretty Hank Hill.
if excel is not database than neither is sqlite. in fact excel has more database features than sqlite.
@@turtlefrog-tn3ek How so? My ebike has more vehicular features than a model t - ITS STILL NOT A CAR 🤣🤣
@@turtlefrog-tn3ek It has more built-in computing features but I think it's far from ACID.
During the COVID pandemic UK government contractors used old version of spreadsheet to do COVID tracing. They lost tens of thousands of records because they reached the limit of 65536 lines.
@@MantasJurkuvenas classic 2^16 limit. I love that number
Yeah, "lost."
It's worse than that. They would have been fine* if they had used rows, but they were putting each day's data into a new set of columns.
* "fine" = would have avoided data loss until the UK government got bored of pretending to care about covid.
@@hfuhruhurrNever assume malice when incompetence can easily explain it.
Don't forget about 2010, when the government decided on austerity as the basis on which to inform the next decade plus of fiscal policy because of a false prediction caused by an incorrect VLOOKUP link in a spreadsheet.
Excel, the most commonly used distributed database.
lol totally. I started working on this company to fix their processes. Once I did my audit after two months, noticed they needed a DB, they have complex analytics running through power query and then they wonder, why the reports ends up with a bad result or errors. Well to put it simple, you need a DB! Not only that, they personal they have were not really data driven, they just followed the orders from above without putting any pushback letting them know why is a bad idea doing "x".
you have a typo in their... distributed -> distubing
@@hobbesthetigger You have a typo too: distubing -> disturbing.
"I spreadsheeted really hard"
The past tense of spreadsheet is spreadshat
🤣 I can hardly describe how much I love this!
19:52
dev: "But it works on my machine"
company: "but are you going to give your computer to the client?"
dev: "yes, I don't want to rewrite this thing"
use containers children, protects you from having "children" machines.
Fun fact, I once worked for a company that outsourced a web development project, and at the end, the executives all complained because it didn't look right to them. The third party salesman was sharp, realized they were all looking at the site on ancient computers using IE 6. He just said "I can have that fixed for you by next week!"
And his fix? He ordered new laptops sent to each of the executives (and billed them for it).
Then they loved the new site!
@@rascta You're fucking kidding me. Please tell me this happened at least more than 10 years ago, otherwise I just can't. IE 6?!
I work on a mobile game played by millions. They made the specific decision to move TO using google sheets as the source of game data like assets, IAPs and so much more. They were so proud of it they wrote a blog post about it. Now if anyone updates one of the spreadsheets it forces every player globally to restart the game. As a previous data engineer... it makes me so sad
💀 soul destroying
Congrats on one of the worst stories I've ever heard on this, as a guy that receives a million emails on this.
Pain.
That's actually insanity
I heard you like databases, so we made your database in google sheets
If you ever find yourself linking a spreadsheet to another spreadsheet, stop immediately and atone for your wickedness.
I will link a spread shit to another spreadsheet and then, link that last one to the first. What you gonna do ?
what do you think SQL is?
@@turtlefrog-tn3ek noooooo SQL is not a set of spreadsheets, and it’s designed to handle joins
Hah. I link therefore I am. Play purist on hobby time.
I do that all the time for the mapping documents used to program our database ETLs.
An excel mapping document speeds up the programming of the ETL as you can write a script to turn the sheet into code if the sheet is done correctly and it gives a human readable format for those who don't know SQL.
I don't however put the spreadsheet in the data pipeline. It's sole purpose is to provide mapping for the ETL process.
I’ve done things with Excel/VBA that many people aren’t aware are possible. Chiefly because clients have back themselves into corners and had no other reasonable recourse. Terrible decisions. Many terrible decisions. Spreadsheets have their place; running businesses off them entirely is an egregious thought that I’ve seen too many times.
I have the thousand-yard stare, boys. I’ve….seen things…
"I've seen things you people wouldn't believe." etc
@@thesenamesaretaken Tears in Excel
@@thesenamesaretaken I've seen things you people wouldn't believe... Attack ships on fire off the shoulder of Orion... I watched C-beams glitter in the dark near the Tannhäuser Gate. All those moments will be lost in time, like tears in rain...
@@r9999t Legend.
@@r9999t Grow up. Don't waste people's time with your mind's garbage.
This author clearly has never worked at a company where it takes 6 months to get IT to setup a database.
spot on. As someone in finance, anything I ask IT for is minimum 1 month out to fit it in with their stupid little scrum framework. At one point I was using a local postgreSQL db. I think the phrase running a business into the ground, will be replaced with sprinting a business into the ground.
I'm an accountant, and yes
@@pluto8404 lol
@@pluto8404 Can relate. You are not alone.
@mikel734 I mean have you ever asked why it takes so long? Sometimes IT is heavily understaffed or limited by company policies and 99% of the time when a sector decides to "solve" the problem on their own, they are actually going against company policies and creating more future problems, sometimes there is only 1 person or a small team of 5 responsible for everything IT on that company and 8/10 times they are underpaid, as someone that is always on the other side of this type of complaint, I find it amusing when someone asks me to get an db up or fix an web applet when the area/sector itself already has an person specifically for that purpose, and they ask that and other things when I have other 30-50 things to look into, things that would make the entire enterprise crawl to an halt if not looked into. Always consider the other side my friend.
For a software development consultant such as myself, Excel/Access/Power Apps/ etc. are a godsend. Literally any business application you can devise could be an Excel workbook. That makes them the ultimate MVP. Once a company gets dependent on them, then scales out of them, you the developer just swoop right in. No selling required, the client already knows exactly what they want, and understands the business value.
I understand the approach but it’s a minefield. I’ve been there. Especially when we start talking about maintainability and changes in time. Plus, of course, the horrendous performance issues…😱
Great comment, developers love working prototypes
Sounds like an ambulance chaser excerpt business logic
So yes. But also… 8/10 [citation needed] times, that sheet gets out of control but also never gets replaced for…. Reasons.
@@thiswillprobhrt The reason the sheet exists in the first place is because management will not pay for the off the shelf software that already solves the problem.
We used to scrape Windows Stores front end as another team WITHIN Microsoft. We’d scrape the store instead of some sane data delivery, pump it into several excel workbooks, load those into Access after review, and then run a rube goldberg machine of powershell scripts to execute PowerPoint desktop app instances on the cluster of windows servers to generate reports that nobody looked at.
I shit you not, this abomination was written by actual Microsoft employees.
Man that just screams "Splunk dashboard" I'm so sorry
I believe this.
And I believe there was a perverse pleasure in this as a solution. It's not about if you should, it's because fuck it, we can.
This hits home so hard. I'm finally attempting to learn programming at age 32 almost entirely because I've been so completely victimized at a small business run on spreadsheets from vendors (that need to be updated manually every time you want to look up their current pricing), internal spreadsheets, and quickbooks running off a system of spreadsheets, tubes, and pulleys. The Inventory is a 204,000 line spreadsheet that needs to be updated manually every time you use it. I need an adult, and the business needs an exorcist.
You can do it. Python and jq are your friends. Godspeed
I am the author and the offer to help is real! Reach out to the email in the article!
I’m starting at 42, working at small business(finishing/assembly), been running inventory on a spreadsheet and QB, for decades(no automation just access to the spreadsheet). Every quarter we add columns for the next quarter so we have inventory tracking. It also functions as the production request to machining. I’m sure someone who knows what they’re doing could unravel quickly. But as a small business I spend most of my time running a machine, and hand finishing.
You could try importing your spreadsheet into Microsoft Access or another database program that allows it.
Sounds like each of those columns should be a table of its own, containing unique items and their quantities. That is, if you don't want to constantly wait to load decades of historical data that is almost never looked at. @@chrisf2636
Having worked in ETL and having been told that this business important, daily update data, was coming from an excel sheet on Bob's laptop that Bob manually updated by pulling data from our database systems... I fully support this gate keeping.
My whole job is fixing shit like this. Thank goodness for all the PMs doing ridiculous processes with just etl data and a dream, so i have endless work.
If it wasn't for Bob setting up that upload 17 years ago, your company would have gone bankrupt from poorly informed decisions.
@@PakRoc-dev Either you die young or you live long enough to see yourself become the enemy.
It's not gatekeeping when the other side is a cliff. It's called preservation.
@@hobbesthetigger I'm definitely using that
You just angered the "Watches tutorials on Microsoft Office applications" audience that accidentally got recommended this.
Which is kind of funny because Microsoft Office includes a database program.
@@SmallSpoonBrigade That absolutely no one pays for lol.
@@SmallSpoonBrigade Except access is worse than excel. Can't do even half of the shit there I can with excel.
@@Just_some_guy_1 and there's also shit Access does that Excel can't. Excel has unique calculation stuff, because Excel is a calculator. Access has unique database stuff, as a database program: it forces you to only have one table per "sheet", which it's not explaining why to you, but it's because if you set up multiple tables per excel sheet you no longer have standardization: you have a freeform scratch pad. You're intended to take this appropriate restriction and go into the database design faculties to set up rules for what each column is, what kind of data is allowed where, how to auto format or handle improperly formatted data, and how the tables connect together. Anytime you don't know how to fit all of the data into one table, it's likely that you need another table that references one of the key values in the first, or is referenced by it in that sort of way, and it's also just as likely that this is the actual logical layout that best describes your data, and would allow future data work (SQL queries) to be done properly.
This is why game devs know the difference between prototype and production. Every experienced dev says to never ship prototypes because you will always regret it. And often times you need different people because some people are tinkerers others make finished products.
I am a tinkerer and I approve this message
I work in manufacturing IT/OT. One of our manufacturing lines used paper forms in Excel to write down all the collected data for assembled products. In my tenure in charge of that line, I said "This sucks", set up some automated data-collecting routines in Excel to let us at least chart the form content over time (because each form tended to have the same tags next to the associated data) then transitioned to onboarding a better record keeping software.
HOWEVER. Before I could finish bringing it online, the new engineer in charge of the production line switched the entire layout of the form so that batches of product could be squeezed on the same form. When they did this, instead of just adding new columns to the right, they completely re-wrote the entire form and added tons of merged cells in a way that made it impossible to calculate which data was associated with which product. This broke all of the process control charts, and when we went to do failure analysis later I had to tell them "Sorry, you're fucked. I can't back calculate any of this without someone manually writing it in by hand"
This is why we have a seperate dev and prod environment
The new "engineer" decided to use merged cells... I have a sales manager who does such things but he can be forgiven due to sphere of competency...
The issue with most of this is the distinction between hindsight and foresight. If you start out to get something done, you don't know yet where you'll have to go. Therefore you just can't start out by building THE ONE proper perfect database for the task. There is much exploratory work in many cases. Most of the article seems to come from a point of looking back at what has already been built. Sure, then the necessary structure becomes obvious. But in a business environment, who is gonna pay you or give you the time to completely scrap a running and functioning feature just so that it becomes more "elegant"?
One that recognizes the difference between safe and effective vs "working". One that sees how limited they are by this form and how much better growth or change can be with the right supporting system. I know I'm dreaming, but let me have this
Working with Microsoft Access 2007 in 2024.. not ideal, but VBA is still a great way to learn how to code and come up with solutions to specific business requirements.
Nothing better than an Access Form calling a batch script which calls a Python script which is listening for pngs to appear in a folder, so it can apply a frame to them and the end result gets printed via the Access Form. 😎
👏🏻🔥😆
Your IT department is SEETHING right now
What happens at every company everywhere is: Developer: "I can make it work in time, but we will need to spend a few weeks after release to implement a real solution." Manager: "OK, great! As long as we can release in time." Developer: *Releases dirty hack* Manager: "OK, now that Feature A is finished, let's start working on Feature B right away. Chop chop, no time to waste! It's getting released tomorrow!"
Nothing is more permanent than a temporary solution
As someone who manages a data warehouse, spreadsheets are the bane of my existence. Avoid them like the plague.
What do you prefer to use instead?
@@carultch If you’re talking about sources for an application or business intelligence report then the best possible source would be a relational database (sql server, oracle, postgres, etc.). This is because the data is structured with defined types for each field. If it’s well designed these tables also include various constraints to eliminate potential for data integrity issues. Spreadsheets on the other hand have none of these attributes by default and rely too much on humans to do the right thing which more often than not results in failure. Hopefully that answers your question.
People at my office use sharepoint because there is no better option given by IT. The reality is it's not even that hard for a company to give everyone the access to create and manage their own databases, but no IT department allows it, so the result - everyone uses sharepoint lists, and if they don't have sharepoint lists, they use spreadsheet software. IT need to get out of their arses and allow people to actually maintain structured data if they want people to make structured data stores.
Freakin' Amen! It's even worse in some sectors of the government, where getting approval to set up a new application using a database back-end can take months to even _years_ so everything ends up in SharePoint list monstrosities.
My company also uses sharpoints and excels. Its a worldwide company that manufactures millions of different parts for indurstries from home appliances to nuclear and military equipment. close to half a million employees worldwide
At least where I work, they delay isn't due to any technical reason, it's all down to compliance. Say you put in a request for a relatively simple db. If your org has a cloud hosted db solution (usually imposed on them by non IT senior management to allegedly save money), that may incur cost to the org. Now that a cost in involved, the cost has to be approved. Cost approver (most likely the non-IT dept needing the db) will want a meeting. The approver has a busy calendar is going on PTO, meeting takes a while to happen. Once the cost is approved, IT has to ensure that your org's retention and integrity rules will be followed. In many instances, these rules are required for regulatory reasons, or for insurance reasons. Getting your org's compliance dept to sign-off is also a place time goes to die. This is how under 30 minutes of technical work can go on for months.
Another issue that can happen when IT isn't involved is that mission critical processes end up not having any failover ability or proper back up. If a key part of employee benefits lives on one employees laptop as a local db, can coffee hits that laptop causing a delay. The complaints that reach the C suite will end up being "IT's fault". So yeah, they're going to want shit done with as proper of a set up as the org's resources allow. They gatekeep because they catch the blame no matter what, so they'd rather catch heat in set up than catch heat in the crash of "oh that person left the company and all these things ran on their laptop with their creds"
IT depts main goal should be to "pave the cow paths" as my old boss put it. What often happens however, is things like "CEO saw a news article about ransomware, he wants things locked down" IT obliges and now people are pissed. Then the C suite orders things opened up so people can "get back to work". IT obliges and now your end up like Ascension Health. Functional orgs can assuage this by haivng things like regular alignment/touch point meetings with IT so that upcoming/bubbling projects and issues can be made easier ahead of time. Also things like bringing IT into project planning early so that they can have it on their radar, provide insight, and warn of roadblocks that may not be known outside of IT. IT depts also have historically had poor track records of communicating with non-IT teams. They don't think to do things like send emails or IMs out saying ""Hey, I know your need is vital, it's still with compliance review. I'm pushing them to get their part done, but I can't move forward until they do"'
Make a robot that moves and rearranges the pebbles in your lawn, and get the binary value via a webcam
18:40 --- here's the problem... you haven't worked in goverment. Do you think we're just allowed to install cron and go onto our PC.
Or large corporations which aren't tech companies. Our organisation has the rule that "we aren't a tech company, we don't create our own technology". To even get a Cron job or database set up we need to:
1. Write a business case and requirements (3 month process)
2. Go through procurement (6 month process)
3. An external consultant with no business knowledge develops the solution (depends on the project)
So I could spend 9 months setting up a cron job, or use some VBA and get it over and done with in 5 minutes... hmm... Wonder what I'm going to choose...
Cron? Windows scheduler.
Actual programming language? Disapproved by management, use excel.
The team I'm contracting with is doing exactly this. I wonder if the author is at the company.
> I wrote a Python script that took a spreadsheet as an input, ran regular expressions in it against a database, spat out ten more spreadsheets, then used those to power a generic PowerBI template which had to have all ten spreadsheet targets repointed by hand. This became one of the most requested services my team performed for several months.
You in Australia?
@@roymarshall_ Thankfully I'm not. Maybe two companies do things this dumb.
Everytime someone says to use Excel for a process as a stopgap or temporary solution, I remind them that there is nothing more permanent in business than a temporary solution
As someone who used to work with spreadsheets for so long (translation prep and QA somehow always boils down to spreadsheets, idk why but most clients translate those lol) this is always one of my first thoughts as well. Tho i usually go with csv instead
Edit: rants incoming lol
Now instead of Excel, in the new company, we use Google sheets and i wanna die. Day one, they told me that we need to check generated strings's (purposefully keeping it vague) uniqueness and length via ctrl+f in a column woth almost 10k results, and i died inside. Few minutes later, i learned Google sheets and added a conditional formatting to that column. And what did i find? Several dozen, if not more, repeated strings lol
A few days later, we had to keep track of some categories' frequency of use, and i was like "great, I'll just write a few simple formulas to count them!" Co-worker looked at me and said, "wait you can do that? I used to count them by hand for months..."
Even CSVs are painful. So many times I've been told by software engineers at our parent company that they "made a database of results" only to open the source files and find a csv file that they were just tacking data onto, often in ways that made it impossible to actually do any failure analysis. Apparently it's never occurred to anyone there that you can literally just use sqlite to set up a tiny database on the PC.
The fuckers even had the audacity to tell me they were "Backing up the data to a cloud DB", which actually was them dumping csv files into blob storage which they then refused to give us access to even though it was our job to troubleshoot the equipment.
You should have kept it a secret, asked how much time do you have to do it, do it in a couple of minutes and chill the rest of the time xd
On one gov project the scrummasters had to send daily reports to management. One created a spreadsheet and automated the process. So now they greatly reduced their work and could play half the day. Some time later, there was a reoganisation and new sm were assigned. After some time, they broke the spreadsheet. After that, they had to fully create the report by themself and were spending a lot of time on it.
They were just into the 'work' business and it showed.
Some people are not curious enough to learn their tools...
There’s a book by O’Reilly titled „Going GAS. From VBA to Google Apps Script” 🤓😉
This is my life. I just wrote a 2k line VBA application in MS Visio to parse .CSVs and draw diagrams.
This is probably the last time this combination of words will ever be seen again
I can’t imagine a worse use of time
Were you told to do this? I hope that manager never works again
2k? vba? visio? The part with csv and diagrams came as a real plot twist. Please save yourself from this suffering. Python can do this in about 20 lines. Yes, only 0.1% the amount of lines, and probably 0.00001% the effort
As addendum to the Python tip: use Pandas and matplotlib. Pandas to load the csv, matplotlib for graphs. Ask ChatGPT for examples if you're not sure how to start. Writing a single script should be fine for your use case. You can output PNGs if you need them. Perhaps also take a look at "Jupyter Notebooks" if you want to combine code and images. If any future programmer complains that you were writing notebooks, tell them you were doing this before that - that should shut them up fast enough 😂
I freaking love this channel. It's one of the only places where smart people are, and it's one of the few places where I'm decidedly in the bottom quantile of skill/IQ. I find myself watching it even when I have no direct interest in the subject matter and when said subject matter flies straight over my head.
Jacobins mentioned around 2:05. So few people would get the reference to who the Jacobins were and what they were all about.
Thank you. Please keep streaming.
"Every last line of code you will ever write is one layer on a shit onion that is slowly being added." Never have truer words ever been uttered.
"It feels so good [to hit the limits of a concept]" yeah, if you're not the one who has to refactor around that limitation...
its honestly so fun to have an entire business just relying on this one computer with some port forwarding tucked away somewhere
and noone actually remembers where the thing is
Spreadsheets are evil. In the army while working with spreadsheets I've witnessed utmost nightmareous files
Randomly merged columns, zero-width rows, among many other things
Hidden rows are a good thing. They hide little secrets that give you joy when you find them later.
having to repeatedly ask Flip to take something out to be clear that it's not a joke is so funny 😂
whoever wrote "hawktuah( )" in chat is my hero of the day xD
Unless somebody here can offer a good suggestion for getting a somewhat-layperson to enter data as easily and efficiently as they can to a spreadsheet, I'm not fully convinced.
Yeah.
The spreadsheet is required as a user interface.
If a layperson can't copy paste 500 rows of data, edit, and shuffle it around, then we're done.
I was so excited when I saw Excel had added Python support! Online only...
Excel + VBA is sooo close to being good, but there are just a thousand gotchas.
Alas, there's no better tool, usually.
True, and alternatives these days are all cloud based with ridiculous limits and pricing plans.
There's things like Airtable, but that's basically just a fancy spreadsheet as well.
Anyway point being, some things which are not worth developing their own custom Crud UI for, need some sort of standard setup that you can one click launch, with all security, access APIs easily taken care of, and in most cases Google Sheets etc is enough.
Sure, it might not be temporary, but alternative is building it out, spending much more resources and getting a lot less done, company won't be as successful.
Excel was designed exactly for this purpose. You can still use it as an input interface.
Just don’t use it as a fucking database!! FFS
IT needs to setup a database that can you can export the data into. There are importer/transformer tools for this purpose
7:00 My department currently utilizes a dashboard built on about 100k lines of VBA. It's technically on Excel but disables all of the Excel features for performance reasons. I spent 3 months trying to refactor it before giving up because it was such a deeply entangled mess.
I worked for Nasdaq around 2000 and their entire payroll system was on a VAX that was in a closet since 1990. Nobody knew how to use it and no one dare touch it.😅
Yeah, we had one of those. Not a VAX, but a PC sitting under someone's desk, performing business-critical processes. Then the person got laid off and the PC was repurposed. Well, I guess it wasn't so business-critical after all.
Yeah a vax is basically a desktop pc @@YDV669
Ours is an iMac. Guy wrote scripts that have to fire on his desktop for the company’s daily pricing
You can do databases wrong. Believe me. You can absolutely make them a umantainable mess. I've seen people create subsets of a database with millions of entries in a text file.
MS Access is what got me started in IT.
Absolutely. Didnt' start mine but got me into RAD programming, which makes Access not just a DB. It was great way to getting to learn to swim in the Windows world
20:15 a server that is a random computer running an important infrastructure… At Apple they're called closet servers… but you may find them in somebody's office, behind a desk… hanging from the ethernet outlet.
My friend made a JRPG in C# (XNA), using excel exported to csv.. all from stats, to dialogs to events etc
how tf did he manage to export excel to csv and it not screw up the formatting o.o
@@ferinzz I think he used some script macro. In his next game I convinced him to at least use json instead 😂
your friend is monstrous
@@ferinzzBy not having formatting? I basically exclusively use Excel to create csv files for use in R.
@@brutusthebear9050 my main complaint with excel is. Omfg i do not want scientific format.
Or the fact that you can't specify the type of separator to use. It seems to be region based from what i researched.
If you don't run into those issues, fantastic.
it's why old MS Access was great. small projects that need a portable database. now that spreadsheets can handle a million rows, it's not as attractive
Except for that 2GB file limit.
@@goodfortunetoyou But rows are not records. Should still use a DB.
@@lesh4357 Yes? I'm pointing this out because I've seen that limit reached.
It might be wise to consider a database other than access if you expect the data you're working with will ever approach 2GB. SQLite, for example, can go up to 281 TB.
at least one general practitioner uses this to write patient records and cross-reference keywords
Indeed a proper db management tool is best, but for small applications and the ability to use the new data model features, excel is sufficient. Never let the perfect be the enemy of the good.
I once created a million cell spreadsheet to implement a an algorithm I used in my undergrad thesis project to calculate electrical properties of custom precision wound coils for work. It took about 1.5 seconds to refresh every time I edited a cell. So satisfying!
Depends on the spreadsheet. I worked at a shop once where they had a mainframe job whose //DD * records they would fill out manually by retyping from a spreadsheet. When I suggested they simply reformat their spreadsheet in a very specific but easy way and upload it to the mainframe, then change to //DD DSN=??? (I think that was the syntax, 20 years is a bitch on memory), IT screamed that it was impossible. So I showed it to the poor lady responsible for this transcription, and she loved it. I did not like their IT, which refused to move out of a box very carefully constructed in 1977(this was in 2006).
And I was in that IT department, too.
It's actually neither hang or hung, but hanged
hunged
I'm hung
All three are valid, depending on what you're trying to say. Although hung is definitely the most catastrophically wrong word 😅
@@MrSomethingred RIP :(
@@MrSomethingred Grow up.
I feel like getting to know Excel, VBA and Power Query gives you better idea about how to write business requirements.
So many devs complain about operations not being capable of giving clear requirements. How they suppose to know if they cant write a basic working logic themselves?
A place I worked at had a BSD machine that ran our local Cygwin mirror. Our entire build and dev process depended on custom Cygwin packages on that mirror. It never went down and nobody in our 15 engineer team knew where the box physically was. The landlord sold the building, and as part of some new remodeling the new landlord found a doorway to a small closet that had been plastered over. Our BSD Cygwin mirror was humming along on a shelf in that closet.
When I worked in cable, we had to manually upload a CSV every few days to feed the TV Guide's data.
Oh man I just binged on this guy’s articles and spat out my coffee. 11/10 strongly recommend.
No way punk. No way will I be separated from my spreadsheet. It's not going to happen. It is the ultimate DSL.
Have you heard of your lord and saviour: Postgresql and SQL? Or maybe Pandas and Matplotlib for Python.
oh yes we will.. step into my office...
the unixware servers found behind walled shut doors after decades of just keeping the house cards up are legendary - and true,
10:38 - there is a joke pattern for this and it is called annual rings: each year we add another layer to decouple us from the crap we built last year.
The simulation in which we live is really spreadsheets all the way down.
When the company has the technician doing a task that can easily be automated but they forbid any kind of developing tools or third party applications, that Excel file with a VBA macro is the go to solution. It might be what saves them from burn out.
Followed closely by cmd shell scripting (aka PowerShell) and AutoHotkeys (.exe 😜) 😁😉
I feel like actuaries and accountants are the prime targets for this rant.
And I need to define a process to automate data processing using Python. Ah yes, the data is spread across a dozen spreadsheets, Word, PDF, manually entered by colleagues and business partners with limited data validation and recognizable patterns. You guess it, only a hell of condition branches and loop can help you here.
One automation (and there are more than 10) can take more than 40 minutes, but it can save weekdays of labor work. I sympathize with those who needs to manually process the data entries before.
I was on a team developing a SAAS for shipping and logistics. Of course we needed truck drivers so we started a separate business to hire truckers to field test the software. We never intended the test business to be our primary source of revenue, but COVID happened and shipping went crazy. Next thing I know, were running 100+ truckers in 48 states delivering well over $100 million in products annually and the backend for the whole operation was three guys and an Excel spreadsheet that was stored in box, but could only run on one specific machine because all the macros used fixed paths. It was SOOOOOO janky, everyone was afraid to touch it. It frigging broke at least once per day. It was a literal Rube Goldberg machine.
I maintain major companies excel sheets that my brother wrote for them 12 years ago and they have no idea how they work.
Funnily enough as a mechanical engineer we also had a lecture where it was emphasized that excel is not a program for leadership and data between different levels of organisation. It is not ERP or SAS or whatever there is.
On the other hand for some group work I made a table of multiple values and scaled it to thousands of entries and made a graph that would show a cut-off point. A classmate copied that graph to our report in Word. We had to abandon the report file because nobody could load it up successfully for those massive data entries directly linked to the small graph on one page.
The one thing I actually wanted to do with Excel was a transformation from a drop down option to actual value. Like you'd choose in your gym program the % of 1RM and it'd convert into an actual weight in kilograms in that cell as you've chosen it. Without additional number and value comparisons. But apparently that required VBA and it got way too messy and I decided to never go that far with Excel. It wasn't even necessary to do that way, but I already had way too many sheets of numbers and calculations of % of 1 RM and corresponding max reps, I just wanted it to look neat in the logging side.
Yes, I agree that spreadsheets are not databases , but , For data science nothing beats a spreadsheet. Sure you could program something better but that takes time , and time is money.
16:39 once you're writing Vbasic scripts with the above point, kind of goes out the window..
Here's the kicker, an excel spreadsheet is free. A database and client web client costs a metric tonne of money to make, and metric tonnes more of money for all the little changes that are required when the product doesn't do what it's supposed to. And then the website gets bloated and slow.
Malformed Excel spreadsheet that requires manual input, no validation, and likely not great for making statistics from. Or expensive slow website that's stuck at being 80% done?
When high-paid individuals are spending time, that costs money. Working with Excel requires office licensing, too.
Database + API can be faster, way better solution for sharing sales data. Spreadsheets have a place, they just aren't great for data storage and processing at an enterprise level.
It's less that it's free and more that you already bought it with Outlook.
Unpopular opinion: most developers know fuck all about databases. I met actual 'professional' software developers who didn't know what an index is.
It's been 10 years working IT, whenever I think I understand databases, I realize I don't
Dev knowledge is patchwork too much to know, too much copium for some framework to magically do the work
Sadly, so true.
When your IT department flat out refuse to install Python and your BI department take 2 months to build a basic dashboard, spreadsheets are great.
That’s an infosec skill issue and the lack of 360 feedback loop between the business depts and IT. Plus, full detachment of the IT from business results. Once people start _feeling_ the results of their (lack of) engagement in their own wallets, things change very quickly. It’s similar with the infosec depts - the job is not to forbid any action because it may be insecure; the job is to sandbox it in such a way that it stays secure despite the internal agility. But such responsibility detachment usually goes all the way up to the C-level… and when the CxOs fight each other at the table, the CEO becomes the final oracle - and I think we all know that those people do not become CEOs because of their tech savvy, do they? So here we are…
I love me my spreadsheets. This is the hill I'm willing to die on.
Better to die on a hill of spreadsheets than to live to see it become a mountain
Spreedsheet was the second Killer App - the first was Bill Gate's BASIC interpreter for the Altair. To disrespect the spreedsheet is like disrespecting one's grandparents. Dissing one's grandparents is not a good look.
Spreadsheets are absolutely awesome. And I'm a programmer, I love coding and do it for a living and for fun. Still I love spreadsheets and specially MS excel and I owe so much to it
@@charlesabju907 let's get some spread sheet love!!!
@@charlesabju907 You're not a programmer, you're a code monkey. Maybe you have good things now but karma will come around eventually and you will be exposed as the fraud that you are.
The fact that you can copy tabular data from a spreadsheet and paste it into a web page and it keeps its structure is super slept on.
yeah something something xml
And in some cases, vice versa too.
I'm a former Metrologist and used to program automated calibration procedures in Excel using VBA to acquire measurement data back in the mid 90's.
We've come a long way since then.
My best work at my last job was making a complicated front-end for a database application inside Excel.
We did that because a) I was not allowed to code something from scratch, b) rolling out software throughout the company was a nightmare and Excel was already working on all machines, c) users rejected the jerry-rigged web interface of a completely different system for it, and d) the hairbrained decision to do complex calculation logic as stored procedures inside the DB instead of literally anything else was made against my strongest objections already.
What is wrong with you?
@@jakubhalik-rr6sv I did get better.
Good for you. Google Sheet is nice too if you want you JS scripts.
as long as it works and helps the people who bring in money do their job. No issue there. No need to over engineer everything. If someone needs to know a number, and the easiest way to deliver that is writing it on a used napkin, go ahead and do that.
@@pluto8404 using a db is not overengineering.... a 12 year old can do that. Using excel as a spreadsheet is literally the dumbest lazy child thing that a 6 year old would do... Your application is then entirely dependant on a UI, backend, db and the entire web infra of a different proprietary online product not changing at all, u r making easy things (databases) hard for no reason but childish fear, people like you should not use computers....
Currently writing a web app to replace a business process done by spreadsheet. Sometimes, it happens amd you get to be the one to guide the organization into the light.
I just made an automated process using a spreadsheet a couple days ago. It was soooo easy to set up and it just works. There are not that many projects, especially outside large corporations, that need a millions of rows that would choke things up. The real problem was the difficulties he ran into dumping it to a database when they got too large.
OP. It's more complex than that. Size isn't a huge deal, but format is. Random people at an org will turn the spreadsheet into a non-tabular format, and then use it as an input for ANOTHER process.
Major universities here manage academic workload allocation with literally 1000+ spreadsheets with no guarantee that they're in even remotely the same format.
I pray for anyone who has to maintain your "handiwork". You know Python exists, right?
"The real problem was the difficulties he ran into dumping it to a database when they got too large" Uh huh.. This is one reason we despise some of our coworkers.
We did this at my previous job. We used the spreadsheet as a giant config. It was used to configure where to put data, how to transform, filter and combine it. If we used a database instead we would need an entire frontend to handle all the daily changes we made to the spreadsheet. We saved so much complexity and maintenance on using Google sheets.
mongoDB has front ends. And there is KEXI for SQL.
Spreadsheets are great.
I made my business analysts write the tests, thus shifting left the responsibility for bugs.
I can't make my BAs edit sqlites, but excels, no problem!
I have literally created an automated spreadsheet like yesterday which got published onto a web dashboard that was overdue to be pushed to customers as a time being data showcase platform. It feels GOOD to BAD
I am a programmer. I use a spreadsheet to wrangle data all the time. It lets me do stuff I would never do by hand. It is just a tool.
Don't really care if someone uses it as a database. If you have toy sized data you can use a toy sized database.
I had a statistics teacher who suggested we use Excel as a calculator. Actually pretty nice in that context.
I spent over 1000 hours building process infrastructure for my company to never have this problem again.
Well engieneered solutions are now easier to set up, deploy and maintain rather than any VBA or Excel based solution to any problem.
I also built components to ingest and export Excel content dynamically described in a single schema validated file per job.
Spreadsheets are awesome, it's how people use them that is cringe. Sharepoint lists are awesome, it's how people use them that is cringe. There is no reason to not use sharepoint lists as a simple request system. It takes like maybe a couple hours to make one and Ive done it for requesting label printing and managing requests for an internal machine shop and it works well. These lists have worked for 3+ years without issue ot much modification as things were thought about *in depth* up front, they arent exposed beyond one building, they dont track sensitive info, and they are used by very few people (less than 100 for the most part)
You know how long it takes to ask IT to install a database, to write code to access that database, to write a front end to abstract all the query language? It takes much longer than a couple hours. Not to mention, databases have their own issues with being *EASILY* improperly designed, multiple key structures for query optimizations, changing table schemas affecting *the entire db and all its applications because of foreign keys*
The argument should be problem scope, not use an M-16 for deer hunting. Remember, its perfectly fine to use a screwdriver to drive in a screw, even though an automatic drill exists.
As a federal employee, I'm starting to understand why nothing ever functions properly. We use spreadsheets, Access, and SharePoint for everything.
all corporations run on spreadsheets, u wont change that, by seeling them some bloated overhaul over Spreadsheets, they will still download a spreadsheet and use it as such. I've seen 100+ MB spreadsheeds used as database in almost every job i had. and im the one who comes over and tells them this should be a database, and they will still use spreadsheets, and i will work on spreadsheets doing what they asking me to do. because that's what they know and can understand.
You can explain them that database just a spreadsheet with different interface
Best piece of wisdom I've seen as a programmer "don't optimise code to be extensible and future proofed, optimise your code to be replaceable."
I can't hate excel too hard - Where else do you get reactive programming... a visual repl... with 0 new dependencies... and usable from the start by a non-techical audience?
Version control however...
From Google Sheets, they now have named functions, various array functions and lambda...
Even using only built-in functions to play with data can be so handy and so much fun!
Programmers often forget that the main purpose of the program is to solve a problem, and to solve it now.
And the non-programmers never seem to get it through their heads about the real costs of extendability, interoperability, security, type checking, maintenance, licensing, back ups, etc.
Remember that time Public Health England lost 10’s of thousands of COVID test results because they used a spreadsheet instead of a database? Excel is not a database.
Also ChatGPT isn’t a search engine. It scares me that people use it as one.
5:57 imagine living in 2024 and suggesting Index/Match or VLOOKUP over XLOOKUP.
Does XLOOKUP work on Mac now?
Index match was always better, no one uses Vlookup.
Query in Google Sheets is all you need.
@@aj-jc4cv Boeing does
@@greatcanadianmoose3965 oh dear
as someone who has to read from excel spreadsheets to procces prices and stocks i feel this, making excel spreadsheets into json and using sqlite/postgress for the more important stuff feels good and is better
So, do you really want to have some person from HR or worse from bookkeeping, come to your desk bugging you to do some stuff for them on pretty much a daily basis?
Instead of them being able to do it themselves via spreadsheets and the occasional VBA (ok, sometimes you will also meet the accountant where his VBA script is in the tens of thousands of lines)?
Until they get a frontend in which *they* can write their own scripts and visualization in, spreadsheets are more of a necessity.
From my experience excel is fine as long as that data never leaves that sheet again.
I hate any data coming from an excel file with passion
Every time some data from those files gets processed something goes wrong and I include every kind of export format like csv as well
I wanted to go on a rant about that whole topic but nah
I rather spend my weekend doing more enjoyable things
I'm that accountant, nice to meet you
@@TabischYou get it! Spreadsheets at the end of the flow of data are okay, but people keep building on them and using them for processes downstream! Everyone thinks it's fine, I don't want to be bothered, etc, but I've been emailed about literal oil spills in the ocean caused by spreadsheets.
theres nothing as permanent as a "temporary stopgap"
I mean, for completely automated tasks, a spreadsheet might be superfluous.
For cases where a user might need to check the data, or even calculate something, having it in a format that many people understand how to modify is invaluable. Putting data in SQL can make it inaccessible for many to even select, let alone update. Furthermore it might just be simply easier than developing all the needed calculations and possible calculations and graphics in code.
@@TenFrenchMathematiciansInACoat give more details. What dashboards are you talking about? From my experience dashboards are pretty inflexible, usually you can filter. But doing stuff like calculating new data, or God forbid changing data.... that has to be coded in, if possible.
omg he just called our entire MDM out! There's a mac mini in our Corporate Office's Tech closet.
It always exists
Sometimes i use google sheets as a temporary database 🙂
Temporary is the most permanent solution
At some point it becomse contemporary
The pebbles in your front lawn thing, is REAL. Ethiopian shamans would use binary multiplication using pebbles put into two rows of holes to tell people how much money a herd of animals was worth. It worked because the first row was used to convert one number to binary digits (by halving the number of pebbles in each slot in the row and then seeing if the result is odd (1) or even (0)), and then the second row (which instead doubled the number of pebbles in each row) was basically multiplied by the bits calculated in the first row, which calculates the partial products that just need to be added together.
The example used in the article I read was 34×7, which looks like this (zeros added so things line up nicely):
Row 1: 34, 17, 08, 04, 002, 001
Row 2: 07, 14, 28, 56, 112, 224
After determining which columns in row 1 had an odd or even number of pebbles, and then removing the pebbles from the bottom row corresponding to a value of 0 in the top row:
Row 1: 00, 01, 00, 00, 000, 001
Row 2: 00, 14, 00, 00, 000, 224
So then they'd just count the remaining pebbles in row 2, and there's your answer: 238.
It's worth noting that the binary value of 34 is used, but is technically backwards; 100010 is 34 in binary, but when actually performing the calculation I have the bits backwards. That's simply because it's annoying to halve or double a value from right to left while typing.
using a spreadsheet to show customers their revenue is really nice. Also using spreadsheet to show stats to product owners is also nice
@@TenFrenchMathematiciansInACoat yea did that to show stats for product owners
Let me tell you this thing called Jupyter Notebook.
Yes, it very much is.
This is why we have many libraries for generating spreadsheets from code plugged to a database.
@@hanifarroisimukhlis5989 that was for tech illiterate people, so just simple stuff is enough. I have other things to fix :P
@@hanifarroisimukhlis5989 right? A friend showed me his 3D point cloud on a html page rendered by matplotlib in Jupyter. Beautiful!
I feel this article so much...
I work in a laboratory and I worked in another lab before that (material scientist turned software engineer). Labs are the manifestation of "temporary" Excel spreadsheets. And there is a simple reason for that: Lab software is unbelievably expensive and still they are all without exception complete garbage. The developers have never been within 10 km to an actual lab so they get advisors from a university. But newsflash: universities are not how labs operate. It's like asking a dutch riding his bicycle to work about advise on driving 300 kph on the german autobahn.
So every lab has that one guy/girl that "knows Excel" and thus creates all test evaluations in it. I have been that guy. I have been so good at it that I learned to hate Excel with a passion. It is a great spreadsheet programm but complete and utter garbage for everything else. I hate it! Microsoft should roll out an update: "F*** you! We removed everything that isn't relevant to spreadsheets.".
I managed to convince "that guy" in the lab I now work at to learn Python with my help. And if he ever creates an Excel spreadsheet again I swear I will use all my non existent martial arts experience to beat him until he sees reason!
I will effin laugh at you all when you need a solution quickly and the dev guys say "come back next year" and I'll build it for you in one afternoon in Excel! 🤩
There's a time for proper development - and there's a time for spreadsheets. I'm sorry.
You can save a year now or 10 years in debugging and suffering later.
@@MrDelord39 I will let my Boss know i won't be able to process payments for our clients for a year then.
Functions in excel are what got me into programming. I use a db as the backend of every program now bc it makes more sense to me
I'm a big fan of spreadsheets and VBA automation only for the fact that they're the reason I learned to code lmao. After being tasked with filtering data manually from a multi-thousand line Excel spreadsheet to create a report, I knew there had to be a better way, and not long after I was making code to automatically filter and clean data, to organize it, to create reports and charts, to web scrape, and to aggregate data into a Microsoft Access database since the business I worked for was too scared to use anything that wasn't a big tech name like Microsoft or Google.
Literally how I became a dev. With some people, you give them an insanely tedious Excel-based manual task and they learn to code to avoid it.
My friend works at Santander (multinational Spanish bank), and she once showed me a mockup of an application UI their management told them to implement. It was in Excel. They made a mockup design of an app UI using colored cells in excel and just gave it to a team of engineers to make sense of it and implement it so that it works for the customer.