New issues added as they occur and begin trading.
Please NOTE that this will not work with EXCEL only Google Sheets.
- You need a Google Account to use this sheet correctly. Go here to set up an account if you do not have one. While an account gives you gmail and other apps it importantly gives you an account for accessing and using Google Sheets.
- After creating an account with Google make sure you are signed in.
- Go the the sortable spreadsheet which you can find here.
- Once you open the spreadsheet as long as you are logged into your Google account the spreadsheet will automatically be in you spreadsheet list.
- You will see that when you open the spreadsheet it will say ‘view only’.
- Make a copy of the sheet for yourself–go to File, Make a Copy then give it the name you want.
- You will now be able to use own copy to sort etc.
Unless you are very well versed in Google Sheets I would suggest not modifying much in the formulas–BUT if you are like me and just can’t help yourself you can always come back here if you ‘break’ the sheet.
Users who liked this:
Putting this here because I’m not sure of a better place…
Has anyone successfully modified your local copy of this spreadsheet to give “stripped yields”? If so, how have you done it?
I’m thinking it would be quite useful, but I’m not sure how best to do it. One option would be to add another “sheet” that uses the same base information but presents it differently. Another would to add a “stripped price” column that uses an optional “next ex-div” date, but falls back to the full price if not present. And then color code the result differently?
I’m not that worried about implementing the formulas. Mostly I ask because I’m not sure how to present the information under the assumption that I’ll only be keeping the ex-div dates current for a small number of lines. Unless I can figure out a way to rig it to automatically update the ex-div dates from some source?
I’d be interested to hear what others have done, especially if you have a solution you are satisfied with!
I was trying to update my local version of the Google Sheets to show current rates for floating issues, but I couldn’t find a ticker symbol that worked for the SOFR three month average. I figured out a way around this, so I thought I’d post it here in case it helps others:
=IMPORTXML(“https://markets.newyorkfed.org/read?productCode=50&limit=1&format=xml”, “/markets/rates/rate/average90day”)/100
This downloads the current SOFR rates from the New York Fed and extracts just the 3 month one. I put it on the “Lists” sheet, then gave it a “Named Range” of SOFR3M so I could use more easily in a formula. The other one that comes up for resets is the 5 year treasury, but that one’s easier because it has a ticker:
=GoogleFinance(“FVX”)/1000
Hope this helps someone!
Nathan the 3mo Term SOFR used for resets is published by the CME here https://www.cmegroup.com/market-data/cme-group-benchmark-administration/term-sofr.html If you can figure out how to get the daily data into a google sheet that would be awesome. The New York Fed data you are using is not what’s used for the reset rates.
Am I the only person in the world to get so frustrated trying to log in CMEGroup to give up on it? I’ve taken to using https://www.barchart.com/stocks/quotes/SOFERMM3.RT/interactive-chart
2whiteroses I installed the duo app on my phone and presto it gives me a code. I think it lasts like 90 days for the login.
It’s purely personal I suppose on my part but first off, I’m not going to install an app anywhere for the sole purpose of satisfying a single website’s log-in requirements and secondly I refuse to have my lived life on my phone.. I know, troglodyte mentality..
2wr and mbg – rookie question regarding this site, how do you know if someone replied to a post you made? I love the information on this site but I haven’t quite figured out how to even be able to read the newest posts, especially if they’re in a seldom used topic folder like this one. How do you make your way around the site and keep up on things? Thanks!
Trying to re-find previous posts has always been a problem on here IMHO, however, that being said, there’s recently been some good discussion about the use of and abilty to tailor RSS feed reader feedbro to try to keep up on all incoming posts, not just posts to anything you personally have written….with some exceptions, it’s an app that you can find in your browser…..
2wr thanks. I’ve been using innoreader for awhile in an attempt keep up on things
2wr,
Using pinky’s link, I don’t even have to log in. I just scroll down and there are the rates. I press the “Page Down” key on my keyboard 10 times to get there.
Date Term SOFR (%)
1M 3M 6M 1Y
2025-Feb-27 4.32268 4.31851 4.26176 4.13491
2025-Feb-26 4.32384 4.31324 4.25155 4.12001
2025-Feb-25 4.32352 4.31852 4.27042 4.17208
2025-Feb-24 4.32359 4.31804 4.27516 4.19186
2025-Feb-21 4.31930 4.32153 4.29076 4.22342
Not for me it doesn’t….. When I scroll down I’m met with a “you have to login to gain premium access and current rates” kind of message. On top of that I had forgotten the other big pain I also have with the site – I use Firefox as my default browser and cmegroup.com completely freezes FF up when I try to open it. In fact I had to use TaskManager to shut FF down and reopen it just to write this post….
Sorry to hear that, 2wr.
2wr ugh that IS a pain…
The bar chart link provides the rates that are close enough for sure. Plus easy access to historical rates. Thanks for that link!
I agree that this is the wrong SOFR. There was a thread about it sometime after I posted this. As you say, we need the “3M Term SOFR” (forward looking) and not the “90D Average SOFR” (backward looking). For a while I had a source that was working using a similar XML import, but then it broke. Since then I’ve just been updating the number manually.
Unfortunately, I’m haven’t been able to figure out how to use the CME Group page or the Barcharts page automatically. Both are doing some Javascript foolery to display the current rates, and the XML import function to Google Sheets can only take things straight from the HTML source.
But your message did inspire me to search again to see if I can find a plain HTML page that I can use, and at least for now, I found one. I think this formula should work in Google Sheets:
=VALUE(SUBSTITUTE(IMPORTXML(“https://www.global-rates.com/en/interest-rates/cme-term-sofr/2/term-sofr-interest-3-months/”, “//table[1]/tbody/tr[1]/td[2]”), ” “, “”))
Summarizing the formula, we grab the page at https://www.global-rates.com/en/interest-rates/cme-term-sofr/2/term-sofr-interest-3-months/, find the first table, then look at the second table data in the first table row. Then we strip out the extra space between the number and the percent sign using SUBSTITUTE. Then we convert it to a percentage with VALUE.
EDIT: Looks like the automatic ‘smartquotes’ feature in this forum breaks the formula for direct use. You’ll have to change all the quotes back to standard after you paste it in. Sorry!
I’ve worked as a coder for decades and have made some of my own preferred sheets with some different data sources. Tim’s are better overall. I could write code to generate sheets that are fully sortable and cover most or all of what is commented on here. The code could run on an interval (perhaps every few minutes) and that would make them available offline and faster if values were precalculated. We just would need access to good data sources rather than programmatically scraping websites.
Another version could be made that would require Internet and be able to generate more real time numbers. If there are other values and calculations that people are taking into account and think are important, we could add these. Much is possible, the output format is rather simple and I’ve already experimented/researched quite a bit on working with this type of data.
Tim’s sheets could probably even be programmatically downloaded, altered and then reshared to provide additional functionality.
Let me know what kind of interest is out there and if it’s worth putting a project together.
I recall reading that certain brokers might offer an API to very fresh data but I am unclear if you need an account minimum or a monthly fee. What has always stopped me from doing this with just a spreadsheet/any programming language for example is often the more obscure preferred/tickers do not work. Kind of annoying to only get 90% of my interest completed. Most of the downfall of this whole idea is the data feed and it’s reliability over the years.
Hi Tim,
I’ve been making various changes and improvements to my local copy of this spreadsheet, and I wonder if it’s worth trying to share them with others. I’ve added stuff like sorting by Change and Ticker, highlighting Current Yields when they are callable, and adding a Yield to Maturity column for Term issues.
There are still things I’m trying to figure out how to do reasonably, like adding the ability to “star” certain issues of interest, but things are mostly going smoothly. I’m still a beginner with regard to Google Sheets, but I’m a well-versed computer programmer capable of reading documentation and figuring things out. If there are other changes you’d like made to this, I’m happy to take a stab.
Presumably you’ve got my email via the comment submission if you want to discuss further. Or if others have suggestions, feel free to add in comments here.
Nathan, one function I found handy and recently discovered is
=GoogleFinance(A10, “name”) Where A10 is any cell that contains the ticket symbol.
Thanks for the wonderful resource! The directions were clear, and my copy now works well. Not sure if this is the right place to mention it, but I have a small correction to the sheet. Despite being Baby Bonds, I think BIPI, BEPI, and BEPH should be marked “Yes” for QDI. This made me wonder if there might be other Baby Bonds that give Qualified Dividends. Does anyone know of others?
Tim ; I can pull up the spread sheet fine , but i can’t get it to sort when I click on one of the headings i.e. Yield ? any suggestions please
ted–look on the bottom of the sheet where it says ‘filter alpha’ – click that and then you will see sorting options at the top.
actually,
if i just add the spreadsheet in any browser i’m able to sort the columns just by clicking on the header. no google account necessary…
thanks a lot for the sheet though!
What’s up with the Sortable Sheet? Used to just download. Now shows as needing to request access? I requested yesterday, no access yet. I’m OK waiting if that’s how it’s supposed to work, or is this something broke/not working right? Thanks for making the sheet available and updating.
TAP-TAP-TAP…. is this mike on? TAP-TAP-TAP
See #3 line in instructions above. I clicked on it and the spreadsheet simply opened. * You need to be signed into your GOOGLE account. Hope this helps.
Must have been fixed as I was clicking on #3, had a pop-up to request access. Now opens the spreadsheet. Thanks
Not sure the correct place to post this item.
On the page entitled “Floating Rate and Fixed-to-Floating Rate Preferred Stocks”, I was looking at AGNCO. It lists the spread over 3mL as 4.332%. In fact, the spread over 3mL is 4.993%. (I believe the reason for the typo is that AGNCM has a spread of 4.332%, which is listed correctly on the same page).
Thanks
While I’m on nitpicks, I see the potential coupon for NLY-F listed as 8.633%. I’m confident that the dividend determination date is 9/29 at 11am London time. When I look at the history of libor pricing on web pages, I see 3.74286% as the 3mL rate on 9/29. That would suggest a coupon of 8.735%. I think 8.633% is based upon the 9/28 3mL rate. But maybe the popular web based rates shift the date because of the time difference? Just wondering which is correct? Thanks
Hi Tim, May have missed it though not seeing GDV-H on the CEF sheet. Thank you!
Hi Tim, Brightsphere Investment, ticker BSA, should be removed from sheet.
AGNC’s newest preferred should be added. Just tagging along to Mr. C’s comment.
FWIW, AFSIN listed twice in the spreadsheet with differing data and missing AFSIA and AFSIM.
Thanks Theo
Tim,
Just a reminder of calls, effective today/tomorrow of securities on the master list: DRUA, SAF, UZA, TDI.
Thanks!
Got it A4I–Thanks
Tim,
Would you like a list of 40+ big board symbols that are not in the list?
Hello,
great site, thanks for sharing this valuable resource.
The ex-Dividend Calendar file link seems to no longer work.
https://docs.google.com/spreadsheets/d/1aCoOrYNMlNr2z8uMhMn–ENKyQcYA8HZZzuzO-w6TWw/edit#gid=278372155
Is it still available?
Thank you