I think "don't use Excel" is the wrong solution. The answer is to develop some clear metrics around when to migrate off Excel. We use some pretty big spreadsheets, and they're fine for the intended purpose.
Stop Using Excel, Finance Chiefs Tell Staffs
161–170 of 435 posts
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#162Just something I discovered recently that entirely takes care of the problem if you have ONE PERSON who knows how to code: Google Sheets with the IMPORTHTML() function. You host a web server that runs Python or PHP or whatever and it queries and pulls data from any number of places, and dumps the information to CSV. Then in the Google Doc, you request the data from your server with the IMPORTHTML function. It populat…
You mean the function inside your Doc(ument) will periodically fetch the http://myweb.com/periodically_generated.csv ? Can't you just "push" to the target Doc(ument) instead?
Yes, IMPORTHTML fetches your csv, but you can have your CSV populated by your server dynamically on each request. You can't push to a Google Sheet as far as I know.
But polling is just as good for these purposes, your data will be up to a few minutes old.
If you want true live data then you should use something else, but that's not what the finance chiefs are complaining about. They want people to stop manually importing CSV data because it's really slow, and causes lots of errors.
EDIT:
I was going off the assumption that there was actually a database somewhere. Turns out they're using Excel as the database.
I'd still argue that one person who codes can fix it. Take your Excel sheets, put them into an SQL database, and dump to CSV for importing to Google Docs for graph/display purposes.
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#163Earlier quoted context omitted.
There are upsides to excel in cases like that… The health insurance people of various sorts who use this setup are the ones with domain knowledge. With an excel program, they can (1) understand how it works (2) add to it, modify it (3) create a copy and break it (4) debug it (5) have ideas about how it could work differently… I’m not saying that excel is the tool for the job (sounds like it is definitely not), and th…
On top of what you're saying, the alternative is frequently a heap of under specified, under tested (and only manually tested!), copy-pasted and shoddy quality Cobol/Java/Oracle/C#/SQLServer/PHP/MySQL code. Yes, the performance will generally be better (1 minute to run a report, not 1 hour), but I'm not sure things will generally be better. As you said, the business people can at least modify the Excel. These systems…
> the business people can at least modify the Excel
I'm not convinced that's a good thing. If they can change the Excel then they can also fuck up the system, and if you fuck up a system like this then it's horrendously difficult to unfuck. I think systems like this look great when they're working, but the second they go tits up you realise why people are horrified by them.
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#164Just something I discovered recently that entirely takes care of the problem if you have ONE PERSON who knows how to code: Google Sheets with the IMPORTHTML() function. You host a web server that runs Python or PHP or whatever and it queries and pulls data from any number of places, and dumps the information to CSV. Then in the Google Doc, you request the data from your server with the IMPORTHTML function. It populat…
Unless I'm missing something, this only provides a way to display data from a database. But users cannot write to the database via the Google Sheet, and it thus doesn't solve the problem...
Oh god you're right, I just re-read the article and realized what they're doing now.
It didn't even occur to me that anyone would use Excel as the database. Holy hell. Nevermind.
Still, it shouldn't be hard to convert Excel to an SQL database. Then you dump to CSV, and use ImportHTML to have a spreadsheet be your source of information.
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#165Earlier quoted context omitted.
I used to be a Lotus Notes specialist, and one interesting thing I noticed is that from the earliest versions it was designed specifically to be a "step up" from a spreadsheet (Lotus of course also made Lotus 1-2-3, which was the original killer-app spreadsheet before Excel took over). Notes still let you see data as rows and columns and had very spreadsheet-like functions, many of which could be transferred directly…
> my career in business software has still mostly been about converting spreadsheets into more "proper" applications. Still true for many people in 2017. @patio11 probably said it best: > Every spreadsheet shared in a business is an angel announcing another SaaS app still needs to be built. https://twitter.com/patio11/status/655674551615942657 FWIW we ended up in business software after one of our open source librari…
No. An domain-specific SaaS app takes all the power away from the people writing their spreadsheet programs. Only one app is needed, a hosted spreadsheet with backup and versioning an replication. The only domain-specific business is in writing plugins.
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#166I would argue that the most commonly used programming language is Excel. But few of the people using it realize they're programming. It's a brilliant reactive data programming model that makes intuitive sense to non-technical users. They feel empowered to use it to solve problems right now with a computer. They experiment with it, try things, Google how to do more things- just like any programmer does. And they feel…
MS access is often a way better tool for what people do. The problem is that it requires a bit more work up front, a little more abstraction, and usually some programming. Having said that, most companies have people in IT that can help out something like that together for people but that's not their job.
Does access have a spreadsheet view for bulk editing?
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#167What should we use, then? I desperately need some tool I can tell non-tech people to use where they can input arbitrary structured data. After the data is there, in any format the user wants, as long as it is structured somehow, millions of programs can be written to use that data and combine it, but somehow the data has to be written FIRST. It is not feasible to come up with the schema, write a software based on tha…
That's a hard question to answer and depends on your size and what you really need. For small companies, Excel may really be all that you need. But at a certain size, you might want to use something like PowerBI instead since it will also pull directly from the database and you can share the dashboards you create very easily. Large companies like the article are referring to might be able to get by with PowerBI (Micr…
The same ETL functionality that's in Power BI is also available in Excel 2010+ as "Power Query".
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#168Earlier quoted context omitted.
Preferences have become a pretty natural part of using a computer, I'm not sure why we can't rely on users trying that when they run into problems. I would be worried about endless forking of defaults as a feature of newly built tools, leading to a bunch of new tools that will be harder to maintain. If they can't manage implicit data casting preferences (if those exist, they may not), surely handing them something mo…
This “change the settings” argument comes up from the Excelistas every time valid criticism is leveled at the tool. Can you point to this setting? No? I didn’t think so. That’s because it doesn’t exist. You’re arguing a hypothetical from a position of ignorance.
>Excelistas
I'm not an Excelista, and you shouldn't have assumed that considering I asked several posts up if the setting existed.
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#169Earlier quoted context omitted.
Functional programming before it was fashionable!
This is how I like to explain functional programming to developers today. When you say "Functional Programming", a lot of devs think "scary complicated thing". When you say "Basic excel spreadsheets" they go "anyone can do that!".
Excel is a very limited form of functional programming. It's sort of SIMD and it eschews side effects, but it has no first class functions which is extremely frustrated and -non-functional
Re: Stop Using Excel, Finance Chiefs Tell Staffs
#170Earlier quoted context omitted.
I've been working on a spreadsheet which is more amenable to embedding in programs (compiling to an IR which can be translated to high level languages or [eventually] compiler IRs like those of LLVM or GCC). I worked at a company where months were spent recreating models from a spreadsheet produced by subject matter experts, and this was a major cost. A spreadsheet optimized for integration into software build proces…
I'll join in with a bit of show-and-tell of my own, if I may. I'm working on a plugin for Excel for dev's and other technical people. It lets the user use SQL and C# in Excel, connect to various types of databases and move data in either direction, and provides a runtime for automation. The idea is basically to let tech users use their existing skills to make life easier. Main areas of use are: - process (live) data…
.NET and ODBC already provide C# and SQL, no?