PDA

View Full Version : Sleeper: Min,Max,First and Last Values within Time window



Ted
04-20-2010, 08:44 PM
Hi Experts,

I want to see Min, Max, First value, Last value and number of occurances in 30 sec time window using macro.

Time is in Column A and values are in Column B.

Can anyone help me on that.

Thanks

p45cal
04-21-2010, 10:52 AM
see attached.
The formulae in cells C2 to F2 are all Array-Entered (ctrl+shft=enter, rather than just Entered), that in G2 is normally entered (but it wouldn't matter if you did array-enter it).
Then all formulae can be copied down.
I note that you have the columns in OLHC order rather than the more conventional OHLC.

Ted
04-21-2010, 11:34 PM
Thanks alot p45cal. This was great help and I learned Array-Entered formula today !

I am refining my sheet right now, but I fear I will land into issues with new scenarios/requirements. Wanted to thank you till I ask for more help.

Ted
04-22-2010, 09:19 AM
Hi pa45cal,
Your solution works well but the sheet has gone too slow.
Can you please describe how to convert these formulas to macro which lets say would trigger at every 30 seconds. I am thinking that would reduce the calculation load in the sheet.
Thanks for your help

p45cal
04-22-2010, 10:15 AM
Are these data coming in real-time? If so, how?

Ted
04-22-2010, 10:31 AM
Yes data is coming real time from DDE event.

Ted
04-22-2010, 10:35 AM
So it means that in Sheet 1, Col A and Col B get new data at random time. I am already placing these new data per row as they come in Sheet1.

Now i want to find Min, Max, First and Last value per 30 second from the dynamic data feed in sheet 1 and show results in sheet2.

p45cal
04-22-2010, 10:44 AM
So you're already using macros to do this? I'd like to use that code to trigger what you're after.

Ted
04-22-2010, 01:08 PM
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
If Range(<DDE Cell Ref>).value <> 0 Then
Dim LastCell As Range
With Worksheets("Sheet2")
Set LastCell = .Cells(.rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.offset(1, 0)
End If
End With
Dim rSource As Excel.Range
Dim rDestination As Excel.Range
Worksheets("Sheet1").Range(<DDE Cell Ref>).Copy
Worksheets("Sheet2").Range("B" & LastCell.row).PasteSpecial xlPasteValues
Worksheets("Sheet2").Range("A" & LastCell.row).value = Now
End If
Application.ScreenUpdating = True
End Sub

Ted
04-22-2010, 01:12 PM
I placed the above code in core module of Sheet1.

Description of what code does.
Whenever any cell changes in Sheet1, it copies the value of DDE cell and pastes it in Sheet2's new row.

I have to improve trigger code in Sheet1 such that it triggers ONLY when DDE cell is changed irrespective if other cells are changing or not. (There is another thread which is dealing with trigger improvements)

Aussiebear
04-22-2010, 02:26 PM
G'day Ted, When posting code to the forum, please highlight the code then use the Green & White VBA Button to enclose the code inside the tags

p45cal
04-22-2010, 04:26 PM
Tomorrow or Saturday..

Ted
04-22-2010, 05:01 PM
No problem. Thanks for your help.

Let me try to improve the calculation triggering code above with suggestion from other threads.

p45cal
04-24-2010, 07:37 AM
This is work in progress, code is still configured for testing (I have no DDE feed, and even if I had one, markets are closed) so there are still quite a few changes to be made, but the attached is where I've got to so far.

The values match those obtained by the formulae in the last offering.

The final row on the output sheet changes in real time with each incoming change in value, rows being added at each half-minute as long as data is incoming..

Sometime next week I could make up my DDE link and get data, but you'd make things easier for me if I had a look at what you've got yourself.
If you don't want to make your sheet public, PM me.

mdmackillop
04-24-2010, 08:04 AM
Hi Pascal,
Thanks for these formulas, I've finally worked out how to use them!
How about using dynamic range names instead of the cell references? I notice you hadn't updated for your extra test rows (1127 - 1172) so helps avoid such issues.
Regards
Malcolm

p45cal
04-24-2010, 09:42 AM
Absolutely md.
Because the OP thought these would be too slow, (the existing data is about 1000 rows for only one hour's data, after a seven hours trading day there'll be rather more - and there are stocks/instruments which trade much more frequently than this!) I was thinking along lines of reducing calculating time:
1. By converting established rows on the output sheet to values.
2. By reducing the sizes of the ranges used within the formulae, and this is where some sort of dynamic named ranges would be most useful.

However, all that changed when the OP said the data was coming in real-time. Now I hope to lose all formulae from the output sheet. In fact, once the OP is satisfied that the output sheet is correct by checking against the raw data sheet, he won't even need that raw data sheet to be created any more.

So yes, you're right, dynamic names would have been better - I had changed the bottom-most formulae to use up to row 1200, but just for cross-checking purposes.

johnchoo
06-03-2010, 07:02 PM
I am also using a version which does tick data.