Pivot Points for trading scan question

[200ASX]

Hi there,

I have been using excel for basic calculations for years and now I`d like to do some further investigation with the information I`ve collected.
The pivot points are calculated each day using the OHLC, and I use them as potential profit targets for the next day/ next couple of days.
I`d like to be able to scan my information to find out how often the S1,S2,S3 and R1,R2 and R3 levels are hit using the prior days OHLC levels.

I don't know where to start, I think I`ve given all the information that you might need, just looking for someone to point me in the right direction.

Thanks in advance!!!

[sourabhg98]

Hey there,

Welcome to this forum!!

Can you please provide some examples.

Also manually fill in the desired results so that we can understand the situations better.

[200ASX]

This example shows the pivots calculated on 2/12/2013 for the 3/12/2013

[

As you can see the S2 pivot of 5198 came into play as the low of the day on 3/12/2013 was 5190
[COLOR="#0000FF"][/I know that's an 8 point difference but pivot points are not an exact science COLOR]

EDIT
Sorry those screen caps didn`t work, I`ll try posting again later tonight, with more of an explanation of what it is that `I'm trying to acheive

[200ASX]

This example shows the pivots calculated on 2/12/2013 for the 3/12/2013

Excel forum 1.jpg

Excel forum 2.jpg

As you can see the S2 pivot of 5198 came into play as the low of the day on 3/12/2013 was 5190

I know that's an 8 point difference but pivot points are not an exact science.

I would like to be able to do a scan for monthly and yearly spreadsheets but just using this monthly (at the moment) spreadsheet can I create a scan that will search through the month of data if I`m looking for how many times was the S2 pivot reached on the current day using the previous day`s S2 pivot point. And so on for the other pivot point levels S1,S3,R1,R2 and R3.
Obviously I would just substitute S2 in the formula with whatever scan I wanted to do

[200ASX]

I hope that gives you more of an idea of what I`m talking about, if you need more information, just let me know?

[sourabhg98]

Okay, so you want to create a scan that would check whether all the pivots S1, S2, S3 and R1, R2, R3 were between the highs and lows of the previous day?

For example- In cell L4 you have 5100. Also on the next day the high was 5160 and the low was 5113. So, now we see that 5100 is not between 5113 and 5160 so you do not want to count that. But in cell K4 you have 5149 which is between 5113 and 5160, so you want to count that.

Is this what you are trying to achieve for all the pivots?

[200ASX]

Yes that is correct,

I would like to run separate scans for all the different pivot points over the same sample set of data to get a better understanding (in percentage terms) of which pivots are hit and how frequently.

Thanks heaps sourabhg98 for your interest in my question!!!

[sourabhg98]

Hi,
Sorry, I just wasn't getting earlier what you needed exactly.

So, now I have made this macro-

Please Login or Register  to view this content.

Check attached.

I think this was what you were trying to accomplish. (I haven't checked the results yet)
If not do tell me if I made any mistakes.

Regards

[200ASX]

Excel forum scan.jpg

Thanks again for your help, I appreciate the effort that your putting into my question!!!

I have used your scan and even though I don't completely understand the formula that you are using I do like that its scanning the C&D columns to see if the pivots points are present. However the results from your scan show
S1-11
S2-0
S3-0
R1-10
R2-0
R3-0

I have manually gone through the spreadsheet that I have given you, and the results that I can see are
S1-7
S2-3
S3-0
R1-8
R2-4
R3-0

As I went through the spreadsheet and checked manually, my findings are the following:

Row 2: -
Row 3: -
Row 4: S1
Row 5: S2
Row 6: R1 & R2
Row 7: S1
Row 8: S1
Row 9: S1 & S2
Row 10: -
Row 11: S2
Row 12: R1
Row 13: R1 & R2
Row 14: R1
Row 15: S1
Row 16: -
Row 17: S1 & R1
Row 18: R1
Row 19: R2
Row 20: R1 & R2
Row 21: R1
Row 22: S1

I hope this gives you a better understanding of what I`m trying to achieve.

[sourabhg98]

Oops, I am sorry. I forgot that we have to see the Highs and Lows of the next day. This was calculating it for the same day.

In your result you did a small mistake. In Row 4 that should be S2 instead of S1.
So results are as follows-
S1-6
S2-4
S3-0
R1-8
R2-4
R3-0

If your are fine with that old macro just make these changes in it-

Please Login or Register  to view this content.

However, I made some improvements in the old macro.
Just add a new sheet named "Results" and it gives you detailed report of "Hit"(s).

Check attached.

[200ASX]

Thanks heaps sourabhg98!!!

That looks impressive, I don't have any time to play around with it today and I`m sure I will have more questions for you regarding applying this to all the other spreadsheets that I have.

Thanks again. I should be able to test it out tomorrow night.