Mutual Fund Distribution Issues from QM2007 to QM2020

cwiener

I own a number of standalone mutual funds which are classified by QM2020 as Brokerage accounts. The dividends and capital gains distributions are not reinvested but are instead deposited in my separate savings account. QM2007 had not issue handling this transaction properly.

After converting to QM2020, these transactions are entered as sales of the underlying mutual funds at $1.00 per share with a note in the memo field that: "Created based on Withdrawal of $XXXX.XX.” I don’t see any transfer of the funds to the savings account.

There is a second sales transaction for the same amount of shares (but no share value is listed) along with a corresponding debit of the amount of the distribution. But there is no way to view where the distribution goes. The memo field for these begins “Account for cash . . .” but there’s no way for me to review both parts of the transaction - edit only brings up the sale transaction.

This leads to an inaccurate price and transaction history for the mutual funds in question. And there is no way for me to reconcile my accounts properly. (They were reconciled before I converted them since it’s easy to do for a standalone mutual fund account in QM2007.)

What can I do to clean up this mess and get these accounts back on track?

cwiener

Some additional information on this issue.

When I look at the portfolio for each single mutual fund account, it shows a cash balance which shouldn’t be there.

All the mutual fund portfolios show the correct number of shares except for one which is way off and shows a negative share balance.

John_in_NC

The single transaction types that also include a transfer are supported in some versions of Quicken, but not in the newer versions of Quicken for Mac. They will not convert over, and you will only get part of the transaction. I suspect that is what is happening in your case.

These will unfortunately require (one time) manual cleanup if so. Each transaction will have to show the underlying investment transaction (Dividend, Sell, etc.) and the subsequent and separate transfer transaction to where your distribution ends up (Savings.)

I can't see your data, so you will have to compare what really happened versus what Quicken now displays. The $1.00/share transactions sounds as if money market funds are being reported in Quicken as most brokerage holds cash values in them.

That is about the best I can offer without seeing your data, but it should point you in the right direction to take corrective action.

cwiener

The $1.00/share thing is confusing because there are no money market funds involved.

Basically when a dividend or other distribution occurs, the amount of the distribution shows as a sale of shares of the underlying mutual fund at $1.00/share. And there are two transactions each time. But I can’t see anyway to look at the underlying transfer to the bank account.

So I’m not even sure how I even go about cleaning up the
transactions.

John_in_NC

Do you still have access to 2007 to see the underlying transactions?

You should see two transactions: one for the Dividend (or Capital Gains, etc.) , and the other for the transfer. From a test file:



You will have to figure out what originally happened, and recreate the two transactions. Then, delete the erroneous one(s). I don't know of a way around this.

cwiener

In QM 2020, I see the Dividend Income transaction but there is no corresponding Payment/Deposit transaction for the transfer. Instead, there are two corresponding Sell Transactions which look like this:



However, in the Transfer account, there is a Transfer transaction from the Mutual Fund accounts that correctly represent the transfers but I can’t see the other side in the Mutual Fund accounts.

jacobs

@cwiener  This is what the Quicken Mac product manager wrote last fall in response to a question about single mutual funds:

As far as we're concerned, Quicken Mac (subscription) does support single-mutual funds.  You aren't seeing the transfers because single mutual funds don't have a cash balance and therefore we hide the transfer even though it's actually there under the hood for reporting and other purposes.  This is similar to Quicken Windows that has a BUYX action which is a combination of a Buy and a Transfer.  This is the way we designed the feature so it's working as we designed it.

You might want to read the whole thread here. 

Chris_QPW

@jacobs @Quicken Marcus is mistaken about how Single Mutual Funds work in Quicken Windows, and I assume in Quicken Mac 2007 and that is leading to this problem conversion problem.

In another Windows thread we had to dig into all the details of how "cashless" 401K account works, and as a result I now know quite a bit about such an account and Single Mutual Funds accounts.

With 401K accounts during the connecting to the financial institution "somehow" the financial institution signals that it is "cashless" as there isn't a cash balance securities are bought and sold without any indication of where the cash for this transaction came from.  From the OFX spec:

"Some servers may not report the cash transactions, e.g. deposits are immediately used to purchase securities and only the buy transactions are reported. Similarly, securities are sold to fund a withdrawal and only the sell transactions are reported and not the actual withdrawal transaction"

The way Quicken Windows handles this is during the download a transaction like a Buy is turned into a BuyX with the account as the the "transfer account" set as the same 401K account.  This is the "syntax" for Quicken Windows that means the the funds came/or went from "outside" of Quicken.  As in don't affect any category or account in Quicken.  I don't have an example of an automatic download into a Single Mutual Fund account, but it is reasonable to assume they work the same way.

Just as described by the spec and by Marcus.

BUT Quicken Windows (and guess Quicken Mac 2007) doesn't "force" it to be this account.  It can be recorded as a transfer from another account in Quicken.
Especially if the account was setup for manual entry.

Here is a buy transaction in a Single Mutual Fund account in Quicken Windows.
Yes a Buy is forces to be a BoughtX, and yes it is forced to be a transfer, not from the cash balance, but look you can select the account that the cash is transferred from.


It isn't force to have the same account which would mean that the cash comes from outside of Quicken as Quicken Mac Subscription is doing.  So Quicken Mac Subscription isn't handling this use case correctly.

Chris_QPW

As for fixing the problem "after the conversion" here is what I believe you are going to need to do.

You need to change all of those transfers to some kind of category that you will from now on use to track how much is funding those investments.

cwiener

@jacobs Thanks for that thread. It helps to explain why I can’t see the transfer but I don’t get Quicken’s logic for hiding it. Makes it hard to compare when you can only see one side of the transfer.

@Chris_QPW QM2007 doesn’t have a BuyX transaction but when entering a cash distribution you can enter the transfer account directly. There’s no need to do two separate transactions, i.e., one for the distribution and one for the transfer.

Also, not sure how much this matters, but for all the mutual fund accounts in question, the transactions are all entered manually. There’s no option to download them either directly or by importing a file.

Chris_QPW

@cwiener "BuyX" is just Quicken Windows nomenclature/action for a buy that you also specify to transfer the amount at the same time, which is the same as what you are describing with Quicken Mac 2007.  As the name might be different, but the results is the same.

Note that the problems you are describing not only come up for a conversion from Quicken Mac 2007 to Quicken Mac Subscription, they also come up when converting from Quicken Windows to Quicken Mac Subscription.

But this does beg a question that I might not have got correct since I can't really see the full results.

In Quicken Windows it is very possible to do this.
There is a buy in a Single Mutual Fund account that transfers the money from a checking account.

Now if during the conversion Quicken Mac Subscription just makes the buy "cashless" as in change the "funding" to be "not shown in a Quicken account", then it seems that the checking account transactions/balance would be wrong unless those transactions are removed from the checking account.

Is this what happens?

cwiener

> @Chris_QPW said:
> As for fixing the problem "after the conversion" here is what I believe you are going to need to do.
>
> You need to change all of those transfers to some kind of category that you will from now on use to track how much is funding those investments.

I hope that’s not the case. I entered a couple of transfers directly in the accounts since they’re now classified as Brokerage accounts which let me do the two transactions for the distribution/transfer without issue.

John_in_NC

In Quicken Mac Subscription, there is no way (that I know of) to setup an account as "Single Mutual Fund," nor link it to a cash account. I have seen one user screenshot where there was a preference for SMF, and the only way I concluded this option was possible was it somehow discerned this from a Direct Connect download.

Being that I don't have any SMF accounts, I cannot confirm the behavior. But other investment account types in subscription (including 401k) displays a cash balance. How the balance remains at zero is up to the user (via setting up transfers) or via the Placeholder being updated via download.

But, since cwiener mentioned early on about an incorrect cash balance (and knowing what I do from other peoples' conversions with similar transactions), there is a cash balance, and this requires the two transactions as I noted early on. From his or her last post, I think that cwiener is now doing that to correct. 

John_in_NC

@cwiener

Your screenshot posted of your questionable Sell transaction is equally confusing (or intriguing); I don't know how you got what appears to be a split transaction. (I have not seen that possible, so I wonder what the conversion is doing/making this possible.)

If you make the "Transfer" column visible in your investment account, does it allow you to enter a transfer in this view? I haven't seen it before.

jacobs

@John_in_NC  You definitely cannot create a single mutual fund account, or convert an account to a single mutual fund, but Quicken Mac preserves an existing converted (Quicken 2007 or Quicken Windows) single mutual fund account. I still don't understand why they didn't get rid of SMF accounts and upon import just turn the imported transactions into a pair of Buy/Sell and Transfer transactions, but I guess they felt they needed to support people who had it this way in the past. As Marcus wrote, "You aren't seeing the transfers because single mutual funds don't have a cash balance and therefore we hide the transfer even though it's actually there under the hood for reporting and other purposes."

As for the screenshot you may remember seeing in the past, Tyka posted last year, "At one point there was a checkbox under settings to turn an account into a single mutual fund account. I did find mention that the ability to turn off an SMF account had to be removed because it caused issues."

John_in_NC

@jacobs

Thanks for the clarification. I knew I had seen that checkbox somewhere.

Chris_QPW

Well since you established that in fact the balance of another account (the funding account) is now wrong I can say with little doubt that the Mac development team doesn't understand how the SMF accounts were implemented in Quicken Mac 2007 and in Quicken Windows.

Even though a SMF cash account doesn't have a cash balance, there is two ways to "view this".  The one Quicken Marcus points out is that "cash" is "totally invisible" to Quicken.  As in when you do a buy/sell/... nothing in Quicken is affected cash wise, no categories or accounts affected.  This is what they implemented and forced on all SMF accounts.

But there is another way to view this as in "the account never holds a cash balance because as the money is transferred in it is use immediately for the buy (reverse for a sell)".  So even though the SMF accounts don't have a cash balance, they can have a "funding account".

If there isn't any funding account (the money came/went from outside of Quicken) then what they have done will work correctly.  But if there is a funding account they have just left one side of the transfer (broken) and as such it will not reconcile.

And as such unless the Quicken Mac developers change their minds and fix this then the only fix it to somehow get rid of the "broken transfer" transaction.  And that can be done either by deleting it or one wants to still know "how much did I fund this account with" kind of question, then change it to a category.

Chris_QPW

Note that the workaround that I suggested assumes that you can in fact get to and change the "broken transfer" in the "funding account" and it isn't hidden in some way preventing that.

cwiener

There’s a lot to unpack here, so let me take it one step at a time.

> @John_in_NC said:
> But, since cwiener mentioned early on about an incorrect cash balance (and knowing what I do from other peoples' conversions with similar transactions), there is a cash balance, and this requires the two transactions as I noted early on. From his or her last post, I think that cwiener is now doing that to correct. 

Just to clarify, I have not begun to correct the problem. I was referring to the fact that I was entering current distribution transactions successfully,

> Your screenshot posted of your questionable Sell transaction is equally confusing (or intriguing); I don't know how you got what appears to be a split transaction. (I have not seen that possible, so I wonder what the conversion is doing/making this possible.)

It may look like a split transaction, but it really isn’t. When I go to edit the transaction, I only see the sale transaction where the number of shares is the distribution amount assuming a share price of $1.00 and the share price is 0 resulting in a total amount for the transaction of $0.

> If you make the "Transfer" column visible in your investment account, does it allow you to enter a transfer in this view? I haven't seen it before.

When I make the Transfer column visible, it shows the correct transfer account that the distribution was deposited in. The transfer account also shows up in the second line of the “split” transaction.

> @Chris_QPW said:
> Well since you established that in fact the balance of another account (the funding account) is now wrong I can say with little doubt that the Mac development team doesn't understand how the SMF accounts were implemented in Quicken Mac 2007 and in Quicken Windows.

There may be a bit of confusion here. In fact, the balances of the funding accounts (there have been multiple over the years) are correct as imported. I’m just concerned about throwing them out of whack when I start trying to clean things up.

Chris_QPW

OK that makes it a lot clearer of what you are seeing.

Not perfectly clear, because I still don't know exactly what they did in the funding accounts (but I have two guesses there), but certainly clearer.

Let's start with the investment accounts.  What the conversion should have done is changed each of these transactions into two transactions transfer and buy or say a sale and transfer.  Note that removing the account isn't needed, just breakout each into there investment and "cash" parts.  It is also interesting to note that in the OFX standard there isn't anything like BuyX (buy and transfer in one transaction), this a "Quicken invention".  BTW I don't know if this investment account is being downloaded, but the financial institutions can "enforce" the SMF concept and as such have an account per fund. And also not send any cash transactions.  But the cash transactions can be "supplied" by linked transfers from/to the funding account.  But note that they won't get the "cleared" status, you will have to do that manually.

But that isn't what they did, and that is why the price history is wrong. They did this funky sell at $1.00, and then followed that with another transaction that just removes the amount from that account.

So that leads to the two questions of:
How do I clean up this history?  (answer might be leave it)
How do I enter transactions in the future?

These questions hinge on what they did in the "funding accounts".
There is two possibilities.
One, they completely removed the transaction and then adjusted the balance somehow.
The second is that the transaction is still there, but instead of being a transfer is it just a transaction with a different category, and that category might be "empty".

First I'm going address what I think should be done for future transactions.  They should be entered as "regular/non SMF" accounts.  As in two transactions, transfer and buy, or sell and transfer, or div and transfer.  These should be linked transfers to the funding account.

That should keep your price history correct and the balance of the funding account correct.

Now on cleaning up the history.  As I said above one possibility is just leave it alone and ignore the price history being wrong.

On correcting this the history there are few possibilities.  To correct the price history.
Change their sell to have the right sell price, which will in turn change the amount of cash, so you end up changing their second transaction that is removing that cash from the register to that amount.

Another possible course is and change that first sell again like above, but on the second transaction change it to a linked transfer to the funding account.  This is the "most correct", but now you have to deal with correcting the funding account.

What you have to correct depends on what they did in that account.
I can see two possible ways they might have done it.
One is "delete the transfer/transaction" and make either a "balance adjustment" kind of transaction in its place or just adjust the balance one time for all the deleted transactions.

For this case you going you are going to have to "reverse" the balance adjustment, however they did it.  That might adjusting the starting balance or if there are individual balance adjustment transactions, remove them.

The other possibility is that they left the transfer transaction there, but it isn't a linked transfer.  So in reality is just a regular transaction with some kind of category (might be empty). In this case you have to remove these transactions because the linked transfer from the investment account will duplicate them.

cwiener

I definitely can’t leave the sale transactions as is - they throw off my capital gains as I noted this week in trying to figure my estimated tax payments.

FYI, all transactions were entered manually - there is no download capability for this MF company.

As I’ve said I’ve entered recent transactions manually with no problem that I see. I did them as a split transaction with the distribution on one entry and the transfer out as another.

Chris_QPW

cwiener said:

FYI, all transactions were entered manually - there is no download capability for this MF company.

I suspected as much.

This is in fact the only time I think this comes up (manual entry of a SMF account).

If you were downloading there isn't anything in the OFX spec that allows the financial institution to send what account the transfer of funds would go to.  As such when downloading the only way it can be coded is for the money to come/leave the account "outside of Quicken".

It isn't until you have a user that can select an account that the transfer possibility comes into play.