Keep Showing Previous Values in Excel while Waiting for Async Result

[Sun Chong Fung]

Hi Team,

I am working on Excel-DNA with F#.

I am having the same issue in the following posts

https://groups.google.com/g/exceldna/c/JBft-8fOLlY/m/OVfO2DFSBgAJ

https://groups.google.com/g/exceldna/c/SRhuNAlctlc/m/VspUXPyYBgAJ

The main problem is that the following FsAsync.FsAsyncUtil.excelObserve function will run in the thread pool. It crashes if too many cells are executing this function.

Here is the code

// to create a Timer

let createTimer (interval: float) =

    let timer = new System.Timers.Timer(interval)

    timer.AutoReset <- true

    timer.Start()

    timer.Elapsed

// helper function to use with excelObserve

let performOnEvent event (f: unit -> 'b) : IObservable<'b>=

    event

    |> Observable.map (fun _ -> f ())

   

// test function

let testAsync =

    async {

        let res = rnd.Next(0,1000)

        return res

   }

let funTest () =

    Async.RunSynchronously testAsync

// example

[<ExcelFunction(Description = "Test")>]

let testObservable (str: string) =

    FsAsync.FsAsyncUtil.excelObserve "test" [|str|]

        (performOnEvent (createTimer 5000.) funTest)

I then tried to use Async.StartWithContinuations (adding timer in the async function to force it refresh) as below

let excelRunAsync functionName parameters async =

        let obsSource =

            ExcelObservableSource(

                fun () -> 

                { new IExcelObservable with

                    member __.Subscribe observer =

                        // make something like CancellationDisposable

                        let cts = new CancellationTokenSource ()

                        let disp = { new IDisposable with member __.Dispose () = cts.Cancel () }

                        // Start the async computation on this thread

                        Async.StartWithContinuations 

                            (   async, 

                                ( fun result -> 

                                    observer.OnNext(result)

                                    observer.OnCompleted () ),

                                ( fun ex -> observer.OnError ex ),

                                ( fun ex ->

                                    observer.OnCompleted () ),

                                cts.Token 

                            )

                        // return the disposable

                        disp

                }) 

        ExcelAsyncUtil.Observe (functionName, parameters, obsSource)

// test function 2

let testAsync2 (timer) =

    async {

        let res = rnd.Next(0,1000)

        return res

    }

// Timer()

let Timer () =

    FsAsync.FsAsyncUtil.excelObserve "Timer" [||]

        (performOnEvent (createTimer 5000.) (fun _ -> some random number))

// example

[<ExcelFunction(Description = "Test 2")>]

let testObservable2 () =

    FsAsync.FsAsyncUtil.excelRunAsync “test 2” [|Timer()|] (testAsync2(Timer()))

It works well with multiple cells this time. However, these cells will show #N/A in excel while pending the corresponding Async function to return a value. I want to keep the previous value in excel while the Async function is computing, so other cells may use this current values before a new values.

I am thinking to solve these problems by two approaches,

1. A way to merge the following functions “excelRunAsync” and  “excelObserve” into one single function so that it won’t give #N/A while pending Async computation.

2. A way to store the previous result and keep it while pending Async computation.

May I ask if the above approaches are possible?

Many thanks

Mark

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

You can access direct Excel-DNA support with easy billing through a corporate GitHub account.

--------------------------------------------------

Hi Mark,

Dealing with the first call to an async function or stream request is tricky, and for this RTD-style approach you have to return something to Excel like #N/A or #GETTING_DATA. 

After you have the first result, if you want to return the previous value instead of the #N/A value, one option might be to manage your own cache of returned values and return your cached value in the case where ExcelAsyncUtil.Observe returns ExcelErrorNA. 

While you might be able to get the cell value from the calling cell, this might not be the result coming of the function call only if the function is embedded in a larger formula. So I would not suggest that as a good approach.

The basic functionality that maps onto Excel's RTD feature is the excelObserve style stream of values. The excelAsync case is a special case where the stream of values returned will contain only one value and will then complete. If you consider the excelObserve stream, a question is to know when to push the next value to the sheet. With the excelAsync, you can start a new calculation if the inputs change, or if the user enters the formula again. The timer examples generate a stream of values based on the timer ticks. You can also generate stream of values by handling incoming updates from some external source.

Maybe if you describe a bit more about what the functions will be doing, other Excel-DNA users might indicate how they are approaching this.

-Govert

[Sun Chong Fung]

Hi Govert,

Thank you for getting me back quick. The cache approach is worth trying.

I am building excel functions to get external data via API. Those data will keep changing continuously (eg. AAPL price from an exchange). The excelObserve stream works well with only a little number of cells calling these functions (with timer = 5 sec). But it crashes when more cells are calling these functions. Increasing timer refreshing interval kind of solve this problem, but this is not what I want.

I suspect the problem is that these functions will run in the same thread pool as mentioned in your blog article.

I am looking for a way/ function to keep the excelObserve stream fearture while replacing the IObservable<'a> parameters with an Async<'b> parameters in the excelRunAsync function.

May I ask if it is possible?

Thanks a lot

Mark

[Govert van Drimmelen]

Hi Mark,

For this kind of application, the streaming-style functions using the IObservable pattern should be the right one, and should be able to scale to hundreds of thousands of cells without trouble. Thread pool limitation would only come is with the default async implementation - otherwise you can control the updates of a very large number of cells from one or two extra threads dealing with the external calls.

The internal implementation for the streaming function is important to get right. For example, having a large number of timers that run often and update all the cells would not work. The setup of a new observable (which would happen if you make this as separate async calls) has much more overhead than an update call for an observable stream. You also need quite an efficient structure internally to manage or receive the updates, and route this to the onNext calls. The calls to onNext can be very seldom or very frequent and can be from any thread. 

It's worth monitoring what work happens on the main thread, and making sure you are not doing some work on the main thread that could be done on separate threads. So you typically don't want to have the timer callback running on the main thread. 

-Govert