Login Page - Create Account

Support Board


Date/Time: Wed, 21 Aug 2019 08:57:37 +0000



Automated Trading

Support Request:
[2019-01-02 00:12:02]
User575872 - Posts: 33
Hello, I have been a customer of Sierrachart since December and I am very satisfied with the software. I am currently experimenting with spreadsheet-auto-trading. My question: Is it possible to trigger an order at a certain time? I did not get any further with the "time-line" study. I am also not an excel professional ....

Thank you for an answer.
regards
[2019-01-02 16:43:44]
John - SC Support - Posts: 1185
There are some examples of how to use Dates and Times related to Spreadsheet Trading here:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#TradingFormulas

We are unable to help beyond pointing you to this documentation, therefore we have marked this thread as Programming Help. There are other users that may be able to help you further with your questions.
[2019-01-02 18:19:59]
tomgilb - Posts: 2221
Is it possible to trigger an order at a certain time?

Yes, if you are using the Spreadsheet System for Trading study.

You would need to use the FRACTIME and NOW() functions and compare it to a time value window, like in these examples:
=AND(FRACTIME(NOW())>=TIMEVALUE("09:30:00"),FRACTIME(NOW())<TIMEVALUE("09:30:02"))
=AND(FRACTIME(NOW())>=$J$75,FRACTIME(NOW())<$J$75+2/86400)

You may need to make the time window wider than 2 seconds.

J75 is the chart's Session Start Time.
2/86400 is equal to 2 seconds of Serial Date Time Value.


These formulas would go in row 3 of columns K or M. Other entry conditions could be included.

These are the available spreadsheet functions:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html
[2019-01-03 10:20:43]
User575872 - Posts: 33
Hello, thank you very much for the tips! I tried it out right away, the system also responded but put a buy entry on every bar. Is there another clue? Sorry, I'm a newcomer to Excel and looking for ways to handle automatic trading...
Date Time Of Last Edit: 2019-01-03 10:20:57
[2019-01-03 14:35:05]
tomgilb - Posts: 2221
To limit the visual to only the entry bar, add a reference to row 3, like this:

=AND(FRACTIME(NOW())>=TIMEVALUE("09:30:00"),FRACTIME(NOW())<TIMEVALUE("09:30:02"),ROW()=3)
[2019-01-04 19:17:10]
User575872 - Posts: 33
=AND(FRACTIME(NOW())>=TIMEVALUE("09:30:00"),FRACTIME(NOW())<TIMEVALUE("09:30:02"))
=AND(FRACTIME(NOW())>=$J$75,FRACTIME(NOW())<$J$75+2/86400)=AND(FRACTIME(NOW())>=TIMEVALUE("09:30:00"),FRACTIME(NOW())<TIMEVALUE("09:30:02"),ROW()=3)
=ID0.SG4@3:ID0.SG4@4

Hi, what's wrong with this command? I put it in row K 3. The system should make a long entry at time 9:30. The long entry is coming, but not on the current chart value and not at the scheduled time …..
[2019-01-04 22:49:38]
tomgilb - Posts: 2221
I gave two examples in post #3. Use one or the other.

The two examples assume you want to enter exactly at 09:30:00, and in order to do that, use these spreadsheet study settings:
- 'Signal Only On Bar Close' set to No
- 'Signal Only Once Per Bar' set to Yes.
This will allow only the first intrabar entry signal to occur.

If you are placing a Limit or Stop entry order, use a formula in cell J22 that returns the price, and set the correct order type in cell J71.
[2019-01-06 14:24:35]
User575872 - Posts: 33
I set:
'Signal Only Once Per Bar' to Yes.
'Signal Only On Bar Close' to No

I put:
=AND(FRACTIME(NOW())>=TIMEVALUE("09:30:00"),FRACTIME(NOW())<TIMEVALUE("09:30:02")) in cell K3

In cell J22 a "0" ( I think it means the actual contract-price)
In cell J71 a "S" for Stop Buy
In cell J75 a "09:00:00"

However, no order is triggered in the replay mode. Where is my mistake? ... or maybe ... are my mistakes? :-)
[2019-01-06 14:51:22]
tomgilb - Posts: 2221
You can't use the NOW() function in replay because now is now, not a time in the past.

Use this instead:
=AND(FRACTIME($J$41)>=TIMEVALUE("09:30:00"),FRACTIME($J$41)<TIMEVALUE("09:30:02"))

J41 updates at each chart update, which is not necessarily as frequent as NOW(). This may cause entries to be slightly late, so you may need to widen the time window beyond 2 seconds.
[2019-01-06 19:04:39]
User575872 - Posts: 33
Thank you, it works and I can start working. I'm just looking for a way to enter multiple times in the system.
It's all pretty complex and my little Excel skills are complemented by my bad English ....
But it is very interesting and I just have to take small steps.
[2019-01-08 18:33:37]
User575872 - Posts: 33
Sorry, I do not find the answers I'm looking for in the documentation. For example, I would like to enter more time values in the formula below, for example: ("10:00:00"). If I do that and separate them with a comma or semicolon an error message appears. Or: If I copy the formula from cell k2 (buy entry) in cell O2 for example it doesn´t work too. What can I do?

=AND(FRACTIME($J$41)>=TIMEVALUE("09:00:00"),FRACTIME($J$41)<TIMEVALUE("09:00:06"))
[2019-01-08 19:15:00]
tomgilb - Posts: 2221
Combine them with the OR function:

=OR(AND(FRACTIME($J$41)>=TIMEVALUE("09:00:00"),FRACTIME($J$41)<TIMEVALUE("09:00:06")),AND(FRACTIME($J$41)>=TIMEVALUE("10:00:00"),FRACTIME($J$41)<TIMEVALUE("10:00:06")))

In the Spreadsheet System for Trading study, only columns K-N are entry/exit columns. Other Formulas Columns O-Z...BR are for intermediate calculations, or other uses.

When you copy/paste a formula from one cell to another, the references will auto-compensate for the move, unless the references are made absolute with a preceding $. So to avoid this auto-compensation, you'll need to double click in the source cell, highlight the formula, Ctrl-C, double click in the destination cell, Ctrl-V.
Date Time Of Last Edit: 2019-01-09 19:30:55
[2019-01-08 20:09:01]
User575872 - Posts: 33
Fine! That helps me a lot! Thank you
[2019-01-09 19:14:16]
User575872 - Posts: 33
=OR(AND(FRACTIME($J$41)>=TIMEVALUE("09:00:00"),FRACTIME($J$41)<TIMEVALUE("09:00:06")),
=AND(FRACTIME($J$41)>=TIMEVALUE("10:00:00"),FRACTIME($J$41)<TIMEVALUE("10:00:06")))

Too soon looked forward to. If I use these formulas the error message "Syntax" comes.
[2019-01-09 19:32:49]
tomgilb - Posts: 2221
Sorry.
Try again, using the edited formula in post #12.
[2019-01-11 09:15:29]
User575872 - Posts: 33
Well, thank you, the triggering works for the second time. My idea then simply copy that for the third and other time values, does not work. What do I have to adjust for it?

=OR(AND(FRACTIME($J$41)>=TIMEVALUE("09:00:00"),FRACTIME($J$41)<TIMEVALUE("09:00:06")),AND(FRACTIME($J$41)>=TIMEVALUE("10:00:00"),FRACTIME($J$41)<TIMEVALUE("10:00:06")),AND(FRACTIME($J$41)>=TIMEVALUE("11:00:00"),FRACTIME($J$41)<TIMEVALUE("11:00:06")))
[2019-01-11 15:59:37]
tomgilb - Posts: 2221
The formula in post #16 does not return any errors for me. What is not working for you?

If you have many entry times, you could organize them in column H, then reference them in K3, like this:

H1:
=AND(FRACTIME(J41)>=TIMEVALUE("09:00:00"),FRACTIME(J41)<TIMEVALUE("09:00:06"))
H2:
=AND(FRACTIME(J41)>=TIMEVALUE("10:00:00"),FRACTIME(J41)<TIMEVALUE("10:00:06"))
H3:
=AND(FRACTIME(J41)>=TIMEVALUE("11:00:00"),FRACTIME(J41)<TIMEVALUE("11:00:06"))
K3:
=OR($H$1,$H$2,$H$3)
[2019-01-14 15:31:28]
User575872 - Posts: 33
The formulas in post # 17 work great. Many Thanks!
I still have two questions ...
Can I automatically cancel an active order after a certain period of time? So, for example after 30 minutes without fill?

Second question: Is it possible to trigger an order via volume? For example: If the volume of the current bar is twice the average of the last ten bars, then trigger an order.
[2019-01-14 17:30:24]
tomgilb - Posts: 2221
1. Yes. There is an example on this page:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php

2. Yes. The entry formulas in K3/M3 can contain any set of conditions. Volume is in column F, or you can add the Volume study. You can base a moving average study on either, or you can create a moving average formula on the spreadsheet; see above link for an example. Then write a formula to compare the current volume to the average volume.
[2019-01-15 08:49:08]
User575872 - Posts: 33
I found this but moved H4 to H10 (because H4 is already occupied by my time commands). The order is briefly activated and then disappears immediately. Where is my mistake?

Cell Y3: =IF(AND (K5 = 0, K4), FRACTIME(A3), Y4)
This formula returns the closing timestamp of a Buy Entry signal in Column K.

Cell Z3: =IF(AND(M5 = 0, M4), FRACTIME(A3), Z4)
This formula returns the closing timestamp of a Sell Entry signal in Column M.

Cell H4: 3
This is the number of minutes to wait before canceling an unfilled working order.

Cell J27: =OR(AND(J4 > 0, FRACTIME(J41) - Y3 >= H4 / 1440), AND(J4 < 0, FRACTIME(J41) - Z3 >= H4 / 1440))
This formula will cancel an unfilled working order 3 minutes after the close of the entry bar. If bars are not time-based, it can occur intrabar.
[2019-01-15 16:04:19]
tomgilb - Posts: 2221
It is assumed that entries are Limit or Stop, and that cells J71/J73 are set correctly, and that cells J22/J24 contain a formula that returns the entry price.
[2019-01-16 08:59:40]
User575872 - Posts: 33
Yes I set J71/J73 on S and use a formula (=E3+0.03) for J22/J24 to get a offset. It works in the sense that all orders are actually canceled except the one filled.

I wonder why in these formulas of K5 and K4 is the speech and not by K3

Cell Y3: =IF(AND (K5 = 0, K4), FRACTIME(A3), Y4)
This formula returns the closing timestamp of a Buy Entry signal in Column K.

Cell Z3: =IF(AND(M5 = 0, M4), FRACTIME(A3), Z4)
This formula returns the closing timestamp of a Sell Entry signal in Column M.


Sorry I have another question:
How can I say that I want to trigger an order depending on yesterday's high and low?
[2019-01-16 09:09:44]
User575872 - Posts: 33
Additional problem to post # 21: Really all unfilled orders will be canceled, including the stop order and limit order from the (filled) bracket order ...hmmm…. I ihink thats my problem … :-)
[2019-01-16 14:23:28]
tomgilb - Posts: 2221
I wonder why in these formulas of K5 and K4 is the speech and not by K3
The formulas assume that entries are at close of bar, so it finds the opening timestamp of the next bar.

How can I say that I want to trigger an order depending on yesterday's high and low?
It would be possible to calculate the H/L using a few Formula Columns, or you could add the Daily OHLC study and reference its outputs.

Really all unfilled orders will be canceled, including the stop order and limit order from the (filled) bracket order
Use cell J78 instead of J27.
[2019-01-22 14:44:31]
User575872 - Posts: 33
Thank you very much, I have a very good basis for experimenting. But the structure is not so clear to me that I can help myself completely.

Some things work very well, others do not. For example, the command for Cell J89 "Cancel order at 11:00:00" does not work for me at all.

Also different entries (post # 17) are not executed. I enlarged the list to seven times. The following happens: Time entries 1 and 2 are executed, 3 and 4 are not, 5, 6 and 7 are executed again. Here too, canceling the non-executed orders does not work (via: Cell J27: = OR (AND (J4> 0, FRACTIME (J41) - Y3> = H4 / 1440), AND (J4 <0, FRACTIME (J41) - Z3 > = H4 / 1440)).
   
Another timed entry question: Is there a formula for regular entry, for example every ten minutes?

For my question from post # 18 ("Second question: Is it possible to trigger an order via volume?") I found the following formula: = AVERAGE (SG1 [0: -10)]> 100
But I do not know how to assign this formula to the volume.
[2019-01-23 05:29:36]
tomgilb - Posts: 2221
the command for Cell J89 "Cancel order at 11:00:00" does not work for me at all.
You must enable this with a =1 in J88.

The following happens: Time entries 1 and 2 are executed, 3 and 4 are not, 5, 6 and 7 are executed again
Some possible causes:
- You have 'Allow Multiple Entries In Same Direction' or 'Allow Entry With Working Orders' set to No.
- J41 is not updated within the time window in your formula.
- K4 or M4 are 1 and there is no transition from 0. (You could set 'Reset Condition On New Bar' to Yes)

canceling the non-executed orders does not work
You must use J78 instead of J27 for this.

Is there a formula for regular entry, for example every ten minutes?
The formulas you are using should work. But here is another way if your signal chart is time based with a shorter evenly divisible time than 10 minutes:
Add a 10 min chart, and overlay the Main Price Graph on your signal chart using the Study/Price Overlay study, with the 'Fill Blanks With Last Value' set to No. Then include this in your entry formula:
=ID1.SG1@3>0
where ID1 is the Study/Price Overlay study; edit as needed.

Is it possible to trigger an order via volume?") I found the following formula: = AVERAGE (SG1 [0: -10)]> 100
That formula is using the Simple Alert syntax. The syntax for spreadsheets would be:
=AVERAGE(F3:F13)>100
[2019-02-11 17:01:00]
User575872 - Posts: 33
Most of the automatic trading is going very well. But there are still some questions …

1. How do I tell the spreadsheet system that I want yesterday's "Daily High & Lows", rather than today's?

2. For example, if I want to place an order every day at 9:00 am, it will not run every day even though the order validity is one day, which will not fail because of too many open orders or similar errors.

3. In "Replay Mode" I currently have the problem that it looks like the backtest is running well, but in the "trade activity log", for example, it only shows the trades for a week, even though the test was over two months… ("single Chart, Accurate Trading System..., use start date-time checked, Speed 100.000, skip empty periods checked").
[2019-02-11 17:22:01]
tomgilb - Posts: 2221
1. How do I tell the spreadsheet system that I want yesterday's "Daily High & Lows", rather than today's?
You can add the Daily OHLC study, with 'Reference Days Back' set to 1, and reference its outputs with your formulas.

2. For example, if I want to place an order every day at 9:00 am, it will not run every day even though the order validity is one day, which will not fail because of too many open orders or similar errors.
I don't understand this. Please explain.

3. In "Replay Mode" I currently have the problem that it looks like the backtest is running well, but in the "trade activity log", for example, it only shows the trades for a week, even though the test was over two months…
- Go to Trade >> General Trade Settings and verify the 'Number of Days to Save in Trade Activity Log' is sufficient.
- In the Trade Activity Log, verify that the Date Range 'From' date is early enough.
[2019-02-11 18:21:36]
User575872 - Posts: 33
1. "... and reference its outputs with your formulas…"

Okay, how do I do that exactly? About J17 and J18 and K3 and M3?


2. Example: Every morning at 9 o'clock the system should trigger a buy and a sell entry. It works maybe ten times (days), then five times not, then three times well. The offsets for the entries are so small that it can not be that the order is not filled (3 Ticks).


3. Yes, I changed that. It also had something to do with "maximum position allowed" in spreadsheet settings.
[2019-02-11 20:43:27]
tomgilb - Posts: 2221
1. "... and reference its outputs with your formulas…" Okay, how do I do that exactly?
A study's outputs appear on the spreadsheet beginning in columns AA to BR, depending on the Number Of Formula Columns setting.
You can reference them using the column/row but the preferred way is to use this method:
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php#ReferencesStudySubgraphColumnsSpreadsheetStudy

2. Example: Every morning at 9 o'clock the system should trigger a buy and a sell entry. It works maybe ten times (days), then five times not, then three times well. The offsets for the entries are so small that it can not be that the order is not filled (3 Ticks).
At a replay speed of 100, the orders might not even get placed. Locate a specific day when there is no entry, and replay thru it at a slower speed.
[2019-02-12 17:00:20]
User575872 - Posts: 33
Hi, for "1" in post #30 I need some time …
so first this question… How can I tell the trading system that, for example, starting at 8 am, the highs and lows of the first 15-minute bar should ALWAYS be used for the entry buy or entry sell?
[2019-02-12 17:53:19]
tomgilb - Posts: 2221
How can I tell the trading system that, for example, starting at 8 am, the highs and lows of the first 15-minute bar should ALWAYS be used for the entry buy or entry sell?
Create persistent variables of the High and Low of the 8am 15min bar, using a Formula Column for each, with formulas like this:
e.g. cell S3 for the High:
=IF(AND(FRACTIME(A4)<TIMEVALUE("08:00:00"),FRACTIME(A3)>=TIMEVALUE("08:00:00")),C3,S4)
e.g. cell T3 for the Low:
=IF(AND(FRACTIME(A4)<TIMEVALUE("08:00:00"),FRACTIME(A3)>=TIMEVALUE("08:00:00")),D3,T4)

Then include a reference to these values in your entry conditions.

There are other examples of persistent variable formulas here:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php
[2019-02-14 18:36:25]
User575872 - Posts: 33
from post #32:

"Then include a reference to these values in your entry conditions."


Sorry, I don´t know what I have to do exactly
[2019-02-14 19:29:05]
tomgilb - Posts: 2221
With the formulas in post #32, the High and Low of the 8am 15min bar will be retained all day, so reference them instead of any other highs or lows.
[2019-02-14 19:49:08]
User575872 - Posts: 33
….I think its not sufficient, if I write "=S3" in K3 and "=T3" in M3 ….
I have a basic understanding-problem with the term "reference"
[2019-02-14 20:19:27]
tomgilb - Posts: 2221
the highs and lows of the first 15-minute bar should ALWAYS be used for the entry buy or entry sell?
What are your entry conditions/rules regarding the High and Low of the first 15-minute bar?
I can only assume that you'd compare current price to these values.
[2019-02-14 20:43:58]
User575872 - Posts: 33
Yes, but it is also important from where the course comes. Buy entry should only be triggered if the price was below the 15-min high, sell entry if the price was above the low.
[2019-02-14 23:00:58]
tomgilb - Posts: 2221
Buy entry should only be triggered if the price was below the 15-min high, sell entry if the price was above the low.
Then include something like this in your entry formulas:
K3:
E3<S3
M3:
E3>T3
[2019-02-18 17:54:40]
User575872 - Posts: 33
# 30: I have now entered the formulas =AC3<=(E3+2) in K3 and =AB3<=(E3-2) in M3 and it works so far … (to trigger limitorders on the high and the low of the previous day).

I would like to add conditions:
- the order should only be triggered if the opening is in the range high / low of the previous day
- that only on the first arrival of the condition (once low, once high) is triggered.

I noticed that the function "Flatten positions & cancel orders in time" (J88, J89) gets from (my) system prio 1. So stops or limits are disregarded when I activate this feature. Can I change that?
[2019-02-18 23:37:58]
tomgilb - Posts: 2221
I would like to add conditions:
- the order should only be triggered if the opening is in the range high / low of the previous day
Add conditions to the formulas:
K3:
=AND(AC3<=E3+2,opening<rangehigh,opening>rangelow)
M3:
=AND(=AB3<=E3-2,opening<rangehigh,opening>rangelow)

- that only on the first arrival of the condition (once low, once high) is triggered.
Put the entry formulas in a Formula Column where you can create a persistent variable of the entry, then in K3/M3 find the transitions. Here is an example:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#BuyOnFirstSignalAndIgnoreMultipleSignals

I noticed that the function "Flatten positions & cancel orders in time" (J88, J89) gets from (my) system prio 1.
Don't use J85 to J89. Instead, do this:
-Use a formula like this in J28:
=AND(J8 = 0, OR(FRACTIME(J41) < TIMEVALUE("09:30:00"), FRACTIME(J41) > TIMEVALUE("16:00:00")))
This will allow exits/flattening after autotrading entries are disabled.
-Then use a formula like this in J29:
=FRACTIME(J41) > TIMEVALUE("16:14:00")
The time in J29 should be later than the end time in J28.
[2019-02-19 11:06:01]
User575872 - Posts: 33
#40
okay, I put =AND(AC3<=E3+2,AA3<AB3,AA3>AC3) in K3 and =AND(AB3<=E3-2,AA3<AB3,AA3>AC3) in M3

and =IF(=AND(AC3<=E3+2,AA3<AB3,AA3>AC3, 1, IF(=AND(AB3<=E3-2,AA3<AB3,AA3>AC3, 0, P4)) in P3

because of this info in the support board
Cell P3: =IF(BuyConditions, 1, IF(SellConditions, 0, P4))
In the above formula BuyConditions would be replaced with a desired Buy formula, and the SellConditions would be replaced with a desired Sell formula.

But there comes a ERROR SYNTAX

Also when I use the other info from the support board:

Cell K3: =AND(P4 = 0, P3)
Cell M3: =AND(P4, P3 = 0)

I don´t know where and how I have to place these formulas. I added them to the buy/sell formulas in K3 and M3 and used them alone ... both wrong
[2019-02-19 12:35:44]
jenda3 - Posts: 20
I would like to go back to that:

1. How do I tell the spreadsheet system that I want yesterday's "Daily High & Lows" rather than today's?
You can add the Daily OHLC study, with 'Reference Days Back' set to 1, and reference its outputs with your formulas.

I need something like this. I added Daily OHLC study, it works as I need but when i run back test with thist study it's very very very slow. For example, without this study, back test to 30 days, takes about 10 minutes and with Daily OHLC study, takes about 3 hours. And I need a back test for one year.

I tried to use the Daily High Cell J17 and Daily Low Cell J18. But they are running value and do not reset at the beginning of the day

So can I calculate Daily High and Daily Low for the previous day somehow differently?

Thanks
[2019-02-19 14:36:35]
tomgilb - Posts: 2221
I don´t know where and how I have to place these formulas.
Remove the = before AND.

P3:
=IF(AND(AC3<=E3+2,AA3<AB3,AA3>AC3), 1, IF(AND(AB3<=E3-2,AA3<AB3,AA3>AC3), 0, P4))
K3:
=AND(P4 = 0, P3)
M3:
=AND(P4, P3 = 0)
Date Time Of Last Edit: 2019-02-19 14:47:31
[2019-02-19 15:35:15]
tomgilb - Posts: 2221
I added Daily OHLC study, it works as I need but when i run back test with this study it's very very very slow.
There is nothing inefficient about the Daily OHLC study. It must be something else. Here's a possibility:
https://www.sierrachart.com/index.php?page=doc/ChartStudies.html#StudyCalculationPrecedence

So can I calculate Daily High and Daily Low for the previous day somehow differently?
You could use the High/Low for Time Period - Extended study.
This wouldn't fix an unrelated inefficiency problem.

You could calculate it on the spreadsheet:
O3:
=IF(INT(A3)>INT(A4),C3,MAX(C3,O4))
current day's midnight-to-midnight high
P3:
=IF(INT(A3)>INT(A4),O4,P4)
previous day's midnight-to-midnight high
Q3:
=IF(INT(A3)>INT(A4),D3,MIN(D3,Q4))
current day's midnight-to-midnight low
R3:
=IF(INT(A3)>INT(A4),Q4,R4)
previous day's midnight-to-midnight low
[2019-02-19 17:59:08]
User575872 - Posts: 33
#43
I removed the = before AND.

P3:
=IF(AND(AC3<=E3+2,AA3<AB3,AA3>AC3), 1, IF(AND(AB3<=E3-2,AA3<AB3,AA3>AC3), 0, P4))

...and I did this:

K3:
=AND(P4 = 0, P3)
M3:
=AND(P4, P3 = 0)

..but no order is triggered (I disabled J28/J29 for this attempt) ... I think I have to do a little more…..
[2019-02-24 19:05:34]
User575872 - Posts: 33
#43: I have activated the studies Spreadsheet for trading and Daily OHLC (reference days back 1) and used the following formulas:

P3:
=IF(AND(AC3<=E3+2,AA3<AB3,AA3>AC3), 1, IF(AND(AB3<=E3-2,AA3<AB3,AA3>AC3), 0, P4))
K3:
=AND(P4 = 0, P3)
M3:
=AND(P4, P3 = 0)

Not a single order is triggered. I can not find the mistake.
[2019-02-24 19:17:24]
tomgilb - Posts: 2221
What studies are AA, AB and AC?
Date Time Of Last Edit: 2019-02-24 19:18:08
[2019-02-24 19:34:37]
User575872 - Posts: 33
I think these are the references of the OHLC study. Maybe I misunderstood your post #43
[2019-02-24 21:37:22]
tomgilb - Posts: 2221
I think these are the references of the OHLC study
Post 43 is merely using your example references.
You need to be sure what these are actually referencing.
[2019-02-26 12:04:22]
User575872 - Posts: 33
I think I caused confusion. I wanted to design a spreadsheet system with the following parameters:
Buy entry if low previous day is reached plus 2 ticks
Sell entry if high previous day is reached minus 2 ticks.
Date Time Of Last Edit: 2019-02-26 12:25:01
[2019-02-26 14:07:37]
tomgilb - Posts: 2221
Not a single order is triggered. I can not find the mistake.
Ticksize is found in J21. You'll need to reference it.

Assuming the Daily OHLC study is at the top of the Studies to Graph list,
and that its 'Reference Days Back' is set to 1,
and you want yesterday's Open to be between yesterday's High and Low, but not equal to either,
and you want to trigger an entry if the current Last price is within 2 ticks of yesterday's High or Low,
and you only want the first occurrence of either trade direction,
and you always want to alternate longs and shorts:

P3:
=IF(AND(E3>=AB3-2*$J$21,AA3<AB3,AA3>AC3), 1, IF(AND(E3<=AC3+2*$J$21,AA3<AB3,AA3>AC3), 0, P4))
K3:
=AND(P4 = 0, P3)
M3:
=AND(P4, P3 = 0)

Note: this is an example of the necessity for specificity.
Date Time Of Last Edit: 2019-03-06 21:51:33
[2019-02-27 14:15:38]
tomgilb - Posts: 2221
Formula in post 51 has been corrected.
[2019-03-01 17:36:10]
User575872 - Posts: 33
#51 works great. Many Thanks!

Now I have tried to tell the system the following formulas:
Trigger a sell order if the difference between the high and low of the last bar is greater than 0.06 ticks and C3 is less than B3.
Trigger a buy order if the difference between the high and low of the last bar is greater than 0.06 ticks and D3 is less than B3.

=(C3-D3>0.06)=AND (C3<B3)

=(C3-D3>0.06)=AND (C3>B3)

Is triggered almost on every candle and I do not know why.
[2019-03-01 20:59:09]
tomgilb - Posts: 2221
The formula is properly written as:
=AND(C3-D3>0.06,C3<B3)
and says:
the current bar's range is greater than 0.06 points (not ticks),
and the current bar's high is less than the current bar's open.

If you want to reference ticks instead of points, you must reference the tick size in J21:
=AND(C3-D3>6*$J$21,C3<B3)
[2019-03-06 19:39:57]
User575872 - Posts: 33
1. How can I tell the system that the formulas from #51 are only used when the High and Low from "Reference Days Back 1" are in the range of "Reference Days Back 2"?

2. Why is it that using the formula from # 51 at this point in the chart does not trigger an order? (see attached pictures)
image20190305_184217_001.jpg / V - Attached On 2019-03-06 19:37:26 UTC - Size: 5.44 MB - 109 views
imageInked20190305_184029_001_LI.jpg / V - Attached On 2019-03-06 19:37:52 UTC - Size: 2.9 MB - 120 views
[2019-03-06 19:52:35]
tomgilb - Posts: 2221
1. Please tell me the ID#s of both instances of the Daily OHLC study.

2. Swap the shorts and longs in the P3 formula:
P3:
=IF(AND(E3>=AB3-2*$J$21,AA3<AB3,AA3>AC3), 1, IF(AND(E3<=AC3+2*$J$21,AA3<AB3,AA3>AC3), 0, P4))
Date Time Of Last Edit: 2019-03-06 21:52:32
[2019-03-06 20:33:02]
User575872 - Posts: 33
Daily OHLC study Ref. Days Back 1 ID:2

Daily OHLC study Ref. Days Back 2 ID:3
[2019-03-06 23:50:00]
tomgilb - Posts: 2221
When referencing study outputs, you really should use this method:
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php#ReferencesStudySubgraphColumnsSpreadsheetStudy

So the formula in post #56 would look like this:
P3:
=IF(AND(E3>=ID2.SG2@3-2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3),1,IF(AND(E3<=ID2.SG3@3+2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3),0,P4))
Reasons for using this format:
- You can insert/delete Formula Columns on the spreadsheet while retaining the correct references.
- You can add/remove/rearrange studies in the Studies to Graph list while retaining the correct references.
- You can increase/decrease the Number of Formula Columns while retaining the correct references.

1. Try this:
- Don't use the P3 formula.
Q3:
=IF(AND(ID2.SG2@3<ID3.SG2@4,ID2.SG3@3>ID3.SG3@4),1,IF(OR(ID2.SG2@3>ID3.SG2@4,ID2.SG3@3<ID3.SG3@4),0,Q4))
R3:
=AND(Q3,E3>=ID2.SG2@3-2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3)
S3:
=IF(AND(Q3,R3),1,IF(OR(Q3=0,AND(E3<=ID2.SG3@3+2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3)),0,S4))
T3:
=AND(Q3,E3<=ID2.SG3@3+2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3)
U3:
=IF(AND(Q3,T3),1,IF(OR(Q3=0,AND(E3>=ID2.SG2@3-2*$J$21,ID2.SG1@3<ID2.SG2@3,ID2.SG1@3>ID2.SG3@3)),0,U4))
K3:
=AND(S3,S4=0)
M3:
=AND(U3,U4=0)

I don't mind helping out with basic questions but the answers to your requests are becoming more complex than a forum post can accommodate.
[2019-03-07 14:39:27]
User212764 - Posts: 161
I don't mind helping out with basic questions but the answers to your requests are becoming more complex than a forum post can accommodate.

Tom, the OP should be paying you for a consultation on this, since this is what you do for a living.

https://www.sawtoothtrade.com/services.html
[2019-03-08 19:55:35]
User575872 - Posts: 33
Sorry, I did not know that this question required such an extensive answer. Irrespective of that, I am very happy for the support
[2019-03-19 18:20:06]
User575872 - Posts: 33
#58 I used the formula for cell p3

Only a understanding ask: Why does the spreadsheet-system trigger day 4-12 but not 4-13 ?
image20190319_185546.jpg / V - Attached On 2019-03-19 18:15:46 UTC - Size: 4.53 MB - 113 views
[2019-03-19 18:21:03]
User575872 - Posts: 33
...it was breakout at high or low
[2019-03-19 20:10:58]
tomgilb - Posts: 2221
Why does the spreadsheet-system trigger day 4-12 but not 4-13 ?
The P3 formula in post #58 doesn't give another long unless the Last price crosses the LOD.

From post #51:
and you always want to alternate longs and shorts

[2019-04-08 15:41:44]
User575872 - Posts: 33
Sorry, I still have a question of understanding: What exactly is to be understood by "LOD" in the context of your answer in post 63?
[2019-04-08 16:32:53]
tomgilb - Posts: 2221
LOD = low of day

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

Login

Login Page - Create Account