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
let myBarIndexSequance = StartBar / barSizeInTicks
let barBeginDateTime = datas.dtTime.Date.AddTicks(myBarIndexSequance / barSizeInTicks)
group datas by barBeginDateTime into tickGroup
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