Return values between time range

Question

Hi, I'm struggling to produce an idea of how to return value and then run certain calculations.

In the example below, I have two columns. Time and numbers

• first, I would like to return values between 11:00:00 and 12:00:00, all the way down (the Tiem column represents 5-minute intervals of 24 hours),

To be more specific I would like to return values for each day, between 10:00:00 and 11:00:00

• second, out of the returned values, I would like to return to the next columns only values that are greater than the top cell.

(the table below is an example of a few thousand data points that represent days)

10:50:00	2369.2
10:55:00	2368.3
11:00:00	2370
11:05:00	2369.2
11:10:00	2368.2
11:15:00	2370.9
11:20:00	2370.2
11:25:00	2366.3
11:30:00	2367
11:35:00	2367.8
11:40:00	2371
11:45:00	2368.7
11:50:00	2367.3
11:55:00	2374.2
12:00:00	2373.3
12:05:00	2367.8
12:10:00	2367.8
12:15:00	2364.4
12:20:00	2361.9

Answer 1

It looks like Excel is not the best tool for such analysis, and definitely it is above my capabilities right now. Although I thank you for your time and effort, I appreciate it.

You're right. If you're with Tradestation, you've got some good strategy testing tools. See

https://help.tradestation.com/10_00/eng/tradestationhelp/st_testing/strategy_testing.htm

May I lastly ask you how did you corrected the date formats ?

In the link you supplied in your message in this thread of 23 Mar 2024 10:22:24 PM to TS.xlsx (hopefully:
https://1drv.ms/x/s!AnQVWPhytqkwga0hvM-BdIhtd-rYNw?e=qOrlp8 ) I've done the following:

1. Changed the formatting of column A to General
2. Scrolled down to the vicinity of row 2290 where you can see a mix of text and numbers in column A, where:

• the text values are unconverted data which Excel failed to convert
• numbers which Excel has converted from text to dates... only it's got them ALL wrong (months/days the wrong way round)

So: see the formula in cell I2286:

=IF(ISNUMBER(A2286),DATE(YEAR(A2286),DAY(A2286),MONTH(A2286))+MOD(A2286,1),DATEVALUE(A2286)+TIMEVALUE(A2286))

which I've copied down for 10 or so cells. You need to copy this up and down for the whole range of data.

This formula says: If column A is a number then swap days and months, otherwise convert the text to date and time using DateValue and TimeValue.

This works well for me here in the UK but it (DateValue) may not work in your locale, so check that the results are correct (the dates/times should flow consecutively). Then only if all is OK, copy/paste-values from column I to column A over the existing data.

If the results are not OK, come back and I'll have a workaround.

Answer 2

It looks like Excel is not the best tool for such analysis, and definitely it is above my capabilities right now. Although I thank you for your time and effort, I appreciate it.

May I lastly ask you how did you corrected the date formats ?

Answer 3

However, I would like to test the specified time interval at a specific time of the day. So, let's say 50 minutes each day at the same time of the day. For example, start at 09:00 and end at 09:50 every day.

See linked-to file below.

Long trades only.

Sheet1: Specify start time in cell D1, length in cell F1

Result (green) table at cell H2. Needs refreshing like a pivot table: right-click somewhere in the green table and choose Refresh.

It lists only trades which could be exited at a profit, even if that happens in the same bar as the entry (as long as that bar opens at its low).

Sheet2: Previous 'every-n-minutes' solution as specified in cell F1 of Sheet1. Refresh to update it.

The workbook: https://app.box.com/s/9ywmgjqp087jzc3kr9zdiyjxtjf2thsw

I wasn't expecting it will be so complex and difficult

I agree, those formulae are very difficult to adjust/maintain, hence the attached.

Answer 4

It looks very good!

I'm trying to wrap my head around it.

The one thing that I'm worried about is that I could mislead, or we could misunderstand each other, about "time interval." The way I understand it now is that we test every 50 minutes. (pls correct me if I'm wrong)

However, I would like to test the specified time interval at a specific time of the day. So, let's say 50 minutes each day at the same time of the day. For example, start at 09:00 and end at 09:50 every day.

I apologize if it comes from my side.

That formula in the M column, ohh my ohh my. I don't even know where to start.

I wasn't expecting it will be so complex and difficult