DISCUSSION: A future for Excel as a programming platform

[Peter Bartholomew]

The past of Excel lies very much in the tradition of low-tech, ad-hoc solutions that allow end-users to interact with their 'numbers' with little or no knowledge or interest in programming.

Now we are faced with the announcement of Lambda functions and claims that Excel is 'Turing-complete'.  I have only a hazy idea what that implies, but I take it as meaning that 'anything that can be calculated in any other functional or imperative programming language, can now be calculated in Excel.  I am not sure that is strictly true given current limits on the depth of recursion of Lambda functions, but the intention is clear.

In the video, I have used nested Lambda functions to access non-normalised data as if it were a multi-dimensional array [so next time I look at a question on the forum and think 'frankly, I wouldn't start from here', I have a way forward].  

https://youtu.be/r4DD9PeVtmA

Note: The reference to @Bill Jelen  in the attached file comes from the fact that when I scanned my Excel folders for a suitable data set I lighted upon one of Bill's formula challenges.  Most of the solutions Bill received at the time were built using Power Query.  The Lambda functions described here offer a further solution almost by accident.

Points that might prove worthy of further discussion could include:

1. Do you see such 'programming approaches' featuring strongly  in the future of Excel?
2. Is it time the finance community (and others) formulated new standards to embrace new functionality?
3. Can there ever be such a thing as an Excel community or will traditionalists forever be at odds with those embracing new, more programming-oriented, methods? [A similar question arises in the context of the BI power tools which also permit the development of radically different solutions]

I will be very interested to see how others view the new developments and whether they will be coming along for the ride.  Maybe you look elsewhere when it comes to 'serious' programming, or maybe you see the future as AI (artificial intelligence) providing functionality without involving the end user?

[#2]

@Peter BartholomewNice construction, will experiment with them, but they certainly look promising! To muse on your questions, see my responses below

1. Do you see such 'programming approaches' featuring strongly  in the future of Excel?
   • I think so, and hope so as well. Things I'd think would be possible steps in that direction:
     • Native/legacy functions become higher-order functions like LAMBDA, i.e. you can pass a native function to function rather than having to wrap with a LAMBDA.
     • some analog to VBA where you can modify cells/sheets/data types with LAMBDAs or similar functions; we know some old XLM macros like EVALUATE, FILES are accessible to LAMBDA, but those can't reach outside of their instantiation and "do" anything other than provide information.
     • the LET function structure matches so much to M in Power Query, I think having a port to bring Power Query functions into worksheets would be amazing and prevent reinventing the wheel for working with Lists, Tables, etc.
     • more flexible custom data types that behave like classes
2. Is it time the finance community (and others) formulated new standards to embrace new functionality?
   • I think so, coming from an MBS-background, I think it would be a boon. For example, if I could define a "bond" data type that can store characteristics and non-static data like payment history, then use LAMBDAs to model a waterfall for REMICs, I'd be thrilled personally.
3. Can there ever be such a thing as an Excel community or will traditionalists forever be at odds with those embracing new, more programming-oriented, methods?
   
   • I think if Microsoft approaches it as I outline in 1, then the two could coexist pretty well. Allowing for more flexible methodologies of what you have to do in a sheet vs what you have to do in VBA vs what you have to do in Power Query.

These may also be the musings of someone who doesn't know as much as he thinks he does. :)

[#3]

@lori_m has just sent me a link to 

Keynote: Excel meets Lambda - Andy Gordon, Simon Peyton Jones | Lambda Days 2021 - YouTube

If you must make a choice, listen to them rather than me!  After all, I now know that my recursive Lambdas have been using 'Tail Recursion'.  My learning by trial and error takes me so far, but it will not necessarily embrace the standard terminology.

On thing that will need to be sorted, is that Andy and Simon probably shouldn't use the term MVP to refer to 'Minimum Viable Product' in the MS Excel context!

[#4]

@tboulden 

Finance is something of a closed book to me; I might even do better with general relativity!  I was still shocked by the assumptions of the FAST standard:

FAST 2.02-01   Construct all calculations in a separate calculation block

FAST 4.03-01   Do not use Excel Names

FAST 3.03-09   Do not construct array formulas

They have key recommendations that I trap as errors.  Their objective was always to use the most primitive operations that could give the result.  Exactly the opposite of your abstract data types that are constructed to capture the properties and behaviours of the objects of interest to you.  

[#5]

Thank you for the link (thx to Lori_m too) it was very interesting to see the history of how Lambda evolved.
As for Lambda functionality and my thought to many that are asking for added functionality that include more VBA type of actions (modifying other cells, system functionality interactions, etc...) I want to say that my hope and excitement for Lambda is the added functionality in an inherently safe playground (i.e. we don't need macro permissions and hopefully soon excel online should be able to natively allow their functionality). If they want to expand Lambda further then it should have some clear delimitator such that user permissions are not needed for standard added Lambdas but only required when that expanded 'LambdaVBA' is embedded .

[#6]

I suspect the 'no state change', 'no side effects' properties of functional programming will play well with those that do not wish to trust macros. I welcome the opinion of others, better informed than I, concerning the point.

[#7]

@Peter Bartholomew 

Even if I'm for decades in IT business I'm not a professional programmer, more street developer, and afraid can't support such discussion, just have not enough skills. However, few comments from practical point of view.

Functional programming in general and lambdas in particular have a long history and have their own niche, and, as everything in this world, they have their strong and weak points. Among weak ones are poor maintainability and complicated re-use. From this point of view I'm bit disappointed by Andy Gordon comments that he has no idea how lambdas will bi distributed and supported, perhaps through Twitter. LAMBDA in Excel needs to have more support, these are additional functionality like MAP(), more rich IDE compare to current formula bar and Name Manger and perhaps kind of framework. Without that it will be quite limited usage.

Declaring of Minimum Viable Product (MVP), which was quite popular few years ago, applied to LAMBDA is a bit formal. MVP is a great approach, but it's not a golden bullet which solves everything and need it's own set of tools to support.

@Liam_Bastick knows much more about financial modeling in particular and about Excel in general, perhaps he comments. From my point of view the core of FAST is to ensure reliability and maintainability taking into account current status of Excel. From that point of view it's quite reasonable.

Despite of some scepticism I consider introduction of lamdas in Excel as a great step forward. With some further support it will have it's own niche, how wide it depends on supporting tools. But IMHO, that doesn't mean LAMBDA or nothing.

[#8]

@Sergei Baklan 

I think you are under-selling the depth of your understanding!  Like you, though, I have never been a professional programmer.  I did develop and prototype computer methods for the design of aircraft, but the final coding was done under contract.  I missed the early days of spreadsheets because I was using VAX and Prime computers. 

When I did get involved, I was not especially impressed with the code developed by user interaction on the worksheet, nor by macro recorder (highly impressive mechanisms for developing bad code?).  What I look for in code is readability, relating data objects to their business context, and I want the process intent to be clearly expressed.  By 2010, my workbooks were dominated by Names and Array formulas.  I had discovered that array formulas worked when committed via Name Manager but had no way of telling whether that was the intended behaviour or some accident.  When FAST came out (in 2015?), I took it to be a claim that its proposed methods represented 'best practice' and other approaches were to be deprecated.  After a lot of discussion with Andrew Berkley of F1F9, we agreed to differ on the detail, with me accepting that their need was for shared practices that could be deployed by end-users.

Dynamic arrays, LET and now LAMBDA make my life so much easier, building on things I was doing anyway but without the pain of CSE and allowing me to use local names far more freely.  One case that I do argue is that 'simplicity' is not achieved by adopting to most primitive methods; it requires the most appropriate.  What I try to avoid it moving from being 'thought provoking' to being just plain annoying; that is very difficult to judge.