Support Board
Date/Time: Thu, 27 Feb 2025 09:05:22 +0000
[User Discussion] - VWAP FORMULA FOR SPREADSHEET
View Count: 1158
| [2021-06-01 17:25:44] |
| Hopeman - Posts: 166 |
|
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] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
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] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
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] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
Thanks a lot
|
| [2021-07-03 18:17:33] |
| Hopeman - Posts: 166 |
|
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 - 184 views |
| [2021-07-03 19:21:36] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
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 |
| [2021-07-03 21:13:38] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
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 - 182 views |
| [2021-07-04 11:35:02] |
| Sawtooth - Posts: 4176 |
|
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] |
| Hopeman - Posts: 166 |
|
Thanks for your support!
|
To post a message in this thread, you need to log in with your Sierra Chart account: