Importing yields from treasury.gov into Google spreadsheet
Importing yields from treasury.gov into Google spreadsheet
During the last few days I've spent some time learning how to import yield-curve yields from treasury.gov into a Google sheet using the ImportXml function. I'm sharing some things I've learned here, but also asking for help if with filling in the gaps in my knowledge.
---------- Locations of the XML files with treasury curve yields -----------
Yields for the current month are in an xml file at this URL: http://www.treasury.gov/resource-center ... /yield.xml.
Through some Googling and trial and error, I found the xml files for years 1991 through 2010. They are at the same URL as above, but with the file name format: yield_historical_YYYY.xml, for example:
http://www.treasury.gov/resource-center ... l_2010.xml
I've pulled data from the files for 2004-2010, verified that there is a file as old as 1991, and checked a couple of random years beween 1991 and 2004, and files existed, so I assume there are files for 1991-2010 using this filename convention at this location.
I cannot find the xml files for years 2011 through 2014. Does anyone know where they are???
EDIT: I found monthly files for 2011 using this filename convention: yield_mmmdd; e.g., for January 2011:
http://www.treasury.gov/resource-center ... _jan11.xml
The structure of the xml files with historical data is somewhat different than that for current month yields, and there are some differences between the historical files for 2004 and earlier and those for 2005 and later.
------------- Using ImportXml to retrieve yields for current month -----------
I use Google Sheets (aka Google Docs spreadsheets) pretty much exclusively, and Google provides an ImportXml function.
The syntax of ImportXML is: IMPORTXML(url, xpath_query). If you look at the xml files and know a little bit about xpath (or read a bit about it if you don't), the queries below shouldn't be hard to understand.
The following xpath_query returns all dates for which yields may be reported (i.e., weekdays) for the current month:
//BID_CURVE_DATE
So assuming the URL for the current month yields (first URL above) is in cell A1 of the spreadsheet, and the xpath_query is in cell A2, the ImportXml to retrieve the dates is:
ImportXml(A1,A2)
The dates are retrieved in one column (one row for each date).
The xpath_query to retrieve the yields for all maturities provided (i.e., 1-month through 30-years) for all dates for the current month is:
//G_BC_CAT
Each yield is returned in a separate column, with one row for each date. There is an extra element with the 30-year rate duplicated, so there will be two columns at the right with the 30-year yield. For a weekday that is a Federal holiday, all yield elements are empty (blank cell values) and the text FEDERAL HOLIDAY is returned in the last element (in an extra column to the right of the blank column that would normally have the second 30-year yield).
So you can use one ImportXml to retrieve the dates in one column, and then in the next column to the right use another ImportXml to retrieve the yields, so you have dates matched up with corresponding yields.
I came up with a single query that pulls the dates and yields, but it puts the date in one row and the corresponding yields in the next, so that's no good. As I recall, I used the union XML operator to combine the date and yield xpath queries above.
You can use some spreadsheet magic to grab the yields for just the most recent date (one row), but you also can do that with this xpath_query:
EDIT: Here's a simpler query that works:
(//G_BC_CAT)[last()]
EDIT: OLD: //G_WEEK_OF_MONTH[last()]//G_NEW_DATE[last()]//G_BC_CAT
EDIT: And here's a similar query to retrieve the most recent date for the current month:
(//BID_CURVE_DATE)[last()]
If for some reason you want to grab just the yields for the last day of each week of the current month, I discovered by accident that this works:
//G_NEW_DATE[last()]//G_BC_CAT
-------------- Xpath queries for historical files ---------------
The following examples are for retrieving yields from one of the historical files (e.g., 2010 or earlier). So enter the URL for the historical file of interest in cell A1, and use the same ImportXml(A1,A2) function as above with one of the following xpath_queries in cell A2.
To retrieve yields for a specific date from a historical file, use an xpath_query like this:
G_BID_CURVE_DATE[BID_CURVE_DATE = '05-JAN-10']//G_BC_30YEAR
To retrieve yields for non-empty dates (e.g., skip Federal holidays), use this xpath_query:
//G_BC_30YEAR[BC_1MONTH>0]
I wanted to retrieve yields for the first non-empty date (non-holiday) from each historical file, so I first used the above query within an array function to limit the data to the first row and just the columns (maturities) I want, like this (assume URL is in cell A1, but I'll hardcode the xpath_query here):
=ARRAY_CONSTRAIN(IMPORTXML(A1,"//G_BC_30YEAR[BC_1MONTH>0]"),1,11)
I'd rather use just xml to retrieve yields for the first non-holiday of the year. Noting that often the first day of the year is a Federal holiday (New Years Day), this xpath_query retrieves yields for just the first two dates:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR
So assuming that the first non-holiday is in the first two week days of the year (no exception comes to mind), this works:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR[BC_1MONTH>0]
Still, I'd prefer an xpath query that returned yields for the first non-empty date of the year without relying on the assumption that it's in the first two date elements, since that's more general. Ideas?
EDIT: This works:
(//G_BC_30YEAR[BC_1MONTH>0])[1]
One gotcha I ran into is that some of the historical files, specifically verified in 2004 and 2005 have an empty first yield element (<BC_30YEAR/>). So if you assume the first yield element is the 1-month rate, as it is in the newer files, you get an empty column for 1-month and all yields are shifted one column to the right. I solved this by using a FILTER function around the ImportXml, but that's kludgy. I couldn't find a way using only xml to retrieve all but the first (empty) yield elements of the first non-holiday into a single row. The closest I came was this:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR[BC_1MONTH>0]/*[position() > 1]
EDIT: This is better; simpler and more general:
//G_BC_30YEAR[BC_1MONTH>0])[1]/*[position() > 1]
But this retrieves the yields into a one column multiple rows instead of one row multiple columns. The spreadsheet TRANSPOSE function can be used to solve this though. Again, assuming URL is in cell A1 and the xpath_query immediately above is in cell A2:
=TRANSPOSE(importXml(A1,A2))
Anyone know of an xml-only way to retrieve non-empty child elements into mutliple columns in one row?
That's it for now.
Kevin
---------- Locations of the XML files with treasury curve yields -----------
Yields for the current month are in an xml file at this URL: http://www.treasury.gov/resource-center ... /yield.xml.
Through some Googling and trial and error, I found the xml files for years 1991 through 2010. They are at the same URL as above, but with the file name format: yield_historical_YYYY.xml, for example:
http://www.treasury.gov/resource-center ... l_2010.xml
I've pulled data from the files for 2004-2010, verified that there is a file as old as 1991, and checked a couple of random years beween 1991 and 2004, and files existed, so I assume there are files for 1991-2010 using this filename convention at this location.
I cannot find the xml files for years 2011 through 2014. Does anyone know where they are???
EDIT: I found monthly files for 2011 using this filename convention: yield_mmmdd; e.g., for January 2011:
http://www.treasury.gov/resource-center ... _jan11.xml
The structure of the xml files with historical data is somewhat different than that for current month yields, and there are some differences between the historical files for 2004 and earlier and those for 2005 and later.
------------- Using ImportXml to retrieve yields for current month -----------
I use Google Sheets (aka Google Docs spreadsheets) pretty much exclusively, and Google provides an ImportXml function.
The syntax of ImportXML is: IMPORTXML(url, xpath_query). If you look at the xml files and know a little bit about xpath (or read a bit about it if you don't), the queries below shouldn't be hard to understand.
The following xpath_query returns all dates for which yields may be reported (i.e., weekdays) for the current month:
//BID_CURVE_DATE
So assuming the URL for the current month yields (first URL above) is in cell A1 of the spreadsheet, and the xpath_query is in cell A2, the ImportXml to retrieve the dates is:
ImportXml(A1,A2)
The dates are retrieved in one column (one row for each date).
The xpath_query to retrieve the yields for all maturities provided (i.e., 1-month through 30-years) for all dates for the current month is:
//G_BC_CAT
Each yield is returned in a separate column, with one row for each date. There is an extra element with the 30-year rate duplicated, so there will be two columns at the right with the 30-year yield. For a weekday that is a Federal holiday, all yield elements are empty (blank cell values) and the text FEDERAL HOLIDAY is returned in the last element (in an extra column to the right of the blank column that would normally have the second 30-year yield).
So you can use one ImportXml to retrieve the dates in one column, and then in the next column to the right use another ImportXml to retrieve the yields, so you have dates matched up with corresponding yields.
I came up with a single query that pulls the dates and yields, but it puts the date in one row and the corresponding yields in the next, so that's no good. As I recall, I used the union XML operator to combine the date and yield xpath queries above.
You can use some spreadsheet magic to grab the yields for just the most recent date (one row), but you also can do that with this xpath_query:
EDIT: Here's a simpler query that works:
(//G_BC_CAT)[last()]
EDIT: OLD: //G_WEEK_OF_MONTH[last()]//G_NEW_DATE[last()]//G_BC_CAT
EDIT: And here's a similar query to retrieve the most recent date for the current month:
(//BID_CURVE_DATE)[last()]
If for some reason you want to grab just the yields for the last day of each week of the current month, I discovered by accident that this works:
//G_NEW_DATE[last()]//G_BC_CAT
-------------- Xpath queries for historical files ---------------
The following examples are for retrieving yields from one of the historical files (e.g., 2010 or earlier). So enter the URL for the historical file of interest in cell A1, and use the same ImportXml(A1,A2) function as above with one of the following xpath_queries in cell A2.
To retrieve yields for a specific date from a historical file, use an xpath_query like this:
G_BID_CURVE_DATE[BID_CURVE_DATE = '05-JAN-10']//G_BC_30YEAR
To retrieve yields for non-empty dates (e.g., skip Federal holidays), use this xpath_query:
//G_BC_30YEAR[BC_1MONTH>0]
I wanted to retrieve yields for the first non-empty date (non-holiday) from each historical file, so I first used the above query within an array function to limit the data to the first row and just the columns (maturities) I want, like this (assume URL is in cell A1, but I'll hardcode the xpath_query here):
=ARRAY_CONSTRAIN(IMPORTXML(A1,"//G_BC_30YEAR[BC_1MONTH>0]"),1,11)
I'd rather use just xml to retrieve yields for the first non-holiday of the year. Noting that often the first day of the year is a Federal holiday (New Years Day), this xpath_query retrieves yields for just the first two dates:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR
So assuming that the first non-holiday is in the first two week days of the year (no exception comes to mind), this works:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR[BC_1MONTH>0]
Still, I'd prefer an xpath query that returned yields for the first non-empty date of the year without relying on the assumption that it's in the first two date elements, since that's more general. Ideas?
EDIT: This works:
(//G_BC_30YEAR[BC_1MONTH>0])[1]
One gotcha I ran into is that some of the historical files, specifically verified in 2004 and 2005 have an empty first yield element (<BC_30YEAR/>). So if you assume the first yield element is the 1-month rate, as it is in the newer files, you get an empty column for 1-month and all yields are shifted one column to the right. I solved this by using a FILTER function around the ImportXml, but that's kludgy. I couldn't find a way using only xml to retrieve all but the first (empty) yield elements of the first non-holiday into a single row. The closest I came was this:
//G_BID_CURVE_DATE[position() < 3]//G_BC_30YEAR[BC_1MONTH>0]/*[position() > 1]
EDIT: This is better; simpler and more general:
//G_BC_30YEAR[BC_1MONTH>0])[1]/*[position() > 1]
But this retrieves the yields into a one column multiple rows instead of one row multiple columns. The spreadsheet TRANSPOSE function can be used to solve this though. Again, assuming URL is in cell A1 and the xpath_query immediately above is in cell A2:
=TRANSPOSE(importXml(A1,A2))
Anyone know of an xml-only way to retrieve non-empty child elements into mutliple columns in one row?
That's it for now.
Kevin
Last edited by Kevin M on Thu Oct 23, 2014 5:28 pm, edited 1 time in total.
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
So 115 views and no replies. I guess that means no one who has viewed the post knows where the missing yield files are or has any tips on how to find them?Kevin M wrote: I cannot find the xml files for years 2011 through 2014. Does anyone know where they are???
Kevin
If I make a calculation error, #Cruncher probably will let me know.
-
- Posts: 5
- Joined: Wed Oct 22, 2014 8:24 pm
Re: Importing yields from treasury.gov into Google spreadshe
[OT comment removed by admin LadyGeek] why not just pull each of the curves from FRED?
http://research.stlouisfed.org/fred2/
http://research.stlouisfed.org/fred2/
Re: Importing yields from treasury.gov into Google spreadshe
I haven't use google spreadsheets before. But, for this data I've always just used copy and paste from the website (e.g. http://www.treasury.gov/resource-center ... &year=2013) into a LibreOffice spreadsheet
Re: Importing yields from treasury.gov into Google spreadshe
Sure, that's what I've done in the past, but it's not near as fun. The other thing is that once I get something set up in a spreadsheet, it can be dynamic or easily modified to pull data for similar analysis more quickly and easily than click, click ... copy/paste.stlutz wrote:I haven't use google spreadsheets before. But, for this data I've always just used copy and paste from the website (e.g. http://www.treasury.gov/resource-center ... &year=2013) into a LibreOffice spreadsheet
Simple example is some of the graphs I've been sharing in our long discussion on yield curves use data for today's yield curve (or for latest day available on treasury.gov). These yields are imported automatically with one of the ImportXml calls in my OP, so I don't have to manually copy/paste the yields to get the most current ones.
Note that the URL you shared is exactly the same is the ones where the xml files are up to "interest-rates", so it's basically the same source, but in a different format.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Kevin, I believe this will get you the data you want for 2011- present. It's not as nicely formatted though as in the xml you found. The dates throughout the year jump around, contrary to your file in which they present consecutively. It also appears that prior to 2011 rates data went to 17 decimal places whereas from 2011 on it's just 2. If you plug in 2010 into this url you'll see the 17 places, as well as the disordered dates. Regardless, hopefully this will give you what you need?Kevin M wrote:I cannot find the xml files for years 2011 through 2014. Does anyone know where they are???
http://www.treasury.gov/resource-center ... &year=2011
Re: Importing yields from treasury.gov into Google spreadshe
Thanks Angst! I'll play around with it and see if I can get it to work for me.Angst wrote:Kevin, I believe this will get you the data you want for 2011- present. It's not as nicely formatted though as in the xml you found. The dates throughout the year jump around, contrary to your file in which they present consecutively. It also appears that prior to 2011 rates data went to 17 decimal places whereas from 2011 on it's just 2. If you plug in 2010 into this url you'll see the 17 places, as well as the disordered dates. Regardless, hopefully this will give you what you need?Kevin M wrote:I cannot find the xml files for years 2011 through 2014. Does anyone know where they are???
http://www.treasury.gov/resource-center ... &year=2011
How did you find this?
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
While working on the xpath to get just the most recent date from the current month file, I learned some much simpler xpath syntax to retrieve the yields for the most recent date:Kevin M wrote: You can use some spreadsheet magic to grab the yields for just the most recent date (one row), but you also can do that with this xpath_query:
//G_WEEK_OF_MONTH[last()]//G_NEW_DATE[last()]//G_BC_CAT
(//G_BC_CAT)[last()]
That works, but if you use the syntax below, it just retrieves yields for all dates (note the missing parentheses):
//G_BC_CAT[last()]
I had to do some head scratching to figure out how the last() predicate works, but now I think I've got it. The latter syntax recursively traverses the tree and finds the lowest level G_BC_CAT descendant in each branch, which is just all G_BC_CAT elements (yields for all dates) . The former syntax recursively traverses the tree, returns a collection of all G_BC_CAT elements, then selects the last one from that collection.
So the last date in the current month file is retrieved with this xpath_query:
(//BID_CURVE_DATE)[last()]
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Don't kick yourself, but it's right there on the yield curve page: http://www.treasury.gov/resource-center ... &year=2013 You need to click where it says "If you are having trouble viewing the above XML in your browser, click here."Kevin M wrote:Thanks Angst! I'll play around with it and see if I can get it to work for me.
How did you find this?
Kevin
-
- Posts: 1819
- Joined: Thu May 26, 2011 9:36 pm
Re: Importing yields from treasury.gov into Google spreadshe
Thanks for that link -- I particularly liked the yield charts that let you compare real and nominal between any two dates.Angst wrote:
Don't kick yourself, but it's right there on the yield curve page: http://www.treasury.gov/resource-center ... &year=2013 You need to click where it says "If you are having trouble viewing the above XML in your browser, click here."
fd
I love simulated data. It turns the impossible into the possible!
Re: Importing yields from treasury.gov into Google spreadshe
Now that I've checked it out, I see that it's similar to what you get when you just click on the XML button. I had already played with that, but the problem is that it's not in an XML format the ImportXML seems to understand--at least I can't get it to work.Angst wrote: Don't kick yourself, but it's right there on the yield curve page
Here's a chunk from one of the xml files I've figured out how to import from:
Code: Select all
<G_BC_30YEAR>
<BC_1MONTH>0.05</BC_1MONTH>
<BC_3MONTH>0.08</BC_3MONTH>
<BC_6MONTH>0.18</BC_6MONTH>
<BC_1YEAR>0.45</BC_1YEAR>
<BC_2YEAR>1.09</BC_2YEAR>
<BC_3YEAR>1.66</BC_3YEAR>
<BC_5YEAR>2.65</BC_5YEAR>
<BC_7YEAR>3.36</BC_7YEAR>
<BC_10YEAR>3.85</BC_10YEAR>
<BC_20YEAR>4.60</BC_20YEAR>
<BC_30YEAR>4.65</BC_30YEAR>
<WEEK_OF_MONTH>1001</WEEK_OF_MONTH>
<BOND_MKT_UNAVAIL/>
</G_BC_30YEAR>
Code: Select all
<m:properties>
<d:Id m:type="Edm.Int32">5757</d:Id>
<d:NEW_DATE m:type="Edm.DateTime">2013-01-02T00: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.08</d:BC_3MONTH>
<d:BC_6MONTH m:type="Edm.Double">0.12</d:BC_6MONTH>
<d:BC_1YEAR m:type="Edm.Double">0.15</d:BC_1YEAR>
<d:BC_2YEAR m:type="Edm.Double">0.27</d:BC_2YEAR>
<d:BC_3YEAR m:type="Edm.Double">0.37</d:BC_3YEAR>
<d:BC_5YEAR m:type="Edm.Double">0.76</d:BC_5YEAR>
<d:BC_7YEAR m:type="Edm.Double">1.25</d:BC_7YEAR>
<d:BC_10YEAR m:type="Edm.Double">1.86</d:BC_10YEAR>
<d:BC_20YEAR m:type="Edm.Double">2.63</d:BC_20YEAR>
<d:BC_30YEAR m:type="Edm.Double">3.04</d:BC_30YEAR>
<d:BC_30YEARDISPLAY m:type="Edm.Double">3.04</d:BC_30YEARDISPLAY>
</m:properties>
Ideas?
Thanks,
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
hopefully there's someone here with xml experience who can help. i saw those differences too and just figured/hoped they were something easily dealt with. i suppose it wouldn't be worth the trouble to do a lot of search/replacements to clean things up, if that could actually solve the problem in the first place? i really have no meaningful experience with xml, but i do appreciate seeing the results you and others are able to put together and post based on data acquired around the net. good luck!Kevin M wrote:That doesn't look like any XML I've seen in the references/examples I've learned from, and I can't find an xpath_query that works with it.
Ideas?
Thanks,
Kevin
angie.
Importing yields from WSJ into Google spreadshe
I discovered last night that treasury quotes can be imported from the WSJ website with ImportXML. Here are the URL and xpath_query to use:
http://online.wsj.com/mdc/public/page/2 ... asury.html
(//table)[3]//tr
Before figuring out the xpath that works, I found that it's even easier to import the quotes with the ImportHTML function. Here is the syntax that works, assuming the URL is in cell A1:
=importhtml(A1,"table",3)
This retrieves data from the 3rd table specified by the HTML for the page.
Historical data can be retrieved as far back as 6/19/2008. Here is the URL for that date:
http://online.wsj.com/mdc/public/page/2 ... stcalendar
But for this page the table is the 4th table on the page, so the table index in the importHTML is 4 instead of 3:
=importhtml(A1,"table",4)
But for fun I wanted to see if ImportXML would work. As a first step, I used the Chrome browser tool "Inspect Element" to examine the page source. In the inspection window, right-click on one of the data elements in the table, then select "Copy Xpath", and paste it into a cell in spreadsheet. It looked something like this:
//*[@id="column0"]/div/table[1]/tbody/tr[2]/td[4]
Tried that and it didn't work, but using my newfound xpath knowledge, I experimented and found that this does work:
(//table)[3]//tr
This basically tells ImportXML to "retrieve the collection of all tables, then select the 3rd table from the collection, then retrieve all rows from that table".
ImportHTML is simpler and works, and that's probably what I'll use. However, ImportXML is more flexible; for example, I could just import a subset of the quotes with xpath like this:
(//table)[3]//tr[position() > 41 and position() < 249]
I think Xpath has date functions, so if I cared to spend the time learning about it, I probably could just import quotes by specifying the range of maturities of interest, but I can do it now by simply narrowing down the position numbers that contain the quotes for the maturities of interest. For example, the xpath_query above returns quotes for terms to maturity from 1-year to 10-years.
Kevin
http://online.wsj.com/mdc/public/page/2 ... asury.html
(//table)[3]//tr
Before figuring out the xpath that works, I found that it's even easier to import the quotes with the ImportHTML function. Here is the syntax that works, assuming the URL is in cell A1:
=importhtml(A1,"table",3)
This retrieves data from the 3rd table specified by the HTML for the page.
Historical data can be retrieved as far back as 6/19/2008. Here is the URL for that date:
http://online.wsj.com/mdc/public/page/2 ... stcalendar
But for this page the table is the 4th table on the page, so the table index in the importHTML is 4 instead of 3:
=importhtml(A1,"table",4)
But for fun I wanted to see if ImportXML would work. As a first step, I used the Chrome browser tool "Inspect Element" to examine the page source. In the inspection window, right-click on one of the data elements in the table, then select "Copy Xpath", and paste it into a cell in spreadsheet. It looked something like this:
//*[@id="column0"]/div/table[1]/tbody/tr[2]/td[4]
Tried that and it didn't work, but using my newfound xpath knowledge, I experimented and found that this does work:
(//table)[3]//tr
This basically tells ImportXML to "retrieve the collection of all tables, then select the 3rd table from the collection, then retrieve all rows from that table".
ImportHTML is simpler and works, and that's probably what I'll use. However, ImportXML is more flexible; for example, I could just import a subset of the quotes with xpath like this:
(//table)[3]//tr[position() > 41 and position() < 249]
I think Xpath has date functions, so if I cared to spend the time learning about it, I probably could just import quotes by specifying the range of maturities of interest, but I can do it now by simply narrowing down the position numbers that contain the quotes for the maturities of interest. For example, the xpath_query above returns quotes for terms to maturity from 1-year to 10-years.
Kevin
Last edited by Kevin M on Fri Oct 24, 2014 8:09 pm, edited 1 time in total.
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Thanks for the input Angie.
Apparently the files I'm using are "legacy" files. You can get to the current month legacy file by clicking on the link "To access interest rate data in the legacy XML format and the corresponding XSD schema, click here", but there are no links on the linked page to get to the historical data. I found the older files by Googling luck and trial and error.
My browser (Chrome) "understands" how to interpret the legacy files; i.e., you can collapse and expand nodes by clicking. Not so with the web page returned by clicking on the XML button.
However, as I'll share in next post, I've figured out how to retrieve the data directly from the page one views to see the yield curve rate table.
Thanks,
Kevin
Apparently the files I'm using are "legacy" files. You can get to the current month legacy file by clicking on the link "To access interest rate data in the legacy XML format and the corresponding XSD schema, click here", but there are no links on the linked page to get to the historical data. I found the older files by Googling luck and trial and error.
My browser (Chrome) "understands" how to interpret the legacy files; i.e., you can collapse and expand nodes by clicking. Not so with the web page returned by clicking on the XML button.
However, as I'll share in next post, I've figured out how to retrieve the data directly from the page one views to see the yield curve rate table.
Thanks,
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
As mentioned in previous post, I've figured out how to retrieve the rates directly from the treasury.gov rates page intended for human viewing. The key was the technique of using the Inspect element tool in the Chrome browser. Inspecting elements of the rates page and copying the xpath gave me xpath that didn't work, but from which I was able to figure out xpath that did work.
For example, the URL for the 2013 rate is:
http://www.treasury.gov/resource-center ... &year=2013
These are two xpath_queries that work; the first more closely resembling what Inspect element gave me, but the second I got working first:
(//*[@id="t-content-main-content"]//table)[last()]//tr
(//table)[68]//tr
With the URL in cell A1 and either of the above xpath_queries in cell A2, ImportXml(A1,A2) retrieves the rates into rows and columns as expected.
Now knowing that the data is in table 68 on the page, I tried ImportHTML(A1, "table", 68), and it worked perfectly as well.
Again, importXML is more flexible, since a subset of the data could be retrieved, but if one wants the entire year, either one works fine.
One downside of this solution is if the layout of the page changes, these imports might not work as is. But for now, at least there's a complete solution for anyone who wants to import historical rates from treasury.gov.
Kevin
For example, the URL for the 2013 rate is:
http://www.treasury.gov/resource-center ... &year=2013
These are two xpath_queries that work; the first more closely resembling what Inspect element gave me, but the second I got working first:
(//*[@id="t-content-main-content"]//table)[last()]//tr
(//table)[68]//tr
With the URL in cell A1 and either of the above xpath_queries in cell A2, ImportXml(A1,A2) retrieves the rates into rows and columns as expected.
Now knowing that the data is in table 68 on the page, I tried ImportHTML(A1, "table", 68), and it worked perfectly as well.
Again, importXML is more flexible, since a subset of the data could be retrieved, but if one wants the entire year, either one works fine.
One downside of this solution is if the layout of the page changes, these imports might not work as is. But for now, at least there's a complete solution for anyone who wants to import historical rates from treasury.gov.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Apparently TheSupperHappy1 posted something that was "off topic" (probably telling me how ignorant I am), but this turns out to be an excellent suggestion.TheSuperHappy1 wrote:[OT comment removed by admin LadyGeek] why not just pull each of the curves from FRED?
http://research.stlouisfed.org/fred2/
The data is formatted differently, but a good way to load a relatively static set of Treasury CMT data is to download the series for each maturity from FRED, and load it into a spreadsheet. There is one download XLS file for each maturity (e.g., 1-year, 2-years, etc.), but once loaded into a spreadsheet, this can then be easily manipulated to generate a yield curve for any date.
Here is the link to the constant maturity Treasury (CMT) data series:
Treasury Constant Maturity - FRED - St. Louis Fed
The daily yields for many nominal maturities go back to 1962, and there are monthly average yields back to 1953 for some maturities.
I still like having the importXML in a spreadsheet to keep the latest yield curve data current, but for historical data, pulling from FRED makes a lot more sense than messing around with treasury.gov.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Kevin,
I case its of interest, the FRED has developed a very powerful api that allows for importing of economic data. I use it routinely to get interest rate data into excel (using vba calls).
Fred API
Also, the FRED has developed an add-in for excel (that's uses the API) for easier import for those that don't want to program...
FRED Econ data add-in for MS Excel
MB
I case its of interest, the FRED has developed a very powerful api that allows for importing of economic data. I use it routinely to get interest rate data into excel (using vba calls).
Fred API
Also, the FRED has developed an add-in for excel (that's uses the API) for easier import for those that don't want to program...
FRED Econ data add-in for MS Excel
MB
Re: Importing yields from treasury.gov into Google spreadshe
I actually did take a quick look at it, and even tried to see if I could use ImportXml to load data, but it didn't work. I was impatient to get the data, so went with the xls download/import approach for now.mindbogle wrote: I case its of interest, the FRED has developed a very powerful api that allows for importing of economic data. I use it routinely to get interest rate data into excel (using vba calls).
My self-imposed limitation is that I live in Google world. Most of my computer time and all of my spreadsheet time is with my $250 Chromebook in my lap using free Google Sheets. There is scripting capability in Sheets, but so far I've managed to do what I need to get done without it.
If you want another small project to entertain you (c'mon, you know you do!), feel free to script an example of accessing FRED with Google sheets and share it. You could even contribute it as an add-on. I've done lots of coding/scripting in the past (although none since I retired in 2007), so a good example such as this might motivate me to dust off my scripting skills.
Thanks MB,
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: Importing yields from treasury.gov into Google spreadshe
Just FYI, MB and I independently figured out how to get something working with FRED using ImportXML. I'm playing around with it now to see how flexible it is.
Kevin
Kevin
If I make a calculation error, #Cruncher probably will let me know.