Excel RTD Servers: Using Topics

chart-990380_640

In the previous installment, we saw how to flesh out the IRtdServerinterface to create a functioning RTD server. We simplified this task by considering only one possible topic. We will now examine the topic of topics, so to speak, by creating an RTD server to provide recent trade prices for stocks. There will be several topic strings, many representing the ticker symbol of a particular stock. We include the new class, StockQuote, in the same test library we created for SimpleRtdServer.

Implementing Topics with a Dictionary Object

Each time an analyst using Excel creates a formula using the RTD function, our server will receive from Excel a topicID and a topic string. In the previous example, the topic string was irrelevant; now the topic string is a critical piece of information. We will need to record the topicId and topic string when an RTD function first establishes a connection, and we will have to read that information before an update to provide the data that Excel has asked for. To do this, we will jettison the single _topicId field we used in the SimpeRtdServer class and use a generic Dictionary instead.

private Dictionary<int, string> _topics;

Since there will only be one dictionary, we will instantiate it in the ServerStart method.

_topics = new Dictionary<int, string>();

Every time a new connection is made by some RTD formula in Excel, we will need to record the topicId and the topic string. In this code, if more than one string is supplied, the rest will simply be ignored.

public object ConnectData(int topicId,

ref Array strings,

ref bool newValues)

{

string symbol = strings.GetValue(0).ToString();

_topics.Add(topicId, symbol);

_timer.Start();

return GetQuote(symbol);

}

The DisconnectData method is complicated slightly when compared to SimpleRtdServer. We cannot stop the timer, since there will likely be many topics. We must also remove the entry for the disconnecting topic so our list of topics continues to match those open in Excel.

public void DisconnectData(int topicId)

{

// _timer.Stop();

_topics.Remove(topicId);

}

The greatest change in our new implementation is with RefreshData method. This time when Excel calls RefreshData we must return an array that matches topicIds with the appropriate updated data.

public Array RefreshData(ref int topicCount)

{

string symbol;

object[,] data = new object[2, 1];

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

{

data[0, i] = i;

_topics.TryGetValue(i, out symbol);

data[1, i] = GetQuote(symbol);

}

The GetQuote method is built on a classic web service. The technique is the same as described in the web service blog, but the web service itself is different. The web service used in the previous blog doesn’t work as of February 2016 and there is no indication on the website whether this situation will change or not. The code presenting here uses a different web service, http://ws.cdyne.com/delayedstockquote/delayedstockquote.asmx

Since the StockQuote class is in the same library as the SimpleRtdServer class, if you run the SimpleRtdServer example you already know that your library is being correctly registered on the target system. The StockQuote server can be used as so:

RtdStockQuoteFormula

Note that now there are several different topics, each using a different string supplied by an adjacent worksheet cell.

Conclusion

Creating an Excel RTD server in C# is not difficult, but it does require attention to detail, and most of those details will result in a failed server if not handled correctly. It is wise to start with a COM server and make sure you can connect from Excel. Then add a simple class that does little more than implement the IRtdServer interface and return some value. When that works, you should be in a position to create the actual class you want with full expectations of a practical working RTD server.

The source code for this project can be downloaded here. However, this project is specific for the 32-bit version of Excel 2010. If you are developing for a different environment, you will have to make the corresponding changes.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.