Philadelphia Reflections

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

Related Topics

No topics are associated with this blog

Treasury Yields from XML using Excel Topics: 230,

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:
    ' The Maturity input argument is any one of the elements
    ' following the colon in the <d: ... nodes are 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 success 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 point 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 = ""
    url_part1 = "$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
    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 success 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 a 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
' --------------
            Exit Function
            MsgBox "Error " & Err.Number & ": " & Err.Description
            Resume ExitHere
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

Originally published: Tuesday, December 04, 2012; most-recently modified: Thursday, September 23, 2021


Please Let Us Know What You Think


(HTML tags provide better formatting)