Login Page - Create Account

Support Board


Date/Time: Sun, 17 Feb 2019 23:35:09 +0000



Automated Trading

Support Request:
[2019-01-02 00:12:02]
User575872 - Posts: 20
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: 661
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: 2097
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: 20
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: 2097
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: 20
=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: 2097
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: 20
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: 2097
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: 20
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: 20
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: 2097
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: 20
Fine! That helps me a lot! Thank you
[2019-01-09 19:14:16]
User575872 - Posts: 20
=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: 2097
Sorry.
Try again, using the edited formula in post #12.
[2019-01-11 09:15:29]
User575872 - Posts: 20
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: 2097
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: 20
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: 2097
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: 20
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: 2097
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: 20
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: 20
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: 2097
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: 20
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: 2097
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: 20
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: 2097
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: 20
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: 2097
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: 20
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: 2097
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: 20
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: 2097
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: 20
….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: 2097
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: 20
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: 2097
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

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

Login


Login Page - Create Account