Datatables / Enumerated Lists, C# and calculating difference between two numbers

Question

I'm new to DataTables and Enumerated lists in C#. I've looked up a lot of help but can't seem to get anywhere with this problem, which is.... I have a simple table Dates,Code,Price see the codes as ticker symbols. I'm essentially trying to calculate the return or change in price between this date and the next date i.e. date t+1 /date t for the specific code. Then adding it back to the table.

I'm not sure if a Datatable or Enumerated set is the way to go, none the less I tried with a DataTable first, the setup below worked to create the table:

private static DataTable GetData(dynamic Dataset)
{
    DataTable tbl = new DataTable();

    tbl.Columns.Add("EvalDate", typeof(DateTime));
    tbl.Columns.Add("Code", typeof(string));
    tbl.Columns.Add("Price", typeof(double));
    tbl.Columns.Add("Return", typeof(double));

    for (int irow=2;irow<Dataset.GetLength(0); irow++)
    {
        if (Dataset[irow,1] == null) { break; }
        DateTime EvalDate = DateTime.FromOADate((double)(Dataset[irow, 1]));
        string code = (string)(Dataset[irow, 2]); 
        double price = (double)Dataset[irow,3];                

        tbl.Rows.Add(EvalDate,code,price,null);

    }

    return tbl;

}

but I then tried to use lists to solve the problem :

var Dataset = getRng("A1:E50000", "Data");
DataTable tbl = GetData(Dataset);   
var uniquecodes = (from items in tbl.AsEnumerable()
                select items["Code"]).Distinct().ToList();
List<object> Running = new List<Object>();

    foreach (var code in uniquecodes)
    {
        var ts = (from items in tbl.AsEnumerable()
                  orderby items.Field<DateTime>("EvalDate")
                  where items.Field<string>("Code") == (string)code
                  select items);

        ts.ElementAt(0).SetField<double>("PriceRet", 1);

        for (int idx = 1; idx < ts.Count(); idx++)
        {

            double price0 = ts.ElementAt(idx - 1).Field<double>("Price");
            double price1 = ts.ElementAt(idx).Field<double>("Pice");
            double delta = price1 / price0;

            ts.ElementAt(idx).SetField<double>("PriceRet", delta);                       

        }
        Running.Add(ts);

It didn't crash, but it's hopelessly slow and inefficient.

The data looks like this:

My SQL knowledge is good, but I struggling to turn that into workable code as above. Any help would be greatly appreciated.

So I achieved the desired outcome, but through SQL.... I used the following statement/query :

select  row_number() over (partition by[Code] order by [EvalDate]) [Id], 
        [Evaldate], [Code], [Price]

into #TEMP
from  MyTable

where  Code in ('AAA','BBB','CCC'); -- The list of codes to calc the return for ...

select T1.[EvalDate],T1.[Code],T1.[Price]/T.[Price] as [Delta]
from    #TEMP T1
        inner join #TEMP T
            on T1.Id = T.Id +1
            and T1.Code = T.Code 

drop table #TEMP

This then correctly gives me a unpivoted list, with the applicable change in price for each change in date.

I would have expected a DataTable would be as easy and quick as SQL?

Answer 1

Read the answer to this question. Your code uses dynamic objects, and that always makes this slow. I think you will find that your GetData() function is the bottleneck, and if you can re-factor you code so that you can pass GetData a statically known data type, it will run much faster.