nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

QWin 2017 - Investment sell transactions downloaded via QFX are entered into the register signed bac

Miranda

Ever since updating to Quicken 2017 Deluxe I have been having an issue with the downloaded transactions for my 401k account. This is not a new account, I have had this account since 2006, and it has been through many versions of Quicken along the way.

My bank provides downloads via QFX file for import. Once a month, they sell off a fraction of my shares in each fund that I have to pay maintenance fees and because of the way they divide things up among 10 funds it is 30 separate transactions every time they sell stuff off. So when I download the QFX a few times a year, I usually have 100-150 sell transactions affected by the below problem.

Since my update to 2017, all of these sell transactions are being imported with the cash amount signed improperly. For example, if I sell 10 shares for $5, the sell transaction is being entered into the register as -$5.00 from the investment amount, but the cash amount is also entered as -$5.00, rather than increasing my cash balance by $5.00. This throws the cash balance totals completely off every time I import transactions.

It is not a difficult fix to get the cash amount for the transaction to be signed properly. I have figured out that I don't even need to edit it, I just need to re-save it, and the cash amount is signed properly after saving. However, this is an extremely tedious process. It takes sometimes up to a minute or 2 per transaction to re-save it, and there are always 100+ of them. I don't know if it's because of the age/number of transactions in the account, but every transaction edit takes this long.

I thought perhaps the QFX file was incorrect, but I have QFX files for this bank/account going back several years, and in all of them the total amounts look the same (I'm not really sure how much of this data is identifying, so I edited some of it out, the units/price/total are unchanged, though):


********************OTHER-0.05799419.657239-1.14CASHCASH

Is there anything that can be done about getting these transactions to enter properly the first time?

Find more posts tagged with

Comments

mshiggins

Who is your 401(k) provider and what year version of Quicken did you use prior to QW2017?

mshiggins

From C. D. Bales:

To give you an idea what a typical Sell transaction looks like, here is a copy of the OFX data for one of my mutual fund Sell transactions:

0042817-0207640703562316231000

20170428

20170428

FUND MERGE REDEEM NON-TXBL

nnnnnnnnn

CUSIP

-583.3440

36.1971

0.00

0.00

0.00

21115.36

OTHER

OTHER

SELL

As you can see, the transaction is positive.

You might see if you can provide more information about the transaction from your original post in this discussion, and in a more readable format; and provide info about any other transaction(s) that might be related. Also perhaps double-check the correspondence between your current transaction and one that "worked" in the past.

As I understand the situation, the proceeds from your Sell transaction are basically going to be immediately removed from the account to pay fees, so at some point on, or near, the date of the Sell transaction there should be some transaction with a negative effect on the cash in the account.

If Q2017 were incorrectly handling downloaded Sell transactions, there should have been a lot of complaints by now ... I can't recall seeing any.

unknown

In both of your posts I find it hard to read because they lack the "tags/fields" formatting that I expect from OFX data (maybe the forum is missing up the display because it thinks it is HTML, if it is my post will be messed up too and I will try to fix it if possible.)

To start about the slowness of accepting transactions into Quicken. Quicken wasn't designed for a lot of investment transactions/securities/and lots in any given account. Having 100+ transactions in an investment account each month is certainly going to lead to bad performance in that account in a short period of time.

On the signs of the amount in a buy/sell, here it is from the OFX standards:

In a transaction, the signage of UNITS and TOTALs is determined by the transaction's effect on the underlying cash balance (an investment sell would contain a positive TOTAL and negative UNITS whereas an investment buy would contain a negative TOTAL and positive UNITS). All other Investment transaction amounts are always positively signed. In other words UNITPRICE, COMMISSION, FEES, TAXES, PENALTY, WITHHOLDING, STATEWITHHOLDING, LOAD, MARKUP and MARKDOWN are always positive numbers.

Here is are examples of a buy and a sell:
                    <BUYMF>
                        <INVBUY>
                            <INVTRAN>
                                <FITID>09919592626.5533.01202011.0
                                <DTTRADE>20110120160000.000[-5:EST]
                                <DTSETTLE>20110120160000.000[-5:EST]
                                <MEMO>EXCHANGE FROM Prime MM
                            </INVTRAN>
                            <SECID>
                                <UNIQUEID>922042841
                                <UNIQUEIDTYPE>CUSIP
                            </SECID>
                            <UNITS>251.453
                            <UNITPRICE>39.57
                            <FEES>50.0
                            <TOTAL>-10000.0
                            <SUBACCTSEC>CASH
                            <SUBACCTFUND>OTHER
                        </INVBUY>
                        <BUYTYPE>BUY
                    </BUYMF>

                    <SELLMF>
                        <INVSELL>
                            <INVTRAN>
                                <FITID>09919592626.1220.06042010.0
                                <DTTRADE>20100604160000.000[-5:EST]
                                <DTSETTLE>20100604160000.000[-5:EST]
                                <MEMO>EXCHANGE TO Prime MM
                            </INVTRAN>
                            <SECID>
                                <UNIQUEID>921921508
                                <UNIQUEIDTYPE>CUSIP
                            </SECID>
                            <UNITS>-1500.0
                            <UNITPRICE>11.57
                            <TOTAL>17355.0
                            <SUBACCTSEC>CASH
                            <SUBACCTFUND>OTHER
                        </INVSELL>
                        <SELLTYPE>SELL
                    </SELLMF>

Also I'm confused about the statements of "cash transactions". I wouldn't expect any in a 401K.
401Ks do not "deal" in cash. They buy and sell funds.

So yes Quicken would be using the cash balance as the "go between", but the transactions should be a series of buys/sells that cancel out the cash in the 401K account.

On the different behavior you say you are seeing in Quicken 2017 even if the QFX files "signs" haven't changed.

I certainly have not back tested that anything has changed, but if your financial institution has been coding the sign wrong all this time, I suppose it is possible that in the past Quicken "hid/worked around" the bad sign, and now Quicken 2017 isn't (I sort of doubt it, but it is within reason until proven otherwise).

unknown

Well the OFX field tags posted fine, so I'm not sure why yours don't include them.

On the subject of filtering out sensitive information. These should be the only ones:
<INTU.USERID>
<ACCTID>

Miranda

Sorry, I didn't notice that the tags had gotten messed up from the original post, and I haven't gotten any notifications that there were replies even though I enabled that! They may have gotten messed up when I edited the post to fix a typo and re-saved it, because they did post originally ok. I'll edit the original and fix that.

My 401k provider is Transamerica Retirement Solutions. I upgraded from Quicken 2015 Deluxe to 2017, although I've had the account through at least 4 or 5 different upgrades. I usually upgrade every 1-2 years since 1997.

My finance terminology about cash transactions and things may be off, but I'm a software developer by profession, so going through the files trying to figure out where the issue lies in why they are not being imported correctly is something I'm comfortable with. :-) When I was talking about cash, I was trying to convey that the balance was moving in the wrong direction when the sell transactions were imported. I have no corresponding buy transaction for any of the sells, because it's just to pay fees, and the fees/expenses do not come through in their Quicken download. I have always just manually added by hand a MiscExp transaction to balance out the sells. So, if they sell off $10 of my funds in 30 transactions, then I expect the cash balance to increase to $10, and then I enter the MiscExp transaction by hand for $10 to cancel it out... but instead of there being $10 cash for me to zero out, I had -$10 cash, and my $10 fee brought it to -$20.

From reading the above posts and looking through files going back several years, I think this is a combination of Transamerica's file format being wrong starting around August 2014 timeframe and the upgrade to Quicken 2017. Initially I had gone back to a file from last year, when I knew I was still using Quicken 2015, and compared it to the latest one I downloaded, and the Units and Total were both the same between the two files. But it looks like it's always been wrong according to the description of the OFX standard above, because in both versions of the file, the buy transactions have positive units and positive total, while the sell transactions have negative units and negative total.

I didn't really think to go back much further than a file that was known good with Quicken 2015, but I have QFX files for this account going back to 2008, so I started looking further back. Sometime between when I downloaded a file with transactions ending 8/19/2014 and when I downloaded another one with transactions ending 10/5/2014, the file generation changed. Prior to that, all the buy/sell transactions were BUYMF and SELLMF like shown above, and the file was generated with no linebreaks at all, and afterwards many more linebreaks in the file and all the buys and sells are BUYOTHER and SELLOTHER... but I'm sure that is all just cosmetic. The real issue seems to be that before the cosmetic changes to the file happened, the transactions had the correct positive/negative values for units and total as described in the OFX standard.

I guess that Quicken 2015 was just handling it better than 2017 is, but the real people I should be complaining to is Transamerica. I don't have much hope that will go anywhere, so maybe I will just start pre-processing my QFX files after I download them and before I import them so that they match the standard. While also tedious, it is much less tedious to edit a text file than it is to manually re-save 100+ transactions in Quicken once it's already been imported.

This is what was originally posted that got mangled:

<SELLOTHER>
  <INVSELL>
    <INVTRAN>
      <FITID>2017-06-16162020170616929HBN8146774907433340013985820174333400</FITID>
      <DTTRADE>20170616</DTTRADE>
      <DTSETTLE>20170616</DTSETTLE>
    </INVTRAN>
    <SECID>
      <UNIQUEID>81467749074333400</UNIQUEID>      
      <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>-0.057994</UNITS>
    <UNITPRICE>19.657239</UNITPRICE>
    <TOTAL>-1.14</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
  </INVSELL>
</SELLOTHER>

And here is a buy transaction from the same file as above:

<BUYOTHER>
  <INVBUY>
    <INVTRAN>
      <FITID>2017-06-05200201706059228G43848865246790740013985820174333400</FITID>
      <DTTRADE>20170605</DTTRADE>
      <DTSETTLE>20170605</DTSETTLE>
    </INVTRAN>
    <SECID>
      <UNIQUEID>38488652467907400</UNIQUEID>
      <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>1.367931</UNITS>
    <UNITPRICE>11.045876</UNITPRICE>
    <TOTAL>15.11</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
  </INVBUY>
</BUYOTHER>

mshiggins

Sorry, I didn't notice that the tags had gotten messed up from the original post, and I haven't gotten any notifications that there were replies even though I enabled that! They may have gotten messed up when I edited the post to fix a typo and re-saved it, because they did post originally ok. I'll edit the original and fix that.

My 401k provider is Transamerica Retirement Solutions. I upgraded from Quicken 2015 Deluxe to 2017, although I've had the account through at least 4 or 5 different upgrades. I usually upgrade every 1-2 years since 1997.

My finance terminology about cash transactions and things may be off, but I'm a software developer by profession, so going through the files trying to figure out where the issue lies in why they are not being imported correctly is something I'm comfortable with. :-) When I was talking about cash, I was trying to convey that the balance was moving in the wrong direction when the sell transactions were imported. I have no corresponding buy transaction for any of the sells, because it's just to pay fees, and the fees/expenses do not come through in their Quicken download. I have always just manually added by hand a MiscExp transaction to balance out the sells. So, if they sell off $10 of my funds in 30 transactions, then I expect the cash balance to increase to $10, and then I enter the MiscExp transaction by hand for $10 to cancel it out... but instead of there being $10 cash for me to zero out, I had -$10 cash, and my $10 fee brought it to -$20.

From reading the above posts and looking through files going back several years, I think this is a combination of Transamerica's file format being wrong starting around August 2014 timeframe and the upgrade to Quicken 2017. Initially I had gone back to a file from last year, when I knew I was still using Quicken 2015, and compared it to the latest one I downloaded, and the Units and Total were both the same between the two files. But it looks like it's always been wrong according to the description of the OFX standard above, because in both versions of the file, the buy transactions have positive units and positive total, while the sell transactions have negative units and negative total.

I didn't really think to go back much further than a file that was known good with Quicken 2015, but I have QFX files for this account going back to 2008, so I started looking further back. Sometime between when I downloaded a file with transactions ending 8/19/2014 and when I downloaded another one with transactions ending 10/5/2014, the file generation changed. Prior to that, all the buy/sell transactions were BUYMF and SELLMF like shown above, and the file was generated with no linebreaks at all, and afterwards many more linebreaks in the file and all the buys and sells are BUYOTHER and SELLOTHER... but I'm sure that is all just cosmetic. The real issue seems to be that before the cosmetic changes to the file happened, the transactions had the correct positive/negative values for units and total as described in the OFX standard.

I guess that Quicken 2015 was just handling it better than 2017 is, but the real people I should be complaining to is Transamerica. I don't have much hope that will go anywhere, so maybe I will just start pre-processing my QFX files after I download them and before I import them so that they match the standard. While also tedious, it is much less tedious to edit a text file than it is to manually re-save 100+ transactions in Quicken once it's already been imported.

This is what was originally posted that got mangled:
<SELLOTHER>
  <INVSELL>
    <INVTRAN>
      <FITID>2017-06-16162020170616929HBN8146774907433340013985820174333400</FITID>
      <DTTRADE>20170616</DTTRADE>
      <DTSETTLE>20170616</DTSETTLE>
    </INVTRAN>
    <SECID>
      <UNIQUEID>81467749074333400</UNIQUEID>      
      <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>-0.057994</UNITS>
    <UNITPRICE>19.657239</UNITPRICE>
    <TOTAL>-1.14</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
  </INVSELL>
</SELLOTHER> 
And here is a buy transaction from the same file as above:
<BUYOTHER>
  <INVBUY>
    <INVTRAN>
      <FITID>2017-06-05200201706059228G43848865246790740013985820174333400</FITID>
      <DTTRADE>20170605</DTTRADE>
      <DTSETTLE>20170605</DTSETTLE>
    </INVTRAN>
    <SECID>
      <UNIQUEID>38488652467907400</UNIQUEID>
      <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>1.367931</UNITS>
    <UNITPRICE>11.045876</UNITPRICE>
    <TOTAL>15.11</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
  </INVBUY>
</BUYOTHER>

From C. D. Bales:

"In both of your posts I find it hard to read because they lack the "tags/fields" formatting that I expect from OFX data (maybe the forum is missing up the display because it thinks it is HTML ...."

Yes, that is what happened; my bad, I did not think of that before I posted (and the transcriptionist missed it as well).

FWIW: Here is my previously posted mutual fund sold transaction; with left and right braces replacing less-than and greater-than signs.

{SELLMF}

{INVSELL}

{INVTRAN}

{FITID}0042817-0207640703562316231000

{DTTRADE}20170428

{DTSETTLE}20170428

{MEMO}FUND MERGE REDEEM NON-TXBL

{/INVTRAN}

{SECID}

{UNIQUEID}nnnnnnnnn

{UNIQUEIDTYPE}CUSIP

{/SECID}

{UNITS}-583.3440

{UNITPRICE}36.1971

{COMMISSION}0.00

{TAXES}0.00

{FEES}0.00

{TOTAL}21115.36

{SUBACCTSEC}OTHER

{SUBACCTFUND}OTHER

{/INVSELL}

{SELLTYPE}SELL

{/SELLMF}

Miranda

I guess I can't edit the original post anymore, but the example that got mangled in the original is in the previous reply. :-)

Thanks for your help mshiggins and QPW! I think it seems like I can now see where it's gone wrong.