Checking EE Bonds with Google Sheets

I had a stack of old and untracked old United States EE Treasury bonds. It was finally time to check them.

After typing in the Serial Number, Issue Date, and Face Value into Google Sheets, I wanted to fetch the rest of the interesting data from the official Treasury site.

It took me a bit to figure out the right spreadsheet formula, so I figured I’d write it here if anybody else was looking.

Manually fill out the data:

=QUERY(ImportHtml("" & ENCODEURL(C2) & "&SerialNumber=" & A2 & "&IssueDate=" & ENCODEURL(B2) & "&btnAdd.x=CALCULATE&OldRedemptionDate=782&ViewPos=1","table",4),"offset 1",0)

I put this in column E, and the values will be:

Then to fix up the total current value column, I had one more I put in R:


This converts the string value with the asterisk and dollar sign over to be a proper number that Google Sheets can do math on.