Following my Balance To Balance And Composite Logic post I did a study to test the validity of the idea that a rejection of yesterday’s VPOC in the ES might lead to a retest of the developing VPOC at the point of test. More importantly, I wanted to demonstrate some simple Excel techniques. In this post I’ll try to outline what I’ve done in principle and then follow it up with a video at a later date.

What I’m looking at

The simple criteria for a “test” are that price must test to within 0.5 points of YVPOC and then not exceed it by more than 6 ticks before returning to within 2 ticks of the DevVPOC at the point of test. Simple huh?

Before going any further at all, I’d point out that there are often many ways to do the same thing in Excel and if you know of a much simpler, faster way to do what I have done then please let me know! J With this said, I find that the keys to successfully completing studies are: –

• knowing some simple formulae
• a basic understanding of pivot tables
• careful and logical process-orientated steps
• a little bit of knowledge of massively time-saving keyboard shortcuts
• a lot of patience/endeavor

Yesterday VPOC Rejection Excel Analysis Steps

So the steps I used to create this were broadly as follow: –

1. Export 1min data for SESST indicator and instrument data – it’s probably better after consultation with Chad, to use a CI and the “PROF” token.
2. Import the data into Excel and format it neatly!
3. You could either import another SESST indicator for yesterday’s VPOC, but I just took the last value from the DevVPOC of the prior day.
4. Check to see if yesterday’s VPOC tested to within a certain tolerance – I’ll use +/- 0.50 points for ES.

“Whoa there!” I hear you say. That last step is the one which is crucial and the most complicated of all the others. I’ll remind you of the 3^rd step in the keys above. I will go over these in the video, but for now realize that for a large part, this specific study used a lot of “nested ifs”.

OMG Nested Ifs!

An “IF” statement in Excel is simply “check if something is true/if it is, return a value of x/if it isn’t, then return a value of y”. But the values can be formulae in themselves. So you can have an if of an if or an if of an if of an if on both branches of the original if! This is about simple logic, but when you’re tired and hypnotized by the numbers, it can become pretty complicated to decipher – especially when things go wrong. When they do, I use a simple technique. Now don’t laugh but here it is: –

The point is that I’m trying to check I have all the syntax correctly input.

Anyway, the results of this test were that exactly 2/3 of the time, the developing VPOC at point of the YVPOC test was retested before the YVPOC was broken by more than 6 ticks. What a stat! However, the reason I added the “Distance DevVPOC at 1st Test of YVPOC from YVPOC” column was to identify how far this retest was likely to be when it did happen – and unsurprisingly, the average was pretty low. In this sample set (which was intentionally  small), the average distance was 2 points. This means that taking into account the 2 tick tolerance for each VPOC test, the distance was as low as 1 point. Not so useful now!! However, this study didn’t do a couple of important things. One is that it didn’t register instances of non-retested DevVPOC’s where the reversal was large but the distance between the two VPOC’s was larger. Two is that it didn’t take into account any additional movement beyond the DevVPOC retest – it was purely a digital “test” or “no test” result.

The beautiful part of studies is that to the open-minded, even those which yield not especially useful results might just lead to something rather more intriguing. A good idea for a follow up study to this one would be to test how far price moves away from YVPOC before breaking that 1.5 point barrier. I hope this has whet your appetite for the video which will do some exploration of the spreadsheet in question!