Login Page - Create Account

Support Board


Date/Time: Wed, 24 Jul 2019 05:43:54 +0000



[User Discussion] - Spreadsheet

Support Request:
[2019-06-20 17:57:17]
User796344 - Posts: 28
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]
Sierra Chart Engineering - Posts: 74136
Refer to this page:
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php

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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2019-06-21 08:43:58]
User796344 - Posts: 28
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]
User796344 - Posts: 28
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]
User796344 - Posts: 28
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ28_DisableAutoTrading

=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]
tomgilb - Posts: 2195
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:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#ReturnLastTwoZigZagReversalValues

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]
User796344 - Posts: 28
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]
tomgilb - Posts: 2195
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:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ11_CumulativeClosedProfitLoss
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ45_ClosedDailyProfitLoss

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]
User796344 - Posts: 28
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?
imageorder canceled.png / V - Attached On 2019-07-03 11:52:29 UTC - Size: 30.08 KB - 11 views
Private File
[2019-07-03 13:06:20]
tomgilb - Posts: 2195
Check the 'Time In Force' setting on the Trade Window:
https://www.sierrachart.com/index.php?page=doc/AttachedOrders.html#TimeInForce
[2019-07-03 18:39:31]
User796344 - Posts: 28
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?
image1.png / V - Attached On 2019-07-03 18:39:26 UTC - Size: 139.08 KB - 14 views
[2019-07-03 22:19:14]
tomgilb - Posts: 2195
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]
User796344 - Posts: 28
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?
imageTS.png / V - Attached On 2019-07-04 07:19:55 UTC - Size: 70.92 KB - 15 views
[2019-07-04 13:46:38]
tomgilb - Posts: 2195
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]
User796344 - Posts: 28
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]
tomgilb - Posts: 2195
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]
User796344 - Posts: 28
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]
tomgilb - Posts: 2195
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

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

Login

Login Page - Create Account