Recalculate only if cell change

[virginie]

Hi,

I'm working on a contribution system via ExcelDNA which works
perfectly except for one point. Just to explain, I've got a function
in C# used in excel to contribute some data to an SQL server. On
another computer, I retreive the data in real time in Excel via the
RTD server in Excel DNA.

The only problem I have is the fact that every time the workbook
recalculate, the function resend the data to the database even if the
value to contribute hasn't changed. The idea is to contribute only the
data which have changed to avoid too many traffic.

Here is the code of the function which contribute:

[ExcelFunction(Description = "Contibute to the RTD server",
Category = "CONTRIB - Pricing Library")]
public static DateTime Contribute_Send(string Ticker, string
Field, double Value)
{
try
{
string[] s = Ticker.Split('.');

MySqlConnection thisConnection = new
MySqlConnection("");
thisConnection.Open();
MySqlCommand thisCommand =
thisConnection.CreateCommand();
thisCommand.CommandText = "SQL Command Here";
thisCommand.ExecuteNonQuery();
thisConnection.Close();
return DateTime.Now;
}
catch (Exception)
{
throw new Exception();
}
}

The idea is to return the Time so I know if the function has
contributed recently.

Any Idea how can I solve that?

Tks,

Julien

[Govert van Drimmelen]

Hi Julien,

Whether your function will recalculate depends on how the workbook is
recalculated. Normally, it will only recalc if one of the arguments
change (since your function is not volatile).

Are you perhaps forcing a recalculate yourself? In that case Excel is
recalculating and will call your function again.

One way to have the update happen less frequently would be to store
the last calculate time (say in a static variable), or to store the
last value in a static variable, and then only resubmit the data if a
sufficient period has elapsed or a new value has arrived.

-Govert

[Tjaart van Wijck]

Julien,

As Govert rightly mentioned, I would also recommend keeping a global variable, to cache the last recorded value. You could for example implement this as 

Dictionary<string, Dictionary<string,Tuple<DateTime,double>>>

To map Tickers to Fields and (timestamp,value) pairs or you could design a more sophisticated data structure for the cache.

You could also consider moving the initialisation and closing of the connection to separate routines. Not only will this be more performant but you wouldn't need the try/catch block and could declare your function as exception safe which will allow the function to take advantage of the multi-threaded features in more recent versions of Excel.

-Tjaart

--
You received this message because you are subscribed to the Google Groups "Excel-Dna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[virginie]

Tks for your answer to both, indeed I realized that I was forcing the
recalculate of the workbook by pressing F9 (as today the market are
closed so my variable do not move). I will put the calculation in
automatic mode Monday and see if the function contribute only if the
variable change.

I can't store a value of the last update to limit the frequency as it
must be real time and my developer skill are not good enough to go
that way (I'm a quant guy).

Anyway the RTD server works perfectly based on your example with an
adaptation to update an SQL database.

Tks,

Julien