Retrieve Transactions issue for Vanguard Brokerage

[fmuser]

Vanguard recently pushed me to combine separate mutual fund and brokerage accounts by moving fund positions into my Vangaurd Brokerage Services (VBS) account. Since the conversion, I have issues with FundManager results from the "Retrieve Transactions" function for the VBS account. I'm currently using FM 2014.

Account conversion changed the handling for many of my mutual funds to payout into the settlement account. The settlement fund for the VBS account is (and was) a Vanguard money market fund. When a fund pays a dividend that is not re-invested into the same fund, the cash is moved to the sweep account. The Vanguard site shows these transfers as "sweep in" transactions with a negative dollar amount. When retrieved by FundManager, they turn in to Sell transactions from the settlement account, with a negative "Shares" but postive "Total" in the "Retrieve Transactions" window. The effect of those transactions decreases the balance in the FM view of the settlement account (when the actual settlement account balance increased).

Vanguard maintains that the negative dollar amount in "sweep in" transactions is correct, but the fact that it produces a Sell transaction in FM is a problem for me.

The only solution I've come up with so far is to tell FM to use the settlement fund as the "Default Cash Account", and manually identify/de-select the "sweep in" transactions in the list presented by the "Retrieve Transactions" function.

Is there a better (more automatic) solution?

[Mark]

Hi fmuser,

Can you give me an example, so I can try to follow this a little better? For example:

MutualFund DIVIDEND $100
Sweep BUY $100
Sweep SELL $100
MoneyMarket BUY $100

Is the above what is happening? If you retrieve, which of these 4 transactions are explicitly listed in the "Preview Importing Transactions" dialog?

[fmuser]

Vanguard web presentation of (2) transactions for one dividend payment:

Trade date: 03/01/2016
Name: XXX Money Market
Transaction type: Sweep in
Amount: -$380.94

Trade date: 03/01/2016
Name: XXX Bond Fund
Transaction type: Dividend
Amount: $380.94

FM retrieval of the same transactions:

Date: 03/01/16
Type: Sell
Name: XXX Money Market
Shares: -380.940
Total: 380.94

Date: 03/01/16
Type: Dist
Name: XXX Bond Fund
Total: 380.94

FWIW, Vanguard provided this description of their processing. I admit I don't know whether I should use (or not use) the FM "Default Cash Account" feature to mirror Vanguard transaction behavior:

"...each item is reported as two different ledger items but purchases/sales directly into or out of the settlement fund itself only one ledger item.

Purchases >>
When any purchase is made in a brokerage account, the purchase will appear in transaction history with a negative dollar amount and a positive share quantity. The negative dollar amount reflects that the transaction is a debit, which will be charged to the settlement fund. The positive share quantity reflects that new shares of the security are now held within the account.

The second ledger item that corresponds to a purchase reflects the resulting settlement fund activity. A brokerage purchase will result in a positive dollar value and a negative share quantity for the settlement fund activity. The positive dollar amount offsets the negative dollar amount of the purchase (since buying a brokerage security does not immediately result in a change in market value for the account). The negative share quantity reflects that shares of the settlement fund have been "replaced" in the account with shares of the security that was purchased.

Sales >>
When any sale is made in a brokerage account, the sale will appear in transaction history with a positive dollar amount and a negative share quantity. The positive dollar amount reflects that the transaction is a credit, which will be credited to the settlement fund. The negative share quantity reflects that the shares of the security are no longer held in the account.

The second ledger item that corresponds to a sale reflects the resulting settlement fund activity. A brokerage sale will result in a negative dollar value and a positive share quantity for the settlement fund activity. The negative dollar amount offsets the positive dollar amount of the sale (since selling a brokerage security does not immediately result in a change in market value for the account). The positive share quantity reflects that the shares of the security that was sold have been "replaced" in the account with additional shares of the settlement fund.

Cash dividends received >>
Cash dividends will initially post to a brokerage account as a positive dollar value. The second ledger item, reflecting the settlement fund activity, will have a negative dollar amount and positive share quantity. The two dollar amounts offset, and the positive share quantity reflects that additional shares of the settlement fund are now held in the brokerage account.

Reinvested dividends received >>
Dividends will initially post to a brokerage account as a positive dollar value. The second ledger item, reflecting the reinvestment, will have a negative dollar amount and positive share quantity. The two dollar amounts offset, and the positive share quantity reflects that additional shares of the security that paid the dividend are now held in the brokerage account."

[Mark]

Hi fmuser,

Thanks for the information. The money market sweep in is being reported as an "Investment Banking Transaction", and is reported with a negative value, which is why FM imports it as a sell. There is no way to import both of these and end up with correct balances, whether you use a default cash account or not. You could un-check the money market sell transaction during the preview screen, but that is error prone. For this account, I would suggest using a default cash account, but turning off the option:

Import "Investment Banking Transactions" into Default Cash Account(s)

this will prevent that sell of the money market from showing up, so you'll only have the dividend, and then by using the default cash account, you'll get the proceeds deposited into cash.

[fmuser]

Thanks. I doubt that I would have discovered/tried the "Investment Banking Transactions" option without your help.

[Porsche87]

Be aware that interest on your Money Market will come in as a negative dollar amount. Vanguard also combines distributions and sometimes other transactions together so the dollar amounts don't match up. Plus they don't post transactions until they settle. I am to the point where I do many of the updates manaully for that account. Never had these problems with anybody else.

[quercus]

Hi Mark,

I've been trying your Mar 11 suggestion. The one problem I've noticed with it is that legitimate sell orders from the settlement account don't get imported by FM.

The most recent example was when I sold $1800 of a bond fund and told Vanguard to send the proceeds via ACH to my bank. I wound up with these transactions in FM:
6/22 SELL -$1800 bond fund
6/22 BUY $1800 federal money market (settlement fund)

In addition to the above, Vanguard's web site also shows this one:
6/23 WITHDRAWAL -$1800 to <my bank>

But the above transaction never showed up in FM and had to be manually added. Before doing so, I ran the reconciliation wizard which confirmed my federal money market has off by $1800.

So it seems like you have to do some sort of manual intervention regardless of which way you set 'Import "Investment Banking Transactions" into Default Cash Account(s)'.

- Bill

[Mark]

Hi Bill,

Can you remove the ACCTID from your "xmldump.txt" log file, and then post it or email it to me? If you retrieve transactions just for this date range, that would be helpful, so we can see what Vanguard is reporting for this case.

[Mark]

Hi Bill,

Thanks for emailing your log file. For the sell, it has:

Code: Select all

- SELLMF
   - INVSELL
      - UNITS (-170.132)
      - UNITPRICE (10.58002)
      - TOTAL (1800.0)
      - SUBACCTSEC (CASH)
      - SUBACCTFUND (CASH)
   - SELLTYPE (SELL)
- INVBANKTRAN
   - STMTTRN
      - TRNTYPE (OTHER)
      - TRNAMT (-1800.0)
   - SUBACCTFUND (CASH)

So, if you use a default cash account, and you leave on the option to record "Investment Banking" transactions, it will work properly. However, this will not work for the way dividends are being reported. Your log also showed a dividend as:

Code: Select all

- INCOME
   - INCOMETYPE (DIV)
   - TOTAL (999.68)
   - SUBACCTSEC (CASH)
   - SUBACCTFUND (CASH)
- INVBANKTRAN
   - STMTTRN
      - TRNTYPE (OTHER)
      - TRNAMT (-999.68)
      - NAME (TRANSFER TO MONEY MARKET FUND)
      - MEMO (Sweep To Money Market Fund)
   - SUBACCTFUND (CASH)

Which wouldn't work with those same settings, as leaving on the "Investment Banking Transaction" option would result in the cash from the dividend being shown as sold out of default cash, just like your withdrawal.

Unfortunately, I don't see a good way to deal with this as reported by Vanguard. We don't want to get into things like parsing the MEMO field, as the contents of this field is not part of the OFX standard. If withdrawals are less common than dividends for you, I would suggest leaving off this "Investment Banking Transaction" option, and manually recording your withdrawals when they happen. If withdrawals are more common than dividends for you, then leave on this option, and delete (or un-check in the Preview dialog) the extraneous dividend sell of cash marked with the memo "TRANSFER TO MONEY MARKET FUND".

[quercus]

Thanks Mark, that's pretty much what I suspected.