Support Board
Date/Time: Fri, 31 Jan 2025 19:02:11 +0000
[User Discussion] - calculate bars per x time
View Count: 1207
| [2019-05-15 04:34:01] |
| whats1thingnow - Posts: 407 |
|
hi support, is there a study, or easy way to calculate the number of bars in a minute, or count of bars in 5 minutes? i'm using point and figure charts and i want to calculate the number of bars in x time thank you! |
| [2019-05-15 14:44:59] |
| Sawtooth - Posts: 4164 |
|
You could do it with the Spreadsheet Study study. First round the bar timestamp to the nearest 5 min multiple, e.g in cell O3: =MROUND(A3,5/1440) Then count the bars from each 5 min multiple, e.g. in cell P3: =IF(O3<>O4,1,P4+1) |
| [2019-05-16 04:33:59] |
| whats1thingnow - Posts: 407 |
|
thank you so much tomgilb, you are a genius! i almost have it working... couple questions: 1) 5/1440 why does that give me 'nearest 5 min multiple' 2) is it possible to start the every 5 mins from 9:30 EST? |
| [2019-05-16 05:09:59] |
| Sawtooth - Posts: 4164 |
|
1. There are 1440 minutes in 24 hours, so 5/1440 is equal to 5 minutes in Serial DateTime format. Spreadsheet Functions: Serial DateTime Values The MROUND function in the formula rounds the bar timestamp up or down to the nearest 5 min interval. 2. The easiest way is to set the Session Start time to 09:30:00, and check 'New Bar At Session Start'. |
| [2019-05-16 05:48:03] |
| whats1thingnow - Posts: 407 |
|
tried the start time at 9:30:00 but unfortunately didn't work: 2019-05-15 09:32:44 2817.25 2817.25 2816.50 2816.50 264 150 43600.399
2019-05-15 09:32:25 2817.50 2817.75 2816.25 2817.00 3245 1559 43600.396 2019-05-15 09:32:07 2818.50 2818.50 2817.75 2817.75 1520 780 43600.396 2019-05-15 09:32:01 2818.00 2818.25 2817.50 2818.25 1052 532 43600.396 2019-05-15 09:31:47 2819.00 2819.00 2818.25 2818.25 1249 611 43600.396 2019-05-15 09:31:31 2819.25 2819.50 2818.00 2818.75 1717 1001 43600.396 2019-05-15 09:31:00 2818.00 2820.25 2817.25 2819.50 6014 2893 43600.396 2019-05-15 09:30:31 2818.75 2819.25 2817.00 2817.75 5242 2494 43600.396 2019-05-15 09:30:12 2818.75 2819.75 2818.50 2819.00 3096 1659 43600.396 2019-05-15 09:30:06 2817.75 2818.50 2817.75 2818.50 1588 774 43600.396 2019-05-15 09:30:00 2818.25 2818.75 2817.75 2818.00 3456 1479 43600.396 as seen above, 09:32:44 it became 43600.399 the reason i want 9:30:00 to 9:34:59 to be 1 set then 9:35:00 to 9:39:59 to be the 2nd set is because i'm doing vlookups i have entry and exit timestamps and i'm doing vlookups with these timestamps to find out the # of 'point and figure' bars that occurred when i took that trade so what i was thinking was to round the entries and exits timestamp to nearest 5 min as well, and with a common rounded to nearest 5 min timestamp, i can do vlookups Date Time Of Last Edit: 2019-05-16 05:49:46
|
| [2019-05-16 09:16:21] |
| User907968 - Posts: 825 |
|
You could substitute FLOOR for MROUND in the formula above. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#FLOOR_Function e.g. =FLOOR(A3,5/1440) instead of MROUND(A3,5/1440) This should round down the timestamp to the nearest 5-min, so 09:30:00 to 09:34:59 will all become 09:30:00 |
| [2019-05-17 05:19:49] |
| whats1thingnow - Posts: 407 |
|
genius guys!!! this works like a charm :) |
To post a message in this thread, you need to log in with your Sierra Chart account: