PHILADELPHIA REFLECTIONS
Musings of a Philadelphia Physician who has served the community for six decades

Return to Home

Using XML to Retrieve Financial Data

It's often desirable to get live financial data and everyone knows. XML is the thing to use but actually writing programs that work takes a bit of trouble. Plus, once you've got the data you need to display it.

Financial Graphing using PHP

The Financial Strategy

The problem to be solved in December 2012 was to fund a 30-year inflation-adjusted after-tax immediate annuity with the money in an equity portfolio located in a combination of 401(k)s, IRAs and taxable brokerage accounts.

After considerable analysis and consideration we settled on a 30-year bond ladder. The composition of such a thing could be the subject of another blog, but suffice it for here that a collection of bond ladders was the objective. The alternatives were to continue to harvest capital gains via judicious portfolio rebalancing but in this instance the risk no longer seemed warranted, and commercial annuities but the loss of control was unpalatable and the costs were very high.

The problem was that a number of the bonds would be long-dated coupon bonds held to maturity and nobody wanted to buy such things when interest rates were at the lowest level in two generations.

{December 2012 Treasury Rates}

The Macro Situation

Whither the economy, the equity market and interest rates? December 2012 was 44 months, nearly 4 years, from the bottom of the recession/market in March 2009. The previous cycle had its low in November 2003 and its peak in mid 2007 ... 3 1/2 years. This was the heart of the Bear case: these things move in cycles and this cycle was getting long in the tooth. Bear markets hurt equity portfolios and bring lower rates. Add to that the fact that with Obama's re-election Ben Bernanke seemed likely to be at the Fed for a while longer and Chairman Bernanke had promised us low interest rates until 2015.

The Bull case was that this recession had been much worse than the previous one and so the recovery would be longer, too. After 4 years of recovery, unemployment was still officially 8% and actually more like double that; corporate profits were doing well but there was a lot of capacity and retained earnings were being warehoused rather than invested or returned; real estate had come back strongly and looked like continuing to improve; and the Euro crisis seemed to be subsiding. Plus, the re-elected Democrats seemed more stimulative than the Republicans might have been, possibly except for tax rates which might have been over emphasized in terms of their economic impact. Finally, there seemed to be a chance that the politicians would act to avoid the fiscal cliff and after a national debate on the debt and deficit going back to the 2010 mid-terms there seemed to be some, albeit small, chance that they might even go further and do something useful for the economy.

The Plan

So we agreed on the Bull case and decided to hold equities into 2013 and also hold some TLT (20+ Treasury ETF) as an insurance policy in case of hiccups. As for when to buy bonds, we looked at rates going back to the end of the last recession in 2009 and found that the highest rates of that period had been in April 2010.

{April 2010 Treasury Rates}

We decided to leg into the ladders: we would buy in four steps ... 25% of the bonds when rates got 25% of the way to the levels of April 2010 and so on. By so doing we could substantially reduce the cost of the ladder (said another way, we could have more money left over after the ladder was in place). We would adapt the approach as events unfolded.

The Technical Implementation of Visual Trigger Points

Although we were focused on this subject now, rates wouldn't move for quite a while and our attention would turn to other things. What we needed was something to quickly remind ourselves of where we stood viz-a-viz our plan.

We used two technologies to build the picture below which shows the rate levels of the four steps with fixed dashed lines and an updating solid line for the current level of Treasury rates. When the solid line moved up to touch a dotted line, it would be time to buy.

The first technology we used was the PHP function I had written to pull the current yield curve levels from the Treasury website to update the solid line. That function is shown here.

The second technology is an absolutely superb graphing package for use with PHP called pChart. I want to tell you that it works right out of the can exactly as promised and as long as you write your programs in a sub-folder to the installation root folder, it will. I cannot recommend it highly enough. From initial installation to producing the chart below took me maybe two hours but I think less.


{Yield Curve Graph}

http://www.philadelphia-reflections.com/blog/2387.htm


Treasury Yields from XML using PHP

The Treasury reports yields on its constant-maturity bonds daily and produces an XML feed to retrieve them. This is a PHP function that returns an array of the most-recent yield curve data.

<?php
//
// Returns an array of the latest (usually the day before) yields on constant maturity Treasury bonds
//     see http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield
//
// Thanks very much to uramihsayibok, gmail, com at http://php.net/manual/en/function.simplexml-load-file.php

/*
On Sunday December 3, 2012 the following call:

    $yields = TreasuryYields();
    print_r($yields);

produced this output:

Array
(
    [Id] => 5736
    [NEW_DATE] => 2012-11-30T00:00:00
    [BC_1MONTH] => 0.11
    [BC_3MONTH] => 0.08
    [BC_6MONTH] => 0.13
    [BC_1YEAR] => 0.18
    [BC_2YEAR] => 0.25
    [BC_3YEAR] => 0.34
    [BC_5YEAR] => 0.61
    [BC_7YEAR] => 1.04
    [BC_10YEAR] => 1.62
    [BC_20YEAR] => 2.37
    [BC_30YEAR] => 2.81
    [BC_30YEARDISPLAY] => 2.81
)
*/

function TreasuryYields()
{
  // create the XML URL for today's month and year

  //    this one picks the last (more than) 5,000 entries (25+ years?)
  //    $url = 'http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData';
  $url_part1      = 'http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=month(NEW_DATE)%20eq%20';
  $url_this_month = date("m");
  $url_part2      = '%20and%20year(NEW_DATE)%20eq%20';
  $url_this_year  = date("Y");

  $url = $url_part1 . $url_this_month . $url_part2 . $url_this_year;
  $xml = simplexml_load_file($url);
  $number_of_entries = count($xml->entry);

  // test for no entries (first day of the month on a Saturday, for example, has no entries for this month)
  if ($number_of_entries == 0)
  {
    $last_month     = date('Y-m-d', strtotime(date('Y-m-d')." -1 month"));
    $url_this_month = substr($last_month, 5, 2);
    $url_this_year  = substr($last_month, 0, 4);

    $url = $url_part1 . $url_this_month . $url_part2 . $url_this_year;
    $xml = simplexml_load_file($url);
    $number_of_entries = count($xml->entry);
  }

  // pull out all the yields for the last (most-recent) entry
  $counter = 0;
  foreach ($xml->entry as $entry)
  {
    if (++$counter < $number_of_entries) continue;
    foreach ($entry->content->children("m", true)->properties->children("d", true) as $label => $value)
    {
      $yields[$label] = (string)$value;
    }
  }
  return $yields;
}
?>

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2381.htm


Treasury Yields from XML using Excel

The Treasury reports yields on its constant-maturity bonds daily and produces an XML feed to retrieve them. This is an Excel function that returns a string with the most-recent yield curve data for a specified maturity.

Function TreasuryRates(Optional Maturity As String = "BC_10YEAR") As String

    ' This Excel VBA function returns a string with
    ' the most-recent constant-maturity Treasury bond yield specified
    
    ' The default is the 10-year rate
    
    ' Before you start, read this:
    ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
    
    ' The Maturity input argument is any one of the elements
    ' following the colon in the <d: ... nodes shown in a sample of the XML produced by the Treasury
    
    ' On December 4, 2012
    ' =TreasuryRates()            returned 1.62
    ' =TreasuryRates("BC_30YEAR") returned 2.78
    
    '  <entry>
    '    <content type="application/xml">
    '      <m:properties>
    '        <d:Id m:type="Edm.Int32">5738</d:Id>
    '        <d:NEW_DATE m:type="Edm.DateTime">2012-12-04T00:00:00</d:NEW_DATE>
    '        <d:BC_1MONTH m:type="Edm.Double">0.07</d:BC_1MONTH>
    '        <d:BC_3MONTH m:type="Edm.Double">0.1</d:BC_3MONTH>
    '        <d:BC_6MONTH m:type="Edm.Double">0.15</d:BC_6MONTH>
    '        <d:BC_1YEAR m:type="Edm.Double">0.18</d:BC_1YEAR>
    '        <d:BC_2YEAR m:type="Edm.Double">0.25</d:BC_2YEAR>
    '        <d:BC_3YEAR m:type="Edm.Double">0.34</d:BC_3YEAR>
    '        <d:BC_5YEAR m:type="Edm.Double">0.63</d:BC_5YEAR>
    '        <d:BC_7YEAR m:type="Edm.Double">1.04</d:BC_7YEAR>
    '        <d:BC_10YEAR m:type="Edm.Double">1.62</d:BC_10YEAR>
    '        <d:BC_20YEAR m:type="Edm.Double">2.36</d:BC_20YEAR>
    '        <d:BC_30YEAR m:type="Edm.Double">2.78</d:BC_30YEAR>
    '        <d:BC_30YEARDISPLAY m:type="Edm.Double">2.78</d:BC_30YEARDISPLAY>
    '      </m:properties>
    '    </content>
    '  </entry>
    
    ' This function returns a string; the String_to_Number function
    ' defined with my GetQuote function will convert to a string number to double
    
    Dim TreasuryXMLstream As MSXML2.DOMDocument
    
    Dim DNodes As MSXML2.IXMLDOMNodeList
    Dim DNode As MSXML2.IXMLDOMNode
    
    Dim fSuccess As Boolean
    Dim URL As String, _
        url_part1 As String, _
        url_this_month As String, _
        url_part2 As String, _
        url_this_year As String
    Dim iInt As Integer
    
    On Error GoTo HandleErr
    
    ' create the XML request URL for today's month and year
    ' -----------------------------------------------------
    
    ' this one returns more than 5,700 entries (25+ years?)
    ' URL = "http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData"
    
    url_part1 = "http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=month(NEW_DATE)%20eq%20"
    url_part2 = "%20and%20year(NEW_DATE)%20eq%20"
    
    iInt = Month(Now)
    url_this_month = LTrim(Str(iInt))
    
    iInt = Year(Now)
    url_this_year = LTrim(Str(iInt))
    
    ' this is used to test whether the month we requested had no data
    ' ---------------------------------------------------------------
    TreasuryRates = "empty"
    
    ' set up to pull in the XML stream
    ' --------------------------------
    
    Set TreasuryXMLstream = New MSXML2.DOMDocument
    TreasuryXMLstream.async = False                 ' wait for completion
    TreasuryXMLstream.validateOnParse = False       ' do not validate the XML stream
    
TryAgain:
    
    URL = url_part1 & url_this_month & url_part2 & url_this_year
    
    ' pull in the XML
    ' ---------------
    
    fSuccess = TreasuryXMLstream.Load(URL)         ' load the XML stream
    
    If Not fSuccess Then                           ' quit on failure
        MsgBox "error loading Treasury XML stream"
        Exit Function
    End If
    
    ' Iterate through the <d: nodes looking for the <d:Maturity
    ' ---------------------------------------------------------
    
    ' this assumes
    ' 1. the last node in the XML stream returned to us is the <entry> node we want
    '   2. the last node in the <entry> node is a <content node
    '     3. the last node in the <content node is an <m:properties> node ...
    '       4. ... which contains the <d:BC_10YEAR (or whatever) nodes

    Set DNodes = TreasuryXMLstream.DocumentElement.LastChild.LastChild.LastChild.ChildNodes
    '                                              entry     content   m         d's
    
    For Each DNode In DNodes
    
        If DNode.BaseName = Maturity Then
                        
            TreasuryRates = DNode.Text
            Exit Function
                            
        End If
    
    Next DNode
    
    ' test for no entries (first day of the month on a Saturday, for example, has no entries for this month)
    ' go to prior month in that case
    
    If TreasuryRates = "empty" Then
    
        ' go through twice, and we assume the input parameter is wrong
        TreasuryRates = Maturity & " is not a valid parameter for TreasuryRates function"
        
        url_this_month = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm")
        url_this_year = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy")
        
        GoTo TryAgain
        
    End If
    
' error handlers
' --------------
    
ExitHere:
            Exit Function
HandleErr:
            MsgBox "Error " & Err.Number & ": " & Err.Description
            Resume ExitHere
            Resume
End Function


How to install an Excel macro

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Tools > References > Microsoft XML v6 per the picture below
  5. Switch back to your spreadsheet (x out of the VBA screen if you want)
{Microsoft XML v6 installation}

Thanks to declaring early bound msxml object throws an error in vba for both the clue and the picture

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2388.htm


Exchange Quotes from XML using Excel (Yahoo Finance)

Excel VBA function that returns stock information from Yahoo Finance's XML streams.

Function GetQuoteYahoo(Symbol As String, _
         Optional YahooParameter As String = "LastTradePriceOnly", _
         Optional YahooFeed As String = "a") As String

    ' This is an Excel VBA function which returns data about a stock
    ' using Yahoo Finance's various XML streams
    
    ' On December 8, 2012 =GetQuoteYahoo("VTI")                   returned 73.04
    '                     =GetQuoteYahoo("VTI", "Dividend_Yield") returned 1.89
    '                     =GetQuoteYahoo("MS",  "Industry", "e")  returned Investment Brokerage - National
    
    ' This function returns a string;
    ' the Str_to_Num function included below will convert a string-number to a double
    
    ' Yahoo Finance offers several XML streams
    ' You should look at each stream and figure out which one you want and what the node names are
    ' Stream description
    '   "a" is from the CSV data
    '   "b" is from yahoo.finance.quotes
    '   "c" and "d" are from yahoo.finance.quant and quant2
    '   "e" is from yahoo.finance.stocks
    '
    ' =GetQuoteYahoo("GOOG","URL","a") will give you the URL for stream "a" to look at for Google, etc.
    ' The default is stream "a" and node "LastTradePriceOnly"
    
    ' The full list (as of Dec 2012) of yahoo.finance data tables was
    '   yahoo.finance.historicaldata
    '   yahoo.finance.industry
    '   yahoo.finance.isin
    '   yahoo.finance.onvista
    '   yahoo.finance.option_contracts
    '   yahoo.finance.options
    '   yahoo.finance.quant
    '   yahoo.finance.quant2
    '   yahoo.finance.quotes
    '   yahoo.finance.quoteslist
    '   yahoo.finance.sectors
    '   yahoo.finance.stock
    '   yahoo.finance.stocks
    '   yahoo.finance.xchange
    
    ' In case you want even more variety, I have a GetQuote function which uses the XML
    ' stream from Google Finance but they are threatening to discontinue it
    ' see http://www.philadelphia-reflections.com/blog/2385.htm
    
    ' My thanks to http://vikku.info/codetrash/Yahoo_Finance_Stock_Quote_API
    ' and http://developer.yahoo.com/yql/console/
    
    ' Before you start, read this:
    ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
    
    ' --------- code follows ---------
    
    Dim YahooXMLstream As MSXML2.DOMDocument
    
    Dim oChildren As MSXML2.IXMLDOMNodeList
    Dim oChild As MSXML2.IXMLDOMNode
    
    Dim fSuccess As Boolean
    Dim URL As String, _
        url_part1 As String, _
        url_part2 As String, _
        url_part3 As String, _
        url_part4 As String, _
        url_part5 As String
    
    On Error GoTo HandleErr
    
    ' create the URL that requests the XML stream from Yahoo Finance
    If LCase(YahooFeed) = "a" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20csv%20where%20url%3D'http%3A%2F%2Fdownload.finance.yahoo.com%2Fd%2Fquotes.csv%3Fs%3D"
        url_part2 = "%26f%3Dsnll1d1t1cc1p2t7va2ibb6aa5pomwj5j6k4k5ers7r1qdyj1t8e7e8e9r6r7r5b4p6p5j4m3m7m8m4m5m6k1b3b2i5x"
        url_part3 = "%26e%3D.csv'%20and%20columns%3D"
        url_part4 = "'Symbol%2CName%2CLastTradeWithTime%2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CChange%20PercentChange%2CChange%2CChangeinPercent%2CTickerTrend%2CVolume%2CAverageDailyVolume%2CMoreInfo%2CBid%2CBidSize%2CAsk%2CAskSize%2CPreviousClose%2COpen%2CDayRange%2CFiftyTwoWeekRange%2CChangeFromFiftyTwoWeekLow%2CPercentChangeFromFiftyTwoWeekLow%2CChangeFromFiftyTwoWeekHigh%2CPercentChangeFromFiftyTwoWeekHigh%2CEarningsPerShare%2CPE%20Ratio%2CShortRatio%2CDividendPayDate%2CExDividendDate%2CDividendPerShare%2CDividend%20Yield%2CMarketCapitalization%2COneYearTargetPrice%2CEPS%20Est%20Current%20Yr%2CEPS%20Est%20Next%20Year%2CEPS%20Est%20Next%20Quarter%2CPrice%20per%20EPS%20Est%20Current%20Yr%2CPrice%20per%20EPS%20Est%20Next%20Yr%2CPEG%20Ratio%2CBook%20Value%2CPrice%20to%20Book%2CPrice%20to%20Sales%2CEBITDA"
        url_part5 = "%2CFiftyDayMovingAverage%2CChangeFromFiftyDayMovingAverage%2CPercentChangeFromFiftyDayMovingAverage%2CTwoHundredDayMovingAverage%2CChangeFromTwoHundredDayMovingAverage%2CPercentChangeFromTwoHundredDayMovingAverage%2CLastTrade%20(Real-time)%20with%20Time%2CBid%20(Real-time)%2CAsk%20(Real-time)%2COrderBook%20(Real-time)%2CStockExchange'"
    
        URL = url_part1 & Trim(Symbol) & url_part2 & url_part3 & url_part4 & url_part5
    ElseIf LCase(YahooFeed) = "b" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22"
        url_part2 = "%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "c" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "d" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant2%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "e" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.stocks%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    Else
        ' return error message if YahooFeed isn't recognized
        GetQuoteYahoo = YahooFeed & " is an invalid YahooFeed parameter supplied to GetQuoteYahoo function"
        Exit Function
    End If
    
    ' In case you want to look at the XML
    If YahooParameter = "URL" Then
        GetQuoteYahoo = URL
        Exit Function
    End If
    
    ' pull in the XML stream
    Set YahooXMLstream = New MSXML2.DOMDocument
    YahooXMLstream.async = False                 ' wait for completion
    YahooXMLstream.validateOnParse = False       ' do not validate the XML stream
    
    fSuccess = YahooXMLstream.Load(URL)          ' pull in the feed
    
    If Not fSuccess Then                         ' quit on failure
      MsgBox "error loading Yahoo Finance XML stream"
      Exit Function
    End If
    
    ' iterate through the nodes looking for one with the name in YahooParameter
    GetQuoteYahoo = YahooParameter & " is not valid for GetQuoteYahoo function with YahooFeed " & YahooFeed
    
    Set oChildren = YahooXMLstream.DocumentElement.LastChild.LastChild.ChildNodes
    
    For Each oChild In oChildren

        If oChild.nodeName = YahooParameter Then
        
            GetQuoteYahoo = oChild.Text
            Exit Function
            
        End If
        
    Next oChild
        
' error handlers
ExitHere:
            Exit Function
HandleErr:
            MsgBox "Error " & Err.Number & ": " & Err.Description
            Resume ExitHere
            Resume
End Function

Function Str_to_Num(num_as_string As String) As Double

    ' The GetQuoteYahoo function returns string values
    ' This function converts numbers in string format to double

    Str_to_Num = Val(num_as_string)

End Function

Function FiftyTwoWkHi(Myrange As Range) As String
'
' Function to return the 52-Week High as a String
'
' Usage: =Str_to_Num(FiftyTwoWkHi(F2))
' where "F2" is =GetQuoteYahoo("VTI","FiftyTwoWeekRange")
'
' Tools > References > Microsoft VBScript Regular Expressions 5.5
'
    Dim regEx As New RegExp
    Dim strOutput As String
    Dim Matches   As MatchCollection

    With regEx
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "\d+\.\d+$"
    End With

    If regEx.test(Myrange.Value) Then
        Set Matches = regEx.Execute(Myrange.Value)
        FiftyTwoWkHi = Matches(0)
    Else
        FiftyTwoWkHi = "Not matched"
    End If

End Function

Function FiftyTwoWkLo(Myrange As Range) As String
'
' Function to return the 52-Week Low as a String
'
' Usage: =Str_to_Num(FiftyTwoWkLo(F2))
' where "F2" is =GetQuoteYahoo("VTI","FiftyTwoWeekRange")
'
' Tools > References > Microsoft VBScript Regular Expressions 5.5
'
    Dim regEx As New RegExp
    Dim strOutput As String
    Dim Matches   As MatchCollection

    With regEx
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "^\d+\.\d+"
    End With

    If regEx.test(Myrange.Value) Then
        Set Matches = regEx.Execute(Myrange.Value)
        FiftyTwoWkLo = Matches(0)
    Else
        FiftyTwoWkLo = "Not matched"
    End If

End Function

How to install an Excel macro

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Tools > References > Microsoft XML v6 per the picture below
  5. Switch back to your spreadsheet (x out of the VBA screen if you want)
{Microsoft XML v6 installation}

Thanks to declaring early bound msxml object throws an error in vba for both the clue and the picture

My thanks to HTML entity encoder/decoder

http://www.philadelphia-reflections.com/blog/2392.htm


Exchange Quotes from XML using PHP (Yahoo)

Yahoo provides several XML feeds. This PHP function returns an array of data fields for an input array of symbols.

<?php
//
// Takes in an array of symbols and returns an array of stock/fund information
// retrieved from various Yahoo Finance XML streams
//
// Thanks to http://vikku.info/codetrash/Yahoo_Finance_Stock_Quote_API
// and http://developer.yahoo.com/yql/console/
//
// The first parameter is an array of symbols
//
// The second parameter specifies the XML stream you want
//    "a"  CSV
//    "b"  yahoo.finance.quotes
//    "c"  yahoo.finance.quant
//    "d"  yahoo.finance.quant2
//    "e"  yahoo.finance.stocks
// or
//    "url_a", "url_b", etc.  to see the URL
// or
//    "all" returns an array with the results of all of the XML streams
//    This is the best way to figure out which stream and which nodes you want

/*    example

$symbols    = array("MS", "FNJHX");
$quote_list = YahooStockQuote($symbols, "all");
print_r($quote_list);

*/

function YahooStockQuote($symbols, $YahooStream = "a")
{
  // create the symbol list
  // ----------------------

  $symbol_list = '';
  foreach ($symbols as $symbol)
  {
    $symbol_list .= trim($symbol) . "%2C";                       # symbols separated by commas
  }
  $symbol_list   = substr($symbol_list, 0, -3);                  # strip off the last comma
  $symbol_list_r = str_replace('%2C','%22%2C%22',$symbol_list);  # different format for different streams

  // pull the specified XML stream and parse
  // ---------------------------------------

  if (strtolower(trim($YahooStream)) == "a" || strtolower(trim($YahooStream)) == "all" || strtolower(trim($YahooStream)) == "url_a")
  {
    $url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20csv%20where%20url%3D'http%3A%2F%2Fdownload.finance.yahoo.com%2Fd%2Fquotes.csv%3Fs%3D";
    $url_part2 = "%26f%3Dsnll1d1t1cc1p2t7va2ibb6aa5pomwj5j6k4k5ers7r1qdyj1t8e7e8e9r6r7r5b4p6p5j4m3m7m8m4m5m6k1b3b2i5x";
    $url_part3 = "%26e%3D.csv'%20and%20columns%3D";
    $url_part4 = "'Symbol%2CName%2CLastTradeWithTime%2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CChange%20PercentChange%2CChange%2CChangeinPercent%2CTickerTrend%2CVolume%2CAverageDailyVolume%2CMoreInfo%2CBid%2CBidSize%2CAsk%2CAskSize%2CPreviousClose%2COpen%2CDayRange%2CFiftyTwoWeekRange%2CChangeFromFiftyTwoWeekLow%2CPercentChangeFromFiftyTwoWeekLow%2CChangeFromFiftyTwoWeekHigh%2CPercentChangeFromFiftyTwoWeekHigh%2CEarningsPerShare%2CPE%20Ratio%2CShortRatio%2CDividendPayDate%2CExDividendDate%2CDividendPerShare%2CDividend%20Yield%2CMarketCapitalization%2COneYearTargetPrice%2CEPS%20Est%20Current%20Yr%2CEPS%20Est%20Next%20Year%2CEPS%20Est%20Next%20Quarter%2CPrice%20per%20EPS%20Est%20Current%20Yr%2CPrice%20per%20EPS%20Est%20Next%20Yr%2CPEG%20Ratio%2CBook%20Value%2CPrice%20to%20Book%2CPrice%20to%20Sales%2CEBITDA";
    $url_part5 = "%2CFiftyDayMovingAverage%2CChangeFromFiftyDayMovingAverage%2CPercentChangeFromFiftyDayMovingAverage%2CTwoHundredDayMovingAverage%2CChangeFromTwoHundredDayMovingAverage%2CPercentChangeFromTwoHundredDayMovingAverage%2CLastTrade%20(Real-time)%20with%20Time%2CBid%20(Real-time)%2CAsk%20(Real-time)%2COrderBook%20(Real-time)%2CStockExchange'";

    $URL = $url_part1 . $symbol_list . $url_part2 . $url_part3 . $url_part4 . $url_part5;

    if (strtolower(trim($YahooStream)) == "url_a")
    {
      echo $URL;
      exit;
    }

    // create a SimpleXML object from the XML stream
    $xml = simplexml_load_file($URL);

    // pull out the quotes
    foreach ($xml->results->row as $quote)
    {
        $symbol = (string)$quote->Symbol;
        foreach ($quote as $info_name => $info)
        {
        if ($info_name == "Symbol") continue;
        $node_list[$symbol][$info_name] = (string)$info;
        }
    }
    if (strtolower(trim($YahooStream)) == "a") return $node_list;

    $node_list_list["a"] = $node_list;
    unset($node_list);
  }

  if (strtolower(trim($YahooStream)) == "b" || strtolower(trim($YahooStream)) == "all" || strtolower(trim($YahooStream)) == "url_b")
  {
    $url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22";
    $url_part2 = "%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";

    $URL = $url_part1 . $symbol_list . $url_part2;

    if (strtolower(trim($YahooStream)) == "url_b")
    {
      echo $URL;
      exit;
    }

    $xml = simplexml_load_file($URL);
    foreach ($xml->results->quote as $quote)
    {
        $symbol = (string)$quote->attributes()->symbol;
        foreach ($quote as $label => $value)
        {
          $node_list[$symbol][$label] = (string)$value;
        }
    }
    if (strtolower(trim($YahooStream)) == "b") return $node_list;

    $node_list_list["b"] = $node_list;
    unset($node_list);
  }

  if (strtolower(trim($YahooStream)) == "c" || strtolower(trim($YahooStream)) == "all" || strtolower(trim($YahooStream)) == "url_c")
  {
    $url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant%20where%20symbol%20in%20(%22";
    $url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";

    $URL = $url_part1 . $symbol_list_r . $url_part2;

    if (strtolower(trim($YahooStream)) == "url_c")
    {
      echo $URL;
      exit;
    }

    $xml = simplexml_load_file($URL);
    foreach ($xml->results->stock as $quote)
    {
        $symbol = (string)$quote->attributes()->symbol;
        foreach ($quote as $label => $value)
        {
          $node_list[$symbol][$label] = (string)$value;
        }
    }
    if (strtolower(trim($YahooStream)) == "c") return $node_list;

    $node_list_list["c"] = $node_list;
    unset($node_list);
  }

  if (strtolower(trim($YahooStream)) == "d" || strtolower(trim($YahooStream)) == "all" || strtolower(trim($YahooStream)) == "url_d")
  {
    $url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant2%20where%20symbol%20in%20(%22";
    $url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";

    $URL = $url_part1 . $symbol_list_r . $url_part2;

    if (strtolower(trim($YahooStream)) == "url_d")
    {
      echo $URL;
      exit;
    }

    $xml = simplexml_load_file($URL);
    foreach ($xml->results->stock as $quote)
    {
        $symbol = (string)$quote->attributes()->symbol;
        foreach ($quote as $label => $value)
        {
          $node_list[$symbol][$label] = (string)$value;
        }
    }
    if (strtolower(trim($YahooStream)) == "d") return $node_list;

    $node_list_list["d"] = $node_list;
    unset($node_list);
  }

  if (strtolower(trim($YahooStream)) == "e" || strtolower(trim($YahooStream)) == "all" || strtolower(trim($YahooStream)) == "url_e")
  {
    $url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.stocks%20where%20symbol%20in%20(%22";
    $url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";

    $URL = $url_part1 . $symbol_list_r . $url_part2;

    if (strtolower(trim($YahooStream)) == "url_e")
    {
      echo $URL;
      exit;
    }

    $xml = simplexml_load_file($URL);
    foreach ($xml->results->stock as $quote)
    {
        $symbol = (string)$quote->attributes()->symbol;
        foreach ($quote as $label => $value)
        {
          $node_list[$symbol][$label] = (string)$value;
        }
    }
    if (strtolower(trim($YahooStream)) == "e") return $node_list;

    $node_list_list["e"] = $node_list;
    unset($node_list);

    return $node_list_list;
  }
}
?>

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2380.htm


Exchange Quotes from XML using PHP (Google Finance)

Google provides an XML feed of delayed quotes. This function returns an array of quotes for an input array of symbols.

<?php
//
// Takes in an array of symbols and returns an array of delayed quotes
// Thanks to http://www.jarloo.com/google-stock-api/
//
/*
On Tuesday December 4, 2012, the following call

    $symbols    = array("MS");
    $quote_list = GoogleStockQuote($symbols);
    print_r($quote_list);

Produced this output:

Array
(
    [MS] => Array
        (
            [pretty_symbol] => MS
            [symbol_lookup_url] => /finance?client=ig&q=MS
            [company] => Morgan Stanley
            [exchange] => NYSE
            [exchange_timezone] => ET
            [exchange_utc_offset] => +05:00
            [exchange_closing] => 960
            [divisor] => 2
            [currency] => USD
            [last] => 16.47
            [high] => 16.61
            [low] => 16.38
            [volume] => 5755086
            [avg_volume] => 19896
            [market_cap] => 32517.05
            [open] => 16.46
            [y_close] => 16.47
            [change] => +0.00
            [perc_change] => 0.00
            [delay] => 0
            [trade_timestamp] => 21 seconds ago
            [trade_date_utc] => 20121204
            [trade_time_utc] => 173319
            [current_date_utc] => 20121204
            [current_time_utc] => 173340
            [symbol_url] => /finance?client=ig&q=MS
            [chart_url] => /finance/chart?q=NYSE:MS&tlf=12
            [disclaimer_url] => /help/stock_disclaimer.html
            [ecn_url] =>
            [isld_last] =>
            [isld_trade_date_utc] =>
            [isld_trade_time_utc] =>
            [brut_last] =>
            [brut_trade_date_utc] =>
            [brut_trade_time_utc] =>
            [daylight_savings] => false
        )
)
*/

function GoogleStockQuote($symbols)
{
  // create the XML query
  $url_part1 = "http://www.google.com/ig/api?";
  $url_part2 = '';
  foreach ($symbols as $symbol)
  {
    $url_part2 .= "stock=" . $symbol . "&";        # stock=VTI&stock=TLT ...
  }
  $url_part2 = substr($url_part2, 0, -1);          # strip off the last ampersand

  $url = $url_part1 . $url_part2;

  // create a SimpleXML object from the XML stream
  $xml = simplexml_load_file($url);

  // pull out the quotes
  foreach ($xml->finance as $quote)
  {
    $symbol = (string)$quote->symbol->attributes()->data;
    foreach ($quote as $info_name => $info)
    {
      if ($info_name == "symbol") continue;
      $quote_list[$symbol][$info_name] = (string)$info->attributes()->data;
    }
  }
  return $quote_list;
}

?>

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2378.htm


Exchange Quotes from XML using Excel (Google Finance)

Excel VBA function that returns stock information from Google Finance's XML stream.

Function GetQuote(Symbol As String, Optional GoogleParameter As String = "last") As String

    ' This is an Excel VBA function which returns data about a stock
    ' using Google Finance's XML stream
    
    ' On December 6, 2012 the Excel call =GetQuote("VTI")           returned 72.55
    '                                    =GetQuote("VTI", "volume") returned 68676
    
    ' Since this function returns strings, I've included a String_to_Number function below
    
    ' I got the essential ideas from here:
    ' http://msdn.microsoft.com/en-us/library/aa163921%28office.10%29.aspx
    
    ' BUT before you start, read this:
    ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba

    ' Note: Google has threatened to discontinue this feed in the future
    '       see my GetQuoteYahoo function as an alternative in that case
    '       but until then the two streams have special features
    ' see http://www.philadelphia-reflections.com/blog/2392.htm
    
    ' "GoogleParameter" is one of the following node names, it defaults to "last":
    ' "URL" is also valid in case you want to look at the XML stream returned
    '
    '<xml_api_reply version="1">
    '    <finance module_id="0" tab_id="0" mobile_row="0" mobile_zipped="1" row="0" section="0">
    '        <symbol data="VTI"/>
    '        <pretty_symbol data="VTI"/>
    '        <symbol_lookup_url data="/finance?client=ig&q=VTI"/>
    '        <company data="Vanguard Total Stock Market ETF"/>
    '        <exchange data="NYSEARCA"/>
    '        <exchange_timezone data=""/>
    '        <exchange_utc_offset data=""/>
    '        <exchange_closing data=""/>
    '        <divisor data="2"/>
    '        <currency data="USD"/>
    '        <last data="72.55"/>
    '        <high data="72.69"/>
    '        <low data="72.45"/>
    '        <volume data="68676"/>
    '        <avg_volume data=""/>
    '        <market_cap data="22385.76"/>
    '        <open data="72.54"/>
    '        <y_close data="72.60"/>
    '        <change data="+0.03"/>
    '        <perc_change data="0.04"/>
    '        <delay data="0"/>
    '        <trade_timestamp data="1 minute ago"/>
    '        <trade_date_utc data="20121206"/>
    '        <trade_time_utc data="145144"/>
    '        <current_date_utc data="20121206"/>
    '        <current_time_utc data="145323"/>
    '        <symbol_url data="/finance?client=ig&q=VTI"/>
    '        <chart_url data="/finance/chart?q=NYSEARCA:VTI&tlf=12"/>
    '        <disclaimer_url data="/help/stock_disclaimer.html"/>
    '        <ecn_url data=""/>
    '        <isld_last data="72.55"/>
    '        <isld_trade_date_utc data="20121206"/>
    '        <isld_trade_time_utc data="142903"/>
    '        <brut_last data=""/>
    '        <brut_trade_date_utc data=""/>
    '        <brut_trade_time_utc data=""/>
    '        <daylight_savings data="false"/>
    '    </finance>
    '</xml_api_reply>

    
    Dim GoogleXMLstream As MSXML2.DOMDocument
    
    Dim oChildren As MSXML2.IXMLDOMNodeList
    Dim oChild As MSXML2.IXMLDOMNode
    
    Dim fSuccess As Boolean
    Dim URL As String
    
    On Error GoTo HandleErr
    
    ' create the URL that requests the XML stream from Google Finance
    URL = "http://www.google.com/ig/api?stock=" & Trim(Symbol)
    
    ' In case you want to look at the XML
    If GoogleParameter = "URL" Then
        GetQuote = URL
        Exit Function
    End If
    
    ' pull in the XML stream
    Set GoogleXMLstream = New MSXML2.DOMDocument
    GoogleXMLstream.async = False                 ' wait for completion
    GoogleXMLstream.validateOnParse = False       ' do not validate the XML stream
    
    fSuccess = GoogleXMLstream.Load(URL)          ' pull in the feed
    
    If Not fSuccess Then                          ' quit on failure
      MsgBox "error loading Google Finance XML stream"
      Exit Function
    End If
    
    ' iterate through the nodes looking for one with the name in GoogleParameter
    GetQuote = GoogleParameter & " is not valid for GetQuote function"
    
    Set oChildren = GoogleXMLstream.DocumentElement.LastChild.ChildNodes
    
    For Each oChild In oChildren
    
        If oChild.nodeName = GoogleParameter Then
        
            GetQuote = oChild.Attributes.getNamedItem("data").Text
            Exit Function
            
        End If
        
    Next oChild
        
' error handlers
ExitHere:
            Exit Function
HandleErr:
            MsgBox "Error " & Err.Number & ": " & Err.Description
            Resume ExitHere
            Resume
End Function

Function String_to_Number(num_as_string As String) As Double

    ' The GetQuote function returns string values
    ' This function converts numbers in string format to double

    String_to_Number = Val(num_as_string)

End Function



How to install an Excel macro

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Tools > References > Microsoft XML v6 per the picture below
  5. Switch back to your spreadsheet (x out of the VBA screen if you want)
{Microsoft XML v6 installation}

Thanks to declaring early bound msxml object throws an error in vba for both the clue and the picture

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2385.htm


Black-Scholes-Merton in Excel

The provenance is numerous sources including Wilmott, Hull and Benninga but I wrote the majority of this myself while I was at MIT in the Master of Finance program.

Option Explicit

' Black Scholes Merton Excel VBA George Fisher MIT Fall 2011
'      d1, d2
'      N  (en)     std normal CDF
'      N' (nprime) std normal PDF
'
'      Binary Options
'      Euro Call & Put; Black's Model Call & Put
'      Black's Formulation
'      Greeks
'      Implied Volatility

'      Put/Call Parity

'      American_Call_Dividend
'      American_Put_Binomial

'
' Also includes
'      risk-neutral prob
'      nodal value of a binimial tree
'      Monte Carlo terminal value of one stock path
'      Monte Carlo next-step along one stock path
'      forward prices & rates
'      CAGR
'      randn/randn_ssdt
'      cell-display functions
'      convert discrete to continuous interest rate


' Developer > Visual Basic (Alt_F11)
' Insert > Module


' Interest is
'       risk-free rate
'       domestic risk-free rate for currencies

' Yield is
'       dividend yield for stock
'       lease rate for commodities
'       foreign currency risk-free rate for currencies

' Sigma is the standard deviation of the underlying asset

' Time is a year fraction: for 3-months ... Time = 3/12

' Stock is S_0

' Exercise is K

' => Interest, Yield, Sigma, Time are all annual numbers
' => Time = 0 is the value at maturity
'        most of the functions accomodate this
'        for some, it's infinity or otherwise meaningless
' => Sigma = 0 is also accomodated in most functions

''
''   Utilities
''   ---------
''

' N: the standard-normal CDF

Function en(x)

    en = Application.NormSDist(x)

End Function

' N': the first derivative of N(x) ... the standard normal PDF

Function nprime(x As Double) As Double

    nprime = Exp(-0.5 * x * x) / Sqr(2 * 3.1415926)

End Function

' Random Normal (epsilon)

Function RandN()
' produces a standard normal random variable epsilon

    RandN = Application.NormSInv(Rnd())
    
End Function
Function RandN_ssdt(ssdt)
' produces a standard normal random variable epsilon times sigma*sqrt(deltaT)

    RandN_ssdt = Application.NormInv(Rnd(), 0, ssdt)
    
End Function

' Functions to display a formula in a cell (from Benningna)

Function GetFormula(Rng As Range) As String

    Application.Volatile True
    GetFormula = "<-- " & Application.Text(Rng.FormulaLocal, "")
    
End Function

Function GgetFormula(Rng As Range) As String

    Application.Volatile True
    GgetFormula = Application.Text(Rng.FormulaLocal, "")
    
End Function

' binomial tree risk-neutral probability (Hull 7th edition Ch 19 P 409)

Function RiskNeutralProb(Interest, Yield, sigma, deltaT)

    Dim u As Double
    u = Exp(sigma * Sqr(deltaT))
    
    Dim d As Double
    d = Exp(-sigma * Sqr(deltaT))
    
    Dim a As Double
    a = Exp((Interest - Yield) * deltaT)
    
    Dim numerator As Double
    numerator = a - d
    
    Dim denominator As Double
    denominator = u - d
    
    RiskNeutralProb = numerator / denominator

End Function

' value of node j at time t in a binomial tree ***NOT DEBUGGED***
'   t starts at zero (on the left)
'   j starts at zero (at the bottom ... all downs)

Function BinomialValue(S_0, u, d, T, j)

    BinomialValue = S_0 * u ^ j * d ^ (T - j)

End Function

' terminal value of one stock path (one of many) for Monte Carlo simulation

Function MCterm(S_0, Interest, sigma, Time)
    
    MCterm = S_0 * Exp((Interest - 0.5 * sigma * sigma) * Time + sigma * RandN() * Sqr(Time))

End Function

' next step along a path for Monte Carlo simulation

Function MCnextStep(prior_value, Interest, sigma, deltaT)
    
    MCnextStep = prior_value + prior_value * (Interest * deltaT + sigma * RandN() * Sqr(deltaT))

End Function

' next step along a path for Monte Carlo simulation;
' Antithetic Variable ***NOT SURE ABOUT THIS***

Function MCnextStepAV(prior_value, Interest, sigma, deltaT)
    
    Dim f1, f2 As Double
    f1 = prior_value + prior_value * (Interest * deltaT + sigma * RandN() * Sqr(deltaT))
    f2 = prior_value + prior_value * (Interest * deltaT + sigma * -RandN() * Sqr(deltaT))
    
    MCnextStepAV = (f1 + f2) / 2

End Function


' Call & Put prices derived from put-call parity

Function CallParity(Stock, Exercise, Time, Interest, Yield, Put_price)

    CallParity = Put_price + _
                 Stock * Exp(-Yield * Time) - _
                 Exercise * Exp(-Interest * Time)
         
End Function

Function PutParity(Stock, Exercise, Time, Interest, Yield, Call_price)

    PutParity = Call_price + _
                Exercise * Exp(-Interest * Time) - _
                Stock * Exp(-Yield * Time)
         
End Function

' forward price

Function ForwardPrice(Spot, Time, Interest, Yield)

    ForwardPrice = Spot * Exp((Interest - Yield) * Time)

End Function

' forward rate from Time1 to Time2 (discrete compounding)

Function ForwardRate(SpotInterest1, Time1, SpotInterest2, Time2)

    Dim numerator As Double
    numerator = (1 + SpotInterest2) ^ Time2
    
    Dim denominator As Double
    denominator = (1 + SpotInterest1) ^ Time1
    
    ForwardRate = ((numerator / denominator) ^ (1 / (Time2 - Time1))) - 1

End Function

' CAGR

Function CAGR(Starting_value, Ending_Value, Number_of_years, Optional form)

    ' the default for is discrete; the alternative is continuous
    ' the parameter "form" is optional; leave out for discrete, put in a zero for continuous

    If IsMissing(form) Then
        CAGR = ((Ending_Value / Starting_value) ^ (1 / Number_of_years)) - 1
    Else
        CAGR = Log(Ending_Value / Starting_value) / Number_of_years
    End If
    

End Function

Function r_continuous(r_discrete, compounding_periods_per_year)

    Dim m As Double
    m = compounding_periods_per_year
    
    r_continuous = m * Log(1 + r_discrete / m)

End Function

' Convert TO discrete compounding FROM continuous
'
' t_discrete = m * (exp(r_continuous / m) - 1)
'
' where m is the number of compounding periods per year
'
Function r_discrete(r_continuous As Double, m As Integer) As Double

  r_discrete = m * (Exp(r_continuous / m) - 1)

End Function

' --------------------------------------------------------------------------------

''
''   Black Scholes
''   -------------
''

Function dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    ' (365 * 24 * 60 * 60) = number of seconds in a year =  31,536,000
    ' 1 / (365 * 24 * 60 * 60) = 3.17097919837646E-08
    ' the year fraction of a second
    If Time <= 0 Then Time = 1E-20

    dOne = (Log(Stock / Exercise) + (Interest - Yield + 0.5 * sigma * sigma) * Time) _
            / (sigma * Sqr(Time))
            
End Function

Function dTwo(Stock, Exercise, Time, Interest, Yield, sigma)

    ' (365 * 24 * 60 * 60) = number of seconds in a year =  31,536,000
    ' 1 / (365 * 24 * 60 * 60) = 3.17097919837646E-08
    ' the year fraction of a second
    If Time <= 0 Then Time = 1E-20
    
    dTwo = (Log(Stock / Exercise) + (Interest - Yield - 0.5 * sigma * sigma) * Time) _
            / (sigma * Sqr(Time))
            
End Function

'
' Binary Options
'

' Digital: Cash or Nothing

Function CashCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time < 0.000000005 Then
        If Stock >= Exercise Then
            CashCall = 1
            Exit Function
        Else
            CashCall = 0
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_, Nd2
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    Nd2 = Application.NormSDist(d2_)
    
    CashCall = Exp(-Interest * Time) * Nd2

End Function

Function CashPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time < 0.000000005 Then
        If Stock >= Exercise Then
            CashPut = 0
            Exit Function
        Else
            CashPut = 1
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_, Nminusd2
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    Nminusd2 = Application.NormSDist(-d2_)
    
    CashPut = Exp(-Interest * Time) * Nminusd2

End Function

' Asset or Nothing

Function AssetCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time < 0.000000005 Then
        If Stock >= Exercise Then
            AssetCall = Stock
            Exit Function
        Else
            AssetCall = 0
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_, Nd1
    If Exercise < 0 Then Exit Function
    If Exercise < 0.000000005 Then
        Nd1 = 1
    Else
        d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
        Nd1 = Application.NormSDist(d1_)
    End If

    AssetCall = Stock * Exp(-Yield * Time) * Nd1

End Function

Function AssetPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time < 0.000000005 Then
       If Stock >= Exercise Then
            AssetPut = 0
            Exit Function
        Else
            AssetPut = Stock
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_, Nminusd1
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Nminusd1 = Application.NormSDist(-d1_)
    
    AssetPut = Stock * Exp(-Yield * Time) * Nminusd1

End Function

'
'  Black's Formulation
'

Function BFormulationCall(Forward, Exercise, Time, Interest, Yield, sigma)

    Dim d1 As Double, d2 As Double
    d1 = (Log(Forward / Exercise) / (sigma * Sqr(Time))) + ((sigma * Sqr(Time)) / 2)
    d2 = (Log(Forward / Exercise) / (sigma * Sqr(Time))) - ((sigma * Sqr(Time)) / 2)
    
    BFormulationCall = Exp(-Interest * Time) * (Forward * en(d1) - Exercise * en(d2))
  
End Function

'
' European Call and Put
'

Function EuroCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time = 0 Then
        EuroCall = Application.Max(0, Stock - Exercise)
        Exit Function
    End If
    
    If sigma = 0 Then
        EuroCall = Application.Max(0, Exp(-Yield * Time) * Stock - Exp(-Interest * Time) * Exercise)
        Exit Function
    End If
    
    Dim d1_ As Double, d2_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)

    EuroCall = Stock * Exp(-Yield * Time) * Application.NormSDist(d1_) _
               - Exercise * Exp(-Interest * Time) * Application.NormSDist(d2_)
     
End Function

Function EuroPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time = 0 Then
        EuroPut = Application.Max(0, Exercise - Stock)
        Exit Function
    End If
    
    If sigma = 0 Then
        EuroPut = Application.Max(0, Exp(-Interest * Time) * Exercise - Exp(-Yield * Time) * Stock)
        Exit Function
    End If

    EuroPut = Exercise * Exp(-Interest * Time) * _
     Application.NormSDist(-dTwo(Stock, Exercise, Time, Interest, Yield, sigma)) - _
     Stock * Exp(-Yield * Time) * Application.NormSDist(-dOne(Stock, Exercise, _
        Time, Interest, Yield, sigma))
        
End Function

'
' Black's Model
'
Function VeronessiCaplet(n, Z, f, K, sigma, Tminus1)
    ' caplet for period T_i+1
    '
    ' n = time in years between periods (1 for a year; 0.25 for quarterly)
    ' Z = discount rate Z(0, T_i+1)
    ' f = forward rate  f(0, T_i, T_i+1) = (1/n) * [Z(0, T_i) / Z(0, T_i+1) -1]
    ' K = exercise rate/cap rate
    ' sigma = annual vol of f
    ' Tminus1 = T_i
    '
    
    Dim d1_ As Double, d2_ As Double
    d1_ = (1 / (sigma * Sqr(Tminus1))) * Log(f / K) + 0.5 * sigma * Sqr(Tminus1)
    d2_ = d1_ - sigma * Sqr(Tminus1)
    
    VeronessiCaplet = n * (Z * 100) * (f * Application.NormSDist(d1_) - K * Application.NormSDist(d2_))
    
End Function

Function BlacksModelCall(Forward, Exercise, Time, Spot_Yield_to_term_of_option, vol_of_forward)

    Dim Interest As Double, sigma As Double
    Interest = Spot_Yield_to_term_of_option
    sigma = vol_of_forward

    If Time = 0 Then
        BlacksModelCall = Application.Max(0, Forward - Exercise)
        Exit Function
    End If
    
    If sigma = 0 Then
        BlacksModelCall = Application.Max(0, Exp(-Interest * Time) * (Forward - Exercise))
        Exit Function
    End If
    
    Dim d1_ As Double, d2_ As Double
    d1_ = (Log(Forward / Exercise) + ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
    d2_ = (Log(Forward / Exercise) - ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))

    BlacksModelCall = Exp(-Interest * Time) * (Forward * Application.NormSDist(d1_) _
                                             - Exercise * Application.NormSDist(d2_))
     
End Function
Function BlacksModelPut(Forward, Exercise, Time, Spot_Yield_to_term_of_option, vol_of_forward)

    Dim Interest As Double, sigma As Double
    Interest = Spot_Yield_to_term_of_option
    sigma = vol_of_forward

    If Time = 0 Then
        BlacksModelPut = Application.Max(0, Exercise - Forward)
        Exit Function
    End If
    
    If sigma = 0 Then
        BlacksModelPut = Application.Max(0, Exp(-Interest * Time) * (Exercise - Forward))
        Exit Function
    End If
    
    Dim d1_ As Double, d2_ As Double
    d1_ = (Log(Forward / Exercise) + ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
    d2_ = (Log(Forward / Exercise) - ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))

    BlacksModelPut = Exp(-Interest * Time) * (Exercise * Application.NormSDist(-d2_) _
                                            - Forward * Application.NormSDist(-d1_))
     
End Function

'
' Per Kerry Back Chapt5.bas
'

Function American_Put_Binomial(S0, K, r, sigma, q, T, n)
'
' Inputs are S0 = initial stock price
'            K = strike price
'            r = risk-free rate
'            sigma = volatility
'            q = dividend yield
'            T = time to maturity
'            N = number of time periods
'
Dim dt, u, d, pu, dpu, dpd, u2, S, i, j
Dim PutV() As Double
ReDim PutV(n)
dt = T / n                              ' length of time period
u = Exp(sigma * Sqr(dt))                ' size of up step
d = 1 / u                               ' size of down step
pu = (Exp((r - q) * dt) - d) / (u - d)  ' probability of up step
dpu = Exp(-r * dt) * pu                 ' one-period discount x prob of up step
dpd = Exp(-r * dt) * (1 - pu)           ' one-period discount x prob of down step
u2 = u * u
S = S0 * d ^ n                          ' stock price at bottom node at last date
PutV(0) = Application.Max(K - S, 0)     ' put value at bottom node at last date
For j = 1 To n
    S = S * u2
    PutV(j) = Application.Max(K - S, 0)
Next j
For i = n - 1 To 0 Step -1              ' back up in time to date 0
    S = S0 * d ^ i                      ' stock price at bottom node at date i
    PutV(0) = Application.Max(K - S, dpd * PutV(0) + dpu * PutV(1))
    For j = 1 To i                      ' step up over nodes at date i
        S = S * u2
        PutV(j) = Application.Max(K - S, dpd * PutV(j) + dpu * PutV(j + 1))
    Next j
Next i
American_Put_Binomial = PutV(0)         ' put value at bottom node at date 0
End Function

'
' from Kerry Back Chapt8.bas
'
Function American_Call_Dividend(S, K, r, sigma, Div, TDiv, TCall)
'
' Inputs are S = initial stock price
'            K = strike price
'            r = risk-free rate
'            sigma = volatility
'            Div = cash dividend
'            TDiv = time until dividend payment
'            TCall = time until option matures >= TDiv
'
Dim LessDiv, upper, tol, lower, flower, fupper, guess, fguess
Dim LessDivStar, d1, d2, d1prime, d2prime, rho, N1, N2, M1, M2
LessDiv = S - Exp(-r * TDiv) * Div          ' stock value excluding dividend
If Div / K <= 1 - Exp(-r * (TCall - TDiv)) Then  ' early exercise cannot be optimal
    American_Call_Dividend = Black_Scholes_Call(LessDiv, K, r, sigma, 0, TCall)
    Exit Function
End If
'
' Now we find an upper bound for the bisection.
'
upper = K
Do While upper + Div - K < Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv)
   upper = 2 * upper
Loop
'
' Now we use bisection to compute Zstar = LessDivStar.
'
tol = 10 ^ -6
lower = 0
flower = Div - K
fupper = upper + Div - K - Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv)
guess = 0.5 * lower + 0.5 * upper
fguess = guess + Div - K - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
Do While upper - lower > tol
    If fupper * fguess < 0 Then
        lower = guess
        flower = fguess
        guess = 0.5 * lower + 0.5 * upper
        fguess = guess + Div - K _
               - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
    Else
        upper = guess
        fupper = fguess
        guess = 0.5 * lower + 0.5 * upper
        fguess = guess + Div - K _
               - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
    End If
Loop
LessDivStar = guess
'
' Now we calculate the probabilities and the option value.
'
d1 = (Log(LessDiv / LessDivStar) _
   + (r + sigma ^ 2 / 2) * TDiv) / (sigma * Sqr(TDiv))
d2 = d1 - sigma * Sqr(TDiv)
d1prime = (Log(LessDiv / K) _
        + (r + sigma ^ 2 / 2) * TCall) / (sigma * Sqr(TCall))
d2prime = d1prime - sigma * Sqr(TCall)
rho = -Sqr(TDiv / TCall)
N1 = Application.NormSDist(d1)
N2 = Application.NormSDist(d2)
M1 = BiNormalProb(-d1, d1prime, rho)
M2 = BiNormalProb(-d2, d2prime, rho)
American_Call_Dividend = LessDiv * N1 + Exp(-r * TDiv) * (Div - K) * N2 _
                       + LessDiv * M1 - Exp(-r * TCall) * K * M2
End Function

'
' Greeks from Hull (Edition 7) Chapter 17 p378
' --------------------------------------------
'

' per the Black Scholes PDE for a portfolio of options
' on a single non-dividend-paying underlying stock
'
' Theta + Delta * S * r + Gamma * 0.5 * sigma^2 * S^2 = r * Portfolio_Value

' Per Hull: for large option portfolios, usually created by banks in the
' course of buying and selling OTC options to clients, the portfolio is
' Delta hedged every day and Gamma/Vega hedged as needed
'
'             Delta      Gamma      Vega
' Portfolio   P_delta    P_gamma    P_vega
' Option1     w1*1_delta w1*1_gamma w1*1_vega
' Option2     w2*2_delta w2*2_gamma w2*2_vega
'
' Set the columns equal to zero and solve the simultaneous equations

' Most OTC options are sold close to the money; high gamma and vega
' as (if) the price of the underlying move away gamma and vega decline

' Delta
' -----
'
' If a bank sells a call to a client (short a call)
'   ... it hedges itself with a synthetic long call
'
' Synthetic long call = Delta * Stock_price - bond
' ie., borrow the money to buy Delta shares of the stock
'

Function DeltaCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time = 0 Then
        If Stock > Exercise Then
            DeltaCall = 1
            Exit Function
        Else
            DeltaCall = 0
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    DeltaCall = Exp(-Yield * Time) * Application.NormSDist(d1_)
    
End Function

Function DeltaPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If Time = 0 Then
        If Stock < Exercise Then
            DeltaPut = -1
            Exit Function
        Else
            DeltaPut = 0
            Exit Function
        End If
    End If
    
    If sigma = 0 Then
        sigma = 0.0000000001
    End If
    
    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    DeltaPut = Exp(-Yield * Time) * (Application.NormSDist(d1_) - 1)
        
End Function

'
' Gamma the convexity
' -----
'

Function OptionGamma(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    OptionGamma = nprime(d1_) * Exp(-Yield * Time) _
        / (Stock * sigma * Sqr(Time))

End Function

'
' Theta the decay in the value of an option/portfolio of options as time passes
' -----
'
' divide by 365 for "per calendar day"; 252 for "per trading day"
'
' In a delta-neutral portfolio, Theta is a proxy for Gamma
'

Function ThetaCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Dim d2_ As Double
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd1_ As Double
    Nd1_ = Application.NormSDist(d1_)
    Dim Nd2_ As Double
    Nd2_ = Application.NormSDist(d2_)
    
    ThetaCall = -Stock * nprime(d1_) * sigma * Exp(-Yield * Time) / (2 * Sqr(Time)) _
        + Yield * Stock * Nd1_ * Exp(-Yield * Time) _
        - Interest * Exercise * Exp(-Interest * Time) * Nd2_
    
End Function

Function ThetaPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Dim d2_ As Double
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd1_ As Double
    Nminusd1_ = Application.NormSDist(-d1_)
    Dim Nminusd2_ As Double
    Nminusd2_ = Application.NormSDist(-d2_)
    
    ThetaPut = -Stock * nprime(d1_) * sigma * Exp(-Yield * Time) / (2 * Sqr(Time)) _
        - Yield * Stock * Nminusd1_ * Exp(-Yield * Time) _
        + Interest * Exercise * Exp(-Interest * Time) * Nminusd2_
    
End Function

'
' Vega the sensitivity to changes in the volatility of the underlying
' ----
'
Function Vega(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Vega = Stock * Sqr(Time) * nprime(d1_) * Exp(-Yield * Time)
    
End Function

'
' Rho the sensitivity to changes in the interest rate
' ---
'

'
' Note the various Rho calculations see Hull 7th Edition Ch 17 P378
'

Function RhoFuturesCall(Stock, Exercise, Time, Interest, Yield, sigma)

    RhoFuturesCall = -EuroCall(Stock, Exercise, Time, Interest, Yield, sigma) * Time
    
End Function
Function RhoFuturesPut(Stock, Exercise, Time, Interest, Yield, sigma)

    RhoFuturesPut = -EuroPut(Stock, Exercise, Time, Interest, Yield, sigma) * Time
    
End Function

'
' The Rho corresponding to the domestic interest rate is RhoCall/Put, below
'                              foreign  interest rate is RhoFXCall/Put, shown here
'
Function RhoFXCall(Stock, Exercise, Time, Interest, Yield, sigma)

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd1_ As Double
    Nd1_ = Application.NormSDist(d1_)
    
    RhoFXCall = -Time * Exp(-Yield * Time) * Stock * Nd1_
    
End Function
Function RhoFXPut(Stock, Exercise, Time, Interest, Yield, sigma)

    Dim d1_ As Double
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd1_ As Double
    Nminusd1_ = Application.NormSDist(-d1_)
    
    RhoFXPut = Time * Exp(-Yield * Time) * Stock * Nminusd1_
    
End Function

'
' "Standard" Rhos
'

Function RhoCall(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_ As Double
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd2_ As Double
    Nd2_ = Application.NormSDist(d2_)
    
    RhoCall = Exercise * Time * Exp(-Interest * Time) * Nd2_
    
End Function

Function RhoPut(Stock, Exercise, Time, Interest, Yield, sigma)

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_ As Double
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd2_ As Double
    Nminusd2_ = Application.NormSDist(-d2_)
    
    RhoPut = -Exercise * Time * Exp(-Interest * Time) * Nminusd2_
    
End Function

'
' Since Bennigna and Back produce identical numbers
' and MATLAB produced numbers that are +/- 2%, I'm
' inclined to go with these numbers
'

'
' Implied Volatility from Benningna
' ---------------------------------
'
Function EuroCallVol(Stock, Exercise, Time, Interest, Yield, Call_price)

    Dim High, Low As Double
    High = 2
    Low = 0
    Do While (High - Low) > 0.000001
    If EuroCall(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
        Call_price Then
             High = (High + Low) / 2
             Else: Low = (High + Low) / 2
    End If
    Loop
    EuroCallVol = (High + Low) / 2
    
End Function

Function EuroPutVol(Stock, Exercise, Time, Interest, Yield, Put_price)

    Dim High, Low As Double
    High = 2
    Low = 0
    Do While (High - Low) > 0.000001
    If EuroPut(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
        Put_price Then
             High = (High + Low) / 2
             Else: Low = (High + Low) / 2
    End If
    Loop
    EuroPutVol = (High + Low) / 2
    
End Function

'
' Implied Volatility from Kerry Back p64
' Chapt3.bas Newton Raphson technique
' Answer IDENTICAL to Bennigna (EuroCallVol)
'
Function Black_Scholes_Call(S, K, r, sigma, q, T)

    Black_Scholes_Call = EuroCall(S, K, T, r, q, sigma)

End Function
Function Black_Scholes_Call_Implied_Vol(S, K, r, q, T, CallPrice)
'
' Inputs are S = initial stock price
'            K = strike price
'            r = risk-free rate
'            q = dividend yield
'            T = time to maturity
'            CallPrice = call price
'
Dim tol, lower, flower, upper, fupper, guess, fguess
If CallPrice < Exp(-q * T) * S - Exp(-r * T) * K Then
    MsgBox ("Option price violates the arbitrage bound.")
    Exit Function
End If
tol = 10 ^ -6
lower = 0
flower = Black_Scholes_Call(S, K, r, lower, q, T) - CallPrice
upper = 1
fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Do While fupper < 0                   ' double upper until it is an upper bound
    upper = 2 * upper
    fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Loop
guess = 0.5 * lower + 0.5 * upper
fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
Do While upper - lower > tol               ' until root is bracketed within tol
    If fupper * fguess < 0 Then            ' root is between guess and upper
        lower = guess                      ' make guess the new lower bound
        flower = fguess
        guess = 0.5 * lower + 0.5 * upper  ' new guess = bi-section
        fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
    Else                                   ' root is between lower and guess
        upper = guess                      ' make guess the new upper bound
        fupper = fguess
        guess = 0.5 * lower + 0.5 * upper  ' new guess = bi-section
        fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
    End If
Loop
Black_Scholes_Call_Implied_Vol = guess
End Function

'
' Implied Volatility from Wilmott Into Ch 8 p192 Newton Raphson***NOT DEBUGGED***
'
Function ImpVolCall(Stock, Exercise, Time, Interest, Yield, Call_price)

    Volatility = 0.2
    epsilon = 0.0001
    dv = epsilon + 1
    
    While Abs(dv) > epsilon
        PriceError = EuroCall(Stock, Exercise, Time, Interest, Yield, Volatility) - Call_price
        dv = PriceError / Vega(Stock, Exercise, Time, Interest, Yield, Volatility)
        Volatility = Volatility - dv
    Wend
    
    ImpVolCall = Volatility

End Function

How to install an Excel macro

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Switch back to your spreadsheet (x out of the VBA screen if you want)

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2394.htm


Black-Scholes-Merton in Python

The provenance is numerous sources including Back, Benninga, Hull and Wilmott but I wrote the majority of this myself while I was at MIT in the Master of Finance program.

# Black Scholes Merton for Python (from Excel VBA)
# George Fisher MIT Spring 2012
#
# I created BSM routines for Excel first and then converted to Python
# I drew upon published work by Back, Benninga, Hull and Wilmott but the majority is my own original work
#
#      d1, d2
#      N  (en/phi) std normal CDF
#      N' (nprime) std normal PDF
#
#      Binary Options
#      Euro Call & Put
#      Greeks
#      Implied Volatility

#      Put/Call Parity

#      American_Call_Dividend
#      American_Put_Binomial

#
# Also includes
#      risk-neutral prob
#      forward prices & rates
#      CAGR
#      randn/randn_ssdt
#      convert discrete to continuous interest rate

# Interest is
#       risk-free rate
#       domestic risk-free rate for currencies

# Yield is
#       dividend yield for stock
#       lease rate for commodities
#       foreign currency risk-free rate for currencies

# Sigma is the standard deviation of the underlying asset

# Time is a year fraction: for 3-months ... Time = 3/12

# Stock is S_0

# Exercise is K

# => Interest, Yield, Sigma, Time are all annual numbers
# => Time = 0 is the value at maturity
#        most of the functions accomodate this
#        for some, it's infinity or otherwise meaningless
# => Sigma = 0 is also accomodated in most functions

##
##   Utilities
##   ---------
##

# N: the standard-normal CDF

def en(x):
    return phi(x)
    

def phi(x):
    import math
    # constants
    a1 =  0.254829592
    a2 = -0.284496736
    a3 =  1.421413741
    a4 = -1.453152027
    a5 =  1.061405429
    p  =  0.3275911

    # Save the sign of x
    sign = 1
    if x < 0:
        sign = -1
    x = abs(x)/math.sqrt(2.0)

    # A&S formula 7.1.26
    t = 1.0/(1.0 + p*x)
    y = 1.0 - (((((a5*t + a4)*t) + a3)*t + a2)*t + a1)*t*math.exp(-x*x)

    return 0.5*(1.0 + sign*y)


# N': the first derivative of N(x) ... the standard normal PDF

def nprime(x):
    import math
    return math.exp(-0.5 * x * x) / math.sqrt(2 * 3.1415926)

# Random Normal (epsilon)

def RandN():
    # produces a standard normal random variable epsilon
    import random
    return random.gauss(0,1)

def RandN_ssdt(ssdt):
    # produces a standard normal random variable epsilon times sigma*sqrt(deltaT)
    import random
    return random.gauss(0,ssdt)

# binomial tree risk-neutral probability (Hull 7th edition Ch 19 P 409)

def RiskNeutralProb(Interest, Yield, sigma, deltaT):
    import math

    u = math.exp( sigma * math.sqrt(deltaT))
    d = math.exp(-sigma * math.sqrt(deltaT))
    
    a = math.exp((Interest - Yield) * deltaT)
    
    numerator = a - d
    denominator = u - d
    
    return numerator / denominator

# Call & Put prices derived from put-call parity
#                                ---------------

def CallParity(Stock, Exercise, Time, Interest, Yield, Put_price):

    import math
    return Put_price + Stock * math.exp(-Yield * Time) - Exercise * math.exp(-Interest * Time)

def PutParity(Stock, Exercise, Time, Interest, Yield, Call_price):

    import math
    return Call_price + Exercise * math.exp(-Interest * Time) - Stock * math.exp(-Yield * Time)

# forward price

def ForwardPrice(Spot, Time, Interest, Yield):

    import math
    return Spot * math.exp((Interest - Yield) * Time)

# forward rate from Time1 to Time2 (discrete compounding)

def ForwardRate(SpotInterest1, Time1, SpotInterest2, Time2):

    numerator   = (1 + SpotInterest2) ** Time2
    denominator = (1 + SpotInterest1) ** Time1
    
    return ((numerator / denominator) ** (1 / (Time2 - Time1))) - 1

# CAGR

def CAGRd(Starting_value, Ending_Value, Number_of_years):

    # discrete CAGR

    return ((Ending_Value / Starting_value) ** (1 / Number_of_years)) - 1

# Convert TO continuous compounding FROM discrete

def r_continuous(r_discrete, compounding_periods_per_year):

    import math
    m = compounding_periods_per_year
    return m * math.log(1 + r_discrete / m)

# Convert TO discrete compounding FROM continuous
#
# t_discrete = m * (exp(r_continuous / m) - 1)
#
# where m is the number of compounding periods per year
#
def r_discrete(r_continuous, compounding_periods_per_year):

  import math
  m = compounding_periods_per_year
  return m * (math.exp(r_continuous / m) - 1)

# --------------------------------------------------------------------------------

##
##   Black Scholes
##   -------------
##

def dOne(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    return (math.log(Stock / Exercise) + (Interest - Yield + 0.5 * sigma * sigma) * Time) / (sigma * math.sqrt(Time))

def dTwo(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    return (math.log(Stock / Exercise) + (Interest - Yield - 0.5 * sigma * sigma) * Time) / (sigma * math.sqrt(Time))

#
# Binary Options
#

# Digital: Cash or Nothing

def CashCall(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time < 0.000000005:
        if Stock >= Exercise:
            return 1
        else:
            return 0
    
    if sigma == 0:
        sigma = 0.0000000001
    
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    Nd2 = phi(d2_)
    
    return math.exp(-Interest * Time) * Nd2

def CashPut(Stock, Exercise, Time, Interest, Yield, sigma):

    if Time < 0.000000005:
        if Stock >= Exercise:
            return 0
        else:
            return 1
    
    if sigma == 0:
        sigma = 0.0000000001
    
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    Nminusd2 = phi(-d2_)
    
    return math.exp(-Interest * Time) * Nminusd2

# Asset or Nothing

def AssetCall(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time < 0.000000005:
        if Stock >= Exercise:
            return Stock
        else:
            return 0   
    
    if sigma == 0:
        sigma = 0.0000000001
    
    if Exercise < 0.000000005:
        Nd1 = 1
    else:
        d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
        Nd1 = phi(d1_)
    
    return Stock * math.exp(-Yield * Time) * Nd1

def AssetPut(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time < 0.000000005:
        if Stock >= Exercise:
            return 0
        else:
            return Stock
    
    if sigma == 0:
        sigma = 0.0000000001
    
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Nminusd1 = phi(-d1_)
    
    return Stock * math.exp(-Yield * Time) * Nminusd1

#
# European Call and Put
# ---------------------
#

def EuroCall(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time == 0:
        return max(0, Stock - Exercise)
    
    if sigma == 0:
        return max(0, math.exp(-Yield * Time) * Stock - math.exp(-Interest * Time) * Exercise)
    
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)

    return Stock * math.exp(-Yield * Time) * phi(d1_) - Exercise * math.exp(-Interest * Time) * phi(d2_)

def EuroPut(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time == 0:
        return max(0, Exercise - Stock)
    
    if sigma == 0:
        return max(0, math.exp(-Interest * Time) * Exercise - math.exp(-Yield * Time) * Stock)
        
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)

    return Exercise * math.exp(-Interest * Time) * phi(-d2_) - Stock * math.exp(-Yield * Time) * phi(-d1_)


#
# American Put
# ------------
# Per Kerry Back Chapt5.bas
#

def American_Put_Binomial(S0, K, r, sigma, q, T, N):
    import math
    """
    #
    # Inputs are S0 = initial stock price
    #            K = strike price
    #            r = risk-free rate
    #            sigma = volatility
    #            q = dividend yield
    #            T = time to maturity
    #            N = number of time periods
    #
    """
    dt = T / N                                   # length of time period
    u = math.exp(sigma * math.sqrt(dt))          # size of up step
    d = 1 / u                                    # size of down step
    pu = (math.exp((r - q) * dt) - d) / (u - d)  # probability of up step
    dpu = math.exp(-r * dt) * pu                 # one-period discount x prob of up step
    dpd = math.exp(-r * dt) * (1 - pu)           # one-period discount x prob of down step
    u2 = u * u
    S = S0 * d ** N                              # stock price at bottom node at last date
    PutV[0] = max(K - S, 0)                      # put value at bottom node at last date
    for j in range(1,N+1):
        S = S * u2
        PutV[j] = max(K - S, 0)

    for i in range(N - 1, 0, -1):                # back up in time to date 0
        S = S0 * d ** i                          # stock price at bottom node at date i
        PutV[0] = max(K - S, dpd * PutV(0) + dpu * PutV(1))
        for j in range(1,i+1):                   # step up over nodes at date i
            S = S * u2
            PutV[j] = max(K - S, dpd * PutV(j) + dpu * PutV(j + 1))

    return PutV[0]                               # put value at bottom node at date 0

#
# Greeks from Hull (Edition 7) Chapter 17 p378
# --------------------------------------------
#

# per the Black Scholes PDE for a portfolio of options
# on a single non-dividend-paying underlying stock
#
# Theta + Delta * S * r + Gamma * 0.5 * sigma**2 * S**2 = r * Portfolio_Value

# Per Hull: for large option portfolios, usually created by banks in the
# course of buying and selling OTC options to clients, the portfolio is
# Delta hedged every day and Gamma/Vega hedged as needed
#
#             Delta      Gamma      Vega
# Portfolio   P_delta    P_gamma    P_vega
# Option1     w1*1_delta w1*1_gamma w1*1_vega
# Option2     w2*2_delta w2*2_gamma w2*2_vega
#
# Set the columns equal to zero and solve the simultaneous equations

# Most OTC options are sold close to the money; high gamma and vega
# as (if) the price of the underlying move away gamma and vega decline

# Delta
# -----
#
# If a bank sells a call to a client (short a call)
#   ... it hedges itself with a synthetic long call
#
# Synthetic long call = Delta * Stock_price - bond
# ie., borrow the money to buy Delta shares of the stock
#

def DeltaCall(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time == 0:
        if Stock > Exercise:
            return 1
        else:
            return 0
    
    if sigma == 0:
        sigma = 0.0000000001

    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    return math.exp(-Yield * Time) * phi(d1_)

def DeltaPut(Stock, Exercise, Time, Interest, Yield, sigma):

    import math
    if Time == 0:
        if Stock < Exercise:
            return -1
        else:
            return 0
    
    if sigma == 0:
        sigma = 0.0000000001
    
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    return math.exp(-Yield * Time) * (phi(d1_) - 1)


#
# Gamma the convexity
# -----
#

def OptionGamma(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)

    OptionGamma = nprime(d1_) * math.exp(-Yield * Time) _
        / (Stock * sigma * math.sqrt(Time))

#
# Theta the decay in the value of an option/portfolio of options as time passes
# -----
#
# divide by 365 for "per calendar day"; 252 for "per trading day"
#
# In a delta-neutral portfolio, Theta is a proxy for Gamma
#

def ThetaCall(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Dim d2_
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd1_
    Nd1_ = phi(d1_)
    Dim Nd2_
    Nd2_ = phi(d2_)
    
    ThetaCall = -Stock * nprime(d1_) * sigma * math.exp(-Yield * Time) / (2 * math.sqrt(Time)) _
        + Yield * Stock * Nd1_ * math.exp(-Yield * Time) _
        - Interest * Exercise * math.exp(-Interest * Time) * Nd2_

def ThetaPut(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    Dim d2_
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd1_
    Nminusd1_ = phi(-d1_)
    Dim Nminusd2_
    Nminusd2_ = phi(-d2_)
    
    ThetaPut = -Stock * nprime(d1_) * sigma * math.exp(-Yield * Time) / (2 * math.sqrt(Time)) _
        - Yield * Stock * Nminusd1_ * math.exp(-Yield * Time) _
        + Interest * Exercise * math.exp(-Interest * Time) * Nminusd2_

#
# Vega the sensitivity to changes in the volatility of the underlying
# ----
#
def Vega(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Vega = Stock * math.sqrt(Time) * nprime(d1_) * math.exp(-Yield * Time)

#
# Rho the sensitivity to changes in the interest rate
# ---
#

#
# Note the various Rho calculations see Hull 7th Edition Ch 17 P378
#

def RhoFuturesCall(Stock, Exercise, Time, Interest, Yield, sigma):

    RhoFuturesCall = -EuroCall(Stock, Exercise, Time, Interest, Yield, sigma) * Time

def RhoFuturesPut(Stock, Exercise, Time, Interest, Yield, sigma):

    RhoFuturesPut = -EuroPut(Stock, Exercise, Time, Interest, Yield, sigma) * Time

#
# The Rho corresponding to the domestic interest rate is RhoCall/Put, below
#                              foreign  interest rate is RhoFXCall/Put, shown here
#
def RhoFXCall(Stock, Exercise, Time, Interest, Yield, sigma):

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd1_
    Nd1_ = phi(d1_)
    
    RhoFXCall = -Time * math.exp(-Yield * Time) * Stock * Nd1_

def RhoFXPut(Stock, Exercise, Time, Interest, Yield, sigma):

    Dim d1_
    d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd1_
    Nminusd1_ = phi(-d1_)
    
    RhoFXPut = Time * math.exp(-Yield * Time) * Stock * Nminusd1_

#
# "Standard" Rhos
#

def RhoCall(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nd2_
    Nd2_ = phi(d2_)
    
    RhoCall = Exercise * Time * math.exp(-Interest * Time) * Nd2_

def RhoPut(Stock, Exercise, Time, Interest, Yield, sigma):

    If sigma = 0 Then
        sigma = 0.0000000001
    End If

    Dim d2_
    d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
    
    Dim Nminusd2_
    Nminusd2_ = phi(-d2_)
    
    RhoPut = -Exercise * Time * math.exp(-Interest * Time) * Nminusd2_

#
# Since Bennigna and Back produce identical numbers
# and MATLAB produced numbers that are +/- 2%, I'm
# inclined to go with these numbers
#

#
# Implied Volatility from Benningna
# ---------------------------------
#
def EuroCallVol(Stock, Exercise, Time, Interest, Yield, Call_price):

    Dim High, Low
    High = 2
    Low = 0
    Do While (High - Low) > 0.000001
    If EuroCall(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
        Call_price Then
             High = (High + Low) / 2
             Else: Low = (High + Low) / 2
    End If
    Loop
    EuroCallVol = (High + Low) / 2

def EuroPutVol(Stock, Exercise, Time, Interest, Yield, Put_price):

    Dim High, Low
    High = 2
    Low = 0
    Do While (High - Low) > 0.000001
    If EuroPut(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
        Put_price Then
             High = (High + Low) / 2
             Else: Low = (High + Low) / 2
    End If
    Loop
    EuroPutVol = (High + Low) / 2

#
# Implied Volatility from Kerry Back p64
# Chapt3.bas Newton Raphson technique
# Answer IDENTICAL to Bennigna (EuroCallVol)
#
def Black_Scholes_Call(S, K, r, sigma, q, T):

    Black_Scholes_Call = EuroCall(S, K, T, r, q, sigma)

def Black_Scholes_Call_Implied_Vol(S, K, r, q, T, CallPrice):
#
# Inputs are S = initial stock price
#            K = strike price
#            r = risk-free rate
#            q = dividend yield
#            T = time to maturity
#            CallPrice = call price
#
Dim tol, lower, flower, upper, fupper, guess, fguess
If CallPrice < math.exp(-q * T) * S - math.exp(-r * T) * K Then
    MsgBox ("Option price violates the arbitrage bound.")
    Exit Function
End If
tol = 10 ^ -6
lower = 0
flower = Black_Scholes_Call(S, K, r, lower, q, T) - CallPrice
upper = 1
fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Do While fupper < 0                   # double upper until it is an upper bound
    upper = 2 * upper
    fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Loop
guess = 0.5 * lower + 0.5 * upper
fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
Do While upper - lower > tol               # until root is bracketed within tol
    If fupper * fguess < 0 Then            # root is between guess and upper
        lower = guess                      # make guess the new lower bound
        flower = fguess
        guess = 0.5 * lower + 0.5 * upper  # new guess = bi-section
        fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
    Else                                   # root is between lower and guess
        upper = guess                      # make guess the new upper bound
        fupper = fguess
        guess = 0.5 * lower + 0.5 * upper  # new guess = bi-section
        fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
    End If
Loop
Black_Scholes_Call_Implied_Vol = guess

#
# Implied Volatility from Wilmott Into Ch 8 p192 Newton Raphson***NOT DEBUGGED***
#
def ImpVolCall(Stock, Exercise, Time, Interest, Yield, Call_price):

    Volatility = 0.2
    epsilon = 0.0001
    dv = epsilon + 1
    
    While Abs(dv) > epsilon
        PriceError = EuroCall(Stock, Exercise, Time, Interest, Yield, Volatility) - Call_price
        dv = PriceError / Vega(Stock, Exercise, Time, Interest, Yield, Volatility)
        Volatility = Volatility - dv
    Wend
    
    ImpVolCall = Volatility



#
# from Kerry Back Chapt8.bas ... need Python's "BiNormalProb"
#
def American_Call_Dividend(S, K, r, sigma, Div, TDiv, TCall):
    import math
    """
#
# Inputs are S = initial stock price
#            K = strike price
#            r = risk-free rate
#            sigma = volatility
#            Div = cash dividend
#            TDiv = time until dividend payment
#            TCall = time until option matures >= TDiv
#
"""
    LessDiv = S - math.exp(-r * TDiv) * Div               # stock value excluding dividend
    If Div / K <= 1 - math.exp(-r * (TCall - TDiv)):      # early exercise cannot be optimal
        return Black_Scholes_Call(LessDiv, K, r, sigma, 0, TCall)
    #
    # Now we find an upper bound for the bisection.
    #
    upper = K
    while upper + Div - K < Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv):
        upper = 2 * upper
    #
    # Now we use bisection to compute Zstar = LessDivStar.
    #
    tol = 10 ** -6
    lower = 0
    flower = Div - K
    fupper = upper + Div - K - Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv)
    guess = 0.5 * lower + 0.5 * upper
    fguess = guess + Div - K - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
    
    while upper - lower > tol:
        if fupper * fguess < 0:
            lower = guess
            flower = fguess
            guess = 0.5 * lower + 0.5 * upper
            fguess = guess + Div - K - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
        else:
            upper = guess
            fupper = fguess
            guess = 0.5 * lower + 0.5 * upper
            fguess = guess + Div - K - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)

    LessDivStar = guess
    #
    # Now we calculate the probabilities and the option value.
    #
    d1 = (math.log(LessDiv / LessDivStar) + (r + sigma ** 2 / 2) * TDiv) / (sigma * math.sqrt(TDiv))
    d2 = d1 - sigma * math.sqrt(TDiv)
    d1prime = (math.log(LessDiv / K) + (r + sigma ** 2 / 2) * TCall) / (sigma * math.sqrt(TCall))
    d2prime = d1prime - sigma * math.sqrt(TCall)
    rho = -math.sqrt(TDiv / TCall)
    N1 = phi(d1)
    N2 = phi(d2)
    M1 = BiNormalProb(-d1, d1prime, rho)
    M2 = BiNormalProb(-d2, d2prime, rho)
    return LessDiv * N1 + math.exp(-r * TDiv) * (Div - K) * N2 + LessDiv * M1 - math.exp(-r * TCall) * K * M2


My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2396.htm


Get Historical Stock Data in Excel (single date; from Yahoo Finance)

This VBA function returns data for a stock on a single specific date, using Yahoo Finance's CSV data.

Function GetHistoricalData(Symbol As String, _
                           QuoteDate As Date, _
                  Optional QuoteType As String = "AdjClose") As Double

    ' Returns stock data for "Symbol" on "QuoteDate" using Yahoo Finance
    '
    ' The choices for "QuoteType" are
    '   Open
    '   High
    '   Low
    '   Close
    '   Volume
    '   Adj Close or AdjClose (Default)
    '
    ' ... and these calculated values:
    '   MAX (maximum of Open, High, Low, Close, AdjClose)
    '   MIN (minimum of Open, High, Low, Close, AdjClose)
    '   AVG (average of High, Low)
    '
    ' for example
    '    =GetHistoricalData("BRK.A", DATEVALUE("2/26/2012"))
    ' returns
    '    120,350.00
    '
    ' (you'd be more likely to refer to a cell with a date in it)
    '
    ' Thanks to Peter Urbani at http://www.wilmott.com/messageview.cfm?catid=10&threadid=25730
    '
    ' Note: I figure out if you gave me a weekend and I look for the previous Friday
    '       but if you give me a weekday holiday, I will produce unpredictable results
    '       give me "02/30/1998" and I'll give you #VALUE
    
    
    ' If you want current data see the following:
    '
    ' http://www.philadelphia-reflections.com/blog/2392.htm
    ' http://www.philadelphia-reflections.com/blog/2385.htm
    
    ' Before you start, read this:
    ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
    
    Dim URL As String
    
    Dim StartMonth As Integer, _
        EndMonth As Integer, _
        StartDay As Integer, _
        EndDay As Integer, _
        StartYear As Integer, _
        EndYear As Integer, _
        DateInt As Integer
    
    Dim Parts() As String

    ' if date entered is a weekend, find the previous Friday
    DateInt = Weekday(QuoteDate)
    If (DateInt = 1) Then      ' Sunday
        QuoteDate = DateAdd("d", -2, QuoteDate)
    ElseIf (DateInt = 7) Then  ' Saturday
        QuoteDate = DateAdd("d", -1, QuoteDate)
    End If

    ' note that I pick a single date
    StartYear = year(QuoteDate)
    EndYear = StartYear

    StartMonth = month(QuoteDate)
    EndMonth = StartMonth

    StartDay = day(QuoteDate)
    EndDay = StartDay

    ' Yahoo Finance URL
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol & _
           IIf(StartMonth = 0, "&a=0", "&a=" & (StartMonth - 1)) & _
           IIf(StartDay = 0, "&b=1", "&b=" & StartDay) & _
           IIf(StartYear = 0, "&c=" & EndYear, "&c=" & StartYear) & _
           IIf(EndMonth = 0, "", "&d=" & (EndMonth - 1)) & _
           IIf(EndDay = 0, "", "&e=" & EndDay) & _
           IIf(EndYear = 0, "", "&f=" & EndYear) & _
           "&g=d" & _
           "&ignore=.csv"


    ' Send the request URL
    Dim HTTP As New XMLHTTP
    HTTP.Open "GET", URL, False
    HTTP.Send
    
    If HTTP.Status <> "200" Then
        MsgBox "request error: " & HTTP.Status
        Exit Function
    End If
    
    ' split the returned comma-delimited string at the commas
    Parts = Split(HTTP.responseText, ",")
    
    Select Case LCase(QuoteType)
        Case "open"
            GetHistoricalData = Val(Parts(7))
            Exit Function
        Case "high"
            GetHistoricalData = Val(Parts(8))
            Exit Function
        Case "low"
            GetHistoricalData = Val(Parts(9))
            Exit Function
        Case "close"
            GetHistoricalData = Val(Parts(10))
            Exit Function
        Case "volume"
            GetHistoricalData = Val(Parts(11))
            Exit Function
        Case "adjclose", "adj close"
            GetHistoricalData = Val(Parts(12))
            Exit Function
        Case "max"
            GetHistoricalData = Application.Max(Val(Parts(7)), Val(Parts(8)), Val(Parts(9)), Val(Parts(10)), Val(Parts(12)))
            Exit Function
        Case "min"
            GetHistoricalData = Application.Min(Val(Parts(7)), Val(Parts(8)), Val(Parts(9)), Val(Parts(10)), Val(Parts(12)))
            Exit Function
        Case "avg"
            GetHistoricalData = Application.Average(Val(Parts(8)), Val(Parts(9)))
            Exit Function
        Case Else
            MsgBox QuoteType & " invalid QuoteType for GetHistoricalData function"
            Exit Function
    End Select
    
End Function

How to install an Excel macro

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Tools > References > Microsoft XML v6 per the picture below
  5. Switch back to your spreadsheet (x out of the VBA screen if you want)
{Microsoft XML v6 installation}

Thanks to declaring early bound msxml object throws an error in vba for both the clue and the picture

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2398.htm


Graph Real Time Option Prices

When buying or selling options it is helpful to be able to see the information graphically rather than just looking at the tabular option chain tables. (I sell puts instead of placing buy limit orders; and I sell covered calls when I want to sell a position that may continue to appreciate.)

The feature below allows you to see how all the puts and calls are priced for a particular stock; in a glance it is often possible to see if the strategy you have in mind would be worth the trouble.

The data is from Yahoo Finance and the process of repetitively querying their XML streams takes a while, so be patient.

To see the messy, ugly code, click here

http://www.philadelphia-reflections.com/blog/2400.htm


Get Historical Stock Data in PHP (single date; from Yahoo Finance)

This PHP function returns data for a stock on a single specific date, using Yahoo Finance's CSV data.

I use file_get_contents; some servers will force you to use CURL.

<?php
//
// Takes in a symbol and a date;
// returns the Yahoo Finance Historical data for that single date
// If there's no data, the array is empty.
//
/*

$quote = YahooHistoricalStockQuote("VTI", strtotime("2009-09-11"));
print_r($quote);

Array
(
    [Open] => 53.14
    [High] => 53.32
    [Low] => 52.80
    [Close] => 53.08
    [Volume] => 1291300
    [Adj Close] => 49.43
)

*/

function YahooHistoricalStockQuote($Symbol, $QuoteDate)
{
  $month = date("m", $QuoteDate)-1; $day = date("d", $QuoteDate); $year = date("Y", $QuoteDate);

  $url = "http://ichart.finance.yahoo.com/table.csv?s=$Symbol&a=$month&b=$day&c=$year&d=$month&e=$day&f=$year&g=d&ignore=.csv";

  $return_data = @file_get_contents($url);
  $parts       = explode(",", $return_data);

  $data['Open']      = $parts[7];
  $data['High']      = $parts[8];
  $data['Low']       = $parts[9];
  $data['Close']     = $parts[10];
  $data['Volume']    = $parts[11];
  $data['Adj Close'] = $parts[12];

  return $data;
}
?>

My thanks to Encode / Decode HTML Entities

http://www.philadelphia-reflections.com/blog/2401.htm


Plot Investment Results

Track ivestment progress based on Yahoo Finance historical data using either the Closing or Adj Close.

To see the code, click here

http://www.philadelphia-reflections.com/blog/2402.htm


SMTP Authorization and Handling Bounced Emails with PEAR Mail

Recently our ISP started requiring user signon in order to send emails. PHP's mail function stopped working as a result.

Naturally, the ISP did not notify us of this change so we were quite surprised when many thousands of emails on our newsletter list were rejected (every one of them, in fact).

What error message was returned to us to notify us of what the problem was? Why this helpful note:

Mail sent by user nobody being discarded due to sender restrictions in WHM->Tweak Settings

Doesn't that just say it all?

I'm being snide, but our ISP is really quite good about keeping its software up to date and aside from an occasional surprise like this, they are very reliable. Being up to date included the automatic incorporation of the PEAR Mail facility which we are now using.

PEAR's Mail system works quite well but two problems were very vexing until we stumbled our way to a solution:

  1. How, exactly, do we sign on to the SMTP server?
  2. How do we ensure that bounced emails (the bane of all email lists) get returned to us?

You might not think that the first question would be so hard but it actually took a good deal of trial and error to get it right. As for the second question, there is an awful lot of wrong information available out in Internet land (including but not limited to VERP and XVERP which I advise you to avoid).

With PEAR Mail you first set up a "factory" and then send emails, either singly or in a loop. We keep the user id, password, etc. in a file "above" the web server in hopes that will keep them secret ... here's the code (it actually is in production and it does in fact work):

<?php
include('Mail.php');

# the email constants are contained in a file outside the web server
include("/level1/level2/level3/constants.php");

$headers = array (
         'From' => '"name"<addr@domain.com>',
         'Sender' => '"name"<addr@domain.com>',
         'Reply-To' => '"name"<addr@domain.com>',
         'Return-Path' => 'addr@domain.com',
         'Content-type' => 'text/html; charset=iso-8859-1',
         'X-Mailer' => 'PHP/' . phpversion(),
         'Date' => date("D, j M Y H:i:s O",time()),
         'Content-Language' => 'en-us',
         'MIME-Version' => '1.0'
         );

// call the PEAR mail "factory"
$smtp = Mail::factory('smtp',
      array (
            'host' => EMAIL_HOST,
            'port' => EMAIL_PORT,
            'auth' => true,
            'username' => EMAIL_USERNAME,
            'password' => EMAIL_PASSWORD,
            'persist' => true,
            'debug' => false
            ), '-f addr@domain.com'
      );

# to send emails:
#
# $headers['To']      = $to;        # provide the "$to" variable, something like $to = '"name"<addr@domain.com>';
#                                   # note that the first parameter of $smtp->send can be "decorated" this way or just a naked email address
# $headers['Subject'] = $subject;   # provide the "$subject" variable
# $mail = $smtp->send($to, $headers, $contents_of_the_email);
#                          -------- ................................> except for 'To' and 'Subject',
#                                                                     $headers is provided by this module but can be over-ridden
# if (PEAR::isError($mail))
# {
#   echo "<p style='color:red;'>The email failed; debug information follows:<br />";
#   echo $mail->getDebugInfo() . "<br />";
#   echo $mail->getMessage()   . "</p>";
# }
# else
# {
#   echo "<p>email successfully sent</p>";
# }

?>

My thanks to http://htmlentities.net/ for the HTML entites conversion.

http://www.philadelphia-reflections.com/blog/2428.htm



Please Let Us Know What You Think


(HTML tags provide better formatting)

Because of robot spam we ask you to confirm your comment: we will send you an email containing a link to click. We apologize for this inconvenience but this ensures the quality of the comments. (Your email will not be displayed.)
Thank you.