Support Board
Date/Time: Wed, 05 Feb 2025 13:01:36 +0000
[User Discussion] - Spreadsheet Rolling Pivot - Session End Time
View Count: 2676
| [2014-11-19 17:41:42] |
| OneMoreTick - Posts: 79 |
|
I created a 3 day rolling pivot study in spreadsheets so that the high and low bands are based on the prior 3 day's data. However, the bands don't shift at the session end time. In the screenshot, the session end time is marked by the vertical lines. How do I make it shift at the session end time? The study in this chart is overlayed from a daily chart that has the spreadsheet study. The session times are the same on this 60min chart and the daily chart. http://www.sierrachart.com/image.php?l=1416418215508.png |
| [2014-11-19 19:59:39] |
| Sawtooth - Posts: 4167 |
|
Have a look at this: http://www.sawtoothtrade.com/free-stuff-3.html Date Time Of Last Edit: 2015-08-14 02:12:19
|
| [2014-11-20 03:42:00] |
| OneMoreTick - Posts: 79 |
|
I did before, but couldn't get it working. I created a new chartbook, added a daily intraday chart and added the Dilernia study. All of the formula results in the spreadsheet say #VALUE! or #SYNTAX!. I've attached the screenshot.
|
 dilernia.jpg / V - Attached On 2014-11-20 03:38:59 UTC - Size: 960.44 KB - 492 views |
| [2014-11-20 05:41:16] |
| Sawtooth - Posts: 4167 |
|
This works fine in the Old Spreadsheets version, but not in the New Spreadsheets version. Some of the spreadsheet functions are not Excel compatible in the New version. Unfortunately the New Spreadsheets do not have the same depth of functionality, and this is a perfect example why I am disappointed the Spreadsheet Gear plugin is being replaced with an inferior (IMO) in-house version. Within a few months, I and many of my clients will be forced to use a soon-to-be unsupported older version of Sierra Chart. :( http://www.sawtoothtrade.com/blog-1.html Date Time Of Last Edit: 2015-08-14 02:12:58
|
| [2014-11-20 06:41:26] |
| OneMoreTick - Posts: 79 |
|
Ahh, ok. I installed the old spreadsheets version and it's running on there but I'm still having the same issue as I originally mentioned. The pivots don't shift at the session end time, they shift in the middle of the session. In the screenshot, the session end time is marked by the vertical lines. http://www.sierrachart.com/image.php?l=1416465471131.png |
| [2014-11-20 14:01:17] |
| Sawtooth - Posts: 4167 |
|
Use this formula in K3 instead: =IF(AND(A3-INT(A3)>=$J$75,A4-INT(A4)<$J$75),K4+1,K4) This will shift the lines at the Session Start time. The Session End time is in J76. Edit the formula if you'd rather use it. |
| [2014-11-24 18:00:51] |
| OneMoreTick - Posts: 79 |
|
I'm trying to get this working on a monthly chart but the "chart start" value in J75 is 0, so when I use that formula, all the K values change to 0.
|
| [2014-11-24 19:34:05] |
| Sawtooth - Posts: 4167 |
|
The formula in post #6 will only work on an Intraday Chart. Use the original K3 formula on Historical charts: =IF(INT(A3)>INT(A4),K4+1,K4) Date Time Of Last Edit: 2014-11-24 19:34:33
|
| [2014-11-24 20:11:27] |
| OneMoreTick - Posts: 79 |
|
Using the original formula, the pivots shift in the middle of the month instead at the end of the month.
|
| [2014-11-24 22:06:22] |
| Sawtooth - Posts: 4167 |
|
Are you using Intraday data to build a 'monthly' chart?
|
| [2014-11-24 22:12:25] |
| OneMoreTick - Posts: 79 |
|
Nope, it's set to Historical chart.
|
| [2014-11-24 22:57:52] |
| Sawtooth - Posts: 4167 |
|
An Historical chart, set to Monthly, has bar date stamps of the first trading day of the month. It is not possible for the lines to shift in the middle of the month. The crosshairs show at the center of the bar width, but the date stamp owns the entire bar width. I think the Draw Style you are using is confusing you. Try using Dash instead of Stair Step. |
| [2014-11-24 23:59:53] |
| OneMoreTick - Posts: 79 |
|
The dates on the spreadsheet are all the start of the months, but it's shifting in the middle. Here it is overlayed onto a daily chart. http://www.sierrachart.com/image.php?l=1416873416990.png |
| [2014-11-25 00:15:48] |
| Sawtooth - Posts: 4167 |
|
In the Study/Price Overlay study, set the Bar Time Matching Method to Containing Match.
|
| [2014-12-01 01:03:23] |
| OneMoreTick - Posts: 79 |
|
Thanks, that fixed the pivot shift. I'm confused by the formula though. The period close references the close from two periods back and not the prior period close. For example, for the 3 month pivot for Aug through Oct, wouldn't we want to reference the close of Oct? It references the close of Sept.
Date Time Of Last Edit: 2014-12-01 01:15:46
|
| [2014-12-01 01:59:14] |
| Sawtooth - Posts: 4167 |
|
The standard pivot formula looks at the previous period's HLC, and plots the calculated values at the current period. If the period length is 3, the formulas will find the combined HLC of the 3 previous bars, and plot the values at the current bar. You can edit the formulas in S3, T3, U3 removing the -1 after the K3 in the MATCH function so that the current period and two previous periods are used in the calculation, but before you do, manually calculate the values to verify this is what you want. Date Time Of Last Edit: 2014-12-01 19:57:53
|
| [2014-12-01 14:54:18] |
| OneMoreTick - Posts: 79 |
|
Ok, I started from scratch and have the pivots I'd like to see now, except this time the pivots are shifting on the 2nd or 3rd day of the month instead of the 1st. I have Bar Time Matching Method set to Containing Match. Any thoughts? EDIT: Disregard this, the displacement setting was set to 2 shifting everything over Date Time Of Last Edit: 2014-12-01 15:40:27
|
| [2014-12-02 02:46:08] |
| OneMoreTick - Posts: 79 |
|
Using a daily intraday chart to do a 3-day pivot, I tried using this formula in K3: =IF(AND(A3-INT(A3)>=$J$75,A4-INT(A4)<$J$75),K4+1,K4) But it just sets every K field to "1" all the way down. Date Time Of Last Edit: 2014-12-02 03:03:15
|
| [2014-12-02 05:15:09] |
| Sawtooth - Posts: 4167 |
|
=IF(AND(A3-INT(A3)>=$J$75,A4-INT(A4)<$J$75),K4+1,K4) can only be used on an Intraday chart =IF(INT(A3)>INT(A4),K4+1,K4) must be used on an Historical chart, or on an Intraday chart when you want the lines to change at midnight. |
| [2014-12-02 05:27:10] |
| OneMoreTick - Posts: 79 |
|
Yes, I tried the top formula on an intraday chart, but it sets the K cell to 1 in every row.
Date Time Of Last Edit: 2014-12-02 05:27:28
|
| [2014-12-02 13:34:17] |
| Sawtooth - Posts: 4167 |
|
Yes, I tried the top formula on an intraday chart, but it sets the K cell to 1 in every row.
That would mean one or both of the these: 1) You need more spreadsheet rows to allow for more than one day of data (each spreadsheet row is a chart price bar). Increase the 'Number of Rows' in the spreadsheet settings. 2) You need more than one day of data to load. Increase the Chart Settings' number of 'Days to Load' to a value greater than 1. |
| [2014-12-02 13:53:38] |
| OneMoreTick - Posts: 79 |
|
Both requirements are met, but still getting the 1's in each row. See attached. Also, I made the formula change in sheet1 and it then carries over to sheet11, since chart#11 has the study.
|
| dilernia.jpg / V - Attached On 2014-12-02 13:50:30 UTC - Size: 348.68 KB - 446 views |
| [2014-12-02 14:37:30] |
| Sawtooth - Posts: 4167 |
|
OK, try this in K3 for any chart type, any session times: =IF(OR(AND($J$75>$J$76,A3-INT(A3)>=$J$75,A4-INT(A4)<$J$75),AND($J$75<$J$76,INT(A3)>INT(A4))),K4+1,K4) |
| [2014-12-02 14:51:58] |
| OneMoreTick - Posts: 79 |
|
Same result :-(
|
| [2014-12-02 15:24:12] |
| Sawtooth - Posts: 4167 |
|
-Set the Formula Source Sheet Number to the same as the chart number where each spreadsheet study is applied, then copy/paste the entire Sheet1 to that respective sheet number. -Set the Chart Data Output Sheet Number to 0 on all of the spreadsheet studies. |
To post a message in this thread, you need to log in with your Sierra Chart account: