The Benefits of Combining Spark with the LAMBDA Function in Excel
For financial analysts and actuaries creating complex business logic in Excel, they may need advanced functions not available in Excel. Traditionally, analysts and actuaries have used VBA to create user-defined functions. But, another option to consider is the LAMBDA function.
LAMBDA vs. VBA in Excel
The LAMBDA function allows users to string together multiple functions in any combination. A user can name the function, set the inputs, and then use standard Excel formulas to define the calculation logic. LAMBDA functions are very flexible in function composition and chaining, and multiple LAMBDA functions can even be used together and nested with other functions to create complex calculations and models.
LAMBDA has several advantages over VBA including:
- Shorter learning curve: LAMBDA does not require any programming knowledge so it is easier to learn.
- Simplicity: LAMBDA functions are usually shorter than VBA codes. Users can define functions without writing separate code blocks, making it easier to read and understand.
- Security: Because VBA is a flexible programming language, it introduces security concerns and risks. VBA files must be saved in an .xlsm macro-enabled format that can be flagged and blocked by many filters.
- Shareability: Excel files with LAMBDA functions can be shared with outside users as a regular .xls file. They also use input prompts, so users who did create the logic can easily use the function. New users simply enter the LAMBDA function name (like a standard Excel function) and then follow the prompts.
A Better Looping Solution
LAMBDA overcomes the limitations of standard Excel functions for looping. If an analyst or actuary needs to repeat the same calculation multiple times (or repeat calculations until a certain result is reached), VBA is often the first instinct. However, LAMBDA can achieve the same result with less work, less risk, and increased flexibility. By using the IF function in Excel and defining the exit conditions, LAMBDA is a great solution to looping needs.
LAMBDA is compatible with Spark
Since LAMBDA functions are created directly in Excel, they can be seamlessly used with Coherent Spark. Spark can easily ingest and convert any LAMBDA function to a cloud-compatible workflow with greater speed than VBA, especially models with complex calculations and a high number of scenarios.
Once LAMBDA functions are on the Spark server, they can easily be shared with users across the organization. LAMBDA functions can be converted as a part of a complete model or as an individual piece of user-defined logic. This flexibility means that other analysts and actuaries can leverage the XCALL function of Coherent to use the output from one spreadsheet and send it to another model.
XCALL enables different spreadsheets to talk to each other. For example, if a spreadsheet model calculated sales and revenue and an analyst in another country needed to convert the outputs to different currencies, they could use XCALL to connect the sales and revenue model with the currency calculations. User-defined functions become building blocks that can be used in multiple models so users no longer have to dig around for a function or recreate it.
Ready to try LAMBDA with Spark?
The LAMBDA function can be a great alternative to VBA when analysts and actuaries need to create user-defined logic in a flexible way. And with the ability to easily upload it to Spark, LAMBDA functions can be shared and connected to other models in minutes.
