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: –
- 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.
- Import the data into Excel and format it neatly!
- 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.
- 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 3rd 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!
plz share the excel file
Hi Jiang – if you subscribe to the Traderunner Newsletter on the form above (even if you are already subscribed) and make sure that you confirm your subscription when the confirmation email comes through, you will be sent a welcome email with the link to download the spreadsheet.
hI,I have comfirmed my subcribed email.
I’m just the process of switching email list providers, so if you could please sign up on the grey form on this page that states “Sign up to the Traderunner.pro Newsletter and receive a copy of the Excel spreadsheet in this article for FREE!” then confirm your email, you’ll be sent the file. If that doesn’t happen for some reason, please send me a message via the form at https://www.traderunner.pro/about/
Try again to subcribe newsletter and receive the excel file
You should have been sent it now.
thanks I have received the file.
Hello, it’s possible to receive the Harmonic rotations spreadsheet? When I try to subscribe it says unhautorized
Hi Mauro. The signup form has been fixed if you would like to give it another try.