Forum Discussion
DISCUSSION: A future for Excel as a programming platform
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].
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:
- Do you see such 'programming approaches' featuring strongly in the future of Excel?
- Is it time the finance community (and others) formulated new standards to embrace new functionality?
- 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?
7 Replies
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!
- 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 .- 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.
PeterBartholomew1Nice construction, will experiment with them, but they certainly look promising! To muse on your questions, see my responses below
- 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
- I think so, and hope so as well. Things I'd think would be possible steps in that direction:
- 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.
- 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. 🙂
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.
- Do you see such 'programming approaches' featuring strongly in the future of Excel?
