Hello All -

I successfully added sound alerts for a couple of studies in SC using the 'Alert' tab in the study settings dialog. I am now trying to create improved versions of the alert logic using a Spreadsheet System/Alert study.

I have some very basic questions about using spreadsheets to build a study that will fire audible alerts.

Some context:

1) I added a 'Spreadsheet System/Alert' study to my chart.
2) I have the sheet set up with only 8 rows because I don't need to look back very many bars.
3) I have successfully exported the referenced studies into the spreadsheet and I can see the values update in real time in row 3, and older values get pushed down to the rows below.
4) I am using a text editor to edit the alert formulas, then pasting into the sheet cells.
5) I am using columns O,P,Q,R for my formulas.

Questions:

1) When I paste a formula into row 3 of any column, it gets auto-filled (and row references incremented) to all rows below. Why is this? I just want my check to occur on the close of the current bar (row 3).

Q: Why does SC fill all rows below row 3 with my formula/alert logic?

2) In order to avoid having the row references altered by the auto-fill described above, I am using the 'absolute reference' notation to specify study col/row I want to reference. For example: =AND(ID13.SG4@$3>0, SUM(ID9.SG1@$3:ID9.SG1@$7)>0)

Q: Is this the correct/best/only way to get static row references?


3) After poking around in other posts here, I've altered my initial formula to reference row 4 (instead of row 3) to have the alert fire after bar close.

Here is the updated formula: =AND(ID13.SG3@$4>0, SUM(ID9.SG1@$4:ID9.SG1@$8)>0)

I can watch the formula eval to 1 when conditions are met, but the alert sound fails to play.

Attached are 3 images: the study/alert settings, and the details of the formula eval showing that it evals to 1 (= TRUE).

Q: why won't the alert sound play?


4) Does there exist anything like a "theory of operation" overview document or block diagram type of thing for SC spreadsheet studies? The documentation on the SC site is expansive and I'm sure comprehensive, but I find that it assumes a lot and is rather disjointed and scattered all over the place.

Q: Does anyone know of an "architectural overview" or "theory of operation" doc for how spreadsheet studies and systems fit into the overall SC platform architecture?

Thank you all for any guidance you can provide.

1. Each row is a chart bar, where the current bar is in row 3, and earlier bars in rows below.
All formulas in Formula Columns must be entered in row 3, and rows below are auto-populated to show close-of-bar values of those previous bars.

2. There is no need to force an absolute value to all rows because the realtime alert only occurs within, or at the close, of the current bar, which is row 3.

3. With 'Signal Only On Bar Close' set to No, you need to reference row 4 to get an alert at close of bar.
With 'Signal Only On Bar Close' set to Yes, you need to reference row 3 to get an alert at close of bar.
Either way works the same for close of bar alerts, but the latter is less confusing for most, and it allows intrabar alerts.

4. Alerts will only play from columns K-N. Ignore the default names for these columns. All four function the same.
If you need more than 4 distinct alerts, a workaround is to reference the spreadsheet sugbraph on the Alerts tab of any other study.
For example, to get an alert from column O, put =ID1.SG5 on the alerts tab of any other study (where the spreadsheet study is ID1).

tomgilb 

1. Each row is a chart bar, where the current bar is in row 3, and earlier bars in rows below. All formulas in Formula Columns must be entered in row 3, and rows below are auto-populated to show close-of-bar values of those previous bars. 2. There is no need to force an absolute value to all rows because the realtime alert only occurs within, or at the close, of the current bar, which is row 3. 3. With 'Signal Only On Bar Close' set to No, you need to reference row 4 to get an alert at close of bar. With 'Signal Only On Bar Close' set to Yes, you need to reference row 3 to get an alert at close of bar. Either way works the same for close of bar alerts, but the latter is less confusing for most, and it allows intrabar alerts. 4. Alerts will only play from columns K-N. Ignore the default names for these columns. All four function the same. If you need more than 4 distinct alerts, a workaround is to reference the spreadsheet sugbraph on the Alerts tab of any other study. For example, to get an alert from column O, put =ID1.SG5 on the alerts tab of any other study (where the spreadsheet study is ID1).

----
Wow - this has saved me a lot of wasted cycles! Thank you for clarifying all this. I moved my (total of) 4 conditions to cols K - N and the alerts are firing as expected.

To clarify Item 4... so cols O - Z act as subgraphs of the actual spreadsheet study? But they can hold a formula and yield a result (boolean, integer, float)? (I realized that I was missing the conceptual connection of a spreadsheet study being just another "study" like any of the built-ins in SC.)

If that is the case, how should I interpret the alert sound settings for the spreadsheet study columns O - Z in the study settings for the spreadsheet study itself?

Thank you very much!

Each Formula Column is a subgraph of the spreadsheet study. There are a min of 16 and a max of 60 Formula Columns, set with the 'Number of Formula Columns' setting. So subgraphs 1-60 correspond to columns K-BR.

When I test this again, the alert assignments for columns O-Z do sound an alert. Not sure why they didn't before, for both you and me.
Makes sense since there are options to use them. (I've never needed to use those columns for alerts.)
So the workaround I mentioned before isn't necessary, unless you want to delineate the alerts for columns V-Z and AA-BR.

Columns K-N will plot markers at the high or low of the bar when TRUE. (In this regard, all 4 don't function the same.) Any non-zero is a TRUE.
Columns O-Z...BR will need to return a value (price) to place a marker at the correct location, because it will plot the marker at the price value of 1. This is the case unless you use Color Bar or Background, which only require a TRUE. But any non-zero will sound an audible alert.
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php

OK. Armed with the pointers you gave me, I started from scratch (new chartbook, only 2 indicators) and put the alerts in K,L,M,N. I was able to get things working the way I wanted, but man, between top-level study settings and subgraphs, there is a lot that needs to be just so for the stars to align!

Thank you again for your help. I now have a much better overall understanding of how these spreadsheet studies fit into the overall platform landscape of chartbooks>charts>studies>spreadsheets.