Hi

I have one minute financial data of the form:-

20010102, 230300, OP, HP, LP, CP

where OP = Open Price , HP = High Price, LP = Low Price, CP = Close Price
and Date = YYYYMMDD, and Time = HHMMSS

However, some minute data does not exist i.e. there are a variable number of minute data to make up each hour

I would like to condense the minute data into hourly data such that

(i) OP becomes OP for 1st available minute data for that hour
(ii) HP becomes max(of all available HP's) for that hour span
(iii) LP becomes min(of all available LPs) for that hour span
(iv) CP becomes CP for last available minute data for that hour

Any suggestions for the appropriate formulae gratefully received

Thank you

Bob M

p.s. if the fields were separated into their own columns and there were say 6 rows per minute I could use the following:-
OP: =OFFSET(Sheet1!C$2,(ROW()-1)*6-6,0)
HP: =MAX(OFFSET(Sheet1!D$2,(ROW()-1)*6-6,0,6,1))
LP: =MIN(OFFSET(Sheet1!E$2,(ROW()-1)*6-6,0,6,1))


CP: =OFFSET(Sheet1!F$2,(ROW()-1)*6-1,0)

but I do not know how to accommodate the variable number of rows per minute

Could you possibly upload the file (click Go Advanced)?

file attached

Attached Files

tick_testing.xlsx (460.0 KB, 13 views)

Thanks - could you please give some examples of expected results?

e.g.

3 jan 2001
01 minute - 110 rows condensed
02 minute - 56 rows condensed
03 minute 56 rows condensed
04 minute - 59 rows condensed

04 minute values
OP:- 114.58 from very first row
HP:- 114.60 from 7th row
LP:- 114.53 from any of seven rows (incl. last row)
CP:- 114.53 from very last row

I regret, but I don't know how to help.

To separate the data into individual columns, DATA TAB (up top Excel 2007) - TEXT TO COLUMNS.

When you get to the Delimiter type choose COMMA.

First, highlight all of Col A, then take the above steps.

Attached Files

tick_testing (2).zip (1.04 MB, 8 views)

Is it reasonable to think that your expected results posted in #5 are for hours 01, 02, 03 & 04 and the rows for hour 01 would be 54 ?

Are you interested in a macro/vba solution ?

Hi

Not clear what results you are referring to................

Yes - would appreciate a macro solution
Bob M

This macro solution uses 3 separate macros all called from the first.

first:
creates a temporary sheet
distributes the data to columns

second:
creates lists of unique dates and hours

third:
a loop within a loop filters by date and hour
numbers extracted and written to separate sheet
deletes the temp sheet when finished

Your sample data of 28,707 records requires 624 filters being applied and removed.
Results in 501 lines being written to the sheet.
Takes an eternity to run (43 seconds on my computer).
Sorry, I'm not sharp enough to speed that up.

Zipped due to file being just over 500 KB.

Attached Files

tick_testing.zip (323.2 KB, 12 views)

Hi

You are brilliant

Exactly does what I wanted

Many thanks

Bob M

You're welcome.

If that looks after your original request please mark the thread as solved.
Supposedly there is something in the Thread Tools drop down near the top of the page for this.

Thanks
NoSparks

Hi
can't see how to

Marked solved

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post