The LAMBDA function is used to create reusable custom functions, which can be called using a "friendly" name. As an introduction to using the LAMBDA function, a previous post detailed using the Excel LEN function to determine the number of decimals of the tick size for a symbol. That result was used in a different cell for setting the parameter of Excel's TEXT function. This process properly automatically formatted market prices. This post introduces using the LAMBDA function to combine the two steps detailed into one callable custom function.
This image is from Microsoft's support document for the LAMBDA function.
There are two aspects, the "parameters", which can be a value that you want to pass to the function. This can be a cell reference, string, or number. You can enter up to 253 parameters. This argument is optional. The "calculation" or formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required. Above, the parameters are 1 and 3, the calculation is 1+3 and the result is 4.
The LAMBDA function enables the user to streamline their workflow. The function is perfect to replace commonly used formulas that the user needs to enter into cells. The user can eliminate the need to copy and paste formulas. Once created the user can add the functions to the native Excel function library. Additionally, non-programmers can benefit from its use as a LAMBDA function as it doesn't require a working knowledge of VBA, macros, or JavaScript.
In the post "Using Excel's IFS function" the sample spreadsheet uses two cells to deliver to the TEXT function, applied in a separate cell, the proper parameter for formatting each price called using an RTD function. Creating a LAMBDA function reduces the steps down to just one.
The LAMBDA function requires the parameters to be declared first in the function. You can use something as simple as an A and a B. For example:
=LAMBDA(A,B,
Next, the calculation is set up. Again, from the post "Using Excel's IFS Function" the calculation is applying the TEXT function to a market price supplied by an RTD call. The TEXT function is using the formatting found by pulling in the tick size using RTD for the symbol. The IFS function is used (it checks for a condition using an "IF Then" process) and stops when the condition is true. The length of the tick size is reduced by two to avoid counting the first 0 and decimal place. Here is the full formula. Everything after "=LAMBDA(A,B," is the calculation:
=LAMBDA(A,B,TEXT(B,IFS(LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))=1,"#",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=1,"#.0",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=2,"#.00",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=3,"#.000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=4,"#.0000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=5,"#.00000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=6,"#.000000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=7,"#.0000000")))The above function can be used in Excel by adding the two parameters (A and B) at the end of the function. In the example below, cell A2 holds the symbol "EP?" and cell B2 is an RTD call for the "Last Trade", i.e.:
=RTD("cqg.rtd",,"ContractData",$A2,"LastTrade",,"T")".
The function now looks like this with the addition of the two parameters (A2,B2):
=LAMBDA(A,B,TEXT(B,IFS(LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))=1,"#",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=1,"#.0",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=2,"#.00",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=3,"#.000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=4,"#.0000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=5,"#.00000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=6,"#.000000",LEN(RTD("cqg.rtd",,"ContractData","Tsize("&A&")","LastQuoteToday",,"T"))-2=7,"#.0000000")))(A2,B2)Excel offers the ability that once a function is created a "Friendly name" can be created. Go to the Formulas tab and select Name Manager.
The Name Manager dialog opens.
Click New, and in this example the "Name" is FormatPrice, the "Comment" section details the parameters and in the "Refers To" section the entire LAMBDA function is copied and pasted. Do not include the references to the parameters, above this is the (A2,B2).
Now, the Function is included in the native Excel function library. Entering the start of the function in a cell and the Excel function library dropdown list opens.
In the image below, Parameter A is the symbol and is pulled from cell $A2. Parameter B is an RTD formula to pull in the Last Trade price. In the RTD formula the symbol is also referenced from cell $A2 and "LastTrade" is referenced from cell $B$1.
This link is to a post that details how to easily determine the syntax for the RTD formula to use for parameter B.
This post walked through the steps to writing customized functions that can easily be used when designing dashboards. The LAMBDA function can make the workflow more efficient.
Requirements: CQG Integrated Client or QTrader, and Excel 365 (locally installed, not in the Cloud) or more recent.