Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Sierra Chart Worksheet System For Trading/Alert Discussions
I realize many of the J column cells are transitionary, so not stored easily, but..
would there be a way show true when a time has moved past a fixed amount of minutes?
something like (not working obviously)
=fractime($J$46)<fractime(A3)-3*60*.0000116
meaning:
the time of last exit is more than 3 minutes ago
(that decimal is a fractime second)
This compares the last chart update time to the last exit time.
3/1440 is 3 minutes in Serial Date Time format because there are 1440 minutes in 24 hours.
FRACTIME removes the date, but you don't need to do that, especially if the last exit was yesterday.
Random entry testing using Sierra's Spreadsheet System for Trading
Hoping someone can point me in the right direction. I want to see the results of random entry on a few different R multiples as well as scaling-out scenarios on ES. I've used Sierra's Spreadsheet Study before for some simple charting but don't have experience with the Spreadsheet System for Trading study.
I'm able to generate a random list of dates/times/directions within Excel but I'm not sure if these can then be run through Sierra's Spreadsheet System for Trading study, or if the random list needs to be created directly within Sierra. I would like to use the same dates/times/directions if possible for each of the exit criteria below.
Specifically, I want to see results based on random entry over 1,000 trades using the following exit parameters:
5 point stop, 2.5 point target
5 point stop, 5 point target
5 point stop, 7.5 point target
5 point stop, 10 point target
5 point stop, scale half at 4.5 points, remainder at 7.5 points
5 point stop, scale half at 4.5 points, remainder at 10 points
I've done a similar study in the past on a few forex pairs using macros in Excel but it was extremely inefficient and I don't want to go through that process again.
Is anyone who's familiar with Sierra's backtesting able to let me know if a) it is possible to enter based on time/date criteria, and b) am I on the right track generating my list within Excel, or should I be working entirely within Sierra?
The short answer is: You probably can't do what you want with the Spreadsheet System for Trading study. At best, it would be cumbersome.
Sierra Chart spreadsheet studies are simplistic compared to Excel, and are only Excel-similar.
- There are fewer functions, some of the functions operate differently, and some functions are specific to Sierra Chart. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html
- There is no interface with Excel.
- There are no graphs, no conditional formatting, no macros, no referencing of other spreadsheet files, and others.
There is no optimization functionality.
You can add a blank sheet not associated with a chart where you could perform calculations within the limitations of the study. If it couldn't replicate a random list of dates/times/directions as in Excel, you could manually copy/paste from Excel to this sheet and then reference that sheet for entry criteria.
Entries can be based on date and time, however, it's not possible to enter at a specific time unless there was a chart update at that specific time. The best you can do is create a window spanning a specific time.
Backtesting in Sierra Chart is basically replaying the chart's data. Speed and accuracy are dependent on computer hardware and spreadsheet complexity. There are several backtesting options, but the fastest one only works well on some configurations. Accurate backtesting will likely end up being much slower that you would like. Each exit iteration would need to be backtested separately and, worst case, could take hours to accurately test 1000 trades. Six iterations could take days.
After trial and error (and a lot of reference to your responses on Sierra's forum), I've got something almost working. I've created a separate spreadsheet for the date and time criteria as you suggested and have populated it with sample data as follows:
The formula that I'm using in cell K3 within Sheet1 for the Buy Entry trigger is:
When I copy this formula down, it references cells A2, A3,... within Sheet2 as I'd expect. Using Excel logic, I would anticipate the entries to trigger only when the date and time match those in Sheet2 but for some reason it's triggering a buy entry every day at 08:35 and is ignoring the DATEVALUE condition altogether. If I remove the 'ROW()=3' reference at the end of my formula it then triggers buy entries every day at 08:35, 08:45, and 09:00 and once again ignores the date.
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.
