How to push cached array data within Excel-dna library wrapper onto Excel?

[Matthias Wolf]

I need to push data in array form (2D object[,]) onto Excel. I did not find that a UDF meets my needs as the trigger to push the data lies within my library object model (updates of cached data). 

What approach will most likely suit best in this case? 

My library is updated with external data via UDF. In turn the data is cached within my library and I trigger calculations surrounding the new data. The result consists of a 2D array (object[10,2], for example) and I want to push that onto Excel via automatic resizing of arrays. The re-sizing feature I already have implemented but I cannot use a UDF to get the results back to Excel because the re-calculation trigger does not stem from updated parameters of the result UDF but from within my library. Only a manual refresh would trigger the result UDF which is not what I want. I basically pursue a PUSH model where the original source data is pushed into my library and triggers calculations and ultimately the output of the result data back to Excel. 

What is the potentially best way to accomplish the above?

Thanks a lot.

[Govert van Drimmelen]

Hi Matthias,

Excel gives a few options for this kind of scenario, but it's still tricky to decide on the trade-offs.

* You can directly use the COM object model or C API to dump data onto a sheet. (myRange.Value = myData).

* You can set up the data as an External Link, and have the link refresh periodically, when the user presses the Refresh button, or when some macro runs.

* You can use Excel's RTD features to make on-sheet UDFs that can be triggered for update.

The RTD mechanism seems best for what you want to do, but there are still some issues with UDFs returning arrays and RTD. Excel-DNA supports RTD at different levels - you can make an RTD server yourself using the ExcelRtdServer base class, or you can use the Reactive Extensions (Rx) approach, and publish an IObservable to Excel using the ExcelAsyncUtil.Observe helper.

To get around any array issues, you might refactor your function into a single-cell trigger function and an array unpacking function. The trigger function would be an RTD function that returns a fresh 'handle' every time the underlying data changes. Then the array function takes only the handle as input, and returns the whole data block. It's a bit harder to use, since you need the extra cell, but will be reliable.

If you're able to move away from the array formula, it's even better. You can make huge numbers of single-cell UDFs work on a sheet via the push-based RTD mechanism.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Matthias Wolf [matt....@gmail.com]
Sent: 23 December 2015 05:49 AM
To: Excel-DNA
Subject: [ExcelDna] How to push cached array data within Excel-dna library wrapper onto Excel?

--
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.

[Matthias Wolf]

Hi Govert,

I like the latter approach via RTD and ExcelRtdServer base class you described. I am, however, running in a few issues:

* I created an Rtd Server class that derives from ExcelRtdServer and also store within the same class my cached data like

public class RtdServer : ExcelRtdServer

    {

        private readonly List<Topic> _topics;

        private Dictionary<string, double> _realtimePrices;

        private Dictionary<string, double[]> _dailyPriceSeries;

        private Dictionary<string, double[]> _intradayPriceSeries;

...

..

.

I update the cached data from a UDF that is defined outside the Rtd Server like

public static class UserDefinedFunctions

    {

        private static RtdServer _rtdServer;

        static UserDefinedFunctions()

        {

            _rtdServer = new RtdServer();

        }

            

        [ExcelFunction(Description = "UpdateRealTimePrices")]

        public static string UpdateRealTimePrices(object[] symbols, double[] realTimePrices)

        {

            return _rtdServer.UpdateRealTimePrices(symbols, realTimePrices);

        }

　　[ExcelFunction(Description = "GetTodaysMovers")]

        public static object GetTodaysMovers()

        {

            return XlCall.RTD("ExcelLibrary.RtdServer", null, "TodaysMoversSubscription");

        }

...

..

.

And I also expose a UDF to subscribe via Topic to the Rtd Server as can be seen above. 

The problem I am experiencing is that the call to XlCall.RTD(... spins up a different instance of RtdServer than the RtdServer instance I define in my static UDF function class "UserDefinedFunctions"

As a result I cannot update both the cached data and peruse the same data to update Rtd topic subscribers with the refreshed data in any meaningful way. 

Is there a way to get a reference or pass a reference to the existing instance of RtdServer when I subscribe to the topic within a UDF function via XlCall.RTD?

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/lmttFyYU9tU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.

[Govert van Drimmelen]

Hi,

You can't control the creation of the RtdServer instance, and should not 'new' it yourself.

For your scenario you could:

* Make a separate class to hold the cache data, that you 'new' and assign to a static variable, and then the RtdServer object just uses that static variable to get to the data.

* Decide that you only need the data when the Rtd server is alive. Then when the Rtd server is stated, the object puts a reference to itself into a static variable. The update function checks whether there is a live server and only updates the data if there is a live server.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Matthias Wolf [matt....@gmail.com]

Sent: 23 December 2015 10:46 AM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] How to push cached array data within Excel-dna library wrapper onto Excel?

[Matthias Wolf]

That helped a lot, I got it up and running just fine. I still need to experiment a bit more regarding getting arrays back onto Excel. If RTD poses issues with arrays then I might in the end follow your earlier recommendation in that push the responsibility to refresh to a macro and thus circumvent Rtd altogether. I will experiment a bit more and report back. 

Thanks a lot for your prompt help. 

To post to this group, send email to exc...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/lmttFyYU9tU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to exceldna+...@googlegroups.com.
To post to this group, send email to exc...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
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 exc...@googlegroups.com.

[Matthias Wolf]

I ended up not  using RTD for this project. I am heavily dependent on array (object[,]) return values and RTD does not seem to handle arrays as return values all to well as far as I have read and understand.

I use UDF with IsVolatile set to true and refresh either manually or switch to automatic recalc mode or let a macro timer refresh at specified intervals. 

[Matthias Wolf]

Update: I also experimented with your suggestion to use RTD as trigger and then fetch the actual array via UDF, it works well and is definitely the most stable solution for someone who wants event based scheduling of subsequent updates. I am sure that is what Rx is all about though I am not very knowledgeable about Rx so your idea here comes in handy. Thanks for your efforts and great product.