Forum Discussion
DISCUSSION: A future for Excel as a programming platform
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.
