Home >> Support Board

Support Board

Date/Time: Thu, 02 May 2024 13:05:49 +0000

How to Post a Message for Support

[User Discussion] - Help with spreadsheet study. Did not find similar question on the boards

View Count: 682

[2021-09-08 10:29:48] #1 [Go To First Post]
VolTrader73 - Posts: 133
Hello. Im not sure how to post and where but I need help with this question I posted https://www.sierrachart.com/SupportBoard.php?ThreadID=66790 Maybe I posted it wrong as I got no reply and help. Basically, I have both spreadsheet and depth of market studies on the chart. The spreadsheet populates the market depth correctly in columns BC through BF. I have the market depth size ratio in cell K3 (simple formula referencing the BE and BC columns containing the first 10 levels of sizes). Problem: when charting it, the values fluctuates and changes values also backwards as the K column populates K4-12 with levels from the book which fill the chart array but continue to fluctuate. I want to plot on a subgraph a candle of the K3 cell only (each candle interval on the chart will show the K3 o/h/l/c values for the interval and so the history of that value on the chart will be correct. This is so basic yet I did not find any post about this and how to make the chart use only the K3 cell and display its history correctly. Just like the Bid & Ask depth bars study set to RATIO (btw, both the current and "old" B&A depth bars studies and value of K3 are not the same even though i use the same formula for the ratio. They resemble but not the same). Please advise. Thank you Date Time Of Last Edit: 2021-09-08 10:48:27

[2021-09-08 16:43:17] #2 [Go To First Post]
John - SC Support - Posts: 31293
The Spreadsheet Study always fills all the data down the column based on what is entered in the cell in row 3. Therefore, if you do not want any other information to be displayed, then you would need to add an IF statement in which one of the conditions is that the row is 3. Refer to the ROW() function here: https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#ROW_Function For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing

[2021-09-09 00:17:36] #3 [Go To First Post]
VolTrader73 - Posts: 133
Thank you for the reply but I dont understand. I should add this row(3) condition in the K3 cell like this ?=if(row(3),(sum(BE3:BE12)-sum(BC3:BC12))/(sum(BE3:BE12)+sum(BC3:BC12)),0) it results in #value all K column. Again, Im just trying to have a similar plot like the bid&ask depth bar study while using only 1 cell that has the ratio and that should provide all data needed to have the plot on the chart.. Nothing else. Please advise

[2021-09-09 12:42:57] #4 [Go To First Post]
Sawtooth - Posts: 3990
This is the correct syntax: =if(row()=3,(sum(BE3:BE12)-sum(BC3:BC12))/(sum(BE3:BE12)+sum(BC3:BC12)),0) If you are trying to create a OHLC bar, you'll need 4 values, only in columns K,L,M,N, using this setting to Yes: Spreadsheet Study Inputs: Use Price Graph Style

[2021-09-09 14:36:47] #5 [Go To First Post]
VolTrader73 - Posts: 133
Hello. thank you for the help. It is now updating only from the 3 row in all 4 columns but (and this is still the main problem), the chart plot now has no history as if it holds none unless it comes from the spread sheet. Cant the chart populate its own array ? should I configure something in the spreadsheet study ? Also, the only option I have for the OHLC plot in the subgraph settings is bar (but its a histogram type not a candle) in the drop down menu under draw style as the top "Graph draw type" is gray shaded "custom" and I cant change it. Please advise. Date Time Of Last Edit: 2021-09-09 14:43:46

[2021-09-09 15:08:15] #6 [Go To First Post]
Sawtooth - Posts: 3990
Each spreadsheet row is a chart bar, with the current bar in row 3 and earlier bars in rows below. If you want historical bars, you cannot limit the plot to only row 3. With 'Use Price Graph Style' set to Yes, you should get this on the Subgraphs tab: See pic.


OHLC.PNG / V - Attached On 2021-09-09 15:06:19 UTC - Size: 44.62 KB - 153 views

[2021-09-09 21:25:28] #7 [Go To First Post]
VolTrader73 - Posts: 133
Thank you for both answers. To finalize and make sure I clearly understand:, there is no way for the chart to populate its own real time array coming only from row 3 or K3 cell? Any other study maybe ? This seems so basic so I want to make sure Im explaining my self correctly and not missing any other option. Date Time Of Last Edit: 2021-09-09 21:28:01

[2021-09-09 21:37:18] #8 [Go To First Post]
Sawtooth - Posts: 3990
Do you want to create an OHLC bar? Do you want previous bars to also display?

[2021-09-10 04:41:39] #9 [Go To First Post]
VolTrader73 - Posts: 133
Yes to both. Id like to create my own Bid & Ask Depth bars exactly like you have as a build in study using the "Ratio" mode. Using only the first 10 price levels size or less as described in the full text of your study bellow. Bid & Ask Depth Bars I want to use different ratios and manipulate things with my own calculations based on the top 10 or less levels. Maybe there is a way to do this that Im not aware of. Please advise Date Time Of Last Edit: 2021-09-10 04:49:07

[2021-09-10 12:45:10] #10 [Go To First Post]
Sawtooth - Posts: 3990
Note: I'm not SC Support. I am only a user. Try this: - Set 'Use Price Graph Style' to Yes. This plots OHLC candlestick bars from values in columns K-N where: Column K is the Open Column L is the High Column M is the Low Column N is the Close - Set Subgraph colors: K/SG1: Green L/SG2: Light Green M/SG3: Red N/SG4: Light Red - Formulas on the spreadsheet: K3: =OFFSET(N3,9,0) L3: =MAX(N3:N12) M3: =MIN(N3:N12) N3: =(SUM(BE3:BE12)-SUM(BC3:BC12))/(SUM(BE3:BE12)+SUM(BC3:BC12)) K3 is the Open of the 10 bar array because it is the first bar of the 10 bar offset of the Close. (The OFFSET function is not inclusive, so 9 gets the 10th bar.) L3 is the High of the 10 bar array because it is the max of the array. M3 is the Low of the 10 bar array because it is the min of the array. N3 is the Close of the 10 bar array because it is the current bar. Date Time Of Last Edit: 2021-09-10 14:36:35

[2021-09-10 15:55:38] #11 [Go To First Post]
VolTrader73 - Posts: 133
Hello. Thank you for the help. This seems to be in the right direction but again the history of the bars change because the spreadsheet populates 1 row down and as such it refers to less and less of the top 10 book levels (cells K3-N3 refer to rows 3-12 which are the 10 price levels of the Bid/Ask, but then: cells K4-N4 refers to rows 4-13 ... and cells K12-N12 refer to rows 12-21. Due to this, each row not only calculates a wrong ratio but also continues to change "in the past" of the last 10 bars on the chart. I have only the 10 top levels configured from the data feed (top inside bid levels in column BC3-BC12 and top inside ask levels in columns BE3-BE12). To correctly plot the candles on the chart there should be only N3 ratio to refer sample as only it refers to the 10 book levels and holds the correct ratio. Please advise. Date Time Of Last Edit: 2021-09-10 15:59:19**

[2021-09-10 17:53:11] #13 [Go To First Post]
Sawtooth - Posts: 3990
The formulas I offered work with the Ask Volume and Bid Volume studies, because they are aligned with each chart bar. All of the Depth of Market Data study output levels are current values, displayed in rows that are not aligned with chart bars. The spreadsheet study can only calculate the ratio of 10 depth levels at the current OHLC bar. Only the values in row 3 are accurate, so only the current bar is accurate, as you note. The spreadsheet study cannot retain and plot older OHLC bars like the Bid And Ask Depth Bars study does. IOW, you can't do what you want with a spreadsheet study. You would need to create a custom ACSIL study. Date Time Of Last Edit: 2021-09-10 18:19:49

[2021-09-10 19:59:04] #14 [Go To First Post]
VolTrader73 - Posts: 133
OK. Thank you very much both Tom and John.

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