Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sat, 27 Apr 2024 07:06:14 +0000
[User Discussion] - Spreadsheet
View Count: 2241
| [2019-06-20 17:57:17] |
| AndreyPoroshin - Posts: 102 |
|
Spreadsheet In column J, we use the mass of interesting formulas that can be duplicated in other columns or lines of the table and modified to suit your needs. Help with information on this topic, because the formulas from EXCEL are not suitable, digging a ton of information from the network, but constant errors in the preparation of formulas. There are very few examples on your site, not what you need. For example, I need to create a row J106 -107 -108 and so on with a function that searches for the delta value (ID6.SG3@3) at the time of the ENTRY signal and stores it also as J93-J96, in order to later refer to the LAST value in this row Could you create a list of supported formula operators with examples, because otherwise it’s useless to learn from EXCEL formulas, they are poorly supported by SC Spreadsheet |
| [2019-06-20 18:54:31] |
| |
|
Refer to this page: Working with Spreadsheets The way that you work with spreadsheet formula operators is going to be identical to Excel. 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-06-21 08:43:58] |
| AndreyPoroshin - Posts: 102 |
|
On this page there is absolutely a small amount of information from what I asked. Why do you create a product and do not teach how to use it? I'm about the table. Excel formulas give errors, do not be misleading. I lost a month looking for a solution to working with tables, if it were not in the program or it would be indicated that there was no support for it, it would be better during this time to master other ways of writing automatic strategies.
|
| [2019-06-21 08:53:58] |
| AndreyPoroshin - Posts: 102 |
|
for example, which formula uses LAST ENTRY. It is likely that this is something like this = GETCORRESPONDINGMATCH (K3: N1000, <> 0, 0, 1, 0, AA3: AD1000, 0) . In the manual there is no example of its use and it is necessary for understanding. Especially since such an Excel statement is not supported. -generally incomprehensible information |
| [2019-06-21 17:18:29] |
| AndreyPoroshin - Posts: 102 |
|
Spreadsheet Systems, Alerts and Automated Trading: Disable Auto Trading [J28] =OR(J41-INT(J41) < TIMEVALUE("17:10:00"), J41-INT(J41) > TIMEVALUE("15:54:50") ) =AND(J8 = 0, OR(OR(FRACTIME(J41) < TIMEVALUE("17:00:00"), FRACTIME(J41) > TIMEVALUE("15:54:00")), AND(FRACTIME(J41) > TIMEVALUE("08:29:00"), FRACTIME(J41) < TIMEVALUE("09:30:00")))) These formulas are from your documentation. These formulas do not allow to open positions at any time. What is wrong with them? I need to enter the market at 5:05 pm and get out at 3:54 pm Also in the middle of the day I would like not to trade at the open of the market from 8:30 to 9:30 |
| [2019-06-21 23:09:21] |
| Sawtooth - Posts: 3976 |
|
Sierra Chart's spreadsheet studies are flexible enough to do what you want and their documentation is extensive enough to answer most questions. The expectation is that the user will apply it and extrapolate it. Sierra Chart spreadsheet studies are only Excel-similar; they don't support every Function, they have added some non-Excel Functions, and some common Functions use a slightly different syntax, among other differences. You'll need to work within that which is supported. It would be impossible to offer example formulas for every possible scenario. You'll need to use what is provided to get you started, and then do some trial-and-error to learn how to do what you want. Sierra Chart spreadsheet studies' array is in descending order. Each row is a chart bar, with the current bar in row 3 and earlier bars in rows below. If you need to compare a previous bar/row's value to the current bar's row, the easiest way is to create a persistent variable so that the previous value is brought to row 3. Here is an example: Spreadsheet Example Formulas and Usage: Return Last Two Zig Zag Reversal Values Column J's read-only values only contain the most recent value so it is difficult to use them if you need a value other than the most recent. Occasionally SC Engineering will add outputs to column J, so it is not recommended to use the blank cells in this column, or column I. Column H is always blank and can be used for user options, and for calculations that are not associated with chart bars. The example formulas you refer to for cell J28 only work for earlier/later times that don't span midnight, not later/earlier times that do span midnight. Try this formula (the second AND is the significant difference, plus FRACTIME instead of INT): =AND(J8 = 0, OR(AND(FRACTIME(J41) < TIMEVALUE("17:00:00"), FRACTIME(J41) > TIMEVALUE("15:54:00")), AND(FRACTIME(J41) > TIMEVALUE("08:29:00"), FRACTIME(J41) < TIMEVALUE("09:30:00")))) This is a variation of the example here: https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#DisableTradingOutsideOfRegularTradingHoursAnd2HoursMidday If you need more help, post again. If your questions become too complex for a forum response, you'll need to contact me directly. |
| [2019-06-26 17:54:00] |
| AndreyPoroshin - Posts: 102 |
|
Thank you for support! I will try not to be too intrusive and try to figure it out myself. But with some things I can not figure out, I ask you. 1. There is a formula in the column Z = SLOPE (E3: E15, A3: A15) determining the slope of the price. The question seems to be simple, but I can not find the answer. How can I send information to the K and M columns that the slope has changed and goes up to the K buy entry bell and, accordingly, when the slope changes and goes down to the M sell entry column? The only thing that I could realize is = AND (Z3> Z4> Z5> Z6) for K and = AND (Z3 <Z4 <Z5 <Z6) for M. But I don’t think it should be so. If this is correct, then the question is closed. 2. J28 = AND (J8 = 0, OR (FRACTIME (J41) <TIMEVALUE ("08:29:00"), FRACTIME (J41)> TIMEVALUE ("10:30:00")), OR ($ J $ 11 + $ J $ 10> = 1100)) closing by the condition of time and profit J29 = AND (J8 = 0, OR (FRACTIME (J41) <TIMEVALUE ("08:29:00"), FRACTIME (J41)> TIMEVALUE ("10:30:00")), OR ($ J $ 11 + $ J $ 10> = 1100)) closing by the condition of time and profit The formulas work, but until J8 = -1 +1, the alignment of positions and the disconnection of autotrading does not occur until the position encounters a stop order. Naturally becomes J8 = 0 and the formulas work. I have to make the formula work instantly when one of the conditions is reached and close the trade and positions. What am I doing wrong? |
| [2019-06-26 23:50:38] |
| Sawtooth - Posts: 3976 |
|
1. To capture change in direction of the SLOPE function output, try this: Transition to positive: =AND(Z4<0,Z3>0) Transition to negative: =AND(Z4>0,Z3<0) 2. Disabling autotrading does not flatten a position; it merely turns off autotrading. J8=0 is only used in J28 formulas so that exits and flattens can occur after autotrading is disabled. The flatten formula doesn't need a time window; it only needs a single time to flatten. You will probably want to use J45 instead of J11: Spreadsheet Systems, Alerts and Automated Trading: Cumulative Closed Profit/Loss [J11] Spreadsheet Systems, Alerts and Automated Trading: Closed Daily Profit/Loss [J45] If you flatten when J10+J45>=1100, it is possible, because of slippage, that J45 could be less than 1100 when flat (J10 will be 0), allowing another entry. You don't need to use absolute references ($) in column J. If you are scaling in or using reversals, additional formulas might be required. Try these formulas: J28: =AND(J8=0,OR(FRACTIME(J41)<TIMEVALUE("08:29:00"),FRACTIME(J41)>TIMEVALUE("10:30:00")),J45+J10>=1100) J29: =OR(FRACTIME(J41)>TIMEVALUE("10:30:00"),J45+J10>=1100) |
| [2019-07-03 11:56:55] |
| AndreyPoroshin - Posts: 102 |
|
Hello. I do not understand why the limit order is canceled. In the table, for this special functions are not assigned. Counter ENTRY event does not occur. In the study settings I tried to disable all possible points of order cancellation, but this did not help either. Maybe somewhere in the settings of the program, cancellation is enabled when it is not executed for a certain period of time?
|
 order canceled.png / V - Attached On 2019-07-03 11:52:29 UTC - Size: 30.08 KB - 370 views Private File |
| [2019-07-03 13:06:20] |
| Sawtooth - Posts: 3976 |
|
Check the 'Time In Force' setting on the Trade Window: Attached Orders: Time In Force |
| [2019-07-03 18:39:31] |
| AndreyPoroshin - Posts: 102 |
|
Trying to find a formula for calculating the volume above and below after the trigger of the Large Volume Trades trigger What I did wrong, because when the entire volume is on top of the price, the data is simply duplicated, as in the example Then I summarize them and also get wrong, because they are mixed with those that are not within the specified range from = SUM (W3: W103) and = SUM (V3: V103) Maybe there is a more reliable method? |
| [2019-07-03 22:19:14] |
| Sawtooth - Posts: 3976 |
|
Trying to find a formula for calculating the volume above and below after the trigger of the Large Volume Trades trigger
Volume at price in not output by the Large Volume Trades Indicator study, and not directly available to the Spreadsheet System for Trading study.It is output by the Spreadsheet Study study by setting 'Output Volume At Price Data' to Yes, but you cannot use more than 42 Formula Columns because the outputs are in column BA and above. Also the output is horizontal, not vertical, and starts with the volume at the low in column BC. It would be challenging to do what you want with a spreadsheet study because every bar's low is at a different price. |
| [2019-07-04 07:20:07] |
| AndreyPoroshin - Posts: 102 |
|
I understand that in the tables of some things can not be achieved. In fact, this is my first stage in trade automation and I want to improve this for the better. My main task is to filter the volumes, which I perfectly see in T @ S, as in the example on the screen. Is it possible to display this filtering in Spreadshets? If not, then perhaps this can be done with the help of ACSIL, and is it much more difficult? It took me a month to understand the tables, how do you think, to implement automated filtering from T @ S, how should I start and how long will it take? The question is, of course, rhetorical, the main thing is that all of this is really feasible and the costs paid off. Maybe I should pay attention to the paid package5 with the Numbers bars research and use the volume in spreadsheets, is this not an option?
|
| [2019-07-04 13:46:38] |
| Sawtooth - Posts: 3976 |
|
Is it possible to display this filtering in Spreadsheets?
Yes, and no. Mostly no.There are several Time and Sales studies that output to spreadsheet studies, but the data in each row is not associated with the chart bars of each row. What you want to do is best done with ACSIL. |
| [2019-07-11 23:12:54] |
| AndreyPoroshin - Posts: 102 |
|
Hello, please help to deal with the time data sorting formula. I need to break 24 hours into 3 zones to track the volume. At night the volume is less, in the afternoon it is more I made three columns, but they come into conflict with each other, of course it would be better to put all this in one column. US TRADES R =AND(OR(FRACTIME(J41)<TIMEVALUE("08:29:01"),FRACTIME(J41)>TIMEVALUE("15:54:55")),G3>1500) EU TRADES S =AND(OR(FRACTIME(J41)<TIMEVALUE("00:00:00"),FRACTIME(J41)>TIMEVALUE("08:29:00")),G3>400) ASIA TRADES T =AND(OR(FRACTIME(J41)<TIMEVALUE("17:00:00"),FRACTIME(J41)>TIMEVALUE("23:59:59")),G3>400) =IF(OR(R3>0,S3>0, T3>0), E3, O4) |
| [2019-07-12 02:24:48] |
| Sawtooth - Posts: 3976 |
|
I need to break 24 hours into 3 zones to track the volume.
- You must use an absolute reference to J41 with formulas in Formula Columns:FRACTIME($J$41) - Column G is # of Trades, not Volume. - You'll want a time inclusive formula for each, instead of a time exclusive formula: US TRADES R3: =AND(FRACTIME($J$41)>TIMEVALUE("08:29:01"),FRACTIME($J$41)<TIMEVALUE("15:54:55"),G3>1500) EU TRADES S3: =AND(FRACTIME($J$41)>TIMEVALUE("00:00:00"),FRACTIME($J$41)<TIMEVALUE("08:29:00"),G3>400) ASIA TRADES T3: =AND(FRACTIME($J$41)>TIMEVALUE("17:00:00"),FRACTIME($J$41)<TIMEVALUE("23:59:59"),G3>400) - Since you are using Formula Columns, you might want to use the time of each bar in A3, instead of J41: US TRADES R3: =AND(FRACTIME(A3)>TIMEVALUE("08:29:01"),FRACTIME(A3)<TIMEVALUE("15:54:55"),G3>1500) EU TRADES S3: =AND(FRACTIME(A3)>TIMEVALUE("00:00:00"),FRACTIME(A3)<TIMEVALUE("08:29:00"),G3>400) ASIA TRADES T3: =AND(FRACTIME(A3)>TIMEVALUE("17:00:00"),FRACTIME(A3)<TIMEVALUE("23:59:59"),G3>400) - You could use column H for the times and # of trades, then reference these cells in the formulas: H1: US start; end; trades H2: 08:29:01 H3: 15:54:55 H4: 1500 H5: EU start; end; trades H6: 00:00:00 H7: 08:29:00 H8: 400 H9: ASIA start; end; trades H10: 17:00:00 H11: 23:59:59 H12: 400 R3: =AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4) S3: =AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8) T3: =AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12) - You could combine all three in one column: =OR(AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4),AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8),AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12)) |
| [2019-07-13 01:47:56] |
| AndreyPoroshin - Posts: 102 |
|
Good advice, thank you! But how to make so that after the transaction all the following triggers with a smaller volume are ignored for some time? I did so = LARGE (S3: S290.1) Where S- Last Entry Volume 290- lines per day in the table on a 5 minute timeframe The problem is that the 290 is not quite suitable, for example, because on Friday there can be a lot of volume at the close, and on Monday at the beginning of the European session, the volume will be less, but it is more significant than the close on Friday. But the lines in the table between these two volumes will be less than 290, since the market did not trade at the weekend and a lot of time passed. The trigger does not work Of course, it is more correct to specify not = LARGE (S3: S290,1) and adding the formula = AND (F-Volume @ 3> Large Volume @ 4 ..., but to indicate that after 12 or 24 hours the maximum volume should be reset and the formulas could already respond to lower volume values The situation is simple, the fact is that in the Asian session many trends are born and they are not so significant in terms of volume compared to volumes on the US. And the BuyEntry trigger, having triggered on Asia, will no longer respond to market noise during the day or other specified period of time, unless there is more volume. I use Relative Volume to measure volume |
| [2019-07-13 17:37:17] |
| Sawtooth - Posts: 3976 |
|
But how to make so that after the transaction all the following triggers with a smaller volume are ignored for some time?
This is not a question I can answer in a forum post.I would require taking a look at the details of your current system. You can contact me here: https://www.sawtoothtrade.com/contact.html Date Time Of Last Edit: 2019-07-14 14:52:46
|
| [2019-08-01 16:12:16] |
| AndreyPoroshin - Posts: 102 |
|
Hello, I ran into a task that at first glance seemed pretty simple. There is a line - the previous level of high volume (V) When the price goes up or down from the level, I try to use the best option for entering, so that I get minimal slippage when the price turns. = AND (E3 = V3 + 0.25) = AND (E3 = V3-0.25) But the deal is not always executed = AND (E3> V3) = AND (E3 <V3) Transactions are executed but there are a lot of entry markers and this is a bad option. And if you do so = AND (E3> V3, E4 <V3) = AND (E3 <V3, E4> V3) then the transaction will also not always be executed due to a mismatch with a specific condition of the formula and not otherwise. fromcrossbelow and fromcrossabove will not work either, this is similar to the version from the previous formula. Transactions are not always completed How to act in such a way as to create a prerequisite for the execution of the order in case the price passes through the level and even if the price just touched the level and rebounded. In any case, it is necessary to execute the order. Which formula will be most effective in this case? |
| [2019-08-01 16:34:29] |
| Sawtooth - Posts: 3976 |
|
You could try this: = AND (E3 >= V3 + 0.25) = AND (E3 <= V3-0.25) You also may need to round the prices to the the rounded ticksize: = AND (MROUND(E3,1/ROUND(1/$J$21,0)) >= MROUND(V3,1/ROUND(1/$J$21,0)) + 0.25) = AND (MROUND(E3,1/ROUND(1/$J$21,0)) <= MROUND(V3,1/ROUND(1/$J$21,0)) -0.25) How to act in such a way as to create a prerequisite for the execution of the order in case the price passes through the level and even if the price just touched the level and rebounded. In any case, it is necessary to execute the order. Which formula will be most effective in this case?
I don't know enough about your system to answer this.
|
| [2019-08-02 06:28:48] |
| AndreyPoroshin - Posts: 102 |
|
The logic is such that when touching the level, the program must accurately understand the direction of entry, but no one can say for sure, and even more so the logic of the program. Therefore, it remains only to make it understand that by touching the level and passing it 0.25 lower, the sale is turned on, if the price turned around and went higher by 0.25, then the purchase should work and so on. Of course, this is the only right decision if you can minimize the difference between buying and selling, because it is in any case faster than it can be done manually. What you offer = AND (E3> = V3 + 0.25) = AND (E3 <= V3-0.25), creates many entry points, the sensitivity is large, and a lot is lost on slippage. I would like to give such a command to automation 1. It doesn’t matter on which side the impulse is going, but if the bar just approached the level or touched the level and 0.25 below the level, then the sell entry is turned on, but on the contrary, then buy entry accordingly. This is probably difficult for automation and there will be contradictions as when using = AND (CROSSOVER (E3: E4, V3)), i.e. on the same bar there will be both sell and buy. These are all seemingly elementary things, but it turns out that in any strategy this is the most important place where problems begin. With the wrong approach, a lot is lost in this place It is very important that Signal only ON bar close is NO to reduce reversal loss The attached video shows the problem of this strategy when the buy entry does not turn on after a reversal, although it is necessary I am sending you a sample strategy to understand the situation. Timeframe 60min NQ or any other instrument Date Time Of Last Edit: 2019-08-02 06:41:12
|
 BIG VOLUME.scss - Attached On 2019-08-02 06:28:13 UTC - Size: 2.89 MB - 280 views BIG VOLUME.Cht - Attached On 2019-08-02 06:28:22 UTC - Size: 321.08 KB - 301 views Private File |
| [2019-08-02 14:11:05] |
| Sawtooth - Posts: 3976 |
|
It doesn’t matter on which side the impulse is going, but if the bar just approached the level or touched the level and 0.25 below the level, then the sell entry is turned on, but on the contrary, then buy entry accordingly.
When using a spreadsheet study, you cannot compare the Last price to a previous last price in the same bar. You must compare the Last price (E3) to a price in the previous bar, e.g. the previous close (E4), and you then don't need the offset to determine direction:K3: =AND(E4<V4,E3>=V3) M3: =AND(E4>V4,E3<=V3) Even so, these formulas will be insufficient to trigger reversals in the same bar. This is probably difficult for automation
These are all seemingly elementary things, but it turns out that in any strategy this is the most important place where problems begin.
It is very important that Signal only ON bar close is NO to reduce reversal loss
the problem of this strategy when the buy entry does not turn on after a reversal, although it is necessary
Using intrabar entries always increases the complexity of the system, and makes it more difficult to understand its functionality. Since you must reference the previous bar to avoid intrabar whipsaws, you might not be able to do what you want with a spreadsheet study.
Date Time Of Last Edit: 2019-08-02 15:56:33
|
| [2019-08-02 17:56:06] |
| AndreyPoroshin - Posts: 102 |
|
You want to say that it is possible to implement this in ACSIL? If so, then it’s good, then it is a matter of study and time. Another question on time intervals, which I could not figure out myself. What will the formula look like, determining that within one minute the price movement has passed 5 points? Timeframe 1 second. I tried X = E3-E63 The results of X = 5.0 and more became a trigger for making a decision, but this turned out to be incorrect, since transactions do not occur every second. Therefore, the A63-does not always mean that it is a minute, the price can change by 5 points and faster, and even have time to return. The trigger is not fixed. Date Time Of Last Edit: 2019-08-02 18:00:17
|
| [2019-08-02 19:18:47] |
| User90125 - Posts: 715 |
|
OP probably should contract Tom at his own support site http://www.sawtoothtrade.com Tom does this for a living, and has already given you quite a fair bit of complementary support here. Date Time Of Last Edit: 2019-08-04 02:36:33
|
| [2019-08-02 20:27:19] |
| AndreyPoroshin - Posts: 102 |
|
the link does not work
|
To post a message in this thread, you need to log in with your Sierra Chart account: