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 PRICE-LOW 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)

P ACTION

1.55

PRICE ACTION IS ESSENTIAL IN COMPARISON TO NET CHANGE. IT ACTS AS THE LEADING INDICATOR FOR INTRADAY PRICE MOVEMENT DIRECTION/REVERSAL

=SUM((LAST PRICE-OPEN PRICE)+(LAST PRICE-HIGH PRICE)+(LAST PRICE-LOW 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 RETURN-BENCHMARK)*(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

RISK FREE

0.25

 

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)

LOG

EXP

0.88

2.42

NATURAL LOG:

=LN(EXP MOVE/SD SQRT)

EXPONENTIAL:

=EXP(LOG)

SKEW

DAILY %

0.56

0.45

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

64.01

PIVOT PRICE:

=AVERAGE(PRICE SERIES)

OR

=AVERAGE(OPEN;LAST;HIGH;LOW PRICES)

CHG %

1.01%

PIVOT PRICE:

=AVERAGE(PRICE SERIES)

OR

=AVERAGE(OPEN;LAST;HIGH;LOW PRICES)

CHG %

1.01%

=SUM(HIGH PRICE TGT-HIGH PRICE)*1/HIGH PRICE TGT

HIGH

66.07

=SUM(OPEN+EXPONENTIAL)

LOW

65.29

=SUM(HIGH PRICE TARGET-SD SQRT)

INT

EXT

#N/A

#N/A

INT: =SUM(LAST PRICE – STDEV)+IV

EXT: =SUM(LAST PRICE – INTRINSIC)

STDEV

#DIV/0!

=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)

A-B

#N/A

=SUM(ASK-BID)/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(GROWTH-LAST PRICE)

SD SQRT:

=STDEV(IV;VOL)*SQRT(DAYS/252)

ALTERNATE:

=STDEV(ROR;ROC)*SQRT(DAYS/252)

=STDEV(HIGH PRICE;LOW PRICE)*SQRT(DAYS/252)

MIN + TV

#N/A

TIME VALUE

=SUM(MIN+EXTRINSIC)

INDEX

WEIGHT

#N/A

#DIV/0!

INDEX:

=AVEDEV(PRICE SERIES)/5

WEIGHT:

=SUMPRODUCT(STDEV;SV;IV)*PRICE ACTION

ROR

ROC

#N/A

#DIV/0!

ROR:

=SUM(HIGH PRICE-LOW PRICE)/ABS(HIGH)

ROC:

=STDEV(HIGH;LOW)*SQRT(DAYS/252)

MEAN

#N/A

MEAN

=MEDIAN(OPEN PRICE;LAST PRICE;HIGH PRICE;LOW PRICE)

P TGT

EXP MOVE

#N/A

#N/A

PRICE TARGET:

=SUM(LAST+PRICE ACTION)

EXPONENTIAL MOVE:

=(PRICE INDEX)*45/252)

CAN VARY TIME FRAME USING 1/16TH FRACTIONAL

VAR

KURT

0

#DIV/0!

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s