Comparative Quantiles? Formula for excel- Anyone understand?

Messages
6
Likes
0
I am reading Clive Corcorans book Long/Short Market dynamics and am trying to follow along with some of the examples quoted.
On Page 45 is the first Case study of how the CQA method is implemented in a spreadsheet. However While its says how each parameter is calculated in a wordy sort of way its not especially clear to me what I am to actually do for one of the parameters- Does anyone know or have an example spreadsheet withthe correct formulae already put in?
Hopefully its OK to quote here:
I have a spreadsheet with the Date, OHLCV data for a share
These are values from rows A2,B2,C2,D2,E2 and F2 respectively
The next is to work out the Upper Quartile Closing bias for the 20 previous periods, so extrapolating the OHLC data down through the sheet with date at line 21 I can do the first calc based on a 20 period lag:: in G21 I have =PERCENTILE(E2:E21,0.75) (this calcs the upper quartile on the previous 20 sessions (OK so far think)
The next column (H) calculates the Lower quantile of the closing bias. (using the 25% percentile) So this I presume starts a further 20 lines further on at H40 and has the following formula: =PERCENTILE(G21:G40,0.25) (working out the lower quartile of the UQ Closing bias) I think I follow this so far.
The next column (I) is a register: Set to 1 under a condition : "determine whether the closing price falls above the UQ close or not. If it does then a positive occurence is registered...." So I have compared that sessions closing price (Col E) to that sessions closing bias in column G, thus : =IF(E40>G40,1,0)
The next bit though has me stuck. Any idea what this means?
"Similarly for the closing bias in each session, it needs to be determined whether the closing bias is in the lower quantile of values or not. If it is the positive occurence is also registered separately otherwise left blank"- or zero I guess. I am not sure what I have to work out for this statement though. Anyone have an idea? Would have been nice for the book to have a website with the examples already in spreadsheets or an explicit description of the calculations like in Dr Elder's books.

Thanks if you persevered even reading this far!!! :D

Kenny
 
Top