Blueskies123 wrote: ↑
Sun May 27, 2018 2:30 pm
Kevin, the spreadsheets you are posting are very useful. Can you explain how you do them? I assume you are downloading all the bond data points from Fidelity and then sorting them. Can you give us a few sorting tips or send a link so we can download them?
Glad you find them useful.
I find them particularly useful in quickly identifying what looks good at a particular time when the market is open, more for CDs than for Treasuries, but also to compare them to each other. This also allows me to calculate TEY if I'm buying in a taxable account. When you chart the results, you can quickly identify outliers that are offering higher than average yields (again mostly useful for CDs as opposed to Treasuries).
My last raw download of CDs out to 3-year maturity included about 750 CDs, but then in a separate sheet, I do some additional filtering to screen out the ones that have lower yields, and ended up with only about 35 CDs to chart and take a closer look at. Similarly, for the charts in this post, I start with all the 0-10 year Treasuries, about 330 of them, then in separate sheets filter by maturity, or filter to just look at STRIPS or to filter out STRIPS, for example.
Although you can look at the Fidelity yield summary page without logging on, as far as I can tell, you must log on to your account to do searches. So I can't really send you a link to download them the way I do.
You can also get prices and yields from the WSJ Treasury Quotes page
. You can copy/paste these into a spreadsheet. If you know how to use ImportHTML in Google Sheets, you can import them that way. With this URL in cell B1, http://online.wsj.com/mdc/public/page/2 ... nav_2_3020
, use =importhtml(B1,"table",3). I haven't used this in awhile, since I'm usually interested in prices/yields for bonds I can buy when the market is open, and I'm just leveraging that for what I'm posting here.
If you have a Fidelity account, there is a download button on the Fidelity search results screen. You can sort by any of the columns in the search results screen, and I sort by maturity before downloading, so I don't have to bother doing that in the spreadsheet. It seems that your previous sort choice carries over from one search to the next, so by default they usually are sorted by maturity.
In my search criteria I limit to the maturities of interest. For my daily buying decisions, this is out to 3-year maturity, since yield curves are too flat beyond that to interest me. Obviously for the charts in this thread I'm going out to 10 years.
There are a number of other parameters you can use to restrict your search results, such as minimum yield, coupon rate, etc. I use these, depending on what my purpose is.
The downloaded data gives you everything you need to calculate duration except settlement date, so I enter the appropriate settlement date into a cell and use that in the DURATION function. You also can calculate maturity if you want to check whatever Fidelity shows. Also, for CDs and munis, for which there is a commission, I calculate net yield by adding 0.1 to the ask price, and using the YIELD function.
Hope that helps,