C# Tick by tick stock data to Ohlc candles resample on different timeframe-CodePudding

I want to convert tick by tick data to 1, 5,10 minutes, 1 hour, 2 hours time frame based on time frame. Data looks like below every second data.

var source = new List<TickData>()
    {
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:02.100"), Price = Convert.ToDouble("294.00"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:32.680"), Price = Convert.ToDouble("296"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:36.263"), Price = Convert.ToDouble("295.08"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:42.090"), Price = Convert.ToDouble("295.08"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:42.127"), Price = Convert.ToDouble("295.08"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:46.357"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:46.357"), Price = Convert.ToDouble("292.00"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:46.598"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:47.168"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:47.914"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:48.290"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:49.391"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:49.854"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:49.854"), Price = Convert.ToDouble("295.02"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:00:50.024"), Price = Convert.ToDouble("295.99"), Volume = Convert.ToInt32("500")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:01:22.893"), Price = Convert.ToDouble("295.2"), Volume = Convert.ToInt32("100" )},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:36.026"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:43.101"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:46.025"), Price = Convert.ToDouble("295.2"), Volume = Convert.ToInt32("1900")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:46.526"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:46.527"), Price = Convert.ToDouble("295.2"), Volume = Convert.ToInt32("1900")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:54.163"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:56.029"), Price = Convert.ToDouble("295.2"), Volume = Convert.ToInt32("1900")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:56.370"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:56.371"), Price = Convert.ToDouble("295.2"), Volume = Convert.ToInt32("1900")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:02:56.530"), Price = Convert.ToDouble("295.21"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:03:34.546"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:04:27.331"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:04:35.805"), Price = Convert.ToDouble("295.35"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:06:34.495"), Price = Convert.ToDouble("295.37"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:06:48.211"), Price = Convert.ToDouble("295.37"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:06:48.857"), Price = Convert.ToDouble("295.37"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:06:52.385"), Price = Convert.ToDouble("295.37"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:06:57.519"), Price = Convert.ToDouble("295.37"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:07:14.514"), Price = Convert.ToDouble("295.55"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:07:15.370"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:14.167"), Price = Convert.ToDouble("295.55"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:14.167"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:16.583"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:16.590"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("200")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:18.260"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("200")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:18.857"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:20.425"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:08:22.247"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:09:19.030"), Price = Convert.ToDouble("295.5"), Volume = Convert.ToInt32("100" )},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:09:54.711"), Price = Convert.ToDouble("295.5"), Volume = Convert.ToInt32("100" )},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:10:10.505"), Price = Convert.ToDouble("295.5"), Volume = Convert.ToInt32("100" )},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:10:54.926"), Price = Convert.ToDouble("295.5"), Volume = Convert.ToInt32("100" )},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:11:01.927"), Price = Convert.ToDouble("295.5"), Volume = Convert.ToInt32("1100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:11:58.106"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:12:09.706"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:12:14.453"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:12:38.186"), Price = Convert.ToDouble("295.41"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:13:10.196"), Price = Convert.ToDouble("295.52"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:13:58.690"), Price = Convert.ToDouble("295.49"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:14:03.660"), Price = Convert.ToDouble("295.45"), Volume = Convert.ToInt32("100")},
            new TickData{ Timestamp = Convert.ToDateTime("2020-01-02 04:14:35.205"), Price = Convert.ToDouble("295.45"), Volume = Convert.ToInt32("100")}

    };

sample data can have multiple ticks/multiple data points in a seconds. I tried below, but it is not working as expected.

var period = TimeSpan.FromMinutes(1);


    var ohlcv = source.Select(d => new
    {
        Time = d.Timestamp,
        d.Price,

        d.Volume,
        Period = d.Timestamp.Ticks / period.Ticks
    })
         .GroupBy(d => d.Period)
         .Select(g => new Ohlc()
         {
             Timestamp = g.Min(d => d.Time),
             Open = g.FirstOrDefault().Price,
             Close = g.LastOrDefault().Price,
             Low = g.Min(d => d.Price),
             High = g.Max(d => d.Price),
             Volume = g.Sum(d => d.Volume)
         });

    foreach (var ohlc in ohlcv)
    {
        Console.WriteLine($"T:{ohlc.Timestamp} O:{ohlc.Open}, H:{ohlc.High}, C:{ohlc.Close}, L:{ohlc.Low}, V:{ohlc.Volume}");
    }

this gives following output, which is not correct.

T:02-01-2020 04:00:02 O:294, H:294, C:294, L:294, V:100
T:02-01-2020 04:00:32 O:296, H:296, C:296, L:296, V:100
T:02-01-2020 04:00:36 O:295.08, H:295.08, C:295.08, L:295.08, V:300
T:02-01-2020 04:00:46 O:295.02, H:295.02, C:295.02, L:295.02, V:4000
T:02-01-2020 04:00:46 O:292, H:292, C:292, L:292, V:500
T:02-01-2020 04:00:50 O:295.99, H:295.99, C:295.99, L:295.99, V:500
T:02-01-2020 04:01:22 O:295.2, H:295.2, C:295.2, L:295.2, V:7700
T:02-01-2020 04:02:36 O:295.21, H:295.21, C:295.21, L:295.21, V:600
T:02-01-2020 04:03:34 O:295.41, H:295.41, C:295.41, L:295.41, V:1600
T:02-01-2020 04:04:35 O:295.35, H:295.35, C:295.35, L:295.35, V:100
T:02-01-2020 04:06:34 O:295.37, H:295.37, C:295.37, L:295.37, V:500
T:02-01-2020 04:07:14 O:295.55, H:295.55, C:295.55, L:295.55, V:200
T:02-01-2020 04:09:19 O:295.5, H:295.5, C:295.5, L:295.5, V:1500
T:02-01-2020 04:13:10 O:295.52, H:295.52, C:295.52, L:295.52, V:100
T:02-01-2020 04:13:58 O:295.49, H:295.49, C:295.49, L:295.49, V:100
T:02-01-2020 04:14:03 O:295.45, H:295.45, C:295.45, L:295.45, V:200

OHLCV, Open is nothing but, first tick/second data point of that minute, High max value of price from Minute(or given time frame), Low is small value of time frame, Close is last value of that timeframe. Any help would be appreciated.

Python data frames are good for resample data in different time frame example python eg. Can it be possible to resample in different timeframe with c# code using linq or any other way?

Sample code of python, expecting same behavior.

df=pd.DataFrame(data,columns=['Date','Value'])
df['Date']=pd.to_datetime(df['Date'])
minutes=df.resample('1Min',on='Date').mean().dropna()

CodePudding user response：

Timeseries resampling to different time frame, equal to pandas.resample

var period = TimeSpan.FromMinutes(1);


    var ohlcv = source.OrderBy(p=>p.Timestamp).Select(d => new
    {
        Time = d.Timestamp,
        d.Price,

        d.Volume,
        Period = d.Timestamp.Ticks / period.Ticks
    })
         .GroupBy(d => d.Period)
         .Select(g => new Ohlc()
         {
             Timestamp = g.Min(d => d.Time),
             Open = g.FirstOrDefault().Price,
             Close = g.LastOrDefault().Price,
             Low = g.Min(d => d.Price),
             High = g.Max(d => d.Price),
             Volume = g.Sum(d => d.Volume)
         });

CodePudding user response：

One possibility is to calculate the interval by dividing the timestamp's Ticks property with the period we want, then group by that result :

var period=TimeSpan.FromMinutes(1);

var stats=source.Select(d =>new { 
        d.Timestamp,
        d.Price,
        Period=s.Timestamp.Ticks/period.Ticks
    })
    .GroupBy(d=>d.Period)
    .Select(g=>new Ohlc{
        Timestamp=g.Min(d=>d.Timestamp),
        Open=g.First(d=>d.Price),
        Close=g.Last(d=>d.Price),
        Low=g.Min(d=>d.Price),
        High=g.Max(d=>d.Price)
    });