CODEX.QBT – MATRIX
STATISTICAL PARAMETRIC
EXCEL SEQUENCE EQUATIONS AND COMPUTATIONS
INPUT DATA MATRIX
Symbol 
Impl Vol

%Change

Close

Open

AG

0.61

0.03

10.8

11.06


STRIKE CALL

INTRINSIC

C PROB OTM

C PREMIUM $


10.00

0.82

0.34

1.00



1.20

0.04


BIDU

46.39%

2.19%

170.14

172.38


STRIKE CALL

INTRINSIC

C PROB OTM

C PREMIUM $


175

5.11

0.56

8.2

INTRINSIC IS THE DIFFERENCE BETWEEN THE UNDERLYING AND STRIKE PRICE FOR CALL CALL OPTIONS;
PUT OPTIONS IS THE DIFFERENCE BETWEEN THE STRIKE PRICE AND THE UNDERLYING
TGT: =SUM(HIGH PRICELOW PRICE)+OPEN
TGT 2: =SUM(OPEN PRICE – CLOSE PRICE)+LAST PRICE
INTRINSIC VALUE IS THE ACTUAL VALUE BASED ON AN UNDERLYING PERCEPTION OF ITS TRUE VALUE, BOTH TANGIBLE AND INTANGIBLE.
TGT

TGT

RANGE

11.59

11.42

0.79

P PROB OTM

P PREMIUM $

IV POP

0.65

0.35

0.48

0.04



CALL IMPLIED VOLATILITY PROBABILITY OF PROFIT
PUT IMPLIED VOLATILITY – PROBABILITY OF PROFIT
CALL IV POP: =SUM(STDEV IV – CLOSE)
PUT IV POP: = SUM(STDEV IV – PUT PREMIUM
C IV

C HV

OPT IV

STDEV IV

0.57

0.45

0.61

0.833

STANDARD DEVIATION EQUATION FOR IMPLIED VOLATILITY
=STDEV(C IV; C HV; OPT IV)*10
DELTA

THETA

GAMMA

VEGA

0.710

0.009

0.230

0.010

0.290

0.009

0.032

0.088

CASH DELTA
= DELTA* UNDERLYING PRICE * POSITION SIZE
THETA = 10,000 * 1 = 100;
MEASURE OF TIME DECAY FOR A ONE DAY TIME HORIZON;
EXTRAPOLATED OUT TO EXPIRATION
PROFIT/LOSS
CASH DELTA * SPOT CHANGE IN %; (CASH GAMMA * SPOT CHANGE IN %)/2;
THETA*NUMBER OF DAYS (USUALLY 1 EXCEPT FOR W/E;
VEGA*CHANGE IN IV
Q & P WORLD
(QUANTITATIVE – OPTIONS; PORTFOLIO – EQUITIES)
PRICE ACTION IS ESSENTIAL IN COMPARISON TO NET CHANGE. IT ACTS AS THE LEADING INDICATOR FOR INTRADAY PRICE MOVEMENT DIRECTION/REVERSAL
=SUM((LAST PRICEOPEN PRICE)+(LAST PRICEHIGH PRICE)+(LAST PRICELOW PRICE))/1.8
NET/PRICE MOVEMENT RATIO
=SUM(NET CHANGE/PRICE ACTION)
HV

STDEV

IV

0.91

0.96

1.17

HV (HISTORICAL VOLATILITY) IS COMPARED TO THE PRICE RANGE STANDARD DEVIATION AND THE IMPLIED VOLATILITY THAT SHOWS IF THE ASSET’S PRICE MOVEMENT IS VOLATILE – MEANING THE OPTION PREMIUMS WILL BE HIGHLY ACTIVE
ALPHA

BETA

EXP RTNS

0.42

0.31

0.08

ALPHA:
=SUM(RISK FREE RATE)+(EXP RETURNBENCHMARK)*(STDEV RETURN/STDEV MARK)
BETA:
=SQRT(EXP RETURNS)/ABS(HV)
IF BETA IS “2” IT WILL BE EXPECTED TO SIGNIFICALLY OUTPERFORM IF MARKET IS GOING UP, AND SIGNIFICANTLY UNDERPERFORM IF MARKET IS GOING DOWN.
IF BETA IS “1” THEN ASSET AND MARKET WILL GENERATE SIMILAR RETURNS OVER TIME
EXP RETURNS: =STDEV(IV;HV)*SQRT(DAYS/252)
BENCH: =SUM(CLOSE PRICE;OPENPRICE;LAST PRICE; HIGH PRICE; LOW PRICE)/5*0.01
STDEV RETURNS: =STDEV(PRICE ACTION;IV)
STDEV MARK: =STDEV(STDEV;BENCH)
BENCH

STDEV RTNS

STDEV MARK

0.64

0.27

0.23

LIST FOR BLACK SCHOLES CALCULATION
EXP MOVE

SD SQRT

1.87

0.77

EXP MOVE:
=LN(PIVOT PRICE)*0.45
SD SQRT:
=STDEV(OPEN PRICE;LAST PRICE)*SQRT(EXP MOVE)
NATURAL LOG:
=LN(EXP MOVE/SD SQRT)
EXPONENTIAL:
=EXP(LOG)
SKEW:
=SKEW(HV;STDEV;IV)/EXPONENTIAL
ALTERNATIVE:
=SKEW(STDEV;SQRT SD;LOG;EXP)
DAILY %:
=SUM((PRICE ACTION)*0.1/SQRT(DAYS/252)
ADD: MEAN AND VARIANCE
PIVOT PRICE:
=AVERAGE(PRICE SERIES)
OR
=AVERAGE(OPEN;LAST;HIGH;LOW PRICES)
PIVOT PRICE:
=AVERAGE(PRICE SERIES)
OR
=AVERAGE(OPEN;LAST;HIGH;LOW PRICES)
=SUM(HIGH PRICE TGTHIGH PRICE)*1/HIGH PRICE TGT
=SUM(OPEN+EXPONENTIAL)
=SUM(HIGH PRICE TARGETSD SQRT)
INT: =SUM(LAST PRICE – STDEV)+IV
EXT: =SUM(LAST PRICE – INTRINSIC)
=STDEV(CLOSE;OPEN;LAST PRICE SERIES)
SV

IV

VOL

#N/A

#N/A

#DIV/0!

STATISTICAL VOLATILITY
=AVEDEV(CLOSE;OPEN;LAST;HIGH;LOW)^0.314
TO FIND IMPLIED VOLATILITY RANK (INTRADAY)
=STDEV(OPEN, HIGH, LOW, LAST PRICE RANGE)
THEN,
=SQRT(STDEV)
=SUM(ASKBID)/VOLUME
RULE:
WHEN SV, IV, VOL ARE NEAR NEUTRAL THIS IS A BUY SIGNAL –
STDEV SIGNALS AN ANOMALY IF IT LOOKS LIKE AN OUTLIER TO THE TRIAD.
GROWTH

VALUE

SD SQRT

#DIV/0!

#DIV/0!

#DIV/0!

GROWTH:
=GROWTH(LAST PRICE; HIGH PRICE;SV;IV;MIN+TIME VAUE)+MIN PRICE TGT
VALUE:
=SUM(GROWTHLAST PRICE)
SD SQRT:
=STDEV(IV;VOL)*SQRT(DAYS/252)
ALTERNATE:
=STDEV(ROR;ROC)*SQRT(DAYS/252)
=STDEV(HIGH PRICE;LOW PRICE)*SQRT(DAYS/252)
TIME VALUE
=SUM(MIN+EXTRINSIC)
INDEX

WEIGHT

#N/A

#DIV/0!

INDEX:
=AVEDEV(PRICE SERIES)/5
WEIGHT:
=SUMPRODUCT(STDEV;SV;IV)*PRICE ACTION
ROR:
=SUM(HIGH PRICELOW PRICE)/ABS(HIGH)
ROC:
=STDEV(HIGH;LOW)*SQRT(DAYS/252)
MEAN
=MEDIAN(OPEN PRICE;LAST PRICE;HIGH PRICE;LOW PRICE)
PRICE TARGET:
=SUM(LAST+PRICE ACTION)
EXPONENTIAL MOVE:
=(PRICE INDEX)*45/252)
CAN VARY TIME FRAME USING 1/16TH FRACTIONAL
VARIANCE:
=VARP(OPENPRICE TO LOW PRICE SERIES)
KURTOSIS:
=KURT(SV;IV;ROC;STDEV)
ALPHA

BETA

RSQ

#N/A

#DIV/0!

#N/A

ALPHA:
=SUM(IV)+(PRICE ACTION – STDEV)*(INDEX/WEIGHT)
BETA:
=FTEST(IV;NET CHANGE; PRICE INDEX)
PEARSON:
=PEARSON(HIGH, LOW, CURRENT PRICE; NET CHANGE, PRICE ACTION, PRICE INDEX)
(Disclaimer: The data presented is intentionally provided for educational purposes only. We are not making any recommendations nor implying that this statistical set up is a proven format for making profitable trades. Our intent is to expose traders to the combinations of scenarios regarding Excel equation inputs and the means of calibration by making a modular layout of specific “family” statistical inputs that can be supportive for other modular bins.)
Peace.
Rich
Like this:
Like Loading...