Black-Scholes in Excel, Part II

Following on from the post a few weeks ago on a simple Black-Scholes Excel macro, here is a follow up, with a slightly updated version that also calculates the major greeks (vega, gamma and delta). The formulae are from Hull’s book, or see here for examples of the closed-form greeks.

Usage of the spreadsheet is identical to the previous example, so refer to that post. The only difference is the output, which now looks like this (click to enlarge):

black-scholes-greeks.png

The spreadsheet is available for download here, and the code is below for reference.

Function BlackScholes(SpotPrice As Double, ExercisePrice As Double,
        TimeToMaturity As Double, RiskFreeRate As Double, sigma As Double,
        Optional DividendYield As Double) As Double()

    Dim d1 As Double
    Dim d2 As Double
    Dim Nd1 As Double
    Dim Nd2 As Double
    Dim N_dash_d1 As Double
    Dim ResultArray() As Double
    Dim gamma As Double
    Dim vega As Double
    Dim theta As Double

    ReDim ResultArray(10) As Double

    If (IsMissing(DividendYield)) Then
        d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
        + ((RiskFreeRate + (0.5 * (sigma ^ 2))) * TimeToMaturity)
    Else
        d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
        + ((RiskFreeRate - DividendYield + (0.5 * (sigma ^ 2))) * TimeToMaturity)
    End If

    d1 = d1 / (sigma * (TimeToMaturity ^ (1 / 2)))
    d2 = d1 - (sigma * (TimeToMaturity ^ (1 / 2)))
    Nd1 = WorksheetFunction.NormSDist(d1)
    Nd2 = WorksheetFunction.NormSDist(d2)

    'Call Value
    If (IsMissing(DividendYield)) Then
        ResultArray(0) = (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    Else
        ResultArray(0) = Exp(-DividendYield * TimeToMaturity)
        * (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    End If

    'Call Delta
    ResultArray(1) = Nd1 * Exp(-DividendYield * TimeToMaturity)

    'Call Gamma
    N_dash_d1 = (1 / ((2 * WorksheetFunction.Pi) ^ (1 / 2)) * (Exp((-d1 ^ 2) / 2)))
    gamma = (N_dash_d1 * Exp(-DividendYield * TimeToMaturity)) / (SpotPrice * sigma * (TimeToMaturity ^ (1 / 2)))
    ResultArray(2) = gamma

    'Call Vega
    vega = SpotPrice * (TimeToMaturity ^ (1 / 2)) * N_dash_d1 * Exp(-DividendYield * TimeToMaturity)
    ResultArray(3) = vega

    'Call Theta
    theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
    theta = theta + (DividendYield * SpotPrice * Nd1 * Exp(-DividendYield * TimeToMaturity))
    theta = theta - (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
    ResultArray(4) = theta

    'Put Value
    If (IsMissing(DividendYield)) Then
        ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
        * (1 - Nd2) - SpotPrice * (1 - Nd1)
    Else
        ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
        * WorksheetFunction.NormSDist(-d2) - Exp(-DividendYield * TimeToMaturity)
        * SpotPrice * WorksheetFunction.NormSDist(-d1)
    End If

    'Put delta
    ResultArray(6) = (Nd1 - 1) * Exp(-DividendYield * TimeToMaturity)

    'Put Gamma
    ResultArray(7) = gamma

    'Put Vega
    ResultArray(8) = vega

    'Put Theta
    theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
    theta = theta - (DividendYield * SpotPrice * WorksheetFunction.NormSDist(-d1) * Exp(-DividendYield * TimeToMaturity))
    theta = theta + (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * WorksheetFunction.NormSDist(-d2))
    ResultArray(9) = theta

    BlackScholes = ResultArray
End Function

Apache Source License Scanner (Ruby)

The other day, I needed to replace all instances of the old-style Apache license header in the commons-net codebase with the new style license header. My first thought was to write a simple Perl script to extract and replace instances of the license, however I decided to give Ruby a shot. The result is below: LicenseScanner.rb will scan the given directory and all subdirectories for Java source files and attempt to locate and replace any instances of older-style source headers. Being used to Perl for this kind of thing, you really appreciate some of the Perl-isms built into Ruby, for instance:

  • “Here” variables – see the definition of @@new_asl_license;
  • First class regular expression support, via // and =~.

Here is the source:

require "find"
# Written by Rory Winston <rwinston@apache.org>
# 
class LicenseScanner
  # Older, incorrect license headers - Replace
  @@asl_patt_10 = /(\/\*(.*) \* The Apache Software License, Version 1.1(.*?)\*?(?!\/)\*\/)/mis
  @@asl_patt_20 = /(\/\*(.*) \* Licensed under the Apache License, Version 2.0(.*?)\*?(?!\/)\*\/)/mis
  @@asl_patt_other = /(\/\*(.*) \* Copyright 200[0-9](-200[0-9])? The Apache Software Foundation(.*?)\*?(?!\/)\*\/)/mis

  # New corrected header - Leave as-is
  @@new_asl_pattern = /(\/\*(.*) \* Licensed to the Apache Software Foundation (ASF)(.*?)\*?(?!\/)\*\/)/mis

  # The new license header 
  @@new_asl_license = <<EOL
/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
EOL

  @prompt = true

  def initialize(dir)
    @dir = dir
    @str = ""
  end

  def initialize(dir, prompt)
    @dir = dir
    @prompt = prompt
  end

  def scan
    Find.find(@dir) do |path|
      Find.prune if [".",".."].include? path
      readFile(path) if File.basename(path) =~ /(.*).java$/
    end
  end

  def readFile(filename)
    @str = ""
    @filename = filename
    @str = IO.read(@filename)
    @str.gsub!("\015", "")
    scanFile
  end

  def scanFile
    if @str =~ @@asl_patt_10
      puts "Found Apache 1.x License in #{@filename}"
      replaceLicense(@@asl_patt_10)
    elsif @str =~ @@asl_patt_20
      puts "Found other Apache 2.0 License in #{@filename}"
      replaceLicense(@@asl_patt_20)
    elsif @str =~ @asl_patt_other
      puts "Found Apache 1/2 license in file in #{@filename}"
      replaceLicense(@asl_patt_other)
    elsif @str =~ @new_asl_pattern
      puts "Correct license found in #{@filename}"
    else
      puts "No license found in #{@filename}"
      insertLicense
    end
  end

  # Replace an existing Apache 2.0 license with the new format
  def replaceLicense(pattern)
    resp = confirm "Replace license in #{@filename}"

    case resp
      when "y","Y"
        # Open existing file and truncate
        puts "Replacing license in #{@filename}..."
        srcFile = File.new(@filename, "w+")
        srcFile.puts  @str.sub(pattern, @@new_asl_license)

        srcFile.close
      when "n", "N"
        puts "Skipping..."
    end
  end

  # Insert a new license at the beginning of the file
  def insertLicense
    resp = confirm "Insert license in #{@filename}"

    case resp
      when "y","Y"
        puts "Inserting license in #{@filename}..."
        srcFile = File.new(@filename, File::TRUNC | File::RDWR)
        srcFile.puts  @@new_asl_license
        srcFile.puts @str
        srcFile.close
      when "n", "N"
        puts "Skipping..."
     end

  end

  def confirm(msg)
    if @prompt != true
      puts "#{msg} [y/n]?"
      resp = gets
      resp.chomp!

      while (resp !~ /[YyNn]/)
        puts "#{msg} [y/n]?"
        resp = gets
        resp.chomp!
      end
    else
      resp = "y"
    end

    resp
  end

end

# Usage: LicenseScanner.new("path/to/src/dir", auto-replace [true/false])
scanner = LicenseScanner.new("c:/sandbox/net/src/main/java", true)
scanner.scan

The CME Globex Electronic Futures Trading System

The story of the Reuters Globex futures trading system developed for the Chicago Mercantile Exchange is a classic ETS (Electronic Trading System) case study. Originally devised in 1987 and launched in 1992, the product was plagued with technical difficulties and defects, and the general reluctance of the traditionally pit-based futures trading community to accept its merits.

On its first day of business in June 1992, 2,063 futures contracts traded on CME Globex. Today, an average of more than one million contracts a day are traded on CME Globex, accounting for over 50% of total CME volume.

See http://www.cme.com/trading/get/abt/welglobex951.html

The orginal product was beset with issues, and there were still serious doubts as to whether an ETS could ever replace the open outcry system:

See http://sigchi.org/chi95/proceedings/papers/jll_bdy.htm

However, the success of Globex (although still not without its issues – see http://www.finextra.com/fullstory.asp?id=13768) is a strong validation of the potential efficiency gains that can be realised on an ETS platform.