Is there a better way to convert objects in List to show side by side view by date

Question

I would like to reorganize the list of objects and classify them by the date of entry in a side by side view so I can compare the trades across instruments by day. so this view needs to be reorganised

as the following

The only logic I created was to use a Dictionary of dynamo objects. However it forces me to define the columns everytime. Is there a more efficient approach?

code in linqpad share. linqpad share

void Main()
{
    List<STSmyTrade> testoj = new List<STSmyTrade>(); // object 1
    STSmyTrade a1 = new STSmyTrade();
    a1.instrument = "eurusd";
    a1.entryPrice = 3;

    a1.entryTime = DateTime.Now.AddDays(1);
    testoj.Add(a1);
    STSmyTrade a2 = new STSmyTrade();
    a2.instrument = "eurusd";
    a2.entryPrice = 6;
    a2.entryTime = DateTime.Now.AddDays(2);
    testoj.Add(a2);
    
    
    
    STSmyTrade a3 = new STSmyTrade();
    a3.instrument = "audusd";
    a3.entryPrice = 3;
    a3.entryTime = DateTime.Now;
    
    testoj.Add(a3);
    STSmyTrade a4 = new STSmyTrade();
    a4.instrument = "audusd";
    a4.entryPrice = 6;
    a4.entryTime = DateTime.Now.AddDays(2);
    testoj.Add(a4);
    testoj.Dump();

     


    // need to compare the 2 instruments sorted by date

    Dictionary<DateTime, ExpandoObject> analysiscompare = new Dictionary<DateTime, ExpandoObject>();
    foreach (STSmyTrade t in  testoj ) // loop first time comparing before and after
    {
        dynamic eo;
        if (!analysiscompare.Keys.Contains(t.entryTime.Date))// if i dont have a record in the dictionary object, create it.
        
        {
            eo = new ExpandoObject();
             eo.day=t.entryTime.Date.ToString("yyyy-MM-dd");// just assign the day.
            eo.eurusdentryTime = null;
            eo.eurusdentryPrice = null;
            eo.audusdentryTime = null;
            eo.audusdentryPrice = null;
            analysiscompare.Add(t.entryTime.Date, eo);

        }
        //dynamic eo;
 
        eo = analysiscompare[t.entryTime.Date];
        switch (t.instrument)
        {
            case "eurusd":
                eo.eurusdentryTime = t.entryTime;
                eo.eurusdentryPrice = t.entryPrice;
                 
                break;

            case "audusd":
                eo.audusdentryTime = t.entryTime;
                eo.audusdentryPrice = t.entryPrice;
                break;




        }


    }
    analysiscompare.Values.ToList().Dump("side_by_side_view");
     


}


class STSmyTrade
{
public DateTime entryTime { get; set; }
    public string instrument { get; set; }


    public double entryPrice { get; set; }
    
}

The only way, I can make this work is by creating a custom column mapping to each unique object(audusd, eurusd). However this is not scalable.

Answer 1

Assuming you don't have two entries for a currency on the same day, you can group by date and select the eurusd and audusd entries per group, eg.

(from r in testoj
 group r by r.entryTime.Date into results
 let eur = results.SingleOrDefault(a => a.instrument == "eurusd")
 let aud = results.SingleOrDefault(a => a.instrument == "audusd")
 select new
 {
     day = results.Key.ToString("yyyy-MM-dd"),
     eurusdentryTime  = eur?.entryTime,
     audusdentryTime  = aud?.entryTime,
     eurusdentryPrice = eur?.entryPrice,
     audusdentryPrice = aud?.entryPrice,

 }).Dump();

You probably want to sort the results, but your original doesn't seem to be sorted, so I haven't sorted mine either.

Answer 2

Assuming a class:

class STSmyTrade
{
    public string instrument;
    public decimal entryPrice;
    public DateTime entryTime;
}

We create a new class for the results. (Instead, you can of course also create an anonymous type new { ... } if you prefer. I used a class because it makes it easier to understand the result we are producing):

class SideBySideEntry
{
    public DateOnly day;
    public TimeOnly? eurUsdTime, audUsdTime;
    public decimal? eurUsdPrice, audUsdPrice;
}

We can initialize the test by using object and collection initializers:

var testoj = new List<STSmyTrade> {
    new() {
        instrument = "eurusd",
        entryPrice = 3,
        entryTime = DateTime.Now.AddDays(1)
    },
    new() {
        instrument = "eurusd",
        entryPrice = 6,
        entryTime = DateTime.Now.AddDays(2)
    },
    new() {
        instrument = "audusd",
        entryPrice = 3,
        entryTime = DateTime.Now
    },
    new() {
        instrument = "audusd",
        entryPrice = 6,
        entryTime = DateTime.Now.AddDays(2)
    }
};

We can convert the entries like this by using LINQ and some pattern matching:

var sideBySideList = testoj
    .GroupBy(x => DateOnly.FromDateTime(x.entryTime))
    .Select(g => new SideBySideEntry {
        day = g.Key,
        eurUsdTime = g.FirstOrDefault(e => e.instrument == "eurusd")
            is { entryTime: var dt } ? TimeOnly.FromDateTime(dt) : null,
        eurUsdPrice = g.FirstOrDefault(e => e.instrument == "eurusd")?.entryPrice,
        audUsdTime = g.FirstOrDefault(e => e.instrument == "audusd")
            is { entryTime: var dt2 } ? TimeOnly.FromDateTime(dt2) : null,
        audUsdPrice = g.FirstOrDefault(e => e.instrument == "audusd")?.entryPrice,
    })
    .OrderBy(x => x.day)
    .ToList();

This console test...

Console.WriteLine($"Day        | EUR USD time | EUR USD price | AUD USD time | AUD USD price");
foreach (var e in sideBySideList) {
    Console.WriteLine($"{e.day} | {e.eurUsdTime,12} | {e.eurUsdPrice,13:n2} | {e.audUsdTime,12} | {e.audUsdPrice,13:n2}");
}

...produces this output (sorted by date):

Day        | EUR USD time | EUR USD price | AUD USD time | AUD USD price
30.12.2023 |              |               |        18:24 |          3.00
31.12.2023 |        18:24 |          3.00 |              |
01.01.2024 |        18:24 |          6.00 |        18:24 |          6.00

It makes no sense to display the date part up to 3 times in the same line, since it will always be the same date (by definition).

Answer 3

I did build an outer join:

    var eurusd = testoj.Where(x=>x.instrument=="eurusd");
    var audusd = testoj.Where(x=>x.instrument=="audusd");

    var left = from e in eurusd 
                  join a in audusd on e.entryTime equals a.entryTime into xn
                  from nx in xn.DefaultIfEmpty()
                  select new 
                  {
                    entryTime = e.entryTime,
                    eurousdPrice = e.entryPrice ?? 0,
                    audusdPrice = nx?.entryPrice ?? 0
                  };
    var right =   from e in audusd 
                  join a in eurusd on e.entryTime equals a.entryTime into xn
                  from nx in xn.DefaultIfEmpty()
                  select new 
                  {
                    entryTime = e.entryTime,
                    eurousdPrice = nx?.entryPrice ?? 0,
                    audusdPrice = e.entryPrice ?? 0
                  };

    var testabc = (from l in left select l).Union(
                  from r in right select r).GroupBy(x=>x.entryTime.ToString("yyyy-MM-dd HH:mm:ss"))
                     .Select(t=> new {
                        date = t.Key.Substring(0,10),
                        entryTime = t.Key,
                        eurusdPrice = t.Sum(w=>w.eurousdPrice),
                        audusdPrice = t.Sum(w=>w.audusdPrice)
                     }).OrderBy(x=>x.entryTime);
     testabc.Dump("testabc");

NOTE: For this to work, I made entryPrice nullable

class STSmyTrade
{
    public string instrument { get; set; }
    public double? entryPrice { get; set; }
    public DateTime entryTime { get; set; }
}