Macros Available

There are 2 macros which are useful for options queries. They are NormalDist and BlackScholesOptionPrice.

 

NormalDist(n)

NormalDist(n) returns the percent of area under the normal distribution curve commonly called the z-table. The parameter n is the number of standard deviations from the mean. The macro NormalDist(n) returns the percent of the curve below parameter n.  This is commonly used to compute probability.

For example, compute the probability of a future price level for a given volatility of the 5 day move over the last 100 days.

   LET
     @stock = IBM

   SHOW
     prob_above: 1 - NormalDist ( natural_log ( price_target / price_now ) /
                 volatility_period ) 
     price_target: price_target
     price_now: price_now
     vol_period: volatility_period
     5_day: percent_move from today to 5 days later  of Close of @stock 
     worked: IF
                 Close of @stock 5 days later  - Close of @stock is more than
                 0.01 * Close of @stock
             THEN 1
             ELSE -1
             ENDIF 
   WHEN
       Date is within 1 year
     AND
       Close of @stock 5 days later  is DEFINED
     AND
       Close of @stock is DEFINED
     AND
       price_target = Close of @stock * 1.01
     AND
       price_now = Close of @stock
     AND
       volatility_period = std_dev ( (5 day percent_move of Close of @stock) / 100, 100 days)
 

BlackScholesOptionPrice 

Computes the option price using the Black-Scholes formula. 

BlackScholesOptionPrice (ATTR @underlying_price, ATTR @strike, ATTR @interest_rate, ATTR @sigma, ATTR @days_expiration, CONSTANT @is_call)

 

Example query: 

   SHOW
     settle: Close of SP_2004Z.C1100
     m: BlackScholesOptionPrice ( Close of SP_2004Z, 1100,
                                  FRH_DGS1 / 100,
           (30 day std_dev of Close of SP_2004Z) / 100,
           periods_in_range from today to SP_2004Z.C1100  last_data_day,
           1 )
     s: Close of SP_2004Z
     x: 1100
     r: FRH_DGS1 / 100
     sigma: 30 day std_dev of Close of SP_2004Z
     t: periods_in_range from today to SP_2004Z.C1100  last_data_day
   WHEN
       Date is 11/7/2004

Date 11/05/2004  
Day Fri       
Settle 71.3000       
Model 72.7949     
Underlying 1167.7000  
Strike 1100.0000        
Rate 0.0244       
Sigma% 17.7383  
Days Exp 29.0000

ATTR MACRO BlackScholesOptionPrice (ATTR @underlying_price, ATTR @strike,  ATTR @interest_rate, ATTR @sigma,
      ATTR @days_expiration, CONSTANT @is_call) VARS
    @S @X @r @days_exp @time_sqrt @e @d1 @d2 INITIALIZE
       @S = @underlying_price
     AND
       @X = @strike
     AND
       @r = @interest_rate
     AND
       @days_exp = (@days_expiration / 365)
     AND
       @time_sqrt = @days_exp ** 0.5
     AND
       @e = 2.7182818284590455349
     AND
       @d1 =
       (
         (natural_log ( @S / @X ) + @r * @days_exp) / (@sigma * @time_sqrt
        )) + (0.5 * @sigma * @time_sqrt)

     AND
       @d2 = @d1 - (@sigma * @time_sqrt) RETURN
       IF @is_call > 0 THEN
          @S * NormalDist ( @d1 )  - @X * @e ** (-1 * @r * @days_exp) *
NormalDist ( @d2 )
       ELSE
           -1.0 * @S * NormalDist ( -1.0 * @d1 )  + @X * @e ** (-1 * @r * @days_exp) * NormalDist ( -1.0 * @d2 )
       ENDIF
ENDMACRO