Sierra Chart Worksheet System For Trading/Alert Discussions

September 7th, 2020, 06:32 PM

tomgilb

Use INTDATE instead of DATEVALUE.
And use an absolute reference to the Sheet2 cell. (You won't need ROW()=3.)

=AND(DATEVALUE($J$41)=DATEVALUE(Sheet2!$A$1),FRACTIME($J$41)>=FRACTIME(Sheet2!$A$1),FRACTIME($J$41)<FRACTIME(Sheet2!$A$1)+5/86400)

You could use this shorter version instead:
=AND($J$41=Sheet2!$A$1,$J$41>=Sheet2!$A$1+5/86400)
You don't need to remove the Time with INTDATE, and you don't need to remove the DATE with FRACTIME.

Also, I'd rename the blank sheet to something other than the default Sheet2, because that is the default name associated with Chart2, should this spreadsheet study ever be added to Chart2. Just to be safe, because it would be overwritten.

Thanks again Tom, got it working.

September 7th, 2020, 07:19 PM

tomgilb

Use INTDATE instead of DATEVALUE.
And use an absolute reference to the Sheet2 cell. (You won't need ROW()=3.)

=AND(DATEVALUE($J$41)=DATEVALUE(Sheet2!$A$1),FRACTIME($J$41)>=FRACTIME(Sheet2!$A$1),FRACTIME($J$41)<FRACTIME(Sheet2!$A$1)+5/86400)

You could use this shorter version instead:
=AND($J$41=Sheet2!$A$1,$J$41>=Sheet2!$A$1+5/86400)
You don't need to remove the Time with INTDATE, and you don't need to remove the DATE with FRACTIME.

Also, I'd rename the blank sheet to something other than the default Sheet2, because that is the default name associated with Chart2, should this spreadsheet study ever be added to Chart2. Just to be safe, because it would be overwritten.

Hi Tom, sorry to bother you again on this. I thought I had everything working but when testing further I think I'm still mis-understanding how the Spreadsheet System works and I haven't been able to find anything in the documentation that clarifies.

In my Sheet2 (re-named to EntryCriteria) tab I want to eventually have a list of 1000 separate date/time combinations for the backtest to run on. The formula I currently have in K3 for my Buy Entry references this list. When I run my backtest it's only executing on the first two date/times in my EntryCriteria list.

Does the Study not iterate through the lines? For example, looks at K3, executes, moves to K4, executes, moves to K5, executes... to K1002? Or would doing what I'm trying to do require me to have an OR formula in K3 that references each of the 1000 lines within my EntryCriteria tab (ie. =OR($J$41=EntryCriteria!$A$1,$J$41=EntryCriteria!$A$2,$J$41=EntryCriteria!$A$3,...$J$41=EntryCriteria!$A$1000)?

September 7th, 2020, 10:17 PM

Quoting

I think I'm still mis-understanding how the Spreadsheet System works

Signals are only generated in row 3 for intrabar entries, or row 4 for close of bar entries. This choice is with the setting 'Signal Only On Bar Close (K,M)'.
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php

Quoting

Does the Study not iterate through the lines?

To make the study iterate thru each row on the EntryCriteria sheet, try this:

Use column H on Sheet1:
- In cell H1: =AND(J41>=EntryCriteria!A1,J41<EntryCriteria!A1+5/86400)
- In cell H2: =AND(J41>=EntryCriteria!A2,J41<EntryCriteria!A2+5/86400)
-repeat for each row in column A on the EntryCriteria sheet.
On Sheet1 cell K3:
=OR($H$1,$H$2,$H$3,$H$4,...[repeat for each row in column H] )
Note: I did not test this so I'm not sure how efficient this will be, or if it will cause some lag, especially with 1000 rows.

If you are using Attached Orders and are expecting each target and stop to be associated with its entry, you'll be disappointed. See this thread:
https://www.sierrachart.com/SupportBoard.php?ThreadID=55800

September 7th, 2020, 11:31 PM

tomgilb

Signals are only generated in row 3 for intrabar entries, or row 4 for close of bar entries. This choice is with the setting 'Signal Only On Bar Close (K,M)'.
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php

To make the study iterate thru each row on the EntryCriteria sheet, try this:

Use column H on Sheet1:
- In cell H1: =AND(J41>=EntryCriteria!A1,J41<EntryCriteria!A1+5/86400)
- In cell H2: =AND(J41>=EntryCriteria!A2,J41<EntryCriteria!A2+5/86400)
-repeat for each row in column A on the EntryCriteria sheet.
On Sheet1 cell K3:
=OR($H$1,$H$2,$H$3,$H$4,...[repeat for each row in column H] )
Note: I did not test this so I'm not sure how efficient this will be, or if it will cause some lag, especially with 1000 rows.

If you are using Attached Orders and are expecting each target and stop to be associated with its entry, you'll be disappointed. See this thread:
https://www.sierrachart.com/SupportBoard.php?ThreadID=55800

Makes sense now why it wasn't working for me, thank you for the clarification.

I was hoping to expand this to have unique stops/targets attached to the individual buy/sell orders based on volatility conditions that I've calculated so I don't think this is going to work for me.

Thanks again for all your help with this Tom, I really appreciate it and your insight has saved me hours of bungling around. I'll have to see if there's another software solution out there for what I'm trying to do.

September 11th, 2020, 12:37 AM

Another time related question, Tom
I was using the actual FRACTIME second value, after division, for formulas, like the previous post, and it was working until recently (and still on version 2150)
...you know .000012

Here what was working;
=FRACTIME($J$41)<(FRACTIME(AW3)+(($J$36/2)*.000012))

It would be true until half-way through bar.
And, just confirming, L,N-Z Signal on bar close is No in the speadsheet study.

Is there better way to do this?

September 11th, 2020, 05:19 PM

Quoting

Is there better way to do this?

Sorry, I'm not understanding what you want to do.
What is in AW3?
What is 0.000012 ?

September 12th, 2020, 02:08 AM

Those were terms used unsuccessfully for a formula that was true until half-way through bar.
Using $J$36/2 would probably be the only thing kept in a solution you may suggest.
sorry for the confusion, Tom

September 14th, 2020, 08:34 PM

It was the AW3, it works if corrected to A3.
Hadn't noticed it became transposed when moved from another spreadsheet.
Thanks for the added perspective, Tom, you are welcome to simplify if there is another solution.

October 8th, 2020, 04:47 AM

Hi again Tom,

I think the following makes sense

Using sum e.g
= SUM( AB3:AB10 )
an entry candidate is being "kept alive" for 7 bars.

Is there something you have to suggest to substitute for the 10 in that formula (like a variable),
so then a value in the H column can increase or decrease that number of bars

October 8th, 2020, 09:32 AM

Quoting

Is there something you have to suggest to substitute for the 10 in that formula (like a variable),
so then a value in the H column can increase or decrease that number of bars

Use something like this:
=SUM(AB3:OFFSET(AB3,$H$1,0))
- where H1 is 7.

This will give the same result as =SUM(AB3:AB10)

Note: The OFFSET function does not include its starting cell in the offset count.

Sierra Chart Worksheet System For Trading/Alert Discussions

Discussion in Sierra Chart

Sierra Chart Worksheet System For Trading/Alert Discussions