Not to be too nitpicky since it looks like the author is a developer and not a writer, but referring to the person building the spreadsheet as the "admin lady" throughout the piece sounds a bit awkward. Maybe just give her a made-up first name?
Looks like the author is from Taiwan, where the vernacular 'lady' is a common suffix for admins. Although it's good feedback for someone marketing to a non-Asian culture.
I haven't tried this yet, but if it works well, the implications towards rapid Business Intelligence development are great. In the past, when a user has needed some 15-year-old, macro-infused spreedsheet turned into a more streamline, business-wide process, any data entry components would require roping in webdevs to get the job done, as BI developers don't usually come with a web skillset. The idea that BI developers could just use the spreadsheet to generate a web app not only cuts down the expertise needed, but also solves the problem of training old-timers to use the new process.
Excel and its open source variants achieved total market dominance by providing a seemingly future proof technology platform and a generally maintainable (usually by a single human) solution for business. The biggest hurdle with converting these programmable documents into applications has always been long term the maintainability of the replacement solution. Even Microsoft had it's fair share of failures developing simple programmable database solutions (Access, Visual Fox Pro) to meet the needs of businesses and governments that "just needed a little more than what Excel could provide". It's a marketing pitch that never fails because it underplays the cost of maintenance of the new solution and the real value of the spreadsheet.
I welcome solutions that serve this need for "better than excel" without succumbing to its pitfalls but I'm not holding my breath. Even this article just outlines a solution that could be implemented in Excel with a little extra knowledge from the user, making it "no better than excel" for the specified use case.
Interestingly, Excel et al. achieved that status without the "no-code/low-code" mantra that is the rage in sales these days, where "citizen developers" are expected to relish in GUI editors severely limited in the logic they can accomplish.
On a turing-completeness spectrum, they live on the opposite half of where VBA+formulas stand proudly. Therefore, it is no surprise that for anything beyond "out-of-the-box", it is expected that an experienced IT developer/consultant will be needed to figure something out.
A decent reason to disallow custom code is that these platform are to be upgraded regularly, yet every major version seems to bring its lot of breaking changes while my decade-old macros still run like a champ. Oh the irony.
What you describe here is exactly what we are doing with a tool called EASA. It enables us to very quickly create web apps using an existing spreadsheet as the logic module. You don't need to be a dev to do this - our BIs find it straightforward - the closest you get to code is a bit of SQL.
It works well - we have successfully applied this to about 20 really critical spreadsheets for functions such as pricing, deal valuation, risk analysis, and even for engineering analysis. Users simply use a web app (that can look like the underlying spreadsheet - or not). The system is also integrated with SAML, so authentication is there, and you can also integrate the apps you create with 3rd party systems - we are looking at launching Excel-based pricing apps directly from SF.com, for example.
Anyway, the site is https://easasoftware.com/ - there are some vids there which probably do a better job of explaining it than I have done here. It's not just about putting an app on top of a spreadsheet, but really putting an app on top of processes that involve spreadsheets. And you can use things other than Excel as the logic module, like Matlab, R, Python - in fact we are dabbling with using EASA to deploy ML models created in Python.
I don't know if a BI Developer with a web skillset would solve this problem. I worked as a BI Developer and was solely hired because I had mucked around with the COGNOS and its API's and written a few analytics based web apps. The reality was that the rest of the team didn't want to support anything outside COGNOS/MicroStrategy nor did the project managers foresee web apps as a solution to any problem.
Companies like Celigo created an industry out of allowing Excel users to shoehorn pretty much anything into a spreadsheet and then have it interact with their major accounting/ERP systems.
I thought it was clear in my original comment that it would require more than one developer before - one for the BI process and at least one for the data-entry component. Given that most BI teams don't have a web developer, that also spreads the process over multiple teams, which can slow down maintenence. And I'm not sure why you think it'd take a whole other developer to manage this tool. BI developers are used to picking up new tools and languages (my BI job requires that I know how to use SQL, python, MDX, and C#, and numerous third-party applications).
And when I'm referring to "old timers", I'm referring to users, not the developers. Why would a user need to understand that snippet? Are we even talking about the same thing?
I can see exactly what that code is doing on a first read. It's pretty procedural. I don't like the range reference.
> "C6:G6"
I think the bigger problem is it's not going to be a developer maintaining this. It's going to be a pretty literate VBA user and nothing will go wrong. Inevitably they will move on and pass over the code, the 'user application' to someone who will feel reassured they can read it too, but then when it comes time to make a change, they won't be able to. And then it'll be passed over again and become a black box.
A developer (if there's funding) will not like picking up something undocumented, then it becomes a project (less funding). The process, a magical black box that may or may not have stopped working also loses documentation. Governance is lost. Particularly but not exclusively in financial applications, potential for misuse and loss, either accidentally or fraudulently, has been introduced.
So, the above looks (mainly) OK to me. But only because I know to insist on maintaining a manual backup or otherwise maintaining knowledge of how the black box works.
The problem usually isn't the webdevs or the BI developers but the fact that it's logical thinking and engineering skills that make a supportable application. Without those skills, bad software gets made, and bad Excel spreadsheets can be just as bad as bad webapps.
You can do some of these tricks with Excel 2016 or later and SharePoint Enterprise or Office 365 -- which many corporations already have.
You can embed an auto-updating Excel file in a SharePoint webpage using a "webpart" that renders as HTML. (Microsoft is getting away from web-rendering Excel with Silverlight,and allowing more functionality without the need for a browser plug-in.)
The trick is to setup the Excel data connection using the "Get External Data" -> "From Other Sources" -> "OData Data Feed" and not use the "Data Connections / Microsoft Query" method. Note: Microsoft Query connections won't update server side, the Excel file needs to be opened, data connections refreshed, and re-saved.
It does require a bit of Excel expertise to setup, but there is no need to create a Java application to get it to run.
The first step should have been to call the bank. For businesses, banks usually have a website that she could log into to: 1) get more up-to-date or more indicative rates, 2) execute the trade. Will save dev & maintenance time.
> Why do you think Excel is so ingrained into all kinds of ...
Because it works.
Programmers underestimate and put down excel all the time, but I think it's actually how many projects they end up with get started.
Someone had a problem, solved it for themselves using excel, got noticed, expanded the solution to other problems until the excel solution itself became the problem and then... it ends up as a project for you.
There's nothing wrong with that. It does the job, and it's not like the first course of action when there's a problem should be to assemble a scrum team with a cast of characters like a sitcom, and blow a million dollars to implement a bespoke software solution. Sometimes excel is just enough (and then some).
My first job consisted in updating Visual Basic code behind an Excel file that specified the hydraulic circuit for an aircraft. It was a simplified version of the circuit for use in a simulation that was used to calculate pressure loss based on several valve and pipes parameters. The results were exported to a txt file to be loaded by the simulator.
Also in the same project I remember another Excel file with mechanical calculations that actually updated a nice graphic that simulated all the linkages and different extensions of the actuators and computed forces at different points. The unholy alliance of elegant Lagrangian mechanics with Visual Basic.
I hope to never touch Basic again but it was a valuable lesson about getting things done and I'm glad I took it so early in my career.
Because spreadsheets are at the sweet spot of most complicated programming language that non-technologists can reasonably be expected to learn on their own.
Because you should have a reeeealy good justification to write a single line of code for something that can be done in Excel much cheaper, and mostly you don't.
I guess what I'm trying to say is: it's a feature, not a bug, and it's really important for entrepreneurs to understand this because otherwise they might think they have no competition where their competition is with the most useful piece of software humanity ever produced.
You only need one tool to run your business. You can use Excel for bookkeeping, inventory, salaries, invoices, project management, budgeting, presentations, planning, forecast, brainstorming, timetable, to-do list and so on. If you would use a separate app for each of these you would go insane. Easier to learn one powerful tool than hundred small ones.
Take a look a talk by Joel Spolsky called You Suck at Excel. It's on YouTube. It will give you a quick glimpse into how Excel powerusers are using it and how fast you can create something useful.
- Because a spreadsheet is the best UI for many business applications (far better than an HTML table on a webpage).
- Because Excel allows the user to (i) perform ad-hoc calculations, (ii) audit the input and outputs, and (iii) easily modify the functionality in real time.
- People don't like filling out forms and most web apps are basically filling out forms.
Say we had a REPL that also had a table (like excel, each cell has an address... a 2D stack if you will).
Say we interact using a mouse to select a cell or in the REPL to specify what cell we want to write to. Then, if we use a Lisp, we have tabular code and tabular data...
I don't know if it is what you are envisioning, but there is a spreadsheet program called Scheme In A Grid (Siag) [1] (part of Siag Office [2]).
From the "Introduction" [3] of its Online Documentation: "Siag is an X-based spreadsheet for Unix. It uses Scheme (a Lisp dialect) both for expressions and as an extension language, which makes it easy to create new functions (for native Lispers, that is). There is no requirement to know Scheme to use Siag, expressions can be entered in traditional spreadsheet syntax as well."
This is fine and dandy for small sheets, but if you're trying to build a sheet with multiple tabs, circularity, self-referencing IF statements, or lots of data, it will get really hard
Also, the #1 Excel rule in Wall Street is "never use your mouse"
IMHO, I think the better project is an equivalent of an RMarkdown / RStudio tool that generates a final report from a declarative set of instructions.
With formulas, custom formats, charts, etc. I think you can make a pretty solid case that Excel is far more than a form.
But even if you are just using it as a form, with bulk operations and keyboard shortcuts, it's usually much easier to fill out an Excel form vs. a web / app form.
I've seen it countless times. Somebody sends an excel file over e-mail and it is either an old outdated version of the file, or the persons that receives it hangs on to it for months as a source of reference.
With a conventional LAN with a fileserver, it is already easy to keep one spreadsheet. I don't think people can edit it together.
MS Access is much better for allowing simultaneous editing, especially (IIRC) if it's backed by an RDBMS, since it will use row level locks.
My previous employer had several databases with read-only web interfaces, but Access editing interfaces. They're very fast to develop, and support all the usual database features (constraints, keys, search etc), and could be set to load a form-editing view by default.
Excel allows for the democratization of technology, decreasing the barriers to entry and allowing for easier user experience.
People/teams/companies need different degrees of security, complexity, data, etc to their day-to-day operations. Someone may be interested in fiddling with data without others seeing, others may not want to share certain data broadly (answering your point below). Excel has served as a way to unify these needs for the most part. Some other options available tends to fall on the outer-layers, where excel may not be optimal and someone felt a specialized tool could improve speed/quality/cost.
I agree with most of the answers already posted about why Excel is so ubiquitous. To respond to this comment specifically, though, I'll just mention that we use a product called EASA to enable us put a web app on top of critical spreadsheets while also connecting to a database i.e. you can "de-couple" the data from the logic. There are some use-cases that describes this here:
https://easasoftware.com/case-studies/amlin-reducing-risks-e...
Most programmers mistake the majority of the use of Excel as the creation of software. It isn't. It's mostly just construction of reports; sometimes just for one-off purposes.
I would theoretically be possible to write some software/reporting solution for every use of Excel but it wouldn't be an efficient use of resources.
And even the one-off reports represent the ability to do coding in tiny increments, precisely tuned to the demand. You can find some report from months ago and just open it up and add to it.
And as you're adding features, you instantly see the results; even as you type if you use the formula builder.
If you're working on any kind of software tooling, watching how people work with Excel is incredibly insightful.
Anybody being able to quickly make a significant change has its ups and downs, though... On more than one occasion I've caught word of some fancy spreadsheet a different team in a company has been using to draw insights for months, only to have nontrivial critiques of the source data or interpretation of the data that caused big changes in the results - stuff we would have ideally caught in a dev team's code review or QA workflow, but those processes are entirely bypassed when someone on the marketing team makes a spreadhsheet on their computer and dumps audience data from an advertising platform to seed it.
Spreadsheets are nontechnical users' version of "move fast and break things". Often times that can be useful, even essential, but there are many more cases where the primary utility of working that way is also the major flaw. Its faster than traditional report development because it skips any kind of review or QA.
> I would theoretically be possible to write some software/reporting solution for every use of Excel but it wouldn't be an efficient use of resources.
One company I know which did this was called "Actuate"; that was years ago, a quick googling shows they were bought, now called "OpenText" (?) - also had something to do with MongoDB and something called BIRT (Business Intelligence and Reporting Tools)...
Last I had any involvement with them for a company I worked for, it was with their reporting suite of tools; the company I worked for became a VAR for them, so anytime the tool had a bug, we could quickly get them in an IRC chat (told you it was a while back) and get a patch within a few hours to a day or so, depending on the issue.
The reporting system was fairly unique for the time; a basic WYSIWYG drag and drop editor, ala VB - and for more complex designs, a VB-like object-oriented "reporting design" language. It was essentially what VB6 should have been on the OOP side of things, but geared entirely toward reporting.
Reports could pull data from any number of different sources, from flat ascii files, to excel documents, to database engines, ODBC, etc.
One of the last things I heard from them, at a seminar they put on that my company sent me to in Santa Monica, was this idea of reforming people using Excel spreadsheets. They called the whole sharing and copying of such spreadsheets, such that there was no one single authoritative source - a "spreadmart". So they wanted to do something about it.
They came up with some kind of Java-based solution with a custom front-end that looked exactly like Excel of the time, worked just like it - pivot tables, whole nine yards. But it interfaced to a backend using these "business logic" modules. The idea was all instances of the "spreadsheet" could see the same data via those modules, and those modules would be created by an IT staff (or DBA or something), and anything interacting with them in a company would have to go thru them - reports, gui, excel spreadsheets, etc. The idea was to present one cohesive view of the data for all users.
I don't know where it ended up - if they launched the product or not (we were being given a sneak peek at it at the seminar - it was a pretty nice demo, overall for the time).
A couple of weeks after coming back from the seminar, the company let me go (I'd worked for them for about 8 years - missed the whole "dot-com" thing because I believed in this company so much - lived and learned).
Excel is a great tool for MVPs. It's much cheaper to test internal automation within a company with Excel than to build a entire platform, and some users can do it without the need for professional developers.
Congrats on the product!
And what a coincidence, I'm about to launch a product that 'Turns a (bunch of) Word files into static website': https://docxmanager.com
We are both building a software basing on Office :)
I didn't get if some of the code shown on the page is written as macros in the Excel file itself or are all of them external. Some of it like the cell protection stuff seemed like within the sheet?
It seems you still need to code the plumbing to get the sheet to work and serve it in some webserver, so you might not be able to get rid of your developers any time soon. And I didn't see any mention of it but it seems to be using Java (since it mentions mvn in the example's GitHub page).
There is a known issue that the current beta version works with only 100% zoom level. Please check your browser and OS display zoom level and make it 100% to try it out before the fixed version is uploaded.
I can't wait to post what I've been building with Excel... from the reaction here, there's going to be some cringing happening.
One thing that I've found amazing is that if you build from scratch, you can have a cross-platform native desktop application that runs on software that's pretty much installed on all company computers.
"Cross platform". If cross platform means "pretty much installed on all company" then sure, but if it means ubiquitous among all platforms, then decidedly know. Excel is a huge piece of software, and it will probably never be cross platform, simply because it would take longer to port than the lifespan of the platform you were to port it to.
You're correct in saying that Excel is not cross-platform, but I'm referring to my application that lives on top of Excel. If the user is running Excel 16.0 64Bit, my app can run on Windows or Mac... does that not make it cross-platform?
Probably very similar to my desire to climb mountains. I just wanna see what it looks like on other side... aka curiosity.
If you’re only doing things other people tell you that you should, it’s worth taking some time for self-reflection to figure out why, and to find out what you _yourself_ are interested in.
> ... my desire to climb mountains. I just wanna see what it looks like on other side... aka curiosity.
I wonder for some period of time why people like to climb, so I am excited by your answer. Given all the documentaries, news and travel ads, one can reasonably imagine what it looks like on the other side, don't we? Moreover, its geometry would remain the same in one's life time, for any specific mountain. One can get a pretty close idea from Google Map's terrain view. If one is not that into the details, like the precise curvature for each piece of a mountain, then they are largely the same.
I find myself not attracted to climbing for exactly the reason that they are not different enough from each other to satisfy my curiosity.
Ah, I used to think this way, it’s kind of a “peakbagger” mentality... bag the peak and move on. I’m all for seeing new mountains, and actually there is a _ton_ of variation in what you can find due to altitude, aspect, geographic location, etc etc. In the same valley I’ve walked down lush green forest on one side, and walked up dry baking desert on the other. I think the more I’ve trained my eye, the more unique qualities I see.
In the last year or two though I’ve realized that every climb is it’s own experience, even on the same mountain. The season, the plants and animals that come with it (or snow!), the weather that particular day, whether it’s a dry or wet year, the time of day. Some days you get turned back before you reach the peak. Maybe an avalanche happened overnight. Or you time it just right to get a view of sunset/sunrise/aurora/milky way. There’s always something new to learn even if it’s your 100th ascent. They actually change much more than you’d think.
I always thought it would be tough or impossible to, say, get all the 13ers/14ers in Colorado, or all the 4,000ers in the northeast. Now I realize I could spend a lifetime just getting to really know a handful of them. I find a strange comfort in that. Maybe I’m getting older.
Also, I love to ski and snowboard. Gotta get up to ride down! But I’ve never lost the desire to go around one more bend, or over one more peak, to see what I can see beyond.
By all means, please go after any pursuits that you like and also climb any mountain, that you wish. But, please do not write 'web' and 'excel' in the same sentence. There are bigger and better problems that we should be going after.
And you might want to still use it in x years time after Google decides to kill-off Sheets. You must be very wary when building anything on a Google service.
And what happens if Keikai fails long before Google gets rid of sheets? I get that Google has a habit of killing things off, but they are often things that are unpopular. Sheets is far from unpopular.
Yep, I agree it's always a risk; but if you were really concerned you could perhaps acquire a small outfit like Keikai and protect yourself. Your money just isn't interesting to Google so that's never an option.
And I don't believe it's true to say they've only killed unpopular things. Anything outside of the core business (and I will argue Sheets/Docs is outside) is up for the chop.
So 4 million G Suite business customers and 70 million educational G Suite customers is outside the core business? I would say 74 million customers is a major part of any business.
G Suite is a lot more than just Google Sheets. And while it's not advertising, I think you are right and I was a bit hasty to declare it as outside their core business.
They do not have 74 million paying customers. However, I agree that the "free" version is safe for as long as the enterprise offering continues to do well (5 million PAYING users as of Feb 2019: https://cloud.google.com/blog/products/g-suite/5-million-and...).
Before Sheets was acquired by Google it was known as XL2Web, and it was strikingly similar to this. I can't find a lot of press, but see [0], for example - basically the same article, just with 2004-isms like using XML.
We used something like this to build configurators for clients at a previous job some 10+ years ago. I always thought they should have sold it as a service rather than just internal tooling the clients didn’t know anything about.
I love spreadsheets. But not like this. Take a step back and really look at it. This is horrible web UI design. Even as just a spreadsheet it could use a lot of work. For example, it would probably function much better as a single sheet rather than having to cycle through three screens for each conversion.
Version control is a big one for financial institutions, because there's no way to verify what the state of a spreadsheet was when a particular analysis was done.
One place I worked at created a Python library called the DAG (Directed Acyclic Graph) for this. It enabled an easy way to translate a chain of Excel cell functions into Python code, which can then be version controlled, diffed, code reviewed, etc.
And this could be the true Excel killer. If we figure out a paradigm for nested structures (or simple sql relationships really) that is as easy to work with as Excel we would have a very strong case for moving users away from spreadsheets. Every time data is interrelated in Excel it becomes a hot, very manual, mess.
Excel allows creating primary/foreign key relationships between tables using the Data tab, but you can only use these relationships to produce pivot tables, not in normal formulas.
Doing something like this for learning, innovation and fun? Sure! But otherwise I don't understand why people are doing things like these, especially with Excel.
Boss asks you for currencies of several countries, so you present a table with the data. The easiest way would be to make it in Excel. Then they ask you to keep a record of the currencies they are buying/selling. Again, the easiest database to do that is an Excel file. You have something that works and didn't need the help of anyone to do it.
The important point would be to recognize that when they ask you this for the second or third time it might be time to automatize it.
Or don't please, I think it's a terrible idea to abuse spreadsheets like this.
I'm being forced by my manager to do this for a client due to a close deadline (the idea is to implement a spreadsheet as interface to make it quicker than a classic web app), lbut they keep asking me to implement advanced features that would be 10 times faster to implement with a regular interface or sometimes not even possible on a sheet. Spreadsheets have a precise use case and it's not to replace web apps.
I had a situation in a certain business field where the calculations the customer designed and implemented in a spreadsheet changed so regularly that implementing them in code was unworkable because by the time the code was out if was both late and outdated. I designed[0] a solution that recursively parsed and resolved the formulas from a list of input and output cells, built an AST out of it and transpiled it to actual code or bytecode. The prototype worked beautifully and made the result diffable, extendable with, and easily embeddable within real code. My then boss found the solution useless and NASA-class, outright rejected it, and had another team just spin up some Excel, calling it with OLE or whatever and called it a day, which had abysmal performance, reliability, and runtime cost (as in $$) which ended up killing the deal with the now irate customer.
[0] Actually since I had the idea waaaay before the business need and project, I had a personal MIT-licensed clean room toy implementation of it sleeping somewhere on my hard drive and used that for the business prototype.
Thank you! That's the plan since the onset as soon as I have some time to turn this into not a glorious hack that basically only runs on my machine. Just the Excel and OpenDocument formula parser (using PEG via treetop in Ruby, can't recall what I used for Go, got a Python thing too) could be very useful to some already.
Honestly even as a horrible hack that only runs on your machine, I think I’d be interested in seeing it. PM me and I’d be happy to help make it not-so-hacky. I say this as someone who has plenty of projects in the “drafts” bin that often just needed a conspirator to have a reason to push things over the line.
The reason is, I don't think there is a single website out there, which couldn't be described in a spreadsheet.
What I suggest though, is take a look at this, and throw your companies sheet at it in a 30 minute hack session. If this proves unfruitful, I'd be happy to hear all about it.
Spreadsheets are a terrible way to make web-sites, though.
One of the bits I agree about, is that we should be very wary that we, developers, don't get replaced by one.
> One of the bits I agree about, is that we should be very wary that we, developers, don't get replaced by one.
Friendly reminder that this is literally the whole point of the job. Automation. Making people stop doing tasks that can be done by machines - which often means replacing people with code. Sometimes even ourselves.
Well, too bad the smart guys in Silicon Valley are busy perfecting their yak shaving, otherwise I'm sure they would have found the holy grail of business software that us foolish dark matter programmers have failed to deliver on for several decades in no time.
Stop encouraging people to use Excel for tasks where it is a poor fit. I have wasted so many hours of my life because of Excel and people using it for non-spreadsheet purposes.
I must be part of the dying breed too. Having been involved in migrating very broken Excel/Access/VBA into properly formed web applications for companies I've worked at, the problem is people using the wrong tool in the first place. It may meet their immediate need but with no viewpoint on it's future usage, then fragility sets in and it costs the company more in upkeep and resource. I can see how that happens, but perpetuating it seems a bad idea.
Worked on a project last winter to add a fully featured compliance management system to a state regulatory agency in Access. They have moved to a sql server backend but the support for database operations is incredibly limited by the Access JET DB engine. Ended up just using ADO in my VBA scripts for everything instead of the Access database tools because it was not capable enough for the complexity. This client doesn't have the budget to migrate from Access, but has the budget to continue slapping garbage to it every year. At least as the primary NGO involved works with other states to create a web-based solution, my client might be able to move away from Access by 2025? I would say the majority of orgs that encounter this really have no choice in perpetuating the use of bad systems.
Looking at it from the perspective of a developer tasked with making a proper app from a spreadsheet, you're bound to come to that conclusion. But that's with the benefit of hindsight and with a massive dose of survivorship bias.
Of all the spreadsheets that start out exactly like the ones that need to be converted to an app, how many were thrown away having served their temporary purpose just fine? How many of them never outgrow the immediate needs and skills of its creator?
Perhaps spreadsheets should be seen as tools for business process prototyping. Starting a proper software engineering project only after a task has outgrown its spreadsheet based implementation may be a pretty efficient approach.
I share your opinion. As a developer, I do not like Excel and the scripting environment around it, but I realize that I am not the target audience: it’s an incredible tool for analysts, managers and whatnot to explore and analyze data.
I work with a lot of data in my daily life, and see most BI tools struggling to recreate the same functionality that Excel offers, often failing in the process.
People try to recreate the Excel experience online too much, forgetting that Excel itself is a really solid solution and it’s worth taking serious.
Excel has some real limitations with large datasets and multiple users accessing it. It's far better to use something like Access or SQL for these purposes. It's also nicer in access when creating a form for people to use that pulls data from multiple input tables. For smaller data sets or sharing access quickly Excel is a champ and even when it fails it tends to try to do so gracefully. VBA does add a great amount of usefulness and is wonderful as an entry scripting language.
A surprising number of people have difficulty understanding Java or JS or Haskell or any "traditional" programming language but immediately grok complex Excel workbooks with VBA macros
Why would VBA be better than Python, Julia or R for handling complex math? Seems like the language would be really cumbersome and lacking in libraries relative to those languages.
I once worked for a company where the owner had written a Point of Sales system in Excel. It handled mixed orders where some product was given to the customer immediately, some they could come back later and it would be packed for them to grab, and some would be shipped after the event. There were hundreds of SKUs, up to 8 computers taking orders, and most scary of all, they were networked together so that another computer running excel could collect sales data and help the production team figure out how many of each product they should make next. Also the order fulfillment team used the same collected data.
It was brilliant. Excel was also the wrong tool. We constantly had problems with file locks, network shares, etc. After one year I worked with another guy to rewrite it as a web app.
It's worth noting that the described flow could've been fully automated from within the Excel itself, with just a few macros and formulas. I've created a spreadsheet doing very similar thing (grabbing the exchange rate from net and generating invoice) like a decade ago, so it's not even something new. The main issue people have with Excel is the same as with vim, majority of users just don't use it properly and know only the most basic functionality - which results in them doing a lot of repetitive things manually. I don't mind using tools like keikai, they're one approach to solve the problem, but perhaps companies could also try to send their employees on a proper Excel course first.
I've been able to learn vim because the web is full of high quality content that will teach it from the basics in a way that's compatible with my mindset.
I never had the same experience learning Excel, maybe the problem is my mindset. The only learning experience I enjoyed about excel is 'You suck at Excel' by Joel Spolsky.
Can someone recommend a good Excel learning path for someone in this situation?
The best way to learn Excel is to use it at work. I thought I was fantastic at Excel/VBA until I got my first finance role. Learned more in 3 months than I could have learned in 3 years on my own.
One secret I'll let you in on: most people use plugins like KuTools to do the heavy lifting. There are tons of industry-specific plugins. When you really can't find a plugin to do what you want, then, and only then, should you be writing macros and saving them to your xlsb.
But what if you're not able to use it at work? Then I recommend picking up pet projects and continually look for ways to improve. Just keep asking, "could this be easier?"
Whenever you get stuck on Excel issues, I recommend watching Excelisfun [0] who has thousands of hours of video content on every Excel feature you can think of. If you have difficulties with VBA, check out MSDN help docs.
P.S. Don't write a fuzzy text matching algo yourself. You will drive yourself crazy and Microsoft has one for free to download.
Yes, get a job in finance and learn off your colleagues. Sorry but that seems to be what most people I know who ate good at Excel did. In reality it is just like programming, practice solving real problems with a team of experienced people.
The described workflow can be entirely designed in a visual tool like EasyMorph* (https://easymorph.com) without any need for scripting at all. Plus the benefits like support for version control systems, auto-documentation, scheduling, etc.
How do you do version control with code embedded into spreadsheets? Am I not better off making code that generates a spreadsheet (or CSV or whatever formats I care about)?
It's possible to export the code to text files. MZ-Tools [1] can do it, or use VBA directly [2]. Xtrail [3] is popular in Finance companies but I haven't used it personally
It’s been a long time since I looked into it but I remember a version of Office like 20 years ago that integrated with version control. I wonder if that’s still a thing.
There is a solution developed in France, WeMaik [0]that is very similar. We've used their solution in the consulting firm I'm a part of.
The clients are very satisfied, because the web app that's outputted by WeMaik is so easy to design and produce that they're produced by industry-knowledgeable consultants, and not web developers.
We go from idea to actionable mock-up in half a day, present it to the client, and then build the real solution assisted by WeMaik. The connections, queries, logic are not no-code, though, for this solution. We describe the specs in the spreadsheets we send to WeMaik, and they configure it.
I’ve built http://upstart.me entirely on Google Sheets. Haven’t had any issues and works really well. This allows me to collect data from newsletter through a Google Form - which gets stored in a Sheet - that I can then curate, copy & paste to an other sheet that is used as the DB for the Live website.
Im also working on an internal CRM tool for http://fairpixels.pro with a sleek frontend that can be used as a browser homepage (showing key business stats) all while the same Sheet is used to manage customers.
I highly recommend looking into this because it allows for a simple to manage backend for those that live in Sheets.
We were recently considering quick prototyping using Google Forms but ran into two key limitations:
(1) Getting a _validated_ email address of the submitter. It does support an email field, but makes it a free-form text input with no way of enforcing ownership of that email.
(2) Pre-populating some of the fields when we send the user to a form via a link. Without this, the user ends up filling the fields even though they have already submitted that information in another form.
Are there any powerful (forms + spreadsheet) products out there that solve these (and are cheaper than, say, AirTable) ?
Calcapp may be what you're looking for. You can validate text fields using regular expressions and there's an example of how to validate an e-mail address here: https://www.calcapp.net/learn/beyond-numbers.html#validating.... Fields can be pre-populated with either values you know ahead-of-time or through Excel-like formulas.
Yes. We can make one in 30 minutes with https://qbix.com/platform .
But what is the goal of this? I want to understand since it sounds like you need real PROFILES for reusable personal data.
Excel is a dead-end road. I view all software as a vehicle to achieve more software. In other words, the solutions of today become the platforms of tomorrow. To that end, anything made in Excel is completely worthless. You can get some way by using excel, but at some point you want to build something on top of it, and at that point you will have to throw your entire excel solution out the window.
Excel sheets are nothing buy a worse version of bash scripts, at least in my opinion. At least bash scripts let you plug them into something bigger using standard process operations.
Spreadsheets are a great way for non-programmers to prove an algorithm or calculation. I always encourage non-technical people to start in a spreadsheet, because if they can model it in a two-dimensional spreadsheet, a programmer can very easily extract that out into working code. Spreadsheets are a perfect working reference.
I suspect you've never worked in a non-software business environment. Your statements live up to your username. I'm sure you're an intelligent and skilled person, but you're speaking outside your wheelhouse right now.
> To that end, anything made in Excel is completely worthless.
I suspect this lady and her employer find worth in this approach, which would make your assertion incorrect.
I've also seen numerous $100k+++ "proper" software projects advocated by people who share a similar mindset as you, when a mildly complicated spreadsheet that could be built in a week or two could accomplish the same thing and more.
You took your logic train too far. Thousands, nay, millions of people and groups stop somewhere past "I need to table some data, do some calculations and conditionals and dashboards" and before "We need this to be a multi-user application with app and data tiers".
Bash scripts don't visualize. Bash is harder to learn than Excel.
Go to vertex42.com and tell me that everyone who could ever use any of those templates will invariably need to throw it all away and ask a developer to build a custom tool to replace it.
The OP's application is a bit of a shoehorn for a spreadsheet, but do you foresee alternatives to excel beating it at its own game at some point in the future?
https://www.ecb.europa.eu/stats/policy_and_exchange_rates/eu...