Login Page - Create Account

Support Board


Date/Time: Wed, 22 Sep 2021 05:47:56 +0000



[Programming Help] - Time Period Volume (and Text Display)

[2021-09-15 03:30:20]
SavantTrader - Posts: 29
Let's assume I have a 1 minute intraday bar chart. I'm looking for a way to display (as text) the total volume for the first 5 minutes of the open. I know I can use Text Display for Study to display a quantity from another study, but I don't know how to get only the first 5 minutes of volume. In other words, let's assume I want to display the 5 minute Opening Range volume (computed as total volume between 09:30:00 and 09:34:59). How do I accumulate just this period volume?
[2021-09-15 11:59:59]
tomgilb - Posts: 2989
Try this:

Add the Spreadsheet Formula study. and check Hide Study.
- Use a formula like this in its Formula field:
=IF(BARTIME=TIME(09,34,00),SUM(V[0:-4]),ID1.SG1[-1])
where the Spreadsheet Formula study is ID1.

Then use the Text Display For Study study to display the Spreadsheet Formula study's output.

https://www.sierrachart.com/index.php?page=doc/StudyChartAlertsAndScanning.php#BaseGraphIdentifiers
https://www.sierrachart.com/index.php?page=doc/StudyChartAlertsAndScanning.php#ReferencingARangeOfData
[2021-09-16 05:29:30]
SavantTrader - Posts: 29
Thank you for the effort. Unfortunately, this doesn't work. Unless the time is exactly 09:34:00, the total volume (for the first 5 minutes of the opening range) will display as zero. This error makes sense, since the IF statement is basically computing the sum at that exact time, else it's returning an empty cell.

Is there a way to store the sum in some variable after it's computed, so that I can display it any time of the day?
Date Time Of Last Edit: 2021-09-16 05:31:04
[2021-09-16 07:40:39]
User907968 - Posts: 583
This error makes sense, since the IF statement is basically computing the sum at that exact time, else it's returning an empty cell.

No, if your study ID is set correctly in the formula, it will only return 0 up until the volume is first calculated, after which it will return the last calculated volume, i.e. the calculated volume is copied from the preceding index.

What is the study ID of your Spreadsheet Formula, this need to be inserted in place of 'ID1' (if not ID1).

=IF(BARTIME=TIME(09,34,00),SUM(V[0:-4]),ID1.SG1[-1])
[2021-09-16 12:12:24]
tomgilb - Posts: 2989
Unless the time is exactly 09:34:00, the total volume (for the first 5 minutes of the opening range) will display as zero.
True.

The formula in post #2 assumes you are using time-based bars of 1 min, and that ID1 would be edited to the ID# of the Spreadsheet Formula study you have added.
Since a bar's timestamp is at the Open, the 09:34:00 bar is the 5th bar since the Open.
If the formula locates the 09:34:00 bar, it sums the Volume of itself and of the 4 bars previous.
If the formula cannot locate the 09:34:00 bar, it returns 0.
The formula will make the value persistent until it can locate 09:34:00 again by repeating its previous value: ID1.SG1[-1]

The formula worked for me when I tested it, but it's possible there is floating point imprecision in the BARTIME value, so the formula cannot find the exact 09:34:00 time.
Try this formula:
=IF(MROUND(BARTIME,1/86400000)=TIME(09,34,00,000),SUM(V[0:-4]),ID1.SG1[-1])
This rounds the BARTIME to the millisecond and compares it to the TIME to the millisecond.

https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#UsingDateTime
https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#MROUND_Function
https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#TIME_Function
Date Time Of Last Edit: 2021-09-16 12:31:15

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

Login

Login Page - Create Account