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!
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