How to correct errors in Wells Fargo Advisors transactions involving UITs

jasonkanter

Every time there is a UIT transaction in Wells Fargo Advisors (WFA), Quicken misreports the number of units and the price per unit. For example, WFA shows 1751 units at $11.42 per unit, total $20,001.67. The same transaction appears in Quicken as 1.751 units at $1,141.900057 per unit, total $20,002.67. The only way I have found to fix this is to enter a new transaction with the correct data and then to void the incorrect transaction. Tedious manual intervention. Has anyone found a fix for this?

q_lurker

I will venture a guess that you are downloading transaction from WFA and accepting them into your Quicken file as is.  If so, then WFA is sending these to Quicken with these securities typed as "Bonds".  Not knowing the specifics of those Unit Investment Trusts, I can't say if that is most appropriate or not.

Further, it would be WFA who is reporting these to Quicken with the prices and units adjusted by the 1000 factor.  What I am suggesting is that WFA is reporting in one system (their's) one way and reporting to the Quicken system a different way. 

If you are up to it, you can look at the OFX logs (Quicken Help) and see the information as reported by WFA to Quicken.  Generally best to export the log file to a text file and look at it there with file search capabilities.  

I don't see anything in particular wrong with what Quicken is showing; it is just different than the WFA presentation.  As such, perhaps this is something you need adjust your mindset to, rather than make repetitive changes.  

HTH

q_lurker

So the June 25 real world transaction was a 'reinvestment' of $110.91 getting 12.179 'Units' at $9.11/unit (really 9.106659/unit).
Treated as a Security Type = Bond in Quicken, this became $110.91 getting 0.012179 'shares' at $910.6659.  (math off by 10x)
Treated as a Security Type = Other in Quicken, this became $110.91 getting 0.12179 'shares' at $910.6659.  (correct math)

I think you really need to find in the QFX file what WFA is sending for that transaction and for that security.  Is the data consistent? 
  
I have never attempted a reinvestment on a bond.  Doing so now in a test file it seems to behave 'normally'.  Enter the $110.91 total and whatever the correct number of 'shares' is 12.179, 0.12179, 0.012179 and Quicken does the math for the $/share.  That is the way the transaction goes in $ and 'shares' 

I will add that buying bonds in quicken is odd, in my opinion.  That is, through the Bonds Bought window, you buy 1 Bond at a $100 price, it costs you $1000 and you get 10 'shares' of that bond.  I understand the math and what is taking place, but it is still weird.  Somehow I see that same weirdness when you have your UIT typed as a Bond.

I'll also concur with @Rocket J Squirrel that changing the type may not fully alter all the related aspects.  I have noted the same phenomena he cited in my own files.   

q_lurker

I don't know how to find the QFX file - I don't have any dated later than March 16, unless they are hidden from file search.

The information is in the OFX log files.  

Jim_Harman

Go to Help > Log files and click on OFX Log. Click on Save As... to save it as a text file, then you can view and search the saved file with Notepad or your favorite text editor. 

q_lurker

It appears to me that WFA is saying these are "Debt" securities and that then Quicken is saying "Debt" = "Bond" with 'Corporate Bond' being the default for bonds. 

Is Quicken reading the transaction as 12503 Units and $999.75/Unit means 12.503 $1000-bonds then means 125.03 'shares' considering a $100 base (whereby the price should become 99.975)?  I don't know that answer. 

Then elsewhere in the download, WFA says the price is $10/unit.  Did Quicken enter that in the price history for the new security?

I would be more inclined to try to get WFA to send info as an "Other" rather than as a "Debt".  I think then Quicken would do no manipulation of the data. 

But since that is not likely to happen either, I think your original post with those manual corrections is the only thing to keep the data 'right'.  

Rocket J Squirrel

jasonkanter said:

I think the ultimate answer is that UITs are being handled as Bonds, whereas they ought to be handled as if they were stocks; and maybe UIT ought to be one of the default asset types.

This is what I said in my original reply to your post. UITs can be treated as any asset type except Bond and will turn out fine. As previously mentioned, you can create a UIT type and it will work. Here is what my Buy transaction looked like the last time I bought a UIT. Notice it's a normal Buy, not a Bonds Bought.

jasonkanter

Here's one example.

jasonkanter

Here's a second example.

Quicken_Tyka

Hello @jasonkanter

Thank you for taking the time to visit the Community to report this issue, although I apologize that you haven't received a response.

May I ask that you navigate to Help > About Quicken and provide the release that you are currently running.

Also, what are the regional settings for your PC? Are they set to the US?

If there is nothing unusual about the regional settings, I would next recommend running the Validate & Repair tool on the data file from the File menu > File Operations > Validate & Repair option.

In the Validate File window that opens, click the top box to "validate file" and then click OK.

When the validation completes, a data log will open in Notepad, please let us know if any errors or issues are found/repaired, and when ready close and re-open Quicken.

Please let me know what you find!

-Quicken Tyka

jasonkanter

Hello Quicken_Tyka. Thanks for your suggestion. I am running Quicken Deluxe for Windows R28.24. Regional= US, Gregorian calendar, etc nothing special.
To try the validate/repair, I will open a previously saved copy in which there were two fresh UIT transactions and I had not yet voided them and replaced them with corrected transactions. So the validate/repair report might show the errors being made.
This note to be continued after I make the attempt.
==I have studied the log file:==
<my comments in angle brackets. All the twenty-one WFA UIT transactions have happened since March 10 of this year. None of the errors identified were of any relevance to WFA or March-August 2020.>
[Thu Sep 03 12:46:44 2020]

File: "C:\Users\Jason\Documents\Quicken\JMK-2020Q3Aug27-With UIT Errors"

QDF:
Validating your data.
Repaired your data file by removing a damaged category. Please check your category list for missing categories by going to Tools>Category List.
Quicken found an invalid transaction and removed it. "SAFE BOX 0413 XX0413" 0/ 0/1900
<of no consequence>

QEL:
No read errors.

QEL:Removed 8 qel records for transfers not in register
<of no consequence>

[Thu Sep 03 12:56:32 2020]
Quicken has found 1 stock split(s) for account "TD-039", security "Direxion Technology Bear 3X Shares", on 3/29/2018 that might be missing.
<of no consequence>
------------------------------------------------------------
The following transaction(s) were linked to account(s) that are no longer in Quicken. Quicken has changed them to indicate that the account was deleted. Please check them and set the category or destination account correctly. Make sure that this does not duplicate transactions.
<all these removed as not relevant>
------------------------------------------------------------
Corrected 7 Bought or Shares Added transactions that were incorrectly categorized as a realized gain.
If any of these should have been for a Cover Short Sale, you should modify them. Listed:

<none of these relevant>
---
Minor problems with Tags corrected
No out-of-range security references found.

Validation has completed.
==end of log==

q_lurker

I will venture a guess that you are downloading transaction from WFA and accepting them into your Quicken file as is.  If so, then WFA is sending these to Quicken with these securities typed as "Bonds".  Not knowing the specifics of those Unit Investment Trusts, I can't say if that is most appropriate or not.

Further, it would be WFA who is reporting these to Quicken with the prices and units adjusted by the 1000 factor.  What I am suggesting is that WFA is reporting in one system (their's) one way and reporting to the Quicken system a different way. 

If you are up to it, you can look at the OFX logs (Quicken Help) and see the information as reported by WFA to Quicken.  Generally best to export the log file to a text file and look at it there with file search capabilities.  

I don't see anything in particular wrong with what Quicken is showing; it is just different than the WFA presentation.  As such, perhaps this is something you need adjust your mindset to, rather than make repetitive changes.  

HTH

Rocket J Squirrel

Yes, it's definitely because you have assigned the UIT an asset type of Bond. See the "Base 100" next to the price? That accounts for it.

When I hold a UIT, I classify it as asset type "Other" and asset class "Domestic bonds". If you reclassify the UIT, Quicken's results will agree with the broker's. You might need to adjust the price & number of shares after reclassifying it, I'm not sure. Back up your data file before changing anything.

jasonkanter

These transactions come in through One Step Update and arrive in my registers classified as "bonds" by Quicken, so yes the mathenanigans with "base 100" get applied. The OFX log contains some but not all of these, and lists them with the correct numbers not adjusted for base 100. The terms "bond" and "UIT" do not appear in the OFX log.
The problem then is that the total account values are off, sometimes very far off, because the number of shares is so far off in "holdings". See next graphic to illustrate this.

jasonkanter

Example of the inaccuracies introduced by this error

Rocket J Squirrel

Wow, those are huge differences. What I meant to suggest in my previous post is that you manually change the Asset Type of the UITs to Other.

jasonkanter

I'll try this. We won't know if it works until the end of the month -- all the UIT transactions have occurred between the 25th and end of month.

Rocket J Squirrel

You can test this now if you have a backup file from before the last transactions downloaded. Restore the backup with a different file name from your real QDF file. Then edit the UIT securities to be asset type Other and finally do the download. Last month's transactions should re-download.

q_lurker

jasonkanter said:

... The OFX log contains some but not all of these, and lists them with the correct numbers not adjusted for base 100. The terms "bond" and "UIT" do not appear in the OFX log.

What 'numbers' are correct?  Shares held, prices/share, total value?  Some of those values may be related through CUSIP designations rather than names.  
I'd be looking in the OFX Log for sequences like:

<INVPOS>

<SECID><UNIQUEID>xxxxxxxxx<UNIQUEIDTYPE>CUSIP</SECID>

<HELDINACCT>OTHER

<POSTYPE>LONG

<UNITS>####.###

<UNITPRICE>$$.cc

<MKTVAL>$$$$$$.cc

<DTPRICEASOF>20160224160000.000[-5:EST]

<MEMO>Price as of date based on closing price

</INVPOS>

Note that there is no "name" directly associated with that investment position.  That sequence happens to be from Vanguard; other firms may sequence differently.  But the key may be that <UNIQUEID> identifier.    

jasonkanter

Tried this. "FIRST TR PORTFOLIOS, LP 30312T114" is now classified as Other, Domestic Bond, in a QDF file that was a May backup before the UIT transactions on May 26, June 25, July 27, and August 25. Saved it as, opened it, double-checked the asset class was still Other/Domestic Bond, then did a one-step update on WFA only. Interesting results, all just like the following.
WFA transaction on June 25 was: 12.179 units, cost basis $9.11, total cost $110.91. This is the correct version.
Quicken's erroneous download on June 25 (before the current fix) showed:
0.012179 bonds, price $910.6659 (base 100), total cost $110.91.
Quicken's download of that transaction after I instituted the Asset Class fix on the May backup:
0.12179 shares, price $910.6659 per share, total cost $110.91.
So it eliminated the "base 100" rule somehow but still winds up with the price off by x100 and the units/shares off by /100.
Go figure.

Rocket J Squirrel

This leaves me pretty much out of ideas. All I know is that when I held UITs (which, currently, I don't), I never let them be automatically classified as bonds. My broker Morgan Stanley didn't download them as such. So it could be that your data are polluted.

One interesting thing is that if I re-classify an actual bond as something else, that's OK until I re-classify it back as a bond. In that case, the bond info such as maturity date and call date repopulates. So Quicken is remembering info maybe it shouldn't. Last idea would be to delete the UIT securities which were bonds and re-create them from scratch as not bonds. You can create an Asset Type "UID" if you routinely hold these. That should operate the same as "Other".

q_lurker

So the June 25 real world transaction was a 'reinvestment' of $110.91 getting 12.179 'Units' at $9.11/unit (really 9.106659/unit).
Treated as a Security Type = Bond in Quicken, this became $110.91 getting 0.012179 'shares' at $910.6659.  (math off by 10x)
Treated as a Security Type = Other in Quicken, this became $110.91 getting 0.12179 'shares' at $910.6659.  (correct math)

I think you really need to find in the QFX file what WFA is sending for that transaction and for that security.  Is the data consistent? 
  
I have never attempted a reinvestment on a bond.  Doing so now in a test file it seems to behave 'normally'.  Enter the $110.91 total and whatever the correct number of 'shares' is 12.179, 0.12179, 0.012179 and Quicken does the math for the $/share.  That is the way the transaction goes in $ and 'shares' 

I will add that buying bonds in quicken is odd, in my opinion.  That is, through the Bonds Bought window, you buy 1 Bond at a $100 price, it costs you $1000 and you get 10 'shares' of that bond.  I understand the math and what is taking place, but it is still weird.  Somehow I see that same weirdness when you have your UIT typed as a Bond.

I'll also concur with @Rocket J Squirrel that changing the type may not fully alter all the related aspects.  I have noted the same phenomena he cited in my own files.   

jasonkanter

I don't know how to find the QFX file - I don't have any dated later than March 16, unless they are hidden from file search.
If I use a backup and delete the six securities completely (I doubt Quicken will permit this) and then rebuild them as a new asset class "UIT" and then update, will Quicken recognize the update transactions as belonging to the newly created security? I doubt it but I'm not even close to certain about anything in this mess. The easiest path has been to recreate the *transactions* as new non-bond transactions with the correct numerics, and then void the erroneous transactions that have been thus supplanted.
Thanks for your helpful thoughts, to both.

q_lurker

I don't know how to find the QFX file - I don't have any dated later than March 16, unless they are hidden from file search.

The information is in the OFX log files.  

Jim_Harman

Go to Help > Log files and click on OFX Log. Click on Save As... to save it as a text file, then you can view and search the saved file with Notepad or your favorite text editor. 

jasonkanter

Well I have studied the OFX log and located three 8/25/20 UIT transactions that appear in the OFX download of 8/27. The total cost, and the number of units, are both correct. The price per unit is, however, insane. Screenshots follow.

jasonkanter

This shows the disconnect between the actual Wells Fargo Advisors report on the three transactions, and the Quicken OFX report on the same transactions.

jasonkanter

Here's how the first two transactions (AAM and FT 60/40) appear in the Quicken register.

jasonkanter

These huge UNITPRICE figures are interesting. They represent the cents in the actual unitprice. The AAM transaction, for example, should have a price per unit of $11.99 according to WFA. The OFX's UNITPRICE is 9921.3057. That's the 99cents and correct fraction to make the "total price" exactly correct.
The first FT transaction's price per unit should be 9.46; that 46 becomes the OFX's UNITPRICE of 4576.6898, which contains the additional fraction to make the math work.
But still, it's not a correct interpretation of the WFA transaction.

q_lurker

That was the sort of info I was hoping you would find.  I am not concerned with the transaction <UNITPRICE> values.  I don't think Quicken uses or pays attention to that field, though it is odd how WFA is populating it.

I do take note that WFA is sending this as a Div and a Buy rather than a reinvestment.  I assume in your transaction history, you similarly saw a Div action for the dollar figures indicated.  (You only posted the Buy transactions.)

On the buys, I see WFA sending them as a <BUYDEBT> transactions.  I have not spotted any similar transactions in my recent downloads, but I am wondering if somehow that is the reason that the 6.86200 shares in the QFX data gets divided by 100 to become 0.06862 shares in the Quicken transaction.

(By the way, is that Buy transaction you presented with the security type as a Bond or as a UIT?).    

 

jasonkanter

Yes, there are accompanying Div actions for these UIT transactions. These are not so significant because they are small amounts, but maybe they are revealing, see screenshot.
The Security Type comes in as Bond, Corporate Bond. (James's brother.) In this case I changed it to Other/Domestic Bond, which seems to have not fixed anything. I did also correct the Buy transaction by reentering the correct data as a new non-bond transaction and then voiding the transaction that was initially brought in. So this transaction history reflects a lot of manipulation. See next comment however.

jasonkanter

There was a large buy of a new UIT security.
12,503 units @$10.00, total cost $125,005.74
Attached image shows how the transaction gets misinterpreted by Quicken.
Quicken winds up thinking this is 125 shares @ $1000
Next comment shows the pertinent OFX data.

jasonkanter

Here's the image of the pertinent lines from the OFX relating to this initial $125k buy.

jasonkanter

I think the ultimate answer is that UITs are being handled as Bonds, whereas they ought to be handled as if they were stocks; and maybe UIT ought to be one of the default asset types.

q_lurker

It appears to me that WFA is saying these are "Debt" securities and that then Quicken is saying "Debt" = "Bond" with 'Corporate Bond' being the default for bonds. 

Is Quicken reading the transaction as 12503 Units and $999.75/Unit means 12.503 $1000-bonds then means 125.03 'shares' considering a $100 base (whereby the price should become 99.975)?  I don't know that answer. 

Then elsewhere in the download, WFA says the price is $10/unit.  Did Quicken enter that in the price history for the new security?

I would be more inclined to try to get WFA to send info as an "Other" rather than as a "Debt".  I think then Quicken would do no manipulation of the data. 

But since that is not likely to happen either, I think your original post with those manual corrections is the only thing to keep the data 'right'.  

Rocket J Squirrel

jasonkanter said:

I think the ultimate answer is that UITs are being handled as Bonds, whereas they ought to be handled as if they were stocks; and maybe UIT ought to be one of the default asset types.

This is what I said in my original reply to your post. UITs can be treated as any asset type except Bond and will turn out fine. As previously mentioned, you can create a UIT type and it will work. Here is what my Buy transaction looked like the last time I bought a UIT. Notice it's a normal Buy, not a Bonds Bought.

q_lurker

In that same context from @Rocket J Squirrel, you may need to create the security as a stock or other (anything but bond) prior to the initial download of that security from WFA.  As I recall from the prior posts, just changing the security from Bond to Stock was not fully successful for you.