Finding a value by date and time. Quickly without looping.

[Mellstock]

I have 5 min currency market data with the Columns date, time, Open, High, Low, Close.

I would like to find the close value of the 21:55 time period on a given day and to do this as efficiently as possible.

As the user the macro asks me to enter a date. I would like the macro to return me the close value of the 21:55 time period for that date.

Is is it best to find 21:55 in the time column first then compare that with dates or the other way round.

I know the close value I would like to return is Offset(0,5) from the date column once I have found the Row with the correct date and time combination. Thank you.

[p45cal]

Is the data coming from outside Excel (I guess it has to)? You might be quicker with a direct query/sql on the external data.
I guess you're saying 'without looping' because you think it might not be fast; it can be faster than vlookup/index/match and the like.
Either attach a file here with some data (or link to one on a file-sharing site if it's too big, or attach a data file containing that data, or tell us where we could find such data). This would save us guessing (probably wrongly) how your data is arranged/formatted etc. and save us time setting up time trials - make it easy for us to help you.

[Artik]

If you are looking for the last value on a specific day, then find ... the first on the next day. When you find it, it's enough for you (probably) to move one row higher ( Offset (-1, 0) ).

Artik

[Mellstock]

Thank you very much for your reply. Yes the data is imported from an external source. I am away from home at the moment with just an iPad without access to Excel and my PC. I will post a sample when I get back. Other work I have done on the spreadsheet with vba has taken a noticeable amount of time hence the comment regarding speed.

[Mellstock]

Thank you very much for your reply. Yes the data is imported from an external source. I am away from home at the moment with just an iPad without access to Excel and my PC. I will post a sample when I get back. Other work I have done on the spreadsheet with vba has taken a noticeable amount of time hence the comment regarding speed.

[p45cal]

In that case, include what VBA code you've tried.

[Mellstock]

Thank you for your interest. I have not written any code so far to try to solve the problem asked about.

The code I have written successfully extracts 5min (or potentially any time interval) from raw imported 1 minute data.

[Mellstock]

Thank you for your interest Artik. Yes I have previously thought of this as a solution. I am still left with the issue of finding the 22:00 entry on a given day. Also there is no 22:00 entry on a Friday.

[p45cal]

Does the source data have a single value/field for date & time, or value/field for date and a separate value/field for time?

[p45cal]

Other work I have done on the spreadsheet with vba has taken a noticeable amount of timeThere's a good chance we could speed that up!

[Mellstock]

The source data comes with a combined date and time field with a hyphen (-) as the separator. My code written so far splits the field in the source data I nto two columns one each for date and time.

[p45cal]

This makes the search a lot easier because we can create a search item from the date input and your 21:55 and only search one field.
I await workbook (and source data if possible).

[Mellstock]

Will do. Maybe 48 hrs until I am back with access to my PC.

[Mellstock]

Hopefully I have uploaded some sample data. Sheet 1 has the raw imported data in one minute intervals. Note it is supplied with the High column values lower than the Low column values!

On sheet 2 there is the same data where this error has been corrected, the combined date and time stamp split into 2 columns and the data rounded to integer values with a Macro called Sanitise.
Bankers round noted for Excel vba.

Next to this data 5 min time interval data has been extracted from the cleansed and rounded raw data with Macro Extract5mindatabestgo or similar.

My question is how to search the extracted data for the 21:55 close value?


I have just checked my post and noted the extracted data is 15 min. In this case the question (the same underlying principle applying) would be how do I search the 15min data for the 21:45 close on a given date.
Apologies my first time and it to me ages to work out how to upload the spreadsheet.

[SamT]

There was insufficient data to test a search function, but it was not hard to really speed up the results you already have, There are only two parts to this workbook at this time. ImportData which works on your sample data faster than I can see, and SetGranularity which is also very fast.

If you can upload a few days of Sample data...

Due to the scarcity of data, SetGranularity is very bare bones. Both Subs are available from the Macros Menu

I did add a hidden column to the Results sheet, containing just the minutes value of the TimeStamp. It is now just used for SetGranularity, but I figured that this method will make it easy to use PivotTables to visualize your entire data set at any granularity desired. Note that the hidden Minutes column uses "60" instead of "00" so that you can avoid the exact hour times as needed.

[Mellstock]

Thank you so much SamT for your help. Your code is a real education to me with many functions and approaches I am not familiar with which I am sure will be very helpful to me with this and future projects. I have had a 15min or so look at it but will need to study it in more detail to understand more fully. I need to go to my day job now.

My original question remains. How can I search data like this to find a particular cell value for a given date and time period.

p45cal was hinting at maybe searching not splitting the combined date and timestamp. Presentation wise I like the split combined date and time field. Your hidden minutes column is very elegant, as is expressing 00 minutes as 60.
Maybe vba could work with the joined up date and time field in its memory and me, the user could see the separate date and time columns.

My stated query was how to find 21:55 Close value in 5 min data on a given date (the New York close London time) . It could equally be the New York Open on a given day namely the opening value of the 13:30 data row.

[SamT]

Finding the Row containing a particular date and Time is sorta complex, but not complicated, after that it's merely choosing what "price" field you want. From A1, find the date in the Previous direction, Offset that cell to B, then find the time, also in the Previous direction., It will happen in microseconds.

The same algorithm used in SetGranularity can be used against the Time column to hide all Times except 23:59

For Rw= 2 to UBound(Times)
Rows(Rw).Hidden = CBool(Times(Rw) <> "23:59")

Please upload three days worth of Raw data.

also decide what granularity of data you need, 1 minute, 5 minutes. Note that only keeping the 5 minute data makes the data set 80% smaller. It mostly depends on your own stock tracking and dealing style. You might even want to keep the data set as atomic as possible until you have "played the market" for a while.

[Mellstock]

3 days worth of raw one minute data is 4320 rows of data.

[p45cal]

I'm travelling all day today and won't be in front of a pc until tomorrow, so I'm replying on a phone.
If the original question of looking for a specific open/close value at a specific time and only that you won't need to go to the bother of downloading all the data, cleaning it up, adjusting the time frame. The value you want is sitting there waiting for you to pick it up (and round it if you want).
If you want 5 minute granularity then you look for the 21:55 value, for 15 minute granularity you look up the 21:45 value, for 20 min. granularity you look for the 21:40 value etc. This should be perfectly possible using a query probably with a short sql statement, just exactly what query depends on the source of your raw data.
5000 rows of data is insignificant. I'd like to see the query that sits behind your getting that raw data into Excel. It might only need adjusting.

[Mellstock]

Thank you p45cal for your help and interest. The macro which extracts the data from the raw data is in the spreadsheet I uploaded. I think it is called betterextract5mindata or something similar (There are only 2 Macros in the spreadsheet) which I wrote to run after the raw data had been run through Sanitise.

Despite its name it will extract 5,10,15 or 20 min data after an input box request.

I will upload more raw data when I am back at my PC

[p45cal]

What you're calling raw data and what I call raw data is a bit different - I'd like to know how you get your raw data into Excel in the first place!, but I'll look into how I might extract the necessary value from what you're calling raw data tomorrow.

[Mellstock]

Thanks to viewing a few you tube videos I have managed to get nearer to solving this issue using Index and Match formulas in a spreadsheet. (attached) So I know Excel can do this.

How to get a vba programatic solution is my next step.

Thanks for all the help so far. Any pointers on achieving a vba solution will be greatly appreciated.

[p45cal]

You can do this from your raw data sheet directly, that way (a) you don't have to clean and reorganise that data at all and (b) you'll only have to match/lookup one column.

Let's say you have you granularity value in a cell, say K4, a number in minutes, so 1, 5 10, 12, 20, 30, whatever.
We'll have a regular excel date in cell K5.
We can derive a string to match thus:
=TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss")
You don't need this formula in a cell anywhere but it will show you how it works and you can experiment changing the values in K4 and K5.
Instead incorporate it into a larger formula:
=VLOOKUP(TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss"),$A$1:$E$30,5,FALSE)
The $A$1:$E$30 reference is for your raw data sheet (Sample)
You can apply a rounding function around the whole lot of course.
The equivalent index/match formula would be:
=INDEX($E$1:$E$30,MATCH(TEXT(K5+TIME(22,IF(K4>1,-K4,0),0),"yyyy:mm:dd-hh:mm:ss"),$A$1:$A$30,0))
again the $E$1:$E$30 and $A$1:$A$30 are references to cells on the Sample sheet of the file you attached to msg#14 (whose times are out of range for this formula).

vba tomorrow

[Mellstock]

Thank you so much for this p45cal. I have played around with the VLOOKUP version and altered K4,K5 and the hard coded 22 in the code. All works a treat.