Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Excel warriors who save governments and companies from spreadsheet errors

wired.co.uk

Post reply on HN
Print view View original

41–50 of 193 posts

Re: Excel warriors who save governments and companies from spreadsheet errors

#41

Post by smabie »

crispyporkbites wrote: post #21

Excel is a triumph of software. Never before has there been a REPL so powerful, a programming language so widely used, an environment so simple even mere mortals can produce magic - without it what would we have? The power of software would remain closed behind the Ivory gates of the self-titled engineers with development cycles, sprints and countless hours to make a single formulae change. Fear the excel less world,…

I don't actually like Excel, and think it could be significantly better, but in general, I agree with you. Excel has allowed users with domain specific knowledge to create real honest to god software cheaply and quickly.

The biggest triumph of Excel is that you can incrementally learn new things, slowly turning a basic worksheet into a Turing complete masterpiece. In contrast, I believe that programming languages have a much higher barrier to entry.

Re: Excel warriors who save governments and companies from spreadsheet errors

#42

Post by wintorez »

no_wizard wrote: post #36
DavidPeiffer wrote: post #28

Excel suffers from being accessible to "everyone", and having a low barrier of entry. While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious t…

I am just completely amazed but yet not surprised there is an entire organization dedicated to spreadsheet risks and best practices. What a world we live in. This just goes to show what the killer app is for computers, it seems. We have these wonderful machines and 80% of people use them for browsing the web/using web apps and producing spreadsheets. Thats the reality I tend to forget sometimes.

Spreadsheet and word processor were THE killer apps of microcomputers. As long as humanity uses letters and numbers, we will have spreadsheet and word processor in some shape and form.

Re: Excel warriors who save governments and companies from spreadsheet errors

#43

Post by ourlordcaffeine »

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Same where I work. Excel was replaced completely with R a long time ago. Even automated pdf reports are made using R programs. Yet people are often shocked to hear it at first.

Re: Excel warriors who save governments and companies from spreadsheet errors

#44

Post by wintorez »

smabie wrote: post #41
crispyporkbites wrote: post #21

Excel is a triumph of software. Never before has there been a REPL so powerful, a programming language so widely used, an environment so simple even mere mortals can produce magic - without it what would we have? The power of software would remain closed behind the Ivory gates of the self-titled engineers with development cycles, sprints and countless hours to make a single formulae change. Fear the excel less world,…

I don't actually like Excel, and think it could be significantly better, but in general, I agree with you. Excel has allowed users with domain specific knowledge to create real honest to god software cheaply and quickly. The biggest triumph of Excel is that you can incrementally learn new things, slowly turning a basic worksheet into a Turing complete masterpiece. In contrast, I believe that programming languages hav…

I would like to argue that Excel (i.e. spreadsheets) are a form of programming language; albeit in tabular format. Each cell is a variable, some cells are constants, some cells are computed, cells can be conditional, etc.

A programming language must not necessary come in form of written text. We can say that concept of spreadsheet is the programming language, and Excel, Google Sheets, etc. are just implementation of the same concept.

The success of spreadsheet is in the fact that its visual and intuitive, similar to what made Visual Basic and Delphi very successful.

Re: Excel warriors who save governments and companies from spreadsheet errors

#46

Post by contextfree »

blendo wrote: post #40
cosmie wrote: post #16

Earlier quoted context omitted.

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every ste…

From the M-code link: “A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.” Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?

It's not really comparable to VBA, as it's not used to automate/script the Excel object model. There is a JavaScript scripting model in recent releases which at least overlaps in use cases with VBA but I'm not sure if you can use it to do everything that VBA can.

Re: Excel warriors who save governments and companies from spreadsheet errors

#48

Post by LegitShady »

blendo wrote: post #40
cosmie wrote: post #16

Earlier quoted context omitted.

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every ste…

From the M-code link: “A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.” Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?

M code is the query language mostly used for getting the data into power query and transforming it/cleaning it. The replacement for VBA for power pivot (as well as analysis services and power bi) is DAX (Data Analysis expressions). I've never used F# before.

Re: Excel warriors who save governments and companies from spreadsheet errors

#49

Post by Fej »

jgilias wrote: post #19

Excel is a great case study. Turns out, if you give people an accessible and widely available Touring complete environment, they will manage to do pretty much anything with it. On another note, it's interesting to observe that the problems spreadsheets have are the same problems that plague poorly designed software everywhere - lack of documentation, lack of testing, lack of input validation, among others.

It's not even just that Excel is Turing-complete. It's that Excel is widely used and is its own programming language with a comprehensive standard library, and I don't mean VBA - entering the = character in a cell opens up a massive library of functions which can be used in a fashion sort of similar to functional languages, since everything is nested functions.

There's branching. There's Boolean operations. There's variables (cells, which is ultimately the point of the entire exercise). There's even a switch construct. There's even really simple web requests! All of these are of course functions. The only thing that's missing is function definitions (which can be done with VBA, but I excluded that specifically for the sake of this exercise).

It's not particularly pretty, but is usable by almost anyone, has good documentation, and a huge community of like-minded power users. As you mentioned, Excel doesn't have any of the constructs which help developers for other languages to avoid mistakes. The simple syntax, users which are not necessarily aware of good practices, and a lack of methods to implement such practices produces a perfect storm of factors to introduce bugs.

Re: Excel warriors who save governments and companies from spreadsheet errors

#50

Post by cosmie »

blendo wrote: post #40
cosmie wrote: post #16

Earlier quoted context omitted.

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every ste…

From the M-code link: “A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.” Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?

> Does M-code serve as a replacement for (the abysmal) VBA?

Sort of depends. M (and PowerQuery in general) is fundamentally about data processing. If you were only dropping into VBA for ETL purposes, then chances are you can use PowerQuery/M instead. With the advantage that it exists in a standard xlsx file, rather than the macro-enabled xlsm files that are more heavily restricted in many environments. It's not intended for more general purpose tasks, nor can it do stuff like interact with the Excel object model directly, so you'd still need to use VBA (or if targeting Excel 2016 and above, Javascript[1]) for that.

PowerQuery/M is basically a sidecar app within Excel, and doesn't have access to nor is aware of the Excel object model. Excel.CurrentWorkbook[2] is just a convenience function available when used within Excel, and it treats data from the current workbook the same as if it was pulling from an external workbook or csv file or web API or any other source. To the extent that even when solely accessing data from within the same workbook and not calling out externally at all, it prompts users with an "Enable External Connections" warning to use. You can then either have the output of that sync to a Table object within Excel (and easily/generally available for use), or made available from within the data model (only available via the object model or pivot tables).

That said, the data access functions[3] are fairly expansive, and you can get pretty creative in how you (ab)use them. For example, Web.Action is one I've abused often: pinging a web service to track usage, triggering external jobs, and exporting/syncing data to an external service. Although those last two uses require a bit of complexity in architecting, to account for cases when it gets manually refreshed/triggered in quick succession or with duplicate data.

> And how similar is it to F#?

I'm not familiar enough with F# to be able to answer that. But if you are, you can probably find out pretty quickly via this[4] article series. PowerQuery/M is used across a variety of Microsoft products, and most resources around it are akin to product-specific "how-tos". Ben's primer series is the only source I've come across that approaches it from a software engineering perspective.

[1] https://docs.microsoft.com/en-us/office/dev/add-ins/referenc...

[2] https://docs.microsoft.com/en-us/powerquery-m/excel-currentw...

[3] https://docs.microsoft.com/en-us/powerquery-m/accessing-data...

[4] https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-p...

Post reply on HN