Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

I have been sharing a Google Sheets spreadsheet that tracks the SEC Yield history for the 4 major Vanguard Money Markets (VMFXX, VMRXX, VMSXX, and VUSXX) and from that data, the sheet produces a set of charts and tables that attempt to analyze which fund is best for an individual based upon a back-test of the past year (or less). Imperfect, I know, but predictions are hard, especially about the future!

The Vanguard sheet has been expanded to include a tab for analyzing Fidelity funds, plus a high level set of queries for any US money market fund supported by the site: https://moneymarket.fun

I have also released a sheet that follows the model of the original one, but allows analysis of all the funds supported by https://moneymarket.fun. See below for current releases of both sheets.

I was asked to start a thread to track the maturation of this sheet, accept ideas, bug fixes, etc. This is that thread.

This thread would be best served to focus on the tool and not the findings / personal situations. Feel free to start threads about your situation using the tool. I would like to keep this thread focused on the tool itself.

The tool is published as a Google Sheet shared with view/commenting only. You will need to make personal copies for local updates/customizations.

Caveat: The Optimizer tool is native to Google Sheets, it is very unlikely you will get anything useful out of it after converting the spreadsheet to Excel or any other spreadsheet platform. This was a conscious decision as Google Sheets "live" features are a very productive platform for this type of solution.

All releases are announced in this thread. The current release of tool can be found here: Future major releases will be added to this page. Major releases will be new and separate instances of the Google Sheet. These are "live" worksheets and update daily with the most recent yield data for the funds and T-bills.

If I find minor bugs, I may update the sheet and add a minor release number such as 5.01 without releasing a new file.

How do I use this tool?

TLDR; go to the My Parameters tab, and enter your personal tax state of residence, Federal marginal tax rate, and State marginal tax rate, then browse over the Vg BestNow tab and see what funds yield best for you.

The Instructions tab provides detailed information about how to fully use the features of the tool.

Harry Sit at the Finance Buff has a nice article about how to choose a Vanguard Money Market Fund and he demonstrates the key use of this spreadsheet - Which Vanguard Money Market Fund Is the Best at Your Tax Rates

The major features of the sheet are as follows, listed by tab in the worksheet:

My Parameters - a single location for all normal user configurable parameters to be adjusted. Please make a personal copy of the sheet to adjust to your needs.

Vg Best Now - A tab that attempts to summarize a current date snapshot of the best Vanguard funds for your particular situation as well as a quick table for a broad range of Federal and State tax rates.

Fidelity Best Now - A tab that attempts to summarize a current date snapshot of the best Fidelity funds for your particular situation as well as a quick table for a broad range of Federal and State tax rates.

Optimize Study - this page does a back-test of a swapping regime among the 4 funds across a range of tax rates and calculations the value such a scheme would have returned if executed over the previous time period. The study attempts to create a return per swap action over the time period in the column $ per swap. Note all key calculations are done on an After-Tax basis. This is the chart that answers the question: is it worth it to swap between Treasury and Muni MM funds? The answer is two-fold: what is your time worth, and how high are your marginal state tax rates?

Tbill Returns - This tab attempts to simulate a set of Money Market replacement T-bill ladders/roll schemes and compare them against the MM funds. The methodology is documented at the bottom of the page.

Funds Watch - A one page watch list of whatever money market funds included https://moneymarket.fun dataset. Simply put the ticker name in one of the yellow cells. I have pre-populated with a popular list of funds, but you can add/delete as you see fit. Just like the My BestNow tab, the fund's After-tax yield and Tax-equivalent yield values are provided for fair comparisons.

Funds Filter Sort - Allows you to filter and analyze over 700 MM funds by categories: Prime, Government, Muni, In-State Muni, and Treasury and further filter on the various data elements such as $ Assets, Expense Ratio, Fund Name, etc.

There are many additional charts in the sheet, feel free to investigate. I believe they are self-documenting, but questions are welcome.
Last edited by retiringwhen on Thu May 23, 2024 9:22 pm, edited 39 times in total.
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

Thanks @retiringwhen! As mentioned in another thread, this is a fine example of good spreadsheet engineering. :beer

On sheet "My T-bill Rolls/Ladders TellTale" in the chart title you say "vs. VUSXX", but perhaps you might make it more explicit as you have done in some of the other charts by having the chart title say, "After-tax growth telltale with VUSXX as baseline for simulated T-bill roll or Ladder (returns relative to VUSXX)."

On the instructions sheet, change "These use the same tax rates as defined on the My BestNow tab." to "These use the same tax rates as defined on the My Parameters tab." Also update the instructions in the section "How to update data and dates for the sheets." You refer to the My Best Now and Optimize Study sheets but everything is now on the "My Parameters" sheet. On sheet My Best Now, update the "How to use this sheet" section to refer to My Parameters sheet instead of updating cells A1:A3.
Last edited by scoothome on Tue Apr 04, 2023 10:05 pm, edited 1 time in total.
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

Suggestion: rather than having separate charts for several tranches (Chart A, Chart C, Chart E, Chart F, Chart G, Chart H, Chart J), why not just have one sheet, Chart X, and give the user the ability to select which tranche to plot and have the plot update accordingly? Alternatively, have one sheet that plots 4 or 7 charts on one sheet. It's important to have My Rate Chart as a separate full-size plot, but I don't think most users will want to spend much time on each of the tranches as they are currently presented and you can reduce some sheet clutter.
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

Suggestion: simplify the legend labels for the charts. For example, on sheet "My Growth Telltale" you have "VMFXX After-Tax Growth", "VMSXX After-Tax Growth", etc. However, the chart title already says "After-Tax Growth Telltale...". Shorten the legend labels to be just the tickers without the After-Tax Growth text. Similarly for the "My T-bill Rolls/Ladders TellTale", don't need the text "After-Tax telltale" in the legend label because it's already in the Chart title. Same comment for several of the other charts. BTW, change the sheet name for the My T-bill sheet to show "Telltale" instead of "TellTale" to be consistent with the other sheets.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Thanks for the eagle eye! I will look at those and incorporate them into the next version. I had gotten a good idea via PM to add a daily email status reminder feature. I have already done this in Google Sheets for another application, so I am almost done with the implementation. A couple days of testing and I think we can release that one. I a guess I am lower in the cost of swapping to a bit less regular effort!
User avatar
mangorunner
Posts: 162
Joined: Mon Jun 21, 2021 6:24 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mangorunner »

@retiringwhen, thanks for your hard work and sharing this spreadsheet. I'm very eager to play with it!

I'm getting the following error message after I downloaded an Excel copy and tried to open it in Excel 2019 (version 2302). Any ideas? Should I click "Yes"?

Image
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Sorry, but the spread sheet is a very native Google Sheets app and cannot be used in any practical way in Excel. The tool depends upon a set of Google Apps Script functions to retrieve current mutual fund data from vanguard.com plus the US Treasury.

Please try it just using it as a Google Sheet in the browser.

I am a 30+ year user of Excel, but the vastly superior script development environment in Google Sheets has made me a believer. This tool would not even be minimally conceivable in Excel.

I still use Excel for a large set of more static data sources, but I have fallen in love with the "live" nature of what you can really do with Google Sheets. I know you can do some of those things in Excel, but it is soooo much easier and consistent in Sheets.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

BTW, if you do download an Excel file, it will give you a current snapshot of the data in pretty reasonable form, but it cannot be updated via any of the "live" features. Consider it a snapshot and it is okay. Go ahead and say Yes, nothing bad will happen.
Last edited by retiringwhen on Thu Apr 06, 2023 9:23 pm, edited 1 time in total.
User avatar
mangorunner
Posts: 162
Joined: Mon Jun 21, 2021 6:24 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mangorunner »

Thanks for the reply and the insight. I will use it in Google Sheets, as designed. Thanks again! :sharebeer
dchobo
Posts: 7
Joined: Wed Mar 01, 2023 2:46 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by dchobo »

@retiringwhen

Thanks for the great spreadsheet!

As I started to look at MMF vs Munis vs T-Bills, especially for high tax rates, your spreadsheet definitely provided me a level of details that no forum thread can easily answer.

Now, I am curious whether your spreadsheet can be extended to include Fidelity and Schwab funds?

I understand you are pulling "live" data from vanguard.com. Could it also be adapted to pull data from Fidelity or Schwab?

Thanks
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

dchobo wrote: Thu Apr 06, 2023 11:05 pm Now, I am curious whether your spreadsheet can be extended to include Fidelity and Schwab funds?

I understand you are pulling "live" data from vanguard.com. Could it also be adapted to pull data from Fidelity or Schwab?
I have been looking at it. I would most likely implement Fidelity first as I am an investor there too. Unfortunately, their website is not well suited for the style of scraping done in this sheet for Vanguard. I have been working a longer term project in the background to lay the foundation for a general approach to scraping their site, banks, and eventually Vanguard when they retire their old site endpoints. That is taking a while, but happening between real life.

I would offer if there is anyone familiar with Google Apps Script, Puppeteer, or the Google API library, I may be able to get help to divide and conquer. Part of the problem is a reliable (and cheap) design that will scale for many users accessing the data daily. I am not completely done with a prototype to prove out my ideas. The first couple of tries turned out to be very slow or expensive (as in $) due to the use of Google Cloud Functions.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Version 7 has been released: https://docs.google.com/spreadsheets/d/ ... sp=sharing

Key new features:
  • Added new email notification feature. You can configure to get a daily email that reports on whether or not you should swap based upon the currently reported rates at vanguard.com - See My Parameters tab to configure.
  • Changed the name Tranch to RateSet to better reflect the meaning of the data. RateSets are a pairing of a tax state, federal marginal tax rate and state marginal tax rate with the yield history for a set of funds.
  • Better, more clear (I hope) instructions, plus increased consistency of references to various data series among the tabs.
  • Consolidated all the RateSet graphs from the Optimize Study tab into one single sheet. This is less jumping around, plus it allows the reader to visually compare how tax rates impact their return. It is proof on one page that your personal tax rate matters!
  • Added detailed documentation in Instructions tab on how to approve Apps Script Authorization. This is critical to allow the email feature to work. Google security model is good, and requires explicit permission from the owner of the sheet (you when you make a copy) to allow the script to execute and send email.
  • Updated the Treasury Funds tab to reflect data collected as of 4/5/2023
  • updated the USGO% for VUSXX to 70% based upon the 3/31/23 report by Vanguard. I plan to add a tab that tracks the evolution of that parameter during the year.
  • Added AZ to the list of States in the State Parameters tab. Thanks to a user submitted update!
Thanks to @scoothome for some excellent feedback that I believe improved the sheet's usability a lot and helped me clarify the documentation.

Also thanks to an anonymous BH poster who emailed with a tickle to provide the email feature, he also submitted the AZ rates entry. I had actually implemented it on other sheets in the past, but hadn't thought of doing it here :oops:
Last edited by retiringwhen on Fri Apr 07, 2023 8:02 am, edited 2 times in total.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

A note on code, security and permissions. This sheet uses Google Apps Script to implement much of the functionality. These scripts are the root cause of the need to approve permissions for execution. This is similar to the old Excel Macros problem and potentially a risk vector.

You do not have to give permissions to view the sheets, but some of the updates and the email feature will definitely not work.

If you are skeptical, all of the code is visible and documented for the reader. It is accessible via the App Script editor that you can reach via the menu item Extensions -> Apps Script

Besides the email function (in emailTrigger.gs), the code mostly does http requests for retrieving data from vanguard.com and treasury.gov. The rest of the code is just book keeping, caching and massaging of that data for use in the spread sheet.

I am game for peer review input on the code. Much of this started off a sheet shared by @KevinM a couple years ago, but I have massively customized the code and extended over time.The big thing I added is caching to improve performance on repeated use of the data. I also found a prototype of the Treasury access code somewhere on the web that was posted by a long dormant BH poster a couple years ago. All bugs are mine.
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

Huge usability improvement in v7, kudos!
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

retiringwhen wrote: Fri Apr 07, 2023 7:25 am Version 7 has been released: https://docs.google.com/spreadsheets/d/ ... sp=sharing

Key new features:
  • Changed the name Tranch to RateSet to better reflect the meaning of the data. RateSets are a pairing of a tax state, federal marginal tax rate and state marginal tax rate with the yield history for a set of funds.
This reminded me to bring up, for at least the VA rate set, as a single vs MFJ, these don't quite seem right. I've been tweaking every new version to this instead as it aligns better to a single with the VA and Federal brackets.

But I don't know how much this really matters to anyone [shrug]

Code: Select all

$1.3M
5.75%
40.80%
$800K
5.75%
40.80%
$550K
5.75%
38.80%
$230K
5.75%
35.80%
$200K
5.75%
32.00%
$180K
5.75%
24.00%
$95K
5.75%
22.00%
$17K
5.00%
12.00%
$5k
3.00%
10.00%
$3K
2.00%
10.00%
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

mouth wrote: Fri Apr 07, 2023 11:24 am This reminded me to bring up, for at least the VA rate set, as a single vs MFJ, these don't quite seem right. I've been tweaking every new version to this instead as it aligns better to a single with the VA and Federal brackets.

But I don't know how much this really matters to anyone [shrug]
It is kind of tough to have one table that covers both Single and MFJ rates. I don't even cover all the state permutations for some situations either.

That is almost a project in itself. My goal with the rate table is to provide a range out outcomes to see the big picture, but allow the user to put their personal situation in the other tab. Guess I would just allow for different columns for the same state like Single, MFJ, MFS, etc. I'll have to think about that.
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

Certainly don't want to make more work for you; you've done so much already.

I'm pretty sure I deciphered it myself but that took a moment, and I was guessing. In particular I wasn't 100% sure at first if I should change the notional line and what I might exactly want to change it to. Obviously I did though.

Maybe just a little more description for the theory of operation of those values to make it easier for others to understand how / why to make changes. And maybe take mine for VA, label it as VA-Single, and use it as an example for other's to follow if they feel the need. Maybe label the current VA as VA-MFJ as well for clarity. I didn't validate the current VA values as being wholly appropriate for MFJ though.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

mouth wrote: Fri Apr 07, 2023 2:42 pm Certainly don't want to make more work for you; you've done so much already.

I'm pretty sure I deciphered it myself but that took a moment, and I was guessing. In particular I wasn't 100% sure at first if I should change the notional line and what I might exactly want to change it to. Obviously I did though.

Maybe just a little more description for the theory of operation of those values to make it easier for others to understand how / why to make changes. And maybe take mine for VA, label it as VA-Single, and use it as an example for other's to follow if they feel the need. Maybe label the current VA as VA-MFJ as well for clarity. I didn't validate the current VA values as being wholly appropriate for MFJ though.
We are thinking alike. I will put in VA as a prototype for next version and add some detail in the instructions tab on how the data is used.
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

retiringwhen wrote: Fri Apr 07, 2023 2:44 pm
mouth wrote: Fri Apr 07, 2023 2:42 pm Certainly don't want to make more work for you; you've done so much already.

I'm pretty sure I deciphered it myself but that took a moment, and I was guessing. In particular I wasn't 100% sure at first if I should change the notional line and what I might exactly want to change it to. Obviously I did though.

Maybe just a little more description for the theory of operation of those values to make it easier for others to understand how / why to make changes. And maybe take mine for VA, label it as VA-Single, and use it as an example for other's to follow if they feel the need. Maybe label the current VA as VA-MFJ as well for clarity. I didn't validate the current VA values as being wholly appropriate for MFJ though.
We are thinking alike. I will put in VA as a prototype for next version and add some detail in the instructions tab on how the data is used.
okay I just had a crazy idea that you are of course free to scoff at. What about creating the RateSets programmatically? You already know all of the federal brackets. You could create a state bracket/rate input table for the user to fill out (with a hard coded example next to it) and then calculate the user's RateSet from that. You could then get rid of the full table except for maybe the general case (or that's the hard coded example). You could even move it to the Parameters tab as well.

I know, I LOVE it when my clients get hit by the Good Idea Fairy too.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

mouth wrote: Fri Apr 07, 2023 3:22 pm okay I just had a crazy idea that you are of course free to scoff at. What about creating the RateSets programmatically? You already know all of the federal brackets. You could create a state bracket/rate input table for the user to fill out (with a hard coded example next to it) and then calculate the user's RateSet from that. You could then get rid of the full table except for maybe the general case (or that's the hard coded example). You could even move it to the Parameters tab as well.

I know, I LOVE it when my clients get hit by the Good Idea Fairy too.
I may be able to do that, but not sure the value beyond setting a single rate pair for the My BestNow on My Parameters. I'll think about it!
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

retiringwhen wrote: Fri Apr 07, 2023 3:31 pm
mouth wrote: Fri Apr 07, 2023 3:22 pm okay I just had a crazy idea that you are of course free to scoff at. What about creating the RateSets programmatically? You already know all of the federal brackets. You could create a state bracket/rate input table for the user to fill out (with a hard coded example next to it) and then calculate the user's RateSet from that. You could then get rid of the full table except for maybe the general case (or that's the hard coded example). You could even move it to the Parameters tab as well.

I know, I LOVE it when my clients get hit by the Good Idea Fairy too.
I may be able to do that, but not sure the value beyond setting a single rate pair for the My BestNow on My Parameters. I'll think about it!
Yeah the only value add I could think of is it allows easy customization for all states and all filing types without you having to come up with all the combinations yourself or having to keep them updated. That's on the user now. So long as you make the fed rates and brackets editable you technically don't even have to do that; though I know you'd end up doing that for your own use.

I mean yes you sort of have that now but it's sort of hit or miss if a user understands how to make the edits for their own state.
dchobo
Posts: 7
Joined: Wed Mar 01, 2023 2:46 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by dchobo »

@retiringwhen -

Thanks for v7 update and the incredible work! Also, good to hear you are looking into extracting the yield rates from Fidelity website.

Another suggestion - on the tab "T-bill Roll Returns", should there be corresponding "annualized" yields & returns? I was playing with the back-test days on the T-bills for something like 30 days or 90 days. The yields calculated are for the specific period, but I think it is easier to think of yields as "annualized". For example, I did something quick like this for 90 days:

Image

... where I simply scale the yields by 365/(number of backtest days). Strictly speaking, the denominator should be the exact number of days you used to calculate the yield in your spreadsheet.

Do you think that will be valuable/useful?

Thanks!
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

@dchobo

Thanks for the feedback, that is something I noticed as well. It may apply across more tabs as well. I will look into it.
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

@retiringwhen,

Harry Sit, author of "The Finance Buff", has written about MM Optimizer! I added a comment on his post that links to this thread.
https://thefinancebuff.com/best-vanguar ... rates.html
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

scoothome wrote: Fri Apr 07, 2023 10:22 pm @retiringwhen,

Harry Sit, author of "The Finance Buff", has written about MM Optimizer! I added a comment on his post that links to this thread.
https://thefinancebuff.com/best-vanguar ... rates.html
Sweet! Good thing we didn't mess up the screen shots too much that he pulled from version 6 ;-)
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Version 8 has been released: MM Optimizer v8

This is mostly backend cleanup and some further refinements on the user experience. Not critical that you update. I am hoping this will be the last update for a while as I want to start working the back-end for other fund complexes.

Key new Features include:
  • Added the USGO% tab
  • Combined all the My * graphs into one tab for hopefully better overview and navigation
  • Added NJ (Single) and VA (Single) as a template for other states where Married File Jointly rates don't make sense for Single filers
  • Code cleanup on vg.Summaries.gs (code cleanup is complete)
  • Added annualized returns to the Tbill Ladders Analysis
  • I also re-arranged the tabs with My Best Now being closest because that is where most people go to first
  • Other minor edits and cleanup of documentation
scoothome
Posts: 124
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by scoothome »

MM Optimizer just keeps getting better and better! Good call on consolidating charts to My Charts. My completely arbitrary rule of thumb when it comes to spreadsheets is that I shouldn't have to scroll to see all of the sheets (yes, I know it depends on screen resolution, sheet name length, etc.). You are almost there, ha ha! :) I was going to suggest today that you move My Best Now towards the front as the most used sheet, and lo and behold you had done it already. :beer
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

scoothome wrote: Sat Apr 08, 2023 8:50 am MM Optimizer just keeps getting better and better! Good call on consolidating charts to My Charts. My completely arbitrary rule of thumb when it comes to spreadsheets is that I shouldn't have to scroll to see all of the sheets (yes, I know it depends on screen resolution, sheet name length, etc.). You are almost there, ha ha! :) I was going to suggest today that you move My Best Now towards the front as the most used sheet, and lo and behold you had done it already. :beer
You are training me well!
CityMillennial1
Posts: 30
Joined: Sun Feb 09, 2020 4:28 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by CityMillennial1 »

Very nice spreadsheet!!!

One suggestion is to make all the colors consistent in all of the graphs. It will make them much easier to read.

I also don't like the light blue vs other light blue. Those colors are too similar to easily distinguish. I suggest looking at a color panel for people who are color blind and pick colors that are easy to tell apart.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

CityMillennial1 wrote: Mon Apr 10, 2023 8:17 pm Very nice spreadsheet!!!

One suggestion is to make all the colors consistent in all of the graphs. It will make them much easier to read.

I also don't like the light blue vs other light blue. Those colors are too similar to easily distinguish. I suggest looking at a color panel for people who are color blind and pick colors that are easy to tell apart.
Good point. I will work on that. The colors were the same originally, but some of the reworking of the charts probably messed them up. I will get a palette that is consistent so that VUSXX is always the same, etc.

Edit: An easy fix for you in the meantime is to choose the option Format->Theme. and then choose the Standard Theme. I believe it has the best high contrast palette by default. It does change the font to Arial, but you can leave if you like or revert to Roboto, the font I have been using.
dchobo
Posts: 7
Joined: Wed Mar 01, 2023 2:46 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by dchobo »

retiringwhen wrote: Sat Apr 08, 2023 8:06 am
  • Added annualized returns to the Tbill Ladders Analysis
@retiringwhen
Thanks for adding the annualized returns to the "T-bill Roll Returns" tab!

However, I noticed one minor issue with regards to the annualized yields calculation: due to the "weekend effect", the actual number of days used in the "Backtest dates" may not correspond to the "Back-test Days" entered in "My Parameters".

For example, if I enter: Back-test End Date = 4/6/2023 and Back-test Days = 90, the "T-bill Roll Returns" tab shows the "Backtest Dates:" as 1/9/2023 to 4/6/2023. But this range corresponds to only 88 days, not 90 days. So the annualized yields should have been calculated with a factor of 365/88, not 365/90.

The effect is minimal, though, since the purpose of the chart is to compare various T-bills rolls/ladders with MMF. The relative results are not affected.

Thanks
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

dchobo wrote: Wed Apr 12, 2023 2:22 am However, I noticed one minor issue with regards to the annualized yields calculation: due to the "weekend effect", the actual number of days used in the "Backtest dates" may not correspond to the "Back-test Days" entered in "My Parameters".
Got it, i will fix in the next version. I will calculate it from the actual dates retrieved for the funds and t-bills, not the requested dates.
Scrooge McDuck
Posts: 36
Joined: Mon Apr 10, 2017 11:49 am
Contact:

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by Scrooge McDuck »

retiringwhen wrote: Fri Apr 07, 2023 7:03 am
dchobo wrote: Thu Apr 06, 2023 11:05 pm Now, I am curious whether your spreadsheet can be extended to include Fidelity and Schwab funds?

I understand you are pulling "live" data from vanguard.com. Could it also be adapted to pull data from Fidelity or Schwab?
I have been looking at it. I would most likely implement Fidelity first as I am an investor there too. Unfortunately, their website is not well suited for the style of scraping done in this sheet for Vanguard. I have been working a longer term project in the background to lay the foundation for a general approach to scraping their site, banks, and eventually Vanguard when they retire their old site endpoints. That is taking a while, but happening between real life.

I would offer if there is anyone familiar with Google Apps Script, Puppeteer, or the Google API library, I may be able to get help to divide and conquer. Part of the problem is a reliable (and cheap) design that will scale for many users accessing the data daily. I am not completely done with a prototype to prove out my ideas. The first couple of tries turned out to be very slow or expensive (as in $) due to the use of Google Cloud Functions.
I'm scraping the Fidelity and Schwab sites (as well as Vanguard) for a similar project (fundyields.com) so it is possible. It's ugly and fragile code which would break if Fidelity or Schwab update their sites, but such is the nature of scraping.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Scrooge McDuck wrote: Wed Apr 12, 2023 12:47 pm I'm scraping the Fidelity and Schwab sites (as well as Vanguard) for a similar project (fundyields.com) so it is possible. It's ugly and fragile code which would break if Fidelity or Schwab update their sites, but such is the nature of scraping.
Let's talk, I am happy to not reinvent the wheel. In fact, I am using https://moneymarket.fun/data/fundYields.json from this thread created by @adamhg to pull very recent yield reporting data for Money Markets.

I have a scheme for building Google Apps Script libraries to implement this. I am hoping with release 9 to have a pretty solid base to extend. Implementing the moneymarket.fun scrape ingestion has brought some discipline to my design. :beer
tj
Posts: 10000
Joined: Wed Dec 23, 2009 11:10 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by tj »

Saw this linked on the TFB posts about Vanguard and Fidelity money market funds. Super cool!
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

I want to say, I am getting lots of help on the scraping side. Bogleheads is a great open source development community! :sharebeer
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

retiringwhen wrote: Thu Apr 13, 2023 7:51 am I want to say, I am getting lots of help on the scraping side. Bogleheads is a great open source development community! :sharebeer
It's almost like we might have a few (or more) very highly qualified (and compensated) developer types in our midst :D
bondsr4me
Posts: 2513
Joined: Fri Oct 18, 2013 7:08 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by bondsr4me »

ok...this is a question that will most likely make you either laugh or sneer!!

here goes: do I have to log-in to gmail to use this?

I admit, I am hugely sceptical of Google and giving them ANY of my personal information.

ok...fire away!!

thanks.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

bondsr4me wrote: Thu Apr 13, 2023 8:10 am do I have to log-in to gmail to use this?

I admit, I am hugely sceptical of Google and giving them ANY of my personal information.
Yes, you need to have a google account to make a local copy of the spreadsheet. You should be able to look at it without one, but you cannot configure any personal parameters. As I stated earlier, it is the cost of using the tool.

I have no illusions about what Google collects about me. I am amazed how much google search customizes my search query based on my recent activity. It knows what I am looking for faster than I can type it. I have made the conscious decision to accept the trade-off. Google Sheets is an amazing development platform!
bondsr4me
Posts: 2513
Joined: Fri Oct 18, 2013 7:08 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by bondsr4me »

retiringwhen wrote: Thu Apr 13, 2023 8:22 am
bondsr4me wrote: Thu Apr 13, 2023 8:10 am do I have to log-in to gmail to use this?

I admit, I am hugely sceptical of Google and giving them ANY of my personal information.
Yes, you need to have a google account to make a local copy of the spreadsheet. You should be able to look at it without one, but you cannot configure any personal parameters. As I stated earlier, it is the cost of using the tool.

I have no illusions about what Google collects about me. I am amazed how much google search customizes my search query based on my recent activity. It knows what I am looking for faster than I can type it. I have made the conscious decision to accept the trade-off. Google Sheets is an amazing development platform!
Thank You for prompt reply.
I logged into my Gmail account and have created the file.
I have worked on Excel spreadsheets most of working career (I started off with using Lotus 1-2-3 many moons ago!!)
I would guess there will be a learning curve with Google Sheets!k

Again...Thank You.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

I started using Visicalc and some early cheap version of an All-in-one called something like Framework on DOS :shock: My first use of Excel was version 1.0 for the Mac! I gave up Excel slowly, but once I understood how powerful the Apps Script environment, I moved quickly. I still use Excel for stuff that has to live static for a long time (like my tax records).

I was a VBA jockey for many years.
mouth
Posts: 676
Joined: Sun Apr 19, 2015 6:40 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by mouth »

retiringwhen wrote: Thu Apr 13, 2023 8:49 am I started using Visicalc and some early cheap version of an All-in-one called something like Framework on DOS :shock: My first use of Excel was version 1.0 for the Mac! I gave up Excel slowly, but once I understood how powerful the Apps Script environment, I moved quickly. I still use Excel for stuff that has to live static for a long time (like my tax records).

I was a VBA jockey for many years.
oh wow you all are bringing back memories of putting 5.25" floppies into an Apple IIe to load up VisiCalc into the RamDisk!!!
bondsr4me
Posts: 2513
Joined: Fri Oct 18, 2013 7:08 am

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by bondsr4me »

retiringwhen wrote: Thu Apr 13, 2023 8:49 am I started using Visicalc and some early cheap version of an All-in-one called something like Framework on DOS :shock: My first use of Excel was version 1.0 for the Mac! I gave up Excel slowly, but once I understood how powerful the Apps Script environment, I moved quickly. I still use Excel for stuff that has to live static for a long time (like my tax records).

I was a VBA jockey for many years.
:happy

I've heard of Visicalc but have no idea about it :shock:

Thanks for all your hard work on this....I'm sure it's required a boatload of time! (Vanguard ship pun unintended!)
Scrooge McDuck
Posts: 36
Joined: Mon Apr 10, 2017 11:49 am
Contact:

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by Scrooge McDuck »

The spreadsheet looks great. Just started to play around with it.

Nitpick: it looks like I need to update "Back-test end date" in the "My Parameters" tab in order to get the charts in the "My Charts" tab to reflect the latest data. I was puzzled for a while why the charts seemed out of date. Might be nice if the charts always reflected the latest data.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Scrooge McDuck wrote: Thu Apr 13, 2023 8:27 pm The spreadsheet looks great. Just started to play around with it.

Nitpick: it looks like I need to update "Back-test end date" in the "My Parameters" tab in order to get the charts in the "My Charts" tab to reflect the latest data. I was puzzled for a while why the charts seemed out of date. Might be nice if the charts always reflected the latest data.
One of the downsides of Apps Script functions is that they don't like parameters that derive from dynamic inputs like rand() or today(). So I put in a trigger starting in version 8 that will daily programmatically update the end date at 1:00AM without causing issues with the Apps Script calls.

There are steps under Apps Script Authorization in the Instructions Tab on how to install the triggers (requires giving permissions to the script to run under you account). The date update feature is part of the emailTrigger() installation.
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Version 9 has been released: MM Optimizer v9

This release has one major new feature, thanks to @adamhg's source for daily yield data for almost 800 Money Market Funds, plus some minor cleanup.

Key new Features include:
  • Added daily updated 7-day SEC Yields for over 800 funds. See tabs Fund Watch and Funds Filter
  • Also made some minor changes in formatting and colors on charts
  • Fixed the annualization calculation on the T-bills tab
  • Some defensive coding in the emailTrigger for failure situations
  • Major backend updates to support my long-term goal of a general purpose set of Apps Scripts functions to provide BH users with good data sources
If you are interested in availability and relative After-Tax Yields for non-Vanguard funds, the Fund Watch and Funds Filter tabs will help you.
  • Funds Filter allows you to filter and analyze funds by categories: Prime, Government, Muni, StateMuni, and Treasury and further filter on the various data elements such as $ Assets, Expense Ratio, Fund Name, etc.
  • Funds Watch is a one page watch list of whatever money market funds included moneymarket.fun dataset. Simply put the ticker name in one of the yellow cells. I have pre-populated with a popular list of funds, but you can add/delete as you see fit. Just like the My BestNow tab, the fund's After-tax yield and Tax-equivalent yield values are provided for fair comparisons.
Phaedrus
Posts: 77
Joined: Sat Feb 27, 2010 6:31 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by Phaedrus »

retiringwhen wrote: Thu Apr 13, 2023 8:48 pm
  • Funds Watch is a one page watch list of whatever money market funds included moneymarket.fun dataset. Simply put the ticker name in one of the yellow cells. I have pre-populated with a popular list of funds, but you can add/delete as you see fit. Just like the My BestNow tab, the fund's After-tax yield and Tax-equivalent yield values are provided for fair comparisons.
Thanks very much for all the effort you have put into this spreadsheet. It is awesome and much appreciated.

In the FundsWatch sheet, I see there's a NJ fund at the bottom (FSJXX), even though I put NY as the state in my parameters. The page says you can edit the list, so I replaced this with VYFXX, a NY fund. However, the TEY on the Funds Watch and Funds Screen page don't match. Is there a formula error somewhere? It seems like the FundsWatch page may still be assuming I am in NJ, as it lists the % in State Munis as 0.
Phaedrus
Posts: 77
Joined: Sat Feb 27, 2010 6:31 pm

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by Phaedrus »

Phaedrus wrote: Thu Apr 13, 2023 11:45 pm
retiringwhen wrote: Thu Apr 13, 2023 8:48 pm
  • Funds Watch is a one page watch list of whatever money market funds included moneymarket.fun dataset. Simply put the ticker name in one of the yellow cells. I have pre-populated with a popular list of funds, but you can add/delete as you see fit. Just like the My BestNow tab, the fund's After-tax yield and Tax-equivalent yield values are provided for fair comparisons.
Thanks very much for all the effort you have put into this spreadsheet. It is awesome and much appreciated.

In the FundsWatch sheet, I see there's a NJ fund at the bottom (FSJXX), even though I put NY as the state in my parameters. The page says you can edit the list, so I replaced this with VYFXX, a NY fund. However, the TEY on the Funds Watch and Funds Screen page don't match. Is there a formula error somewhere? It seems like the FundsWatch page may still be assuming I am in NJ, as it lists the % in State Munis as 0.
Also, on the Funds Filter page, are the ATY wrong for state muni funds? If I live in NY, shouldn't the ATY = the SEC yield? It's showing the ATY is higher than the SEC yield?
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

Phaedrus wrote: Thu Apr 13, 2023 11:50 pm Also, on the Funds Filter page, are the ATY wrong for state muni funds? If I live in NY, shouldn't the ATY = the SEC yield? It's showing the ATY is higher than the SEC yield?
Yes, there appears to be a bug, looking into it now. Did you set your state on the Parameters page to be NY?
Topic Author
retiringwhen
Posts: 4930
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

Post by retiringwhen »

I fixed an error with how State Specific Funds were handled. They were not putting updating their % Is State correctly. It has been fixed. in version 9.1. If you pulled version 9, please pull it again. The changes are isolated to the Funds Watch and Funds Filter tabs, both exhibited the same error.
Post Reply