Home >> Support Board

Support Board

Date/Time: Fri, 26 Apr 2024 02:47:48 +0000

How to Post a Message for Support

Limit v Chase Market Orders using spreadsheet

View Count: 2723

[2019-06-10 16:01:55] #1 [Go To First Post]
MotoMoto - Posts: 47
Hi, there seems to be a few options that could be used when using an automated spreadsheet for trading strategy that is used to capture spikes, BUT is fraught with issues when market become thin and gappy when 'at market' is used. This happened the other day (ECB rate decision 6th June 13:45 Paris time). The EUR.AUD gapped and traded very thinly, and I wanted to check on the best way to minimise issues from this in the future....and to ensure I am understanding some of the features given the extensive documentation. (The other reason I ask is that simulated trading v live is often different so replicating this unless its exactly the same is unlikely to help, plus maybe because I am using range bars, reply always freezes my system so is pretty dead to me.) ///// setup -- connected to and using Interactive brokers data and a paper trading account at IB (not simulated within SC). The strategy relies on capturing spikes like this, but using 'at market' is fraught with issues. 1) I use a basic rule that should a range bar close above a line (I manually enter) from below, send a buy signal to trade at market. Everything works perfectly normally most times. 2) The spreadsheet buy order triggered at price level 1.6080 (time 13:45:13), BUT the market order filled at 1.6102 (at 13:45:14). The spreadsheet was set to trade at market. The Buy Entry (Col K) was set as a true and false response. This all happened pretty quickly and I understand there are lags and gapping markets etc. Q: if I wanted to ensure that a PURE limit order was entered so that the price if entry was at 1.60857 (the actual close of the range bar), do I need to a) ensure the Settings at J71, J73 are set at 'L', and b) ensure that the close of the bar is returned as a number rather than a simple (true or false) in Col K? OR Q: am I better setting it as a limit order (the first option above), BUT returning a number that is slightly more than that returned by the close in Col K? eg; add 5 ticks, thus rather than col K returning the close of 1.60857 it returns 1.60887 (and thus becomes like a limit order and if the market happens to be lower at the time of entry you are filled at market) OR Q: if I wanted to give myself some leeway to ensure I was set in the trade (but not necessarily at market) do I make this a Limit Chase Order, and thus a) ensure settings at J71, J73 are set at 'L' and b) ensure that the close of the bar is returned as a number rather than a simple (true or false) in Col K? c) ensure there is a limit to the number of ticks to chase in J46 (eg; 5) - thus the max the limit price would have been approximately 1.60857+25 = 1.60882 (as opposed the actual market order fill of 1.6102 d) ensure that Menu/M >> Orders >> Use Limit Chase Orders is also ticked. Thanks in advance for any help.

[2019-06-10 18:11:30] #2 [Go To First Post]
Sawtooth - Posts: 3976
In the Spreadsheet System for Trading study, columns K-N are order triggering columns: any non-zero is TRUE, and zero is FALSE. Entry prices are not set in these columns. Non-Market entry order type prices are set in cells J22/J24, and the entry order types are set in J71/J73. To place a Buy Limit order at the high of the previous bar, J71 should contain a text L and J22 should use a formula like this: =C4 To use Limit Chase, enable the Menu/M >> Orders >> Use Limit Chase Orders option on the Trade Window menu. Set the max chase using the Trade Window's Set tab's 'Max Chase for Chase Limit Orders' setting. The documentation says to set 'Use Chase Orders' to Yes in the spreadsheet study settings, and to use J46 to set the maximum chase amount. Spreadsheet Systems, Alerts and Automated Trading: Use Chase Orders However, using these settings does not override the Trade Window settings so they seem to be non-functional. You will also need a way to cancel an unfilled Limit entry, should that occur. Here are two examples of how to do this: Spreadsheet Example Formulas and Usage: Cancel an Unfilled Order After n Bars Date Time Of Last Edit: 2019-06-11 13:47:21

[2019-06-11 13:37:18] #3 [Go To First Post]
MotoMoto - Posts: 47
Hi Tomglib thankyou - for confirming some points and I clearly missed the obvious use of J 22/24 (doh!) its been a while since I was on SC and its good to see you are still here and your help is much appreciated. As a followup… I walked through the replays using the range bars, and discovered that the repainting of the range bars due to the gaps causes some issues. This is because the range bar that actually triggers the buy entry is then turned into 3 or 4 smaller range bars, due to the size of the gaps and the short time taken in the data fills (all within 1-2 seconds). Even allowing for some changes in the range bars being painted by changing Graph Draw Type in the chart settings it seems to be the same issue always. Hence suddenly there are multiple rows populated in the spreadsheet in one hit. Thus the original range bar entry trigger in Col K is triggered, but when the bars are repainted it is now down on row 6, but the limit order (or market order) is sent at the much higher price as showing in Row 3. The best way I can see to get around this is simply set the limit order to be based on a fixed price line (the original level I manually inputted). This will stop any crazy chasing the market, or really excessive limit prices being sent. It also eliminates any potentially funky formulas required that probably would not be much better than having to trade at market anyways. It is then highly likely that you need a double bottom of some sort to get set. (Such is trading!) I attach some thoughts and images for extra clarity if anyone was interested and came across this problem themselves.


Limit Orders.pdf - Attached On 2019-06-11 13:36:08 UTC - Size: 515.39 KB - 350 views

[2019-06-11 14:06:13] #4 [Go To First Post]
Sawtooth - Posts: 3976
I've used range bars for years and I've never seen them repaint. Range bars require a quality data feed, and the 'Intraday Data Storage Time Unit' must be set to 1 tick. Which of the 6 range bar types are you using?

[2019-06-11 16:29:51] #5 [Go To First Post]
MotoMoto - Posts: 47
1) I checked my settings and it was set at the 1 sec level and have since changed it to 1 tick.... I then (maybe stupidly deleted and downloaded from IB the data from the 6th June for EUR.AUD-CASH-IDEALPRO) The chart now looks completely different in that there is no double bottom and the micro details are gone, but I suspect this is because of the poor quality of IB data. (which is why I am never a great believer in relying to much on back testing as the data is usually suspect) So I also downloaded the EURAUD from SC data and the same double bottom is there that happened in reality and was in the original IB data. The unfortunate consequences of FX trading, rebundling of data and no single exchange. 2) I just have it set at range bars standard....but during the replay had the same thing happen even when I tried a couple of different variations. You can see clearly in the images I managed to record that the range bar was repainted during the replay bar by bar by SC, and to me it makes sense that it did because of the gaps in prices but for complete accuracy it should really paint 3 bars in one re paint rather than 1 ....however, this would still not change the situation of where the trigger was and where the final bar price was setting the limit at, as they are set off different bars, and given the actual price change was so large it probably painted/filled the spreadsheet in one repaint (hope this is the right vocab). All in all an interesting issue for investigation and solving. :) Date Time Of Last Edit: 2019-06-11 17:24:43

[2019-06-11 18:34:59] #6 [Go To First Post]
Sawtooth - Posts: 3976
If you change the Intraday Data Storage Time Unit to a more precise setting, you must delete and redownload the data. I just replayed the EURAUD in that section you referenced and the bars don't repaint. If you set Gap Fills to None in the Chart Settings, there are no bars in the gap. Regardless, they happen very fast, so you are just experiencing slippage. There is no way to avoid this, even with Limit Chase orders.

[2019-06-11 20:57:25] #7 [Go To First Post]
MotoMoto - Posts: 47
yes.... I deleted and downloaded the data....I even tried for the entire data history showing in the chart.... IB data which is the data provider for the instrument is known to have poor data, so I can assume that the change is a result of this new download of data after the old information was deleted. (looking at the Data information from IB its pretty crap from a historical perspective) The SC download for the same instrument, different code from the SC data feed shows what I would consider a more accurate representation of what happened. I already had it set to Gap Fills to None when it did the repaint.....Let me experiment tomorrow on this SC data (as opposed to the IB data) and with changing the gaps fills to none section, even though it is already set to Gap Fills to None and see what happens with the re painting. Yep - I understand the whole issue is about slippage and speed --- the point is how to get it so that you minimise the issues of this slippage and still get to get the advantages of the spreadsheet for auto trading based on the trigger events, otherwise simply using 'at market' and accepting the slippage is the easy option...such is the joys of trading! thanks edit: I can see that there is no repainting using the SC data when I run a replay, but this still does not explain the clear repainting that is occurring with the old IB data as shown in the images (which I foolishly now have deleted)....maybe SC engineers can offer more insight. Date Time Of Last Edit: 2019-06-11 21:11:43

[2019-06-13 16:06:06] #8 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
This is now a rather long thread. So we do not have to go through and read all of this, can you just give us a very short explanation and a screenshot in regards to this "repainting". replay always freezes my system so is pretty dead to me.) In regards to this, refer to: Auto Trade System Back Testing: Improving Back Test Performance of Spreadsheet Trading Systems 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 Date Time Of Last Edit: 2019-06-13 16:08:12

[2019-06-13 17:16:04] #9 [Go To First Post]
MotoMoto - Posts: 47
1) Post number three has the best relevant information and images. (bottom of page 1 and then page 2) 2) It started from wanting to minimise issues with slippage and spreadsheet trading (A resonable solution has been found), and then 3) when backtesting and using the replay I noticed the range bar was repainting, which I found odd. (Unfortunately I deleted the original IB live data when I changed the data download and downloaded again from IB using 1 tick rather than 1 sec - but the images were saved). With the new data from IB it still repaints....whereas with the data from SC it does not which implies that its all about the poor data from IB as a main source of the problem. However, in respect of range bars I would have thought that even in the event of the data showing a large jump in prices (a gap) that the spreadsheet would interpret this as 3 rows and would run each row independently. (thanks for the link to improve the replays - I will add it to the list of things to do, and in the meantime, I simply use the Simple Replay - sorry to sound so harsh) Date Time Of Last Edit: 2019-06-13 17:18:17


Limit Orders.pdf - Attached On 2019-06-13 17:08:22 UTC - Size: 515.39 KB - 361 views

[2019-09-04 03:22:00] #10 [Go To First Post]
Chippstrader - Posts: 30
Hi Quick question, what formula you have used to enter Buy condition with + 5 ticks ? I see you mentioned in the document. EXPERIMENT Change the limit when trigged to be the PRICE LEVEL (eg; 1.6080 + 5 ticks)

[2019-09-05 07:44:16] #11 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
In regards to post #10, are you asking about how to set the Limit order price when using the Spreadsheet System for Trading 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

[2019-09-05 12:06:17] #12 [Go To First Post]
Chippstrader - Posts: 30
Yes, I am looking for Buy condition to take market order if price moves n ticks from my condition and also place if any way to place limit order when price trace back to the level with second condition.

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