RTD functions explenation

[talalvas]

Hello, I have a specific question regarding RTD functions. I wrote a UDF that sends call to an API.

The function parameters are sent to the api and then an array with the call's results is returned to the sheet. The function call look like this:

=xlo_check_fedex_api(AB26, AA26, K26, AD26, E26, AC26, "USD")
AB26: =IF($D26<>"",VLOOKUP($D26,priOringsSuppliers,MATCH(AB$25,OFFSET(priOringsSuppliers,0,0,1),0),FALSE),"")
AA26: =IF($D26<>"",VLOOKUP($D26,priOringsSuppliers,MATCH(AA$25,OFFSET(priOringsSuppliers,0,0,1),0),FALSE),"")
etc...

The issue is that all of the cells that are the inputs of this formula contain formulas themselves so every change I do in the workbook, causes the function to re-calc.

When the function's parameters are constants, the function re-calc occurs only when one of the parameters are changed:

=xlo_check_fedex_api(AB26, AA26, K26, AD26, E26, AC26, "USD")
AB26: ="24060"
AA26: ="IT"
etc...

The UDF function:

@xlo.func
async def xlo_check_fedex_api(shipper_post_code: str, shipper_country_code: str, service_type: str,
                                                 weight_kg: float, quantity: int, value: float, value_curr: str='USD'):
    """Get quick rate from FedEx API"""

    services_dict = {
        'Economy': 'INTERNATIONAL_ECONOMY',
        'Priority': 'FEDEX_INTERNATIONAL_PRIORITY',
        'Priority Express': 'FEDEX_INTERNATIONAL_PRIORITY_EXPRESS'
    }
    service_code = services_dict[service_type]

    ret_dict = quick_rate_quote(shipper_post_code=shipper_post_code, shipper_country_code=shipper_country_code,
                                recipient_post_code=7565427, recipient_country_code='IL',
                                service_type=service_code, preferred_currency='USD',
                                weight_kg=weight_kg, quantity=quantity, value=value, value_curr=value_curr)

    ret_list = list(ret_dict.values())

    return [ret_list]

What are the changes I need to do in the function so it will be called only if one of the ranges in the formula input has been changed?

I've tried to figure it out from the examples about the the RTD topic but it wasn't clear how do I implement it on my function.

Thank you for your help!

[ShieldTrade]

@talalvas I am learning with xloil as you. Maybe I can help.

=xlo_check_fedex_api(AB26, AA26, K26, AD26, E26, AC26, "USD")
AB26: =IF($D26<>"",VLOOKUP($D26,priOringsSuppliers,MATCH(AB$25,OFFSET(priOringsSuppliers,0,0,1),0),FALSE),"")

OFFSET is a volatile function. Volatile functions trigger recalculation on every worksheet change, therefore xlo_check_fedex_api( Volatile..Volatile ) will be called on every worksheet change.

VLOOKUP can also trigger unexpected recalculation. Make your xloil async imputs non volatile might solve your problem.

You can usually do it using excel INDEX/MATCH.

Using xloil async you are telling Excel " while waiting for this function to get the result fell free :-) to do other calculation if needed ". But you are basically using a UDF and all UDF ( VBA, Python, C ) are recalculated when something changes in their inputs or, in case of volatile inputs, on every worksheet change.

If, for any reason, you cannot make your inputs non volatile you have to change your python and return your method before calling the api if the args are equal to the previously received args.

[talalvas]

Thanks @ShieldTrade. Your sulotion worked for me. I used to use ALOT of volatile functions. I wrote a UDF that replaces the usual =INDEX(lookupVal, namedRange, MATCH(matchVal, OFFSET(namedRng, 0, 0, 1), 0) and now the async functions works as expected.

[cunnane]

Thanks @ShieldTrade that's spot on.

@talalvas, the reason OFFSET is volatile is because it can index outside the input ranges, I hope your UDF doesn't do this!

If you're using the latest Excel, you can use XLOOKUP to do things we used to use INDEX/MATCH/VLOOKUP for in a much cleaner and non-volatile way. There's also xloIndex which can return sub-arrays (so can INDEX, but it's not as clean!)