Money Market Optimizer Spreadsheet - Releases, Bugs, etc.

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

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

Post by retiringwhen »

So, I did a quick look and it appears that today we have hit some sort of Google Drive quota. First time I have ever seen this. I am going to have to research what caused it. It may have been related to the need of my tool to process the monthly EDGAR updates from the moneymarket.fun site.

In the background, my app stores histories of some funds and all the EDGAR data in a Google drive account. I try to stay away from quotas by using caching and other mechanisms, but it looks like we hit something today.

I will watch and try to identify the cause and look for solutions.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

This error appears to be caused today by a quota for Google Drive accesses/shares being exceeded. I have never seen that before and I will need to track to see when/why it happens. It appears to have cleared for me at least this evening around 1800 EST.

Are you still seeing the error?

I have an idea on to catch the error and then actually return an error code that describes the situation, that should help debugging (and user awareness) going forward. I will likely publish a minor update in a few days for that situation.

It has not happened to me before, so I am wondering if it is an odd occurrence. But, I know that the Google Drive files got lots of accesses today as m tools updated the EDGAR reports this month overnight (a massive run through many files). If that is the case, I have some schemes to potentially reduce the load.
jamesbogle
Posts: 21
Joined: Sun Jul 14, 2013 9:32 pm

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

Post by jamesbogle »

Actually the last trigger email succeeded on v17.11 at 5:57 PT.

I thought it was just a local thing from the message, but if you're seeing it also, that makes sense.

Do all active spreadsheets reach out to that single google drive? I guess that could cause some kind of rate limit.

If you want to put some debugging info in the emails in future versions, can post those also.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

jamesbogle wrote: Tue Feb 13, 2024 9:15 pm Actually the last trigger email succeeded on v17.11 at 5:57 PT.

I thought it was just a local thing from the message, but if you're seeing it also, that makes sense.

Do all active spreadsheets reach out to that single google drive? I guess that could cause some kind of rate limit.

If you want to put some debugging info in the emails in future versions, can post those also.
yes, the scripts all work off a single google drive tree, but most of the queries (the vast majority) are cached, so much less actual file reads. But, there is still ways that lots of reads could happen.

Basically, I am building a database of yield histories and EDGAR data. Some day, I may convert it into a real database, but json files on a drive works well enough. I've also thought of storing it all in Sheets worksheets, but unfortunately, scripted updates to the sheets is much too expensive in processing time (100s times slower than reading/writing json files).

Also, some data is cached once for a script (for all users), but others are unique to the user and sheet instance, so there is a scaling problem.

I may add some debugging to the emails, but I need to initially put some error reporting down in the guts of the scripts to throw a usable error message to help me identify when the caching error happens.
tinyhamster
Posts: 1
Joined: Thu Feb 15, 2024 8:11 pm

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

Post by tinyhamster »

Hi guys,
I have been using this spreadsheet since the fall. Today it stopped updating when I changed the date. The last update was on 2/13/23. I was using version 13.07, but today i updated to to 17.11 and it is still not loading in values. Not sure if this is related to the recent posts about issues with the optimizer.

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

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

Post by retiringwhen »

Using 17.11 in several different accounts, I am seeing no problems at this time.

Have you run all the authorization steps described in the Instructions tab?

Also, can you run the command Optimizer Actions -> Reload Custom Functions? That will often clear up any issues at script load time.

Any other specific errors that you are seeing?
laughmyassetsoff
Posts: 19
Joined: Wed Jan 03, 2024 6:55 pm

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

Post by laughmyassetsoff »

Hello!

Thanks very much for this spreadsheet!

I am wondering where the USGO data in the EDGAR tab is being pulled from, specifically for the Fidelity funds? I want to ensure that they are accurate because I'm seeing a pretty large discrepancy with Fidelity's published data from this past tax year.

Fidelity just published their data for 2023. If I am interpreting it correctly, FDLXX yielded 90.39% of income from U.S. Government securities that are tax exempt, and SPAXX had 41.18% (see "Fidelity® Government Money Market Fund - All Classes"). The spreadsheet says right now that it's 100% and 54%, respectively.

Did something major change with these funds? Or, are there additional state tax-exempt securities not accounted for in the PDF document I linked? Or, is there an error in that part of the sheet?

I ask because for my specific situation, that small difference would change which Fidelity MMF it makes the most sense for me to use. Since you have that history of EDGAR data available, I'd be grateful if you could check.
Last edited by laughmyassetsoff on Sat Feb 17, 2024 3:16 pm, edited 1 time in total.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

The sheets pull the data from the site https://moneymarket.fun run by forum poster @adamhg.

That data is pulled directly from EDGAR on a monthly basis.

My data only goes back to April 2023 for most of the Fidelity funds.

There is a setting for what history you want to use for EDGAR with three choices, last month, last year, and YTD.

The Fido funds all had high use of Repos early in 2023 skewing the results.

I can’t look right now, but I believe I have the reporting set by default to YTD, which is very different than last year. Investigate the EDGAR tab and the Instructions tab for adjusting history to get an ideas of the different values.

I have a high degree of confidence in that actual data reported, the problem is the gaps before APR 2023.
laughmyassetsoff
Posts: 19
Joined: Wed Jan 03, 2024 6:55 pm

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

Post by laughmyassetsoff »

Okay! So, if I'm understanding what you're saying correctly, 2023 was just an aberration then?

I'm looking at the SPAXX entry on moneymarket.fun, and it looks like the holdings are being correctly classified as state-tax exempt, since the 54% would include 29% Treasuries and 25% Agency Securities, while not including the Repos. I only hope that it's true that all Agency Securities held are state tax exempt—that it's all government agency bonds and no GSE agency bonds. (I don't know quite enough to know what I don't know, here.) My impulse is to cross-check with historical data from last year, but I wouldn't know where to find it.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Generally last year was an aberration due to Fed actions. And current january percentages are close to longer term history. I think I have the classifications correct
KzooKendrick
Posts: 1
Joined: Mon Feb 19, 2024 3:40 pm

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

Post by KzooKendrick »

Thanks for making this sheet! I found it late last year and started with v14. The triggers were working fantastic until Jan 31 when I didn’t receive an email.

A few days ago I made a copy of v17.11 and followed all the instructions. This time I went into the trigger extensions and turned on failure emails to immediate. The triggers haven’t worked for me yet in 17.11 and here’s the email I got from the failure:

Your script, MM Optimizer v17.11, has recently failed to finish successfully. TypeError: swapReportDate.getDay is not a function.

Anyone else getting this or know the fix? I’ve tried most everything including closing and reopening, reinstalling the triggers, force update, etc.

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

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

Post by retiringwhen »

KzooKendrick wrote: Mon Feb 19, 2024 3:51 pm Thanks for making this sheet! I found it late last year and started with v14. The triggers were working fantastic until Jan 31 when I didn’t receive an email.

A few days ago I made a copy of v17.11 and followed all the instructions. This time I went into the trigger extensions and turned on failure emails to immediate. The triggers haven’t worked for me yet in 17.11 and here’s the email I got from the failure:

Your script, MM Optimizer v17.11, has recently failed to finish successfully. TypeError: swapReportDate.getDay is not a function.

Anyone else getting this or know the fix? I’ve tried most everything including closing and reopening, reinstalling the triggers, force update, etc.

Thanks in advance!
This is a field in the hidden SwapCalcs tab. If that field is empty or in error, then you will get errors like this. I try to catch them, but not sure why they would fail without being caught (not a function error says that value is not a date string).

It appears that I missed on scenario where Google Apps Script has not fully loaded the scripts and an error occurs (the value returns as '#NAME?')
I updated the script version to 17.12 with this processing getting caught with an email warning of failure.

Interestingly, This error has started recently and my scripts are seeing the error about twice a day, mostly in the evening. I am guessing this is somehow related to quotas on processing, but I am not sure why it suddenly showed up.

Update, I have instrumented the core file retrieval scripts and hope to get better info on what is going on here, it may take a while since I cannot figure out how to make it happen on demand!
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Quick follow-up. I have confirmed that basically the sheet is not fully initializing before the emailTrigger trigger script runs. Originally, I had no issues with this happening on triggers. It appears to me that Google has done some "optimization" that now makes that happen.

Another symptom is the fact that many times when a user opens the sheet, it can get stuck on loading. That problem seems to come and go over time for random reasons that only Google understands.

I am testing a fix to the emailTrigger script that will force all the custom functions to be executed before the logic of the script is executed. I will release a new version once that is shown to work and not break anything else.

In the meantime, users can manually open the sheet, then run the emailTrigger command AFTER all the tabs properly populate. you can also just unhide the SwapCalcs tab and inspect that manually if you desire. I may unhide it in the future. It was originally hidden because users changing the layout will break the trigger.
laughmyassetsoff
Posts: 19
Joined: Wed Jan 03, 2024 6:55 pm

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

Post by laughmyassetsoff »

retiringwhen wrote: Sat Feb 17, 2024 4:33 pm Generally last year was an aberration due to Fed actions. And current january percentages are close to longer term history. I think I have the classifications correct
Thanks! To be clear, I wasn't suggesting you didn't have things correct, but rather that they were potentially opaque in the data that Fidelity reports.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

All Funds Money Market Optimizer Spreadsheet v6

Post by retiringwhen »

Announcing Version 6 of the MM Optimizer All Funds Edition. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing

I have made three changes in version 6:
  • improved the error reporting, and exception handling for file read actions inside the fintools library to report better on quota and access issues. This may improve performance.
  • added new checks in the emailTrigger function that sends the swapping emails. Hopefully this will reduce the recent issues with failed email processing. The root cause appears to be something related to Google's processing of custom functions in App Script. My update is defensive coding and not a root cause fix for the issue. It does appear to be pretty reliable after several days of testing.
  • Changed the State designation of Florida to "NOTAX" to identify this option as appropriate investors who live in one the 10 states with out an income tax levied on investment income.
As always, feedback, error reports, and feature requests are desired.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Version 18 of the MM Optimizer Vanguard/Fidelity Edition

Post by retiringwhen »

Announcing Version 18 of the MM Optimizer Vanguard/Fidelity Edition. You can find it here: https://docs.google.com/spreadsheets/d/ ... sp=sharing

I have made three changes in version 18 (same as All Funds Edition):
  • Improved the error reporting, and exception handling for file read actions inside the fintools library to report better on quota and access issues. This may improve performance.
  • Added new checks in the emailTrigger function that sends the swapping emails. Hopefully this will reduce the recent issues with failed email processing. The root cause appears to be something related to Google's processing of custom functions in App Script. My update is defensive coding and not a root cause fix for the issue. It does appear to be pretty reliable after several days of testing.
  • Changed the State designation of Florida to "NOTAX" to identify this option as appropriate investors who live in one the 10 states with out an income tax levied on investment income.
As always, feedback, error reports, and feature requests are desired.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

One other note, when upgrading from older versions of the spreadsheets, it is a very good idea to delete the old spreadsheet by using the command File -> Move to Trash.

Leaving around old sheets will potentially reduce the performance of your other newer sheets due to the way Google App Script prioritizes and load balances the server side processing. It is a very good idea to decommission those old scripts by sending them to the trash.

If you don't remember which scripts are laying around, you can always look in Google Drive and remove the files directly: https://drive.google.com/drive/my-drive
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Today around 11am to 1pm EST, the Google App Script engine seemed to fail with some very bizarre errors. I think we are seeing some instability on the Google server side right now. This was recurring internal errors, and when I looked at the custom function failures, the failures were impossible errors implying some sort of corruption on the run-time engines.

They cleared up around 2:30 or so and seem to be working fine without change in my scripts. The scripts are still loading very slowly though.
nh_ets
Posts: 26
Joined: Wed Apr 14, 2010 7:35 pm

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

Post by nh_ets »

Are you able to update this with Minnesota tax information? Thanks!
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

nh_ets wrote: Wed Feb 28, 2024 4:33 pm Are you able to update this with Minnesota tax information? Thanks!
Sure, I am happy to do that. Do you have access to a good marginal rate table? Otherwise, their rates are pretty straightforward I believe. I know they have strange rules about in-state munis but I think I know how to handle that.

You can take a stab at it yourself by adding a column to the State Parameters Tab and updating your rates there.
nh_ets
Posts: 26
Joined: Wed Apr 14, 2010 7:35 pm

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

Post by nh_ets »

Thanks!
Found this...

5.35% Up To6.80%


7.85%


9.85% Over


Married Joint

$46,330


$46,331 - $184,040


$184,041 - $321,450


$321,450

Married Separate

$23,165


$23,166 - $92,020


$92,021 - $160,725


$160,725

Single

$31,690


$31,691 - $104,090


$104,091 - $193,240


$193,240

Head of Household

$39,010


$39,011 - $156,760


$156,761 - $256,880


$256,880
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

I added Minnesota just now, and I did Maryland recently as well. Inline updates to v18 and v6 of the sheets.
You could just copy the State Parameters tabs into local copies for folks who don't want to start over. Should work for most recent vintages of the sheets.
nh_ets
Posts: 26
Joined: Wed Apr 14, 2010 7:35 pm

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

Post by nh_ets »

Thank you! Amazing work.
For me it always seems that you can't beat VUSXX if you have a state with higher tax rates (MN - 9.85%) and a higher federal bracket (>30%)!! Not even HYSA beat it.
tj
Posts: 9481
Joined: Wed Dec 23, 2009 11:10 pm

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

Post by tj »

nh_ets wrote: Thu Feb 29, 2024 10:52 am Thank you! Amazing work.
For me it always seems that you can't beat VUSXX if you have a state with higher tax rates (MN - 9.85%) and a higher federal bracket (>30%)!! Not even HYSA beat it.
Why would the federal bracket be relevant to this statement?
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

tj wrote: Thu Feb 29, 2024 4:20 pm
nh_ets wrote: Thu Feb 29, 2024 10:52 am Thank you! Amazing work.
For me it always seems that you can't beat VUSXX if you have a state with higher tax rates (MN - 9.85%) and a higher federal bracket (>30%)!! Not even HYSA beat it.
Why would the federal bracket be relevant to this statement?
well when comparing to muni fund's higher federal rates would make them more attractive. A lesson recent tracking has shown is that muni's are not keeping pace with treasuries at current rates.
bog007
Posts: 541
Joined: Fri Feb 28, 2020 2:27 am

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

Post by bog007 »

why is wmpxx showing different rate on website

https://www.allspringglobal.com/investm ... tion-10667
Don’t let anyone else ruin your portfolio. It’s your portfolio. Ruin it yourself!!!
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

bog007 wrote: Sat Mar 09, 2024 3:03 pm why is wmpxx showing different rate on website

https://www.allspringglobal.com/investm ... tion-10667
That is a link to STGXX.

Edit:
I am away from real computer for a while and cannot verify the sheet itself due to network limitations, but the source data looks correct.
mouth
Posts: 658
Joined: Sun Apr 19, 2015 6:40 am

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

Post by mouth »

Just gotta pop in and say/ask WTF is going on with munis?!?! :shock:
User avatar
Hacksawdave
Posts: 844
Joined: Tue Feb 14, 2023 4:44 pm

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

Post by Hacksawdave »

mouth wrote: Fri Mar 15, 2024 6:51 pm Just gotta pop in and say/ask WTF is going on with munis?!?! :shock:
Could you be a little more specific?

The yields have been stabilizing in the last month as the SIFMA swap index has moved in a 30 bp trading range. It has not been this smooth since the rates increased in June of 2022.
mouth
Posts: 658
Joined: Sun Apr 19, 2015 6:40 am

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

Post by mouth »

Ha ha, yeah that's what I mean. Just a surprising turn of events, to the mostly uninitiated like me, given the long cyclical pattern we've seen. And heck before June 2022 there wasn't much of a range to trade in anyway unless things went negative.

Just interesting and wondering why it's suddenly gone so flat.
mikebh
Posts: 23
Joined: Sat Sep 16, 2023 9:20 pm

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

Post by mikebh »

how is TBIL tax treated for California taxes?
I try to compare TBIL vs SNSXX
where the SNSXX has to be 100% tax exempt from CA taxes
But, I can't figure out about ETF TBIL for this matter

thanks
Best Regards | Mike
scoothome
Posts: 100
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

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

Post by scoothome »

Should we expect to see negative numbers for Best - Highest? That seems counterintuitive to me. These are small numbers, so it could just be a precision/rounding error (?)

Image
User avatar
Hacksawdave
Posts: 844
Joined: Tue Feb 14, 2023 4:44 pm

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

Post by Hacksawdave »

mouth wrote: Sat Mar 16, 2024 2:18 pm Ha ha, yeah that's what I mean. Just a surprising turn of events, to the mostly uninitiated like me, given the long cyclical pattern we've seen. And heck before June 2022 there wasn't much of a range to trade in anyway unless things went negative.

Just interesting and wondering why it's suddenly gone so flat.
One thing I track is the fund inflows/outflows for Vanguard’s CA tax-exempt MMF VCTXX that I own. There have been large swings as people have been flipflopping between funds hoping to squeeze out additional income. The wider the swings in fund flows, the wider the swings in yields and in the SIFMA swap index. Some days there were swings of more than $40M with large swings of $78-84M during November of 2022 to February of 2023.

Some of these moves were comical. If you remember Gilligan’s Island, it was like Gilligan trying to collect tree sap from 4 trees by running around the trees with only one bucket. By the time he got to the dripping tree and positioned the bucket, the tree stopped dripping the sap.

The flows have been basically flat since February 22 of this year. $8M has been the largest move since February 15. I am only tracking this one fund, so cannot speak for the others but would guess a similar event transpires. As people settle for one fund or get a nasty surprise on their tax bill this may stop the volatility. Only time will tell.
Topic Author
retiringwhen
Posts: 4800
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 Mar 16, 2024 4:48 pm Should we expect to see negative numbers for Best - Highest? That seems counterintuitive to me. These are small numbers, so it could just be a precision/rounding error (?)

Image
This looks like a rounding error, but I try to be consistent with rounding and significant digits. OTOH, it could also be where my algorithm that delays swaps one day reduces returns by a tiny bit vs just holding. The example you show has only a handful of swaps in a year and for only a few days. In this cases, it may not be optimal. I would have to look at the My RatesSet tab (normally hidden) to see exactly what happened.
scoothome
Posts: 100
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

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

Post by scoothome »

retiringwhen wrote: Sat Mar 16, 2024 10:19 pm
scoothome wrote: Sat Mar 16, 2024 4:48 pm Should we expect to see negative numbers for Best - Highest? That seems counterintuitive to me. These are small numbers, so it could just be a precision/rounding error (?)

Image
This looks like a rounding error, but I try to be consistent with rounding and significant digits. OTOH, it could also be where my algorithm that delays swaps one day reduces returns by a tiny bit vs just holding. The example you show has only a handful of swaps in a year and for only a few days. In this cases, it may not be optimal. I would have to look at the My RatesSet tab (normally hidden) to see exactly what happened.
The screenshot can be replicated by selecting CA, 9.3% state tax rate, 22% federal tax rate. I haven't done a deep-dive, but I suspect that the negative Best - Highest values are an artifact of the swap delay. Do you do a final check of swap values against highest-single-fund value to determine if the swaps are actually optimal, and if not, zero out the swaps?
Topic Author
retiringwhen
Posts: 4800
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 Mar 16, 2024 10:50 pm The screenshot can be replicated by selecting CA, 9.3% state tax rate, 22% federal tax rate. I haven't done a deep-dive, but I suspect that the negative Best - Highest values are an artifact of the swap delay. Do you do a final check of swap values against highest-single-fund value to determine if the swaps are actually optimal, and if not, zero out the swaps?
If you unhide the tab My RateSet and look at the two columns Who on Top? and Fund in Best you will see the first showing which fund has the highest ATY today, and the second shows what fund is expected to be held as the Best fund (basically the one day delay).

You will see that at the stated tax rates that VMSXX only gets above VUSXX for a few days a year and probably the fast drop from the top out of VMSXX actually leaves that swap from being profitable due to the delay.

I put the delay in there originally to address the flopping problem between VMRXX/VMFXX and VUSXX where at certain tax rates, the two funds will swap for one day at a time very often, creating a very large list of prospective swaps that do not have any practical value.

OTOH, for investors in the highest state brackets, jumping into VMSXX early may be of value, and jump out early may be of value, but I do report that situation in the daily emails. We are talking pennies a trade and just a few dollars a year even with $100K invested.

This is all angels dancing on the head of a pin territory though since the two real-world factors will make the actual results different:
- the fact we are working with 7 day SEC average yields,
- the real-world delays (usually one trading day) incurred when an investor actually swaps between funds.
scoothome
Posts: 100
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

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

Post by scoothome »

retiringwhen wrote: Sun Mar 17, 2024 6:51 am This is all angels dancing on the head of a pin territory though since the two real-world factors will make the actual results different:
- the fact we are working with 7 day SEC average yields,
- the real-world delays (usually one trading day) incurred when an investor actually swaps between funds.
Thanks for the explanation and agree with this quote. I use MMO similarly to Harry Sit, i.e., looking at the after-tax yields chart for My RateSet and seeing which fund is on top most of the time, rather than chasing a few extra dollars by swapping in and out of funds.
stillcurious
Posts: 4
Joined: Sat Aug 05, 2023 1:05 pm

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

Post by stillcurious »

As one of the many grateful and appreciative users of MMO, wondering if perhaps you have thought about a similar version for bonds, particularly for those of us who hold bonds in taxable accounts, thinking it may be time to start moving some of the cash back into bonds, and are doing a similar after tax analysis of Vanguard treasuries and muni's.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

stillcurious wrote: Thu Mar 21, 2024 12:54 pm As one of the many grateful and appreciative users of MMO, wondering if perhaps you have thought about a similar version for bonds, particularly for those of us who hold bonds in taxable accounts, thinking it may be time to start moving some of the cash back into bonds, and are doing a similar after tax analysis of Vanguard treasuries and muni's.
I actually have a sheet that does something similar for only Vanguard Bond Funds, plus Treasuries/TIPS and a handful of alternative funds.

It is quite different in strategy as it looks for duration/risk anomalies across different asset types (Corporates vs. Treasuries vs. Munis, ST vs. IT vs. LT, etc.)

It needs some cleaning up to be publicly sharable. I will consider doing that.

A few caveats though:
  • Due to the size of the number of funds doing an Optimizer type tab for floating bond funds is not practical, it will just work for one user's tax rates.
  • The analysis is only usable for current yields. There are too many variables involved in floating NAV pricing to make a back-test viable when applying tax rules.
  • I have a nifty trick lifted from @Kevin M for getting current yields for all Vanguard bond funds. Extending beyond their offerings would be a significant effort I am unlikely to attempt.
    • With that said, Vanguard has a very complete set of Bond funds outside some niche areas like Convertibles, Preferred, and true Junk, so someone can analyze the Vanguard offerings and extrapolate to other fund complexes.
stillcurious
Posts: 4
Joined: Sat Aug 05, 2023 1:05 pm

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

Post by stillcurious »

That would be just great. Not at all surprised to hear you've been playing with something and certainly appreciate the need to limit to a reasonable pool. Besides the many variables, there's the ongoing tweaking to get the initial sheet going before thinking about expanding. That's either a fun challenge or way too much work. :)
Pieb2oon
Posts: 1
Joined: Mon Apr 01, 2024 3:32 pm

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

Post by Pieb2oon »

Hi,

Thanks very much for making this available.

I am trying to understand the EDGAR sheet and the 50 per cent rule.

For example, if I look at the section for SPAXX, then both 06/30/2023 and 05/31/2023 report 36.00% in the USGO column, but the former has 50pct rule TRUE and the latter FALSE.

Or for FZDXX, current YTD average shows 24.95% for USGO, and 75.05% taxable, but 50pct rule is TRUE.

Or for FSPXX, every period is 100% MUNI, and yet 50pct rule is changing from TRUE to FALSE and back a lot.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Pieb2oon wrote: Mon Apr 01, 2024 3:50 pm For example, if I look at the section for SPAXX, then both 06/30/2023 and 05/31/2023 report 36.00% in the USGO column, but the former has 50pct rule TRUE and the latter FALSE.
As per my understanding of the 50% rule, it is only applied to funds on the closing dates of a quarter (3/31, 6/30, 9/30, 12/31). So my algorithm requires two things to happen for the 50% rule to be FALSE:
- the USGO% < 50%
- the month end is March, June, September, or December.
Or for FZDXX, current YTD average shows 24.95% for USGO, and 75.05% taxable, but 50pct rule is TRUE.
This is an anomaly, but technically correct, as the fund has not yet failed the quarter end test. This is a key reason why I give the caveat in the sheet's Instructions that says:
https://docs.google.com/spreadsheets/d/1ybZv8xuZ1KGSPn4y2gDEycPQX0KJpLbuzFg3hUReJB4/edit#gid=1411540421 wrote:Warning about EDGAR percentages for residents of CA, NY, and CT: Use of the EDGAR reports (for any fund) may result in the report applying state tax advantages that will not qualify in the final analysis. When using funds that have a marginal portion of USGO that fluctuates, due diligence is required by the investor as the Optimizer tool cannot always do the full test necessary to validate the 50% threshold rule in all cases. The EDGAR tab does display the threshold calculation that is done. Please verify before depending upon the results.
For 50% threshold tests, using Last 12 mos. is likely to be more predictive than YTD.
Or for FSPXX, every period is 100% MUNI, and yet 50pct rule is changing from TRUE to FALSE and back a lot.
See my first answer.

As an aside, there may be special rules for in-state Muni's as well, but I do not apply them in this sheet because the normal EDGAR reporting data I use does not provide enough information to apply in-state rules. That data appears to only be reported at year end in separate tax-related reports.
jamesbogle
Posts: 21
Joined: Sun Jul 14, 2013 9:32 pm

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

Post by jamesbogle »

Anyone get these NaN errors?

Code: Select all

** Vanguard - 4/1/2024 **

Best Today is #ERROR!, Best Yesterday was #ERROR!

After Tax Yields
VMFXX = NaN%
VUSXX = NaN%
VMSXX = NaN%
VCTXX = NaN%

#ERROR! moved into first today, if it stays a second day, a swap will be 
recommended.

** Fidelity - 4/1/2024  **

Best Today is #ERROR!, Best Yesterday was #ERROR!

After Tax Yields
SPAXX = NaN%
FZFXX = NaN%
FDLXX = NaN%
FTEXX = NaN%
FDRXX = NaN%
SPRXX = NaN%

#ERROR! moved into first today, if it stays a second day, a swap will be 
recommended.

Report Date: Mon Apr 01 2024
Trigger Time: Tue Apr 02 2024 04:57:41 GMT-0400 (Eastern Daylight Time)

Sheets Workbook: MM Optimizer v17.11
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

That is evidence that the scripts are being delayed due to performance issues on the server side of Google Apps.

It has been an intermittent issue for a while.

I made some improvements in version 18 that tries to delay/retry when the scripts fail to complete. You may wish to upgrade, but even with those updates, I have seen some issues today.
jamesbogle
Posts: 21
Joined: Sun Jul 14, 2013 9:32 pm

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

Post by jamesbogle »

kk,

It's unrelated to the number of spreadsheets out there right?
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

jamesbogle wrote: Tue Apr 02, 2024 10:32 pm It's unrelated to the number of spreadsheets out there right?
Generally, in aggregate, the number of sheets should not impact performance as each user's scripts are run within their own account and computing resources allocated by Google.

My sheets do use a small, but critical set of shared caches to improve performance. Those should actually make things run faster. My analysis in the past have shown them to massively improve response time. But, it is possible that shared resource could be overloaded or impacted by service issues within Google for users to see widespread impact.

I will mention the same thing I posted a while back. It is a good idea for all users to be sure they delete any old unused Google sheets (not just the Optimizer) to reduce the amount of processing usage by their personal accounts. This is especially true if the sheets use custom functions like mine do.

https://docs.google.com/spreadsheets/u/0/ - this gives you a list of Google Sheets in your account, you can cleanup and delete sheets here.

https://drive.google.com/drive/home?ths=true - this give you a list of files in Google Drive. It is another way for you to see sheets you may have lying around.

https://script.google.com/home/triggers - shows you the list of triggers (aka background scripts configured in your account.) The triggers can be deleted individually, or you can delete the related sheet and they are disabled eventually after the sheet it purged from the Trash.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Here is a followup on the issue I have been seeing with the Loading.... messages.

I have seen something again similar to an error about a month or so ago, where one of the simplest functions starts to fail repeatedly to complete.

I believe (but not confirmed) that this is somehow related to how Google Sheets calculates the formula dependency rules (the order in which cells nee to evaluated in order to get a correct result.) I have stripped down the sheet to its simplest configuration and can recreate the error at will. this same formula has been working many months previously.

I believe I have a work-around by changing the order of evaluation (since I believe that is the cause), but it is somewhat complex so I will have to release a new version of the sheets. I am pretty busy in my day job right now so it may be a little while before I get to a full fix.

If we are lucky, maybe the Google Sheets engine will start behaving again in the meantime.

As an aside, this type of evaluation issue is a long-standing complaint by Sheets users, and use of custom functions is a contributor.


Update: I did some research on the google bug tracking site. This issue has been reported as an issue for over 2 years. Google reported this bug fixed about 2 weeks ago, and it appears that the bug appears to have only gotten worse. Other users are reporting current issues. For those folks interested:
https://issuetracker.google.com/issues/222342097
Last edited by retiringwhen on Thu Apr 04, 2024 12:14 pm, edited 1 time in total.
jamesbogle
Posts: 21
Joined: Sun Jul 14, 2013 9:32 pm

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

Post by jamesbogle »

retiringwhen wrote: Wed Apr 03, 2024 5:38 am
jamesbogle wrote: Tue Apr 02, 2024 10:32 pm It's unrelated to the number of spreadsheets out there right?
Generally, in aggregate, the number of sheets should not impact performance as each user's scripts are run within their own account and computing resources allocated by Google.

My sheets do use a small, but critical set of shared caches to improve performance. Those should actually make things run faster. My analysis in the past have shown them to massively improve response time. But, it is possible that shared resource could be overloaded or impacted by service issues within Google for users to see widespread impact.

I will mention the same thing I posted a while back. It is a good idea for all users to be sure they delete any old unused Google sheets (not just the Optimizer) to reduce the amount of processing usage by their personal accounts. This is especially true if the sheets use custom functions like mine do.

https://docs.google.com/spreadsheets/u/0/ - this gives you a list of Google Sheets in your account, you can cleanup and delete sheets here.

https://drive.google.com/drive/home?ths=true - this give you a list of files in Google Drive. It is another way for you to see sheets you may have lying around.

https://script.google.com/home/triggers - shows you the list of triggers (aka background scripts configured in your account.) The triggers can be deleted individually, or you can delete the related sheet and they are disabled eventually after the sheet it purged from the Trash.
Yeah wondering if all sheets are retrieving from the cache at the same time.

Thanks for the links to the triggers. Realized I haven't cleaned up the old versions each time. Will try to delete the old ones to see if it helps.
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

jamesbogle wrote: Thu Apr 04, 2024 12:03 pm Yeah wondering if all sheets are retrieving from the cache at the same time.

Thanks for the links to the triggers. Realized I haven't cleaned up the old versions each time. Will try to delete the old ones to see if it helps.
Google actually encourages use of caching for scaling purposes. There are some bad ways to use them, and I have made mistakes in the past, but I have learned a lot and they have greatly increase performance and reliability.

I have confirmed that caching is not involved in the current error condition. I have isolated a single function (mmFindStateNameInString) that does not use caching as the offending function. This appears to be a bug in the sheet formula evaluation algorithm.

Glad I can help with cleanup!
Topic Author
retiringwhen
Posts: 4800
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

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

Post by retiringwhen »

Okay, I rewrote the logic completely to avoid using a custom function (less elegant, but all within the spreadsheet.)

I think this particular issue is resolved, until Google breaks something else.

I have made inline updates to both versions.

Here are current links:

All MM Funds Optimizer v6.2

MM Optimizer Vanguard/Fidelity Edition v18.3
Post Reply