Discretizing tick prices

[mohitsingh]

Dear All,

I have historical prices of a FTSE 100 index.

There are two columns, Times and Price. The time is uneven and is in seconds

For instance:
Time Price
2011-04-01T07:12:35.412Z 5949.6
2011-04-01T07:12:35.412Z 5949.4
2011-04-01T07:12:35.412Z 5949.4
2011-04-01T07:12:35.571Z 5949.4
2011-04-01T07:12:35.571Z 5949.4
2011-04-01T07:12:39.974Z 5949.4
2011-04-01T07:12:39.974Z 5950.1
2011-04-01T07:12:39.974Z 5950.1
2011-04-01T07:12:39.974Z 5950.1
2011-04-01T07:12:39.974Z 5949.7
2011-04-01T07:12:39.974Z 5949.7
2011-04-01T07:12:42.887Z 5949.7
2011-04-01T07:12:43.378Z 5950.1
2011-04-01T07:12:43.378Z 5950.1




Now...this goes on for around 1.5 million entries. I want to ask, if I want to convert this into minute-minute rather than uneven second levels and the respective price at that time how can I do it?

So I would want it like

2011-04-01T07:12:35Z
2011-04-01T07:12:36Z
2011-04-01T07:12:37Z
2011-04-01T07:12:38Z with their respective prices when the minute starts.

If anybody could help I would appreciate it and I hope I have explained it well enough

 

[p45cal]

What version of Excel have you managed to get your hands on (two columns around 1.5 million entries)?!

One way, use Text To Columns, with fixed width option to split the time column into two columns, but you're going to skip the second column. Split it to leave the likes of:
2011-04-01T07:12:35

Now add a formula in column C and copy down/up, leaves you with:

Excel Workbook
	A	B	C
45	2011-04-01T07:12:35	5949.6
46	2011-04-01T07:12:35	5949.4
47	2011-04-01T07:12:35	5949.4
48	2011-04-01T07:12:35	5949.4
49	2011-04-01T07:12:35	5949.4	5949.4
50	2011-04-01T07:12:39	5949.4
51	2011-04-01T07:12:39	5950.1
52	2011-04-01T07:12:39	5950.1
53	2011-04-01T07:12:39	5950.1
54	2011-04-01T07:12:39	5949.7
55	2011-04-01T07:12:39	5949.7	5949.7
56	2011-04-01T07:12:42	5949.7	5949.7
57	2011-04-01T07:12:43	5950.1
58	2011-04-01T07:12:43	5950.1	5950.1
Sheet

(This leaves prices at the end of each time slot - a small change to which cells the formula looks at could do the same for the start of each time slot:in C45: =IF(A45<>A44,B45,"")

Now filter on column C excluding blanks:

Excel Workbook
	price	consolidated price
2011-04-01T07:12:35	5949.4	5949.4
2011-04-01T07:12:39	5949.7	5949.7
2011-04-01T07:12:42	5949.7	5949.7
2011-04-01T07:12:43	5950.1	5950.1
Sheet

Now copy just columns A and B to another sheet.

Now I think your example is using seconds rather than minutes and I've followed your example - but I don't know what Z time is - so the only change you might need to make is where you split the date/time column when you Text-to-Columns it; split it leaving the likes of:
2011-04-01T07:12