ExcelDna.Integration.Rtd.Topic.UpdateValue() not updating Excel cells

[rob]

Hello

I noticed some cases where calling Topic.UpdateValue() does not update the target excel cells.

It happens rarely: about twice a week for a trading spreadsheet used by ~30 users. 

The spreadsheet contains approx 5,000 cells with the "Subscribe" RTD user defined function below, which is used to stream spot prices (among other data). 

When the issue happens, none of the cells receives updates.

[ExcelFunction(Description = "Subscribe to a data point updates")] 

public static object Subscribe( 

[ExcelArgument(Description = "Subscription key (data point indentifier)")] string key,

[ExcelArgument(Description = "(optional) Value when data point is missing. Default is empty string")] string defaultValue = "")

{

string[] topics = new string[2];

topics[0] = key;

topics[1] = defaultValue;

return XlCall.RTD("SubscribeExcelRtdServer", null, topics); 

}

The function accepts a "key" (string) as input, which identifies a unique data point.

The data point is updated by a separate thread connected to internal applications. 

When updates are received on this thread, a key value store (C# dictionary, attribute of the class SubscribeExcelRtdServer implementing ExcelRtdServer) is updated with the latest value received.

The publication to excel cells is managed by the function PublishExcelUpdates below, which is triggered by a timer event every 5 seconds (configurable).

I implemented this logic to control the frequency of updates sent to excel, and have more visibility and logs as there are thousands of updates per day.

public SubscribeExcelRtdServer()

{

Log.Info("SubscribeExcelRtdServer created.");

// create timer

int timerPeriod = 5000; // period in milliseconds

this.timer = new Timer(delegate

{

try { this.PublishExcelUpdates(); }

catch (Exception e) { Log.Error("Error at timer event: ", e); }

}, 

null, 0, timerPeriod);

Log.Info("Timer started. Frequency=" + timerPeriod + "ms");

}

The PublishExcelUpdates function does a loop over the key-value store in c# memory, and triggers the associated topic.UpdateValue() if needed.

I logged the number of calls to made to topic.UpdateValue() at each timer event to have more visibility.

public void PublishExcelUpdates()

{

long publishedCount = 0;

foreach (KeyValuePair<string, Topic> subscription in this.subscriptions)

{

Topic topic = subscription.Value;

try

{

if (!latestValues.ContainsKey(subscription.Key))

{

// no value to publish for the given key

continue;

}

if (latestValues.ContainsKey(subscription.Key) && publishedValues.ContainsKey(subscription.Key))

{

//if latest value = published value, do not publish

if (latestValues[subscription.Key] == publishedValues[subscription.Key])

{

continue;

}

}

// publish

object o = latestValues[subscription.Key];

topic.UpdateValue(o);

publishedCount++;

// store published value

if (this.publishedValues.ContainsKey(subscription.Key))

this.publishedValues[subscription.Key] = o;

else

this.publishedValues.Add(subscription.Key, o);

}

catch (Exception e)

{

Log.Error("Error at PublishExcelUpdates for topic: " + topic.TopicId + ": ", e) ;

}

}

Log.Info(publishedCount.ToString() + " topic values published in excel.");

}

When the issue occurs, excel stops updating completely, even though topics continue to be published during several hours. 

Example of logs, after excel stopped updating:

[...]

2018-08-29 11:31:09,597 INFO  - 112 topic values published in excel.

2018-08-29 11:31:14,606 INFO  - 381 topic values published in excel.

2018-08-29 11:31:19,606 INFO  - 199 topic values published in excel.

2018-08-29 11:31:24,615 INFO  - 276 topic values published in excel.

2018-08-29 11:31:29,623 INFO  - 135 topic values published in excel.

2018-08-29 11:31:34,632 INFO  - 182 topic values published in excel.

2018-08-29 11:31:39,640 INFO  - 127 topic values published in excel.

2018-08-29 11:31:44,648 INFO  - 220 topic values published in excel.

2018-08-29 11:31:49,657 INFO  - 172 topic values published in excel.

2018-08-29 11:31:54,657 INFO  - 173 topic values published in excel.

2018-08-29 11:31:59,666 INFO  - 218 topic values published in excel.

2018-08-29 11:32:04,675 INFO  - 188 topic values published in excel.

2018-08-29 11:32:09,683 INFO  - 156 topic values published in excel.

2018-08-29 11:32:14,693 INFO  - 144 topic values published in excel.

2018-08-29 11:32:19,700 INFO  - 143 topic values published in excel.

2018-08-29 11:32:24,708 INFO  - 173 topic values published in excel.

2018-08-29 11:32:29,709 INFO  - 144 topic values published in excel.

2018-08-29 11:32:34,718 INFO  - 138 topic values published in excel.

2018-08-29 11:32:39,726 INFO  - 167 topic values published in excel.

2018-08-29 11:32:44,734 INFO  - 153 topic values published in excel.

2018-08-29 11:32:49,742 INFO  - 154 topic values published in excel.

2018-08-29 11:32:54,750 INFO  - 121 topic values published in excel.

2018-08-29 11:32:59,758 INFO  - 143 topic values published in excel.

2018-08-29 11:33:04,767 INFO  - 117 topic values published in excel.

[...]

I inspected the excel process of a user experiencing the issue; calculation mode was set to automatic, events activated... nothing unsual, but no RTD updates.

Starting a fresh excel process resolves the problem and updates start flowing again in the new one (even when keeping the 'dead' process open).

For info, Excel version used is 14.0.7212.5000 (32-bit), and the 32b xll compiled references ExcelDna.Integration 0.33.5730.40390.

Any help would be truly appreciated.

Thanks in advance

[Govert van Drimmelen]

Hi Rob,

 

Do you have a way to test this against the beta version 0.35 (currently NuGet package version 0.35.4-beta4)?

I think that would help – there have certainly been some subtle fixed made to the RTD callback handling since v 0.33.

 

-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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[rob]

Many thanks for the prompt answer Govert, will try this today.

I'll keep you posted if the error appears in the next few days as I cannot pinpoint it.

Do you know where it could have come from?

Thank you !

[rob]

Hi Govert

I am now using pre-release package 0.35.4-beta4;

But the XlCall.RTD() always throws "N/A" when returning the default value for the function.

I debugged step by step in C# and it seems the function is called twice in a row by Excel. 

1st call returns the correct value, 2nd call returns ExcelDna.Integration.ExcelError.ExcelErrorNA.

Have you seen this issue?

Thank you

[ExcelFunction(Description = "Subscribe to a data point updates")] 

public static object Subscribe( 

[ExcelArgument(Description = "Subscription key (data point indentifier)")] string key,

[ExcelArgument(Description = "(optional) Value when data point is missing. Default is empty string")] string defaultValue = "")

{

string[] topics = new string[2];

topics[0] = key;

topics[1] = defaultValue;

return XlCall.RTD("SubscribeExcelRtdServer", null, topics); //always returns #N/A

[Govert van Drimmelen]

The C# default values are not automatically supported by Excel-DNA, so you're probably setting the RTD server topic value to null (instead of the empty string you intend), and for some reason null is problematic.

-Govert

[rob]

The function always return N/A:

It seems to be called twice by Excel; 1st call returns 239.9 (correct), second call returns ExcelErrorNA immediately after which overrides the correct value.

I tried with stable package 0.34.6 but same issue. 

Any idea? 

Thank you

[Govert van Drimmelen]

Could you make a little project that I can run?

-Govert

[Emanuil Boyanov]

Hi,

Sorry to bump up this old thread, but we have the exact same problem on our side - if there are thousands of subscriptions often times calling UpdateValue will not fire up an update (happens especially on startup when there's a significant load). We are using v0.32.0 - since I can't see any follow up on this thread - was it confirmed that updating to a newer version of Excel DNA will fix the issue? Also, the other problem is that, after startup if we miss out on some updates and try to force an update by pressing ctrl alt f9 - if there aren't any changes to the Excel topic - XlCall.RTD won't pass the data to the server so we can't do much about that. I've tried a pretty brutal hack by always wrapping a Guid.NewGuid() to the the topic which eventually forces an update, but after debugging I saw that this always creates a new topic and the old ones are still there so it could lead to memory leaks and perf issues when we have massive spread sheets. So, essentially two questions - 1) Is there a fix for the missing updates in newer versions of the API and 2) If there's not - how can we force an update of a topic when the values are the same e.g. if I call XlCall.RTD("Path.To.Server", null, subscription) nothing happens if the data wrapped in the subscription list is the same. 

Any help is appreciated.

Thanks

Emo

[Govert van Drimmelen]

It's definitely worth testing with the current beta version 0.35.x, as there were some important RTD reliability fixed in recent years.

-Govert

[Francisco A. Camargo]

Hi Govert,

I too have encountered the same problem: some of my RTD formulas are failing to update, under very similar circunstances as described above. I tried upgrading to excel-dna beta 0.35.x but that did not help either. I still see the RTD returning twice to a single update, just like Rob reported. I tried some brute force method for updating all topics in a loop, and linked the method to a ribbon button which I can manually click on and force all topics to update. It did not help: some cells simply refuse to update (despite their having supplied a new value to topic.UpdateValue, which I can see through the debugger).

Any ideas?

Thank you very much for your attention.

-Francisco

[Francisco A. Camargo]

Ops, my mistake. Some of my RTD functions where returning ExcelErrorNA in the course of their normal processing, and that was causing the problem: they stopped updating.

After reading “https://github.com/Excel-DNA/ExcelDna/issues/102”, I replaced the “N/A”s with “” (null string) and the cells went back on to updating. Simple like that!

Thanks anyway for your attention, and many many many thanks for Excel-DNA: what a great piece of work!


-Francisco.