ExcelDNA RTD not updating new values unless changed manually

Jun 3, 2014 at 2:27 PM
I am using the cookie-cutter excelDNA solution named TestRTD.dna in ...\Dsitrbution\Samples\RTD.

I augmented GetEurOnd() to:
  public static object getTestObject(String arg){
    return GetTestItem(arg);
  }
Where GetTestItem() is unchanged.

My XML is of the following format:


            <?xml version="1.0" encoding="utf-8"?>
            <ISIN>
              <US05574LFY92>
                <ID_ISIN>US05574LFY92</ID_ISIN>
                <A>40</A>
                <B>100.089004516602</B>
                <C>11:22:48.000</C>
              </US05574LFY92>
            </ISIN>
So my formula looks like this in excel:


=getTestObject("ISIN/US002799AM62/A")
Which returns 40. Great!

Now, I am running code throughout the day that is going to update the values in the xml using the following method:
            using (XmlWriter writer = XmlWriter.Create(recordPath))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement("ISIN");

                foreach (rtTrace.dataObj d in lines)
                {
                    writer.WriteStartElement(d.ID_ISIN);

                    writer.WriteElementString("ID_ISIN", d.ID_ISIN);


                    writer.WriteElementString("A", d.A);
                    writer.WriteElementString("B", d.B);
                    writer.WriteElementString("C", d.C);


                    if (!tmp.ContainsKey(d.ID_ISIN))
                    {
                        tmp.Add(d.ID_ISIN, "");
                    }

                    writer.WriteEndElement();
                }
            }
Which successfully DOES update the values in my XML file, yet the formula in excel still shows the old value.

If I go in to my XML file and update the value MANUALLY then the new value with flow through into excel.

Any idea what I am doing wrong here? Is this the right/wrong way to update the xml file such that it is registered by dna?

Hitting a wall.
Coordinator
Jun 3, 2014 at 2:43 PM
Stackoverflow version here: http://stackoverflow.com/questions/24003313/exceldna-rtd-not-updating-new-values-unless-changed-manually

It seems like the contents of the XML file you are writing from the C# is not correct.
Jun 3, 2014 at 2:50 PM
Sure - open to that. And yes thank you for the cross post link.
I am happy to debug what is incorrect, but let's say:
<etc...>
<A>200</a>
</etc...>
from the C# code. Then if I go in and change A to 201 and save in notepad++ I will see the updated 201 in excel via formula.

I am (in general) open to achieving this goal in any way possible, but based on the RTD example given in DNA this seemed the most direct.

If there is a way to accomplish this goal in s way different than I documented above, please feel free to offer that as well.
Coordinator
Jun 3, 2014 at 2:56 PM
What are you trying to do? Create a simple real-time function?
Is the XML story part of what you're trying to do?

Is there something pushing the data updates, or are you just polling with a timer?

-Govert
Jun 3, 2014 at 3:32 PM
1 - Yes the idea is 100% to create a real time function.
2 - The xml is the data the real time function is reading following the example given by the Test.xml file provided by the download.
3 - In the background the C# code is running a requests that returns data, and updates the xml file with this new data (let's say 500x a day).

High level use case:
Discretely updated data feeding into excel via RTD.
Coordinator
Jun 3, 2014 at 4:11 PM
But do you want to use the xml files?

-Govert
Jun 3, 2014 at 4:48 PM
Using XML is 100% not required. This is just due to following the example.
Coordinator
Jun 3, 2014 at 6:40 PM
I suggest:
  • Make a new class that derives from ExcelRtdServer.
  • Override ConnectData, which gets called for every new 'Topic' that is created.
  • Use an internal data structure to keep track of the connected Topics (and information that you will need to update them).
  • Add a Timer, and in the elapsed event get the fresh data, and call Topic.UpdateValue(...) for those Topics that have fresh data.
  • Optionally override DisconnectData to get notified when a Topic is not longer connected, and remove from your internal data structure.
  • Optionally override ServerStart and ServerTerminate to do global initialization and cleanup.
  • Implement some wrapper functions that just call XlCall.RTD(....).
A lot of the complications in implementing the IRtdServer is taken care of by the ExcelRtdServer base class.

-Govert
Jun 3, 2014 at 6:55 PM
So the short answer here:
1 - move it out of xml and create some wrapper that reads an easier file type to be certain it is working correctly.
Coordinator
Jun 3, 2014 at 7:01 PM
Using the ExcelRtdServer base class is better than implementing IRtdServer yourself.

Do you need a file at all? If you do, and want to use an xml file, that's fine. But then you need to understand the classes used to write it, the classes used to read it, and how to figure out why a particular call to SelectSingleNode might fail on the file you wrote.

Where is that data coming from? If you are calling a web service of something, you might not need to keep it in a file locally.

-Govert
Jun 3, 2014 at 7:06 PM
I guess the main question becomes:

Is this really the best solution?

If a user has 300-600 items that update once every 5-10 minutes per day would I be just as well off returning the data to a csv and streaming the csv from vba directly?

I was testing excelDNA-RTD for this use case because it seemed like an interesting test of functionality and something to learn. At this point I am wondering if there is truly a value add here or not.

I am not speaking down on excelDNA (it is fantastic) but I just wonder if I am making this problem tougher on myself than I need it to be.
Coordinator
Jun 3, 2014 at 7:15 PM
The RTD mechanism is the only one to feed real-time data into the worksheet without disrupting a user.
RTD scales well to many cells, and the user has a nice 'function-based' interface, where they can say =SharePrice("AAPL").

Running a macro that dumps the data into a sheet from VBA (or an Excel-DNA add-in) based on a timer is possible but has many complications, and interacting with the sheet and further calculations is hard. It's much harder to implement this approach well.

But you should try both ways...

-Govert