Home >> Support Board

Support Board

Date/Time: Sat, 20 Apr 2024 10:03:54 +0000

How to Post a Message for Support

[User Discussion] - VWAP FORMULA FOR SPREADSHEET

View Count: 955

[2021-06-01 17:25:44] #1 [Go To First Post]
Hopeman - Posts: 156
Hi! I try to do a spreadsheet formula with VWAP I need knows if i can put formula buy entry or sell entry if price is adobe or below to VWAP thanks a lot

[2021-06-01 23:13:34] #2 [Go To First Post]
Sawtooth - Posts: 3976
Try this: Cell K3: =CROSSFROMBELOW(E3:E4,ID1.SG1@3:ID1.SG1@4) Cell M3: =CROSSFROMABOVE(E3:E4,ID1.SG1@3:ID1.SG1@4) where the VWAP study is ID1; edit as needed. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#CROSSFROMABOVE_Function

[2021-06-02 16:36:22] #3 [Go To First Post]
Hopeman - Posts: 156
Thanks, I have another question, would you be so kind to help me? if for example I want to superimpose another study from another cell, how should I attach it? For example, would cell AE1 + AA look something like this? = CROSSFROMBELOW (E3: E4, ID4.SG1 @ 3: ID4.SG1@4) + (AE1 = AA)

[2021-06-02 18:27:40] #4 [Go To First Post]
Sawtooth - Posts: 3976
The correct syntax would look like this: =AND(CROSSFROMBELOW(E3:E4,ID4.SG1@3:ID4.SG1@4),AE1=AA)

[2021-06-02 18:37:37] #5 [Go To First Post]
Hopeman - Posts: 156
okay! Thank you very much, Sorry I came up with this at the last minute, if it were a conditional, would I have to add IF? look something like this? =AND(CROSSFROMBELOW(E3:E4,ID4.SG1@3:ID4.SG1@4)IF(AE1=AA) It would be if it crosses below or above if and if this condition of boxes (AE1=AA) is met

[2021-06-02 20:08:54] #6 [Go To First Post]
Sawtooth - Posts: 3976
If the result of the formula is TRUE/FALSE, or 1/0, you don't need to use the IF function. You only need to use the IF function when the result is a value. =AND(CROSSFROMBELOW(E3:E4,ID4.SG1@3:ID4.SG1@4),AE1=AA) is the same as =IF(AND(CROSSFROMBELOW(E3:E4,ID4.SG1@3:ID4.SG1@4),AE1=AA),1,0) Both formulas would return 1 or 0 when the Last price crosses the VWAP, and AE1 is already equal to AA, compared to the previous bar. You can get an idea of spreadsheet syntax from looking at the available functions: Spreadsheet Functions

[2021-06-03 19:16:03] #7 [Go To First Post]
Hopeman - Posts: 156
Thanks a lot

[2021-07-03 18:17:33] #9 [Go To First Post]
Hopeman - Posts: 156
Hello again! I am making a GAP and HALF GAP formula which calculates the gap and a half gap from the closing and opening of the current day, the formula for the gap = (ID0.SG4@4) and for the HALF GAP = ( ID0.SG4 @ 4 + ID0.SG1 @ 3) / 2 the problem is that the formula only applies when the GAP will be above the price and not below surely I am doing wrong, could you help me to make the calculation correctly! Thank you


Gap Fail.png / V - Attached On 2021-07-03 18:17:31 UTC - Size: 38.32 KB - 148 views

[2021-07-03 19:21:36] #10 [Go To First Post]
Sawtooth - Posts: 3976
For the Gap line, do you want to plot the value of the previous RTH close? For the Half Gap line, do you want to plot the midpoint between the previous RTH Close and the current RTH Open? What is the time of the previous RTH Close you are using for the Gap? Please post a pic of your chart's Chart Settings >> Main Settings tab

[2021-07-03 20:01:12] #11 [Go To First Post]
Hopeman - Posts: 156
For the Gap line, do you want to plot the value of the previous RTH close? - YES For the Half Gap line, do you want to plot the midpoint between the previous RTH Close and the current RTH Open? - YES What is the time of the previous RTH Close you are using for the Gap? What is the time of the previous RTH Close you are using for the Gap? 22:14:59


Gap .png / V - Attached On 2021-07-03 20:01:09 UTC - Size: 44.95 KB - 151 views

[2021-07-03 21:13:38] #12 [Go To First Post]
Sawtooth - Posts: 3976
To extract the previous RTH Close, try this: Cell R3: =IF(MROUND(FRACTIME(A3),1/86400)=TIMEVALUE("22:15:00"),E4,R4) This will round the Time of the DateTime to 1 second, and compare it to the timevalue of 22:15:00. If they are equal, it will persist the Close of the previous bar until the next 22:15:00. This is the Gap line. Set the Draw Style to Dash. To calculate the Half Gap, use two more columns: Cell S3: =IF(MROUND(FRACTIME(A3),1/86400)=TIMEVALUE("15:30:00"),B3,IF(MROUND(FRACTIME(A3),1/86400)=TIMEVALUE("22:15:00"),0,S4)) This will extract the RTH Open and make it persistent until the RTH Close. Set the Draw Style to Hidden or Ignore. Cell T3: =IF(S3>0,AVERAGE(R3,S3),0) This is the Half Gap line. Set the Draw Style to Dash.

[2021-07-04 06:55:00] #13 [Go To First Post]
Hopeman - Posts: 156
Now it works fine Sir !! but I can't understand why it doesn't work with a simple form if I change the seconds to the exact time which do you think it might work fine? Thank you so much Date Time Of Last Edit: 2021-07-04 10:44:08


Gap Solved.png / V - Attached On 2021-07-04 06:54:51 UTC - Size: 71.35 KB - 147 views

[2021-07-04 11:35:02] #14 [Go To First Post]
Sawtooth - Posts: 3976
Sometimes you have to round the time to remove floating point errors. Using the Spreadsheet Study: Imprecision of Floating-Point Numbers and Comparisons Working with Spreadsheets: Floating-Point Values and Comparisons When Using the Spreadsheet Study Spreadsheet Functions: Serial DateTime Values

[2021-07-04 16:37:07] #15 [Go To First Post]
Hopeman - Posts: 156
Thanks for your support!

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