Creating OHLC bars from un-homogenized ticks using LINQ

See more:

C#

LINQ

C#4.0

C#5.0

big-data

Hi All,

I am trying to create OHLC data from un-homogenised data. I have googled and discovered an article at StackOverFlow
How to group a time series by interval (OHLC bars) with LINQ

Which to be honest I have found to be really useful. However, the results I get are not in line with what I was expecting. So I would really appreciate any guidance and help to get over this.

My Code (Thanks to Ani as I have lifted his code.)

public void RunBuilder(List<DataPacket> myData)
{
    Console.WriteLine("Set frequancy i.e. 1,2,3.....n");
    ifrequancy = int.Parse(Console.ReadLine());
    Console.WriteLine("Set s = Second; m = minute; h = hour");
    smh = Console.ReadLine();
    SetFrequancy(ifrequancy, smh);

    long barSizeInTicks = 0;
    if (smh == "s")
    {
        barSizeInTicks = (864000000000 / 24 / 60 / 60 ) * ifrequancy;
    }
    if (smh == "m")
    {
        barSizeInTicks = (864000000000 / 24 / 60 ) * ifrequancy;
    }
    if (smh == "h")
    {
        barSizeInTicks = (864000000000 / 24 ) * ifrequancy;
    }

    long StartBar = myData[0].dtTime.TimeOfDay.Ticks;
    var bars = from datas in myData
               // Calculate the chronological, natural-time, intra-day index
               // of the bar associated with a tick.
               let myBarIndexSequance = StartBar / barSizeInTicks
               //let barIndexForSequance = datas.dtTime.TimeOfDay.Ticks / barSizeInTicks
               // Calculate the begin-time of the bar associated with a tick.
               // For example, turn 2011/04/28 14:23.45
               // into 2011/04/28 14:20.00, assuming 5 min bars.
               let barBeginDateTime = datas.dtTime.Date.AddTicks(myBarIndexSequance / barSizeInTicks)
               //let barBeginDateTime = datas.dtTime.Date.AddTicks(barIndexForSequance / barSizeInTicks)
               // Produce raw tick-data for each bar by grouping.
               group datas by barBeginDateTime into tickGroup
               // Order prices for a group chronologically.
               let orderedPrices = tickGroup.OrderBy(t => t.dtTime.Ticks).Select(t => t.dBestBid)


               select new BAR
               {

                   Open = orderedPrices.First(),
                   Close = orderedPrices.Last(),
                   High = orderedPrices.Max(),
                   Low = orderedPrices.Min(),
                   BeginTime = tickGroup.Key,
                   EndTime = tickGroup.Key.AddTicks(barSizeInTicks)
               };


    try
    {
        foreach (var barData in bars)
        {
            System.Diagnostics.Debug.WriteLine(barData.BeginTime.ToShortDateString()
                + "," + barData.BeginTime.ToLongTimeString()
                + "," + barData.EndTime.ToShortDateString()
                + "," + barData.EndTime.ToLongTimeString()
                + ", " + barData.Open
                + ", " + barData.High
                + ", " + barData.Low
                + ", " + barData.Close);
        };
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    Console.ReadLine();

}

OutPut

31/07/2014,00:00:00,31/07/2014,00:05:00, 1.01364, 1.01612, 1.01269, 1.01454
01/08/2014,00:00:00,01/08/2014,00:05:00, 1.01454, 1.01866, 1.01044, 1.01816
04/08/2014,00:00:00,04/08/2014,00:05:00, 1.01816, 1.01906, 1.01674, 1.01714
05/08/2014,00:00:00,05/08/2014,00:05:00, 1.01714, 1.02132, 1.0166, 1.01988
06/08/2014,00:00:00,06/08/2014,00:05:00, 1.0199, 1.0235, 1.01296, 1.01364
07/08/2014,00:00:00,07/08/2014,00:05:00, 1.01364, 1.01428, 1.0107, 1.01176
08/08/2014,00:00:00,08/08/2014,00:05:00, 1.01176, 1.019, 1.01042, 1.01852
11/08/2014,00:00:00,11/08/2014,00:05:00, 1.01852, 1.019, 1.01091, 1.01342
12/08/2014,00:00:00,12/08/2014,00:05:00, 1.01342, 1.01503, 1.01169, 1.0139
13/08/2014,00:00:00,13/08/2014,00:05:00, 1.01392, 1.0175, 1.013, 1.01508
14/08/2014,00:00:00,14/08/2014,00:05:00, 1.01508, 1.01704, 1.01058, 1.01606
15/08/2014,00:00:00,15/08/2014,00:05:00, 1.01604, 1.01688, 1.00768, 1.01395
18/08/2014,00:00:00,18/08/2014,00:05:00, 1.01394, 1.01796, 1.01342, 1.01716
19/08/2014,00:00:00,19/08/2014,00:05:00, 1.01716, 1.0193, 1.01596, 1.01706
20/08/2014,00:00:00,20/08/2014,00:05:00, 1.01707, 1.02012, 1.01314, 1.01596
21/08/2014,00:00:00,21/08/2014,00:05:00, 1.01596, 1.01944, 1.01519, 1.01878
22/08/2014,00:00:00,22/08/2014,00:05:00, 1.01878, 1.02177, 1.01798, 1.02018
25/08/2014,00:00:00,25/08/2014,00:05:00, 1.02019, 1.02162, 1.01917, 1.01974
26/08/2014,00:00:00,26/08/2014,00:05:00, 1.01974, 1.0226, 1.01706, 1.01764
27/08/2014,00:00:00,27/08/2014,00:05:00, 1.01764, 1.0196, 1.01112, 1.01488
28/08/2014,00:00:00,28/08/2014,00:05:00, 1.01488, 1.01722, 1.01327, 1.0153
29/08/2014,00:00:00,29/08/2014,00:05:00, 1.01528, 1.01626, 1.00962, 1.01396

Sample Data

Date    Time EST    Bbid    Bbid Volume Boffer  Boffer Volume
01/08/2014  17:00:01.915    1.01364 1000000 1.01417 1000000
01/08/2014  17:00:02.021    0   0   0   0
01/08/2014  17:01:11.159    1.01303 900000  0   0
01/08/2014  17:01:13.714    1.01303 900000  1.01359 900000
01/08/2014  17:01:15.987    1.01302 900000  1.01358 900000
01/08/2014  17:01:36.788    0   0   1.01358 900000
01/08/2014  17:01:36.989    1.01301 900000  1.01357 900000
01/08/2014  17:01:57.785    0   0   1.01357 900000
01/08/2014  17:01:57.989    1.01303 900000  1.01359 900000
01/08/2014  17:02:15.738    1.01311 900000  1.01367 900000
01/08/2014  17:02:21.818    0   0   1.01367 900000
01/08/2014  17:02:21.939    1.01318 900000  1.01374 900000
01/08/2014  17:02:37.238    1.01325 900000  1.01381 900000
01/08/2014  17:02:37.303    0   0   1.01381 900000
01/08/2014  17:02:37.438    1.01318 900000  1.01374 900000
01/08/2014  17:02:53.985    1.01327 900000  1.01383 900000
01/08/2014  17:03:14.784    0   0   1.01383 900000
01/08/2014  17:03:14.987    1.0133  900000  1.01386 900000
01/08/2014  17:03:35.784    0   0   1.01386 900000
01/08/2014  17:03:35.987    1.01331 900000  1.01387 900000
01/08/2014  17:03:55.784    0   0   1.01387 900000
01/08/2014  17:03:55.984    1.01332 900000  1.01388 900000
01/08/2014  17:04:02.735    1.01325 900000  1.01381 900000
01/08/2014  17:04:22.984    1.01323 900000  1.01379 900000
01/08/2014  17:04:29.315    1.0133  900000  1.01385 900000
01/08/2014  17:04:49.784    0   0   1.01385 900000
01/08/2014  17:04:49.984    1.01328 900000  1.01384 900000
01/08/2014  17:05:10.783    1.01275 1000000 1.01384 900000
01/08/2014  17:05:10.984    1.01329 900000  1.01385 900000
01/08/2014  17:05:29.077    1.01275 1000000 1.01385 900000
01/08/2014  17:05:29.240    1.01322 900000  1.01378 900000
01/08/2014  17:05:49.786    1.01275 1000000 1.01378 900000
01/08/2014  17:05:49.988    1.01325 900000  1.01381 900000
01/08/2014  17:06:52.785    1.0127  1000000 1.01381 900000

My problem is that the Phase of the data begins at 5PM EST so the Clock runs on a 24 hr base from 5PM to 4pm the next day. I would like to create OHLC Price data but also include in that data the number of events that have actually occurred in the bars creation but also to be able to sum the total Bid Volumes and Offer Volumes. So depending on the duration I choose I can build a corresponding bar for example be it 5 minutes of 32 seconds.

As the data was phased at 5pm EST, I have used the

DateTime.Subtract(TimeSpan(oneDay)

); to get the phase to shift to midnight, hence in the results you see a midnight timestamp for 31st July 2014 and in the data sample its 1st August 2014.

With the code thus far I can only print the OHLC at midnight for each day. Apart from haing to write lots of additional logic to seperate the data I was hoping there was infact an easier way to do this with LINQ.

I would really truly appreciate any help on this as I have been knocking my head against a brick wall.

Ever so humble,
Harry