Cells showing #VALUE when making a lot of API calls

[Bruno Jehle]

Hi all,

I have made a function that makes API calls to load data in Excel.

It works well with a reasonable amount of simultaneous calls (let's say up to 500-1000).

But when there are more cells using the function, these cells end with a #VALUE content
I have te revalidate the formulas in the cells for the content to load.

I know I should group API calls to reduce the number of requets but I can't change the structure of the API for now.
Is there a workaround to avoid these #VALUE (looks like timeout from Excel ?) and just continue loading data from the API until all cells have their proper content ?

Thanks in advance for your help.

Below is the main part of my function.cs :

public class IntelliSenseAddIn : IExcelAddIn

{

    public void AutoOpen()

    {

        ExcelRegistration.GetExcelFunctions()

          .ProcessParameterConversions(GetPostAsyncReturnConversionConfig())

          .ProcessAsyncRegistrations(nativeAsyncIfAvailable: false)

          .RegisterFunctions();

        IntelliSenseServer.Install();

    }

    public void AutoClose()

    {

        IntelliSenseServer.Uninstall();

    }

    static ParameterConversionConfiguration GetPostAsyncReturnConversionConfig()

    {

        var rval = ExcelError.ExcelErrorGettingData;

        return new ParameterConversionConfiguration()

          .AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression<Func<object, object>>)

            ((object returnValue) => returnValue.Equals(ExcelError.ExcelErrorNA) ? rval : returnValue)));

    }

}

public static class MyFunctions

{

    private static Dictionary<string, object> cache = new Dictionary<string, object>();

    public static void ClearCache()

    {

        cache.Clear();

    }

    [ExcelFunction(Description = "Download data")]

    public static object BULLIT(

          [ExcelArgument(Name = "Ticker", Description = "ID of the asset")]

      // ... Some other ExcelArguments

    )

    {

        string cacheKey = $"{ticker}-{data_request}-{period_start}-{period_end}-{transparency}-{order}-{direction}-{titles}";

        if (cache.ContainsKey(cacheKey))

        {

            return cache[cacheKey];

        }

        var functionName = nameof(BULLIT);

        var parameters = new object[] { ticker, data_request, period_start, period_end, transparency, order, direction, titles };

        return ExcelAsyncUtil.RunTask(functionName, parameters, async () =>

        {

            var url = "https://www.com/API";

            var username = "xxx";

            var password = "xxx";

            var PostData = new Dictionary<string, string>

          {

          {"username", username},

          {"password", password},

                // ... Some other PostData

          };

            HttpClient myHttpClient = new();

            HttpContent content = new FormUrlEncodedContent(PostData);

            HttpResponseMessage response = await myHttpClient.PostAsync(url, content).ConfigureAwait(false);

            if (response.IsSuccessStatusCode)

            {

                string jsonString = await response.Content.ReadAsStringAsync().ConfigureAwait(false);

                Json json = JsonSerializer.Deserialize<Json>(jsonString);

                if (json != null && json.Error == "No error" && json.Data != null)

                {

                    int rows = json.Data.Count;

                    int columns = json.Data[0].Value.Count;

                    object[,] result = new object[rows, columns];

                    for (int i = 0; i < rows; i++)

                    {

                        for (int j = 0; j < columns; j++)

                        {

                            if (json.Data[i].Type[j] == "DOUBLE")

                            {

                                if (double.TryParse(json.Data[i].Value[j], NumberStyles.Any, CultureInfo.InvariantCulture, out double numericValue))

                                {

                                    result[i, j] = numericValue;

                                }

                                else

                                {

                                    result[i, j] = json.Data[i].Value[j];

                                }

                            }

                            else

                            {

                                result[i, j] = json.Data[i].Value[j];

                            }

                        }

                    }

                    cache[cacheKey] = result;

                    return result;

                }

                else

                {

                    object[,] result = new object[1, 1];

                    if (json != null && json.Error != null)

                    {

                        cache[cacheKey] = result;

                        result[0, 0] = json.Error;

                    }

                    else

                    {

                        result[0, 0] = "#API_ERROR";

                    }

                    return result;

                }

            }

            else

            {

                object[,] result = new object[1, 1];

                result[0, 0] = "#API_UNAVAILABLE";

                return result;

            }

        });

    }

}

[Govert van Drimmelen]

Hi Bruno,

 

If the inside of your async function fails, suppose you get an exception from the myHttpClient.PostAsync call, then the function will return #VALUE.

If you don’t want the error returned to Excel, leaving the cell with the error result, then don’t complete the async Task until you have a result (or you really give up).

 

I would suggest adding some error management inside the async function, so that you can catch exceptions and log them somewhere (so that you can figure out why it’s failing) and retry if you want to.

 

You probably want to not create a new HttpClient object for every call – make one that you set up and then share between the calls.

There are some settings you can also try, like HttpClient.Timeout.

 

If you have no control of the back end system, it’s tricky, but most likely you are just making too many calls at once to the back end, and so the calls start failing.

If you control the back end, it’s much better to batch the functions into fewer back-end calls.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/f54c670a-88c6-4605-ab0e-d40679daa67an%40googlegroups.com.

[Bruno Jehle]

Thanks Govert for you quick reply

I asked ChatGPT before your replied and it said the same as you
My main problem was not to reuse the HttpClient by creating a new one for every call
It also suggested to use SemaphoreSlim to limit simultaneous calls to 10 and to use thread-safe cache

It seems to works like a charm now, plus it is like 10 times quicker now to load a sheet with thousands of data
Magic

Many thanks to you

Bruno