VBA adding row into a series of time in hours:minutes

[cazdealer]

Hi,
I've downloaded data for a US equities symbol.
It's a sheet containing 1 minute data for a stock that have been trading the last 6 months.
It has 1 row per minute from 9:30 to 16:00 every trading day (monday to friday, except holidays) for the last 5-6months
It has about 30000rows:

from
A1: 09/27/11 B1: 16:00
A2: 09/27/11 B2: 15:59
A3: 09/27/11 B3: 15:58
...
A29703: 04/06/11 B29703: 9:32
A29704: 04/06/11 B29704: 9:31
A29705: 04/06/11 B29705: 9:30

but sometimes, data jumps for 2-3-or plus minutes instead of 1 minute

for exemple:
jumping from
A10001: 06/20/11 B10001: 11:44
to
A10002: 06/20/11 B10002: 11:42

or

A10201: 06/22/11 B10201: 13:27
to
A10202: 06/22/11 B10202: 13:22

What I would like is to create a VBA procedure that would fill the lines that have data jumping for more than 1 min in that trading period (from 9:30 to 16:00)

I don't know where to start to...
maybe something that will find every gap that have more than 1 minute (in column B)in them, when there is a gap of 1 minute or more (beside the timeframe of 9:30 to 16:00) them insert a new line right in between and copy the exact data from the row below and change the cell in column B by adding 1min.

Does something knows a easy way to program that?

It would be really helpful!

thanks

 

[venkat1926]

sample data is like this . copy data in sheet 1 to sheet 2 from A1

Excel Workbook
	A	B	C
1	date	time	price
2	9/27/2011	16:00	9
3	9/27/2011	15:59	9.5
4	9/27/2011	15:58	10
5	9/27/2011	15:56	10.5
6	9/27/2011	15:53	11
7	9/27/2011	15:52	11.5
8	9/27/2011	15:51	12
Sheet1

try this macro and see

Sub test()
Dim j As Long, price As Double, k As Long, m As Long, n As Long
Worksheets("sheet1").Activate
j = Range("A1").End(xlDown).Row
For k = j To 3 Step -1
'MsgBox k
m = WorksheetFunction.Round((Cells(k - 1, 2) - Cells(k, 2)) * 24 * 60, 0)
'MsgBox m
If m > 1 Then
Range(Cells(k, 1), Cells(k + m - 2, 1)).EntireRow.Insert
For n = m - 1 To 1 Step -1
Cells(k + n - 1, 1) = Cells(k + n - 1, 1).End(xlDown)
Cells(k + n - 1, 2) = Cells(k + n - 1, 2).End(xlDown) + TimeValue("00:01")
Cells(k + n - 1, 3) = Cells(k + n - 1, 3).End(xlDown)
Next n
End If
Next k
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub

 

[cazdealer]

thanks for the time you took to answer me but it doesnt work
the procedure made my excel crashed. maybe it's because I have nearly 30 000 rows of data...

also, I don't see where it limit the procedure to not add a new line if it's between the close (16:30) and the next open (9:30)

 

[venkat1926]

as you are having 30000 rows and by insertions perhaps the no. of rows goes beyond restrictions of excel 2003 or earlier versions. I was under the impression that you have a sheet for each day between trading time. my macro my not do for the last few minutes if data is before closing time.
that can be modified.

did you try it on the sample data given by me. if the principle is ok you can modify the macro.

if crashing is due to overflow (para 1 above) then better use ACCESS.
.