Support Board
Date/Time: Fri, 29 Nov 2024 12:55:02 +0000
[Programming Help] - Spreadsheet Formula for average winning trade duration?
View Count: 395
| [2023-02-06 01:45:42] |
| j4ytr4der_ - Posts: 938 |
|
This one is doing my head in. I feel like it's something related to time vs. duration, but not sure. I'd like to compute a realtime average of my time in winning trades. This seemed fairly simple but something isn't right: Formula for getting a running sum of duration in winning trades (Currently in M43, locking cell so that in cases where J41 - J42 is not returning a correct/valid value in any moment like when the trade closes, the value locks): =IF(
J10 > 0, MAX( M43, M43 + (J41 - J42) ), M43 ) Then I just take that result and divide by # of winning trades... right? =M43/J33
Sadly, not matching at all what I see in the TAL Statistics tab. I get a value, it's just way off. Does anyone know what I'm doing wrong, or another way to accomplish this? Date Time Of Last Edit: 2023-02-06 01:46:40
|
| [2023-02-06 02:09:34] |
| j4ytr4der_ - Posts: 938 |
|
On closer inspection, adding the current difference in duration to the existing sum, is adding way faster than it should be. I can have less than 1 second go by on the actual J41 clock, and I'll have 10 or 15 seconds add to the sum. Obviously I need to dig deeper here.
|
| [2023-02-06 06:01:29] |
| j4ytr4der_ - Posts: 938 |
|
The problem appears to be that you can't add a value computed by a realtime formula, to another cell as SC is performing the addition at every update and not only when the value being added, changes. I can't figure out a way to add cell A to cell B, only when cell A changes.
|
| [2023-02-06 12:49:46] |
| Sawtooth - Posts: 4120 |
|
Column M is a Formula Column, so please explain why you are referencing M43.
|
| [2023-02-06 13:25:12] |
| j4ytr4der_ - Posts: 938 |
|
It's just how I usually do things while I'm developing, it's down near the other cells I'm referencing from J so I can easily see them all live. I'm only using 2 rows in the sheet, I almost never reference any past bar data and am usually working on a full globex-session footprint. Once I get it working, I'll move it up to one of the normal formula columns.
|
| [2023-02-06 15:48:59] |
| Sawtooth - Posts: 4120 |
|
Some thoughts: - J41 advances even when J10 is < 0 so the time duration when J10 > 0 will be obscured. - It's not possible to capture an intrabar value in a spreadsheet study so the time duration when J10 is > 0 is not accessible. Date Time Of Last Edit: 2023-02-06 18:36:39
|
| [2023-02-06 18:14:40] |
| j4ytr4der_ - Posts: 938 |
|
Yep, I want J41 to advance as I want the total once the trade closes profitably, assuming it does. But the intrabar calculations are the trouble. I did find a way to lock the trade duration so that it only updates when the trade closes, but I still can't get a cumulative running total to then base an average off of. This is clearly the realm of ACSIL unfortunately, since that's way above my pay grade. Ah well.
|
To post a message in this thread, you need to log in with your Sierra Chart account: