Login Page - Create Account

Support Board


Date/Time: Mon, 30 Dec 2024 02:01:15 +0000



[User Discussion] - Spreadsheet reset at start of new day

View Count: 1163

[2015-07-04 00:14:25]
Neo - Posts: 198
Hi,

I'm using a spreadsheet study with rows equal to the amount of bars in one trading day( based on my charts timeframe).

Using =Median(B3:B198, C3:C198, D3:D198, E3:E198)

What needs to be added so that this calculation will reset and start again each trading day?

Thanks
[2015-07-04 02:02:58]
Sawtooth - Posts: 4143
Try this:

Put this in K3:
=IF(INT(A3)>INT(A4),1,0)
This will identify the first bar of the day with a 1.

Put this in L3:
=MEDIAN(B3:INDEX(B3:B1002,MATCH(1,K3:K1002,0),1),C3:INDEX(C3:C1002,MATCH(1,K3:K1002,0),1),D3:INDEX(D3:D1002,MATCH(1,K3:K1002,0),1),E3:INDEX(E3:E1002,MATCH(1,K3:K1002,0),1))
This will give you the OHLC median, resetting on the first bar of the day.

In the spreadsheet settings, set the Number of Rows to 1000.
You no longer need to limit the rows to the number of bars per day.
[2015-07-04 04:07:47]
Neo - Posts: 198
Legend! Thanks alot Tom, that's exactly what I was after.

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account