Philadelphia Reflections

The musings of a physician who has served the community for over six decades

Volumes

George IV

The Age of the Philadelphia Computer
Computers have a long slow history. The computer industry, however, had an abrupt start and sudden decline, in Philadelphia.

George (3)

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.


Central Securities

Macroeconomics of The 2007 Collapse

Sudden wealth creation, whether from the discovery of gold or oil, the conversion of poverty into useful cheap labor, or the sudden abundance of cheap credit, is of course a good thing. Sudden wealth creation can be compared with a stone thrown into a pond, causing a splash, and ripples, but leaving a somewhat higher water level after things calm down. The globalization of trade and finance in the past fifty years has caused 150 such disturbances, mostly confined to a primative developing country and its neighbors. Only the 2007 disruption has been large enough to upset the biggest economies. It remains to be seen whether disorder to the whole world will result in revised world monetary arrangement. One hopes so, but national currencies, tightly controlled by local governments, have been successful in the past in confining the damage. This time, the challenge is to breach the dykes somewhat, without letting destructive tidal waves sweep past them. Many will resist this idea, claiming instead it would be better to have higher dykes.

It is the suddenness of new wealth creation in a particular region which upsets existing currency arrangements. Large economies "float" their currencies in response to the fluxes of trade, smaller economies can be permitted to "peg" their currencies to larger ones, with only infrequent readjustments. Even the floating nations "cheat" a little, in response to the political needs of the governing party, or, to stimulate and depress their economies as locally thought best. All politicians in all countries therefore fear a strictly honest floating system, and their negotiations about revising the present system will surely be guilty of finding loopholes for each other; the search for flexible floating will therefore claim to seek an arrangement which is "workable".

In thousands of years of governments, they have invariably sought ways to substitute inflated currency for unpopular taxes. The heart of any international payment system is to find ways to resist local inflation strategems. Aside from using gunboats, only two methods have proven successful. The most time-honored is to link currencies to gold or other precious substances, which has the main handicap of inflexibility in response to economic fluctuations. After breaking the link to gold in 1971, central banks regulated the supply of national currency in response to national inflation, so-called "inflation targeting". It worked far better than many feared, apparently allowing twenty years without a recession. It remains to be investigated whether the substitution of foreign currency defeated the system, and therefore whether the system can be repaired by improving the precision of universal floating, or tightening the obedience to targets, or both. These mildest of measures involve a certain surrender of national sovereignty; stronger methods would require even more draconian external force. The worse it gets, the more likely it could be enforced only by military threat. Even the Roman Empire required gold and precious metals to enforce a world currency. The use of the International Monetary Fund (IMF) implies attempts to dominate the politics of the IMF. So it comes to the same thing: this crisis will have to get a lot worse, maybe with some rioting and revolutions, before we can expect anything more satisfactory than a rickety negotiated international arrangement, riddled with embarassing "earmarks". Economic recovery will be slow and gradual, unless this arrangement is better, or social upheavals worse, than would presently appear likely.

An iPhone web app

The iPhone is the best PDA to come along since the Blackberry 15 years ago. It is to the Blackberry what the Blackberry was to cell phones.

Philadelphia Reflections is now a fully-fledged iPhone web app. The application will appear on your iPhone in the appropriate format automatically: just navigate to http://www.philadelphia-reflections.com with the iPhone browser; we will detect it and do the right thing.

A two-step process is required to get a little icon on your iPhone home page so you can go there directly:

  1. Click the "+" plus sign at the bottom of the iPhone screen
  2. Click the "Add to Home Screen" button that appears.

That's it. We do all the rest.

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

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

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

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

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}

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

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

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

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

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

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

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

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

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.

PRO DATA AND THE QUALITY OF MEDICAL CARE

PRO DATA AND THE QUALITY OF MEDICAL CARE

Address to the Symposium on Computer Applications in Medical Care Washington DC, November 5, 1984

George Ross Fisher, M.D.

Almost everyone in this audience knows the PROs replaced the PSROs less than a month ago. If not, absolutely everyone does know that a prospective-pricing DRG system for paying hospitals replaced the old cost-plus system, up to twelve months ago. Prospective payments of hospitals therefore appear to be older than the physician review mechanism which provides oversight; but nothing could be more misleading than this time sequence. The payment system and its oversight system were created by enactment of separate statutes. It is not possible to understand where the Medicare program stands without knowing that the PRO law was drawn up before the DRG (Diagnosis-related Group) law was passed, and reflects a context of uncertainly whether the DRG would be passed.

In a moment, I will return to the important consequence of this anomaly for the PRO data system. However, first it seems useful to offer brief historical review of hospital reimbursement by Medicare. In 1965 the driving force behind the creation of Medicare had been to improve access to care by removing the financial barriers to access. The Blue Cross system of cost reimbursement was ideally suited for that goal, since it allowed hospitals to spend whatever they thought they needed to spend, with a year-end retrospective audit and settle-up. The recklessness of this approach was quickly appreciated as hospital costs to the Medicare program rapidly climbed above expectations. By 1972 the matter was of such concern that congress created the Professional Standards Review Organizations (PSRO) to identify and restrain what was felt to be the main cause of the cost escalation, namely unnecessary or excessive use of the hospital by doctors. From the doctors’ viewpoint, that did have an uncomfortably reasonable sound to it. We were reminded, and we agreed, that only doctors admit patients, only doctors write orders, and only doctors send patients home. After ten years more, however, it was evident that hospital costs had continued to soar in spite of the PSROs. Whether the PSROs had done a good job or not, was immaterial; it was clear that what they had done was not enough. PSROs protested that the blank-check reimbursement system made utilization review of the volume of hospital services quite futile. If doctors should cut the number of blood counts in half, all that was likely to result was that the price of blood counts would double.

By 1982, there were two main options for the government. Either make the PSROs program a whole lot tougher, or else change the reimbursement incentives. As it turned out, government adopted both options. The DRG prospective payment system was enacted; and a set of incredibly bloody-minded policies were prepared for the PRO, including quotas for overall cost savings, quotas for reduced admissions, and a mind-boggling intensification of the surveillance of hospital records. As one government official has written, “The medical record has now become the hospital bill.” It is not possible for an outsider to know how much, or by whom, these regulations and policies were changed during the long period they were held up by internal wranglers within the administration. What is clear is that when they did emerge, the first few months of the prospective payment system had already demonstrated that such attitude were 180 degrees away from what was really needed. Under the old system of reimbursement, the PSROs urged the hospitals to reduce unnecessary services, whereupon the hospitals protested they meant to preserve the quality of care. Under prospective payments, however, it is the hospital which is cutting costs, and the PRO is the one which needs to protect the public from consequent deterioration in quality. This role-reversal has been very sudden, since the impact of DRGs on length of stay has been far greater than generally anticipated. Many hospitals are already reducing staff in order to down-shift to an era of lower occupancy.

This Gilbert-and-Sullivan comedy came to a head and one hopes, came to an end on August 2, 1984 at a Senate Finance Committee hearing when Senator Durenberger (the chairman of the Health Subcommittee) confronted the top administration of HCFA and proclaimed for the public record in no uncertain words that the intent of Congress was that the purpose of the PRO was not to save money. Not to save money. The DRG prospective pricing system was what was to save the money; the PRO was to safeguard the quality of care from the inevitable pressures of reducing expenditures.

There is therefore reason to hope we will soon see major shifts in emphasis in the PRO program, one hopes even including total renegotiation of all the contracts. However, the data system will not be so easy to change, and if that is not changed the people in OMB may yet win the battle. Any program which deals with the analysis of data becomes dominated by the data it receives far more than it is dominated by the intention of its leadership. Present policy mandates the PRO receive its data from the fiscal intermediary, who derives it from the hospital’s reimbursement request. While there may be minor cost savings in such a third-hand data system, it is totally inadequate for the purpose of assessing the quality of care. We have several intermediaries in Pennsylvania and one of them has yet to give us a scrap of data for months Pennsylvania has been on prospective payments. The other intermediaries have indeed given us data, but I would not always the describe their performance as speedy. The reason for delay is not always the fault of the intermediary. In reviewing cost outliers, we have encountered at least one 300-bed hospital which declares it is totally unable to produce an itemized bill. The other hospital seems to produce bills, but the inaccuracy of what we have checked is very ominous.

Now, assume that threats, penalties and fulminations can coerce the data to be accurate and timely. I would not count on it, but it might happen after a year; the previous speaker, dr. Ertel, can tell you better than I can. But the content of the data found on the reimbursement invoice is completely barren of information useful for assessing the quality of care, as indeed it should be, if parsimonious design has limited the data elements to those necessary for the single purpose of issuing and auditing reimbursement. As for the chances of enhancing the data set for additional purposes, the system of passing the data through the intermediary creates a permanent adversary system. Naturally, intermediaries can be expected to rebel at incurring key-entry and data processing costs for purposes which are irrelevant to the reimbursement function, for which they are paid, and on which they are judged. The intermediary can scarcely be expected to become an enthusiastic agent in imposing new data collection requirements on hospitals, while hospitals will predictably resist proposals that they supply data to prove they have been skimping on their services. If past experience is any guide, every proposed addition to the data set will be denounced as costly, unnecessary and unconstitutional, and these denunciations will emanate from the parties who are expected to report it accurately and quickly.

It seems clear to me that HCFA urgently needs to convene some planning task forces. It needs to create a permanent public negotiating arena for the various data combatants; for this, the Prospective Payment Commission would be a good model to copy. HCFA needs to commission some public-domain software. It also needs to stimulate a very large number of demonstrations projects, because the PROs are embarked on a totally new venture, with no existing models to copy.

If you please, I would like to make a proposal right here. We have heard a great deal about provoking or preventing unnecessary deaths, as an index of the quality of medical care. Everyone eventually dies however, so it is more precise to say: medical intervention shortens or prolongs the interval between the intervention and the subsequent date of death. In a statistical sense, we improve on, or fall short of, the individual’s adjusted life expectancy. The advent of disease adjusts the life expectancy, while the therapy or therapist modifies the adjustments. Over on Security Boulevard in Baltimore, a vast computer empire regularly keeps track of the date of death of every Medicare recipient. The data is supplied by undertakers who not file a death certificate without centrally reporting the social security number; the purpose is to stop sending out those green checks aa soon as possible after death.

What that system means to me is that HCFA already has in existence two data systems which, if linked together, would permit calculation of actual subsequent life spans of every Medicare patient, or specified groups of Medicare patients, regardless who treated them, how, or for what. It boggles my mind to consider the potential for assessing the average impact on adjusted life expectancy by one hospital or one therapy. And in the aggregate, if the overall “effects on adjusted life expectancy” should begin to deviate from historical values, there would be cause to investigate whether changes in the financial environment of medical care had been the underlying cause.

Let me close by describing our situation as an exercise in macro-economics. Economics have wondered whether 11.5% of the Gross National Products is too much to spend on health. Others say we can and should afford even more than that. Let me tell you how every government always answers such questions of resource allocation. The method is simple. You cut the 11.5% down to 10.5%, and watch to see if anything bad happens. If not, you cut it some more. Eventually, when something bad does happen, you restore a little money and declare the issue is closed. If you agree with me that the DRG is just such a methodology, then you do not say the role of the PRO is to preserve the quality of care. Rather, you say the role of the PRO is to detect the first signs of inevitable deterioration in quality at the very earliest possible moment. If our data system is poor, it will take longer to detect problems, and it will be harder to convince people you have detected them. With a poor data system, the budget cuts will go deeper than they ought to go, and care will get worse than it has to get. And that will be a shame.

Health Insurance National, and Otherwise

Health Insurance National, and Otherwise

George Ross Fisher, M.D.

A Message to Big Business

Recently the National Chamber of Commerce studied the question of cost containment in the health field, and urged local cambers to organize data reporting systems for employee health and hospital costs. It is not clear what employers could do with such information once they had it, since their employees (or unions) are likely to be resentful of intrusion into personal privacy. If the data should by chance demonstrate that one doctor, hospital or HMO was cheaper than another, the more expensive providers of care would surely claim that quality was related to cost. In any event, the American tradition is for the patient, not his employer, to select his doctor.

A far more productive data analysis for employers would be one which helped him select the best insurance company, or the best health insurance benefit package, for the employee group. While it is true that unions have exerted considerable influence on benefit packages or even carrier selection, the unions and the employers unite in a desire to get the most benefits for the least health insurance cost. It therefore seems likely that more action would result from examination of the financial data than the medical data, although in both cases it is necessary to be a diligent pupil before the data is intelligible. We here propose that it is worth-while to understand the ratio of hospital costs to hospital charges. Having understood the matter, the Chamber is urged to apply it to local hospitals and individual employee groups.

The examination of internal hospital subsidies is greatly assisted by existence of an unwieldy document, the SSA-2552. The Medicare agency requires every hospital to complete a 25-page annual financial summary, complete with folded-over pages, and filled with numbers. This document is prepared for a public purpose, and under the Freedom of Information Act, is available to all who wish to examine it. For the purpose at hand, it is possible to ignore all of this document except for column 2 on page 18. On page 18 is found “Worksheet C.” The Departmental Cost Distribution. In column 1 will be found; the total costs generated by each department during the year (A.), together with the total charges generated by that department (B.) In column 2, the place where present where present attention is focused, each department displays the ratio of A divided by B, the ratio of Cost to Charges. A quick glance will identify that the ratio is usually less than 1.0, in keeping with the practice of charging more than your costs in order to make a “profit”. A glance down the line will quickly show even a casual reader that there is a very considerable variation in the ratios from one department to another, and that there are definitely department with a ratio greater than 1.0, which means that these departments are being subsidized.

The Medicare cost report, available from every hospital, displays the ratio of costs of charges for each revenue-production department of the hospital. The concept of the ratio is simple enough. In a free enterprise system everyone is accustomed to the idea that the price of things is always a little higher than the cost. The difference is called a profit margin, or mark-up. We are even familiar with the occasional situation where the selling price (charge) is less than the cost; that is called a loss-leader.

Therefore, loss-leaders excluded, we would except the normal ratio of costs to charges to be approximately.90, allowing about a 10% profit for bad debts, charity, etc.

Furthermore, we would expect the individual department of the hospital to display a cost-to-charge ratio which is relatively uniform, and fairly close to overall total for the hospital.

Notice that the important issue is not how close the ratio is to unity (1.0) but rather how close it is to the overall hospital total ratio. That is, how uniform the ratios are between departments.

A great many people assume that, if the cost-charge ratio is less than 1.0 and a profit is therefore generated, any insurance company which pays charges must have higher premiums than an insurance company which pays “costs”. Such an inference is not necessarily correct as a theory, and is quite clearly incorrect in certain circumstances. The premium reflects all of the expenses of the insurance company, not just the hospital payments. Subsidy of non-group individual subscribers by group subscribers is a major example of the equalizers affecting health insurance premiums.

The following figures for cost-to-charge ratios were taken from an actual hospital’s Medicare cost report. They fairly represent the national pattern, although there is a great deal of individual variation between hospitals. The important things to notice are the non-uniformity of departments, and the separation of hospital departments into two distinct classes:

Table 1. Ratio of Costs to Hospital Charges by Department

Undercharged (Ratio) Overcharged (Ratio)

Operating Room 1.02 X-Ray .74

Short Procedure 1.20 Isotopes .68

Labor & Delivery 1.32 Laboratory .69

Anesthesia 1.19 Oxygen .59

Physical Therapy 1.38 EKG .22

Daily Room Charge 1.22 EEG .54

Intensive Care 1.25 Medical Supplies .46

Drugs .58

Finally, one dare not assume that the cost-to-charge ratio for a department is reflected in every service performed by that department. The ratio comes about by the cost accountant assigning indirect costs to those departments which have the best cost reimbursement experience. At the same time, charges are raised on those items most likely to be paid for in cash, within the perceived limits of ability to pay. Charges tend to be closely examined on common items like blood counts and chest x-rays, while uncommon test and services tend to be too much trouble to examine frequently in close detail. Therefore, there are often bargains in rarely-used services whose charges have not been raised in some time. Finally, there are items which can be charged off as bad debts if unpaid by a Medicare patient. Under this heading are personal items like television sets, or uncollected 20% coinsurance on ambulatory services; for setting h charges on these items, maximum brazenness is rewarded.

How to Play the Game

The free-market, or Adam Smith, philosophy is presumably highly regarded by the Chamber of Commerce. The theory supposes that every rational person will press his own interest and advantage to the point where he comes into equilibrium with the rest of the community, who are acting on their own separate behalf. It must be clear that the hospital financial and reimbursement system strongly endorses the “every man for himself” philosophy. What follows are a few suggestions for over achievers in the business world who would like to play the hospital game with a little more success than they have demonstrated in the past. Perhaps if they do, the community at large will benefit as a new equilibrium is set.

Notice that a cost/charge ratio greater than unity (1.0) means a loss leader. If your insurance company pays charges, it is paying less than another company which is paying costs. Never mind that the “costs” are inflated with doubtful indirect costs; that’s what the cost-reimbursing insurance company pays.

Notice that the benefit package of a charge-reimbursing insurance company should heavily include the use of those hospital department which are loss-leaders. Those department which are highly profitable for the hospital however should be avoided, since the presence of insurance just raises the prices still more. Since these services are mainly out-patient (ambulatory) services, tell your employees to go for them to their doctor’s offices. If you must cover them with insurance, specify that the insurance is not valid for use in a hospital. (Don’t worry about anti-trust: plenty of policies are only good in a hospital out-patient department.) If you feel you must cover them, put them in the major-medical policy.

Notice that the cost-reimbursing insurance companies have an exactly opposite set of motivations. They need to include a large number of ambulatory benefits, since they get a bargain on such services. However, if they are restrained from this endeavor, they will be forced to resist the cost escalation of inpatient-intensive services, which means they resist the current escalation of indirect costs. Since the root cause of hospital inflation is the rampant growth of unrestrained indirect costs, it is possible that restricting Blue Cross to inpatient reimbursement would stop the spiral. It is in the competitive interest of a charge reimbursing carrier to avoid extending out-patient benefits. Blue Cross, by contrast, would have to be forcibly restrained.

If an employer intends to be serious about playing the hospital game, he needs to know what kind of services his own employees are using. He also needs to know what the particular cost/charge quirks are at the local hospitals where most of his employees find themselves from time to time. It is easy to imagine one employer with 30% of his employees' women under the age of thirty, while another employer mostly might have nothing but middle-aged male employees. A new business will have young active employees, an older business may have pensioners to consider. Climate makes a difference, and occupational hazards must be considered. So, what’s good for one employer isn’t necessarily good for others, or necessarily good after the business grows for ten years. And the hospital cost accountant, by the way, isn’t going to be asleep as things change over time.

It would require a rather sophisticated data system for an employer group (or even an insurance company) to analyze its experience in terms of hospital departmental usage. So, a simpler conceptual approach is suggested. The departments with a high cost/charge ratio tend to be used by surgeons and surgical specialties. Conversely, the non-surgical physicians' internists, pediatricians, psychiatrists, family practitioners) tend to use most heavily the hospital departments which have low cost/ charge ratio. There is no conspiracy at work; it just happens to work out that way as a result of independent stresses which have been discussed elsewhere.

So, it would appear that subsidizing is taking place by the patients of non-surgeons for the benefits of patients who have surgery. Somewhat true, although the situation is more complicated.

Both Blue Cross and the commercial carriers employ an analytic system for large employee groups, known as experience-rating on the basis of charges incurred, (even though the plan pays costs, not charges). The commercial carriers experience-rate on the basis of charges, too, but they actually pay the charges. So, an experience-rated group gains nothing by switching carriers so long as the experience-rating continues to be based on hospital charges. The premium they pay will reflect a subsidy of surgical patients by non-surgical ones.

But there is another class of patients for whom the reverse is true. The non-group individual subscribers to Blue Cross have a diversion of premium money toward surgery, while the whole non-group program is receiving a subsidy from the group subscribers. It is difficult to tell whether the continued effect is positive or negative for the surgical patients. But the non-surgical, non-group subscribers are certainly getting a bargain. Until someone figures out a way to force subscribers to belong to a group, a company should think twice about forming one. Decreased benefit package? Buy an excess major medical policy and forget it.

Of all the subsidies which characterize this giant medical financial equilibrium, the greatest is on the basis of the age of the subscriber. It scarcely needs proof to recognize that young subscribers do not have the same health costs as older ones, but they do pay the same premium. All health insurance plans would do well to devise a system of vesting before competition exploits this inherent weakness and topples the structure. A movement by groups into non-group would eventually reach an equilibrium, but a selective movement of young subscribers to competitors or self-insurance would start a spiral which could be very drastic, indeed.

Finally, there is one other recourse which subscribers could take to the situation wherein non-surgical hospital patients subsidize surgical ones, while experience-rating prevents them from doing much about it. The recourse would be to seek care outside of a hospital. Nowadays there is not much difference between a first-class nursing home and a hospital, except that you can’t do much surgery there. Next time you hear someone talking about “excess hospital beds”, take a hard look at who is talking.

Computers and the Regulation of Medicine.

Computers and the Regulation of Medicine.

George Ross Fisher, M.D.

I am going to take chance in this essay that I can hold he attention of the reader through a preamble of theory, before addressing the consequences for the practice of medicine. That seems necessary, because I believe that the consequences are different from what most readers would intuitively expect and persuasion lies in first convincing the reader of the theory.

CLOSED AND OPEN SYSTEMS

There is a growing body of endeavor known as the Theory of System, which acknowledge that all events are consequences of pre-existing conditions (like the consequences of adding acid to bicarbonate in a beaker), and are thus “closed” systems. However, most events in biology and sociology are so complex that it is only possible to deal with them as “open” system, for which we substitute wisdom for scientific certainty. “Wisdom” is a set of traditions, maxims, opinions and strategies which allow you to make predictions about the inevitable outcome of events within an open system. The teleological nature of human events was once referred to as Manifest Destiny, and realists like Talleyrand spoke of diplomacy as the art of manipulating the inevitable.

Example:

Wisdom has it that in your choice of a practice location, you should remember that “you can’t make money where it ain’t.”

And now a conclusion about the computer revolution: Since computers increase the capacity to store and manipulate detail, the computer revolution increases the number of closed systems, and shifts the scope of wisdom in decision-making from traditional areas to new subject which were formerly incomprehensible.

HIERARCHIES

In dealing with open systems, managers and executives have evolved a basic strategy; they organize manageable subunits into hierarchies. Units are organized within departments, then organized within divisions, reporting to a policy-making body. Further, because the purpose of the organizational structure is to simplify management, each level of the hierarchy is oblivious to the techniques of the level below, and is only interested in the output of the level below.

Example:

The patient paying his bill is interested in the total amount that he has to write on the “bottom line,” which in his case is the dollar amount of the check he must write

The director of the x-ray department is concerned with a subtotal related to the x-ray department. The chief technician is concerned with individual studies. The dark-room attendant is only interested in pieces of film.

COMPUTER CONCLUSION: Primitive Computer Systems merely duplicate the pre-existing manual system. Their real power lies in the next step, which is to reorganize the reporting system. That is, they cause a reorganization of the hierarchy.

TRANSMISSION

The prediction is made that management system will be forced in the direction of a hierarchy of three: Those who are able to make decisions, those who cannot make decisions but are necessary for some task, and the computer. One function often seen in non-computer systems is simply to pass the information unchanged up the line. There is little doubt this activity will vanish.

Example:

Robert McNamara (from Princeton via Ford Motors) was a computer expert who became Secretary of Defense under John Kennedy. By installing computers, McNamara was able to jump the Army reporting system (Sergeant to Captain to Secretary of Defense) and confront the generals with discoveries before the generals had received the news. We are told that the generals didn’t like it a bit. But can anyone doubt that Robert McNamara carefully filtered the data before h presented it to President Kennedy? The system of hierarchical reporting condenses the data to the next step up.

COMPUTER CONCLUSION: Many systems of management by delegation will soon be swept away by the computer revolution, and middle management will be the most threatened region. It will resist but it will lose.

MODIFICATION

Another function of delegated systems is to take raw information and reduce it to condensed form for the benefit of the next level upward. They do so by a process which is often a mystery to the next higher level, and hence a certain power is conferred on the lower subunit to modify the conclusion by modifying the system of manipulation. The method for controlling such activity is to produce a procedure manual which the next higher level must approve, but the inherent complexity which forced a delegated process to be created also obscures the power of the delegated subunit to modify the system.

COMPUTER CONCLUSION: Computer technology strictly defines and inflexibly follows defined procedures for steps in hierarchy. It thereby confers much stricter control power to the higher levels of hierarchy.

THE NEED TO KNOW

If for no better reason than to reduce programming costs, the computer process confers a new power to the lower levels of hierarchy. The higher level must now strictly define its reasons for asking for certain information. If it cannot demonstrate a need to know, it cannot justify the cost of knowing.

Example:

The PSRO, acting on behalf of the physician community, violently resists the inclusion of data elements in the report tape sent to the Bureau of Quality Assurance. At the same time, it is anxious to acquire as much data as possible from units lower in the hierarchy, who in turn resist the process. It can be expected that this process will eventually settle out at roughly the best equilibrium for the community at large, although differing aggressiveness among the participants may cause temporary inequities. The weapons in the battle, which are at the disposal of the physicians are:

(1) Superior Claims on the decision-making process.

(2) The faith of the public in physicians as the most trustworthy custodians of their health privacy.

(3) A superior pool of talent, determination, and independent means committed to a vital issue.

COMPUTER CONCLUSION: If you have a good chance of being the winner in the reorganization of a hierarchy, it is better to participate to your utmost rather than hold back out of fear that someone else will be the winner, because only participants are winner.

NETWORK

We have spoken thus far of hierarchy as the only manageable approach to the complexity of open systems. A more general description would be “modularity” since modules can interact in a lateral direction as well as vertically. When they do, the result is a network of modules in three dimensions. Since computers increase the ability to cope with complexity, they increase the ability to work in three dimensions. Hierarchy is the last resort of manual management; just as three-dimensional chess is beyond the ability of people who are not even very expert at two-dimensional chess. In this sense, the computer revolution provides some hope for the American System, which resents hierarchy and naturally prefers networks when feasible. This is to some extent a philosophical preference, and does not seem to be true of the Japanese social system, or the German mentality, or the Communist method. The natural American instinct for lateral equality is thus an ally in Medicine’s conflict with Government, but a hindrance when it encourages Nurse independence or unrealistic consumerism.

Whether lateral or vertical, the interaction of modules in a complex open system is the same: delegation of method, output from one module as the sole input to other modules, and resistance to the need to know.

COMPUTER CONCLUSION: The organization of modules into vertical hierarchies or horizontal networks is largely a political process, with three dimensional networks as the last resort of compromise, and with strict vertical hierarchies as the last resort of inadequacy.

CONFIDENTIALITY

Complexity is itself a major defense of confidentiality; since computers reduce complexity, they also destroy the smoke screen. Computer System which stumble ahead or are manipulated into breaches of confidentiality are certain to raise a great uproar about the need to know and the right to conceal. In the PSRO system, the issues balance between the duty of accountability and the patient’s right to privacy. When reduced to these terms the physician community has a clear advantage in the mind of the public, if the advantage can be effectively exploited. The matter can however be overturned by speedy pre-emption of the turf. it can be predicted that special pleaders will insist on accountability when all they really want is power and satisfaction of envy; it can fairly be predicted that some will weaken their claim to privacy by over extending its bounds.

Example: The system of peer review on Medicaid prescriptions in Pennsylvania has turned up a number of instances of patients who obtained multiple prescriptions for “controlled” drugs from multiple doctors, filled at multiple drug stores, probably for resale on the streets. When the doctors and pharmacists were notified, they were universally grateful and took steps to curtail the problem. However, the computer vendor learned of the problem (regardless of the fact that all reports are shredded after review) and persuaded the state government to institute a system of restricting problem patients to a single physician. It may now be impossible to dislodge this meat-ax reaction, in spite of the fact that the computer peer-review system is probably able to cope with the problem without invoking hierarchical power.

A Second Example: The United States Navy recently developed a system of computer protection so elaborate that they boasted of it in the newspapers. Two computer scientists read of it, and in a month’s, time had broken into the system via telephone. The Navy was then agitated to read of its disarmament in other newspaper articles.

COMPUTER CONCLUSION: There is no present foreseeable technical method of protecting the confidential of computerized data, except by physical ownership and physical protection of the machine itself and all of its activity.

CONCLUSIONS

The most significant event in the Twentieth Century is the Computer Revolution, just as the Industrial Revolution was the major event of the Nineteenth Century. By the greatest good luck for medicine, the computer revolution is capable of solving the four major problems which now threaten the American Medical System.

1. THE FAILURE OF THE PRE-PAYMENT INSURANCE MECHANISM. The removal of cost restraints on the patient (and thus the provider) has had a predictable upward effect on costs. The overwhelmed system has reacted in a typical hierarchical manner: try to convert insurance companies into regulatory bodies, and if that fails, into rationing systems. The computer revolution (if we are agile) has the potential of drastically reducing the information costs which are now 40% of hospital and insurance company costs. It also has the ability to control utilization abuses, and expose power abuse to public decision.

2. THE VAST INCREASE IN PARAMEDICAL PERSONNEL. Middle management is most vulnerable to computer replacement, and middle management now costs 20% of the hospital dollar. Physicians in complex medical centers are most alarmed about this problem, which they can easily identify by comparing the hospital parking problem with what it was, twenty years ago. Surgeons are typically least concerned, since their role at the center of procedures is least threatened by aspirants. But surgeons are hearing of “unnecessary” surgery, and even the small-town solo practitioner has to hire girls to fill out forms. The complexity of our system must be reduced, and computers can do it. The best way to thwart the claims of aspirants to power is to eliminate jobs.

3. THE EXPLOSION OF SCIENTIFIC INFORMATION. No one would wish to reduce the output of the research community, but ways must be found to organize and transmit the information without resort to fragmentation by sub-sub specialists. The computer is ideally suited to the problem. THE MALPRACTICE CRISIS. Physicians are uncomfortable with the idea that peer review may soon become entangled in the malpractice system, as indeed it inevitably will. The matter comes down to biting the bullet, armed with statistic. Surely consent for an arteriogram is more threatening if it is couched as “you might lose your leg” than if you are told “you have one chance in five thousand” of such an occurrence. Realistic insurance premiums can be set when the risks are defined. Juries can be provided with realistic statistics on normal risks and normal expectation of benefits.

Through all of these four problems runs a common theme: The cost of medical care. The PSRO seems to be the last best hope of curtailing the cost threat to medicine, and so the PSRO can be expected to be the vehicle for the computer revolution’s resolution of the issue. Senator Bennett probably had no idea of what he was doing; but he did it, and the problem is now our problem.

My Years at Stockley

For forty-six years, I drove three hundred round- trip miles from Philadelphia to Stockley, Delaware -- once a month on Saturdays. That takes a whole day, so it kind of means I spent a year at sixty miles an hour, going and coming. In Delaware, they speak of going “South of the Canal”, to indicate the little state of Delaware is actually two states, or at least two cultures. North of the Delaware-Chesapeake ship canal is the posh little city of Wilmington, where most of the major New York banks are moving to enjoy the special banking laws, and where the Dupont family held majestic court over its Ivy League Camelot. Wilmington has more lawyers than anywhere, or at least more white shoe patrician lawyers than anywhere. Little Delaware generated special laws for the benefit of corporations, so a whole hive of corporation lawyers generated an industry of pretending that General Motors and IBM are headquartered there. Those lawyers were once so remote from the graduates of second-rate (i.e. state rather than national) law schools making a living as plaintiff lawyers, that even the doctors in Wilmington were on cordial terms with the Wilmington lawyers.

South of the Canal was something else. I saw burning crosses on several occasions, and my trip took me past two race tracks for horses and two for beat-up jalopies that smash into each other for the fun of it. To be fair about it, I was shot at twice, once below the canal, and once in Wilmington, that's another story. The incident below the canal was not terribly spectacular; I just heard a loud noise as I drove past Elks lodge, or maybe a Moose lodge, and there was a nice round hole in my fender when I got out of the car. I suppose someone in the lodge was just careless with his gun, but it is not impossible that I had crowded a pick-up truck which retaliated with fair warning.

I met a nice lady from Rehoboth who tells me she remembers when the highway was built; before 1930 or so, there was no road connecting lower Delaware with the outside world. The native people speak with an accent which isn't quite Southern and which is said to be very close to true Elizabethan English. The area was settled by Swedes before the English came, so the people are quite handsome in a sort of Daisy Mae, L’il Abner way. The highway has an interesting history. Coleman DuPont purchased the land and built the highway at his own expense. If you know anything about rural legislatures, you can guess what happened next. He offered the highway to the State and the legislature refused to accept the maintenance costs. When he then hired his own police force to patrol the highway, the legislature reconsidered and accepted his offer to give them the highway.

My trips to this area have their destination at the Hospital for the Mentally Retarded in Stockley, Delaware. In spite of the way it is spelled, it is pronounced "Stokely". A state cop once forgave my speeding violation when I told him I had been at "Stokely". He said that in spite of my out-of-state license plates, I must be telling the truth if I knew how to pronounce it. The hospital has always kept a sign-in log in the administration building, and it is fun to see my signatures going back to 1958, month after month. I've had a couple of close calls or near-accidents on the highway which I haven't told my wife about, and on two occasions the ice or fog was so bad I had to turn around and come home without completing the trip. The trip ordinarily isn't so bad. The car is on cruise control, there are medical education tapes to play (Audio Digest, courtesy of the California Medical Association), and a sort of hypnosis makes you forget where you are going until you get there.

The medical director is a nice young fellow who has a practice in a nearby (25 miles) town and stops by for a few hours a day. Except for him, just about every resident doctor in thirty years has been foreign born, and I would judge, very poorly paid. So, several years before I came to Stockley, someone had the idea of bringing in consultants from Wilmington, Philadelphia, and Baltimore. In the early days that was reasonably easy to do, because the hospital was filled with six hundred perfectly fascinating cases. I've seen several albinos and one thirty-year-old who was no bigger than an infant in arms. They used to have a number of cases of grotesque hydrocephalus, where the poor child grew a head larger than you could put your arms around and which would develop huge bedsores because the child couldn't move his head, let alone lift it. Because the Delmarva Peninsula has been a closed society for over three hundred years, there are lots of cases of rare inherited diseases. I have seen many cases of disorders that other doctors have only maybe read about, and I must admit I loved the experience.

But you know after you spend as much time with them as I have, they stop being interesting cases and become individuals, with names and personalities. Since the aging process is accelerated in several common diseases like Mongolism, I have known some of the patients as little children, then as adults, and finally as dying withered victims of senility. Many times, I have watched the central agony of mental retardation; the children inevitably outlive their parents and ultimately have no one to love them except the institution.

In that role, Stockley does pretty well, although perhaps not as well as it used to do. The switch seems to have happened with the John Kennedy administration, when money for the retarded became abundant. That landmark was especially memorable on the Saturday when the Russians menaced us over Cuba. I never knew we had so many eight-engined bombers as circled over the Dover Air Force Base that day. Years later, a pilot brought his son to see me, and I asked him. "Yup," he said. "we were carrying eggs, all right." "Picked them up in Alaska."

 

Please Let Us Know What You Think

 
 

(HTML tags provide better formatting)
 

Blogs

Central Securities
An analysis of Central Securities (CET).

Macroeconomics of The 2007 Collapse
What happened to America in 2007 has happened to hundreds of developing economies in the past fifty years.

An iPhone web app
Philadelphia Reflections is now available on the iPhone as a web app

Exchange Quotes from XML using PHP (Google Finance)
Google provides an XML feed with delayed exchange quotes. This is a PHP function to access the data.

Exchange Quotes from XML using PHP (Yahoo)
Yahoo provides a lot of financial information. Here is how to get it via XML using PHP

Treasury Yields from XML using PHP
Need current treasury yields? The Treasury provides an XML feed and PHP can read it.

Exchange Quotes from XML using Excel (Google Finance)
Excel VBA function that returns stock information from Google Finance's XML stream

Financial Graphing using PHP
PHP is an outstanding way to build dynamic websites. Good graphing capabilities are a felt need. Finance is an important application. Look no further.

Treasury Yields from XML using Excel
An Excel VBA function that returns the most-recent yield-curve rate for a given maturity for Treasury's constant-maturity bonds

Exchange Quotes from XML using Excel (Yahoo Finance)
Excel VBA function to get stock information from Yahoo Finance

Black-Scholes-Merton in Excel
Option pricing in Excel

Black-Scholes-Merton in Python
Black-Scholes-Merton and related functions in Python

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

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.

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

Plot Investment Results
Track investment progress based on Yahoo Finance historical data using either the Closing or Adj Close prices.

SMTP Authorization and Handling Bounced Emails with PEAR Mail
Sign on to an SMTP server and get bounced emails returned to you

PRO DATA AND THE QUALITY OF MEDICAL CARE
New blog 2018-08-15 20:15:47 description

Health Insurance National, and Otherwise
New blog 2018-08-21 17:55:37 description

Computers and the Regulation of Medicine.
New blog 2018-08-22 20:03:03 description

My Years at Stockley
New blog 2018-08-22 22:39:22 description