Support Board
Date/Time: Mon, 16 Jun 2025 10:51:35 +0000
Sum of Values restricted to Session/Trading Day
View Count: 248
| [2025-04-30 09:07:46] |
| pjk0225 - Posts: 42 |
|
I'm trying to get the sum of historical values of a custom study for each trading session over one year. Right now, I'm having a problem where some of the values counted in the sum are being double-counted. The formula I'm using is a spreadsheet formula: sum(ID237.sg1[0:-450])
On some days, there are less than 450 bars, so the sum will "overflow" into the previous day.Since I want the sum of each day, I'm outputting the sums on a spreadsheet and locate the last bar for each day to get that day's sum. Is there a way I can avoid the double-counting? How can I make sure the sum doesn't include values that don't match the date of the last bar of the day's session? |
| [2025-04-30 15:32:43] |
| Sawtooth - Posts: 4227 |
|
Try this, using the Spreadsheet Study study: K3: =IF(INT(A3)<>INT(A4),0,K4+1) L3: =SUM(ID237.SG1@3:OFFSET(ID237.SG1@3,K3,0)) This brings the sum of the most recent day's values to row 3. Assuming the Number of Rows setting is large enough to see previous days, use another Formula Column for each previous day: M3: =IF(INT(A3)<>INT(A4),L4,M4) O3: =IF(INT(A3)<>INT(A4),M4,O4) ...etc. This is based on this example: Spreadsheet Example Formulas and Usage: Return Last Two Zig Zag Reversal Values |
| [2025-04-30 22:55:46] |
| pjk0225 - Posts: 42 |
|
Thank you for the idea. I understand the logic; what if I needed to accomplish this using just the library of studies on SC and the Spreadsheet Formula study? Is there a way to achieve the same thing?
|
| [2025-05-01 00:05:18] |
| Sawtooth - Posts: 4227 |
|
what if I needed to accomplish this using just the library of studies on SC and the Spreadsheet Formula study?
Try this, using the Spreadsheet Formula study:=IF(BARDATE<>BARDATE[-1],ID237.SG1,ID237.SG1+ID1.SG1[-1]) where ID1 is this Spreadsheet Formula study. This will give you a cumulative sum since Midnight. But you need to scroll back to see each day's summed value. You also might try the Cumulative Sum of Study study, and set 'Reset at Start of Trading Day' to Yes. However, this study uses the Chart Setting's Session Start time for the trading day start, if 'Use Evening Session' is set to No, but uses the Evening Start Time if 'Use Evening Session' is set to Yes. |
To post a message in this thread, you need to log in with your Sierra Chart account: