Login Page - Create Account

Support Board


Date/Time: Thu, 28 Mar 2024 20:25:43 +0000



Spreadsheet trading study: canceling an unfilled limit order

View Count: 1915

[2018-02-21 17:49:53]
Shane - Posts: 31
In a spreadsheet trading study, I’ve implemented the code from the SC website to cancel an unfilled limit order after x number of bars.
http://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php


This works as expected for the first buy or sell order, however if there is a second order in the same direction, this approach fails (i.e., a buy order where the trade completes and then the next order that triggers is also a buy order).

What happens is (from the trade activity log) the second order is triggered and accepted, and then immediately *canceled* by the J27 cell formula.

I’m using the spreadsheet to trigger a buy or sell with attached orders. The attached orders then handle the exits for the order. The buy and sell exit columns of the spreadsheet study are not used.

Any ideas how to handle this case? I’m stuck.

*************

These two formulas (from the link above) count the number of cells since a buy and sell event.

In column P: =IF(M3,0,IF(OR(K3,P4>0),P4+1,P4))

In column Q: =IF(K3,0,IF(OR(M3,Q4>0),Q4+1,Q4))

This formula is in the J27 cell (cancel working orders):

=OR(AND(P4 >= H6 + 1, J4 > 0), AND(Q4 >= H6 + 1, J4 < 0))

…where H6 is the number of bars to wait for a fill before canceling the order. (The only difference from the website code is the use of H6 instead of H4 to hold the number of bars to wait).

*************

The formulas in P and Q will start counting the number of bars since a buy or sell event and will just keep counting until an order in the opposite direction is triggered. This is problematic if there are consecutive trades in the same direction.

I would have thought the use of “J4>0” and “J4<0” in the J27 formula would overcome this issue, but it consistently fails to work as expected. Is there an order of formula execution problem?

Am new to spreadsheet studies so any help greatly appreciated.

Regards,
shane
Date Time Of Last Edit: 2018-02-21 19:20:14
[2018-02-21 19:21:28]
Sierra Chart Engineering - Posts: 104368
We will contact the person who worked on those formulas to see if they have a suggestion.

Otherwise, we recommend using ACSIL instead:
Automated Trading From an Advanced Custom Study
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2018-02-21 20:34:17]
Sawtooth - Posts: 3952
Those example formulas are simplistic and assume there is only one entry per direction.

If you have multiple entries in the same direction before one occurs in the opposite direction, you need to set it to 0 using an event other than a reference to the opposite direction signal. Without knowing the details of your system, only you can determine what that might be.

In P3: =IF(BuyZeroingEvent,0,IF(OR(K3,P4>0),P4+1,P4))
In Q3: =IF(SellZeroingEvent,0,IF(OR(M3,Q4>0),Q4+1,Q4))
The zeroing event needs to return a 1 (TRUE).

Here's another simpler way to do it for multiple entries in the same direction:
In P3: =IF(K3,1,P4+1)
In Q3: =IF(M3,1,Q4+1)
This starts the count over at each new entry signal, but it may not return what you want in all situations.
[2018-02-21 20:51:19]
Shane - Posts: 31
Thank you Tom.

I came to similar conclusions but could not figure out where in the spreadsheet is the information that the trade has completed (the zeroing event). I'm not using the buy and sell exit columns. The trade completes via attached orders.

Your second suggestion will work for me: if entry, then reset counter, else increment counter

I tried this prior to posting my help request, but struggled with issues that are likely related to unfamiliarity with spreadsheet studies and the chart replay system. I will try again now that I know that is at least a viable path.

many thanks,
shane
[2018-02-23 17:38:46]
Shane - Posts: 31
Tom:

Thank you for the suggestion. Below is what I have implemented and have been running for two days with no issues.


In P3: =IF(K3,1,P4+1)
In Q3: =IF(M3,1,Q4+1)

In J27: =OR(AND(P4 >= H6 + 1, J4 > 0), AND(Q4 >= H6 + 1, J4 < 0))
(where H6 stores the number of bars

SC support:

You might consider changing the website to reflect this simpler solution. The formulas currently displayed are overly complex and require an unrealistic scenario (alternating buy and sell orders).

No response necessary (I don't see a way to close the ticket/thread.)

Many thanks,
shane
[2018-02-23 23:38:04]
Sierra Chart Engineering - Posts: 104368
Yes we do plan to update the formulas in the documentation.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2018-03-14 21:52:00]
Sierra Chart Engineering - Posts: 104368
The updates have been done.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing

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

Login

Login Page - Create Account