finding MAX, MIN, FIRST, LAST for a given period of time


Hello! I have the data for transactions for a given stock (NFLX) in one hour (column A is the time in hh:mm:ss.0 format) and the prices at which the transactions ocurred (column B is price). The data comprises more than 16,000 transactions in one hour.

I need to find a way to obtain the initial price (first), the last price (last), the highest price (max) and the lowest price (min) for every 5-second period (example, from 10:00:00.0 to 10:00:05.0, then, from 10:00:05.0 to 10:00:10.0, and so on). I've tried to use VLOOKUP but I can't input formulas into the function.

Please help!!!




Selected Answer

In the attached workbook you can enter a starting time in E1 and a section interval in E2. These two criteria generate a list of start times in column E. Each interval is formulated as the time from start to less than end. This is different form your definition. The first section runs from 10:00:00 to 10:00:04, the last from 10:59:55 to 10:59:59. Note that the values in column B have been names as Times and those in column C as Quotes.

[J5] = MATCH($E6-(1/10^6),Times,1) and [I5] = IFERROR(MATCH($E5-(1/10^6),Times,1),0). Note that J5 refers to D6 in the next row. Both formulas use (1/10^6) to define about 1/100th of a second before the precise time so as to leave the exact time in D6 as the start of the next range. This seems to be more exact than your data. If it leads to false setting of ranges increase the exponent. The range itself is defined with this formula in H5. Numbers in column A identify the row numbers of the two named ranges.


This formula draws a sum which I used to check whether the range is set correctly. (You can select the correct range in column B and read the sum in the result panel at the bottom center right of the screen.) The ranges tested in this manner are then used in columns N:O to extract the Highs and Lows. Variations of the First and Last range row formulas are used to extract Open (First) and Close (Last) quotes.

Note that no error should occur except in the very first row. Therefore, strictly speaking, the IFERROR functions in the formulas are required for no other purpose but to allow the formula in the frist row to be copied down unchanged.



First of all, THANKS!!! (sorry for shouting)
The exponent used in J5 and I5 (1/100) is more than enough as my original data is set to 1/10th of second. 
I'll run it on the data, as a quick check shows no mistakes on your approach.
thanks again, I really appreciate your effort in helping me.
renato (rep: 2) Oct 8, '18 at 9:57 am
Hello Renato, shout anytime you feel like it lol: I’m glad I could help. Forgot to mention that the solution is designed to work anywhere. You might have it on a sheet different from the data and then update the data without changing anything on the output sheet.
Variatus (rep: 1850) Oct 8, '18 at 2:56 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login