Single Observer created for every unique Symbol

Question

I am working on a .NET Application which is rendering data from a source (say TCP) to excel using the Excel-DNA Library. I have given example of LtpObserver which I have created by implementing IExcelObservable. I am creating the Observer using a Excel Function Call (Refer Code) --> "GetLtp". 'Data' referred here is containing a List of LtpObservers and Ltp value.

THE PROBLEM - When I call the formula in Excel using a symbol say 'X', it creates the Observer (verified via Logging), and the data starts updating. Constructor of observer is called, as well as, callback is received on Subscribe() Method. But, when I call the same formula for 'X in another cell, no new Observer is created, neither a call on subscribe is received. And upon deletion, of formula from just Cell#1, the Dispose is not called. But upon deleting from both Cell#1 and Cell#2 Dispose is called.

So is there just one observer for every unique symbol in the Excel Workbook? In that case does keeping a list of Observers and updating each of them on OnNext() makes sense? And what about the case when 2 or more formulas are used together in a single cell? How does this work Internally

CODE

class LtpObserver : IExcelObservable
{
    private List _observerList;
    private string _symbol;
    private Timer _timer;
   
    public LtpObserver(string symbol)
    {
        Trace.TraceInformation("Constructor Called. New Ltp Observer Loaded for Symbol : " + symbol);
        _symbol = symbol;
        _observerList = new List();
        _timer = new Timer();
        _timer.AutoReset = true;
        _timer.Interval = FeedTimerConstant.ltpFrequency;
        _timer.Elapsed += _timer_Elapsed;
    
    }

    void _timer_Elapsed(object sender, ElapsedEventArgs e)
    {
        if (Data != null))
        {
            _timer.Stop();
            Data.LtpObserver = _observerList;
            timer_tick(Data.Ltp);
           

        }
        else
        {
            timer_tick(0);
        }
    }

    public IDisposable Subscribe(IExcelObserver observer)
    {
        
        Trace.TraceInformation("LTP Subscription for : " + observer.ToString() + " Symbol : " + _symbol);

    
        _observerList.Add(observer);
        if (Data != null)
        {
            Data.LtpObserver = _observerList;               
            timer_tick(Data.Ltp);
        }
        else
        {         
            timer_tick(0);
            _timer.Start();
        }

        return new ActionDisposable(() => {
            Trace.TraceInformation("Un Subscribed for LTP Price :" + _symbol);
            _observerList.Remove(observer);
        });
    }


    void timer_tick(object _now)
    {
        
        foreach (var obs in _observerList)
            obs.OnNext(_now);
    }
}


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

[ExcelFunction(Name = "GetLtp", IsVolatile = true)]
    public static object GetLtp(string symbol)
    {
        if (String.IsNullOrEmpty(symbol))
        {
            return INVALID_SYMBOL;
        }
        return ExcelAsyncUtil.Observe("GetLtp", symbol, () => new LtpObserver(symbol)
        );
    }
    
-------------------------------------------------------------------------------------------------------------   
    
    public class Data {
    
    public decimal Ltp { get; set; }
    public List LtpObserver { get; set; }
    
    }

user_44264 · Answer

The behaviour you describe is by design. The first two parameters passed to ExcelAsyncUtil.Observe are normally a string with the function name and an object or array of objects that provide a unique identifier for the IObservable stream.

So the part you describe in "THE PROBLEM" is exactly how it is intended to work. For every unique symbol you have a stream of values, and you can use the same values in multiple places on the sheet without a problem - they will be listening to the same underlying stream. If you have multiple different symbols active, you have multiple streams that you update with whatever mechanism, using the OnNext() calls.

In your code you need not make provision for multiple IObservers that Subscribe to your IObservable. But this detail is specific to the way Excel-DNA will call your IObservable. Excel-DNA will only ever Subscribe once to your IObservable. However, if you wanted to use the same IObservable class in other contexts or in another application, this might not hold an you might get multiple Subscribe calls.

(It would also make more sense to rename LtpObserver to LtpObservable.)

You could add extra information to that list to create separate topics according to the calling cell. This code might be something like this: (I've removed the IsVolatile=true as that doesn't make sense for a streaming function wrapper like this)

    [ExcelFunction(Name = "GetLtp")]
    public static object GetLtp(string symbol)
    {
        if (String.IsNullOrEmpty(symbol))
        {
            return INVALID_SYMBOL;
        }

        var callerReference = XlCall.Excel(XlCall.xlfCaller);
        var identifiers = new object[] { symbol, callerReference };

        return ExcelAsyncUtil.Observe("GetLtp", identifiers, () => new LtpObserver(symbol, callerReference)
        );
    }

But now again you have a unique LtpObserver for the combination of symbol and calling cell. And again each such IObservable will only be subscribed to once by Excel-DNA.

Internally this is implemented using Excel's RTD mechanism. Excel can associate one or more RTD topics with a cell. When the topic value changes, Excel will invalidate the relevant cells, and recalculate them. There is some optimisation inside Excel so that this scales well.

Single Observer created for every unique Symbol

1 Answers