Spreadsheet/GoogleFinance or other automated I-Bond and EE-bondprice calculator?

[fnord123]

So I've been moving the cash portion of the PP for me and my wife into I-Bonds each year up to the treasury limit ($20K between the two of us per year).  I've got a spreadsheet on Google that tracks gold prices (accurate down to the level of individual types of coins, thanks to HTML scraping of Apmex buy prices), Stock prices, and LT bond prices.  I've got our non-I-Bond cash accounts (CDs) setup to correctly calculate the updated daily values.

The one thing I haven't figured out how to automate is calculation of the current value of all of our I-Bonds.  Has anybody done this that can share some tips?

[MediumTex]

Use the Savings Bond Wizard program.

It's free on the treasury's website.

Since the I-bonds only adjust once a month, it's not a big deal (to me anyway) to manually transfer the figure from Savings Bond Wizard to your PP spreadsheet.

[fnord123]

I need something that is an online web page, a formula, or combination of the two.  I don't want to manually transfer prices - I want to just go to my spreadsheet and have it automatically have the updated results

I tried the TreasuryDirect web page savings bond calculator (http://www.treasurydirect.gov/BC/SBCPrice), but it doesn't have a URL I can use with =ImportHTML() or other scraping tools.  Has anybody managed to scrape this page, or find other sources of data online that can be used to automatically calculate individual I- and EE-bond values?

[fnord123]

Ok, I figured out how to use the TreasuryDirect website calculator after all.  Thanks for the pointer MT!

For those who also use Google spreadsheets (or Excel, which has a similar mechanism I believe), here is how you calculate the current value of a $1000 I-Bond issued on 12/2009.  Adjust the 1000 value (appears twice) and the issue date to calculate any other bond. To calculate an EE bond, just replace Series=I with Series=EE.

Code: Select all

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

[mukramesh]

Hi All,

I have been using the code in fnord123's post to track my I-Bonds in Google Spreasheets. This has been working fine, but in the past couple days, it has stopped working and just returns #VALUE (error: The data could not be retrieved. Please check the URL.) Is anyone else having the same issue or is there a known workaround/alternative to automatically track I-Bond prices?

[fnord123]

Looking at it now, thanks for the heads up.

[fnord123]

The URL and cell calculation code is all still correct.  I changed from http:// to https:// and it worked once then stopped working.

It looks like some sort of intermittent problem rather than a problem in the code itself. Perhaps give it a week or two (I suspect the folks who maintain the TreasuryDirect servers and the Google spreadsheets are all on holiday currently).  If it is still broken after a week or two I may take a look at come up with a replacement (unless someone beats me to it  )

[mukramesh]

You're right, it does appear to be intermittent; I just reloaded my spreadsheet and it is working correctly (currently using "http://"). Maybe it's not a huge issue, I've only had it happen during the past few days.

Thanks fnord!

[fnord123]

It is still being flaky

I even started a new spreadsheet entirely to see if it would be more robust. At first it was more robust, but pretty quickly it had an error again too.

Why can't a free solution work perfectly!!! Geeze Google.

[dualstow]

Same deal with spreadsheets that pull the value of treasury bonds and notes. Problem seems not to be with the code.

[mukramesh]

I think there is a problem with Google Spreasheet in general. Both importhtml() and =GOOGLEFINANCE(xxx, "price") calls have been pretty inconsistent for me.

Not sure there's anything we can do about it.