Login Page - Create Account

Support Board


Date/Time: Fri, 29 Mar 2024 06:27:31 +0000



[User Discussion] - Orientation ??

View Count: 829

[2015-08-26 12:22:53]
User713273 - Posts: 409
Hi Thanks for taking the time to read and help me come up to speed on Sierra Charts.

I am mainly interested in the spreadsheet functionality and using indicators that exists and some of my own logic in the same row.

1. I want to add my own custom functions that are based on the cells in the same row how can I do that? I put the formula in row 3 and thought that it was suppose to populate the rest of the column automatically when data comes in. How do I do this?
2. How do I do play back in the spreadsheet? I see the chart moving but I don't see the data replaying in the spreadsheet.
3. For the normally these indicators depend on previous rows, how do you deal with the in the first place. For example prev_close - curr_close for the first piece indicator row in the spreadsheet?
[2015-08-26 13:09:38]
Sawtooth - Posts: 3952
1) With the spreadsheet studies, only columns K-Z...BR will auto-populate from row 3, depending on the Number of Formula Columns setting (16 to 60). These should auto-populate in the New Spreadsheets version, but in the Old Spreadsheets version you can force it to recalculate by going to Chart >> Recalculate.

2) Since the spreadsheet study is added to the chart, scroll the chart into the past, and go to Chart >> Replay Chart. If the spreadsheet data is not following the chart data, you may be looking at the wrong sheet#. By default, the chart# will output to its respective sheet#.

3) Each spreadsheet row is a chart bar. The current bar is in row 3; previous bars are rows below. For the previous bar, reference row 4 in your row 3 formula.
[2015-08-26 16:58:46]
Sierra Chart Engineering - Posts: 104368
1. Spreadsheets do not support custom functions. However, they do support your own custom formulas.

Carefully follow through the step-by-step instructions on this page here:

https://www.sierrachart.com/index.php?page=doc/doc_StudiesSystemsAlerts.php

2. Also carefully follow through the step-by-step instructions here:
https://www.sierrachart.com/index.php?page=doc/doc_StudiesSystemsAlerts.php

3. For the last row outputted to the Spreadsheet, this can be a problem. However, you can solve it by checking for 0 in the previous row and then have the formula return 0 in this case.
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
[2015-08-27 06:40:53]
User713273 - Posts: 409
I think you may have answered this in #3 but just to clarify. I am trying to do the following

I am trying to do a custom formula.
For example even though you already have this study. I would like to do SMA for 4 time periods.
in my custom formula in row 3 how would I write that since I need 4 time period before the calculation actually can happen.
Assuming ID0.SG4 is LastPrice
Row Formula
3 =(ID0.SG4@3+???)/4.0
4 =(ID0.SG4@3+ID0.SG4@4 + ???)/4.0
5 =(ID0.SG4@3+ID0.SG4@4 + ID0.SG4@5 + ???)/4.0
5 =(ID0.SG4@3+ID0.SG4@4 + ID0.SG4@5 + + ID0.SG4@6)/4.0 <-- This can actually calculate now.

How do I represent this in row 3? since I can't go ID0.SG4@2, ID0.SG4@1, ID0.SG4@0?


Even simpler example is difference between current and last price. How do I do that in custom formula in row 3?

Thanks again
[2015-08-27 13:38:25]
Sawtooth - Posts: 3952
This formula in row 3 of a Formula Column will recreate an SMA of the Last price:
=AVERAGE(OFFSET(ID0.SG4@3,0,0,$K$1,1))
or
=AVERAGE(OFFSET(E3,0,0,$K$1,1))
where K1 is the length.

If you want to do 4 SMAs of an SMA, you'll need 4 Formula Columns, the first referencing column E, and the others each referencing the column to the left.

This formula in row 3 will give the difference between current price and previous Last price:
=ID0.SG4@3-ID0.SG4@4
or
=E3-E4
[2015-08-27 14:36:23]
User713273 - Posts: 409
Great, thx for the clarity.

I understand that the first value is really for row 6 in row 3 for the belo=w formula. How would I reference that in my next column?

So if column P3
=AVERAGE(OFFSET(E3,0,0,4,1))

Whats is column's Q3 formula since there is no cell to reference 4 back?
=P0?
[2015-08-27 15:13:50]
Sawtooth - Posts: 3952
For an SMA of P3[-1], in Q3:
=AVERAGE(OFFSET(P4,0,0,4,1))

If you want an SMA of E3+ the previous P4, you'll need to use another Formula Column (Q3) to combine the values:
=E3+P4
then in R3:
=AVERAGE(OFFSET(Q3,0,0,4,1))

The spreadsheet array is in descending order; previous bars are in rows below row 3.
Date Time Of Last Edit: 2015-08-28 00:48:51

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

Login

Login Page - Create Account