Add rows based on criteria

[Test123Test]

I have some data as follows, first column being time and 2nd-5th column representing OHLC-data for stock prices.
700 1 1 1 1
705 1 2 2 1
715 1 1 1 1
720 1 1 1 1
725 1 1 1 1

Between 705 and 715 you can see there is a 10-minute interval instead of a 5 minute. My data is filled with such gaps, sometimes as much as 30 minutes. I need the data to have 5-minute intervals universally everywhere in the dataset, so I need some kind of macro that can insert this data wherever it's missing. So after running the macro, the above dataset would look as such:
700 1 1 1 1
705 1 2 2 1
710 1 2 2 1
715 1 1 1 1
720 1 1 1 1
725 1 1 1 1

(For the newly inserted row(s), the macro can just copy the OHLC-data in column 2-5 from the previous row and insert it in the new row(s))

[patel45]

Please Login or Register  to view this content.

[Test123Test]

Nice, thanks for this! Unfortunately a problem with this is it goes above 60 minutes. So if it says 855 and the next datapoint is 900, it will enter 860, 865, 870...all the way up to 900. So it needs some sort of cutoff to prevent this happening.

[patel45]

i'm sorry, I did not understand

[Test123Test]

The datapoints represent time. So for instance 825 means 8:25, 830 means 8:30 and so on. So it can never go above 60 minutes. But, if I run the above macro on a dataset like this for example:
825
830
840
845
900

It will look like this after I run macro
825
830
835
840
845
850
855 (all correct until this point, but then...)
860
865
870
875
880
885
890
895
900

The problem is marked in bold

[patel45]

Please Login or Register  to view this content.

[Test123Test]

Sorry but it seems the problem is still there. But maybe I have it configured Excel wrong... See attached Excel workbook with macro attached, when you run the macro it still goes above 60 on my PC...

[patel45]

Please Login or Register  to view this content.

[Test123Test]

NICE!

Thanks so much for this and for your time. Repped and marked as solved.

[Test123Test]

Is it possible to get the code to run on column C instead of A? I had to update my data, it looks like this now:
X Y 700 1 1 1 1
X Y 705 1 2 2 1
X Y 715 1 1 1 1
X Y 720 1 1 1 1
X Y 725 1 1 1 1

Also I notice if it is run on data that looks like this
755
805

It will look like this after code is run
760
765
770
775
780
785
790
795
800
805

This is the same problem as previous, but it only occurs between 55 and 05 datapoints.

[patel45]

can you attach again a sample file with data and desired result ?

[Test123Test]

For some reason Manage Attachments has stopped working for me. So I uploaded a sample workbook here: http://www.sendspace.com/file/gfot9b
Original data is on the left in the workbook (gaps are marked in red). Desired result is on the right.

If possible, but not neccessary, is it possible to also make the macro check that each day in the dataset starts with 830 and ends with 1510? So if a day starts with 840, just copy data from last datapoint for the previous day and insert as 830 and 835. And if a day ends with 1500, just copy that down for 1505 and 1510 then end. If you scroll down in the sample workbook you can see two examples of what I mean. Your help has been indispensable so far, so it's not really neccessary but would be nice if you could help with this also.

Thanks!

[patel45]

for now try this code for only one day

Please Login or Register  to view this content.

[Test123Test]

Nice, works perfect for one day, thanks!

But at the end of the day after 1510, it keeps adding data beyond 1510. If the data ends at 1510 it adds data to 1530 for instance, but in reality it should just end at 1510. But other than that, I can't complain!

[patel45]

try this for only one day, for many days I did not find the solution for now

Please Login or Register  to view this content.

[Test123Test]

Thanks, but unfortunately when running this code on the Sample sheet attached above, the same problem still occurs, it goes above 1510 for one day. Try to run it on the sheet and you can see it also when scrolling down in the data...

[patel45]

For only one day I mean no other data, only one day data.

maybe I solved for many days

Please Login or Register  to view this content.

[Test123Test]

Thanks, it's working beautifully for one day. For multiple days it's almost good, only 2 problems I could see:

1. If a day ends in 1455 or lower, it will insert 1510 as the next datapoint, and it will forget 1500 and 1505. But if the last datapoint is 1500, it will work correctly.
2. If a day begins in 900 or higher, it will insert datapoints 870, 875, 880, 885, 890, 895 and then 900, instead of 830, 835, 840, 845, 850, 855 and then 900.

I have a sample workbook here, with the macro in it. If you scroll down a bit in the spreadsheet, I have marked in RED the points of interest. Try to run the macro on the spreadsheet and you can see the problems I described.

Thanks again for all your time, I really appreciate it

[patel45]

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

there is absolutely no need to select the cells in each loop - if you comment

Please Login or Register  to view this content.

in both loops the procedure should run considerably faster.

Second is a matter of taste (I donīt like codes where the variables arenīt dimmed so I try to get the code as short as possible for using only a small amount of variables).

Your code (8 variables)

Please Login or Register  to view this content.

Iīd prefer (2 variables):

Please Login or Register  to view this content.

Just my 2 cents on the code you posted.
Holger

[Test123Test]

Works nicely, thanks a lot!

Unfortunately the data I'm working on is even crappier than I thought, it truly is a nightmare and I didn't expect this. Sometimes there can be gaps over 1 hour. For instance a gap between 1145 - 1335. Then the macro will only insert from 1145-1230 and skip right to 1335. Also if a day starts with say 1000, it will insert data from 830-855 then skip to 1000. I didn't expect this but if you could help me with it also it would be much appreciated. I have attached a sample file of example data (with newest macro enabled in workbook). If you run the macro you can see it's behavior.

http://www.sendspace.com/file/tmmm7n

[patel45]

Please Login or Register  to view this content.

[Test123Test]

Thanks, working great for large gaps in data now!

But for some reason, with some larger gaps before the close @ 1510 (If data ends at like 1300 or so, the macro inserts correctly all the way to 1510), but then will add 1515, 1520, then another 1510 and finish.
Also when fixing opening gaps, the macro will add two of the same datapoints at the end. So if the data starts at 1000, it will correctly insert from 830-1000, but willl list 1000 two times in a row, and finish.

Attached sample w/ macro enabled: http://www.sendspace.com/file/1f1u5q

[patel45]

Please Login or Register  to view this content.

[Test123Test]

After some testing, it's working FANTASTIC now! Absolutely beautiful, thanks so much for all your help. I have thousands of these files that need fixing up, this will surely make the job much easier.

I have one small request though, some of my files have a starting time of 700 instead of 830. Is it possible for you to make a copy of the above macro, but with 700 as start-time instead of 830? So I have two versions of it, one for my 700-files and another version for my 830-files.

Thanks again!

[patel45]

Please Login or Register  to view this content.

[Test123Test]

Fantastic, thanks for all your help