Google spreadsheet Ibond price code acting weird

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Google spreadsheet Ibond price code acting weird

Post by dual »

I use a google spreadsheet to track my investments. My code to lookup Ibond prices and insert into a spreadsheet stopped working a couple of weeks ago. I was hoping that this was due to the administrators being on Christmas holiday but it still is not working today.

The weird thing is that sometimes one of the cells works. For example, today this worked but none of the other ones did

Code: Select all

=500+INDEX(ImportHtml("http://www.treasurydirect.gov/BC/SBCPrice?Series=I&Denomination=500&IssueDate=08/2000&btnAdd.x=CALCULATE","table",0),2,8,11)


Questions:

If you use similar code, does yours work?

Does anyone have a different approach to getting Ibond values that does work?
Postmon
Posts: 294
Joined: Mon Jan 02, 2012 2:46 pm

Re: Google spreadsheet Ibond price code acting weird

Post by Postmon »

I use the function to get ETF/MF prices for my Google spreadsheet. Over the holidays it wasn't working properly. It would go back and forth between showing the price and an error message every 5 minutes or so. However, yesterday it seemed to be working fine for me.
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

Postmon wrote:I use the function to get ETF/MF prices for my Google spreadsheet. Over the holidays it wasn't working properly. It would go back and forth between showing the price and an error message every 5 minutes or so. However, yesterday it seemed to be working fine for me.
You use the function I posted for ETF and mutual funds??

I use the GoogleFinance function for those. For example

Code: Select all

=GoogleFinance("vti","price")
Could you provide an example of using the function I posted for an ETF?
Postmon
Posts: 294
Joined: Mon Jan 02, 2012 2:46 pm

Re: Google spreadsheet Ibond price code acting weird

Post by Postmon »

Sorry, I should have been more clear -- I use the GoogleFinance price function. That was the one that wasn't working. I'm assuming they were doing maintenance or something else at the time.
User avatar
dbCooperAir
Posts: 1107
Joined: Tue Jan 07, 2014 10:13 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dbCooperAir »

dual wrote: I use the GoogleFinance function for those. For example

Code: Select all

=GoogleFinance("vti","price")
I learn more here by accident then by design.

I'm a relative new user to Google Docs, this works great, thanks for sharing.
Neither a wise man nor a brave man lies down on the tracks of history to wait for the train of the future to run over him. | -Dwight D. Eisenhower-
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

dbCooperAir wrote: I'm a relative new user to Google Docs, this works great, thanks for sharing.
Always happy to help :happy

However, as you can see from my experience, using google spreadsheet for portfolio tracking has problems. I have found the GoogleFinance function to be pretty reliable but it is quite limited. AFAIK it cannot do bonds.

Screen scraping from other websites as in the Ibonds example is problematic. The web page may change, which will break the function. I used to use Fidelity to look up bonds, but apparently they did not appreciate being used as a data source so they seem to have blocked the google docs ip addresses.

I am currently looking for alternatives to google.
rpj2004
Posts: 18
Joined: Sat Feb 27, 2010 4:17 pm

Re: Google spreadsheet Ibond price code acting weird

Post by rpj2004 »

Here's an alternative approach using Apps Script. In Google sheets go to Tools > Script Editor and create a blank script. Copy and paste the following code:

Code: Select all

function getBondData(series,denomination,issueDate,redemptionDate) {
  if (redemptionDate instanceof Date) { redemptionDate = Utilities.formatDate(redemptionDate, "GMT", "M/yyyy") };
  if (issueDate instanceof Date) { issueDate = Utilities.formatDate(issueDate, "GMT", "M/yyyy") };
  var payload =
   {
     "RedemptionDate" : redemptionDate,
     "Series" : series,
     "Denomination" : denomination.toString(),
     "IssueDate" : issueDate,
     "btnAdd.x" : "CALCULATE"
   };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };
 
  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);

  bondText = response.getContentText().match(/<table id="ta1">[\s\S]*?<\/table>/g).toString();
  bondData = bondText.match(/\d*\.\d*/g);
  return bondData;

}
You can then use this function in Google Sheets as =getBondData("series","denomination","issueDate","redemptionDate") where issue dates are in the format month/year. Values for denomination and series will have to equal what's on the drop down at http://www.treasurydirect.gov/BC/SBCPrice.
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

Thanks for sharing the script rpj2004! Any tips on coming up to speed on the options object; e.g., figuring out how to populate payload?

For other Google script novices ...

After playing around with the script and doing some debugging, I got it working. For example, I assumed that the "series" parameter should be what's shown in the dropdown box on treasurydirect.gov, e.g., "I Bonds", but it's actually just "I" or "EE". So this formula works (in a sheet cell):

=getBondData("I", "100", "01/2013", "01/2015")

I broke up the code a bit because it helped with debugging and figuring it out; e.g.,

Code: Select all

  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);
  var oContent = response.getContentText();
  var oTable = oContent.match(/<table id="ta1">[\s\S]*?<\/table>/g);
  var sTable = oTable.toString();
  Logger.log(sTable);
  var bondData = sTable.match(/\d*\.\d*/g);
  Logger.log(bondData);
Also, if you place this before the function, you'll get the auto-complete help as you do for a standard Google Sheets function:

Code: Select all

/**
 * Retrieve savings bond data from TreasuryDirect.gov.
 *
 * @param {string} series - savings bond series (e.g., "I", "EE").
 * @param {string} denomination - savings bond denomination (e.g., "100").
 * @param {string} issueDate - issue date, format mm/yyyy.
 * @param {string} redemptionDate - redemption date, format mm/yyyy.
 * @return savings bond data.
 * @customfunction
 */
Back to the OP, I also have found ImportHTML and ImportXML a bit flaky, but still useful. With respect to the tangent discussion on GoogleFinance, I've found it pretty reliable, although occasionally get #NA in the cell. I use GoogleFinance extensively in a number of spreadsheets.

I've seen some similar unreliability, as in the Google Import functions, in my own custom functions; e.g., sometimes I just see "Loading ..." in the cell. Sometimes deleting the formula from the cell and the undoing the delete resolves it. I haven't nailed down yet where it's getting hung up, whether on fetching the URL, parsing the XML, or something else. But at least with my own script I can dig into it and try and figure out where it's hanging up.

The thing about any of these approaches is that you're dependent on the structure of the web page you're pulling data from. If it changes, it could break your script or cause your call to one of the import functions to not work. That may be what happened to the OP.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
User avatar
#Cruncher
Posts: 3056
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by #Cruncher »

dual wrote:Does anyone have a different approach to getting Ibond values that does work?
Here are three alternatives:
  1. Continue to use the Index and ImportHTML functions, but with the eyebonds.info/ibonds web site instead of TreasuryDirect. This site is composed entirely of simple static HTML pages containing a single table so accessing it might be more reliable. Here is sample code to look up the January 2015 value of a $500 I Bond Purchased August 2000:

    Code: Select all

    Row                  Column A                          Description                       Formula
    ---  -----------------------------------------------  -------------  -----------------------------------------------
      1                      http://eyebonds.info/ibonds  web site root
      2                                              500  denomination
      3                                             2000  purchase year
      4                                                8  purchase month
      5                                             2015  value year
      6                                                1  value month
      7  http://eyebonds.info/ibonds/500/ib_2000_08.html  URL            =A1&"/"&A2&"/"&"ib_"&A3&"_"&right("0"&A4,2)&".html"
      8                                               17  table row      =A5-A3+2
      9                                                2  table column   =A6+if(A6>mod(A4,6),2,0)+if(A6>mod(A4,6)+6,2,0)+1
                                               $1,187.40  Value 1/1/2015 =INDEX(ImportHtml(A7,"table",1),A8,A9)
    The most complicated part is computing the correct table column. This is because the table includes two pairs of columns listing the semi-annual inflation and composite rates in addition to the columns with the actual I Bond value.
  2. Use a script to calculate the value of an I Bond on the fly. LazyNihilist describes one he wrote for Google Docs in this post.
  3. Use the I Bond Portfolio Calculator to compute the values of all your I Bonds for every month during a given year. Then use an Excel lookup function to pull the values for whatever month you're processing.
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

Thanks for all the suggestions. I looked around and have not found a better alternative than google spreadsheets to get all the values for my investments.

The code that I entered in the original post still does not work. This had worked for over a year so it seems like there has been a change in the webpage although it looks the same.

I decided to use a manual look up with the TreasuryDirect savings bond calculator. I had already structured my spreadsheet so the values of the individual bonds that I own are entered in a column in the same order as my bond inventory in the TDirect calulator html file. This is a vestige of my previous use of Excel to track my portfolio. Then I compute the total by using the number of bonds I own in other cells. This is structured so I can copy and paste from the savings bond calculator webpage. BTW, to copy a column in Firefox, hold down the control key, click and drag. I paste this into the google spreadsheet.

I have tested the function suggested by rpj2004 and managed to get it to work mostly. However I had troubles getting google spreadsheets to find the functions I entered into a script. It kept saying that my functions were unknown. I finally got it to recognize my functions but I do not know why it started working.

Anyway, as I mentioned above, all of these screen scraping approaches are fragile and since I only do updates occasionally and the Ibond values change only monthly, I think the manual approach is adequate.

I still do not have a good way to get the value of individual bonds from their CUSIP. I get the values of TIPS from a private website that I will not name since I want them to continue to allow access. I only have a few other bonds and I enter them manually from the Fidelity website, which I can access with my browser but not from a spreadsheet function.
User avatar
#Cruncher
Posts: 3056
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by #Cruncher »

dual previous post wrote:The code that I entered in the original post still does not work. This had worked for over a year so it seems like there has been a change in the webpage although it looks the same.
dual in original post wrote:

Code: Select all

=500+INDEX(ImportHtml("http://www.treasurydirect.gov/BC/SBCPrice?Series=I&Denomination=500&IssueDate=08/2000&btnAdd.x=CALCULATE","table",0),2,8,11)
This code is bad. The third parameter to the ImportHTML function should be 4, not 0, since the data is in the fourth table on the web page. [ * ] And you have one too many parameters to the Index function. After fixing these errors, the following does work, returning $1,187.40:

Code: Select all

=500+INDEX(ImportHtml("http://www.treasurydirect.gov/BC/SBCPrice?Series=I&Denomination=500&IssueDate=08/2000&btnAdd.x=CALCULATE","table",4),2,8)
* Use your browser's option to View the Page Source and look for the fourth occurrence of "<table".
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

#Cruncher wrote:
dual in original post wrote:

Code: Select all

=500+INDEX(ImportHtml("http://www.treasurydirect.gov/BC/SBCPrice?Series=I&Denomination=500&IssueDate=08/2000&btnAdd.x=CALCULATE","table",0),2,8,11)
This code is bad.
Yes, but it had been working for a long time.
The third parameter to the ImportHTML function should be 4, not 0, since the data is in the fourth table on the web page. [ * ]
This shows how difficult it is to use these functions. Apparently the web page changed although it looks the same.
And you have one too many parameters to the Index function.
It looks like I was using an old version of the index function. According to the google documentation

https://support.google.com/docs/answer/3098242
the third parameter, the range, is ignored by the current version of the spreadsheet.

After fixing these errors, the following does work, returning $1,187.40:

Code: Select all

=500+INDEX(ImportHtml("http://www.treasurydirect.gov/BC/SBCPrice?Series=I&Denomination=500&IssueDate=08/2000&btnAdd.x=CALCULATE","table",4),2,8)
* Use your browser's option to View the Page Source and look for the fourth occurrence of "<table".
Thanks for the help.

edit: I tried out your code and could not make it work. I get the error
error: The data could not be retrieved. Please check the URL.

:?
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

dual wrote:II have tested the function suggested by rpj2004 and managed to get it to work mostly. However I had troubles getting google spreadsheets to find the functions I entered into a script. It kept saying that my functions were unknown. I finally got it to recognize my functions but I do not know why it started working.
It turns out that there are two versions of the spreadsheets. My portfolio tracking sheet is the "old" kind and the sheet I created to test is the "new" kind. The test sheet recognized the functions in the script but when I copied the scripts to the old sheet it did not work. In their inimitable fashion, google whose motto is "do no harm" does not provide a way to migrate an old spreadsheet to the new kind. All they say is that they will do it by and by when they get around to it.
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

dual wrote:In their inimitable fashion, google whose motto is "do no harm" does not provide a way to migrate an old spreadsheet to the new kind. All they say is that they will do it by and by when they get around to it.
They actually do document a couple of ways to "migrate" an old sheet to new sheet, but in my experience, they don't work well if you've done anything but pretty basic stuff in them. For example, you can export ("Download as") old, then create a new sheet and import into it, but you lose anything that isn't supported by the exported standard, which in my case was a lot in some of my sheets.

There are two export formats to try: .xlsx and .ods. As I recall, with one of them I lost things like conditional formatting, and with the other I lost anything Google-specific like the GoogleFinance function; or maybe I lost the latter with both ways. Anyway, neither one retained enough of some of my more interesting spreadsheets to be worth hassling with for now.

You might try that and see how much gets broken.

I really like some of the features of the new sheets, but am living with the old version in most of my old sheets until Google gets around to migrating them all (hopefully without breaking too much).

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
rpj2004
Posts: 18
Joined: Sat Feb 27, 2010 4:17 pm

Re: Google spreadsheet Ibond price code acting weird

Post by rpj2004 »

dual wrote:
dual wrote:II have tested the function suggested by rpj2004 and managed to get it to work mostly. However I had troubles getting google spreadsheets to find the functions I entered into a script. It kept saying that my functions were unknown. I finally got it to recognize my functions but I do not know why it started working.
It turns out that there are two versions of the spreadsheets. My portfolio tracking sheet is the "old" kind and the sheet I created to test is the "new" kind. The test sheet recognized the functions in the script but when I copied the scripts to the old sheet it did not work. In their inimitable fashion, google whose motto is "do no harm" does not provide a way to migrate an old spreadsheet to the new kind. All they say is that they will do it by and by when they get around to it.
Hi Dual,

Sorry my code wasn't self explanatory and that you had to do some work to get it figured out. Looks like you got it, but let me know if you have any questions and I can answer them promptly this weekend.

Scripts that are written with the old Sheets and do not readily work in the new Sheets are unlikely to be migrated properly ever. There are fundamental changes to the Sheets JavaScript objects in the new version. This is true for some functions as well.

There is a way to migrate your old spreadsheet to the new Sheets manually, while preserving all functions, including Google specific ones:
1) Create a new Sheet and give it a name. This should automatically be created with the new version.
2) Go to your old spreadsheet. For each sheet (tab) at the bottom: 1) Click the down arrow. 2) Click "Copy To..." 3) Click the newly created spreadsheet 4) Click Select 5) Wait for the copy to occur, and you'll see a message with success or failure.
3) Go to your new spreadsheet. Everything sheet (tab) specific should be there (named ranges, formulas, formatting, etc.). Scripts will have to be copied over manually by copy-pasting into new script files. Formulas may be broken, but only if there have been changes in the way the formula takes and/or processes input. I have had problems with XIRR, FILTER, and some others. The problems are mostly with the Lookup functions.
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

rpj2004 wrote: Looks like you got it, but let me know if you have any questions and I can answer them promptly this weekend.
Thanks for your offer.

Your function seems to put out a variable number of strings in a column. I want just the bond value and I noticed that the second string had the bond value in a test example, so I modified the return line in your code to

Code: Select all

return bondData[1];
This worked OK but failed when there was a comma in the number on the webpage. For example:
This returns the correct value:

Code: Select all

getBondData("I",100,"6/2011",today())
But this does not

Code: Select all

getBondData("I",5000,"6/2011",today())
I fooled around with your regular expression

Code: Select all

/\d*\.\d*/g
but no luck. I hate regular expressions so I keep a library of handy expressions. My usual one that matches any number did not work. The editor claimed that there was an illegal character in the line:

Code: Select all

(\+|-)?([0-9]+\.?[0-9]*|\.[0-9]+)([eE](\+|-)?[0-9]+)?
What I finally did was use this workaround:

Code: Select all

=I3*getBondData("I",100,J3,$J$2)/100
where I3 has the bond denomination and J3 has the date and $J$2 has today's date.

This works reliably so far but is obviously not very elegant. One of my mottoes when I was a software manager was to stamp out creeping elegance so I quit. But you may want to go for elegance by patching up the regular expression. :D
rpj2004
Posts: 18
Joined: Sat Feb 27, 2010 4:17 pm

Re: Google spreadsheet Ibond price code acting weird

Post by rpj2004 »

Dual,

Thanks for catching the error. I've updated the code to fix it and include formula autocomplete (thanks Kevin for the code).

Another option instead of returning a single value like bondData[1] is to use the INDEX function in Sheets:

=INDEX(getBondData(C6,C3,C5,C4),2)

Code: Select all

 /**
 * Retrieve savings bond data from TreasuryDirect.gov.
 *
 * @param {string} series - savings bond series (e.g., "I", "EE").
 * @param {string} denomination - savings bond denomination (e.g., "100").
 * @param {string} issueDate - issue date, format mm/yyyy.
 * @param {string} redemptionDate - redemption date, format mm/yyyy.
 * @return savings bond data.
 * @customfunction
 */

function getBondData(series,denomination,issueDate,redemptionDate) {  
  
  if (redemptionDate instanceof Date) { redemptionDate = Utilities.formatDate(redemptionDate, "GMT", "M/yyyy") };
  if (issueDate instanceof Date) { issueDate = Utilities.formatDate(issueDate, "GMT", "M/yyyy") };
  var payload =
   {
     "RedemptionDate" : redemptionDate,
     "Series" : series,
     "Denomination" : denomination.toString(),
     "IssueDate" : issueDate,
     "btnAdd.x" : "CALCULATE"
   };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };
 
  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);

  bondText = response.getContentText().match(/<table id="ta1">[\s\S]*?<\/table>/g).toString();
  bondData = bondText.match(/[\d,]*\.\d*/g);
  return bondData;

}
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

Here's a workaround that requires no changes to the script (at least not my slightly modified version). This works:

=index(getBondData("I",5000,date(2013,1,1),today()),2,1)

If you leave off the INDEX function, values are returned (i.e., no ERROR), but the first value is incorrect; i.e., it leaves off the comma and everything before it, so returns 000.00 instead of 5,000.00. Since you are interested only in the second returned value, this bug doesn't really affect you.

I don't know if it's the most elegant solution, but this regexp works for all cases, including denomination 5,000:

/\d*,*\d*\.\d*/g

So it's "digit 0 or more times followed by comma 0 or more times followed by digit 0 or more times followed by period followed by digit 0 or more times".

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

rpj2004 wrote: <snip> /[\d,]*\.\d*/g);
Of course more elegant than my brute force version ( [\d,]* = digit or comma zero or more times).

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
User avatar
Topic Author
dual
Posts: 679
Joined: Mon Feb 26, 2007 7:02 pm

Re: Google spreadsheet Ibond price code acting weird

Post by dual »

Thanks to RJP and Kevin for your suggestions. Changing the pesky regular expression seemed to do the trick. Now it matches the TreasuryDirect website for the cases I have tried. BTW, it also works for EE bond data.

I am not clear the advantage of using the INDEX function so I continue to return bondData[1]. I suppose if I wanted to access other members of the output array, this might be useful but for now all I want is the value.
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

dual wrote: I am not clear the advantage of using the INDEX function so I continue to return bondData[1]. I suppose if I wanted to access other members of the output array, this might be useful but for now all I want is the value.
Yes, just two different ways of doing it. The advantages of using INDEX are not having to modify the script, and as you implied, keeping the script more general purpose. I first used it as a workaround for the original bug in the script before playing around with the regexp.

Another handy function to limit rows and columns returned by a function or formula, but available only in new Google Sheets, is ARRAY_CONSTRAIN. So if for some reason you just wanted the first two values (2 rows, 1 column) returned by the function, you could use:

=ARRAY_CONSTRAIN(getBondData($A$2,$B$2,$C$2,$D$2),2,1)

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google spreadsheet Ibond price code acting weird

Post by Kevin M »

Personally I think I'd make the script a little more general purpose, and return the entire header row and data row. Not only does this provide more flexibility, but it enables you to quickly verify what data is being returned (when I first ran the script, I didn't know what each number was).

Here is the code snippet that does this (all other code in the script is the same):

Code: Select all

  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);
  var oContent = response.getContentText();
  var oTable = oContent.match(/<table id="ta1">[\s\S]*?<\/table>/g);
  var sTable = oTable.toString();
  var sHeader = sTable.match(/[TY][a-zA-Z ]*/g);
  var sData = sTable.match(/[\d,]*\.\d*/g);
  var aTable = [];
  aTable.push(sHeader);
  aTable.push(sData);
  return aTable;
(I'm using prefixes like "o", "s", and "a" to help me keep my data types straight).

This returns data to the spreadsheet in this form:

Code: Select all

Total Price    Total Value    Total Interest  YTD Interest
1,000.00       1,025.60       25.60           1.60
You can still easily pick out an individual value if you want using INDEX.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
xPat
Posts: 13
Joined: Mon Jan 19, 2015 5:37 am

Re: Google spreadsheet Ibond price code acting weird

Post by xPat »

I know this topic is a couple of years old, but I'm interested in getting the Google Script to work with my spreadsheet. Am I correct that the redemptionDate should be the current month if you want to know the current value? i.e. make it TODAY() to keep it current?
rpj2004 wrote:

Code: Select all

 /**
 * Retrieve savings bond data from TreasuryDirect.gov.
 *
 * @param {string} series - savings bond series (e.g., "I", "EE").
 * @param {string} denomination - savings bond denomination (e.g., "100").
 * @param {string} issueDate - issue date, format mm/yyyy.
 * @param {string} redemptionDate - redemption date, format mm/yyyy.
 * @return savings bond data.
 * @customfunction
 */

function getBondData(series,denomination,issueDate,redemptionDate) {  
  
  if (redemptionDate instanceof Date) { redemptionDate = Utilities.formatDate(redemptionDate, "GMT", "M/yyyy") };
  if (issueDate instanceof Date) { issueDate = Utilities.formatDate(issueDate, "GMT", "M/yyyy") };
  var payload =
   {
     "RedemptionDate" : redemptionDate,
     "Series" : series,
     "Denomination" : denomination.toString(),
     "IssueDate" : issueDate,
     "btnAdd.x" : "CALCULATE"
   };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };
 
  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);

  bondText = response.getContentText().match(/<table id="ta1">[\s\S]*?<\/table>/g).toString();
  bondData = bondText.match(/[\d,]*\.\d*/g);
  return bondData;

}
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

returning the more detailed table

Post by sheetzilla »

I know this is a very old thread but it's got me started on this.
This is probably more of a javascript question than anything else; this is outside of my abilities and I'm not finding answers on the internet.

My goal is to return the second results table on the Treasury Direct site as an array. Example:
Serial # Series Denom Issue Date Next Accrual Final Maturity Issue Price Interest Interest Rate Value Note NA
$50 10/2010 06/2019 10/2040 $50.00 $9.32 2.52% $59.32
I have banged on the code and now it looks like this:

Code: Select all

 /**
 * Retrieve savings bond data from TreasuryDirect.gov.
 *
 * @param {string} series - savings bond series (e.g., "I", "EE").
 * @param {string} denomination - savings bond denomination (e.g., "100").
 * @param {string} issueDate - issue date, format mm/yyyy.
 * @param {string} redemptionDate - redemption date, format mm/yyyy.
 * @return savings bond data.
 * @customfunction
 */

function getBondData(series,denomination,issueDate,redemptionDate) {  
  
  if (redemptionDate instanceof Date) { redemptionDate = Utilities.formatDate(redemptionDate, "GMT", "M/yyyy") };
  if (issueDate instanceof Date) { issueDate = Utilities.formatDate(issueDate, "GMT", "M/yyyy") };
  var payload =
   {
     "RedemptionDate" : redemptionDate,
     "Series" : series,
     "Denomination" : denomination.toString(),
     "IssueDate" : issueDate,
     "btnAdd.x" : "CALCULATE"
   };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };

  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);
  var oContent = response.getContentText();
//  var oTable = oContent.match(/<table id="ta1">[\s\S]*?<\/table>/g);
  var oTable = oContent.match(/<table class="bnddata">[\s\S]*?<\/table>/g);
  var sTable = oTable.toString();
  var sHeader = sTable.match(/[AZ][a-zA-Z ]*/g);
  var sData = sTable.match(/[\d,]*\.\d*/g);
  var aTable = [];
  aTable.push(sHeader);
  aTable.push(sData);
//  return aTable;
  return sTable;
//  return oContent;




  
//  bondText = response.getContentText().match(/<table id="ta1">[\s\S]*?<\/table>/g).toString();
//  bondData = bondText.match(/[\d,]*\.\d*/g);
//  return bondData;

}
So I can get the correct table into the 'sTable' variable and it looks like this:

Code: Select all

"<table class=""bnddata"">
			<thead>
			<tr>
				<th class=""lft"">Serial #</th>
				<th>Series</th>
				<th>Denom</th>
				<th>Issue<br />Date</th>
				<th>Next<br />Accrual</th>
				<th>Final<br />Maturity</th>
				<th>Issue<br />Price</th>
				<th>Interest</th>
				<th>Interest<br />Rate</th>
				<th>Value</th>
				<th>Note</th>
				<th class=""rgt"">&nbsp;</th>
			</tr>
			</thead>
			<tbody>
			
			<tr class=""altrow1"">
<td class=""lft"">NA</td>
<td class=""se"">I</td>
<td>$50</td>
<td>10/2010</td>
<td>06/2019</td>
<td>10/2040</td>
<td>$50.00</td>
<td>$9.32</td>
<td>2.52%</td>
<td><strong>$59.32</strong></td>
<td class=""nt"">&nbsp;</td>
<td class=""rgt""><input class=""linkbutton"" type=""submit"" name=""btnDel0.x"" value=""REMOVE"" /></td>
</tr>

			
			</tbody>
		</table>"
Which is exactly the right thing. I just need to get it into an array.
There are some hints here but not enough for me to figure it out.

I suspect the answer is Jsoup rather than regex but that is the end of my abilities. Can Jsoup be used in google sheets scripts?

Can anyone help? It would be great to have that more detailed table in an array, in particular I want to see the rate each iBond is currently paying.

Thanks in advance. I think this could be useful to a lot of people.

-sz
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: returning the more detailed table

Post by Kevin M »

sheetzilla wrote: Mon May 20, 2019 9:10 pm I know this is a very old thread but it's got me started on this.
This is probably more of a javascript question than anything else; this is outside of my abilities and I'm not finding answers on the internet.

My goal is to return the second results table on the Treasury Direct site as an array. Example:
Serial # Series Denom Issue Date Next Accrual Final Maturity Issue Price Interest Interest Rate Value Note NA
$50 10/2010 06/2019 10/2040 $50.00 $9.32 2.52% $59.32
I can come back to your script later if you want, but below is something I put together to get interest and composite rate. You can add a few lines if you want to get the other items.

Instead of using regular expressions I wrote a secondary script to extract the HTML between a start tag and end tag, which I've also used for other scripts. I use that to get the HTML of interest, then manipulate it a bit so that it can be parsed as XML. Then it's easy to extract the particular elements of interest using XML navigation.

I also return any "note" that TD displays.

In my spreadsheet, the returned result looks something like this:

Code: Select all

Interest Rate   Note
-------- ----   ---------------------
416	 2.42%	Includes 3 month interest penalty

Code: Select all

/**
 * Retrieve savings bond interest and composite rate from TreasuryDirect.gov.
 *
 * @param {"I"} series savings bond series: "I", "EE", "E", or "SN".
 * @param {5000} denomination Savings bond denomination; number or string.
 * @param {"05/2011"} issueDate Issue date: string formated mm/yyyy or date.
 * @param {DATE(2019,5,1)} valuationDate Value as of date: string formated mm/yyyy or date.
 * @return savings bond data.
 * @customfunction
 */
function getSavingsBondInterest(series,denomination,issueDate,valuationDate) {
  var debug = false;                                       // true for testing, false for production.
  
  if (debug) {
    // Set variables for testing.
    if (!series) var series = "E";
    if (!denomination) var denomination = "100";
    if (!issueDate) var issueDate = "6/1980";
    if (!valuationDate) var valuationDate = new Date(2019, 7, 15);
  }
  
  if (debug) Logger.log(series + ", " + denomination + ", " + issueDate + ", " + valuationDate);
  if (valuationDate instanceof Date) { valuationDate = Utilities.formatDate(valuationDate, "GMT", "M/yyyy") };
  if (issueDate instanceof Date) { issueDate = Utilities.formatDate(issueDate, "GMT", "M/yyyy") };
  if (debug) Logger.log(series + ", " + denomination + ", " + issueDate + ", " + valuationDate);
  
  var payload =
  {
    "RedemptionDate" : valuationDate,
    "Series" : series,
    "Denomination" : denomination.toString(),
    "IssueDate" : issueDate,
    "btnAdd.x" : "CALCULATE"
  };
  
  var options =
   {
     "method" : "post",
     "payload" : payload
   };
 
  var url = "http://www.treasurydirect.gov/BC/SBCPrice";
  var response = UrlFetchApp.fetch(url,options);
  if (debug) Logger.log('Response code: %s', response.getResponseCode());
  var contentText = response.getContentText();
  
  // Extract just the table data rows with the data of interest.
  var startTag = '<td class="lft">';
  var endTag = '<td class="rgt">';
  var includeStartTag = true;
  var includeEndTag = false;
  var xml = extractHtmlElement(contentText, startTag, endTag, includeStartTag, includeEndTag);
  
  // Throw an error if no xml obtained.
  if (xml.length < 1) {
    // Tags bracketing error message.
    startTag = '<div class="errormessage"><p>The following error(s) have occurred:</p><ul><li>';
    endTag = '</li>';
    includeStartTag = false;
    includeEndTag = false;
    var errMsg = extractHtmlElement(contentText, startTag, endTag, includeStartTag, includeEndTag);
    throw 'No data retrieved for parameters Series: ' + series + ', Denomination: ' + denomination +
           ', Issue date: ' + issueDate + ', Valuation date: ' + valuationDate
           + '. Additional info: ' + errMsg;
  }
  
  // Replace empty rows with valid xml, since '&nbsp;' causes xml parser to fail.
  // Empty row before fixing is '<td>&nbsp;</td>'; replace with '<td><td>'.
  
  var regExp = /&nbsp;/gi;
  xml = xml.replace(regExp, '');
     
  // Add a root element to create valid xml.
  xml = '<root>' + xml + '</root>';                 //Create a root element.
  if (debug) Logger.log(xml);
  
  // Parse xml and make it pretty to aid developing navigation.
  var document = XmlService.parse(xml);
  xml = XmlService.getPrettyFormat().format(document);
  if (debug) {
    Logger.log('----------- xml pretty format -----------------------------');
    Logger.log(xml);
    Logger.log('----------------------------------------');
  }
  
  // Get the data elements from the table rows, and in debug mode, log them.
  var children = document.getRootElement().getChildren();
  
  if (debug) {
    Logger.log('------------------ Children of root element -------------------');
    for (i = 0; i < children.length; i++) {
      Logger.log('Index, value: %s, %s',i, children[i].getText());
    }
    Logger.log('Note (child of children[10]): %s', children[10].getChildText('a'));
  }
  
  // Get the data elements of interest.
  var interest = children[7].getText();
  var rate = children[8].getText();
  var note = children[10].getChildText('a');              // <a ...> is the child HTML tag, hence the XML child name.
  
  // Convert note from abbreviation to descriptive text.
  switch(note) {
    case 'NI':
      note = 'Not issued';
      break;
    case 'NE':
      note = 'Not eligible for redemption';
      break;
    case 'P5':
      note = 'Includes 3 month interest penalty';
      break;
    case 'MA':
      note = 'Matured and not earning interest';
      break;
    default:
      note = '';
  }
  
  // Format interest and rate for conversion to number.
  interest = interest.substring(1);                      // Remove $ from beginning.
  rate = rate.substring(0,rate.indexOf('%'));            // Remove % from end.
  if (debug) {
    Logger.log('----------------------------------------');
    Logger.log('interest: %s, rate: %s, note: %s', interest, rate, note);
    Logger.log('----------------------------------------');
  }
  
  // Convert interest to a number.
  interest = Number(interest);
  
  // If rate missing, the bond has matured, so return '' instead of a rate.
  // Otherwise convert it to a number.
  if (rate.length > 0) {
    rate = Number(rate)/100;                               // e.g., 1.40 -> 0.013999999999999999.
    if (debug) Logger.log(rate);
    rate = Number(rate.toFixed(4));                        // e.g., 0.013999999999999999 -> 0.0140.
  } else {
    rate = '';
  }
 
  var aTable = [[interest, rate, note]];
  if (debug) Logger.log(aTable);
  return aTable;
}

function extractHtmlElement(html, startTag, endTag, includeStartTag, includeEndTag) {
  // Extract the HTML element between startTag and endTag.
  // Include startTag if includeStartTag == true.
  // Include endTag if includeEndTag == true.
  var offsetStart = 0;
  var offsetEnd = 0;
  if (!includeStartTag) offsetStart = startTag.length;
  if (includeEndTag) offsetEnd = endTag.length;
  
  var indexStart = html.indexOf(startTag) + offsetStart;
  var targetText = html.substring(indexStart);                        // Strip off everything before and including startTag.
  
  var indexEnd = targetText.indexOf(endTag) + offsetEnd;
  indexStart = 0;
  var element = targetText.substring(indexStart, indexEnd).trim();    // Strip off endTag and everything after, and trim whitespace.
  return element;
}
Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

Re: Google sheets - get info for I-bonds

Post by sheetzilla »

Kevin,

The code you posted above works great.
In particular the fact that it passes the error message through into the spreadsheet helps a lot.
I don't think it's worth pursuing getting that whole table, as your code plus a little math gets me all of the values that I need.

Now I just have to figure out why Treasury Direct is giving me an error message

Code: Select all

No data retrieved for parameters Series: I, Denomination: 10000, Issue date: 4/2019, Valuation 
date: . Additional info: Impossible series/denomination/issue date selection. Valid issue dates for 
$10000 Series I Bonds are 05/1999 through 01/2008. (line 62).
for the $10,000 I Bond that I definitely bought in April 2019.
It does this directly on the web site also, so the script is working just fine.

Thanks very much for this, it was really helpful.

-sz
User avatar
Kevin M
Posts: 11698
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets - get info for I-bonds

Post by Kevin M »

sheetzilla wrote: Sat Jun 08, 2019 10:22 pm Kevin,

The code you posted above works great.
In particular the fact that it passes the error message through into the spreadsheet helps a lot.
I don't think it's worth pursuing getting that whole table, as your code plus a little math gets me all of the values that I need.

Glad it works for you!
sheetzilla wrote: Sat Jun 08, 2019 10:22 pmNow I just have to figure out why Treasury Direct is giving me an error message

Code: Select all

No data retrieved for parameters Series: I, Denomination: 10000, Issue date: 4/2019, Valuation 
date: . Additional info: Impossible series/denomination/issue date selection. Valid issue dates for 
$10000 Series I Bonds are 05/1999 through 01/2008. (line 62).
for the $10,000 I Bond that I definitely bought in April 2019.
It does this directly on the web site also, so the script is working just fine.
Remembering that the website tool is for paper I Bonds, apparently paper I Bonds were not issued in $10K denominations after 01/2008. I assume you bought yours electronically through TreasuryDirect.

What I do in my spreadsheet is to do the query for a valid denomination, then scale it by the appropriate factor for my actual I Bond value. For example, I pull values for $5,000 denomination, and multiple by purchaseAmount/5,000.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)
Post Reply